Ejemplos de procedimientos almacenados sencillos

En este artículo voy a mostrar el funcionamiento de los procedimientos almacenados (STORED PROCEDURES) mediante varios ejemplos en SQL.

Los ejemplos serán útiles y los explicaré paso a paso. Iré añadiendo ejemplos más complejos a la vez que avances en la lectura del tutorial.

En estos ejemplos vamos a ver procedimientos almacenados sql de varios tipos:

1. Procedimiento almacenado básico.
2. Procedimiento con parámetros de entrada IN.
3. Procedimiento almacenado con paramétros de salida OUT.
4. Procedimiento con parámetros de salida y entrada INOUT.

Pero antes de mostrar los ejemplos me gustaría hacer un pequeño inciso en la sintaxis básica de una rutina almacenada (otra forma de llamar a los procedimientos almacenados).

Estructura de un STORED PROCEDURE

Si estás leyendo esto y no estás familiarizado con los procedimientos almacenados, me gustaría explicarte de forma breve la sintaxis de un procedimiento almacenado.

La estructura general para crear un PROCEDURE es:

1. Usar la palabra reservada DELIMITER seguida de una combinación de caracteres que sepas que no aparecerá en el interior del PROCEDURE. 
2. Crear el procedimiento almacenado con el comando CREATE PROCEDURE seguido del nombre que le quieras asignar.
3. Instrucción BEGIN para indicar que empieza el código SQL del procedimiento en SQL.
4. Código SQL que queramos que se ejecute cuando se llame a la rutina.
5. Cerrar el código con la cláusula END seguida de los caracteres definidos con el comando DECLARE.

La sintaxis a modo de ejemplo sería:

DELIMITER //
CREATE PROCEDURE nombre_procedimiento
BEGIN
  /* CODIGO EN SQL A EJECUTAR */
END //

Si el procedimiento incluyera parámetros los pondríamos después del nombre y entre paréntesis. Como en cualquier lenguaje de programación.

DELIMITER //
CREATE PROCEDURE nombre_procedimiento ( TIPOPARAMETRO parametro1 TIPOVALOR, ...)
BEGIN
  /* CODIGO EN SQL A EJECUTAR */
END //

Ejemplo de procedimiento almacenado sencillo

DELIMITER //
CREATE PROCEDURE total_paises
BEGIN
  SELECT COUNT(*)
  FROM pais
END //

Este sencillo ejemplo permite mostrar el total de paises almacenados en una tabla pais con una simple llamada. Para llamarlo usamos la siguiente línea SQL:

CALL total_paises();

STORED PROCEDURE con parámetros de entrada IN

DELIMITER //
CREATE PROCEDURE total_paises_nombrados_como
(IN palabra CHAR(20))
BEGIN
  SELECT COUNT(*) FROM pais
  WHERE nombre LIKE palabra;
END //

Este "stored procedure" es una rutina SQL que permite calcular el total de paises en una tabla pais que coinciden con una "palabra" pasada como parámetro de entrada. Hay que tener en cuenta que la palabra solo puede tener cómo máximo 20 caracteres ya que el parámetro de entrada "palabra" está definido como CHAR(20).

Para llamar a este método hay que recordar siempre pasar un texto como parámetro de entrada, sino se producirá un error:

CALL total_paises_nombrados_como('españa');
/* devolvería el resultado de la consulta */

Ejemplo de STORED PROCEDURE con parámetros de salida

Ahora es el momento de ver las llamadas SQL con parámetros de SALIDA.

La definición de este tipo de parámetros en los procedimientos almacenados es mediante la palabra OUT.

En el siguiente ejemplo lo verás mejor:

DELIMITER //
CREATE PROCEDURE total_paises
(OUT total INTEGER)
BEGIN
    SELECT COUNT(*) INTO total FROM pais;
END //

Este procedimiento calcula el total de registros de la tabla pais y los inserta con INTO en la variable de salida OUT.

Para llamar al procedimiento SQL y visualizar el contenido de la variable deberé de crear primero una variable, y después pasarsela como parámetro al procedimiento:

SET @total = 0;
CALL total_paises( @total );
SELECT @total;

De esta forma he:

1. Definido una variable con SET.
2. Llamado al procedimiento con CALL.
3. Mostrado el valor de la variable con SELECT SQL.

Video de STORED PROCEDURES

Si prefieres verte una explicación completa en video, con algunos ejemplos y más teoría puedes ver el siguiente video.

Aproximadamente, por la mitad del video, puedes ver ejemplos.