Cursores

Manejo de cursores en MySQL

Programación de Bases de Datos (3.3 y 6.6.- Cursores en MySQL)

Cursores en MariaDB/MySQL

Crear un procedimiento usando un cursor que muestre los nombres y salarios de los empleados con un salario superior a uno dado

DELIMITER $$

CREATE PROCEDURE MostrarEmpleados(salario_minimo FLOAT)
BEGIN
    -- Declaración de variables para el cursor
    DECLARE nombre_empleado VARCHAR(30);
    DECLARE salario_empleado FLOAT(6,2);
    DECLARE fin_cursor BOOLEAN DEFAULT FALSE;

    -- Declaración del cursor
    DECLARE cursor_empleados CURSOR FOR
        SELECT nombre, salario
        FROM empleado
        WHERE salario > salario_minimo;

    -- Declaración del manejador para el fin del cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin_cursor = TRUE;

    -- Abrir el cursor
    OPEN cursor_empleados;

    -- Bucle para iterar sobre los resultados del cursor
    leer_empleados: LOOP
        FETCH cursor_empleados INTO nombre_empleado, salario_empleado;

        -- Verificar si se alcanzó el final del cursor
        IF fin_cursor THEN
            LEAVE leer_empleados;
        END IF;

        -- Mostrar el nombre y salario del empleado
        SELECT CONCAT('Nombre: ', nombre_empleado, ', Salario: ', salario_empleado) AS DetalleEmpleado;
    END LOOP;

    -- Cerrar el cursor
    CLOSE cursor_empleados;
END$$

DELIMITER ;

Ejecución:

CALL MostrarEmpleados(3000);

Ejecución en consola

Ejecución en PhpMyAdmin

Crear un procedimiento usando un cursor para actualizar el salario de los empleados según la antigüedad en la empresa:

si lleva menos de 5 años, se incrementará un 5%

si lleva menos de 10 años, se incrementará un 10%

si lleva menos de 20 años, se incrementará un 20%

y si lleva más de 20 años, se incrementará un 30%

DELIMITER $$

CREATE PROCEDURE ActualizarSalarios()
BEGIN
    -- Declaración de variables para el cursor
    DECLARE _id CHAR(3);
    DECLARE _salario FLOAT(6,2);
    DECLARE _fecha DATE;
    DECLARE _antiguedad INT;
    DECLARE fin_cursor BOOLEAN DEFAULT FALSE;

    -- DECLARE cinco FLOAT DEFAULT 1.05;

    -- Declaración del cursor
    DECLARE cursor_empleados CURSOR FOR
        SELECT cdemp, salario, fecha_ingreso
        FROM empleado;

    -- Declaración del manejador para el fin del cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin_cursor = TRUE;

    -- Abrir el cursor
    OPEN cursor_empleados;

    -- Bucle para iterar sobre los resultados del cursor
    leer_empleados: LOOP
        FETCH cursor_empleados INTO _id, _salario, _fecha;

        -- Verificar si se alcanzó el final del cursor
        IF fin_cursor THEN
            LEAVE leer_empleados;
        END IF;

        -- Calcular la antigüedad en años
        SET _antiguedad = TIMESTAMPDIFF(YEAR, _fecha, CURDATE());

        -- Actualizar el salario según la antigüedad
        IF _antiguedad < 5 THEN
            SET _salario = _salario * 1.05; -- Incremento del 5%
        ELSEIF _antiguedad < 10 THEN
            SET _salario = _salario * 1.10; -- Incremento del 10%
        ELSEIF _antiguedad < 20 THEN
            SET _salario = _salario * 1.20; -- Incremento del 20%
        ELSE
            SET _salario = _salario * 1.30; -- Incremento del 30%
        END IF;

        -- Actualizar el salario en la tabla
        UPDATE empleado
        SET salario = _salario
        WHERE cdemp = _id;
    END LOOP;

    -- Cerrar el cursor
    CLOSE cursor_empleados;
END
$$

DELIMITER ;

Ejecución

CALL ActualizarSalarios();

Refactorizar el código añadiendo constantes ?

Obtener la lista de nombres de empleados que son jefes en la base de datos de proyectos:

DELIMITER $$
CREATE FUNCTION ListadoJefes()
RETURNS VARCHAR(500)

BEGIN
    /*Declaración de variables*/
    DECLARE ultima_fila INT DEFAULT 0;
    DECLARE texto_nombre VARCHAR(50) default '';
    DECLARE resultado VARCHAR(500) default '';
    
    /*Declaración del cursor*/
    DECLARE mi_cursor CURSOR FOR
      SELECT distinct j.nombre 
      FROM empleado e, empleado j
      WHERE e.cdjefe = j.cdemp;
    
    /*Declaración del manipulador de error*/   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET ultima_fila = 1;
    
    /*Abrimos el cursor*/
    OPEN mi_cursor;
    /* Creamos un bucle para recorrer el mi_cursor */
    bucle: LOOP
        /*Cogemos el primer nombre de jefe en la varible*/
        FETCH mi_cursor INTO texto_nombre;

        /*Comprobamos si es la última fila para salir del bucle*/
        IF (ultima_fila = 1) THEN
             LEAVE bucle;
        END IF; 

          /*Vamos concatenando los nombres de los jefes en la variable resultado*/     
         IF resultado = '' THEN
             SET resultado = texto_nombre;
         ELSE
             SET resultado = CONCAT(resultado ,', ' ,texto_nombre);
         END IF;

    END LOOP bucle;

    /*Cerramos el cursor*/
    CLOSE mi_cursor;

    /*Devolvemos la variable de la función*/
    RETURN resultado;
END
$$

DELIMITER ;

Ejecución:

SELECT ListadoJefes();

Cursors

Ejemplo: Crear un texto con todos los emails de una tabla separados por comas

DELIMITER $$

CREATE PROCEDURE create_email_list (
    INOUT email_list TEXT
)
BEGIN
    DECLARE done BOOL DEFAULT false;
    DECLARE email_address VARCHAR(100) DEFAULT "";
    
    -- declare cursor for employee email
    DECLARE cur CURSOR FOR SELECT email FROM employees;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET done = true;
    
    SET email_list = '';

    -- open the cursor
    OPEN cur;
    
    REPEAT
        
        FETCH cur INTO email_address;
        
        IF NOT done THEN
            -- concatenate the email into the emailList
            SET email_list = CONCAT(email_address,"; ",email_list);
        END IF;

        UNTIL done

    END REPEAT;
    
    -- close the cursor
    CLOSE cur;

END
$$

DELIMITER ;

Ejecución

CALL create_email_list (@list);
SELECT @list;

Otra forma de obtener los emails:

DELIMITER $$
CREATE OR REPLACE PROCEDURE listaEmails()
BEGIN
    DECLARE _lista TEXT DEFAULT '';
    FOR _fila IN (SELECT * FROM employees where email IS NOT NULL)
        DO SET _lista := CONCAT (_lista, ', ', _fila.email);
    END FOR;
    SELECT _lista as EMails;
END
$$

DELIMITER ;

Ejecución:

CALL listaEmails();

Ejemplo de cursores