Procedimientos almacenados en SQL

Los procedimientos almacenados son bloques de código que ejecutan una secuencia determinada de acciones en la base de datos.

A diferencia de las funciones, los procedimientos pueden modificar datos y ejecutar una lógica de negocio compleja, y no tienen por qué devolver un valor.

A diferencia de las funciones, los procedimientos pueden modificar datos y ejecutar una lógica de negocio compleja, pero no pueden devolver valores.

Estructura general de un procedimiento almacenado

MySQL 8.4

CREATE PROCEDURE nombre_procedimiento([[IN | OUT | INOUT] parametro tipo [, ...]])
BEGIN
    -- lógica del procedimiento
END;

[ ]parte opcional
a | buna de las opciones
...se puede repetir

nombre_procedimiento: El nombre que usas para llamar al procedimiento con la sentencia CALL.
IN | OUT | INOUT: La dirección del parámetro: hacia el procedimiento, desde él o en ambos sentidos. Por defecto es IN; lo verás en detalle más adelante en la lección.
Palabra claveOpcional
parametro: El nombre con el que el valor del parámetro está disponible dentro del procedimiento. Puede haber varios parámetros separados por comas y, si no hacen falta, los paréntesis se dejan vacíos.
Opcional
tipo: El tipo de datos del parámetro: INT, DATE, VARCHAR(50), etc.

MySQL 8.4

CREATE OR REPLACE PROCEDURE nombre_procedimiento([parametro tipo [, ...]])
LANGUAGE plpgsql
AS $$
BEGIN
    -- lógica del procedimiento
END;
$$;

[ ]parte opcional
...se puede repetir

nombre_procedimiento: El nombre que usas para llamar al procedimiento con la sentencia CALL.
parametro: El nombre con el que el valor del parámetro está disponible dentro del procedimiento. Puede haber varios parámetros separados por comas y, si no hacen falta, los paréntesis se dejan vacíos.
Opcional
tipo: El tipo de datos del parámetro: INT, DATE, VARCHAR(50), etc.

LANGUAGE plpgsql indica que el procedimiento está escrito en PL/pgSQL (el lenguaje procedimental de PostgreSQL).

AS $$ ... $$ es el entrecomillado con dólar, una forma especial de delimitar el cuerpo del procedimiento. Evita tener que escapar caracteres dentro del procedimiento.

Ejemplo sencillo de procedimiento

Vamos a crear un procedimiento para actualizar los datos de un estudiante:

MySQL 8.4
-- Creamos el procedimiento
CREATE PROCEDURE update_student_info(
    IN student_id INT,
    IN new_first_name VARCHAR(50),
    IN new_last_name VARCHAR(50)
)
BEGIN
    UPDATE Student
    SET first_name = new_first_name,
        last_name = new_last_name
    WHERE id = student_id;
END;

-- Llamamos al procedimiento
CALL update_student_info(1, 'Alexander', 'Smirnov');

-- Comprobamos el resultado
SELECT * FROM Student WHERE id = 1;

MySQL 8.4
-- Creamos el procedimiento
CREATE OR REPLACE PROCEDURE update_student_info(
    student_id INT,
    new_first_name VARCHAR(50),
    new_last_name VARCHAR(50)
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE Student
    SET first_name = new_first_name,
        last_name = new_last_name
    WHERE id = student_id;
END;
$$;

-- Llamamos al procedimiento
CALL update_student_info(1, 'Alexander', 'Smirnov');

-- Comprobamos el resultado
SELECT * FROM Student WHERE id = 1;

id	first_name	middle_name	last_name	birthday	address
1	Alexander	Fedorovich	Smirnov	2000-10-01T00:00:00.000Z	ul. Pushkina, d. 36, kv. 5

Este procedimiento recibe el ID del estudiante y los nuevos datos, y después actualiza el registro correspondiente de la tabla Student.

Tipos de parámetros de los procedimientos

En MySQL, los procedimientos admiten tres tipos de parámetros que se pueden pasar a un procedimiento almacenado:

IN: parámetros de entrada (por defecto)
OUT: parámetros de salida para devolver valores
INOUT: parámetros que pueden ser tanto de entrada como de salida

Parámetros de entrada (IN)

Los parámetros de entrada pasan datos al procedimiento. Es el tipo de parámetro más habitual:

MySQL 8.4
CREATE PROCEDURE add_subject(
    IN subject_id INT,
    IN subject_name VARCHAR(100)
)
BEGIN
    INSERT INTO Subject (id, name)
    VALUES (subject_id, subject_name);
END;

-- Llamada con parámetros de entrada
CALL add_subject(15, 'Mathematics');

Parámetros de salida (OUT)

Los parámetros de salida permiten que el procedimiento devuelva valores:

MySQL 8.4
CREATE PROCEDURE get_student_info(
    IN student_id INT,
    OUT student_name VARCHAR(100),
    OUT student_age INT
)
BEGIN
    SELECT
        CONCAT(first_name, ' ', last_name),
        TIMESTAMPDIFF(YEAR, birthday, CURDATE())
    INTO student_name, student_age
    FROM Student
    WHERE id = student_id;
END;

-- Llamada al procedimiento con parámetros de salida
CALL get_student_info(1, @name, @age);
SELECT @name AS student_name, @age AS student_age;

student_name	student_age
Nikolaj Sokolov	24

Parámetros de entrada y salida (INOUT)

Los parámetros INOUT pueden recibir un valor y devolverlo modificado:

MySQL 8.4
CREATE PROCEDURE calculate_discount(
    INOUT price DECIMAL(10,2),
    IN discount_percent INT
)
BEGIN
    SET price = price - (price * discount_percent / 100);
END;

-- Uso de un parámetro INOUT
SET @original_price = 1000.00;
CALL calculate_discount(@original_price, 15);
SELECT @original_price AS discounted_price;

discounted_price
850

Ejemplo de los tres tipos de parámetros

Ejemplos de cómo funcionan los parámetros en un procedimiento almacenado

Diferencias clave entre los tipos de parámetros

Tipo de parámetro	Dirección	Uso
IN	Entrada	Pasar datos al procedimiento
OUT	Salida	Devolver un resultado desde el procedimiento
INOUT	Bidireccional	Modificar el valor recibido

Gestión de procedimientos almacenados

Ver los procedimientos existentes

MySQL 8.4
SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

MySQL 8.4
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE' AND routine_schema = 'public';

Eliminar un procedimiento

MySQL 8.4
DROP PROCEDURE IF EXISTS add_student;

MySQL 8.4
DROP PROCEDURE IF EXISTS add_student(VARCHAR, VARCHAR, DATE);

Modificar un procedimiento

Para modificar un procedimiento en MySQL, primero hay que eliminar la versión antigua y después crear una nueva:

MySQL 8.4
DROP PROCEDURE IF EXISTS add_student;
-- Crear la nueva versión del procedimiento
CREATE PROCEDURE add_student(...) ...

En PostgreSQL puedes usar CREATE OR REPLACE PROCEDURE:

MySQL 8.4
CREATE OR REPLACE PROCEDURE add_student(
    student_first_name VARCHAR(50),
    student_last_name VARCHAR(50),
    student_birthday DATE
)
-- nueva implementación

Los procedimientos almacenados son una herramienta muy potente para implementar una lógica de negocio compleja directamente en la base de datos. ¡Centralizan la lógica, mejoran el rendimiento y garantizan la integridad de los datos! 🚀