Un ejemplo del uso de funciones en Firebird

Deja un comentario

En este artículo ya habíamos visto que actualmente (versión 2.5.2) el Firebird no dispone de funciones nativamente pero que fácilmente podemos simular que sí las tiene.

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

Ahora, veremos un ejemplo práctico de como usar una función.

Ejemplo. Obteniendo la parte gravada y el impuesto

El Gobierno cobra un impuesto por cada venta que se realiza en el país. El valor monetario de ese impuesto se calcula como un porcentaje sobre lo que se le cobra al cliente. Por lo tanto:

PrecioVenta = ImporteGravado + Impuesto     (1)

Como el Impuesto es siempre un porcentaje sobre el ImporteGravado entonces la fórmula es:

Impuesto = ImporteGravado * PorcentajeImpuesto / 100     (2)

Y reemplazando la fórmula del Impuesto en (1) obtenemos:

PrecioVenta = ImporteGravado + ImporteGravado * PorcentajeImpuesto / 100     (3)

Que también podemos escribir como:

PrecioVenta = ImporteGravado * (1 + PorcentajeImpuesto / 100)     (4)

En una tabla llamada FACTURAS tenemos guardado el PrecioVenta y también el PorcentajeImpuesto y lo que queremos hallar es:

  1. El ImporteGravado
  2. El Impuesto

 Para ello, escribimos el siguiente stored procedure seleccionable:

CREATE PROCEDURE DISCRIMINAR_VENTA(
   tnPrecioVenta NUMERIC(12, 2),
   tnPorcentaje  NUMERIC(5, 2))
RETURNS(
   ftnGravado  NUMERIC(12, 2),
   ftnImpuesto NUMERIC(12, 2))
AS
BEGIN

   -- tnPrecioVenta = ftnGravado * (1 + tnPorcentaje / 100)

   ftnGravado = tnPrecioVenta / (1 + tnPorcentaje / 100);

   ftnImpuesto = ftnGravado * tnPorcentaje / 100;

   SUSPEND;

END;

Al cual podemos llamar de esta forma:

SELECT
   F.FAC_MONTOX,
   D.ftnGravado,
   D.ftnImpuesto,
   D.ftnGravado + D.ftnImpuesto
FROM
   FACTURAS F
LEFT JOIN
   DISCRIMINAR_VENTA(F.FAC_MONTOX, F.FAC_PORIVA) D
      ON 1 = 1

Desde luego que F.FAC_MONTOX y D.ftnGravado + D.ftnImpuesto deben ser idénticos, se escribió a ambos solamente para comprobar lo evidente.

En este caso tenemos una tabla llamada FACTURAS, dentro de la cual tenemos una columna llamada FAC_MONTOX donde guardamos el precio de venta y una columna llamada FAC_PORIVA donde guardamos el porcentaje del impuesto.

Al llamar a la función DISCRIMINAR_VENTA obtenemos la parte gravada y el impuesto que corresponden a cada venta realizada.

Artículos relacionados:

Usando un stored procedure como una función

El índice del blog Firebird21

 

Anuncios

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

 

Un stored procedure para otorgarle TODOS los derechos a un usuario

4 comentarios

En ocasiones queremos que un usuario (o un rol) tenga todos los derechos sobre una Base de Datos aunque sin ser el SYSDBA de ella ni tener el rol RDB$ADMIN. O quizás lo que necesitamos es que tenga la mayoría de los derechos, aunque no todos.

Para este último caso lo que podríamos hacer es otorgarle todos los derechos y luego quitarle algunos de ellos, para que él tenga solamente los derechos que nosotros queremos que tenga.

Tenemos dos formas de conseguir nuestro objetivo:

  1. La forma lenta
  2. La forma rápida

La forma lenta es por cada tabla, por cada vista y por cada stored procedure otorgarle el derecho correspondiente. Desde luego que si las tablas, vistas y stored procedures se cuentan por decenas o por centenas hacer algo así será terriblemente aburrido y una gran pérdida de tiempo que podríamos aprovechar haciendo algo más productivo.

La forma rápida es a través de un stored procedure como el siguiente:

CREATE PROCEDURE SP_GRANT_ALL_TO(
   tcNombreUsuario VARCHAR(31))
AS
   DECLARE VARIABLE tcObjeto VARCHAR(31);
BEGIN

   -- Se otorga derechos sobre las tablas y sobre las vistas

   FOR SELECT
      RDB$RELATION_NAME
   FROM
      RDB$RELATIONS
   WHERE
      RDB$SYSTEM_FLAG = 0
   INTO
      :tcObjeto
   DO
      EXECUTE STATEMENT 'GRANT ALL ON ' || tcObjeto || ' TO ' || tcNombreUsuario;

   -- Se otorga derechos sobre los stored procedures

   FOR SELECT
      RDB$PROCEDURE_NAME
   FROM
      RDB$PROCEDURES
   WHERE
      RDB$SYSTEM_FLAG = 0
   INTO
      :tcObjeto
   DO
      EXECUTE STATEMENT 'GRANT EXECUTE ON PROCEDURE ' || tcObjeto || ' TO ' || tcNombreUsuario;

END;

 Este stored procedure le otorga al usuario o al rol cuyo nombre introducimos todos los derechos sobre todas las tablas, sobre todas las vistas y sobre todos los stored procedures de nuestra Base de Datos. Lo llamaríamos así:


EXECUTE PROCEDURE SP_GRANT_ALL_TO('MARCELA');

EXECUTE PROCEDURE SP_GRANT_ALL_TO('R_CONTABILIDAD');

COMMIT;

Y cuando el usuario MARCELA o alguien que pertenezca al rol R_CONTABILIDAD vuelva a conectarse a la Base de Datos, tendrá acceso a todas las tablas, todas las vistas y todos los stored procedures.

Conclusión:

Otorgar los derechos (también llamados permisos o privilegios) a cada tabla, cada vista, y cada stored procedure puede ser extremadamente tedioso, sobre todo cuando los objetos no son dos o tres sino decenas o centenas. Evidentemente que lo más inteligente es tener un stored procedure que se encargue de esa tarea por nosotros.

El stored procedure aquí mostrado cumple con esa tarea, aunque desde luego que podría ser mejorado o adaptado a tus particulares circunstancias. Por ejemplo podrías agregarle WITH GRANT OPTION, o podrías colocar un IF …. THEN para que algunos objetos sean salteados y no se otorgue permisos sobre ellos, algo como:

IF (tcObjeto <> 'CONFIGURACION') THEN
   EXECUTE STATEMENT 'GRANT ALL ON ' || tcObjeto || ' TO ' || tcNombreUsuario;

Aquí, si la tabla se llama CONFIGURACION entonces el usuario no recibe derechos en esa tabla. Recibe derechos sobre todas las otras tablas, pero no sobre la tabla CONFIGURACION. Eso puede ser lo más adecuado para evitar que meta sus pezuñas donde no debe.

Otro método es usar el comando REVOKE para quitarle derechos que anteriormente se le habían otorgado. Sería algo como:

REVOKE ALL ON CONFIGURACION FROM MARCELA

Tanto usando IF … THEN como usando REVOKE ALL, se consigue el objetivo buscado, o sea que el usuario MARCELA no tenga derechos sobre la tabla CONFIGURACION.

Ya tú decidirás cual te resulta más conveniente usar.

Advertencia:

El hecho de que PUEDAS fácilmente otorgarle todos los derechos sobre todos los objetos a un usuario usando un stored procedure como el aquí mostrado no significa que DEBAS hacerlo. La seguridad y la integridad de tu Base de Datos siempre debe ser tu principal prioridad; tu comodidad o la comodidad de los usuarios, debe estar después.

Muchas veces lo preferible es que crees un rol (o varios roles) y uno (o varios) archivos de script para el otorgamiento de derechos. Eso tiene la ventaja de que te sirve como documentación (ya que en tu archivo de script puedes escribir todos los comentarios que desees) y que con simples “copiar y pegar” puedes rápidamente tener muchos archivos de script, cada uno de ellos otorgando o revocando derechos.

Por lo tanto, el stored procedure anteriormente mostrado puede ser muy útil pero siempre deberías verificar que no estás otorgando más derechos de los que deberías otorgar.

Artículos relacionados:

Entendiendo los derechos de acceso

Otorgando permisos con EMS SQL Manager

Delegando el otorgamiento de derechos

El índice del blog Firebird21