Sentencias simples y sentencias compuestas

2 comentarios

Cuando escribimos un stored procedure o un trigger podemos usar sentencias simples o sentencias compuestas.

Si la sentencia es simple una sola línea es ejecutada, si la sentencia es compuesta muchas líneas pueden ser ejecutadas.

Ejemplo 1. Una sentencia simple en un IF()

IF (MiCondición) THEN
   MiSentencia;

-- Esta línea ya no está dentro del IF()
-- Y esta línea tampoco está dentro del IF()

En este caso solamente se ejecutará la línea MiSentencia si MiCondición es verdadera. Las que están a continuación se ejecutarán siempre ¿por qué? porque no están incluidas en el IF().

Ejemplo 2. Una sentencia compuesta en un IF()

IF (MiCondición) THEN BEGIN
   MiSentencia1;
   MiSentencia2;
   MiSentencia3;
   --
   MiSentencian;
END
-- Esta línea ya no está dentro del IF()
-- Y esta línea tampoco está dentro del IF()

En este caso la sentencia ya no es simple sino compuesta ¿y cómo sabemos eso? Porque el Firebird trata a todas las sentencias escritas entre el BEGIN y el END como pertenecientes al IF(), hay varias sentencias allí y todas ellas serán ejecutadas si MiCondición es verdadera.

Ejemplo 3. Una sentencia compuesta y una sentencia simple en un IF()

IF (MiCondición) THEN BEGIN
   MiSentencia1;
   MiSentencia2;
   MiSentencia3;
   --
   MiSentencian;
END ELSE
   MiSentenciax;
-- Esta línea ya no está dentro del ELSE
-- Y esta línea tampoco está dentro del ELSE

En este caso, si MiCondición es verdadera se ejecutará la sentencia compuesta que está entre el BEGIN y el END pero si MiCondición es falsa entonces se ejecutará la sentencia simple que está a continuación del ELSE.

Ejemplo 4. Dos sentencias compuestas en un IF()

IF (MiCondición) THEN BEGIN
   MiSentencia1;
   MiSentencia2;
   MiSentencia3;
   --
   MiSentencian;
END ELSE BEGIN
   MiSentenciax;
   MiSentenciay;
   MiSentenciaz;
   --
   MiSentenciam;
END
-- Esta línea ya no está dentro del ELSE
-- Y esta línea tampoco está dentro del ELSE

En este caso hay un BEGIN … END después del THEN y hay otro BEGIN … END después del ELSE. ¿Qué significa eso? Que si MiCondición es verdadera se ejecutarán todas las líneas que están en el primer BEGIN … END y en cambio si MiCondición es falsa se ejecutarán todas las líneas que están en el segundo BEGIN … END.

Ejemplo 5. Una sentencia simple en un ciclo WHILE()

WHILE (MiCondición) DO
   MiSentencia;
-- Esta línea ya no está dentro del WHILE
-- Y esta línea tampoco está dentro del WHILE

Aquí, mientras MiCondición sea verdadera se ejecutará MiSentencia.

Ejemplo 6. Una sentencia compuesta en un ciclo WHILE()

WHILE (MiCondición) DO BEGIN
   MiSentencia1;
   MiSentencia2;
   MiSentencia3;
   --
   MiSentencian;
END
-- Esta línea ya no está dentro del WHILE
-- Y esta línea tampoco está dentro del WHILE

Ahora en el ciclo WHILE() se está usando una sentencia compuesta. Todas las líneas que hayamos escrito entre el BEGIN y el END se ejecutarán mientras MiCondición sea verdadera.

Ejemplo 7. Una sentencia simple en un FOR SELECT

FOR SELECT
   MiColumna
FROM
   MiTabla
INTO
   :MiVariable
DO
   MiSentencia;
-- Este línea ya no pertenece al FOR SELECT
-- Y esta línea tampoco

Solamente MiSentencia se ejecutará porque se trata de una sentencia simple y por lo tanto solamente la línea que sigue al DO se ejecuta.

Ejemplo 8. Una sentencia compuesta en un FOR SELECT

FOR SELECT
   MiColumna
FROM
   MiTabla
INTO
   :MiVariable
DO BEGIN
   MiSentencia1;
   MiSentencia2;
   MiSentencia3;
   --
   MiSentencian;
END
-- Este línea ya no pertenece al FOR SELECT
-- Y esta línea tampoco

Después del DO hay un BEGIN … END, eso nos indica que se trata de una sentencia compuesta y por lo tanto todas las líneas que se encuentran dentro del BEGIN … END serán ejecutadas.

Conclusión:

Al igual que en Pascal, C, y otros lenguajes en Firebird podemos usar sentencias simples o sentencias compuestas. Las sentencias compuestas empiezan con un BEGIN y finalizan con un END y todo lo que esté escrito entre esas dos palabras se trata como una unidad.

Si no usamos BEGIN … END entonces siempre y en todos los casos la sentencia será simple. Para que sea compuesta sí o sí deberemos rodearla por las palabras BEGIN … END

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Escribiendo un trigger

5 comentarios

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

https://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:

https://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

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