PDF de programación - Laboratorio Informix - Stored Procedures - Triggers

Laboratorio Informix

Stored Procedures
Triggers

Repaso

A partir del ER, hicimos las tablas de la

base de datos.

Creamos en MySql las base de datos y las

tablas

Hicimos una aplicación que se comunica

con el motor de la BD y usamos la BD.
Nos comunicamos directamente con el

motor 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 sistema

Son 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_uni
where id_congreso= p_cong;

End procedure;

Sql en un Procedimiento

Sentencias SQL solas

Aplicación

Motor DB

Pasa las
Sentencias SQL Las SQL son parseadas,
optimizadas y ejecutadas

Sentencias SQL dentro de un procedimiento

Aplicación

Motor DB

Pasa 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

optimizadas

Se genera una lista de dependencias para el

chequeo en ejecución

Se 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 procedimiento

Se 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ón

El intérprete ejecuta el procedimiento

Ejecución de los stored
procedures
Execute 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 datos

Separar la lógica del programa de la

interfaz del usuario

Diferentes aplicaciones pueden compartir

código

Mejorar la performance

Ventajas

Agregar un nivel extra de seguridad
En 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 condicionales
Así 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 definidas

Las variables pasadas por parámetro se definen en el

create procedure

Las otras son definidas con la sentencia Define

Todos los tipos de datos excepto serial y blob

pueden ser usados

Se puede usar la cláusula Like para el mismo

tipo de dato de una columna de una tabla

No 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 defecto

Se 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;

Bloque
Explícito

End
Return var1;
End procedure;

Qué retorna ?

Bloque
Implícito

Sentencia IF

If exists (select id_cong from congresos
where 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_vary

End foreach;

Sentencias de LOOP

While ... End While;
For i=1 to n step m ... End for;
Se puede salir del For, ForEach y del

While con la sentencia exit

Se puede saltear el resto de las

sentencias y continuar con la próxima
iteración con la sentencia continue

Otras ...

Permite recursión

No tiene límite en el nivel de anidamiento

Permite debugear los procedimientos

Set debug file to "/tmp/traza.txt";
Trace on;Trace var;
Trace off;

Permite el manejo de excepciones

Qué es un Trigger?

Triggers

Evento

INSERT
UPDATE
DELETE

Acción

INSERT
UPDATE
DELETE

Tabla asociada

Execute procedures

Triggers
Un 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 datos
Auditoría
Acciones en cascada
Autorizació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 tabla

Son optimizados antes de la ejecución

Componentes del create
trigger

Create trigger Nombre

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

Eventos del Trigger
Create 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ón
Referencing new as post old as pre
Así las columnas pueden referenciarse como
Pre.column_name
Prost.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 envios
referencing old as vieja new as nueva
FOR 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 puede
modelar 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 Evalua

referencing new as nueva
for 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 .sql
Hacer en la máquina local

ftp tulkas
username grupo.., pass ..
poner bin
put <nombre del archivo>.sql