Cómo crear y usar un procedimiento almacenado MySQL

En esta guía vamos a ver cómo crear y configurar procedimientos almacenados en MySQL. Los procedimientos almacenados suelen ser confundidos con las funciones almacenadas, pero son dos conceptos distintos. Por ejemplo, los procedimientos deben ser invocados con la sentencia EXEC, mientras es posible utilizar cualquier función almacenada directamente en una sentencia SQL.

Los procedimientos pueden ser utilizadas para una infinidad de tareas, permitiendo organizar mejor el código y preservar una integridad de datos óptima. El uso de procedimientos también suele suponer una mejora de rendimiento en tareas relativamente complejas. Al igual que ocurre con las funcionales almacenadas, no nos debemos olvidar de que el uso de procedimientos almacenados mejorará también la legibilidad del código.

Qué es un procedimiento almacenado

Un procedimiento almacenado MySQL no es más que una porción de código que puedes guardar y reutilizar. Es útil cuando repites la misma tarea repetidas veces, siendo un buen método para encapsular el código. Al igual que ocurre con las funciones, también puede aceptar datos como parámetros, de modo que actúa en base a éstos.

Para poder crear un procedimiento almacenado es necesario que tengas permisos INSERT y DELETE sobre la base de datos.

Sintaxis

La sintaxis de un procedimiento almacenado es la siguiente:

CREATE PROCEDURE nombre_procedimiento
AS
sentencias_sql
GO;

Para ejecutar un procedimiento almacenado lo invocamos así:

EXEC nombre_procedimiento (param1, param2, ....);

En la versión de SQL de Oracle también podemos invocar al procedimiento así:

CALL nombre_procedimiento (param1, param2, ....);

Parámetros

Como has visto, los parámetros se definen separados por una coma. Los parámetros de los procedimientos almacenados de MySQL pueden ser de tres tipos:

• IN: Es el tipo de parámetro que se usa por defecto. La aplicación o código que invoque al procedimiento tendrá que pasar un argumento para este parámetro. El procedimiento trabajará con una copia de su valor, teniendo el parámetro su valor original al terminar la ejecución del procedimiento.
• OUT: El valor de este parámetros pude ser cambiado en el procedimiento, y además su valor modificado será enviado de vuelta al código o programa que invoca el procedimiento.
• INOUT: Es una mezcla de los dos conceptos anteriores. La aplicación o código que invoca al procedimiento puede pasarle un valor a éste, devolviendo el valor modificado al terminar la ejecución. En caso de resultarte confuso, echa un ojo al ejemplo que verás más adelante.

Ventajas e inconvenientes de su uso

Como toda tecnología, el uso de procedimientos almacenados tiene sus pros y sus contras.

Ventajas

Al reducir la carga en las capas superiores de al aplicación, se reduce el tráfico de red y, si el uso de los procedimientos almacenados es el correcto, puede mejorar el rendimiento.

Al encapsular las operaciones en el propio código SQL, nos aseguramos de que el acceso a los datos es consistente entre todas las aplicaciones que hagan uso de ellos.

En cuanto a seguridad, es posible limitar los permisos de acceso de usuario a los procedimientos almacenados y no a las tablas directamente. Des este modo evitamos problemas derivados de una aplicación mal programada que haga un mal uso de las tablas.

Inconvenientes

Al igual que ocurre con toda tecnología, tenemos que formarnos para aprender a crear procedimientos, por lo que existe cierta curva de aprendizaje.

Otro posible problema puede ocurrir con las migraciones. No todos los sistemas gestores de bases de datos usan los procedimientos del mismo modo, por lo que se reduce la portabilidad del código.

Configuración de la base de datos

A modo de ejemplo, vamos a configurar una base de datos sobre la cual crearemos luego un procedimiento almacenado. Usaremos la línea de comandos, pero también puedes crear una base de datos MySQL mediante otros métodos.

Conéctate a MySQL con este comando, reemplazando usuario por tu nombre de usuario.

mysql -u usuario -p

Vamos a crear una base de datos de nombre base_ejemplo. Para ello sigue estos pasos:

1. Usa la sentencia create para crear la base de datos:

   CREATE DATABASE base_ejemplo;

2. Ahora usa el siguiente comando para seleccionar la base de datos:

   USE base_ejemplo;

   Si usas phpMyAdmin, sencillamente debes hacer clic en el nombre de la base de datos, que en nuestro caso es base_ejemplo.

3. Ahora crea una tabla en la base de datos a la que llamaremos productos. Para hacerlo, ejecuta el siguiente comando:

   CREATE TABLE productos (
       id INT NOT NULL AUTO_INCREMENT,
       nombre VARCHAR(20) NOT NULL,
       estado VARCHAR(20) NOT NULL DEFAULT 'disponible',
       precio FLOAT NOT NULL DEFAULT 0.0,
       PRIMARY KEY(id)
   );

4. Ahora vamos a insertar algunos datos de prueba en la tabla productos:

   INSERT INTO productos (nombre, estado, precio) VALUES ('Producto A','disponible', 8), ('Producto B', 'disponible', 1.5),('Producto C', 'agotado', 80);

Ya tenemos al base de datos creada. Ahora vamos a crear un procedimiento almacenado.

Cómo crear un procedimiento almacenado

Ahora que hemos configurado una base de datos y una tabla, vamos a crear tres procedimientos almacenados para mostrar el uso y las diferencias de cada tipo de parámetro.

Al definir los procedimientos, tendremos que usar delimitadores para indicar a MySQL que se trata de un bloque independiente. En los siguientes ejemplos, DELIMITER $$  frena la ejecución de MySQL, que se retomará de nuevo en la sentencia DELIMITER del final.

Procedimiento almacenado con parámetros IN

Vamos a obtener los productos que tienen un determinado estado. Para crear el procedimiento, ejecuta estas sentencias SQL:

DELIMITER $$
CREATE PROCEDURE obtenerProductosPorEstado(IN nombre_estado VARCHAR(255))
BEGIN
    SELECT * 
    FROM productos
    WHERE estado = nombre_estado;
END$$
DELIMITER

El nombre del estado está contenido en el parámetro nombre_estado que hemos definido como IN. Suponiendo que quieras obtener los productos con estado disponible, tendrías que invocar al procedimiento de este modo:

CALL obtenerProductosPorEstado('disponible')

Este será el resultado:

+---------+--------------+------------+---------+
| id      | nombre       | estado     | precio  | 
+---------+--------------+------------+---------+
| 1       | Producto A   | disponible | 8.00    |
| 2       | Producto B   | disponible | 1.50    |
+---------+--------------+------------+---------+

Procedimiento almacenado con parámetros OUT

Vamos a obtener el número de productos según su estado. Para crear el procedimiento, ejecuta estas sentencias SQL:

DELIMITER $$
CREATE PROCEDURE contarProductosPorEstado(
    IN nombre_estado VARCHAR(25),
    OUT numero INT)
BEGIN
    SELECT count (id) 
    INTO numero
    FROM productos
    WHERE estado = nombre_estado;
END$$
DELIMITER

Al igual que antes, pasamos el estado como nombre_estado, definido como IN. También definimos numero como parámetro OUT. Suponiendo que quieras obtener el número de productos con estado disponible, debes llamar al procedimiento de este modo:

CALL contarProductosPorEstado('disponible', @numero);
SELECT @numero AS disponibles;

Este será el resultado:

+-------------+
| disponibles | 
+-------------+
| 2           |
+-------------+

Para obtener el número de productos agotados, debemos invocar al procedimiento de este modo:

CALL contarProductosPorEstado('agotado', @numero);
SELECT @numero AS agotados;

Este será el resultado:

+----------+
| agotados |
+----------+
| 1        |
+----------+

Procedimiento almacenado con parámetros INOUT

Vamos a crear un procedimiento que incremente una variable llamada beneficio cuando se vende un producto. Para crear el procedimiento, ejecuta estas sentencias SQL:

DELIMITER $$
CREATE PROCEDURE venderProducto(
    INOUT beneficio INT(255),
    IN id_producto INT)
BEGIN
    SELECT @incremento_precio = precio 
    FROM productos
    WHERE id = id_producto;
    SET beneficio = beneficio + @incremento_precio;
END$$
DELIMITER

En el procedimiento pasamos el parámetro beneficio como INOUT para que mantenga su valor tras la ejecución del procedimiento. También definimos el parámetro id_producto como IN para decirle al procedimiento el producto que queremos vender. Para simplificar las cosas partimos de que el producto está siempre disponible. Vamos a vender algunos productos para ver cómo cambia la variable:

SET @beneficio = 0;
CALL venderProducto(@beneficio, 1);
CALL venderProducto(@beneficio, 2);
CALL venderProducto(@beneficio, 2);
SELECT @beneficio;

Este será el resultado:

+-----------+
| beneficio |
+-----------+
| 11.00     |
+-----------+

Cómo borrar un procedimiento almacenado

Puedes borrar un procedimiento almacenado haciendo uso de la sentencia DROP PROCEDURE. Por ejemplo, si quieres eliminar el procedimiento venderProducto del ejemplo anterior, tendrás que ejecutar esta sentencia:

DROP PROCEDURE venderProducto;

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

---