Agenda

1. Maestría en BioinformáticaBases de Datos y Sistemas de InformaciónOtros objetos de Base de DatosIng. Alfonso Vicente, PMPalfonso.vicente@logos.com.uy

2. Agenda • Motivación • Sentencia CREATE SEQUENCE • Utilización Secuencias Triggers Vistas Código almacenado

3. Agenda • Motivación • Sentencia CREATE TRIGGER • Utilización • Autonumerados con secuencias y triggers Secuencias Triggers Vistas Código almacenado

4. Agenda • Motivación • Sentencia CREATE VIEW • Utilización Secuencias Triggers Vistas Código almacenado

5. Agenda • Motivación • Estructura básica de PL/SQL • Procedimientos y Funciones Secuencias Triggers Vistas Código almacenado

6. Agenda • Motivación • Sentencia CREATE SEQUENCE • Utilización Secuencias Triggers Vistas Código almacenado

7. 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 surrogatekey 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)

8. Secuencias • Sentencia CREATE SEQUENCE • create sequence <nombre_secuencia> • start with 1 increment by 1; • Ejemplo • SQL> createtable movimientos ( • 2 id integernotnullprimarykey, • 3 origen integernotnull, • 4 destino integernotnull, • 5 monto number(12,2) notnull • 6 ); • Tablecreated. • SQL> createsequenceseq_movimientosstartwith 1 incrementby 1; • Sequencecreated.

9. Secuencias • Utilización • Las secuencias tienen funciones currval y nextval que devuelven el valor actual y el próximo valor • SQL> selectseq_movimientos.nextvalfrom dual;  1 • SQL> selectseq_movimientos.nextvalfrom dual;  2 • SQL> selectseq_movimientos.currvalfrom dual;  2 • La función nextval, además, adelanta la secuencia al próximo valor • Si SIEMPRE insertamos nextval en una surrogatekey nos aseguramos que no habrá valores repetidos

10. Secuencias • Utilización • SQL> insertinto movimientos(id, origen, destino, monto) • 2 values (seq_movimientos.nextval, 1111, 2222, 500); • 1 rowcreated. • SQL> insertinto movimientos(id, origen, destino, monto) • 2 values (seq_movimientos.nextval, 2222, 3333, 1000); • 1 rowcreated. • SQL> select * from movimientos; • ID ORIGEN DESTINO MONTO • ---------- ---------- ---------- ---------- • 3 1111 2222 500 • 4 2222 3333 1000

11. Agenda • Motivación • Sentencia CREATE TRIGGER • Utilización Secuencias Triggers Vistas Código almacenado

12. 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 (beforeinsert, afterupdate, etc)

13. Triggers • Sentencia CREATE TRIGGER • createtrigger <nombre_trigger> • {before|after} {insert|update|delete} on <nombre_tabla> • foreachrow • [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

14. 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> createtriggertrg_grandes_movimientos • 2 afterinserton movimientos • 3 foreachrow • 4 when (new.monto >= 10000) • 5 begin • 6 insertintograndes_movimientos • 7 values (:new.id, :new.origen, :new.destino, :new.monto); • 8 end; • 9 / • Triggercreated.

15. 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

16. 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> createtriggertrg_audit_movimientos • 2 afterupdateon movimientos • 3 foreachrow • 4 begin • 5 insertintoaud_m(id, origen, destino, monto, fmod, umod) • 6 values (:old.id, :old.origen, :old.destino, :old.monto, • 7 sysdate, user); • 8 end; • 9 /

17. 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

18. 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

19. 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

20. Agenda • Motivación • Sentencia CREATE VIEW • Utilización Secuencias Triggers Vistas Código almacenado

21. Vistas • Motivación • Las vistas son consultas (SELECTs) almacenadas con un nombre • Hay por lo menos dos buenos motivos para crear vistas • Simplificar el código y ofrecer vistas apropiadas de los datos • Permitir controlar la seguridad con una granularidad más fina que la de tabla

22. 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

23. Vistas • Sentencia CREATE VIEW • createview <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)

24. Vistas • Utilización • Es común crear vistas para facilitar las consultas • createviewv_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;

25. 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) • createview 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 • grantselecton v_empleados_dir_03 tomedico_rocha;

26. Agenda • Motivación • Estructura básica de PL/SQL • Procedimientos y funciones Secuencias Triggers Vistas Código almacenado

27. 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

28. 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

29. 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

30. 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 • insertinto movimientos(origen, destino, monto) • values (19, 73, 500) • END; • ¿Qué nos hace falta para hacerlo genérico?

31. 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

32. 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_MONTOwhere id = V_ORIGEN; • -- Agregamos V_MONTO a la cuenta V_DESTINO • update cuentas set monto = monto + V_MONTOwhere id = V_DESTINO; • -- Registramos el movimiento • insertinto movimientos(origen, destino, monto) • values (V_ORIGEN, V_DESTINO, V_MONTO) • END;

33. 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