1 / 33

Agenda

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

alexia
Télécharger la présentation

Agenda

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related