Procedimientos Almacenados con InterBase

Por Alfonso Ricaño Bringas En este artículo hablaré de manera introductoria acerca de lo que son y para qué nos pueden servir los procedimientos almacenados en InterBase.

Como sabemos, InterBase es una base de datos completa que nos permite administrar la información y mantenerla íntegra y segura, además tiene algunas características que nos pueden facilitar el desarrollo de nuestras aplicaciones, y en este caso, hablo de los procedimientos almacenados (Stored Procedures, en inglés, abreviados como SP).

Para los que han programado con bases de datos de archivos únicamente (como dBase, FoxPro, Clipper, Paradox, Access) es difícil entender que se puedan ejecutar procedimientos en la base de datos, ya que conciben a la base de datos como únicamente un conjunto de tablas relacionales con información dentro.

Para aprovechar los SP al máximo, primero debemos de entender que el manejador de base de datos es un proceso que se encuentra en el servidor (en este caso, InterBase) y que los SP son instrucciones que pueden ser ejecutadas directamente por el manejador de base de datos sin necesidad de involucrar a los programas cliente (los programas hechos con Delphi, PHP, C++ Builder, por ejemplo).

Así que para utilizar los SP, primero hay que crearlos en la base de datos, mediante un lenguaje de programación que es algo así como una extensión del SQL, y posteriormente ejecutarlos desde el programa cliente.

Los SP son procedimientos que están almacenados dentro de la misma base de datos (en este caso, en el archivo .gdb) y que pueden ejecutar acciones sobre los datos de la base de datos, y que también pueden devolvernos algun resultado.

Para ilustrar el uso de los SP, haré uso de un pequeño ejemplo que nos permita iniciarnos en el uso de los procedimientos, y continuaré despúes con ejemplos cada vez más complejos. En este caso voy a utilizar las tablas de ejemplo que fueron creadas en los artículos Pasando de Paradox a Interbase, en la Parte 2, y en la Parte 3.

Las tablas se llaman Personas y Equipo y tienen la siguiente definición:

Bueno, ahora supongamos que necesitamos un reporte que nos liste las personas, el número de equipos que tiene, y el precio total del equipo que está a su cargo. Quedaría una tabla como esta:

?Cómo resolver este problema con un SP? Bueno, necesitamos crearlo primero, y después accederlo desde nuestro programa cliente como si fuera una tabla más de la base de datos. Para crear un SP, vamos a abrir el IB_WISQL, y nos conectamos a la base de datos, de esta manera:

Oprimimos el botón Browse, y nos sale una ventana como esta:

Nos vamos a la sección que dice Procedures:

Y oprimimos el botón New. Saldrá otra ventana así:

Como vemos, nos escribió una plantilla de código para crear el procedimiento. Este pedazo de código dice así:

CREATE PROCEDURE NEWPROC //( ) /

/RETURNS ( ) AS //DECLARE VARIABLE BEGIN SUSPEND; 

END 

Ahora utilizaremos esta plantilla de código para crear nuestro procedimiento. Lo primero que debemos de hacer es cambiar el nombre del procedimiento por algun nombre representativo, por ejemplo: REPORTE_EQUIPO.

Para esto, debemos cambiar la línea:

CREATE PROCEDURE NEWPROC //( ) por esta:

CREATE PROCEDURE REPORTE_EQUIPO //( ) 

Los paréntesis que están a continuación son opcionales, pues si tenemos parámetros de entrada, ahí se tienen que especificar. Debido a que nuestro SP no usa parámetros de entrada, los quitaremos, al igual que los caracteres //, ya que se utilizan para indicar que son comentarios.

En la parte que dice RETURNS, deberemos quitar igualmente las barras //, para indicar dentro de los paréntesis de RETURNS los campos que va a regresar nuestro SP.

Por lo tanto, deberemos definir los nombres de los campos y los tipos de datos.

Como sabemos, necesitamos un campo para el nombre completo, otro para el número de equipos y otro para el precio total, por lo tanto la parte de returns quedará así:

RETURNS (NOMBRECOMPLETO VARCHAR(81), NUMERO_EQUIPOS INTEGER, PRECIO_TOTAL FLOAT)

A continuación, declararemos las variables que vamos a necesitar, en este caso son cuatro CNT, donde almacenaremos temporalmente el número de equipos, y luego le asignaremos ese valor a la variable de salida NUMERO_EQUIPOS,

CLAVEPER donde almacenaremos la clave de cada persona, NOMBRE1 y APELLIDOS1 para almacenar el nombre y apellidos de cada persona. Entonces las lineas quedarán así: DECLARE VARIABLE CNT INTEGER; DECLARE VARIABLE CLAVEPER INTEGER; DECLARE VARIABLE NOMBRE1 VARCHAR(40); DECLARE VARIABLE APELLIDOS1 VARCHAR(40); Y ya estamos listos para empezar a programar nuestro SP. Después de la instrucción BEGIN, deberemos hacer un select que obtenga todos los registros de la tabla PERSONAS, aunque aquí tendremos que utilizar una instrucción FOR antes del SELECT, de esta manera indicamos una acción que será realizada para cada registro del conjunto de datos obtenido en el SELECT. En este caso, necesitamos las columnas CLAVEPERSONA, NOMBRE y APELLIDOS de la tabla PERSONAS, y deberán guardarse en las variables CLAVEPER, NOMBRE1 y APELLIDOS1.

Por lo tanto la instrucción quedará así:

FOR SELECT CLAVEPERSONA, NOMBRE, APELLIDOS FROM PERSONAS INTO :CLAVEPER, :NOMBRE1, :APELLIDOS1 DO

Nota como se utiliza el FOR para iniciar la instrucción y se termina con DO.

A continuación del DO se puede poner una instrucción o un conjunto de instrucciones (delimitado por un bloque BEGIN END) que serán repetidos para cada elemento del conjunto de datos obtenido con el SELECT. En este caso, queremos asignar a la variable de salida NOMBRECOMPLETO la concatenación de las variables NOMBRE1 y APELLIDOS1, los cuales tendrán los valores de los campos NOMBRE y APELLIDOS para cada registro del SELECT. Esto se hace con esta instrucción: NOMBRECOMPLETO = :NOMBRE1||' '||APELLIDOS1; Ahora necesitaremos hacer un select que nos indique el número de equipos que tiene asignada la persona. SELECT COUNT(*) FROM EQUIPO WHERE CLAVEPERSONA=:CLAVEPER INTO :CNT; Y ahora deberemos de preguntar si CNT es mayor que cero, entonces podremos obtener el Precio total de los equipos, con otro SELECT: IF (:CNT>0) THEN SELECT SUM(PRECIO) FROM EQUIPO WHERE CLAVEPERSONA=:CLAVEPER INTO :PRECIO_TOTAL; ELSE PRECIO_TOTAL=NULL; Finalmente, asignamos el contenido de la variable CNT a la variable de salida NUMERO_EQUIPOS. NUMERO_EQUIPOS = :CNT; La instrucción SUSPEND que ya está escrita en la plantilla, sirve para indicar que se debe de regresar un egistro del SP, con los valores que le asignamos a las variables de salida. Bueno, pues ahora vamos a ver cómo quedó nuestro SP:

 CREATE PROCEDURE REPORTE_EQUIPO 

RETURNS (NOMBRECOMPLETO VARCHAR(81), NUMERO_EQUIPOS INTEGER, PRECIO_TOTAL FLOAT) 

AS 

DECLARE VARIABLE CNT INTEGER; 

DECLARE VARIABLE CLAVEPER INTEGER; 

DECLARE VARIABLE NOMBRE1 VARCHAR(40); 

DECLARE VARIABLE APELLIDOS1 VARCHAR(40); 

BEGIN 

FOR SELECT CLAVEPERSONA, NOMBRE, APELLIDOS FROM PERSONAS INTO :CLAVEPER, :NOMBRE1, :APELLIDOS1 DO BEGIN 

NOMBRECOMPLETO = :NOMBRE1||' '||APELLIDOS1; 

SELECT COUNT(*) FROM EQUIPO WHERE CLAVEPERSONA=:CLAVEPER INTO :CNT; 

IF (:CNT>0) THEN 

    SELECT SUM(PRECIO) FROM EQUIPO WHERE CLAVEPERSONA=:CLAVEPER INTO :PRECIO_TOTAL; 

  ELSE PRECIO_TOTAL=NULL; 

NUMERO_EQUIPOS = :CNT; SUSPEND; 

END 

END 

Para crearlo, oprimimos el botón de la parte superior de la ventana, y después confirmamos la transacción con el botón: Finalmente, podemos acceder a nuestro SP como si fuera una tabla, de esta manera: SELECT * FROM REPORTE_EQUIPO Como vemos, los SP nos pueden ahorrar código y una de sus virtudes consiste en que no utilizamos transferencia para hacer el proceso, sino únicamente para devolver los resultados. También aprovechamos al máximo la velocidad del servidor, ya que es en éste donde se ejecutan los procedimientos.