Cómo programar procedimientos almacenados en MySQL

En este POST vamos a ver cómo crear, configurar y programar procedimientos almacenados en MySQL. Los procedimientos almacenados y las funciones son muy parecidas, pero en realidad son dos conceptos distintos.

El ejemplo más claro es que a los procedimientos almacenados se les llama o invoca con la sentencia CALL (EXEC para SQL Server).

Los procedimientos almacenados se utilizan para una muchísimas tareas, que permite organizar mejor el código y preservar una integridad de datos óptima. El uso de procedimientos almacenados también supone una mejora de rendimiento en tareas complejas. Además, los procedimientos almacenados mejora la legibilidad del código.

Crear procedimiento

Para crear una procedimiento almacenado en MySQL, podemos hacerlo en diferentes entornos:

Entorno gráfico

Paso 1: Accedemos a nuestro phpMyAdmin y nos vamos a la pestaña de Rutinas

Paso 2: Una vez que estamos en la pestaña de Rutinas, pulsamos al enlace Agregar rutina

Paso 3: Se nos va a abrir una ventana con un formulario y tenemos que indicar lo siguiente:

• Nombre de la rutina: Nombre del procedimiento almacenado.
• Parámetros: En este ejemplo no lo vamos a utilizar, pero podemos pasar datos para que trabajen con el procedimiento.
• Definición: Aquí vamos a insertar nuestro código SQL. En mi caso, voy a hacer un LOG para llevar un control de las ejecucciones de los procedimientos.

Para finalizar, pulsamos a Aceptar.

Paso 4: Como podemos observar, phpMyAdmin nos ha hablilitado un árbol dentro de la Base de Datos, en el que podemos ver todos los procedimientos que tenemos creados.

Línea de comandos

Para crear un procedimiento almacenado, deberemos de ejecutar esta sentencia SQL. Si queremos añadir parámetros, tenemos que añadirlos entre los paréntesis del nombre del procedimiento.

CREATE PROCEDURE JOB_DIARIO() insert INTO log select 'JOB_DIARIO',curdate();

¿Cómo ejecutar un procedimiento almacenado?

Para poder ejecutar un procedimiento almacenado, vamos a utilizar el comando CALL. Por ejemplo, en SQLServer, el comando es EXEC. Para ejecutar nuestro procedimiento almacenado utilizamos el siguiente código:

CALL JOB_DIARIO;

¿Cómo programar un procedimiento almacenado?

Una vez que tenemos creado nuestro procedimiento almacenado, podemos programarlo para que se ejecute en un determinado tiempo. Podemos ejecutarlo cada segundo, minuto, hora, día, semana, año, etc. En este ejemplo vamos a programar la ejecucción del JOB_DIARIO cada día. Para ello tenemos dos maneras de hacerlo:

Opcion 1: Crear evento

¿Qué es un evento? Un evento es una tarea que nos permite ejecuta de forma automática en un momento previamente programado, así la función es automatizar procesos. Ese comando se utiliza desde la versión de MySQL 5.1. Podemos crear un evento de dos maneras:

Entorno gráfico

Paso 1: Para crear un evento, tenemos que ir a nuestro phpMyAdmin, y pulsamos a la pestaña «Eventos».

Paso 2: Una vez que estamos dentro de la pestaña «Eventos», deberemos comprobar que disponemos de los permisos para utilizarlos. Para comprobarlo deberemos de cambiar el «Estado del planificador de eventos» a Encendido, y pulsamos el botón «Añadir Evento»

Paso 3: Se nos va a abrir una ventana con un formulario y tenemos que indicar lo siguiente:

• Nombre del evento: Incidamos un nombre para el evento.
• Estado del evento: Seleccionamos «ENABLED», que babilita el evento.
• Ejecutar cada: Aquí tenemos que indicar cada cuánto tiempo queremos que se ejecute nuestro procedimiento.
• Inicio: Cuando queremos que empiece a ejecutarse el evento.
• Fin: Hasta cuando tiene que ejecutarse el evento. Si lo ponemos en blanco, será indefinido.
• Definición: Aquí vamos a poner la consulta SQL en el que ejecuta el procedimiento JOB_DIARIO.

Línea de comandos

Paso 1: Habilitamos los eventos de MySQL.

SET GLOBAL event_scheduler = ON;

Paso 2: Creamos el evento.

CREATE EVENT JOB_DIARIO
ON SCHEDULE EVERY 1 DAY
STARTS '2020-05-13 00:00:00'
DO
insert INTO log select 'JOB_DIARIO',curdate();

Si nos sale este mensaje de error, significa que no disponemos de permisos para crear eventos. Tendríamos dos opciones:

• Contactar con nuestro proveedor de hosting y que nos den esos permisos.
• En caso que no lo puedan facilitar, pasaremos al plan B, programar un CRON.

Opción 2: Programar un CRON

¿Qué es un CRON?

Un CRON es un planificador de tareas en segundo plano (conocido en Linux como demonio) que ejecuta un script en unos intervalos regulares (cada minuto, hora, día, semana, mes). El nombre CRON viene del griego chronos que significa “tiempo”.

CRON es un «equivalente» a las Tareas Programadas de Windows.

Creamos un script

Deberemos saber que Sistema Operativo está alojado nuestro servidor. En mi caso es un servidor que trabaja bajo Linux. Por lo que vamos a tener que generar un fichero sh. Para Windows hablaríamos de un fichero bat.

Vamos a crear nuestro fichero JOB_DIARIO.sh y lo alojamos en nuestro servidor. La ruta donde se aloja mi script es:

/home/lacodigoteca/jobs/JOB_DIARIO.sh

#!/bin/bash
mysql --user=lacodigo_pruebas --password=lacodigoteca --database=lacodigo_pruebas --execute="call JOB_DIARIO"

Programamos el CRON

Paso 1: Deberemos de ir a nuestro cPanel de nuestro hosting. Tendremos que tener un botón similar a este:

Paso 2: Nos va a aparecer un formulario en el que vamos a programar cuando queremos que se ejecute nuestro fichero JOB_DIARIO.sh

Resultado final

Hemos dejado ejecutarse el procedimiento una semana y este es el resultado.

Y esto es todo. Si quieres, puedes consultar más detalles acerca de los procedimientos almacenados en la documentación oficial de MySQL.

Si quieres aplicar más sentencias en SQL, te invito a que visites esta sección.