Stored Procedure Templates for MySQL-MariaDB

Posted by in MariaDB, MySQL

the more I program for MySQLMariaDB, the more I need templates. That’s my truth, because I am learning tips and tricks I want to add to my code. This way I can accelerate the development process, being even more proffesional!

Today I show you the stored procedures templates I am using:

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';


#  YOUR CODE
SET @message := CONCAT(@message, 'Mensaje: ',@variable, @sep);


# END Message
CALL common_schema.prettify_message(@resumen_procedimiento, common_schema.trim_wspace(@message));
END

You, the reader, can show the use of the commom_schema library I have talked already here, in www.manejandodatos.es, for showing messages during the execution and at the end. If you program a FUNCTION, you must comment the CALL and include a RETURN with the results.

Template with a cursor

The second template I use is similar, but adding everything you need to deal with a cursor, and fetch all records, using the values for what you program, before returning the result. As before, when programming FUNCTIONs, you need to RETURN de result.

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
DECLARE l_last_row INT DEFAULT 0;
DECLARE iContador INT DEFAULT 0;
DECLARE v2 VARCHAR(90) DEFAULT ”;
DECLARE v1 INT DEFAULT 0;

/* Cursor Declaration */
DECLARE cursor1 CURSOR FOR SELECT id, field FROM table;


/* 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; /* Every record is save to variable ‘vX’ */
IF (l_last_row=1) THEN
LEAVE c1_loop;
END IF;
/* YOUR CODE  */

SET iContador = iContador + 1;
END LOOP c1_loop;

/* Close cursor */
CLOSE cursor1;

#SELECT iContador;
#return VALOR ;
END

To end with, I must WARN you about 3 things:

  1. You need to modify the SELECT query at your needs
  2. The number of variables to retrieve MUST BE DECLARED
  3. The number of columns fetched for every record MUST MATCH the variables

If you are aware of this (blue color), the template can save you some time, just adding the variables you need, modifying the query, and adjusting the FETCH to variables v1, v2, … vX

I’ll wait for your comments, and I hope you use it!