Un ejemplo del uso de funciones en Firebird

Deja un comentario

En este artículo ya habíamos visto que actualmente (versión 2.5.2) el Firebird no dispone de funciones nativamente pero que fácilmente podemos simular que sí las tiene.

http://firebird21.wordpress.com/2014/04/20/usando-un-stored-procedure-como-una-funcion/

Ahora, veremos un ejemplo práctico de como usar una función.

Ejemplo. Obteniendo la parte gravada y el impuesto

El Gobierno cobra un impuesto por cada venta que se realiza en el país. El valor monetario de ese impuesto se calcula como un porcentaje sobre lo que se le cobra al cliente. Por lo tanto:

PrecioVenta = ImporteGravado + Impuesto     (1)

Como el Impuesto es siempre un porcentaje sobre el ImporteGravado entonces la fórmula es:

Impuesto = ImporteGravado * PorcentajeImpuesto / 100     (2)

Y reemplazando la fórmula del Impuesto en (1) obtenemos:

PrecioVenta = ImporteGravado + ImporteGravado * PorcentajeImpuesto / 100     (3)

Que también podemos escribir como:

PrecioVenta = ImporteGravado * (1 + PorcentajeImpuesto / 100)     (4)

En una tabla llamada FACTURAS tenemos guardado el PrecioVenta y también el PorcentajeImpuesto y lo que queremos hallar es:

  1. El ImporteGravado
  2. El Impuesto

 Para ello, escribimos el siguiente stored procedure seleccionable:

CREATE PROCEDURE DISCRIMINAR_VENTA(
   tnPrecioVenta NUMERIC(12, 2),
   tnPorcentaje  NUMERIC(5, 2))
RETURNS(
   ftnGravado  NUMERIC(12, 2),
   ftnImpuesto NUMERIC(12, 2))
AS
BEGIN

   -- tnPrecioVenta = ftnGravado * (1 + tnPorcentaje / 100)

   ftnGravado = tnPrecioVenta / (1 + tnPorcentaje / 100);

   ftnImpuesto = ftnGravado * tnPorcentaje / 100;

   SUSPEND;

END;

Al cual podemos llamar de esta forma:

SELECT
   F.FAC_MONTOX,
   D.ftnGravado,
   D.ftnImpuesto,
   D.ftnGravado + D.ftnImpuesto
FROM
   FACTURAS F
LEFT JOIN
   DISCRIMINAR_VENTA(F.FAC_MONTOX, F.FAC_PORIVA) D
      ON 1 = 1

Desde luego que F.FAC_MONTOX y D.ftnGravado + D.ftnImpuesto deben ser idénticos, se escribió a ambos solamente para comprobar lo evidente.

En este caso tenemos una tabla llamada FACTURAS, dentro de la cual tenemos una columna llamada FAC_MONTOX donde guardamos el precio de venta y una columna llamada FAC_PORIVA donde guardamos el porcentaje del impuesto.

Al llamar a la función DISCRIMINAR_VENTA obtenemos la parte gravada y el impuesto que corresponden a cada venta realizada.

Artículos relacionados:

Usando un stored procedure como una función

El índice del blog Firebird21

 

Usando un stored procedure como una función

Deja un comentario

En los lenguajes de programación existe un tipo de rutina al cual se le llama FUNCIÓN.

La característica de una FUNCIÓN es que puede recibir cero, uno, o varios parámetros de entrada y siempre devuelve exactamente un parámetro de salida.

Ese parámetro de salida devuelto por una FUNCIÓN puede ser asignado a una variable, utilizado en una fórmula, o comparado con otros valores.

Firebird actualmente no dispone de funciones, sí tiene stored procedures pero no tiene funciones. Éstas recién estarán disponibles cuando se libere la versión 3.0

Pero mientras tanto aunque no tenga funciones nosotros podemos simular que sí las tiene. Para ello utilizaremos un truco. La sintaxis para usar a un stored procedure como si fuera una función es la siguiente:

SELECT
   T.MiColumna1,
   F.MiValor
FROM
   MiTabla T
LEFT JOIN
   MiStoredProcedureSeleccionable(T.MiColumna1) F
      ON 1 = 1

Entendiendo al truco:

  1. El stored procedure debe ser seleccionable. O sea que debe tener el comando SUSPEND en él.
  2. Es preferible usar LEFT JOIN para que aún en el caso de que el stored procedure devuelva NULL se puedan obtener las columnas de la tabla principal
  3. La condición del LEFT JOIN debe ser siempre verdadera. Al poner 1 = 1 nos aseguramos que así sea, porque evidentemente 1 siempre será igual a 1.

 Usando el valor devuelto por la función

El valor que nos devolvió nuestra función (en realidad nuestro stored procedure seleccionable, como ya sabes) a todos los efectos puede ser tratado como si fuera una columna más. O sea que lo podemos usar en una fórmula, con la cláusula WHERE, con la cláusula ORDER BY, etc.

Ejemplo 1:

SELECT
   T.MiColumna1,
   F.MiValor,
   T.MiColumna2 * F.MiValor
FROM
   MiTabla T
LEFT JOIN
   MiStoredProcedureSeleccionable(T.MiColumna1) F
      ON 1 = 1

En este ejemplo multiplicamos a F.MiValor (que es el valor que devolvió la función) por T.MiColumna2. O sea que usamos a F.MiValor en una fórmula

Ejemplo 2:

SELECT
   T.MiColumna1,
   F.MiValor
FROM
   MiTabla T
LEFT JOIN
   MiStoredProcedureSeleccionable(T.MiColumna1) F
      ON 1 = 1
WHERE
   F.MiValor = 125

En este caso usamos a F.MiValor en una condición de la cláusula WHERE

Ejemplo 3:

SELECT
   T.MiColumna1,
   F.MiValor
FROM
   MiTabla T
LEFT JOIN
   MiStoredProcedureSeleccionable(T.MiColumna1) F
      ON 1 = 1
ORDER BY
   F.MiValor

Ahora, ordenamos el conjunto resultado según el valor que tenga F.MiValor

Conclusión:

Aunque actualmente Firebird no dispone de funciones (las dispondrá cuando salga la versión 3.0) eso no es problema para nosotros porque podemos simularlas muy fácilmente. Para ello lo único que debemos hacer es crear un stored procedure seleccionable, hacer un LEFT JOIN a ese stored procedure seleccionable y asegurarnos que la condición siempre sea verdadera (al poner 1 = 1 nos aseguramos de que así sea).

Y además, este truco tiene una ventaja adicional: no es precioso que el stored procedure devuelva solamente un parámetro de salida, puede devolver varios, y los podemos usar a todos ellos si queremos. O sea que si MiStoredProcedureSeleccionable devuelve los parámetros de salida MiValor1, MiValor2, MiValor3, en nuestro SELECT podremos usar a MiValor1, MiValor2, y MiValor3, si así lo deseamos. ¡¡¡Excelente!!!

Artículo relacionado:

El índice del blog Firebird21

 

Escribiendo un trigger

Deja un comentario

En este artículo habíamos visto como escribir un stored procedure:

http://firebird21.wordpress.com/2014/03/13/escribiendo-un-stored-procedure/

Ahora veremos como escribir un trigger.

En realidad son muy parecidos, hay pocas diferencias entre ellos pero es muy importante que sepas cuales son, puedes leer más en este artículo:

http://firebird21.wordpress.com/2013/06/28/similitudes-y-diferencias-entre-stored-procedures-y-triggers/

Cosas que debes saber sobre los triggers:

  1. Tú nunca pides que se ejecute un trigger, el trigger siempre se ejecuta automáticamente
  2. Los triggers no pueden recibir parámetros de entrada
  3. Los triggers no pueden devolver parámetros de salida
  4. Dentro de los triggers puedes usar las variables booleanas INSERTING, UPDATING, DELETING
  5. Dentro de los triggers existen las pseudo-variables NEW y OLD
  6. Dentro de los triggers no puedes usar el comando SUSPEND
  7. Los triggers se ejecutan siguiendo el orden establecido en POSITION. O sea que primero se ejecuta POSITION 0, luego POSITION 1, luego POSITION 2, etc.

Tipos de trigger

Hay dos tipos de trigger:

  1. BEFORE
  2. AFTER

Los triggers BEFORE se ejecutan antes de que la operación de inserción, actualización, borrado, sea realizada. ¿Para qué se los usa? Para validar que las columnas tengan solamente valores correctos.

Los triggers AFTER se ejecutan después de que la operación de inserción, actualización, borrado sea realizada. ¿Para qué se los usa? Típicamente para cambiar el contenido de otra tabla, o de otra fila dentro de la misma tabla.

Ejemplo 1:

Queremos asegurarnos de que al hacer una venta se registre el precio de venta del producto.

CREATE TRIGGER VENTASDET_BI FOR VENTASDET
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 0
AS
BEGIN

   IF(NEW.VEN_MONTOX IS NULL OR NEW.VEN_MONTOX <= 0) THEN
      EXCEPTION E_SIN_IMPORTE;

END;

La pseudo-variable NEW.VEN_MONTOX tiene el valor que se quiere guardar en la columna VEN_MONTOX. Si ese valor es NULL o es menor o igual que cero eso es un error, no se debe permitir. Por lo tanto se eleva una excepción llamada E_SIN_IMPORTE. Siempre que se eleva una excepción el procesamiento se detiene en ese punto. ¿La consecuencia? que los usuarios jamás podrán grabar una fila si el valor que quieren poner en la columna VEN_MONTOX es NULL o es menor o igual que cero.

En otras palabras, hemos validado que la columna VEN_MONTOX siempre tenga un valor mayor que cero. Hagan lo que hagan los usuarios jamás conseguirán guardar en esa columna un valor NULL, cero, o negativo.

Ejemplo 2:

Queremos asegurarnos que el porcentaje de ganancia siempre sea del 5% o más, nunca debería ser menor.


CREATE TRIGGER PRODUCTOS_BIU FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
BEGIN

   IF (NEW.PRD_PREVTA * 100 / NEW.PRD_PRECTO - 100 < 5) THEN
      EXCEPTION E_PORCENTAJE_MENOR_5
         'El porcentaje de ganancia es menor que el 5%. Identificador = ' || NEW.PRD_IDENTI ;

   WHEN EXCEPTION E_PORCENTAJE_MENOR_5 DO BEGIN
      IN AUTONOMOUS TRANSACTION DO
         INSERT INTO ERRORES
                   (ERR_MODULO, ERR_COMENT)
            VALUES ('PRODUCTOS_BIU' , 'El % de ganancia del producto ' || NEW.PRD_NOMBRE || ' es menor que 5%') ;
      EXCEPTION;
   END

END;

En este caso si el porcentaje de ganancia es menor que el 5% lanzamos la excepción E_PORCENTAJE_MENOR_5. Al lanzar una excepción el procesamiento se detiene en ese punto. Luego capturamos la excepción. ¿Por qué la capturamos? Porque queremos guardar en la tabla ERRORES el error que encontramos, así más tarde podremos revisar esa tabla y ver todos los errores que ocurrieron. Si no capturamos la excepción entonces el producto no será grabado en la tabla PRODUCTOS pero no sabremos el motivo. Recuerda que puede haber muchos motivos por los cuales no se lo pudo grabar (Primary Key duplicada, Unique Key duplicada, restricción Check no cumplida, etc.). Finalmente relanzamos la excepción. ¿Para qué? Para que el Servidor del Firebird no grabe la fila y le diga al Cliente (y el Cliente se lo dirá a nuestra aplicación) que ocurrió una excepción.

Entonces nuestro trigger hace lo siguiente: Valida que el porcentaje de ganancia sea igual o mayor que el 5%. Si lo es, continúa normalmente. Si no lo es, inserta una fila en la tabla ERRORES.

Ejemplo 3:

Queremos asegurarnos de que exista la Sucursal que se quiere usar.


CREATE TRIGGER BIU_PRODUCTOS FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
BEGIN

   IF(NEW.PRD_CODSUC NOT IN (SELECT SUC_CODIGO FROM SUCURSALES)) THEN
      EXCEPTION E_SIN_SUCURSAL;

END;

Lo que estamos diciendo aquí es: si la Sucursal que se quiere grabar no existe en la tabla SUCURSALES entonces lanzar una excepción para que no se grabe este producto.

O sea, nos aseguramos que en la columna PRD_CODSUC se tenga el código de una Sucursal que existe.

Ejemplo 4:

Tenemos una tabla donde guardamos los saldos acumulados de cada cuenta contable. Cuando el usuario quiere ver un Balance de Sumas y Saldos, un Balance General o un Estado de Resultados lo que hacemos es mostrarle el contenido de esa tabla de saldos acumulados. Pero si alguno de los asientos contables ha cambiado entonces se debe reprocesar la tabla de saldos acumulados. ¿Cómo sabemos antes de mostrar un informe si debemos reprocesar la tabla de saldos acumulados? Leyendo el valor de la columna CON_RECSAL (recalcular saldos) de la tabla CONFIGURACIÓN. Si la columna CON_RECSAL tiene una “V” (que significa “verdadero”) entonces sí debemos recalcular los saldos acumulados.


CREATE TRIGGER AIUD_ASIENTOSDET FOR ASIENTOSDET
   ACTIVE AFTER INSERT OR UPDATE OR DELETE
   POSITION 2
AS
BEGIN

   UPDATE OR INSERT INTO CONFIGURACION
            (CON_IDENTI,CON_RECSAL)
     VALUES (1         , 'V');

END;

Fíjate que este es un trigger que se ejecutará después de que la inserción, actualización o borrado se haya realizado en la tabla ASIENTOSDET. ¿Por qué después? Porque dice que se activará AFTER. Y la palabra inglesa “after” significa “después”.

En otras palabras, este trigger solamente se ejecutará si ningún trigger BEFORE lanzó una excepción. Si un trigger BEFORE lanzó una excepción o un trigger AFTER con un valor de POSITION menor que 2 lanzó una excepción entonces este trigger jamás se ejecutará.

Si este trigger llega a ejecutarse eso significa que a la tabla ASIENTOSDET exitosamente se le insertó, actualizó o borró una fila. Y en ese caso actualiza a las columnas CON_IDENTI y CON_RECSAL de la tabla CONFIGURACIÓN. ¿Para qué? Para que sepamos que las columnas de la tabla SALDOS_ACUMULADOS tienen valores incorrectos y volvamos a calcularlos.

Conclusión:

Los triggers son una de las herramientas poderosísimas que nos provee el Firebird y debemos utilizarlos al máximo para tener unas aplicaciones robustas y súper confiables.

Un buen uso de los triggers nos asegurará de que jamás tengamos basura en nuestra Base de Datos (se le llama “basura” a los datos que están pero que no deberían estar).

Artículos relacionados:

Entendiendo a los triggers

Uso de los dos puntos dentro de un stored procedure o trigger

Similitudes y diferencias entre stored procedures y triggers

El índice del blog Firebird21

Escribiendo un stored procedure

7 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.

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.


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.

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:

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.

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.

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

Capturando una excepción y continuando con el procesamiento

2 comentarios

Supongamos la siguiente situación: tenemos que insertar 100 filas en una tabla, pero no serán insertadas manualmente sino leídas desde otra tabla o archivo. En otras palabras, un proceso por lotes.

Pero si alguna de esas 100 filas tiene algún problema el proceso debería continuar. La fila problemática no será insertada (o será insertada con alguno de sus valores originales cambiados) pero las restantes 99 filas sí deberán ser insertadas.

Por ejemplo, la fila 27 tiene un error: no cumple con una restricción UNIQUE KEY.

Así que aquí tenemos dos opciones:

  1. No capturamos la excepción que detecta la violación a las restricciones UNIQUE KEY
  2. Capturamos la excepción que detecta la violación a las restricciones UNIQUE KEY

Si elegimos la opción 1. entonces el Servidor del Firebird cuando encuentre una violación a la restricción UNIQUE KEY detendrá el procesamiento y las filas 27 a 100 nunca serán insertadas. Las filas 1 a 26 serán insertadas o no dependiendo de como se manejan las transacciones: si después de cada INSERT hay un COMMIT entonces sí habrán sido insertadas, pero si el COMMIT se ejecutará solamente después de procesar las 100 filas, entonces no serán insertadas. Todo esto implica que si elegimos la opción 1. entonces es seguro que (algunas, muchas, o todas) las filas no serán insertadas si ocurre una violación a la restricción UNIQUE KEY.

Si elegimos la opción 2. tenemos más control sobre lo que ocurrirá cuando se detecte una violación a la restricción UNIQUE KEY. Al capturar una excepción:

  • Podemos manejarla y “engañarle” al Firebird diciéndole que tal excepción nunca ocurrió
  • Podemos manejarla y decirle al Firebird que continúe con sus acciones normales. O sea que a partir de ahí continuará como si hubiéramos elegido la opción 1. Decirle al Firebird que continúe con sus acciones normales después de que nosotros hayamos manejado la excepción se llama “relanzar la excepción”.

En general, aunque no es obligatorio, es muy útil escribir en una tabla de LOG los errores que causaron que una excepción ocurriera. Así, más adelante podremos revisar esa tabla de LOG y descubrir que fue lo que anduvo mal. Y tomar las medidas adecuadas para que no vuelva a ocurrir.

Múltiples niveles

A veces, tenemos un caso como el siguiente:

El stored procedure 1 llama al stored procedure 2 el cual llama al stored procedure 3

Servidor de Firebird —> stored procedure 1 —> stored procedure 2 —> stored procedure 3

¿Qué ocurre con las excepciones en ese caso?

Si la excepción ocurrió en el stored procedure 3 entonces éste puede manejarla o no. Si la manejó puede relanzarla o no. Si no la manejó o la relanzó entonces la excepción llegará al stored procedure 2. Así mismo, el stored procedure 2 puede manejar la excepción que recibió del stored procedure 3 o no. Si no la manejó o si la relanzó entonces la excepción llegará al stored procedure 1. También el stored procedure 1 puede manejar o no la excepción que le llegó desde el stored procedure 2. Si no la manejó o si la relanzó entonces la excepción llegará al Servidor del Firebird.

Si la excepción llega al Servidor del Firebird entonces allí mismo se detiene el procesamiento. Se termina.

Pero si la excepción nunca llegó al Servidor del Firebird entonces el procesamiento continuará normalmente, como si la excepción nunca hubiera ocurrido.

En nuestro ejemplo de las 100 filas a insertar con la fila 27 teniendo problemas, si alguno de los stored procedures capturó la excepción y no la relanzó entonces el Servidor del Firebird nunca sabrá que tal excepción ocurrió. Y por ello continuará procesando normalmente a las restantes filas.

CREATE PROCEDURE MISTOREDPROCEDURE3
AS
   DECLARE VARIABLE lcNombre VARCHAR(128);
BEGIN

   FOR SELECT
      BAN_NOMBRE
   FROM
      BANCOS
   INTO
      :lcNombre
   DO BEGIN
      INSERT INTO BANCOS_NUEVOS
                 (BAN_NOMBRE)
          VALUES (:lcNombre);
      WHEN SQLCODE -803 DO BEGIN -- Violación de una UNIQUE KEY
         IN AUTONOMOUS TRANSACTION DO BEGIN
            INSERT INTO LOG
                       (LOG_COMENT)
                VALUES ('ERROR AL INSERTAR EL BANCO: ' || :lcNombre);
         END
      END
   END

END;

Tenemos dos tablas: BANCOS y BANCOS_NUEVOS, y queremos que los nombres de todos los Bancos que se encuentran en la tabla BANCOS se inserten en la tabla BANCOS_NUEVOS. Pero como la tabla BANCOS_NUEVOS ya tiene algunas filas entonces algunos nombres podrían estar repetidos, violando por lo tanto una restricción UNIQUE KEY. Pero todos los nombres que no estén repetidos deben ser insertados en la tabla BANCOS_NUEVOS.

El stored procedure MiStoredProcedure3 se encarga de esa tarea. Como la excepción -803 (que detecta las violaciones a la restricción UNIQUE KEY) no fue relanzada entonces ni el stored procedure MiStoredProcedure2 ni el stored procedure MiStoredProcedure1 ni el Servidor del Firebird se enterarán de que tal excepción ocurrió alguna vez.

¿Queremos que el stored procedure MiStoredProcedure2 se entere de que ocurrió la excepción -803? Entonces debemos escribir la palabra EXCEPTION para relanzarla.

WHEN SQLCODE -803 DO BEGIN -- Violación de una UNIQUE KEY
   IN AUTONOMOUS TRANSACTION DO BEGIN
      INSERT INTO LOG
                 (LOG_COMENT)
          VALUES ('ERROR AL INSERTAR EL BANCO: ' || :lcNombre);
   END
   EXCEPTION;
END

Aquí, se maneja la excepción -803 (la cual indica que ocurrió una violación a la restricción UNIQUE KEY) escribiendo en un archivo de LOG el error ocurrido. Luego, se relanza la excepción para que el stored procedure MiStoredProcedure2 se entere de que ocurrió esa excepción. E igualmente puede manejarla o no.

Resumiendo:

Cuando ocurre una excepción dentro de un stored procedure o de un trigger tenemos tres posibilidades:

  1. No manejarla
  2. Manejarla y no relanzarla
  3. Manejarla y relanzarla

Si no la manejamos entonces subirá un nivel. Si en ese nivel tampoco la manejamos subirá otro nivel. Y así hasta llegar al Servidor del Firebird el cual detendrá el procesamiento.

Si la manejamos y no la relanzamos entonces el nivel superior nunca se enterará de que la excepción ocurrió.

Si la manejamos y la relanzamos entonces el nivel superior sabrá que ocurrió esa excepción en el nivel inferior.

Conclusión:

Las excepciones son una herramienta buenísima de la que disponemos y que debemos utilizar para detectar y manejar cualquier error que ocurra dentro de un stored procedure o de un trigger.

Si nosotros no manejamos a las excepciones entonces el Servidor del Firebird se encargará de ello, pero eso implica que el proceso que estábamos realizando se detendrá.

Si no queremos que el proceso se detenga entonces debemos capturar la excepción y no relanzarla, de esa manera el Servidor del Firebird no se enterará de que la excepción ocurrió y no detendrá el proceso que estábamos realizando.

Artículos relacionados:

Entendiendo las excepciones

Capturando excepciones

Capturando excepciones. Códigos de error predeterminados

Capturando excepciones del usuario

El índice del blog Firebird21

Asegurando tener datos consistentes en la Base de Datos

2 comentarios

Lo peor que le podría ocurrir a una Base de Datos es que tenga datos inconsistentes, es decir: que sean contradictorios o que falten datos que deberían estar o que estén datos que no deberían estar.

Firebird nos provee de varias herramientas que podemos usar para asegurar la consistencia, ellas son:

  • Primary Key
  • Foreign Key
  • Unique Key
  • Check
  • Trigger
  • Stored procedure

Con la Primary Key podemos identificar sin dudas a cada fila de la tabla. Lo ideal es que la Primary Key sea numérica y autoincremental.

Con la Foreign Key aseguramos que el valor de una columna (de la tabla hija) exista en otra columna (de la tabla padre). De esta manera siempre podremos relacionar a ambas tablas entre sí, porque tienen datos comunes.

Con la Unique Key sabemos que no habrá valores duplicados en la columna. Todos los valores serán únicos en esa columna de esa tabla.

Con el Check podemos hacer validaciones relativamente simples. Impedimos que se introduzcan valores que sabemos que no deberían estar.

Con el Trigger podemos hacer validaciones simples o muy complicadas. Esto último porque nos permite tener variables locales y entonces las condiciones de validación pueden ser tan complejas como necesitemos. También podemos usarlos para colocar el valor de una columna o para insertar, modificar o borrar filas de otras tablas.

Con el Stored procedure podemos también validar antes de realizar la operación de inserción, actualización o borrado.

Validando en un stored procedure

Aunque podemos usar los stored procedures para hacer validaciones eso no es lo aconsejable. ¿Por qué no? porque alguien podría saltarse esa validación muy fácilmente. Por ejemplo, para insertar una fila en la tabla de BANCOS podríamos tener un stored procedure que se encargue de esa tarea, y entonces en lugar de escribir:

EXECUTE PROCEDURE GRABAR_BANCO(17, 'Citibank');

alguien podría simplemente escribir:

INSERT INTO BANCOS (BAN_CODIGO, BAN_NOMBRE) VALUES (17, 'Citibank');

y así se saltaría cualquier validación que hubiéramos escrito en el stored procedure. En otras palabras las validaciones que escribimos en el stored procedure no sirvieron, nuestro trabajo no sirvió para validar.

En cambio, nadie puede saltarse la validación que escribamos en un trigger. Lo que se escribe en un trigger siempre se ejecuta.

¿Es conveniente validar en un check o en un trigger?

Podemos validar con un check cuando la validación sea relativamente simple. Por ejemplo: que el precio de venta siempre sea mayor que cero, que la fecha siempre sea igual o posterior al “01/ENE/2010″, que todos los productos siempre tengan nombre, que el sexo de la persona siempre sea “F” o “M”, que la cobranza siempre se haga a facturas que tienen saldo, etc.

Con un trigger podemos validar todos los casos anteriores y también cuando las validaciones son más complejas. Por ejemplo: que la fecha de la cobranza siempre sea igual o posterior a la fecha de la venta, que el descuento solamente se aplique a clientes a quienes ya se les vendió por más de 4.000 dólares y la última venta fue hace menos de 90 días y ya se les cobró al menos el 75% de lo vendido.

Además, si usamos un trigger para validar tenemos otra ventaja: podemos mostrarle al usuario mensajes personalizados. Si un check falla el mensaje que nos enviará el Firebird siempre será:

“Operation violates CHECK constraint”

en cambio, si usamos un trigger lanzamos una excepción cuando descubrimos un error y en esa excepción podemos escribir cualquier texto que deseemos, teniendo así mensajes de error personalizados.

Conclusión:

Una Base de Datos cuyo contenido es inconsistente es lo peor que puedes tener porque ninguna información que obtengas será confiable. Por ese motivo hay que evitar a toda costa tener inconsistencias en los datos. Firebird nos provee de varias herramientas muy útiles y debemos utilizarlas y sacarles el máximo provecho que para eso están.

Artículos relacionados:

Entendiendo a las Primary Keys

Claves primarias: ¿simples o compuestas?

Entendiendo a las Foreign Keys

Entendiendo la integridad referencial

Usando la restricción CHECK

Entendiendo a los Stored Procedures

Entendiendo a los triggers

Entendiendo las excepciones

El índice del blog Firebird21

Enviando y recibiendo una cantidad variable de parámetros en los stored procedures

Deja un comentario

En general, la cantidad de parámetros que un stored procedure recibe o devuelve es una cantidad fija. Por ejemplo, este stored procedure recibe dos parámetros:

CREATE PROCEDURE SP1(
   tcParametro1 VARCHAR(512),
   tnParametro2 INTEGER)

cuyos nombres son tcParametro1 y tnParametro2. Esto funciona muy bien, pero requiere que conozcamos de antemano la cantidad de parámetros y el tipo de cada uno de ellos. Pero a veces eso no sucede porque recién en tiempo de ejecución podemos conocer la cantidad de parámetros. ¿Cómo lo resolvemos en ese caso?

La técnica es enviar todos los parámetros en un string y separar cada uno de esos parámetros con un delimitador. El delimitador puede ser cualquier caracter (o conjunto de caracteres) que estamos seguros que no se encontrarán entre los datos. Por ejemplo, sería un error usar como delimitador a la letra A si dentro de los datos hay nombres de personas porque muchas personas tienen nombres que contienen a la letra A. Hay que elegir un caracter que nunca puede estar contenido entre los datos, por ejemplo: ^, &, %, $, etc.

Una vez que hemos elegido un delimitador que estamos segurísimos que nunca puede estar incluido entre los datos, enviamos todos los datos que queremos, separados por ese delimitador.

Ejemplo:

Tenemos una tabla llamada PRODUCTOS que tiene estos datos:

VARIABLES1

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

y queremos consultar los nombres de los productos cuyos identificadores son 10, 15 y 20. Para eso escribimos el siguiente stored procedure:

CREATE PROCEDURE NOMBRES_PRODUCTOS(
   tcIdentificadores VARCHAR(512))
RETURNS(
   tcNombre TYPE OF COLUMN PRODUCTOS.PRD_NOMBRE)
AS
BEGIN

   FOR SELECT
      PRD_NOMBRE
   FROM
      PRODUCTOS
   WHERE
      :tcIdentificadores CONTAINING '^' || PRD_IDENTI || '^'
   INTO
      :tcNombre
   DO
      SUSPEND;

END;

En este ejemplo nuestro delimitador es el acento circunflejo (^).

Llamamos a este stored procedure seleccionable de la siguiente forma:

SELECT * FROM NOMBRES_PRODUCTOS('^10^15^20^');

Y este es el resultado que obtenemos:

VARIABLES2

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

Y ahora los que nos interesan son los nombres de los productos cuyos identificadores son 10, 12, 14, 16, 18, 20, así que invocamos al mismo stored procedure de esta forma:

SELECT * FROM NOMBRES_PRODUCTOS('^10^12^14^16^18^20^');

Y esto es lo que obtenemos:

VARIABLES3

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

O sea que escribimos un solo stored procedure pero nos puede devolver los nombres de 3 productos, de 6 productos, o de la cantidad de productos que se nos ocurra.

Conclusión:

Poder enviar (o recibir) una cantidad variable de parámetros puede ser muy útil en las ocasiones en las cuales la cantidad de parámetros enviados o recibidos solamente podemos conocer en tiempo de ejecución. De esta manera escribimos un solo stored procedure el cual a veces recibe (o devuelve) un parámetro, a veces dos parámetros, a veces tres parámetros, etc., los que necesitemos.

Artículo relacionado:

El índice del blog Firebird21

Older Entries

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 176 seguidores