Stored Procedures Firebird 1 En las tablas se

Stored Procedures Firebird 1

En las tablas se guardan los datos En los stored procedures y en los triggers se procesan los datos Con las vistas se consultan los datos 2

Los stored procedures (procedimientos almacenados) son los equivalentes a las rutinas, procedimientos, o funciones disponibles en casi todos lenguajes de programación , allí se utilizan los datos que se encuentran en las tablas, se realizan operaciones aritméticas o lógicas sobre esos datos y se devuelve el resultado de ese procesamiento. 3

Los stored procedures pueden recibir cero, uno, o varios parámetros de entrada y devolver cero, uno, o varios parámetros de salida. 4

Parámetros de salida de un procedure de ejecución execute procedure miproc(lista parametros) returning_values(lista de variables de output) ejemplo: execute procedure miproc('hola firebird') returning_values(: v. Retorno) 5

En Firebird hay dos clases de stored procedures: Ejecutables Seleccionables 6

Los stored procedures ejecutables son llamados de manera similar a rutinas, procedimientos o funciones en los lenguajes de programación, por ejemplo: EXECUTE PROCEDURE Mi. Stored. Procedure. Ejecutable(123, ‘Firebird me gusta’) Los stored procedures ejecutables pueden devolver cero filas o una fila, jamás pueden devolver más de una fila. 7

8

Los stored procedures seleccionables en cambio son utilizados como si fueran tablas en un SELECT, por ejemplo: SELECT * FROM Mi. Stored. Procedure. Seleccionable(123, ‘Firebird me gusta’) Los stored procedures seleccionables pueden devolver cero filas, una fila, o muchas filas 9

Cuando finaliza un stored procedure ejecutable devuelve todos los parámetros de salida. Todos esos parámetros de salida son devueltos en una fila y solamente en una fila, jamás en más de una fila 10

Cada vez que un stored procedure seleccionable ejecuta el comando SUSPEND le devuelve al SELECT que lo llamó todos los parámetros de salida, con los valores que tienen asignados en ese momento. O sea que devuelve una fila cada vez que encuentra el comando SUSPEND. 11

Ejemplo de un stored procedure ejecutable: CREATE PROCEDURE EXISTE_NUMERO_DOCUMENTO ( Nro. Doc TYPE OF COLUMN VENTAS. NRODOC ) RETURNS( Documento. Existe TYPE OF D_BOOLEAN) AS DECLARE VARIABLE lc. Nro. Doc TYPE OF COLUMN VENTAS. NRODOC; BEGIN SELECT COALESCE(V. NRODOC, '') FROM VENTAS V WHERE V. NRODOC = : Nro. Doc INTO : lc. Nro. Doc; ftc. Documento. Existe = 'F'; IF (Nro. Doc = lc. Nro. Doc) THEN Documento. Existe = 'V'; END; 12

Ejemplo de un stored procedure seleccionable CREATE PROCEDURE PRODUCTOS_CLASIFICADOS () RETURNS ( Resultado SMALLINT ) AS DECLARE VARIABLE ln. Pre. Vta TYPE OF COLUMN PRODUCTOS. PREVTA; BEGIN FOR SELECT PREVTA FROM PRODUCTOS INTO : ln. Pre. Vta DO BEGIN IF (ln. Pre. Vta < 1000) THEN Resultado = 1; IF (ln. Pre. Vta >= 1000 AND ln. Pre. Vta <= 50000) THEN Resultado = 2; IF (ln. Pre. Vta > 50000) THEN Resultado = 3; SUSPEND; END 13

Ejecutar un store procedure seleccionable SELECT * FROM PRODUCTOS_CLASIFICADOS 14

Resumiendo: Los stored procedures se utilizan para procesar datos Hay dos clases de stored procedures: Ejecutables Seleccionables 15

Los stored procedures ejecutables son llamados con EXECUTE PROCEDURE y pueden devolver cero filas o una fila. Los stored procedures seleccionables son llamados con SELECT y pueden devolver cero filas, una fila o muchas filas. 16

Los stored procedures ejecutables cuando finalizan devuelven los parámetros de salida en una fila. Los stored procedures seleccionables devuelven los parámetros de salida cada vez que encuentran el comando SUSPEND. Cuando finaliza un stored procedure ejecutable devuelve sus parámetros de salida, con los valores que tienen en ese momento. Cada vez que un stored procedure seleccionable encuentra al comando SUSPEND devuelve sus parámetros de salida con los valores que tienen en ese momento 17

Triggers Firebird 18

Los triggers (disparador en castellano) son rutinas, que se ejecutan en forma automática cuando se cumple alguna de estas condiciones: Se está insertando una nueva fila Se está borrando una fila Se está actualizando una fila 19

Se usan para Asegurarnos que en la tabla se guarden datos válidos y solamente datos válidos. Para actualizar otra tabla cuyos datos dependen de esta tabla 20

Los triggers se “disparan” automáticamente: Antes de la inserción, borrado, actualización Después de la inserción, borrado, actualización 21

CONSIDERACIONES Los triggers no pueden ejecutarse con un "execute trigger“ Los triggers pueden ejecutar procedimientos de selección o de ejecución Los procedimientos no pueden ejecutar un trigger en forma directa, pero si pueden insertar, borrar o actualizar una o mas tuplas y ello provocaría la ejecución automática del trigger Un trigger se dispara 1 vez por cada tupla borrada, insertada o actualizada 22

Los triggers que se disparan antes sirven para evitar que la tabla tenga datos inválidos. Los triggers que se disparan después sirven para actualizar otras tablas cuyos datos dependen de esta tabla. Lo peor que le podría ocurrir a una tabla es tener datos inválidos o incorrectos dentro suyo. 23

En los triggers se pueden usar dos "alias" o seudónimos de tupla llamadas NEW y OLD NEW nos indica el valor que tiene una columna antes de ser insertada en la tabla. OLD nos indica el valor que tiene actualmente la columna en la tabla. O sea el valor que está grabado en ella. NEW y OLD son útiles para comparar el valor queremos introducir con el valor que ya está guardado en una columna. 24

Por ejemplo Para detectar si el nuevo precio de venta es menor que el actual precio de venta IF (NEW. PRD_PREVTA < OLD. PRD_PREVTA) THEN 25

Para detectar si el nuevo precio de venta es cero o menor que cero podríamos escribir: IF (NEW. PRD_PREVTA <= 0) THEN 26

Para detectar si el nombre del producto está vacío IF (CHAR_LENGTH(TRIM(NEW. PRD_NOMBRE)) = 0) THEN 27

Para detectar si el código del producto es NULL IF (NEW. PRD_CODIGO IS NULL) THEN 28

En el caso de encontrar un error se debería generar una EXCEPCIÓN y así la fila no se insertará, ni se borrará, ni se actualizará. 29

Una tabla puede tener muchos triggers, el orden en que se ejecutan viene dado por el valor de POSITION. El primer trigger que se ejecuta es el que tiene POSITION 0, luego se ejecuta el que tiene POSITION 1, luego el que tiene POSITION 2, etc. 30

Orden de ejecucion de los triggers en una transacción: a) trigger de before b) se aplican / ejecutan todos los constraints (reglas de integridad declarativas) primary key, foreign key, domain, check, unique c) trigger de after si a) ok entonces b) si b) ok entonces c) si c) ok entonces transacción ok. dentro de a) y b) el orden de ejecucion de triggers es acorde con position, si position no se indica, no puede determinarse un orden de ejecucion (se van a ejecutar pero no se asegura un orden determinado). 31

Resumiendo: Los triggers son código que se ejecuta automáticamente cuando se quiere: Insertar una nueva fila Borrar una fila Actualizar una fila 32

Sirven para asegurar que en una tabla solamente se introduzcan datos válidos y para actualizar otras tablas cuyos datos dependen de esta tabla Los triggers pueden dispararse: Antes de la inserción, borrado, actualización Después de la inserción, borrado, actualización 33

34

Dentro de los triggers se pueden usar dos alias: NEW y OLD NEW es el valor que tendrá la columna si se realiza la inserción o actualización exitosamente OLD es el valor que actualmente tiene la columna 35

Si se produce un error en la ejecución de un trigger se debe elevar una excepción para que esa fila no sea insertada ni borrada ni actualizada. Una tabla puede tener muchos triggers, el orden en el cual se ejecutan lo determina la variable POSITION 36