queryLab · software_de_entrenamiento en espera
Nivel 4 · Avanzado · SQL · programación

[SQL 15] Procedimientos, funciones y triggers

Lógica dentro de la base: procedimientos almacenados (MySQL/Oracle) y triggers que se ejecutan solos.
0 / 4 retos resueltos 📘 Semana 10 · apunte de procedimientos · guía Workbench (ActualizarPrecioSimple, AumentarStock)
Al terminar esta práctica podrás…
  • Leer y escribir un procedimiento almacenado en MySQL (DELIMITER, CREATE PROCEDURE, IN, CALL) y su equivalente en Oracle PL/SQL.
  • Distinguir procedimiento, función y trigger.
  • Crear triggers BEFORE / AFTER INSERT / UPDATE / DELETE usando NEW y OLD.
  • Implementar auditoría y validaciones automáticas con triggers.

Las bases de datos permiten guardar lógica junto a los datos:

  • Procedimiento almacenado: un bloque de sentencias con nombre y parámetros que se ejecuta con CALL (MySQL) o EXEC. Ideal para operaciones repetitivas del negocio: «actualiza el precio del SKU 105 a 5000».
  • Función: como un procedimiento, pero devuelve un valor y se usa dentro de un SELECT.
  • Trigger (disparador): se ejecuta automáticamente cuando ocurre un INSERT, UPDATE o DELETE sobre una tabla. Dentro del trigger, NEW es la fila nueva y OLD la fila anterior.

Sobre este motor: SQLite no tiene procedimientos almacenados (está pensado para ir incrustado en una aplicación), pero sí tiene triggers. Por eso los procedimientos se muestran con la sintaxis de MySQL y Oracle, la que usas en Workbench y en la evaluación, y los triggers se practican aquí con código que se ejecuta de verdad.

Sintaxis
-- Trigger (SQLite / estándar)
CREATE TRIGGER nombre
AFTER UPDATE OF columna ON tabla
FOR EACH ROW
WHEN NEW.columna <> OLD.columna
BEGIN
  INSERT INTO auditoria VALUES (OLD.id, OLD.columna, NEW.columna);
END;
CREATE PROCEDURE
Bloque con nombre y parámetros IN / OUT / INOUT; se ejecuta con CALL.
DELIMITER //
En MySQL cambia el fin de sentencia para poder escribir ; dentro del cuerpo.
CREATE FUNCTION
Devuelve un valor con RETURN; se usa en SELECT.
BEFORE / AFTER
El trigger corre antes (para validar o corregir) o después (para auditar).
NEW / OLD
Valores nuevos (INSERT, UPDATE) y anteriores (UPDATE, DELETE).
RAISE
SQLite: RAISE(ABORT, 'msg') cancela; RAISE(IGNORE) descarta la fila. MySQL: SIGNAL.

1 · Ejemplos guiados · ejecuta y modifica cada bloque

01Trigger de auditoría
Cada cambio de salario queda registrado automáticamente en una tabla de auditoría con el valor anterior y el nuevo.
sql-programacion.sql
Presiona «ejecutar» para ver el resultado.
02Trigger de validación (BEFORE)
Impide registrar un cargo cuyo salario mínimo supera al máximo. El INSERT de prueba falla con nuestro mensaje: error esperado.
sql-programacion.sql
Presiona «ejecutar» para ver el resultado.
03Trigger que normaliza datos
Al insertar un empleado, el trigger deja su email en mayúsculas, como el resto de la tabla.
sql-programacion.sql
Presiona «ejecutar» para ver el resultado.
04Ver los triggers definidos
El catálogo sqlite_master guarda la definición de cada objeto (en MySQL: SHOW TRIGGERS).
sql-programacion.sql
Presiona «ejecutar» para ver el resultado.

Sintaxis en otros motores (referencia, no se ejecuta aquí)

MySQL Procedimiento: actualizar el precio de un producto (guía Workbench)
El gerente dice «el SKU 105 subió a 5000». En vez de escribir el UPDATE cada vez, se guarda como procedimiento con dos parámetros de entrada.
DELIMITER //
CREATE PROCEDURE ActualizarPrecioSimple(
    IN codigo_sku_buscado INT,
    IN nuevo_precio INT
)
BEGIN
    UPDATE Producto
    SET Precio = nuevo_precio
    WHERE CodigoSku = codigo_sku_buscado;
END //
DELIMITER ;

CALL ActualizarPrecioSimple(105, 5000);
MySQL Procedimiento: aumentar el stock al recibir mercadería
DELIMITER //
CREATE PROCEDURE AumentarStock(IN sku_producto INT, IN cantidad_nueva INT)
BEGIN
    UPDATE Producto
    SET Cantidad = Cantidad + cantidad_nueva
    WHERE CodigoSku = sku_producto;
END //
DELIMITER ;

CALL AumentarStock(200, 5);
MySQL Procedimiento con parámetro de salida y variables
Un parámetro OUT devuelve un resultado a quien llama; @total es una variable de sesión.
DELIMITER //
CREATE PROCEDURE ContarEmpleadosDepto(IN p_depto INT, OUT p_total INT)
BEGIN
    SELECT COUNT(*) INTO p_total
    FROM employees
    WHERE department_id = p_depto;
END //
DELIMITER ;

CALL ContarEmpleadosDepto(60, @total);
SELECT @total;
Oracle PL/SQL El mismo procedimiento en Oracle
CREATE OR REPLACE PROCEDURE reajustar_depto (
    p_depto IN NUMBER,
    p_pct   IN NUMBER
) AS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_pct / 100)
    WHERE department_id = p_depto;
    COMMIT;
END;
/

EXEC reajustar_depto(60, 10);
MySQL Función que devuelve un valor
DELIMITER //
CREATE FUNCTION salario_anual(p_salary DECIMAL(10,2))
RETURNS DECIMAL(12,2) DETERMINISTIC
BEGIN
    RETURN p_salary * 12;
END //
DELIMITER ;

SELECT first_name, salario_anual(salary) FROM employees;

⚠ Errores frecuentes

  • Olvidar DELIMITER en MySQL: el primer ; del cuerpo corta la sentencia CREATE PROCEDURE.
  • Un trigger que modifica la misma tabla que lo dispara puede encadenarse sin fin: acota con WHEN y WHERE.
  • Esconder demasiada lógica de negocio en triggers: se vuelve difícil de seguir y de depurar. Documenta cada uno.
  • Confundir NEW y OLD: en un DELETE no existe NEW; en un INSERT no existe OLD.

2 · Retos con corrección automática

Escribe tu solución y pulsa comprobar (Ctrl + Enter). Tu consulta y la solución se ejecutan en bases limpias y se comparan los resultados: no importan los nombres de las columnas, sí los datos (y el orden, cuando el reto lo pide). Si te atascas, usa la pista o mira el resultado esperado.

3 · Preguntas tipo solemne

1. En MySQL, ¿para qué sirve DELIMITER // antes de crear un procedimiento?
2. ¿Cómo se ejecuta en MySQL el procedimiento AumentarStock para sumar 5 unidades al SKU 200?
3. ¿Qué diferencia principal hay entre una función y un procedimiento?
4. En un trigger BEFORE UPDATE, ¿qué contiene OLD.salary?
← anterior siguiente →