PDF de programación - Tema 6 FIREBIRD: SQL PROCEDIMENTAL (PSQL)

Firebird: SQL Procedimental (PSQL) Tema 6



FIREBIRD:

SQL

PROCEDIMENTAL

(PSQL)



Vicente Tejero Trueba Pag 1 IES Pedro Espinosa

Firebird: SQL Procedimental (PSQL) Tema 6

FIREBIRD: SQL PROCEDIMENTAL (PSQL)


1.- INTRODUCCION..............................................................................................................................1
2.- EXTENSIONES DEL LENGUAJE...................................................................................................2
2.1.- Terminadores...............................................................................................................................2
2.2.- Variables......................................................................................................................................3
2.3.- Sentencias básicas........................................................................................................................4
2.4.- Cursores.......................................................................................................................................5
2.5.- Sentencias control de flujo..........................................................................................................8
2.6.- Ejecución de código.....................................................................................................................9
2.7.- Eventos........................................................................................................................................9
3.- PROCEDIMIENTOS ALMACENADOS........................................................................................11
4.- TRIGGERS.......................................................................................................................................13
5.- EXCEPCIONES...............................................................................................................................16



Vicente Tejero Trueba Pag i IES Pedro Espinosa

Firebird: SQL Procedimental (PSQL) Tema 6

1.- INTRODUCCION
Uno de los grandes beneficios de la implementación de SQL en Firebird es la posibilidad de
compilar y ejecutar modulos de código interno (procedimientos almacenados y triggers) desarrollados
por un programador. Para ello se tiene PSQL, una sintaxis que combina sentencias de DML con una
extensión para programación.

Se habla en este caso de programación en el lado del servidor, ya que el código se ejecutará en
la máquina del servidor por el propio servidor. Esto tiene sus ventajas:

- Diseño modular: Todas las aplicaciones que acceden a la misma base de datos comparten

los procedimientos almacenados y por tanto se centralizan las reglas de empresa, se
reutiliza código y se reduce el tamaño de las aplicaciones.

- Facilidad en mantenimiento: Cuando se realiza un cambio en un procedimiento, éste se

traslada de forma automática a todas las aplicaciones que lo usan.

- Mejora de eficiencia: El procesado complejo se realiza en el servidor reduciendo el trafico

de red y la carga en los clientes.

- Economía en la arquitectura: Las aplicaciones clientes se pueden orientar hacia la captura

de datos y tareas interactivas mientas el trabajo complejo con datos se realiza en el
servidor.

- Funcionalidad extra: Operaciones que no se pueden realizar fácilmente mediante sentencias

SQL pueden implementarse mediante procedimientos almacenados.


Cada módulo creado podrá ser:


- Procedimiento almacenado : Se pueden utilizar como procedimientos seleccionables

(sustituyen a una tabla o vista en una sentencia SELECT) o como procedimientos
ejecutables (ejecutar una operación o conjunto de operaciones en el lado del servidor) que
se pueden llamar mediante EXECUTE PROCEDURE o desde otro procedimiento o trigger.


- Trigger: Es una rutina asociada a una tabla o vista que se lanza de forma automática
cuando se realiza una operación de inserción, borrado o actualización de una fila. Un
trigger nunca se llama directamente.

Se suelen usar los triggers para realizar actualizaciones de datos ante cambios en filas,
validar datos de entrada, transformaciones de datos, actualizaciones de integridad
referencial en cascada o para hacer vistas de solo lectura actualizables.


Un aspecto importante a tener en cuenta con los módulos de PSQL es que no aportan control de
transacciones. Un módulo se ejecuta siempre en el ámbito de una transacción abierta previamente.
Desde el módulo no puede ser confirmada ni cancelada, lo único que se puede es lanzar una excepción
ante un problema detectado. La aplicación que ha llamado al módulo será la encargada de realizar la
finalización apropiada de la transacción (confirmar o deshacer).

En este tema veremos los ejemplos a partir de la base de datos de bancos:

Vicente Tejero Trueba Pag 1 IES Pedro Espinosa

Firebird: SQL Procedimental (PSQL) Tema 6



2.- EXTENSIONES DEL LENGUAJE.
Los módulos PSQL no son más que sentencias SQL que comienzan con una cláusula CREATE
y finalizan con un terminador. En la definición del módulo apareceran un número variable de
elementos como clausulas, palabras clave, bloques de sentencias, bucles, etc.

Un módulo de PSQL se define estableciendo dos elementos fundamentales:

- La cabecera: En ella se incluyen todos los elementos de definición del procedimiento o

trigger como nombre del módulo, parámetros de entrada y/o salida para los procedimientos,
tipo de trigger, etc.

- El cuerpo : En el se definen todos los elementos que desarrollan el módulo como la

declaración de variables, el bloque de sentencias (entre el BEGIN END) y los manejadores
de excepción.


Así tendríamos que en un procedimiento:

-- se inicia la cabecera
CREATE PROCEDURE procedimiento (entrada INTEGER) RETURNS (salida INTEGER)
--se acaba la cabecera
AS -- se inicia el cuerpo
DECLARE VARIABLE variable INT;
BEGIN
variable = entrada /2;
salida =variable;
END
-- fin del cuerpo



2.1.- Terminadores.
Muchos de los elementos que nos encontramos en un módulo se deben indicar con el
terminador ;. Ya que un módulo es una sóla sentencia finalizada con un terminador es necesario
diferenciar éste de los elementos del módulo. Esto se consigue mediante la sentencia SET TERM. Por
ejemplo una definición tipica de un procedimiento sería:


SET TERM ^; -- establece como terminador el ^ a partir de esta sentencia.
CREATE PROCEDURE MI_PROCEDIMIENTO
…….
AS
BEGIN

Vicente Tejero Trueba Pag 2 IES Pedro Espinosa

Firebird: SQL Procedimental (PSQL) Tema 6

sentencia; --sentencia acabada en ;
END ^ -- finaliza la definición del procedimiento mediante el terminador indicado, el ^

SET TERM ;^ -- fija de nuevo el terminador como ; y acaba la sentencia con el terminador actual ^



2.2.- Variables.
En PSQL se pueden usar 5 tipos de variables en el cuerpo de un módulo, con algunas
restricciones según sea procedimiento o trigger:

- Variables locales, usadas para mantener valores locales en un módulo.
- Las variables NEW.columna y OLD.columna usadas en los triggers para almacenar los

valores modificados de la fila o los antiguos.

- Variables de contexto.
- Argumentos de entrada pasados como constantes en los procedimientos almacenados.
- Argumentos de salida, usados para devolver valores en los procedimientos almacenados.


Las variables pueden usarse en sentencias SQL dentro de los módulos aunque en este caso se
preceden por ‘:’ para diferenciarlas de las columnas de las tablas.


……
FOR SELECT cod_accta FROM ACCIONISTAS
INTO :variable DO -- Variable lleva : al estar dentro de un select.
BEGIN
otra = otra + variable – variable no lleva los : al no aparecer en una sentencia select.
END
……



Una variable se
variable cualquier expresión en la que usemos variables, operadores, funciones SQL.


asigna mediante el = ( variable = expresión), en donde se puede asignar a una

codigo = GEN_ID(‘generador’,1);
codigo = codigo + IIF(codigo<0,0,codigo);


Las variables locales se declaran, una por una, antes del primer BEGIN siendo su ámbito
únicamente el cuerpo del procedimiento o trigger. En la misma sentencia es posible darles un valor:


DECLARE VARIABLE variable
{<tipo>
| <dominio>
| TYPE OF <dominio>
| TYPE OF COLUMN <tabla o vista>.<columna>}
[{DEFAULT valor } | {= valor}];

DECLARE VARIABLE variable INTEGER DEFAULT 0;
DECLARE VARIABLE cadena CHAR(20) = ‘hola’;



En PSQL se pueden usar todas las variables de contexto y literales comentados en temas
anteriores como ‘NOW’, CURRENT_USER, etc. Además se define la variable ROW_COUNT que
contiene el número de filas afectadas por la última sentencia de DML ejecutada.

Los
aplicación pueda pasar valores al mismo. Son declarados en la cabecera pudiéndose usar en cualquier
punto del cuerpo. Los argumentos de entrada son siempre pasados por valor, por lo que cualquier
cambio que se produzca dentro del cuerpo no se envía al exterior.

argumentos de entrada se definen en los procedimientos almacenados para que la

Vicente Tejero Trueba Pag 3 IES Pedro Espinosa

Firebird: SQL Procedimental (PSQL) Tema 6
A los argumentos de entrada se les puede asignar un valor por defecto lo que hace que se pueda
omitir en la llamada al procedimiento. En este caso, una ver definido uno, todos los siguientes deben
tener también un valor por defecto y omitirse en la llamada.

Los
devolver valores a la aplicación que los llama. Se declaran en la cabecera pudiéndose usar en cualquier
punto del cuerpo.


argumentos de salida se definen en los procedimientos almacenados para que puedan

CREATE PROCEDURE procedimiento
[({arg_entrada <tipo> [= valor_defecto]}[, ……] )] – argumentos de entrada.
[RETURNS ({arg_salida <tipo>}[, ……] )] – argumentos de salida.
………



CREATE PROCEDURE procedimiento (entrada INTEGER=0) RETURNS (salida INTEGER)
AS
DEC