CREATE PROCEDURE

CREATE PROCEDURE define un nuevo procedimiento almacenado en la base de datos. El procedimiento puede contener cualquier sentencia SQL, variables, condicionales y bucles.

Cuando ejecutas CREATE PROCEDURE, MySQL compila el código, verifica que la sintaxis sea correcta y almacena la definición en el catálogo de la base de datos (concretamente en la tabla information_schema.ROUTINES). A partir de ese momento, el procedimiento está disponible para cualquier sesión que tenga permisos de ejecución.

Sintaxis

DELIMITER //
 
CREATE PROCEDURE nombre_procedimiento(lista_parametros)
[características]
BEGIN
    -- Cuerpo del procedimiento
    sentencias_sql;
END //
 
DELIMITER ;

El nombre del procedimiento debe ser único dentro de la base de datos. MySQL no soporta sobrecarga de procedimientos (no puedes tener dos procedimientos con el mismo nombre pero diferentes parámetros, como sí puedes en lenguajes como Java). Si intentas crear un procedimiento con un nombre que ya existe, MySQL devolverá un error. Por eso es habitual preceder el CREATE con un DROP PROCEDURE IF EXISTS.

El bloque BEGIN...END

El cuerpo del procedimiento va encerrado entre BEGIN y END. Este bloque define el ámbito del procedimiento: las variables que declares dentro solo existen mientras el procedimiento se ejecuta, y las sentencias se ejecutan en orden secuencial de arriba a abajo.

Si el procedimiento contiene una sola sentencia, técnicamente puedes omitir BEGIN...END, pero es una mala práctica. Tarde o temprano añadirás más lógica y necesitarás el bloque, así que es mejor incluirlo siempre desde el principio.

Dentro de un bloque BEGIN...END puedes anidar otros bloques BEGIN...END, cada uno con sus propias variables locales. Esto es útil para crear ámbitos aislados, como veremos más adelante.

Características opcionales

Después de los parámetros y antes del BEGIN, puedes especificar varias características que le indican a MySQL cómo debe tratar el procedimiento:

CREATE PROCEDURE nombre()
COMMENT 'Descripción del procedimiento'
DETERMINISTIC           -- Siempre devuelve el mismo resultado para los mismos parámetros
-- o NOT DETERMINISTIC   -- Por defecto
SQL SECURITY DEFINER     -- Se ejecuta con permisos del creador (por defecto)
-- o SQL SECURITY INVOKER -- Se ejecuta con permisos del que lo llama
BEGIN
    -- ...
END

DETERMINISTIC indica que el procedimiento siempre produce el mismo resultado para los mismos parámetros de entrada. Un procedimiento que calcula el IVA de un precio es determinístico; uno que consulta la fecha actual o lee datos que pueden cambiar no lo es. Esta característica es informativa y puede afectar a optimizaciones internas, pero MySQL no la verifica: no te impedirá marcar como DETERMINISTIC un procedimiento que lee datos cambiantes.

SQL SECURITY controla con qué permisos se ejecuta el procedimiento. Con DEFINER (por defecto), se ejecuta con los permisos del usuario que lo creó, permitiendo que usuarios con menos privilegios ejecuten operaciones que normalmente no podrían. Con INVOKER, se ejecuta con los permisos del usuario que lo llama. Veremos esto en detalle en el artículo de control de acceso.

COMMENT es simplemente un texto descriptivo que aparece al consultar los metadatos del procedimiento. Es buena práctica incluir un comentario que explique qué hace el procedimiento, especialmente en bases de datos con decenas de procedimientos.

Ejemplo básico

El procedimiento más simple es uno sin parámetros que ejecuta una consulta:

DELIMITER //
 
CREATE PROCEDURE obtener_productos_activos()
BEGIN
    SELECT id, nombre, precio, stock
    FROM productos
    WHERE activo = TRUE
    ORDER BY nombre;
END //
 
DELIMITER ;

CALL obtener_productos_activos();

Este tipo de procedimiento es útil para encapsular consultas que se ejecutan frecuentemente con criterios fijos. En lugar de que cada aplicación repita la misma consulta con las mismas cláusulas WHERE y ORDER BY, todas llaman al mismo procedimiento.

Procedimiento con lógica

Un procedimiento puede contener múltiples sentencias SQL. Cada SELECT dentro del procedimiento genera un conjunto de resultados independiente que se devuelve al cliente:

DELIMITER //
 
CREATE PROCEDURE resumen_tienda()
BEGIN
    SELECT COUNT(*) AS total_productos FROM productos WHERE activo = TRUE;
    SELECT COUNT(*) AS total_clientes FROM clientes;
    SELECT COUNT(*) AS total_pedidos FROM pedidos;
    SELECT SUM(total) AS ingresos_totales FROM pedidos;
END //
 
DELIMITER ;

CALL resumen_tienda();

Este procedimiento devuelve cuatro conjuntos de resultados, uno por cada SELECT. El cliente los recibe en orden y los puede procesar secuencialmente. En la línea de comandos de MySQL, verás cuatro tablas de resultado una debajo de otra. En lenguajes de programación como PHP o Python, necesitarás usar métodos específicos para navegar entre los múltiples resultados (por ejemplo, nextset() en Python o next_result() en PHP).

Procedimiento con variables

Cuando la lógica del procedimiento necesita almacenar valores intermedios, se utilizan variables locales declaradas con DECLARE. Las variables deben declararse al inicio del bloque BEGIN, antes de cualquier sentencia ejecutable:

DELIMITER //
 
CREATE PROCEDURE estadisticas_producto(IN prod_id INT)
BEGIN
    DECLARE nombre_prod VARCHAR(100);
    DECLARE precio_prod DECIMAL(10,2);
    DECLARE total_ventas INT;
 
    SELECT nombre, precio INTO nombre_prod, precio_prod
    FROM productos WHERE id = prod_id;
 
    SELECT COALESCE(SUM(cantidad), 0) INTO total_ventas
    FROM detalle_pedidos WHERE producto_id = prod_id;
 
    SELECT nombre_prod AS producto,
           precio_prod AS precio,
           total_ventas AS unidades_vendidas,
           precio_prod * total_ventas AS ingreso_total;
END //
 
DELIMITER ;

CALL estadisticas_producto(1);

El patrón SELECT ... INTO variable es la forma estándar de asignar el resultado de una consulta a una variable dentro de un procedimiento. La consulta debe devolver exactamente una fila; si devuelve cero filas, las variables quedan como NULL, y si devuelve más de una, MySQL genera un error. Veremos las variables en profundidad en el artículo dedicado.

BEGIN...END anidados

Puedes anidar bloques BEGIN...END dentro de un procedimiento para crear ámbitos de variables separados. Las variables declaradas en un bloque interior no son visibles fuera de ese bloque:

DELIMITER //
 
CREATE PROCEDURE ejemplo_bloques()
BEGIN
    -- Bloque exterior
    DECLARE x INT DEFAULT 10;
 
    BEGIN
        -- Bloque interior
        DECLARE y INT DEFAULT 20;
        SELECT x + y AS suma;
    END;
 
    -- y no es visible aquí
    SELECT x AS valor_x;
END //
 
DELIMITER ;

Este mecanismo es especialmente útil cuando necesitas cursores con handlers NOT FOUND separados, ya que cada bloque puede tener su propio handler sin interferir con los demás.

Convenciones de nomenclatura

MySQL no impone ninguna convención de nombres para los procedimientos, pero es habitual adoptar una para mantener la coherencia en bases de datos con muchos procedimientos:

• Prefijo sp_: sp_crear_pedido, sp_listar_productos. Identifica rápidamente que es un stored procedure.
• Verbo + sustantivo: obtener_productos, calcular_descuento, procesar_devolucion. Describe la acción del procedimiento.
• snake_case: MySQL no distingue entre mayúsculas y minúsculas en los nombres de procedimientos, así que snake_case es la convención más legible y extendida.

Lo importante es elegir una convención y aplicarla consistentemente en toda la base de datos.

Verificar que existe

Después de crear un procedimiento, puedes verificar su existencia y consultar sus metadatos de varias formas:

-- Listar procedimientos
SHOW PROCEDURE STATUS WHERE Db = 'tienda_mysql'\G

-- Ver la definición
SHOW CREATE PROCEDURE estadisticas_producto\G

-- Desde information_schema
SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'tienda_mysql'
AND ROUTINE_TYPE = 'PROCEDURE';

La consulta a information_schema.ROUTINES es la más flexible, ya que puedes filtrar por cualquier campo y combinarla con otras tablas del catálogo. SHOW CREATE PROCEDURE es la más útil para obtener el código fuente exacto con el que se creó el procedimiento, incluyendo las características y el DEFINER.

Limpieza

DROP PROCEDURE IF EXISTS obtener_productos_activos;
DROP PROCEDURE IF EXISTS resumen_tienda;
DROP PROCEDURE IF EXISTS estadisticas_producto;
DROP PROCEDURE IF EXISTS ejemplo_bloques;

En el siguiente artículo veremos los parámetros IN, OUT e INOUT que permiten pasar y recibir datos de los procedimientos.