Las bases de datos del Firebird sirven para guardar datos en ellas, para procesar dichos datos y para consultarlos cuando se los necesita.

  • 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

Los stored procedures (procedimientos almacenados) son los equivalentes a las rutinas, procedimientos, o funciones disponibles en casi todos los lenguajes de programación (Visual FoxPro, Visual Basic, C, Delphi, Java, etc.), 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.

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

En Firebird hay dos clases de stored procedures:

  • Ejecutables
  • Seleccionables

Los stored procedures ejecutables son llamados de manera similar a como se llaman las rutinas, procedimientos o funciones en los lenguajes de programación, por ejemplo:

EXECUTE PROCEDURE MiStoredProcedureEjecutable(123, ‘Firebird me gusta’)

Los stored procedures seleccionables en cambio son utilizados como si fueran tablas en un SELECT, por ejemplo:

SELECT * FROM MiStoredProcedureSeleccionable(123, ‘Firebird me gusta’)

La forma de escribirlos es casi la misma. Las únicas diferencias son:

  • Los stored procedures ejecutables pueden devolver cero filas o una fila, jamás pueden devolver más de una fila
  • Los stored procedures seleccionables pueden devolver cero filas, una fila, o muchas filas
  • 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
  • Cada vez que un stored procedure seleccionable encuentra 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.

Veamos un ejemplo de un stored procedure ejecutable:

CREATE PROCEDURE EXISTE_NUMERO_DOCUMENTO(
   tcNroDoc TYPE OF COLUMN VENTASCAB.VTC_NRODOC)
RETURNS(
   ftcDocumentoExiste TYPE OF D_BOOLEAN)
AS
   DECLARE VARIABLE lcNroDoc TYPE OF COLUMN VENTASCAB.VTC_NRODOC;
BEGIN

   SELECT
      COALESCE(V.VTC_NRODOC, '')     -- Si VTC_NRODOC es nulo, devuelve una cadena vacía
   FROM
      VENTASCAB V
   WHERE
      V.VTC_NRODOC = :tcNroDoc
   INTO
      :lcNroDoc;

   ftcDocumentoExiste = 'F';

   IF (tcNroDoc = lcNroDoc) THEN
      ftcDocumentoExiste = 'V';

END;
  • Este stored procedure se llama EXISTE_NUMERO_DOCUMENTO
  • Recibe un parámetro de entrada llamado tcNroDoc cuyo tipo de datos es el mismo que tiene la columna VTC_NRODOC de la tabla VENTASCAB
  • Devuelve un parámetro de salida llamado ftcDocumentoExiste, el cual es de tipo D_BOOLEAN y por lo tanto puede valer «V» o «F»
  • Usa una variable que es local al stored procedure y cuyo nombre es lcNroDoc y su tipo también es igual al de la columna VTC_NRODOC
  • Busca en la tabla VENTASCAB un documento cuyo número sea igual al del parámetro de entrada (tcNroDoc) y guarda el resultado en la variable lcNroDoc
  • Supone que el documento que se está buscando no existe
  • Compara el parámetro de entrada tcNroDoc con el resultado de la búsqueda que se guardó en la variable lcNroDoc. Si son iguales eso significa que se encontró el número de documento buscado
  • Al terminar el stored procedure se devuelve el valor de la variable ftcDocumentoExiste (que puede ser o «V» o «F») ya que ftcDocumentoExiste es el parámetro de salida

Veamos ahora un ejemplo de un stored procedure seleccionable:

CREATE PROCEDURE PRODUCTOS_CLASIFICADOS
  RETURNS(tnResultado SMALLINT)
AS
  DECLARE VARIABLE lnPreVta TYPE OF COLUMN PRODUCTOS.PRD_PREVTA;
BEGIN
   FOR SELECT 
      PRD_PREVTA 
   FROM 
      PRODUCTOS 
   INTO 
      :lnPreVta 
   DO BEGIN
      IF (lnPreVta < 1000) THEN
         tnResultado = 1;
      IF (lnPreVta >= 1000 AND lnPreVta <= 50000) THEN
         tnResultado = 2;
      IF (lnPreVta > 50000) THEN
         tnResultado = 3;
      SUSPEND;
   END
END

Este stored procedure lo que hace es clasificar a los productos según sus precios de venta: si el precio de venta es menor que 1.000, devuelve 1. Si el precio de venta está entre 1.000 y 50.000 devuelve 2. Si el precio de venta es mayor que 50.000 devuelve 3.

Cada vez que se llega al SUSPEND el parámetro de salida tnResultado se devuelve al SELECT para que éste lo muestre. Después que se le entregó al  SELECT el parámetro de salida tnResultado se procesa la siguiente fila, y así hasta procesarlas a todas las filas. Al SELECT se le devolverá una fila cada vez que se encuentre un SUSPEND. Entonces, si la tabla PRODUCTOS tiene 1.800 filas, este stored procedure seleccionable devolverá 1.800 filas.

SELECT
   *
FROM
   PRODUCTOS_CLASIFICADOS

Aunque PRODUCTOS_CLASIFICADOS es un stored procedure se lo utiliza como si fuera una tabla. ¿Por qué? porque es un stored procedure seleccionable ¿Y cómo sabemos que es un stored procedure seleccionable? Porque tiene el comando SUSPEND dentro suyo.

Resumiendo:

  • Los stored procedures se utilizan para procesar datos
  • Hay dos clases de stored procedures:
    • Ejecutables
    • Seleccionables
  • 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
  • La forma de escribirlos es casi la misma, la diferencia es que en los stored procedures ejecutables cuando finalizan devuelven los parámetros de salida, en cambio 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

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21