Plantilla para Procedimientos almacenados en MySQL – MariaDB

Conforme paso más tiempo programando para MySQL – MariaDB, más necesidad tengo del uso de plantillas, sobre todo porque vas adquiriendo ciertos conocimientos, trucos, etc, …. y de esta forma, no solo no aceleras la programación, sino que también la haces más profesional!

Hoy os presento la plantilla para los procedimientos almacenados (stored procedures) que yo estoy usando, y que incluye un resumen final del proceso:

CREATE PROCEDURE `Plantilla_SP`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
# Versión 1.0.0 - 20131018

DECLARE Num INT DEFAULT 0;
# Valores iniciales
SET @message := ''; -- Mensaje
SET @sep := '\n'; -- Separador de Mensajes
SET @resumen_procedimiento := 'Resumen'; -- Separador de Mensajes

# Escribe código
SET @message := CONCAT(@message, 'Mensaje: ',@variable, @sep);

# Envia Resumen
# Mensaje de FIN de ejecucion
CALL common_schema.prettify_message(@resumen_procedimiento, common_schema.trim_wspace(@message));
END
Como podeis observar, al ser un procedimiento, puedo hacer uso de commom_schema, libreria de la que he hablado en varias entradas, para mostrar mensajes durante y al final de la ejecución. En el caso de funciones, esta última llamada CALL debe ser omitida, e incorporar un RETURN con el resultado a devolver.

Plantilla incluyendo cursor

La segunda plantilla que voy a mostrar es similar a la primera, pero ahora incluye todo lo necesario para llamar a una tabla y recorrer todos los registros, antes de presentar el resultado. En el caso de funciones, y al igual que en el párrafo anterior, hay que incorporar un RETURN con el resultado.

CREATE DEFINER=`root`@`%` PROCEDURE `crea`(IN `tabla` VARCHAR(50))
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
COMMENT ''
BEGIN

# Versión 1.0.0 - 20131018
/* Declaración de variables */
DECLARE l_last_row INT DEFAULT 0;

DECLARE iContador INT DEFAULT 0;

DECLARE v2 VARCHAR(90) DEFAULT '';
DECLARE v1 INT DEFAULT 0;

/* Declaración del cursor de nombre ´cursor1´ */
DECLARE cursor1 CURSOR FOR SELECT id, campo FROM tabla;

/* Flag que permitirá saber si existen más registros por recorrer */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;

SET iContador = 0;

OPEN cursor1;
c1_loop: LOOP
FETCH cursor1 INTO v1, v2; /* Cada registro se le otorga a la variable ‘vX’ */
IF (l_last_row=1) THEN
LEAVE c1_loop;
END IF;
/* Aqui va el tratamiento con CADA REGISTRO */
SET iContador = iContador + 1;
END LOOP c1_loop;

/* cerramos el cursor */
CLOSE cursor1;
#return VALOR;

# SELECT iContador;
END

Por último, tengo que hacer notar tres cosas:

1. Hay que modificar la consulta SELECT del cursor a la que se necesite
2. El número de variables a recuperar DEBEN de ser declaradas, y coindicir con las columnas devueltas por el SELECT
3. El número de columnas devueltas por cada registro debe ser usarse en el FETCH

Cumpliendo estos requisitos (en azul), la plantilla facilita el trabajar con registros, al que sólo le queda incorporar el código necesario para hacer uso de las variables v1, v2, … vX

Espero que sea de vuestra utilidad, así como que me comenteis mejoras, etc….