Laboratorio Informix

Stored ProceduresTriggers

Repaso

A partir del ER, hicimos las tablas de la base de datos. Creamos en MySql las base de datos y lastablasHicimos una aplicación que se comunicacon el motor de la BD y usamos la BD.Nos comunicamos directamente con elmotor de la BD y creamos las tablas

Stored Procedures

Son un conjunto de sentencias SQL y sentencias SPL (stored procedure language) agrupadas como un objeto que se almacena en la base de datos en las tablas del sistemaSon chequeadas sintácticamente y optimizadas en el momento de su creación.

Ejemplo

Create procedure Asignar_org (p_uni int,p_cong int);

update congreso set id_universidad = p_uniwhere id_congreso= p_cong;

End procedure;

Sql en un Procedimiento

Sentencias SQL solas

Aplicación Motor DBPasa lasSentencias SQL Las SQL son parseadas,

optimizadas y ejecutadas

Sentencias SQL dentro de un procedimiento

Aplicación Motor DBPasa execute procedure Las SQL son recuperadas y

ejecutadas

Creación de stored procedures

create procedure <nombre> (<nombre_par> <tipo>, ...) returning

<tipo>, ..;............. Cuerpo del procedimiento..end procedure;

Compilación

Un sp se compila cuando se ejecuta la sentencia create procedure

Las sentencias son chequeadas sintácticamente y optimizadasSe genera una lista de dependencias para el chequeo en ejecuciónSe genera código intermedio binario para la rápida ejecución.Se guarda el código, la lista de dependencias e información del sp en un tabla del sistema

Ejecución

Un sp se ejecuta mediante la sentencia execute procedure

Se extrae de las tablas de catálogo el código, la lista de dependencias y los atributos del procedimientoSe evalúan los parámetros en entrada.Se chequea la lista de dependencias para las sentencias dentro del procedimiento, y si lo necesita hace una reoptimizaciónEl intérprete ejecuta el procedimiento

Ejecución de los stored proceduresExecute procedure <nombre> ( <par_valor>,...);

La salida va ser en forma de tabla, cada columna se corresponde con un valor del retorno, como si hiciéramos un select

Ventajas

Reducir la complejidad de las aplicaciones que usan la base de datosSeparar la lógica del programa de la interfaz del usuarioDiferentes aplicaciones pueden compartir código Mejorar la performance

Ventajas

Agregar un nivel extra de seguridadEn un entorno de cliente/servidor no es necesario distribuir código en muchos clientes. El código es único.

Características del SPL

Poseen un lenguaje procedimental que provee sentencias de loop y condicionalesAsí como también el empleo de variables. Maneja métodos de programación de alto nivel como son las excepciones

Uso de variables

Todas las variables en sp deben ser definidasLas variables pasadas por parámetro se definen en el create procedureLas otras son definidas con la sentencia Define

Todos los tipos de datos excepto serial y blob pueden ser usadosSe puede usar la cláusula Like para el mismo tipo de dato de una columna de una tablaNo existen estructuras de datos

Ejemplo

Create procedure proc1 (var1 int,var2 char(2))

returning int;Define var3 varchar(100);Define var4 like congresos.id_cong;....

return var1;End Procedure

Asignación

Si una variable no está asignada, se le dá el valor por defectoSe usa la palabra reservada LET seguida de cualquier expresión sql

Let a=10; Let b=a+1;Let a = (select colA from table1 where colA=10);Let a = proc_name();Let a = c ||d;

Bloque de sentencias

Create procedure show_var()returning integer;Define var1 integer;Let var1=1;Begin

define var1 integer;let var1=2;

EndReturn var1;

End procedure;

BloqueExplícito

BloqueImplícito

Qué retorna ?

Sentencia IF

If exists (select id_cong from congresoswhere id_cong = 1) then

...Elif ...Else...End if;If nombre_universidad matches "A*" then

...End if;

Loop Foreach

Es una sentencia que declara y abre un cursor. Foreach select id_cong into v_cong

from congresos...End foreach;Foreach execute procedure proc1 (var1,var2)

into v_varyEnd foreach;

Sentencias de LOOP

While ... End While;For i=1 to n step m ... End for;Se puede salir del For, ForEach y delWhile con la sentencia exitSe puede saltear el resto de lassentencias y continuar con la próximaiteración con la sentencia continue

Otras ...

Permite recursiónNo tiene límite en el nivel de anidamiento

Permite debugear los procedimientosSet debug file to "/tmp/traza.txt";Trace on;Trace var;Trace off;

Permite el manejo de excepciones

Qué es un Trigger?

Evento Acción

INSERTUPDATEDELETE

Tabla asociada

INSERTUPDATEDELETE

Execute procedures

Triggers

TriggersUn trigger es un mecanismo de la base de datos para ejecutar automáticamente una sentencia SQL cuando ocurre un evento.Una tabla sólo pueden tener trigger de INSERT o DELETE, y mas de uno de UPDATE mientras que las columnas intervenientes en el update sean disjuntas.

Cuándo usar Triggers?

Reglas de consistencia (no provistas por el modelo relacional)Replicación de datosAuditoríaAcciones en cascadaAutorización de seguridad

Ejecución

La ejecución se realiza cuando una tabla a la que esta asociada el trigger genera un evento, ya sea de inserción, eliminación o actualización.Los triggers están almacenados en una tabla de catálogo del sistema como parte de la propiedades de la tablaSon optimizados antes de la ejecución

Componentes del create trigger

Create trigger Nombre<Evento del trigger> tabla<Accion del Trigger><Nombre correlacionado>

Eventos del TriggerCreate trigger <nombre> insert on <nombre_tabla>Create trigger <nombre> delete on <nombre_tabla>Create trigger <nombre> update on <nombre_tabla>Create trigger <nombre> update of <nombre_columna> on <nombre_tabla>

Acciones del Trigger

before (execute procedure proc1(..))Ejecutado antes que las filas sean procesadas

for each row (execute procedure proc1(..))Ejecutado después que cada fila sea procesada

after (execute procedure <proc1(..))Ejecutado después que todas las filas sean

procesadas

Cláusula Referencing

Permite referenciar a los valores de las columnas dentro de una acciónReferencing new as post old as preAsí las columnas pueden referenciarse comoPre.column_nameProst.column_name

Si un trigger falla

En bases de datos con no logging, no ocurre rollback.

⌧Puede dejar a la base de datos en un estado inconsistente

En bases de datos con logging, ocurre un rollback automático del evento y de la acción.

Ejemplo de auditoria

-- DROP TRIGGER tu_envios ;CREATE TRIGGER tu_envios UPDATE on enviosreferencing old as vieja new as nuevaFOR EACH ROW (

INSERT INTO log_envios (id_congreso, id_trabajo, fecha, usuario, fecha_act, oper)

VALUES ( nueva.id_congreso,nueva.id_trabajo,nueva.fecha,User,today,'U');

);-- ********** Fin del trigger de Update tu_envios **********

Ejemplo

Queremos modelar la restricción del sistema de congresos (que no se puedemodelar con ER) de no permitir que un evaluador sea el que escribió el trabajo.

Vamos a asociarlo al trigger de insert de la tabla evalua.Vamos a hacer un sp que controle esta restricción.

Trigger

Create Trigger Ti_Evalua Insert on Evaluareferencing new as nuevafor each row (execute procedure

spi_evalua(nueva.id_trabajo,nueva.id_investigador,nueva.id_congreso));

Stored Procedure

Create procedure spi_evalua (p_idTrabajo like evalua.id_trabajo,p_idInv like evalua.id_investigador,

p_idCongreso like evalua.id_congreso)

if (select count(*) from escrito where id_investigador = p_idInv and id_trabajo=p_idTrabajo)) <> 0 then

raise exception -746,0, "El investigador escribio el trabajo"end if;

end procedure;

Pasar archivos al servidor

Guardar el archivo con extensión .sqlHacer en la máquina local

ftp tulkasusername grupo.., pass ..poner binput <nombre del archivo>.sql