Calculando el Seguro Social con un procedimiento almacenado de mySQL 5

Hace algún tiempo atrás habia realizado esta y otras funciones para mySQL 5 en un proyecto de nómina (no sé como les terminó de ir) y quise publicarlo ahora ya que algunas personas me habian pedido esta función.

La función almacenada de mySQL consiste en un simple calculo de la cuota del Seguro Social Obligatorio Venezolano; hacerlo en procedimiento almacenado?, claro!, todos los datos necesarios para realizar el cálculo se encuentran del lado del servidor, por qué llevarlo al lado de la aplicación (Lenguaje de programación) para realizar un calculo que enteramente puede realizar la DB?.

Habia hecho esta función junto con otras 15 de conceptos de nómina para unos amigos que estaban desarrollando un sistema.

Primero; vamos a comentar el código; despues aparecerá integramente.

Concepto de Funciones en mySQL 5.

Una función es un pequeño “script” de código SQL (si fuera postgresql; pudiera ser en python o hasta en php) que puede ser convocado en la línea de una instrucción SQL; ejemplo:

SELECT md5(password), CURDATE() as fecha_actual, CURTIME() as hora_actual FROM usuarios

En el ejemplo anterior, las resaltadas son funciones internas de mySQL (una calcula el checksum md5 de una cadena; las otras devuelven la fecha y la hora actual).

Se pueden crear funciones propias, la diferencia entre una función y un procedimiento almacenado en mySQL es que una función SIEMPRE debe devolver un valor y este valor debe ser un escalar (un número, una cadena, el valor de un campo de una fila, etc); un procedimiento almacenado puede devolver escalares, filas, resultsets completos o simplemente no devolver nada.

SSO (Seguro Social Obligatorio) for Dummies.

El SSO Venezolano se calcula en base a los lunes contabilizados del mes, la formula suele ser:

 

((sueldo*12 meses) / Semanas laborales(default: 52)) * (retención SSO (defecto: 0.04)*lunes del mes)) <-redondeado a 2 cifras

Bien; ahora describamos el asunto:

La tabla parámetros:

Es un verdadero fastidio tener que modificar una costante (lease IVA, SSO, retención ISLR, algún gravamen) cada vez que el gobierno decide modificarla; por ende, es mejor guardar este valor en una tabla de parámetros; en dicha tabla, podremos guardar los porcentajes y valores de los distintos conceptos; de esta manera no tendremos que editar la función si de repente estos valores cambian:

La forma de la tabla es como sigue:

 

CREATE TABLE  `nomina`.`parametros` (
  `parametro` varchar(10) NOT NULL default '',
  `valor` decimal(3,3) NOT NULL default '0.000',
  `patronal` decimal(3,2) NOT NULL default '0.00',
  PRIMARY KEY  (`parametro`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

e Insertamos un registro como el siguiente:

 

INSERT INTO parametros VALUES ('sso',  0.04,  -0.87);

A los patronos se les retiene el doble de lo que se le retiene al empleado.

La función Almacenada SSO:

Oficialmente el delimitador de sentencias de mySQL es el punto y coma (;), por ende, debemos cambiarlo antes de poder escribir una función almacenada (que termina todas sus sentencias en punto y coma) para ello escribimos:

DELIMITER $$

Y ahora cuando terminemos de escribir la función almacenada escribimos:

END$$ (en vez de END;)

Ahora viene el cuerpo de declaración de la función:

DELIMITER $$

DROP FUNCTION IF EXISTS `nomina`.`sso`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `sso`(sueldo DECIMAL(8,2), tipo INT) RETURNS decimal(8,2)

Inicialmente ejecuto un DROP IF EXISTS que me permite verificar si la función existe y borrarla (en caso que estuviera editandola; no existe ALTER FUNCTION o ALTER PROCEDURE, por lo que hay que borrar primero un procedimiento si deseamos cambiarlo).

La directiva SQL SECURITY DEFINER nos indica que la función se ejecutará en un ámbito de permisos igual a quien creó la función (forma por defecto); la otra forma INVOKER hace que la función se ejecute en el ámbito de permisos de quien la ejecuta; para un ejemplo:

Tengo permisos sobre la tabla A y creo una función con DEFINER que lee esa tabla -> resultado -> La función puede leer la tabla A
Creo una función que intenta leer la tabla B y no tengo permisos para leer esa tabla -> resultado -> La función no puede leer la tabla B
Creo una función con INVOKER que lee la tabla B y usuario que invoca la función puede leer B -> resultado -> la función puede leer B
Creo una función con INVOKER que lea la tabla A y usuario que invoca NO puede leer la tabla A -> resultado -> La función no puede leer la tabla A.

DEFINER pide el usuario que deseamos colocar como DEFINER.

FUNCTION sso() es la declaración de función y pide algunos parámetros (dentro del paréntesis); estos son sueldo (de tipo DECIMAL(8,2), longitud integer 8 con 2 decimales de precisión) y un tipo (que es un entero 1 o 0); la situación es que en Venezuela, hay 2 cotizaciones de SSO; la nacional (0.04) y la estadal (0.02, es menor porque por lo general en las zonas rurales no hay Seguro Social, asi que para que te voy a retener algo que no existe?); la función retorna un valor escalar (el SSO calculado) de tipo DECIMAL(8,2).

Las funciones de mySQL repito, tienen 3 limitantes:

 

  • No pueden conectarse (por defecto) a datos en otras tablas.
  • No pueden recibir parámetros de salida (todos los parámetros son de entrada)
  • Solo pueden devolver escalares

Para obviar la primera observación (para que nuestra función pueda obtener datos desde otras tablas) entonces agregamos a la declaración:

READS SQL DATA

Y eso permite a nuestra función ejecutar sentencias SQL dentro del código.

El cuerpo de la función:

BEGIN
DECLARE porcentaje DECIMAL(2,2);
DECLARE lunes INT;
DECLARE mes INT;
DECLARE fecha DATE;
DECLARE primero DATE;
DECLARE quince DATE;
SELECT curdate() INTO fecha;
SELECT DAYOFMONTH(fecha)  INTO mes;
SELECT STR_TO_DATE(CONCAT(YEAR(fecha), ‘-‘, MONTH(fecha), ‘-‘, 1), ‘%Y-%m-%d’) INTO primero;
SELECT STR_TO_DATE(CONCAT(YEAR(fecha), ‘-‘, MONTH(fecha), ‘-‘, 15), ‘%Y-%m-%d’) INTO quince;
IF tipo=0 THEN
SELECT valor INTO porcentaje FROM parametros WHERE parametro = ‘sso’;
ELSE
SELECT valor INTO porcentaje FROM parametros WHERE parametro = ‘sso-estado’;
END IF;
IF mes <= 15 THEN
BEGIN
SELECT (WEEK(quince,5) – WEEK(primero,5)) INTO lunes;
END;
ELSE
BEGIN
SELECT (WEEK(LAST_DAY(fecha),5) – WEEK(quince,5)) INTO lunes;
END;
END IF;
return TRUNCATE((((sueldo*12)/52)*(porcentaje*lunes)), 2);
END$$

DELIMITER ;

Algunas observaciones útiles:

DECLARE sirve en ANSI DB2 SQL (la base de mySQL) declarar variables a ser usadas dentro del código.

SELECT INTO … permite hacer un SELECT de un escalar y devolverlo a una variable interna:

SELECT valor INTO porcentaje FROM parametros WHERE parametros = 'sso'

La función calcula el SSO quincenalmente, determinando primero a partir de la fecha actual, en que quincena estamos y retornando la cantidad de lunes de esa semana:

IF mes <= 15 THEN
    BEGIN
    SELECT (WEEK(quince,5) - WEEK(primero,5)) INTO lunes;
    END;
ELSE
    BEGIN
    SELECT (WEEK(LAST_DAY(fecha),5) - WEEK(quince,5)) INTO lunes;
    END;
END IF;

Posteriormente computa el resultado y lo devuelve con dos cifras de precisión usando TRUNCATE().

Le faltan muchas cosas a esta función (como que la fecha sea parametrizada y no que la tome de la actual) y cosas así; pero tomemos en cuenta que no tengo mucha experiencia en administración y esta función la construí con una experta en nómina sentada a mi lado.

Espero que entiendan algunas cosas más de la construcción de procedimientos almacenados y funciones almacenadas en mySQL.

Acerca de phenobarbital

http://about.me/phenobarbital

Publicado el 16 septiembre 2007 en Databases, Linux, PlanetaLinux. Añade a favoritos el enlace permanente. 7 comentarios.

  1. Jesús,

    La declaración “READS SQL DATA” es solo una instrucción informativa de la función, el engine de MySQL no hace nada con esta declaración.

    Así que una función como:

    DELIMITER $$
    
    DROP FUNCTION IF EXISTS `myfunc`$$
    
    CREATE FUNCTION `myfunc`()
    	RETURNS INT
    	NO SQL
    BEGIN
    	DECLARE r INT;
    	SELECT COUNT(*) INTO r FROM tablename;
    	RETURN r;
    END$$
    
    DELIMITER ;
    

    dará el mismo resultado que la función:

    DELIMITER $$
    
    DROP FUNCTION IF EXISTS `myfunc`$$
    
    CREATE FUNCTION `myfunc`()
    	RETURNS INT
    	READS SQL DATA
    BEGIN
    	DECLARE r INT;
    	SELECT COUNT(*) INTO r FROM tablename;
    	RETURN r;
    END$$
    
    DELIMITER ;
    

    No considero esto una limitante.

  2. Lo tendré en cuenta pheno….saludos!!

  3. A partir de mysql 5.0.23 debes obligatoriamente especificar si el tipo de funcion es determinista o no determinista; asi como si lee o no lee (o incluso modifica SQL) SQL; por defecto, para que un comando CREATE PROCEDURE o CREATE FUNCTION sea aceptado, DETERMINISTIC o NO SQL y READS SQL DATA deben especificarse explícitamente. De otro modo ocurre un error:

    ERROR 1418 (HY000): This routine has none of DETERMINISTIC, NO SQL,
    or READS SQL DATA in its declaration and binary logging is enabled
    (you *might* want to use the less safe log_bin_trust_routine_creators
    variable)

    Ahora bien, este error es ejcutado por el query optimizer del mySQL, por ende dependen de la “honestidad” del desarrollador; aunque de plano la función puede funcionar con NO SQL; no es bueno que “fomentemos” este tipo de malas costumbres, es como decirle a un programador que indentar no es necesario y que está ahi por adorno …
    gracias por el comentario …

  4. Actualmente estoy trabajando con la versión 5.0.45 y 5.1.21-beta, en ninguna de estas versiones al crear un PROCEDURE o FUNCTION MySQL me obliga a específicar obligatoriamente si será determinista o no determinista o si lee o no SQL.

    Estoy de acuerdo contigo que no se debe fomentar a malas prácticas de programación, sin embargo, decirle a un programador novel que si no identa su programa el mismo no funcionará no es la mejor manera de enseñarle.

  5. Tal vez sea la forma como compilaste el mysql o tienes un my.cnf bastante laxo; en mi caso uso mysql 5.0.32 (oficial debian) y 5.1.20 y en ambos casos me genera el siguiente error:
    Desde el Query Browser:
    This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) (errno: 1418)
    Click ‘Ignore’ if you’d like to have this error ignored until the end of the script.
    Desde Consola:
    ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQ L DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable).
    Claro, puedes hacer un:
    set GLOBAL log_bin_trust_function_creators = 1
    Para relajar las restricciones en la creación de procedimientos o funciones; o incluso desactivando el binary logging; sin embargo en ambientes de producción (donde hay muchos accediendo a las bases de datos) no son cosas que uno debería permitir.
    Pues de hecho, no le estoy diciendo que su script no va a funcionar, pero a menos que tenga unas reglas de seguridad bastante laxas (y para ello, deberá entonces ya conocer a fondo mysql y modificarlas) no podrá crear procedimientos almacenados o funciones si no agrega las reglas de DETERMINISTIC o NO SQL; pues generará los errores indicados …

  6. Ahora nos entendemos mejor, para que se genere el error, MySQL debe arrancar con el registro binario (binary log) activo, así es mucho más entendible lo que comentas, el problema es que este detalle no fue mencionado en el artículo.

    Ahora bien, el hecho que el registro binario no esté activo no quiere decir que el my.cnf sea lexo, todo depende del contexto donde realmente lo requieras.

  1. Pingback: Phenobarbital con Soda » Blog Archive » mySQL functions: Paro Forzoso (II parte)

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: