Capturando una excepción y continuando con el procesamiento

7 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

El foro 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

3 comentarios

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ículos relacionados:

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

El índice del blog Firebird21

El foro del blog Firebird21

Un stored procedure para guardar la cantidad de registros de una tabla

Deja un comentario

En este artículo:

https://firebird21.wordpress.com/2013/11/06/un-stored-procedure-para-guardar-la-cantidad-de-registros-de-cada-tabla/

habíamos visto un stored procedure que nos permite guardar la cantidad de filas que tienen todas las tablas de nuestra Base de Datos; haríamos algo así para rápidamente obtener esas cantidades cuando las necesitemos.

Aquí tenemos otra alternativa, si por algún motivo la técnica mostrada en el artículo anterior no nos resulta conveniente. En este caso la cantidad de filas de una tabla siempre será la correcta después de hacer un INSERT o un DELETE en ella.

CREATE EXCEPTION E_CODIGO_OPERACION_INCORRECTO 'El código de la operación no existe. Debe ser "I" o "D"';

CREATE PROCEDURE SP_ACTUALIZAR_RECCOUNT(
   tnCodSuc TYPE OF D_CODIGOSUCURSAL,
   tcOperac TYPE OF D_CHAR1,
   tcTablax TYPE OF D_NOMBRE30)
AS
   DECLARE VARIABLE lcComando VARCHAR(128);
   DECLARE VARIABLE lnCantidadFilas INTEGER;
BEGIN

   IF (tcOperac <> 'I' and tcOperac <> 'D') THEN
      EXCEPTION E_CODIGO_OPERACION_INCORRECTO;

   UPDATE
      RECCOUNT
   SET
      REC_CANTID = REC_CANTID + IIF(:tcOperac = 'I', 1, -1)
   WHERE
      REC_CODSUC = :tnCodSuc AND
      REC_NOMBRE = :tcTablax;

   IF (ROW_COUNT = 0) THEN BEGIN -- No se encontró la tabla en RECCOUNT, por lo tanto hay que agregarla
      lcComando = 'SELECT COUNT(*) FROM ' || tcTablax;
      EXECUTE STATEMENT (lcComando) INTO :lnCantidadFilas;
      INSERT INTO RECCOUNT
                 (REC_CODSUC, REC_NOMBRE, REC_CANTID      )
          VALUES (:tnCodSuc , :tcTablax , :lnCantidadFilas) ;
   END

END;

¿Qué hace este stored procedure?

Primero, verifica que la operación a realizar sea “I” (INSERT) o sea “D” (DELETE). Si no es ni “I” ni “D” entonces eleva una excepción y por lo tanto termina la ejecución de este stored procedure.

Segundo, intenta actualizar la cantidad de filas de la tabla destino cuyo nombre está guardado en la tabla RECCOUNT. Si la operación a realizar es “I” aumenta la cantidad de filas en 1 y si la operación a realizar es “D” disminuye la cantidad de filas en 1.

Tercero, si no se pudo actualizar la tabla RECCOUNT porque el nombre de la tabla destino no existía en la tabla RECCOUNT entonces agrega una fila a la tabla RECCOUNT con el nombre de la tabla que no fue actualizada y la cantidad exacta de filas que tiene esa tabla.

De esta manera, al finalizar el stored procedure estaremos seguros que la tabla RECCOUNT tiene una fila con el nombre de la tabla destino y con la cantidad exacta de filas que tiene esa tabla destino.

RECCOUNT1

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

En la columna REC_NOMBRE tenemos el nombre de cada tabla de la Base de Datos, en la columna REC_CANTID la cantidad exacta de filas que tiene cada tabla.

¿Cómo llamamos al stored procedure?

Mediante un trigger AFTER INSERT o un trigger AFTER DELETE, como podemos ver a continuación:

CREATE TRIGGER AI_PRODUCTOS FOR PRODUCTOS
   ACTIVE AFTER INSERT
   POSITION 1
AS
BEGIN

   EXECUTE PROCEDURE SP_ACTUALIZAR_RECCOUNT(NEW.PRD_CODSUC, 'I', 'PRODUCTOS');

END;
CREATE TRIGGER AD_PRODUCTOS FOR PRODUCTOS
   ACTIVE AFTER DELETE
   POSITION 2
AS
BEGIN

   EXECUTE PROCEDURE SP_ACTUALIZAR_RECCOUNT(OLD.PRD_CODSUC, 'D', 'PRODUCTOS');

END;

Conclusión:

Si en nuestros programas necesitamos conocer la cantidad de filas que tiene una tabla hacer un SELECT COUNT(*) FROM MiTabla cada vez que necesitamos conocer esa cantidad no es conveniente porque en tablas que tienen centenas de miles o millones de filas se demorará mucho tiempo obtener el resultado. Es muchísimo más conveniente tener una tabla cuya misión sea guardar las cantidades de filas que tienen las demás tablas, de esta manera la consulta será rapidísima porque esa tabla (llamada RECCOUNT en este ejemplo) tiene pocas filas y además se puede tener un índice en ella con lo cual se conseguirá que la consulta sea instantánea.

La desventaja de esta técnica es que implica mayor trabajo, porque hay que escribir un trigger AFTER INSERT y un trigger AFTER DELETE para cada tabla que nos interesa, pero como la ganancia en velocidad es impresionante, bien vale la pena el pequeño esfuerzo adicional.

Artículos relacionados:

Un stored procedure para guardar la cantidad de registros de cada tabla

El índice del blog Firebird21

Enmascarando los stored procedures

2 comentarios

Se llama “enmascarar” al hecho de llamar de una manera distinta a una función, rutina, clase o stored procedure. Las ventajas de enmascarar son que la llamada a la función, rutina, clase o stored procedure subyacente resulta más fácil para el programador, le permite escribir menos código fuente y además le asegura que siempre todo esté correcto.

Ejemplo:

Tenemos una tabla llamada MOVIMCAB (cabecera de movimientos) cuya estructura (resumida, por supuesto) es la siguiente:

ENMASCARAR2

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

y que tiene (entre otros) a estos datos:

ENMASCARAR1

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

Para saber si un determinado número de documento existe podríamos escribir un stored procedure similar al siguiente:

CREATE PROCEDURE EXISTE_NUMERO_DOCUMENTO(
   tnCodSuc TYPE OF COLUMN MOVIMCAB.MVC_CODSUC,
   tnTipDoc TYPE OF COLUMN MOVIMCAB.MVC_TIPDOC,
   tcNroDoc TYPE OF COLUMN MOVIMCAB.MVC_NRODOC)
RETURNS(
   ftcExisteDocumento D_BOOLEAN)
AS
  DECLARE VARIABLE lnIdenti TYPE OF COLUMN MOVIMCAB.MVC_IDENTI;
BEGIN

   lnIdenti = (SELECT
                  FIRST 1
                  MVC_IDENTI
               FROM
                  MOVIMCAB
               WHERE
                  MVC_CODSUC = :tnCodSuc AND
                  MVC_TIPDOC = :tnTipDoc AND
                  MVC_NRODOC = :tcNroDoc);

   ftcExisteDocumento = iif(lnIdenti > 0, 'T', 'F');

END;

Donde el dominio D_BOOLEAN está definido como:

CREATE DOMAIN D_BOOLEAN AS
   CHAR(1)
   CHECK (VALUE = 'F' OR VALUE = 'T');

Entonces, si desde nuestro programa escrito en Visual FoxPro queremos saber si un número de documento ya existe podríamos escribir algo así:

Local lcAlias, lcComando, llComandoOK, llExisteDocumento
Private pnCodSuc, pnTipDoc, pcNroDoc

   lcAlias = Alias()

   pnCodSuc = 0
   pnTipDoc = 1
   pcNroDoc = '001-001-0000001'

   lcComando = "EXECUTE PROCEDURE EXISTE_NUMERO_DOCUMENTO(?pnCodSuc, ?pnTipDoc, ?pcNroDoc)"

   llComandoOK = SQLEXEC(gnHandle, lcComando)

   llExisteDocumento = llComandoOK .and. ftcExisteDocumento = "T"

   if !Empty(lcAlias)
     select (lcAlias)
  endif

Ahora bien, supongamos que debemos averiguar si un número de documento existe no una vez sino varias veces. Escribir siempre los comandos mostrados arriba sería engorroso y una pérdida de tiempo y además propenso a errores. Mucho mejor sería enmascarar esos comandos con una función, tal como la siguiente:

FUNCTION ExisteDocumento
Parameters tnCodSuc, tnTipDoc, tcNroDoc
Local lcAlias, lcComando, llComandoOK, llExisteDocumento

   lcAlias = Alias()

   lcComando = "EXECUTE PROCEDURE EXISTE_NUMERO_DOCUMENTO(?tnCodSuc, ?tnTipDoc, ?tcNroDoc)"

   llComandoOK = SQLEXEC(gnHandle, lcComando)

   llExisteDocumento = llComandoOK .and. ftcExisteDocumento = "T"

   if !Empty(lcAlias)
      select (lcAlias)
   endif

Return(llExisteDocumento)

Y a la cual podríamos llamar así:

Local lnCodSuc, lnTipDoc, lcNroDoc, llDocumentoExiste

   lnCodSuc = 0
   lnTipDoc = 1
   lcNroDoc = '001-001-0000001'

   llDocumentoExiste = ExisteDocumento(lnCodSuc, lnTipDoc, lcNroDoc)

De esta manera, la función ExisteDocumento() de Visual FoxPro está “enmascarando” al stored procedure EXISTE_NUMERO_DOCUMENTO del Firebird.

Conclusión:

“Enmascarar” a los stored procedures nos permite escribir menos código fuente y además código fuente más seguro porque lo único que debemos verificar bien es que la función enmascaradora sea correcta. Si esa función está bien ya no deberemos preocuparnos por las llamadas al stored procedure correspondiente porque la función se encargará de todas las tareas.

Artículo relacionado:

El índice del blog Firebird21

Stored procedures recursivos

Deja un comentario

Con Firebird podemos escribir stored procedures recursivos, si los necesitamos.

¿Qué significa la recursividad?

Que el mismo stored procedure se ejecuta una vez, y otra vez, y otra vez, y otra vez … hasta que se cumple una condición que lo detiene.

¿Por qué usar la recursividad?

Todos los algoritmos recursivos pueden escribirse también sin usar recursividad, no es obligatorio usarla, en ningún caso. La ventaja es que en muchos casos los algoritmos recursivos son mucho más cortos y más fáciles de entender. También pueden ser más rápidos. En contrapartida casi siempre ocupan más memoria que sus equivalentes no recursivos.

Hay gente a quien le cuesta mucho pensar en forma recursiva y hay gente a quien le resulta muy fácil pensar así. Si para tí no es difícil pensar en forma recursiva entonces que el Firebird te provea de esta característica te puede ser muy útil.

¿Qué se debe tener en cuenta al escribir un stored procedure recursivo?

  1. Que debe existir una condición para que la recursividad se detenga
  2. Que cada vez que se ejecuta el stored procedure se debe estar más cerca de esa condición

Ejemplo 1:

Un ejemplo clásico para mostrar la recursividad es el utilizado para hallar el factorial de un número. Como quizás recordarás, el factorial de un número es ese número multiplicado por todos los anteriores números naturales, desde el 1.

El factorial de 1 es 1

El factorial de 2 es 1 * 2 = 2

El factorial de 3 es 1 * 2 * 3 = 6

El factorial de 4 es 1 * 2 * 3 * 4 = 24

El factorial de 5 es 1 * 2 * 3 * 4 * 5 = 120

El factorial de 6 es 1 * 2 * 3 * 4 * 5 * 6 = 720

y así sucesivamente.

SET TERM ^ ;

CREATE PROCEDURE FACTORIAL(
   tnNumero INTEGER)
RETURNS(
   ftnResultado DOUBLE PRECISION)
AS
BEGIN

   IF (tnNumero = 1) THEN BEGIN
      ftnResultado = 1;
      SUSPEND;
   END ELSE BEGIN
      EXECUTE PROCEDURE FACTORIAL (tnNumero - 1) RETURNING_VALUES ftnResultado;
      ftnResultado = ftnResultado * tnNumero;
      SUSPEND;
   END

END^

SET TERM ; ^

Aquí la condición de fin es que el número recibido como parámetro de entrada sea 1. El stored procedure se va ejecutando cada vez con el número anterior, hasta que se llega al 1 y allí se termina. Por ejemplo, si le envías el número 6, el stored procedure se ejecutará 6 veces y sus parámetros de entrada serán: 6, 5, 4, 3, 2, 1

Ejemplo 2:

Otro ejemplo clásico es hallar el número de Fibonacci. Esta es una serie infinita de números naturales. La serie empieza con los números 0 y 1. A partir de allí, cada número es la suma de los dos anteriores. Esto nos da:

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, etc.

SET TERM ^ ;

CREATE PROCEDURE FIBONACCI(
   tnNumero INTEGER)
RETURNS(
   ftnResultado DOUBLE PRECISION)
AS
   DECLARE VARIABLE lnNumero1 INTEGER;
   DECLARE VARIABLE lnNumero2 INTEGER;
BEGIN

   IF (tnNumero < 2) THEN BEGIN
      ftnResultado = tnNumero;
      SUSPEND;
   END ELSE BEGIN
      EXECUTE PROCEDURE FIBONACCI(tnNumero - 1) RETURNING_VALUES lnNumero1;
      EXECUTE PROCEDURE FIBONACCI(tnNumero - 2) RETURNING_VALUES lnNumero2;
      ftnResultado = lnNumero1 + lnNumero2;
      SUSPEND;
   END

END^

SET TERM ; ^

En este caso la recursividad se detendrá cuando el parámetro de entrada recibido sea menor que 2.

CUIDADO:

Siempre antes de llamar a un stored procedure recursivo hay que validar que el número (o los números) que se envía como parámetro de entrada no provoque una recursividad infinita. En el ejemplo del factorial algo así ocurriría si se envía como parámetro de entrada a un número negativo. ¿Por qué? porque en ese caso en lugar de acercarse a  la condición de fin, cada vez se alejará más. Por ejemplo, si le enviamos como parámetro de entrada al número -6, sus siguientes valores serán -7, -8, -9, -10, – 11, -12, etc., o sea que en lugar de acercarse a la condición de fin (que el parámetro de entrada sea 1) se aleja cada vez más. Al enviarle como parámetro de entrada a un número negativo, cada recursión se aleja más de la condición de fin.

Artículo relacionado:

El índice del blog Firebird21

La notación camello

3 comentarios

Cuando escribimos nuestros stored procedures generalmente necesitamos usar algunas variables: parámetros de entrada, parámetros de salida, variables locales. Estas últimas también las solemos usar en los triggers.

¿Cómo las nombramos, cómo distinguimos si en una variable se guardarán caracteres, números, fechas, etc.?

Si eso no nos preocupa en lo absoluto, entonces podríamos tener algo como esto:

NOMBRE = ‘Este es un nombre’;

NUMERO = 5487;

VAR1 = 456;

VAR2 = ‘Salario mensual’;

pero usar esa notación cuando el stored procedure o el trigger es largo y tenemos muchas variables se presta a malinterpretaciones y la lectura de nuestro código fuente puede demorar más de lo previsto. Y ni que decir si no lo leemos nosotros sino otra persona, se le dificultará entender lo que escribimos.

Para facilitar la lectura del código fuente existe algo que se llama notación camello y que como verás es empleada en este blog.

En la notación camello (llamada así porque parecen las jorobas de un camello) los nombres de cada palabra empiezan con mayúscula y el resto se escribe con minúsculas. No se usan puntos ni guiones para separar las palabras. Además, para saber el tipo de variable se utiliza un prefijo. En este blog los prefijos usados son:

f = Firebird

t = parámetro de entrada o de salida

l = variable local

c = caracter

n = número

d = fecha

h = hora

Ejemplos:

tcNumeroCuenta (parámetro de entrada o de salida, de tipo caracter)

tnSaldoActual (parámetro de entrada o de salida, de tipo numérico)

lnCantidad (variable local, de tipo numérico)

lcEsCuentaAsentable (variable local, de tipo caracter)

tdFechaInicial (parámetro de entrada o salida, de tipo fecha)

ftnCantidadProductosEnOferta (parámetro de salida, de tipo numérico, que se devolverá al lenguaje de programación)

ftcNumeroUltimaFactura (parámetro de salida, de tipo carácter, que se devolverá al lenguaje de programación)

¿Por qué algunos parámetros de salida tienen el prefijo “f”?

Un stored procedure puede ser llamado desde otro stored procedure o desde un lenguaje de programación (Visual FoxPro, Visual Basic, Delphi, C, C++, Java, etc.). Cuando se llama desde un lenguaje de programación donde también se usa la notación camello puede ocurrir un conflicto porque en ese lenguaje de programación se podría estar usando una variable idéntica. Por ejemplo, si en tu programa tienes una variable llamada tnSaldoActual y tu stored procedure devuelve un parámetro llamado tnSaldoActual habrá un conflicto y podrías encontrarte con un problema por esa causa. Para evitar que tal cosa ocurra, los parámetros de salida que pueden ser usados desde un lenguaje de programación son prefijados con la letra “f”, que significa Firebird. Y entonces, no hay confusión posible.

Conclusión:

Usar una notación consistente y siempre la misma notación redunda en un gran beneficio cuando queremos leer el código fuente escrito en los stored procedures y en los triggers. La notación camello es muy clara y muy entendible, por eso fue elegida por el autor de este blog. En esta notación la inicial de cada palabra se escribe en mayúsculas y las demás letras en minúsculas. No se usan guiones para separar a las palabras. Para indicar el tipo de variable se usan prefijos predeterminados los cuales siempre se escriben en minúsculas.

Artículo relacionado:

El índice del blog Firebird21

Older Entries Newer Entries