Crear una función en Firebird 2.x que nos devuelva una fecha en cualquier formato

3 comentarios

A veces podríamos llegar a necesitar representar a las fechas en diferentes formatos, por ejemplo a la fecha 28 de marzo de 2017 podríamos querer presentarla como:

  • 28/03/2017
  • 03/28/2017
  • 2017/03/28
  • 28-03-2017
  • 28-MAR-2017
  • 28/MAR/2017
  • 28 de marzo de 2017
  • etc.

¿Cómo podemos obtener la fecha en el formato que nos interesa?

En Firebird no existe una función llamada DATE_FORMAT() o equivalente como sí existe en Oracle y en MySQL, por ejemplo. Pero si necesitamos esa función entonces simplemente … podemos crearla.

Ya sabemos como escribir un stored procedure y usarlo como si de una función se tratara, lo hemos visto en el artículo:

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

Entonces, usaremos esa técnica para escribir nuestra propia función DATE_FORMAT()

Listado 1.

CREATE PROCEDURE DATE_FORMAT(
   tdFechax DATE,
   tcFormat VARCHAR(128))
RETURNS(
   ftcFecha VARCHAR(128))
AS
   DECLARE VARIABLE lnDia SMALLINT;
   DECLARE VARIABLE lnMes SMALLINT;
   DECLARE VARIABLE lnAno SMALLINT;
   DECLARE VARIABLE lcMes VARCHAR(128);
BEGIN

   tcFormat = UPPER(tcFormat);

   lnDia = EXTRACT(DAY FROM tdFechax);
   lnMes = EXTRACT(MONTH FROM tdFechax);
   lnAno = EXTRACT(YEAR FROM tdFechax);

   IF (tcFormat = '%D/%M/%Y') THEN
      ftcFecha = LPAD(lnDia, 2, '0') || '/' || LPAD(lnMes, 2, '0') || '/' || lnAno;

   IF (tcFormat = '%M/%D/%Y') THEN
      ftcFecha = LPAD(lnMes, 2, '0') || '/' || LPAD(lnDia, 2, '0') || '/' || lnAno;

   IF (tcFormat = '%Y/%M/%D') THEN
      ftcFecha = lnAno || '/' || LPAD(lnMes, 2, '0') || '/' || LPAD(lnDia, 2, '0');

   IF (tcFormat = '%D-%M-%Y') THEN
      ftcFecha = LPAD(lnDia, 2, '0') || '-' || LPAD(lnMes, 2, '0') || '-' || lnAno;

   IF (tcFormat = '%M-%D-%Y') THEN
      ftcFecha = LPAD(lnMes, 2, '0') || '-' || LPAD(lnDia, 2, '0') || '-' || lnAno;

   IF (tcFormat = '%Y-%M-%D') THEN
      ftcFecha = lnAno || '-' || LPAD(lnMes, 2, '0') || '-' || LPAD(lnDia, 2, '0');

   IF (tcFormat = 'NAME') THEN BEGIN
      lcMes = '';
      lcMes = IIF(lnMes =  1, 'Enero'     , lcMes);
      lcMes = IIF(lnMes =  2, 'Febrero'   , lcMes);
      lcMes = IIF(lnMes =  3, 'Marzo'     , lcMes);
      lcMes = IIF(lnMes =  4, 'Abril'     , lcMes);
      lcMes = IIF(lnMes =  5, 'Mayo'      , lcMes);
      lcMes = IIF(lnMes =  6, 'Junio'     , lcMes);
      lcMes = IIF(lnMes =  7, 'Julio'     , lcMes);
      lcMes = IIF(lnMes =  8, 'Agosto'    , lcMes);
      lcMes = IIF(lnMes =  9, 'Septiembre', lcMes);
      lcMes = IIF(lnMes = 10, 'Octubre'   , lcMes);
      lcMes = IIF(lnMes = 11, 'Noviembre' , lcMes);
      lcMes = IIF(lnMes = 12, 'Diciembre' , lcMes);
      ftcFecha = lnDia || ' de ' || lcMes || ' de ' || lnAno;
   END

   SUSPEND;

END;

Por supuesto que podrías agregar más formatos de fecha si lo deseas, en el Listado 1. se mostraron algunas de las posibilidades.

Ahora, cuando queremos ver a una fecha con alguno de los formatos que definimos en nuestro stored procedure lo haríamos así:

Listado 2.

SELECT
   F.ftcFecha
FROM
   RDB$DATABASE
LEFT JOIN
   DATE_FORMAT(CURRENT_DATE, '%D/%M/%Y') F
      ON 1 = 1

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

Listado 3.

SELECT
   F.ftcFecha
FROM
   RDB$DATABASE
LEFT JOIN
   DATE_FORMAT(CURRENT_DATE, '%Y-%M-%D') F
      ON 1 = 1

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

Listado 4.

SELECT
   F.ftcFecha
FROM
   RDB$DATABASE
LEFT JOIN
   DATE_FORMAT(CURRENT_DATE, 'NAME') F
      ON 1 = 1

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

Conclusión:

Aunque Firebird nativamente no tiene una función DATE_FORMAT o equivalente, es bastante sencillo realizar una, tal y como hemos podido ver en este artículo. Y aunque en nuestros ejemplos hemos usado la tabla RDB$DATABASE no es ninguna obligación utilizarla, puedes usar cualquier tabla que desees, también cualquier fecha que desees, y también cualquier formato que desees. Si ese formato no se encuentra en el Listado 1., pues lo agregas y listo.

Artículos relacionados:

Usando un stored procedure como una función

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Stored procedures con cantidad de parámetros variables

2 comentarios

En ocasiones necesitamos escribir un stored procedure que recibirá varios parámetros de entrada pero cuando llamamos a ese stored procedure la cantidad de parámetros que le enviamos puede variar, no es siempre la misma cantidad y ni siquiera sabemos de antemano cuales y cuantos parámetros necesitaremos.

¿Podemos escribir un stored procedure que acepte una cantidad variable de parámetros, existe una forma de hacerlo?

Ejemplo: Necesitamos consultar a una tabla de VENDEDORES pero a veces el dato que conocemos es su Identificador, a veces es el Nombre, y a veces es la Fecha de Nacimiento.

SP01

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

Listado 1.

CREATE PROCEDURE SP_CONSULTAR_VENDEDORES (
   tnParam1 SMALLINT,
   tcParam2 VARCHAR(40),
   tdParam3 DATE)
RETURNS(
   ftnIdenti TYPE OF COLUMN VENDEDORES.VEN_IDENTI,
   ftcNombre TYPE OF COLUMN VENDEDORES.VEN_NOMBRE,
   ftdFecNac TYPE OF COLUMN VENDEDORES.VEN_FECNAC)
AS
   DECLARE VARIABLE lnValor1 SMALLINT;
   DECLARE VARIABLE lcValor2 VARCHAR(40);
   DECLARE VARIABLE ldValor3 DATE;
BEGIN

   FOR SELECT
      VEN_IDENTI,
      VEN_NOMBRE,
      VEN_FECNAC
   FROM
      VENDEDORES
   WHERE
      VEN_IDENTI = COALESCE(:tnParam1, VEN_IDENTI) AND
      VEN_NOMBRE = COALESCE(:tcParam2, VEN_NOMBRE) AND
      VEN_FECNAC = COALESCE(:tdParam3, VEN_FECNAC)
   INTO
      :lnValor1,
      :lcValor2,
      :ldValor3
   DO BEGIN
      ftnIdenti = lnValor1;
      ftcNombre = lcValor2;
      ftdFecNac = ldValor3;
      SUSPEND;
   END

END;

En este caso se trata de un stored procedure seleccionable pero exactamente la misma lógica usaríamos si se tratara de un stored procedure ejecutable. OBSERVACIÓN: en un stored procedure tan simple como este no es necesario usar variables locales, podríamos haber usado solamente los parámetros de salida, pero se muestra el caso general que puede ser más útil.

Entonces, podríamos escribir algo como:

Listado 2.

SELECT
   *
FROM
   SP_CONSULTAR_VENDEDORES(NULL, 'CLAUDIA', NULL)


Y obtendríamos lo siguiente:

SP02

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

Y si escribimos algo como:

Listado 3.

SELECT
   *
FROM
   SP_CONSULTAR_VENDEDORES(NULL, NULL, CAST('30/OCT/1994' AS DATE))

entonces obtendríamos lo siguiente:

SP03

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

Como ves, los parámetros desconocidos o innecesarios completamos con NULL, porque este stored procedure debe recibir sí o sí 3 parámetros de entrada.

Conclusión:

Si necesitamos varios stored procedures que son muy similares y la única diferencia entre ellos está en los parámetros de entrada entonces lo más conveniente es escribir un solo stored procedure y al llamarlo hacerlo con el parámetro o los parámetros adecuados en ese momento.

El truco está en usar la función COALESCE() para saber si un parámetro de entrada es NULL o tiene un valor distinto que NULL. Así sabremos si se le asignó un valor, o no.

Artículos relacionados:

La función COALESCE()

El índice del blog Firebird21

El foro del blog Firebird21

Actualización de stored procedures y triggers

2 comentarios

¿En qué momento se actualiza un stored procedure o un trigger?

Tú cambias el contenido de un stored procedure o de un trigger, luego ejecutas el COMMIT correspondiente, y sin embargo parece que la actualización no ha funcionado.

¿Por qué?

Porque la actualización se realiza solamente cuando el caché está vacío.

En Classic eso ocurre al cerrar la conexión actual.

En SuperServer, como el caché es compartido, eso ocurre cuando se cerraron todas las conexiones.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Agregando filas adicionales

7 comentarios

Un lector del foro de este blog hizo un pedido interesante: mostrar una consulta de una forma especial.

CIUDADES

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

O sea que se tienen 3 tablas: PAISES, ESTADOS, CIUDADES, relacionadas mediante sus identificadores, tal y como debe ser.

Pero ¿cómo hacemos para mostrar las filas de la forma pedida?

Hay varias alternativas, en este artículo mostraremos una de ellas.

La idea es agregar a la tabla de CIUDADES, dos columnas:

  • Si mostraremos el nombre del País
  • Si mostraremos el nombre del Estado

Nuestras tablas por lo tanto tendrán las siguientes estructuras:

CIUDADES2

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

CIUDADES3

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

CIUDADES4.png

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

Los datos contenidos en esas tablas podemos ver a continuación:

CIUDADES6

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

CIUDADES7

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

CIUDADES8

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

Una consulta simple sería la siguiente:

Listado 1.

SELECT
   PAI_NOMBRE,
   EST_NOMBRE,
   CIU_NOMBRE
FROM
   CIUDADES
JOIN
   PAISES
      ON CIU_IDEPAI = PAI_IDENTI
JOIN
   ESTADOS
      ON CIU_IDEPAI = EST_IDEPAI AND
         CIU_IDEEST = EST_IDENTI
ORDER BY
   CIU_IDEPAI,
   CIU_IDEEST,
   CIU_IDENTI

CIUDADES5

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

Pero eso no es lo que queremos obtener, porque los nombres de los Países y de los Estados están repetidos, y lo que queremos es verlos una sola vez.

¿Y entonces, qué hacemos?

Si nos fijamos en la Captura 1. veremos que se muestran en total 18 filas aunque en la tabla de CIUDADES solamente hay 12 filas. Eso significa que se están mostrando 6 filas más. Por lo tanto, lo que debemos hacer es agregar esas 6 filas adicionales.

¿Y cómo lo conseguimos?

Mediante una construcción del Firebird que se llama stored procedure seleccionable.

Listado 2.

CREATE PROCEDURE SP_MOSTRAR_CIUDADES
   RETURNS(
      ftcNombrePais   TYPE OF COLUMN PAISES.PAI_NOMBRE,
      ftcNombreEstado TYPE OF COLUMN ESTADOS.EST_NOMBRE,
      ftcNombreCiudad TYPE OF COLUMN CIUDADES.CIU_NOMBRE)
AS
   DECLARE VARIABLE lcNombrePais    TYPE OF COLUMN PAISES.PAI_NOMBRE;
   DECLARE VARIABLE lcNombreEstado  TYPE OF COLUMN ESTADOS.EST_NOMBRE;
   DECLARE VARIABLE lcNombreCiudad  TYPE OF COLUMN CIUDADES.CIU_NOMBRE;
   DECLARE VARIABLE lcMostrarPais   TYPE OF COLUMN CIUDADES.CIU_MOSPAI;
   DECLARE VARIABLE lcMostrarEstado TYPE OF COLUMN CIUDADES.CIU_MOSEST;
BEGIN

   FOR SELECT
      PAI_NOMBRE,
      EST_NOMBRE,
      CIU_NOMBRE,
      CIU_MOSPAI,
      CIU_MOSEST
   FROM
      CIUDADES
   JOIN
      PAISES
         ON CIU_IDEPAI = PAI_IDENTI
   JOIN
      ESTADOS
         ON CIU_IDEPAI = EST_IDEPAI AND
            CIU_IDEEST = EST_IDENTI
   ORDER BY
      CIU_IDEPAI,
      CIU_IDEEST,
      CIU_IDENTI
   INTO
      :lcNombrePais,
      :lcNombreEstado,
      :lcNombreCiudad,
      :lcMostrarPais,
      :lcMostrarEstado
   DO BEGIN
      IF (lcMostrarPais = 'T') THEN BEGIN
         ftcNombrePais   = lcNombrePais;
         ftcNombreEstado = '';
         ftcNombreCiudad = '';
         SUSPEND;
      END
      IF (lcMostrarEstado = 'T') THEN BEGIN
         ftcNombrePais   = '';
         ftcNombreEstado = lcNombreEstado;
         ftcNombreCiudad = '';
         SUSPEND;
      END
      ftcNombrePais   = '';
      ftcNombreEstado = '';
      ftcNombreCiudad = lcNombreCiudad;
      SUSPEND;
   END

END;

Explicación:

Como necesitamos agregar 6 filas lo podemos conseguir fácilmente usando un stored procedure seleccionable.

Obtenemos y colocamos en variables locales los nombres de los Países, de los Estados, de las Ciudades, y si debemos mostrar el País, y si debemos mostrar el Estado.

Luego, colocamos en los parámetros de salida los valores que deseamos sean devueltos.

Como el stored procedure es seleccionable entonces debemos usar el comando SUSPEND cada vez que deseamos devolver los parámetros de salida.

Listado 3.

SELECT
   *
FROM
   SP_MOSTRAR_CIUDADES

Un stored procedure seleccionable puede ser tratado como si fuera una tabla. Al ejecutar el Listado 3. obtendremos el siguiente resultado.

CIUDADES9

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

Que es exactamente lo que deseábamos obtener.

Artículos relacionados:

Entendiendo a los stored procedures

Agregando filas adicionales (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Escribiendo un trigger condicional

2 comentarios

Como recordarás, los triggers son ejecutados automáticamente por el Firebird cuando se inserta, actualiza, o elimina una fila de una tabla.

Pero ¿Y si deseas tener un trigger que se ejecuta solamente algunas veces, no siempre?

En ese caso deberías escribir un trigger condicional.

¿Cómo?

Muy fácilmente:

  1. Asegurándote que las operaciones de inserción, actualización y borrado sean ejecutadas solamente por usuarios comunes, no por el usuario SYSDBA ni por el creador de la Base de Datos.
  2. Realizando las tareas solamente cuando se cumple la condición que has impuesto, algo como:

TRIGGER_1

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

Artículos relacionados:

Entendiendo a los triggers

Escribiendo un trigger

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Un stored procedure que retorna una cantidad variable de valores

Deja un comentario

Un stored procedure puede devolver cero valores, un valor, o muchos valores.

La forma más sencilla de hacer que nos devuelva solamente algunos valores, no todos, es llamarlo mediante un SELECT.

O sea que nuestro stored procedure deberá ser seleccionable.

Lo cual se consigue poniendo la instrucción SUSPEND dentro de él.

Entonces, si nuestro stored procedure devuelve los valores ftnValor1, ftnValor2, ftnValor3, ftnValor4 y solamente nos interesa obtener ftnValor1 y ftnValor3, escribiríamos algo como:

SELECT
   ftnValor1,
   ftnValor3
FROM
   MiStoredProcedureSeleccionable
WHERE
   MiCondición

Artículos relacionados:

Entendiendo a los stored procedures

¿Por qué usar stored procedures?

Usando un stored procedure como una función

Escribiendo un stored procedure

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

El índice del blog Firebird21

El foro del blog Firebird21

 

 

 

 

¿Por qué usar stored procedures?

3 comentarios

Si recién estás empezando con Firebird o con bases de datos relacionales entonces es posible que te hayas hecho la pregunta que da título a este artículo.

Con todo derecho podrías preguntarte: ¿para qué me servirá usar stored procedures? ¿qué ganaré si los uso?

En primer lugar hay que aclarar que no es obligatorio usarlos. Todo lo que puedes hacer dentro de un stored procedure también puedes hacerlo en el código fuente de tu lenguaje de programación.

Sin embargo, las ventajas de usar stored procedures son varias y muy importantes:

  1. Rapidez
  2. Seguridad
  3. Independencia
  4. Trabajo en equipo
  5. Encapsulamiento
  6. Capas separadas

Veamos cada una de esas ventajas con más detalle:

Ventaja 1. Rapidez

Esta ventaja a su vez se divide en tres partes:

a) Procesamiento en el Servidor

b) Optimización de los resultados

c) Velocidad en la codificación

Procesamiento en el Servidor

Procesar los datos en el Servidor es muy rápido por varios motivos: por un lado, los datos no deben estar viajando por la red, solamente los resultados finales lo harán. Por ejemplo, si deseas calcular los impuestos a pagar este mes, y la tabla tiene 250.000 filas, el procesamiento de esas 250.000 filas se hará en la memoria de la computadora donde se encuentra el Servidor, no deberán estar viajando por la red para llegar a la computadora Cliente y ser procesadas allí, así que la ganancia en la velocidad de respuesta será muy notoria. Por otro lado, generalmente la computadora donde se encuentra el Servidor es más poderosa que todas (o la mayoría) de las computadoras que usan los Clientes y por lo tanto procesará los datos más rápidamente. Así que sumadas ambas ventajas, la ganancia en velocidad puede ser muy grande.

Optimización de los resultados

Los programas de administración gráfica (como el EMS SQL Manager) te pueden mostrar gráficamente el uso de los índices, y la cantidad de filas de cada tabla que fueron procesadas. Mirando esos gráficos puedes notar que se están procesando más filas de las necesarias o que no se están usando los índices correctos. Entonces, tomando eso en cuenta puedes corregir tu stored procedure y conseguir que realice sus tareas más rápidamente.

SP01

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

En la Captura 1. podemos ver la cantidad de filas que se procesaron en cada tabla. Si conocemos la cantidad de filas de cada tabla (lo cual se puede averiguar muy fácilmente con un SELECT COUNT(*) ) y lo que hace nuestro stored procedure entonces podemos notar que alguna tabla está procesando más filas de las necesarias, o que no usa un índice cuando sí debería usarlo.

Velocidad en la codificación

Cuando escribes un stored procedure allí mismo puedes verificar que esté funcionando correctamente, con un solo clic ya lo ejecutas. Y si no está funcionando correctamente, entonces lo corriges y lo vuelves a ejecutar hasta conseguir los resultados deseados. Hacer eso mismo en tu lenguaje de programación te hará demorar más tiempo porque tendrás que ejecutar tu programa, hacer unos cuantos clics aquí y allá y recién después de eso obtendrás los resultados.

Ventaja 2. Seguridad

En un stored procedure solamente puedes usar tablas y columnas que existen en la Base de Datos. Si escribes mal el nombre de una tabla o de una columna, no podrás compilar el stored procedure. De eso se deduce que los errores serán encontrados en tiempo de compilación y no en tiempo de ejecución. Encontrar errores en tiempo de ejecución puede ser muy malo si es un usuario quien los encuentra. Está muy apurado, necesita procesar algo, y recibe el mensaje: “Table unknown” o el mensaje: “Column unknown”. De seguro que no le va a gustar. Algo así no puede pasar si se usó un stored procedure porque el Firebird no permite compilar un stored procedure que tenga esos errores.

Ventaja 3. Independencia

Como los stored procedures se guardan dentro de la Base de Datos están disponibles para ser usados por cualquier programa que se conecte a esa Base de Datos. Ese programa puede estar escrito en cualquier lenguaje de programación (Visual FoxPro, Visual Basic, Delphi, Java, C, C++, etc.).

Por lo tanto, si hoy usas un lenguaje de programación y dentro de unos días decides usar otro lenguaje de programación nada tendrás que cambiar dentro de la Base de Datos, todo estará allí, esperando ser usado.

Y en consecuencia no dependes de un lenguaje de programación, eres independiente, libre de usar cualquier lenguaje que desees.

Ventaja 4. Trabajo en equipo

Esta ventaja está muy relacionada con la anterior. Como los stored procedures son independientes de los lenguajes de programación entonces una persona puede programar en Visual FoxPro, otra persona puede programar en Visual Basic, otra persona puede programar en Delphi, etc. y todos podrán llamar a los mismos stored procedures.

Así, cada programador puede programar en el lenguaje de su preferencia, sin ningún problema.

Además, varias personas podrían estar escribiendo varios stored procedures inclusive al mismo tiempo. Si Juan conoce muy bien el tema de los impuestos entonces a él se le asigna esa tarea, y si María conoce muy bien como procesar los saldos entonces a ella se le asigna esa tarea.

Ventaja 5. Encapsulamiento

Después de compilar un stored procedure se puede dar derechos de ejecución sobre ese stored procedure a un usuario o a un rol o a un programa, quienes nunca verán el código fuente de dicho stored procedure, simplemente lo usarán.

Ventaja 6. Capas separadas

La programación en 3 capas es una metodología muy útil a seguir para construir aplicaciones de calidad porque cada capa se encarga exclusivamente de una tarea.

En la capa de datos se procesa todo lo que tenga que ver con tablas y columnas de una Base de Datos.

La idea es la siguiente: “todo lo que puede procesarse dentro de una Base de Datos debe procesarse dentro de esa Base de Datos”.

La ventaja de la programación en 3 capas es que no se tiene código mezclado. Cada capa (capa de presentación, capa de negocios, capa de datos) siempre se encarga de una sola tarea. Es más fácil de entender que si está todo mezclado.

En el caso de la capa de datos, está se encargará de:

  • Insertar, actualizar, o borrar filas de las tablas
  • Procesar el contenido de las tablas
  • Devolver a la capa de negocios los resultados que ésta solicitó

Conclusión:

Aunque no es obligatorio usar stored procedures, es altamente recomendable utilizarlos porque las ventajas son muchas y muy grandes.

Los principiantes en Firebird o en bases de datos relacionales pueden ser un poco reacios a su utilización pero una vez que empiezan a usarlos se dan cuenta de lo muy provechoso que les resulta.

NOTA: ser principiante no es un defecto, todos hemos sido principiantes alguna vez, nadie nació siendo experto en Firebird, todo lo que sabemos lo hemos aprendido mucho después de nacer.

Artículos relacionados:

Entendiendo a los stored procedures

Escribiendo un stored procedure

Similitudes y diferencias entre stored procedures y triggers

¿Es conveniente usar stored procedures?

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries