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:
- Ejecutar solamente una línea
- 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:
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:
- Averigua cual es el último código guardado en la tabla PRODUCTOS
- Convierte ese código (que es de tipo carácter) a INTEGER y luego le suma 1
- 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
- Devuelve el código que se obtuvo en el paso 3.
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
Mar 14, 2014 @ 06:59:22
Truco: Usando SP como una tabla con LEFT JOIN,
En mi caso suelo montar un SP para que me da acumulados de facturas, bases imponibles, cuota de impuestos, IVA en España, suplidos y totales, si quiero añadir estos valores a una SQL tendría que añadir varios sub-select cada uno con el valor que me interesa, o bien hacer un left join, pero a un SP no se le puede llamar con parámetros, ¿como lo resolvemos?
select f.factura, f.cliente, t.base, t.tipo, t.cuota, t.suplido, t.total
form factura f
left join FacturaTotales(f.idFactura) t on 1=1
y listo.
Mar 17, 2014 @ 10:30:46
Hola, antes que nada, felicitaciones por el blog, lo vengo siguiendo desde hace un tiempo, estoy empezando con firebird y realmente es de mucho valor para mi.
Ahora la consulta, se que hay convenciones para los nombres de variables, me dirías cuales se usan acá?. Me refiero pe en Ejemplo 1 tcNumeroFactura, ftcExisteFactura. Me imagino “tc”, tipo caracter, es así?
Saludos
Mar 17, 2014 @ 10:37:55
Hola Angel
Muchas gracias por las felicitaciones.
Yo utilizo la “notación camello” en Firebird porque es la misma que utilizo en los códigos fuente de los programas que escribo con los lenguajes de programación, hago eso para ser consistente, pero tú puedes usar cualquier notación que desees.
La “t” inicial no significa “tipo de datos” sino “parámetro”. Eso me indica que se trata de un parámetro de entrada.
Cuando se trata de un parámetro de salida que se usará solamente dentro del Firebird también se inicia con “t” pero si será usado por un programa escrito en algún lenguaje de programación entonces empieza con “ft”, la “f” significando “Firebird” y la “t” significando “parámetro”.
Puedes leer más sobre la notación utilizada en este blog en este artículo:
https://firebird21.wordpress.com/2013/10/03/la-notacion-camello/
Saludos.
Walter.
Mar 28, 2014 @ 20:23:35
Hola wrov gracias por este aporte, estoy empezando a trabajar con los SP y he leido un poco, tengo algunas dudas aún y necesito algo de ayuda. Debo hacer un SP a una tabla llamada “reserva” que tiene la función de pre-reserva y reserva, la cual se ha llenado anteriormente por un cliente. En esa tabla se guardan los datos de una reserva, el usuario debe introducir un número de vauche para corroborar si el deposito se hizo. En caso de que el usuario no haya depositado se guardan solos los datos de la reserva(fecha_llegada, fecha_salida, datos del cliente, codigo de la habitación, fecha en que se reservo, y la hora) de está manera si otro cliente consulta esa misma fecha, estará ocupada. Ahora bien necesito que esa pre-reserva tenga un periodo de duración de tan solo 24 horas. El SP debe traer desde la BD la hora y el día en que se hizo la reserva, para poder compararla con la hora actual y determinar si han pasado 24 horas, en caso de que hayan pasado 24 horas y el cliente no culmine la reserva con el número de vauche, el SP también debe cambiar el estatus de la reserva y liberar las dos fechas de la BD.
Mar 28, 2014 @ 20:27:49
Entiendo Andrés.
Lo mejor es que escribas un stored procedure que realice lo que quieres, y si no lo consigues entonces me lo muestras para ayudarte.
Saludos.
Walter.
Jun 04, 2014 @ 01:42:37
Gracias muy bna info !!! wrov
Jun 04, 2014 @ 01:43:59
Necesito ayuda no he podido con un sp. gracias
Oct 22, 2014 @ 14:34:36
Hola Don Walter. Un placer saludarle nuevamente y gracias por el articulo. Esta excelente.
Dado el tema especifico de los SP, su desarrollo y aplicacion, quisiera plantearle una duda. En mi caso, cuando voy a ejecutar un procedimiento desde VFP, utilizo la line EXECUTE PROCEDURE EXISTE_NUMERO_FACTURA(‘001-001-0000007’). En todos los aplicativos me ha funcionado bien.
No obstante… mire en un foro de desarrollo por ahi… que los programadores del equipo, ejecutaban un
EXECUTE PROCEDURE MI_PROC(‘001-001-0000007’)
y seguidamente un
CALL PROCEDURE MI_PROC(‘001-001-0000007’)
Realmente me sorprendio proque hasta donde tenia entendido solo bastaba con manda a ejecutar (EXECUTE) el sp y ya. Fue tanta mi curiosidad que decidi probarlo en la inseccion de un registro a una tabla y funciono perfectamente. Por alguna razon esperaba que el SP se ejecutara 2 veces o algo asi… que hiciera 2 insercciones iguales… pero no! Todo funciono bien.
Investigando un poco… encontre informacion sobre CALL PROCEDURE y su sintaxis que es exactamente la misma…! En algunos foros, instruyen el uso de CALL PROCEDURE… y el resultado es el mismo que EXECUTE para ejecutar un SP.
Podria aclararnos un poco sobre el tema? De antemano muchas gracias, como siempre, por sus valiosos aportes.
Desde Managua, Nicaragua!
Julio.
Oct 22, 2014 @ 15:23:54
CALL PROCEDURE no funciona en Firebird, sí en otros SGBD.
Cada SGBD puede inventar los comandos y sentencias de control que desee para sus stored procedures, así como la forma de llamarlos, porque eso no está estandarizado en SQL. Firebird utiliza EXECUTE PROCEDURE, otros usan CALL PROCEDURE, otros simplemente CALL, etc.
Prueba usar CALL PROCEDURE en ISQL, en EMS SQL Manager, etc. y verás que no funciona.
Lo que te sucedió cuando hiciste la prueba es que el EXECUTE PROCEDURE sí funcionó, el CALL PROCEDURE no funcionó, por eso no obtuviste una fila duplicada.
Revisa los mensajes de error.
Saludos.
Walter.
Oct 22, 2014 @ 18:26:40
Hola don Walter. Asi me puse a comprobar y di en el clavo! Eso significa que en el foro estaban equivocados! :O cuando empece a programar con los SP de firebird, me guie con este articulo hace mesesssss… pero esa respuesta que habia encontrado, leyendo, me llamo la atencion! xD Gracias por la aclaracion. Esta respuesta y explicacion no la encontre en ningun otro lado…! Asi ya evacuamos confusiones! 🙂
Muchas gracias por la aclaracion…! Como siempre, es usted muy amable!
Desde Managua, Nicaragua!
Julio