Entendiendo a los FOR SELECT

2 comentarios

Si dentro de un stored procedure o de un trigger quieres conocer cuales son los valores que tienen las columnas de una tabla entonces tienes dos formas:

  • Asignación directa
  • Usando un FOR SELECT

La asignación directa es la más conveniente cuando solamente te interesa el valor de una columna, por ejemplo:

lnMiVariable = (SELECT MiColumna FROM MiTabla WHERE MiCondición);

Para que funcione, la asignación directa debe cumplir dos requisitos:

  1. Que el SELECT devuelva una sola columna de una sola fila.
  2. Que el SELECT esté rodeado por paréntesis

Pero si necesitas varias columnas o varias filas entonces la asignación directa no es la mejor alternativa. Podrías usarla y funcionará, pero puede ser excesivamente lenta si las columnas son varias y las filas son muchas (además de que escribirás un montón). Para estos casos existe una mucha mejor alternativa: usar el FOR SELECT.

¿Qué hace el FOR SELECT?

Es muy similar a un SELECT pero siempre y en todos los casos el contenido de las columnas es asignado a variables de memoria. ¿Por qué eso? porque dentro de un stored procedure o de un trigger no se puede directamente usar una columna de una tabla. Entonces, si necesitamos usarla debemos previamente guardarla en una variable de memoria.

Columna —> Variable_de_memoria

Escribiendo un FOR SELECT

La forma general de escribir un FOR SELECT es la siguiente:

FOR SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla
INTO
   :MiVariable1,
   :MiVariable2,
   :MiVariable3
DO BEGIN
   -- Aquí se pueden usar las variables
END
-- Aquí también se pueden usar las variables

 Desde luego que en el SELECT puedes usar las cláusulas WHERE, GROUP BY, HAVING, etc.

Lo que se obtiene al ejecutar un FOR SELECT son variables que tienen el mismo valor que las columnas de la tabla. Así, dentro de MiVariable1 tendremos el valor de MiColumna1, dentro de MiVariable2 tendremos el valor de MiColumna2, etc.

Habrás notado que después de la cláusula INTO se ponen los nombres de las variables prefijados con el símbolo :

¿Por qué eso?

Porque es la forma que tiene el Firebird para saber si te estás refiriendo a una columna de una tabla o a una variable de memoria. En todos los lugares donde puede existir confusión es obligatorio que a las variables de memoria se las prefije con los dos puntos. De esa forma el Firebird podrá saber si quieres usar una columna o una variable de memoria.

Conclusión:

Cuando dentro de un stored procedure o de un trigger necesitamos obtener los valores que tienen las columnas de una tabla podemos hacerlo de dos formas:

  1. Con asignación directa
  2. Escribiendo un FOR SELECT

La asignación directa es la más conveniente cuando solamente nos interesa una columna aislada. Cuando estamos interesados en varias columnas entonces lo más conveniente es usar un FOR SELECT.

Todos los FOR SELECT deben tener una cláusula INTO y a continuación de la cláusula INTO debemos escribir los nombres de las variables de memoria prefijadas con dos puntos.

Se las prefija con dos puntos para que el Firebird pueda saber si nos estamos refiriendo a una columna de una tabla o a una variable de memoria. De esa manera las distingue.

Prefijar a una variable de memoria con los dos puntos solamente es necesario en los lugares donde el Firebird se puede confundir. En los lugares donde no hay confusión posible no es necesario (aunque si las prefijamos, ningún problema).

Artículos relacionados:

Entendiendo a los stored procedures

Entendiendo a los triggers

Teniendo FOR SELECT anidados

El índice del blog Firebird21

 

Escribiendo un stored procedure

10 comentarios

Muchas veces quienes están empezando con Firebird tienen miedo de escribir un stored procedure o no saben como hacerlo. En realidad es muy sencillo, si ya has escrito funciones o rutinas en algún lenguaje de programación (Visual FoxPro, Visual Basic, Delphi, C, C++, Java, etc.) entonces ya sabes casi todo lo que necesitas saber.

Primero, como es lógico, a tu stored procedure deberás asignarle un nombre. ¿Por qué? porque dentro de una Base de Datos pueden existir muchísimos stored procedures y se debe poder diferenciarlos. Para eso sirve el nombre, o sea que no puedes tener dos stored procedures con el mismo nombre.

Un stored procedure puede recibir ningún parámetro de entrada, un parámetro de entrada o muchos parámetros de entrada. Entonces la segunda tarea es determinar el nombre de cada parámetro de entrada y el tipo de datos que le corresponde.

A su vez un stored procedure puede devolver ningún parámetro de salida, un parámetro de salida, o muchos parámetros de salida. Entonces la tercera tarea es determinar el nombre de cada parámetro de salida y el tipo de datos que le corresponde.

A veces un stored procedure necesita usar variables locales, o sea variables que solamente se conocerán dentro de ese stored procedure. Entonces la cuarta tarea es determinar el nombre y el tipo de datos de cada variable local.

Luego empieza el procesamiento. Allí puedes hacer lo siguiente:

  • Asignarle valores a las variables. Sea que se trate de parámetros de entrada, parámetros de salida, o variables locales. A cualquiera de ellas le puedes asignar un valor.
  • Usar un ciclo FOR … SELECT. Este sirve para recorrer todas las filas de una tabla, de una vista, o de un stored procedure seleccionable.
  • Usar la construcción IF … THEN … ELSE.
  • Usar la construcción WHILE … DO
  • Usar funciones internas (tales como: COALESCE(), IIF(), SUBSTRING(), TRIM(), etc.) o funciones externas
  • Usar comandos del Firebird (tales como: INSERT, UPDATE, DELETE, EXECUTE STATEMENT, etc.)
  • Capturar excepciones si quieres manejar los errores que puedan ocurrir
  • Si se trata de un stored procedure seleccionable entonces escribir el comando SUSPEND

Tanto si usas FOR … SELECT, IF … THEN … ELSE, WHILE … DO, tienes dos posibilidades:

  1. Ejecutar solamente una línea
  2. Ejecutar dos o más líneas

Si vas a ejecutar más de una línea entonces debes indicarle al Firebird cuales serán esas líneas y eso lo haces con las palabras BEGIN … END. Si vas a ejecutar solamente una línea, entonces no es necesario escribir (aunque puedes hacerlo si quieres) las palabras BEGIN … END.

Ejemplo 1:

Queremos saber si el número de una Factura existe.

Listado 1.

CREATE PROCEDURE EXISTE_NUMERO_FACTURA(
   tcNumeroFactura  VARCHAR(15))
RETURNS(
   ftcExisteFactura CHAR(1))
AS
   DECLARE VARIABLE lcNroFac VARCHAR(15);
BEGIN

   lcNroFac = (SELECT
                  FAC_NUMERO
               FROM
                  FACTURAS
               WHERE
                  FAC_NUMERO = :tcNumeroFactura);

   ftcExisteFactura = IIF(lcNroFac IS NOT NULL, 'V', 'F');

END;

Este stored procedure recibió un parámetro de entrada llamado tcNumeroFactura que contiene el número de la Factura que queremos saber si existe.

Devolverá el parámetro de salida ftcExisteFactura que contendrá una ‘V’ (significa verdadero) o una ‘F’ (significa falso).

Usa una variable local llamada lcNroFac que contendrá NULL si no se encontró el número de la Factura o contendrá el número de la Factura buscada, si es que se la encontró.

Entonces, para saber si la Factura existe o no, usamos la función interna IIF(), la cual es una forma simplificada de escribir un IF … THEN … ELSE

Si la Factura no existe entonces la variable local lcNroFac será NULL. En cambio si la Factura existe, lcNroFac nunca será NULL sino que contendrá el número de esa Factura. Por lo tanto, para saber si la Factura existe, verificamos si lcNroFac no es NULL. Si no es NULL entonces sí existe.

Cuando se alcanza el último END entonces se devuelven todos los parámetros de salida con el valor que tengan en ese momento. En este caso tenemos un solo parámetro de salida, llamado ftcExisteFactura el cual devolverá una ‘V’ si la Factura existe o una ‘F’ si la Factura no existe.

Para ejecutar este stored procedure escribiremos algo como.

Listado 2.

EXECUTE PROCEDURE EXISTE_NUMERO_FACTURA('001-001-0000007')

Y obtendremos algo como:

SP1

Captura 1. Si haces clic en la imagen la verás más grande

A partir de este momento podemos usar a la variable ftcExisteFactura dentro del lugar que llamó al stored procedure (que puede ser otro stored procedure, un trigger, o nuestro lenguaje de programación).

Ejemplo 2:

Queremos saber si un año es bisiesto.

Listado 3.

CREATE PROCEDURE ES_BISIESTO(
   tnAno SMALLINT)
RETURNS(
   tcBisiesto CHAR(1))
AS
BEGIN

   tcBisiesto = 'F';

   IF ((MOD(tnAno, 4) = 0 AND MOD(tnAno, 100) <> 0) OR MOD(tnAno, 400) = 0) THEN
      tcBisiesto = 'V';

END;

En este caso no hemos usado variables locales. Solamente un parámetro de entrada llamado tnAno que contiene el número de un año y un parámetro de salida llamado tcBisiesto que valdrá ‘V’ si el año es bisiesto o ‘F’ si no lo es.

Entonces, para saber si un año es bisiesto escribiríamos:

Listado 4.

EXECUTE PROCEDURE ES_BISIESTO(2014)

Y si el resultado que obtenemos es una ‘V’ entonces sí es bisiesto, pero si obtenemos una ‘F’ entonces no lo es.

Ejemplo 3:

Queremos escribir un stored procedure para guardar los nombres de nuestros empleados, pero si por algún motivo la grabación falló entonces queremos guardar en una tabla llamada ERRORES el nombre que no pudo ser grabado.

Listado 5.

CREATE PROCEDURE GRABAR_NOMBRES(
   tnIdenti INTEGER,
   tcNombre VARCHAR(20))
AS
BEGIN

   UPDATE OR INSERT INTO NOMBRES
            (NOM_IDENTI, NOM_NOMBRE)
     VALUES (:tnIdenti , :tcNombre ) ;

   WHEN ANY DO
      IN AUTONOMOUS TRANSACTION DO
         INSERT INTO ERRORES
               (ERR_MODULO, ERR_COMENT)
        VALUES ('GRABAR_NOMBRES', 'No se grabó el nombre ' || :tcNombre);

END;

Si el nombre no se grabó, sea por el motivo que sea (un motivo podría ser porque la columna tiene menos caracteres que el nombre que se quiso grabar. Por ejemplo, la columna está definida como VARCHAR(20) y se quiso grabar un nombre de 24 letras. Imposible) entonces ocurrirá una excepción. Capturamos esa excepción (eso es lo que hace la construcción WHEN … DO) y guardamos el nombre problemático en una tabla llamada ERRORES. Para asegurarnos que siempre se realice la grabación en la tabla ERRORES el INSERT lo hacemos dentro de una transacción autónoma o sea una transacción que es independiente a la principal y que hace un COMMIT automático cuando finaliza.

Este stored procedure recibe dos parámetros de entrada: tnIdenti y tcNombre y no devuelve parámetros de salida ni usa variables locales.

Entonces lo que hace es lo siguiente: intenta insertar una fila a la tabla NOMBRES, si no lo consiguió entonces inserta una fila en la tabla ERRORES. Esta segunda inserción nunca falla porque se encuentra dentro de la construcción IN AUTONOMOUS TRANSACTION lo cual nos asegura que siempre se realiza.

Ejemplo 4:

Queremos asignarle en forma automática códigos a los productos que vendemos.

Listado 6.

CREATE PROCEDURE SIGUIENTE_CODIGO
   RETURNS(
      ftcCodigo CHAR(7))
AS
   DECLARE VARIABLE lnNumero INTEGER;
BEGIN

   SELECT
      P.PRD_CODIGO
   FROM
      PRODUCTOS P
   ORDER BY
      P.PRD_CODIGO DESCENDING
   ROWS
      1
   INTO
      :ftcCodigo;

   lnNumero = COALESCE(CAST(ftcCodigo AS INTEGER), 0) + 1;

   ftcCodigo = LPAD(lnNumero, 7, '0');

END;

Este stored procedure hace lo siguiente:

  1. Averigua cual es el último código guardado en la tabla PRODUCTOS
  2. Convierte ese código (que es de tipo carácter) a INTEGER y luego le suma 1
  3. Al número obtenido lo vuelve a convertir a carácter, agregándolo los ceros a la izquierda que sean necesarios para que su longitud sea igual a 7
  4. Devuelve el código que se obtuvo en el paso 3.

SP2

Captura 2. Si haces clic en la imagen la verás más grande

El último código que tenemos grabado en la tabla PRODUCTOS es el ‘0001111’, por lo tanto el stored procedure SIGUIENTE_CODIGO nos devuelve ‘0001112’. Fíjate que le ha agregado ceros a la izquierda para que la longitud del código siempre sea de 7.

Conclusión:

Si estudias durante un rato los stored procedures anteriores verás que escribirlos es fácil y sencillo, no tiene misterios, solamente la sintaxis puede ser un poco distinta a la que ya conoces y estás acostumbrado a usar, pero nada más que eso. Practica escribiendo varios stored procedures y verás que enseguida les perderás el miedo y podrás usarlos para tu provecho y beneficio.

Artículos relacionados:

Entendiendo a los Stored Procedures

El índice del blog Firebird21

El foro del blog Firebird21