Stored Procedures Triggers - Principalcs.uns.edu.ar/~gis/ebd/Archivos/clases...
-
Upload
truongnhan -
Category
Documents
-
view
231 -
download
0
Transcript of Stored Procedures Triggers - Principalcs.uns.edu.ar/~gis/ebd/Archivos/clases...
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;