Unidad 9

1. ACI210 – Bases de Datos Unidad 9 Diseño Físico Procedimientos Almacenados y Disparadores (Triggers)

2. ACI210 – Bases de Datos PROCEDIMIENTOS ALMACENADOS

3. PROC. ALMACENADOS • Un procedimiento almacenado (stored procedure) es una colección de sentencias de Transacciones SQL que se constituye como si se tratase de una función de un lenguaje estructurado (C, Pascal ). Es decir: es posible llamarlo mediante un identificador, puede recibir argumentos y devolver un valor de retorno. • La característica primordial de los procedimientos almacenados es que se optimizan en el momento de su creación.

4. PROC. ALMACENADOS • Cuando se crea un procedimiento almacenado el procesador de consultas del gestor crea una versión del mismo con una cierta estructura normalizada y la almacena en una de las tablas de sistema. • Las siguientes ejecuciones de dicho procedimiento, no necesitarán consumir el tiempo necesario para llevar a cabo este proceso de normalización, con lo que su ejecución será más rápida.

5. PROC. ALMACENADOS • Por otra parte, cuando el procedimiento se ejecuta por vez primera, se produce su compilación y la optimización del acceso del procedimiento a los datos. • Este proceso optimizado se mantiene en memoria para posteriores ejecuciones con el consiguiente ahorro adicional de tiempo y recursos.

6. CREACION DE PROC. ALMACENADOS • Existen dos maneras de crear procedimientos almacenados en SQL SERVER: utilizando la sentencia CREATE PROCEDURE y mediante Enterprise Manager. • Sintaxis CREATE PROCEDURE CREATE PROCEDURE [propietario.] nombre_proc [(lista de parámetros) ] [{FOR REPLICATION} | {WITH RECOMPILE} [{[WITH] | [,]} ENCRYPTION]] AS sentencias SQL

7. EJEMPLO DE PROC. ALMACENADOS CREATE PROCEDURE INSERTA_ALUMNO (@ruta char(10), @noma char(10), @appa char(10), @apma char(10), @fonoa char(10), @codcara int) as BEGIN declare @rr char(10) ; select @rr = @ruta; if (@codcara != 100) begin print 'Inicio de Insercion de Datos'; insert into alumno(rut , nombre , app , apm , fono , codcar) values(@rr, @noma , @appa , @apma , @fonoa , @codcara) select * from alumno end else print 'ERROR AL INGRESAR DATOS!!!!!!!!!!!!!!!'; END

8. ACI210 – Bases de Datos CURSORES

9. CURSORES • Los cursores son una herramienta de SQL que nos permite recorrer el resultado de una consulta SQL y realizar operaciones en cada paso de ésta.En algunos SGDB es posible la abertura de cursores de datos desde el propio entorno de trabajo, para ello se utilizan, normalmente procedimientos almacenados. • La sintaxis para definir un cursor es la siguiente: DECLARE nombre-cursor FOR especificacion-consulta [ORDER BY]

10. CURSORES • Por ejemplo: DECLARE Mi_Cursor FOR SELECT num_emp, nombre, puesto, salario FROM empleados WHERE num_dept = 'informatica' (Este comando es meramente declarativo, simplemente especifica las filas y columnas que se van a recuperar. La consulta se ejecuta cuando se abre o se activa el cursor. La cláusula [ORDER BY] es opcional y especifica una ordenación para las filas del cursor; si no se especifica, la ordenación de las filas es definida el gestor de SGBD.)

11. CURSORES • Para abrir o activar un cursor se utiliza el comando OPEN del SQL, la sintaxis en la siguiente: OPEN nombre-cursor [USING lista-variables] (Al abrir el cursor se evalúa la consulta que aparece en su definición, utilizando los valores actuales de cualquier parámetro referenciado en la consulta, para producir una colección de filas. El puntero se posiciona delante de la primera fila de datos (registro actual), esta sentencia no recupera ninguna fila.)

12. CURSORES • Una vez abierto el cursos se utiliza la cláusula FETCH para recuperar las filas del cursor, la sintaxis es la siguiente: FETCH nombre-cursor INTO lista-variables (Lista - variables son las variables que van a contener los datos recuperados de la fila del cursor, en la definición deben ir separadas por comas. En la lista de variables se deben definir tantas variables como columnas tenga la fila a recuperar.)

13. CURSORES • Para cerrar un cursor se utiliza el comando CLOSE, este comando hace desaparecer el puntero sobre el registro actual. La sintaxis es: CLOSE nombre-cursor • Por último, y para eliminar el cursor se utiliza el comando DROP CURSOR. Su sintaxis es la siguiente: DROP CURSOR nombre-cursor

14. EJEMPLO CURSORES CREATE PROCEDURE EJEMPLO AS BEGIN /*La tabla Cliente tiene estos tres campos: CliCod, CliUser, CliPass */ -- declaramos las variablesdeclare @cod as intdeclare @user as varchar(50)declare @pass as varchar(50) -- declaramos un cursor llamado "CURSORITO". -- El select debe contener sólo los campos a utilizar.declare CURSORITO cursor forselect CliCod, CliUser, CliPass from Cliente

15. EJEMPLO CURSORES open CURSORITO -- Avanzamos un registro y cargamos en las variables los valores -- encontrados en el primer registro fetch next from CURSORITO into @cod, @user, @pass while @@fetch_status = 0begin update Cliente set CliPass= @user where CliCod=@cod -- Avanzamos otro registrofetch next from CURSORITO into @cod, @user, @pass end -- cerramos el cursorclose CURSORITO END

16. ACI210 – Bases de Datos TRIGGERS

17. TRIGGERS • Un Trigger se define de manera muy similar a un procedimiento almacenado, salvo que su ejecución es controlada internamente por el DBMS y se asocia a una “tabla-evento”. • Los eventos son operaciones básicas: • Update (actualización de datos) • Delete (eliminación de datos) • Insert (ingreso de datos)

18. TRIGGERS • Uso de disparadores • Evitar ejecución de transacciones inválidas • Garantizar el cumplimiento de restricciones de integridad y de reglas de negocio • Generar automáticamente valores de columnas derivadas • Mal uso • Para garantizar el cumplimiento de restricciones que puedan ser definidas a nivel de esquema  CHECK • Disparadores recursivos • Gran tamaño  Procedimiento almacenado

19. TRIGGERS Sintaxis CREATE TRIGGER <nombre-trigger> ON <nombre-tabla> FOR [INSERT | DELETE | UPDATE] AS Begin - Sentencias SQL – End Para referenciar los registros (tuplas) sobre las cuales ocurre el evento, se utilizan los prefijos (según corresponda): • Inserted (SQL-SERVER) | :NEW (ORACLE). • Updated (SQL-SERVER) | :NEW (ORACLE). • Deleted (SQL-SERVER) | :NEW (ORACLE).

20. TRIGGERS Ejemplo SQL-SERVER (eliminación en cascada) CREATE TRIGGER t ON Autor FOR DELETE AS Begin Delete Libro from Libro, deleted where deleted.IdAutor = Libro.IdAutor End

21. TRIGGERS Ejemplo ORACLE (eliminación en cascada) CREATE TRIGGER t ON Autor BEFORE DELETE FOR EACH ROW Begin Delete Libro from Libro where :New.IdAutor = :Old.IdAutor End

22. Sobre la creación de Triggers • Los nombres de los triggers deben ser únicos dentro de un esquema dado. • Alguna de las dos, BEFORE o AFTER, debe ser utilizada en el CREATE TRIGGER (ORACLE). • La sentencia activadora especifica el tipo de operación que despierta el disparador (DELETE, INSERT o UPDATE). En la sentencia activadora se especifica la tabla asociada al trigger. Puede especificarse exactamente una tabla (no una vista) en la sentencia activadora.

23. Sobre la creación de Triggers • Si la sentencia activadora especifica un UPDATE se puede incluir una lista de columnas en dicha sentencia. Si se incluye la lista de columnas, el trigger se activa por un UPDATE sólo si una de las columnas especificadas es actualizada. Si se omite la lista, el trigger se activa cuando cualquier columna de la tabla se actualiza. No se puede especificar lista de columnas para INSERT o DELETE. • La presencia o ausencia de la opción FOR EACH ROW (ORACLE), determina si el disparador es a nivel de filas (row trigger) o a nivel de sentencia activadora (statement trigger). Especifica que el cuerpo del trigger se ejecuta individualmente para cada una de las filas de la tabla que haya sido afectada por la sentencia activadora.

24. Sobre la creación de Triggers • Opcionalmente, se pueden incluir restricciones en la definición de un row trigger. Para ello se especifica, en una cláusula WHEN (ORACLE), una expresión booleana de SQL. Si se incluye una cláusula WHEN, la expresión se evalúa para cada una de las filas que el disparador afecta. Si el resultado de la evaluación es TRUE, se ejecuta el cuerpo del trigger sobre la fila que hizo cierta la expresión. La expresión en una cláusula WHEN no puede incluir subqueries. • Los triggers pueden incluir cualquier número y clase de sentencias SQL, excepto sentencias SELECT(dependiendo de cómo se utilice); un trigger no puede devolver datos al usuario. Hay unos pocos nombres especiales que se usan en la sentencia CREATE TRIGGER:

25. Sobre la creación de Triggers • Deleted e Inserted son tablas lógicas (conceptuales). Son estructuralmente como la tabla en la cual es definido el trigger, esto es, la tabla en la cual el usuario efectúa la acción, y guarda los viejos o nuevos valores de las filas que serán modificadas por la acción del usuario. Las tablas Deleted e Inserted pueden ser examinadas por el trigger para determinar si la acción se llevará a cabo. La tabla Deleted es usada con DELETE y UPDATE; la tabla Inserted se usa con INSERT y UPDATE. • En términos de rendimiento, el trigger es, normalmente, muy lento. El tiempo usado en ejecutar un trigger es gastado la mayoría de las veces en referenciar otras tablas, las cuales podrían estar en memoria o en disco.

26. Modificar Triggers • No hay modificación explícita, se reemplaza. 1) CREATE OR REPLACE TRIGGER <Nom_Trigger> 2) DROP TRIGGER <Nom_Trigger> CREATE TRIGGER <Nom_Trigger> • (Des)habilitar 1) ALTER TRIGGER <Nom_Trigger> ENABLE/DISABLE; 2) ALTER TABLE <Nom_Tabla> ENABLE/DISABLE ALL TRIGGERS;

27. Ejemplo de Triggers Ejercicio 1 Para un sistema de Administración de stock en Bodega, se requiere implementar la siguiente funcionalidad activa a través de Triggers: Cada vez que se agregue un registro de detalle de compra, se deberá actualizar en forma automática la existencia en Stock actual. Evento: DetalleCompra.Insert Condición: - Acción: Producto.Update(StockActual)

28. Ejemplo de Triggers • Solución 1 • CREATE TRIGGER ActualizaStockCompra • ON DetalleCompra • FOR INSERT • AS • Begin • Update Producto • Set StockActual = StockActual + inserted.Cantidad • from Producto P • where P.IdProducto = inserted.IdProducto • End

29. Ejemplo de Triggers • Ejercicio 2 • Ampliar el Sistema, agregando registro de mermas (pérdidas) • Cada vez que se registre una merma, se deberá actualizar en forma automática la existencia en Stock actual. • Evento: Merma.Insert • Condición: - • Acción: Producto.Update(StockActual)

30. Ejemplo de Triggers • Solución 2 • CREATE TRIGGER ActualizaStockMerma • ON Merma • FOR INSERT • AS • Begin • Update Producto • Set StockActual = StockActual - inserted.Cantidad • from Producto P • where P.IdProducto = inserted.IdProducto • End

31. Ejercicio de Triggers en Laboratorio • Ejercicio 3 – Tarea Laboratorio • Autómata de Giro Bancario • Cada vez que un cliente quiere hacer un giro, el sistema deberá verificar que su saldo de cuenta corriente o en su defecto su cupo de línea de crédito sea suficiente para cubrir la operación.Si la suma del saldo disponible en la cuenta y el saldo de la línea no logra cubrir, se deberá anular la transacción, en caso contrario, se deberá registrar el nuevo saldo y/o nuevo cupo de línea de crédito y detalle de la utilización de la línea.

32. ANEXO - EJEMPLOS create table pruebasinsertar(id int null,fecha datetime, texto char(10)) gocreate trigger trg_PruebasInsertar on PruebasInsertarINSTEAD OF insert   as  begin-- Vamos a poner un bloqueo de aplicación, para evitarnos problemas    -- de concurrencia, Naturalmente esto puede afectar al rendimiento y    -- en un caso real, habría que dedicar tiempo a investigar si es realmente    -- necesario. Como ejemplo didactico sin embargo, me parece muy conveniente.    -- Aprovecharemos que estamos seguro dentro de una transaccion.     exec sp_getApplock 'InsertandoEnPruebasInsertar','Exclusive'    -- Necesitamos saber el máximo..    Declare @Valor int    select @valor=isnull(max(id),0) from PruebasInsertar

33. ANEXO – EJEMPLOS TRIGGERS   -- creamos una tabla para numerar todos los nuevos registros...    select * into #insertados from inserted     -- Actualizamos para tener el  número en el campo id     update #insertados set id=@valor,@valor=@valor+1 insert into PruebasInsertar      Select id,Fecha,texto from #insertados -- despues de insertar los registros liberamos los bloqueos..exec sp_releaseApplock 'InsertandoEnPruebasInsertar'endgo -- Probemos el funcionamiento del trigger...insert into pruebasinsertar(fecha ,texto ) select orderdate,customerid from northwind..ordersgo

34. ANEXO – EJEMPLOS CURSORES 'Abrir un cursor y recorrelo DECLARE Employee_Cursor CURSOR FOR SELECT LastName, FirstName FROM Northwind.dbo.Employees WHERE LastName like 'B%' OPEN Employee_Cursor FETCH NEXT FROM Employee_Cursor WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Employee_CursorEND CLOSE Employee_Cursor DEALLOCATE Employee_Cursor