Maestría en Bioinformática

Bases de Datos y Sistemas de Información

Otros objetos de Base de Datos

Ing. Alfonso Vicente, PMP alfonso.vicente@logos.com.uy

Agenda

Motivación

Sentencia CREATE SEQUENCE

Utilización

Secuencias

Triggers

Vistas

Código almacenado

Agenda

Motivación

Sentencia CREATE TRIGGER

Utilización

Autonumerados con secuencias

y triggers

Secuencias

Triggers

Vistas

Código almacenado

Agenda

Motivación

Sentencia CREATE VIEW

Utilización

Secuencias

Triggers

Vistas

Código almacenado

Agenda

Motivación

Estructura básica de PL/SQL

Procedimientos y Funciones

Secuencias

Triggers

Vistas

Código almacenado

Agenda

Motivación

Sentencia CREATE SEQUENCE

Utilización

Secuencias

Triggers

Vistas

Código almacenado

Secuencias

Motivación

• Una secuencia es un objeto de esquema que permite

obtener números que no se repiten

• Es muy común utilizar secuencias para asignar números

que deben ser diferentes, como los de una surrogate key en

una tabla

• Lo anterior no asegura que los números sean consecutivos

y “sin huecos”, por lo que no es el método a elegir si se

requiere que no existan huecos (como en el caso de

números de factura)

Secuencias

Sentencia CREATE SEQUENCE

create sequence <nombre_secuencia>

start with 1 increment by 1;

• Ejemplo

SQL> create table movimientos (

2 id integer not null primary key,

3 origen integer not null,

4 destino integer not null,

5 monto number(12,2) not null

6 );

Table created.

SQL> create sequence seq_movimientos start with 1 increment by 1;

Sequence created.

Secuencias

Utilización

• Las secuencias tienen funciones currval y nextval que

devuelven el valor actual y el próximo valor

SQL> select seq_movimientos.nextval from dual; 1

SQL> select seq_movimientos.nextval from dual; 2

SQL> select seq_movimientos.currval from dual; 2

• La función nextval, además, adelanta la secuencia al

próximo valor

• Si SIEMPRE insertamos nextval en una surrogate key nos aseguramos que no habrá valores repetidos

Secuencias

Utilización

SQL> insert into movimientos(id, origen, destino, monto)

2 values (seq_movimientos.nextval, 1111, 2222, 500);

1 row created.

SQL> insert into movimientos(id, origen, destino, monto)

2 values (seq_movimientos.nextval, 2222, 3333, 1000);

1 row created.

SQL> select * from movimientos;

ID ORIGEN DESTINO MONTO

---------- ---------- ---------- ----------

3 1111 2222 500

4 2222 3333 1000

Agenda

Motivación

Sentencia CREATE TRIGGER

Utilización

Secuencias

Triggers

Vistas

Código almacenado

Triggers

Motivación

• Un trigger es un código almacenado que se ejecuta

disparado por alguna sentencia

• Se utilizan para automatizar tareas que deben realizarse

cada vez que se ejecuta una sentencia, por ejemplo, para

objetivos de auditoría

• Se puede especificar que se disparen antes o después de

una sentencia, usualmente DML (before insert, after update,

etc)

Triggers

Sentencia CREATE TRIGGER

create trigger <nombre_trigger>

{before|after} {insert|update|delete} on <nombre_tabla>

for each row

[when (<predicado>)]

begin

<codigo>

end;

• Se puede referenciar el estado anterior de la tupla (en casos

de update y delete) mediante :old

• Se puede referenciar el estado final de la tupla (en casos de

update e insert) mediante :new

Triggers

Utilización

• Ejemplo: copiar los movimientos grandes a otra tabla

SQL> create table grandes_movimientos

2 as (select * from movimientos where 0=1);

Table created.

SQL> create trigger trg_grandes_movimientos

2 after insert on movimientos

3 for each row

4 when (new.monto >= 10000)

5 begin

6 insert into grandes_movimientos

7 values (:new.id, :new.origen, :new.destino, :new.monto);

8 end;

9 /

Trigger created.

Triggers

Utilización

SQL> select * from grandes_movimientos;

no rows selected

SQL> insert into movimientos(id, origen, destino, monto)

2 values (seq_movimientos.nextval, 4444, 5555, 9900);

1 row created.

SQL> insert into movimientos(id, origen, destino, monto)

2 values (seq_movimientos.nextval, 6666, 7777, 10500);

1 row created.

SQL> select * from grandes_movimientos;

ID ORIGEN DESTINO MONTO

---------- ---------- ---------- ----------

6 6666 7777 10500

Triggers

Utilización

• Otro caso de uso: auditoría

SQL> create table aud_m as (select * from movimientos where 0=1);

SQL> alter table aud_m add (fmod date, umod varchar2(10));

SQL> create trigger trg_audit_movimientos

2 after update on movimientos

3 for each row

4 begin

5 insert into aud_m(id, origen, destino, monto, fmod, umod)

6 values (:old.id, :old.origen, :old.destino, :old.monto,

7 sysdate, user);

8 end;

9 /

Triggers

Utilización

SQL> update movimientos set monto = 9999 where id = 6;

1 row updated.

SQL> select * from aud_m;

ID ORIGEN DESTINO MONTO FMOD UMOD

---------- ---------- ---------- ---------- --------- ----------

6 6666 7777 10500 31-MAY-12 RRHH

SQL> select * from movimientos where id = 6;

ID ORIGEN DESTINO MONTO

---------- ---------- ---------- ----------

6 6666 7777 9999

Triggers

Autonumerados con secuencias y triggers

• Algunos RDBMS permiten la definición de columnas

autonumeradas en la sentencia CREATE TABLE

• DB2: id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

• MySQL: id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

• En otros (como Oracle y PostgreSQL) no existe esta

funcionalidad pero se puede lograr con secuencias y defaults o secuencias y triggers

Triggers

Autonumerados con secuencias y triggers

create table paises(id number, nombre varchar2(20));

create sequence s_paises start with 1 increment by 1;

create trigger bi_paises before insert on paises for each row

begin

:new.id := s_paises.nextval;

end;

/

insert into paises(nombre) values ('Uruguay');

insert into paises(nombre) values ('Argentina');

SQL> select * from paises;

ID NOMBRE

---------- --------------------

1 Uruguay

2 Argentina

Agenda

Motivación

Sentencia CREATE VIEW

Utilización

Secuencias

Triggers

Vistas

Código almacenado

Vistas

Motivación

• Las vistas son consultas (SELECTs) almacenadas con un

nombre

• Hay por lo menos dos buenos motivos para crear vistas

1. Simplificar el código y ofrecer vistas apropiadas de los

datos

2. Permitir controlar la seguridad con una granularidad

más fina que la de tabla

Vistas

Motivación

• Imagine un sistema de gestión de RRHH, y una tabla

EMPLEADOS con columnas: cedula, nombre, sueldo,

dirección y teléfono

• A alguien que trabaja en liquidación de sueldos se le

querrían dar privilegios de SELECT y UPDATE sobre la

columna sueldo, pero no dirección ni teléfono

• Al médico certificador, se le querrían dar privilegios de

SELECT sobre la columna dirección y teléfono, pero no

sueldo

Vistas

Sentencia CREATE VIEW

create view <nombre_vista> as <sentencia-select>

• Una vez creada, la vista se puede consultar (SELECT) como

si fuera una tabla más

• Dependiendo cómo se haya creado la vista, y cuál sea el

RDBMS, tal vez la vista se pueda modificar (en la mayoría

de los RDBMSs no todas las vistas teóricamente

modificables se pueden modificar)

• Se pueden asignar privilegios sobre la vista (e.g. GRANT

SELECT ON EMPLEADOS_DIR TO MEDICO)

Vistas

Utilización

• Es común crear vistas para facilitar las consultas

create view v_empleados as

select

e.id,

e.nombre,

e.apellido,

e.mail,

c.nom_cargo cargo,

d.nom_departamento departamento,

e.sueldo

from

empleados e,

departamentos d,

cargos c

where

e.cargo = c.id_cargo

and e.departamento = d.id_departamento;

Vistas

Utilización

• Es común crear vistas para aplicar el principio del menor

privilegio (los usuarios deben tener privilegios para hacer

todo lo que necesitan hacer, y ningún privilegio más)

• Note que se pueden restringir los privilegios por columnas

(mediante proyección) y por tuplas (mediante selección)

create view v_empleados_dir_03 as

select e.id, e.nombre, e.apellido, e.direccion, e.telefono

from empleados e

where departamento = 3;

-- Departamento 3 = Rocha

grant select on v_empleados_dir_03 to medico_rocha;

Agenda

Motivación

Estructura básica de PL/SQL

Procedimientos y funciones

Secuencias

Triggers

Vistas

Código almacenado

Código almacenado

Motivación

• Imagine un sistema bancario, donde se debe asegurar que

las transferencias siempre se realizan de la misma manera

• Es necesario hacer un programa, en cualquier lenguaje (e.g.

Java, C, Ruby), que realice las transferencias

• Los RDBMSs nos ofrecen una alternativa a realizar estos

programas con lenguajes externos, y es un lenguaje interno

del DBMS que queda almacenado en la propia base

• Casi todo los RDBMSs ofrecen uno: Oracle (PL/SQL), DB2

(SQL PL), PostgreSQL (PL/PgSQL), MySQL

Código almacenado

Motivación

• Los lenguajes procedurales permiten además ejecutar SQL

estático, evitando la preparación del plan de acceso en cada

ejecución

• Desde hace unos años, DB2 ofrece cada vez más

compatibilidad con el PL/SQL de Oracle, para facilitar la

migración de Oracle a DB2

Código almacenado

Estructura básica de PL/SQL

• PL/SQL se estructura en bloques, definidos por las palabras

clave DECLARE, BEGIN, EXCEPTION y END:

DECLARE

--

-- sección declarativa (opcional)

--

BEGIN

--

-- sección ejecutable (obligatoria)

--

EXCEPTION

--

-- manejo de excepciones (opcional)

--

END

Código almacenado

Estructura básica de PL/SQL

• Ejemplo de un programa mínimo

BEGIN

-- Sacamos 500 de la cuenta 19

update cuentas set monto = monto – 500 where id = 19;

-- Agregamos 500 a la cuenta 73

update cuentas set monto = monto + 500 where id = 73;

-- Registramos el movimiento

insert into movimientos(origen, destino, monto)

values (19, 73, 500)

END;

• ¿Qué nos hace falta para hacerlo genérico?

Código almacenado

Procedimientos y funciones

• Los bloques PL/SQL pueden:

• Ser anónimos (como el del ejemplo anterior)

• Tener nombre: procedimientos y funciones

• Procedimientos

• Pueden tener parámetros

• Usualmente modifican la instancia

• Funciones

• Pueden tener parámetros

• Retornan un valor

• Usualmente no modifican la instancia

Código almacenado

Procedimientos y funciones

• Ejemplo de procedimiento CREATE OR REPLACE PROCEDURE TRANSFERIR(

V_ORIGEN IN NUMBER,

V_DESTINO IN NUMBER,

V_MONTO IN NUMBER) IS

BEGIN

-- Sacamos V_MONTO de la cuenta V_ORIGEN

update cuentas set monto = monto – V_MONTO where id = V_ORIGEN;

-- Agregamos V_MONTO a la cuenta V_DESTINO

update cuentas set monto = monto + V_MONTO where id = V_DESTINO;

-- Registramos el movimiento

insert into movimientos(origen, destino, monto)

values (V_ORIGEN, V_DESTINO, V_MONTO)

END;

Código almacenado

• Queda mucho por ver, se podría hacer un curso entero de

programación sobre bases de datos

• Tutorial amigable sobre PL/SQL

Inglés Español

Building with blocks Construyendo con bloques en PL/SQL

Controlling the Flow of Execution Aún no

Working with Strings Aún no

Working with Numbers Aún no

Working with Dates Aún no

Error Management Aún no

Working with Records Aún no