Cómo crear un Stored Procedure en MySQL

Un stored procedure es un código SQL preparado que se guarda en nuestro Schema para reutilizarlo cuando lo necesitemos.

La mayoría de las bases de datos SQL tienen los stored procedures como una herramienta incorporada, parte del lenguaje original de SQL. Sin embargo, la forma de programar stored procedures puede variar un poco entre las distintas bases de datos SQL.

Hoy vamos a aprender a hacer distintos tipos de sentencias SQL dentro de un Stored Procedure en MySQL.

¿Por qué debería usar Stored Procedures?

Quizás estás usando PHP con MySQL y siempre escribís el mismo código para actualizar tus ventas dentro de un período, o necesitás un reporte rápido de eso. Podrías guardar una consulta que usás siempre dentro del stored procedure y agregarle dos parámetros de tipo fecha, que van a indicar el inicio y el fin del período que estás consultando. O digamos que tenés una consulta que inserta nuevos usuarios en la base de datos y siempre lo hacés de la misma manera, cambiando solo algunos parámetros, entonces podrías usar stored procedures para lograr esto.

Una GRAN ventaja de usar Stored Procedures es que podés usar sentencias de programación, por ejemplo, si una variable parámetro es ‘S’ entonces vamos a ejecutar una consulta, si es ‘N’, vamos a ejecutar otra consulta.

¿Cómo empiezo mi Stored Procedure de MySQL?

Una buena forma de empezar tu Stored Procedure es abriendo tu consola de Adminer (si no tenés Adminer instalado, el código también va a funcionar en la consola o en otro intérprete, pero Adminer viene con una opción que hace más fácil la inserción de variables).

En este punto, estoy asumiendo que ya tenés tu entorno de pruebas creado y funcionando, pero si no es tu caso, acá tenés un pequeño tutorial sobre cómo instalar MySQL en un contenedor de Docker.

Creando un Stored Procedure con Adminer

Para nuestro primer Stored Procedure de MySQL. Antes que nada, vamos a crear una tabla llamada users_table con tres campos: id, name, y surname.

Una vez que estás en Adminer, después de seleccionar tu base de datos SQL vas a tener una opción que dice create procedure, hacé clic ahí.

Después de hacer clic en Create Procedure vas a ver la siguiente página:

Vamos a agregarle un nombre a nuestro Stored Procedure de MySQL y después vamos a agregar tres parámetros, uno va a ser ‘pName’, el segundo ‘pSurname’ y el tercero pId. Tu stored procedure debería verse así por ahora:

Lo que queremos lograr es que si el parámetro id es 0 entonces vamos a insertar un registro en nuestra tabla con nuestro Stored Procedure, caso contrario, vamos a actualizar el registro existente en nuestra tabla con nuestro stored procedure.

La sentencia SQL SECURITY INVOKER

Un stored procedure puede tener esta sentencia o va a usar por defecto una sentencia DEFINER, esto significa que va a correr con los permisos del DEFINER del Stored Procedure. Si el DEFINER no tiene acceso a la tabla a la que estás tratando de acceder, tu Stored Procedure probablemente no va a funcionar… A menos que… definas una sentencia SQL SECURITY INVOKER al inicio del Stored Procedure, que va a usar el permiso del usuario que está ejecutando actualmente el stored procedure. Así que empecemos con esto, y agreguemos un SQL SECURITY INVOKER a nuestra sentencia, tu stored procedure debería verse así:

La sentencia BEGIN

Después de definir (o no) la sentencia SQL SECURITY INVOKER, los stored procedures empiezan con una sentencia BEGIN, esta sentencia declara el inicio de nuestro código. Así que agreguémosla.

La sentencia IF

Podríamos empezar ahora agregando código SQL a nuestro stored procedure, pero en este Stored Procedure de MySQL, vamos a cambiar nuestro código dependiendo del parámetro id, ¿recordás? Así que en este caso en particular vamos a agregar un IF pId=0 THEN

La sentencia INSERT

Como ya sabés, necesitamos usar la sentencia INSERT para agregar un registro a una tabla, así que la vamos a usar para crear nuestro registro con el Stored Procedure de MySQL.

INSERT INTO user_table (name, surname) VALUES (pName, pSurname);

La sentencia ELSE

Como en cualquier lenguaje de programación, si la condición del IF no se cumple, entonces vamos a hacer una sentencia ELSE (así que si pId no es 0, vamos a hacer otro código)

La sentencia UPDATE

La sentencia UPDATE es la forma de actualizar una fila en la tabla, así que la vamos a usar para actualizar nuestra tabla de usuarios dentro de nuestro Stored Procedure de MySQL

UPDATE user_table SET name=pName, surname=pSurname WHERE id = pId; 

La sentencia END IF

Ahora tenemos que cerrar nuestra sentencia IF, ya hicimos todo lo que íbamos a hacer acá.

Una sentencia SELECT final

Así que después de agregar o actualizar nuestro registro, vamos a querer ver si todo salió bien en nuestras pruebas, así que vamos a poner acá una sentencia SELECT

SELECT * FROM user_table;

Por último, la sentencia END

Así que después de escribir nuestro Stored Procedure de MySQL, le vamos a decir a MySQL que ya terminamos, hacemos esto agregando un END al final :P.

¡Felicitaciones! ¡POR FIN terminaste tu Stored Procedure! Ahora guardalo con el botón de guardar.

Ejecutando un Stored Procedure

El comando para ejecutar un Stored Procedure va a depender de la base de datos SQL que estés usando, pero en el caso de MySQL, usás la palabra clave CALL

Abrí el escritor de comandos SQL en Adminer y escribí la siguiente consulta:

CALL add_names('Peter','Parker',0); -- Mi Stored Procedure se llama add_names, fijate si usás el mismo nombre

Esto va a crear un nuevo registro en nuestra tabla y podés ver lo que se escribió gracias a la sentencia SELECT que agregamos al final, ¿te acordás?

Ahora actualicemos ese registro y cambiemos el nombre a Gomez Addams

CALL add_names('Gomez','Addams',1);

Si todo salió bien, acá tenés tu nuevo resultado:

¡Felicitaciones! ¡Por fin comprobaste que tu primer Stored Procedure de MySQL está funcionando bien!

¿Y si no tengo Adminer instalado?

Bueno, entonces tu entorno de pruebas no tiene Adminer, así que vamos a hacer este mismo Stored Procedure pero usando nuestra consola.

La sentencia DELIMITER

Como ya sabés, MySQL usa ; (punto y coma) como su DELIMITER por defecto, pero cuando llegamos a los Stored Procedures, los usamos dentro de él, así que vamos a necesitar cambiar esto para escribir nuestro Stored Procedure por línea de comandos

mysql > DELIMITER $$

Creando nuestro Stored Procedure con la CONSOLA

Después de cambiar el DELIMITER, vamos a escribir nuestro stored procedure, la principal diferencia es que acá vamos a tener que definir nuestros parámetros y tipos a mano y en la sentencia END, vamos a usar nuestro nuevo delimitador, así que el código va a quedar así:

mysql > CREATE PROCEDURE add_names (IN pName varchar(100), IN pSurname varchar(100), pId INT) 
SQL SECURITY INVOKER
BEGIN
IF pId=0 THEN
   INSERT INTO user_table (name, surname) VALUES (pName, pSurname);
ELSE
   UPDATE user_table SET name=pName, surname=pSurname WHERE id = pId;
END IF;
SELECT * FROM user_table;
END$$
mysql > DELIMITER ; -- NO TE OLVIDES DE CAMBIAR DE NUEVO TU DELIMITADOR DESPUÉS DE CREAR TU STORED PROCEDURE

¡Y eso es todo! ¡Tu stored procedure de MySQL ya fue creado! Podés ejecutarlo usando el comando CALL como lo usamos antes.

Conclusión

Los Stored Procedures de MySQL son herramientas poderosas para hacer cambios con distintas sentencias, indagamos solo en la sentencia IF, pero también podés usar sentencias WHILE – END WHILE, sentencias IF – ELSEIF – ELSE – END IF, y sentencias LOOP.

Si querés saber más sobre Stored Procedures te recomiendo usar la documentación oficial de MySQL para empezar a aprender (un consejo: revisá también la sentencia LOOP, la sentencia WHILE, y la sentencia IF, que te van a ser muy útiles para tus próximos Stored Procedures de MySQL!)

Gracias por leer, como siempre, ¡nos vemos en el próximo!