Los procedimientos almacenados son bloques de código SQL que se guardan en la base de datos y se pueden ejecutar cuando se necesiten. Son útiles para encapsular lógica de negocio y mejorar la eficiencia.

1. Introducción a los Procedimientos Almacenados

✅ ¿Qué son?
Un procedimiento almacenado es un conjunto de instrucciones SQL que se ejecutan en la base de datos.

✅ ¿Para qué se usan?

• Automatizar procesos repetitivos (cálculos, validaciones, reportes).
• Mejorar el rendimiento al reducir la carga en la aplicación.
• Asegurar la integridad de los datos.

✅ Diferencia entre Procedimientos y Funciones

2. Creación de Procedimientos Almacenados

1DELIMITER //
2
3CREATE PROCEDURE ObtenerTotalEmpleados()
4BEGIN
5    SELECT COUNT(*) AS total FROM empleados;
6END //
7
8DELIMITER ;
9
10-- Llamar al procedimiento
11CALL ObtenerTotalEmpleados();

3. Parámetros en Procedimientos Almacenados

Los procedimientos pueden recibir parámetros para ser más flexibles.

Ejemplo con Parámetros

1DELIMITER //
2
3CREATE PROCEDURE ObtenerEmpleadosPorDepartamento(IN depto_id INT)
4BEGIN
5    SELECT * FROM empleados WHERE departamento_id = depto_id;
6END //
7
8DELIMITER ;
9
10-- Llamar al procedimiento
11CALL ObtenerEmpleadosPorDepartamento(3);

4. Control de Flujo en Procedimientos Almacenados

Podemos usar estructuras de control como IF, CASE, WHILE y LOOP.

Ejemplo con IF y WHILE

1DELIMITER //
2
3CREATE PROCEDURE VerificarEdad(IN edad INT, OUT mensaje VARCHAR(50))
4BEGIN
5    IF edad >= 18 THEN
6        SET mensaje = 'Es mayor de edad';
7    ELSE
8        SET mensaje = 'Es menor de edad';
9    END IF;
10END //
11
12DELIMITER ;
13
14-- Llamar al procedimiento
15CALL VerificarEdad(20, @resultado);
16SELECT @resultado;

5. Manejo de Errores en Procedimientos Almacenados

Los errores pueden ocurrir, por ejemplo, si un procedimiento intenta dividir por cero. Es importante manejarlos correctamente.

1DELIMITER //
2
3CREATE PROCEDURE DividirNumeros(IN num1 INT, IN num2 INT, OUT resultado FLOAT)
4BEGIN
5    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
6    SET resultado = NULL;
7
8    IF num2 = 0 THEN
9        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: División por cero';
10    ELSE
11        SET resultado = num1 / num2;
12    END IF;
13END //
14
15DELIMITER ;
16
17-- Llamar al procedimiento
18CALL DividirNumeros(10, 0, @res);
19SELECT @res;

6. Optimización de Procedimientos Almacenados

Para mejorar el rendimiento:

• Usar índices en las columnas usadas en consultas frecuentes.
• Evitar cursores cuando no sean necesarios.
• Limitar la cantidad de datos procesados con LIMIT o FETCH FIRST N ROWS.

Ejemplo de optimización:

1SELECT nombre FROM empleados WHERE departamento_id = 3 LIMIT 10;

7. Ejemplo Completo

Vamos a crear un procedimiento que registra una nueva venta, verificando si el producto tiene stock suficiente antes de descontarlo.

1DELIMITER //
2
3CREATE PROCEDURE RegistrarVenta(IN producto_id INT, IN cantidad INT)
4BEGIN
5    DECLARE stock_actual INT;
6
7    -- Obtener el stock
8    SELECT stock INTO stock_actual FROM productos WHERE id = producto_id;
9
10    -- Verificar si hay stock suficiente
11    IF stock_actual >= cantidad THEN
12        -- Registrar la venta
13        INSERT INTO ventas (producto_id, cantidad, fecha) VALUES (producto_id, cantidad, NOW());
14        -- Actualizar stock
15        UPDATE productos SET stock = stock - cantidad WHERE id = producto_id;
16    ELSE
17        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock insuficiente';
18    END IF;
19END //
20
21DELIMITER ;
22
23-- Llamar al procedimiento
24CALL RegistrarVenta(1, 5);

Ejemplos de procedimientos almacenados en MySQL que utilizan IF, CASE, WHILE y LOOP.

1. Ejemplo con IF

Este ejemplo verifica si un empleado es mayor de edad (18 años) y muestra un mensaje basado en la edad.

1DELIMITER $$
2
3CREATE PROCEDURE VerificarEdadEmpleado(IN empleado_id INT)
4BEGIN
5    DECLARE edad INT;
6    
7    -- Obtener la edad del empleado
8    SELECT TIMESTAMPDIFF(YEAR, fecha_contratacion, CURDATE()) INTO edad
9    FROM empleados
10    WHERE id = empleado_id;
11    
12    -- Verificar si el empleado es mayor de edad
13    IF edad >= 18 THEN
14        SELECT 'Empleado mayor de edad' AS mensaje;
15    ELSE
16        SELECT 'Empleado menor de edad' AS mensaje;
17    END IF;
18END $$
19
20DELIMITER ;

Explicación:

• IF edad >= 18 THEN: Compara si la edad del empleado es mayor o igual a 18 y devuelve el mensaje correspondiente.

2. Ejemplo con CASE

Este ejemplo utiliza CASE para verificar el nivel de experiencia de un empleado basado en su antigüedad y asigna un mensaje correspondiente.

1DELIMITER $$
2
3CREATE PROCEDURE VerificarExperienciaEmpleado(IN empleado_id INT)
4BEGIN
5    DECLARE antiguedad INT;
6
7    -- Obtener la antigüedad del empleado
8    SELECT TIMESTAMPDIFF(YEAR, fecha_contratacion, CURDATE()) INTO antiguedad
9    FROM empleados
10    WHERE id = empleado_id;
11
12    -- Usar CASE para clasificar la antigüedad
13    SELECT 
14        CASE 
15            WHEN antiguedad < 1 THEN 'Empleado sin experiencia'
16            WHEN antiguedad BETWEEN 1 AND 3 THEN 'Empleado con poca experiencia'
17            WHEN antiguedad BETWEEN 4 AND 6 THEN 'Empleado con experiencia media'
18            WHEN antiguedad > 6 THEN 'Empleado con alta experiencia'
19            ELSE 'Antigüedad desconocida'
20        END AS nivel_experiencia;
21END $$
22
23DELIMITER ;

Explicación:

• CASE: Evalúa la antigüedad del empleado y devuelve una clasificación sobre su nivel de experiencia.

3. Ejemplo con WHILE

Este ejemplo muestra cómo usar un bucle WHILE para contar y mostrar los empleados hasta un número determinado.

1DELIMITER $$
2
3CREATE PROCEDURE ContarEmpleadosLimite(IN limite INT)
4BEGIN
5    DECLARE contador INT DEFAULT 0;
6    
7    -- Bucle WHILE que cuenta hasta el límite especificado
8    WHILE contador < limite DO
9        -- Incrementa el contador y muestra el número de empleado
10        SET contador = contador + 1;
11        SELECT CONCAT('Empleado número ', contador) AS mensaje;
12    END WHILE;
13END $$
14
15DELIMITER ;

Explicación:

• WHILE contador < limite DO: El bucle sigue ejecutándose mientras contador sea menor que el limite proporcionado como parámetro.

4. Ejemplo con LOOP

Este ejemplo muestra cómo usar un bucle LOOP para iterar a través de los empleados y mostrar su nombre.

1DELIMITER $$
2
3CREATE PROCEDURE MostrarEmpleados()
4BEGIN
5    DECLARE done INT DEFAULT 0;
6    DECLARE emp_nombre VARCHAR(100);
7    DECLARE emp_cursor CURSOR FOR 
8        SELECT nombre FROM empleados;
9
10    -- Declarar un manejador para finalizar el bucle
11    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
12    
13    OPEN emp_cursor;
14    
15    -- Bucle LOOP para recorrer los empleados
16    REPEAT
17        FETCH emp_cursor INTO emp_nombre;
18        
19        IF NOT done THEN
20            SELECT emp_nombre AS empleado;
21        END IF;
22    UNTIL done END REPEAT;
23
24    CLOSE emp_cursor;
25END $$
26
27DELIMITER ;

Explicación:

• LOOP: Utiliza un bucle LOOP con un cursor para recorrer los empleados y mostrar su nombre uno por uno.

Ejecución de los procedimientos:

Una vez que hayas creado los procedimientos, puedes ejecutarlos de la siguiente manera:

1-- Llamar al procedimiento VerificarEdadEmpleado
2CALL VerificarEdadEmpleado(1);
3
4-- Llamar al procedimiento VerificarExperienciaEmpleado
5CALL VerificarExperienciaEmpleado(1);
6
7-- Llamar al procedimiento ContarEmpleadosLimite con un límite de 5
8CALL ContarEmpleadosLimite(5);
9
10-- Llamar al procedimiento MostrarEmpleados
11CALL MostrarEmpleados();

Estos ejemplos ilustran cómo utilizar IF, CASE, WHILE y LOOP dentro de procedimientos almacenados en MySQL.