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

 

 

 

 

Valores de las variables en un stored procedure

Deja un comentario

Leyendo un comentario que escribió Jaume en el artículo:

Entendiendo a los Stored Procedures

pensé que alguien más podría tener esa misma confusión y por tal motivo estoy escribiendo este artículo. Es para aclarar el comportamiento de las variables en un stored procedure.

Veamos un ejemplo de un stored procedure muy sencillo, sólo para mostrar donde puede existir confusión:

CREATE PROCEDURE VALOR_ANTERIOR
   RETURNS(
      tcNombre TYPE OF COLUMN CLIENTES.CLI_NOMBRE)
AS
BEGIN

   tcNombre = 'PRUEBA';

   FOR SELECT
      CLI_NOMBRE
   FROM
      CLIENTES
   WHERE
      CLI_IDENTI > 1000000
   INTO
      :tcNombre
   DO
      SUSPEND;

   IF (tcNombre = 'PRUEBA') THEN
      SUSPEND;

END;

Evidentemente este es un stored procedure seleccionable (sabemos eso porque tiene el comando SUSPEND dentro de él). Y la pregunta es: ¿qué valor o valores devolverá este stored procedure cuando lo ejecutemos con el comando SELECT?

SELECT
   *
FROM
   VALOR_ANTERIOR

Bien, eso depende de si hay alguna fila que tenga en la columna CLI_IDENTI un valor mayor que 1000000 ó no. Si hay una o más filas, entonces devolverá el nombre de los respectivos clientes pero si ninguna fila cumple con esa condición entonces devolverá la palabra ‘PRUEBA’.

¿Por qué devuelve ‘PRUEBA’ y no devuelve NULL?

Uno podría pensar que si ninguna fila cumple con la condición entonces debería devolver NULL, sin embargo no es así, devuelve el valor que anteriormente tenía la variable tcNombre, en este caso ‘PRUEBA’. ¿Por qué eso?

Eso es porque un SELECT puede devolver cero filas, eso ocurre cuando la tabla no tiene filas o cuando ninguna fila cumple con la condición. En ese caso el valor que tenían las variables asignadas por el SELECT (las que se encuentran después de la cláusula INTO) no puede cambiar ya que ninguna fila fue retornada. La asignación a esas variables se hace solamente después de obtener una fila, como es lógico.

En consecuencia, si ninguna fila es retornada todas esas variables mantienen el valor que tenían anteriormente, porque ninguna asignación fue hecha a ellas. En este caso tcNombre seguirá valiendo ‘PRUEBA’.

Para que tcNombre valiera NULL, el Firebird tendría que asignarle NULL antes de ejecutar al SELECT pero ¿para qué haría eso? sería una causa de conflicto porque una columna de un SELECT puede legítimamente valer NULL y en ese caso ¿cómo se diferenciaría entre un NULL previamente asignado y un NULL como valor legítimo de una columna? No habría forma de diferenciar a un NULL del otro, por lo tanto el Firebird hace lo más lógico, coherente, y racional: asignarle valores a las variables solamente después de obtener una fila del SELECT, nunca antes.

Así que, el Firebird hace lo correcto.

Por lo tanto, si un SELECT no retorna filas, todas las variables que se encuentren después de la claúsula INTO mantendrán el valor que tenían antes del SELECT.

No asumas que el valor de dichas variables será NULL, porque podría no ser así.

Artículos relacionados:

Entendiendo a los Stored Procedures

El índice del blog Firebird21

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

El índice del blog Firebird21

El foro del blog Firebird21