Recorra todas las filas de una tabla MySQL

1. Recorra todas las filas de una tabla MySQL
2. Use un bucle WHILE en un procedimiento almacenado para recorrer todas las filas de la tabla MySQL
3. Use CURSOR en un procedimiento almacenado para recorrer todas las filas de la tabla MySQL
4. Comparación del uso de WHILE y CURSOR

Hoy, aprenderemos sobre el uso de WHILE y CURSOR en un procedimiento almacenado para recorrer todas las filas de una tabla MySQL. También exploraremos los pros y los contras de cada técnica para diferenciar cuál es adecuada en qué situación.

Recorra todas las filas de una tabla MySQL

Aprenderemos sobre varios enfoques que podemos usar para recorrer todas las filas de una tabla MySQL para leer/insertar datos de una o varias tablas en particular. Aprendamos cada uno de ellos con un ejemplo de código.

Para ello disponemos de dos tablas: empleados y la otra es emp_rendimiento. La tabla empleados tiene EMP_ID, FIRSTNAME, LASTNAME, GENDER y AGE como atributos (también llamados nombres de columna).

La tabla emp_rendimiento tiene los campos PERFORM_ID, FIRSTNAME, LASTNAME y PERFORMANCE, donde FIRSTNAME y LASTNAME son los mismos que en la tabla employees.

Imagínese que tenemos que copiar FIRSTNAME y LASTNAME de la tabla employees e insertarlos en la tabla emp_rendimiento para calcular el RENDIMIENTO de cada empleado cada mes.

Debe haber una forma de SELECCIONAR los valores necesarios de la tabla empleados, INSERTAR en la tabla emp_rendimiento y continuar con el cálculo de RENDIMIENTO más adelante. También puedes crear empleados y emp_perfomance para continuar con nosotros; el código se da a continuación.

Código de ejemplo:

#create an `employees` table
CREATE TABLE employees (
  EMP_ID INT NOT NULL AUTO_INCREMENT,
  FIRSTNAME VARCHAR(45) NOT NULL,
  LASTNAME VARCHAR(45) NOT NULL,
  GENDER VARCHAR(45) NOT NULL,
  AGE INT NOT NULL,
  PRIMARY KEY (EMP_ID));

#insert data
INSERT INTO employees (FIRSTNAME, LASTNAME, GENDER, AGE) VALUES
('Mehvish','Ashiq', 'Female', 30),
('Thomas', 'Christopher', 'Male', 22),
('John', 'Daniel', 'Male', 34),
('Saira', 'James', 'Female', 27);

#create a `emp_performance` table
CREATE TABLE emp_performance (
  PERFORM_ID INT NOT NULL AUTO_INCREMENT,
  FIRSTNAME VARCHAR(45) NOT NULL,
  LASTNAME VARCHAR(45) NOT NULL,
  PERFORMANCE  VARCHAR(45) NULL,
  PRIMARY KEY (PERFORM_ID));

Podemos usar WHILE y CURSOR en un procedimiento almacenado para recorrer todas las filas de la tabla empleados e INSERTAR en la tabla emp_rendimiento.

Use un bucle WHILE en un procedimiento almacenado para recorrer todas las filas de la tabla MySQL

Ahora, tenemos nuestras mesas listas. Entonces, podemos escribir y ejecutar el siguiente procedimiento para SELECCIONAR el FIRSTNAME y el LASTNAME de la tabla employees e INSERTAR en la tabla emp_performance.

Código de ejemplo:

DROP PROCEDURE IF EXISTS CALCPERFORMANCE;
DELIMITER ;;

CREATE PROCEDURE CALCPERFORMANCE()
BEGIN
DECLARE length INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
SELECT COUNT(*) FROM employees INTO length;
SET counter=0;
WHILE counter<length DO
  INSERT INTO emp_performance(FIRSTNAME, LASTNAME)
  SELECT FIRSTNAME, LASTNAME FROM employees LIMIT counter,1;
  SET counter = counter + 1;
END WHILE;
End;
;;

DELIMITER ;
CALL CALCPERFORMANCE();

Una vez insertados los registros, utilice el comando SELECT para ver la salida emp_performance.

SELECT * from emp_performance;

Salida (tabla emp_rendimiento usando el bucle WHILE):

Una vez que ejecute el procedimiento almacenado CALCPERFORMANCE, solo se completarán PERFORM_ID, FIRSTNAME y LASTNAME.

Explicación del código usando números de línea

1. La línea 1 elimina cualquier procedimiento ya creado con el nombre CALCPERFORMANCE.
2. La línea 2 cambia el delimitador predeterminado a ;;. De esta forma, el SQL no ejecutará todas las líneas mientras intentamos escribir un procedimiento para realizar una tarea específica.
3. La línea 4 crea el procedimiento utilizando el nombre de procedimiento proporcionado.
4. Todas las declaraciones necesarias para este procedimiento se escribirían entre la Línea 5 y la 15.
5. La línea 5 inicia el procedimiento.
6. Las líneas 6 y 7 declaran una variable denominada longitud y contador de tipo INT cuyo valor DEFAULT es 0 y 0, respectivamente.
7. En la línea 8, SELECCIONAMOS el recuento de la tabla empleados y asignamos ese valor a la variable longitud.
8. La línea 9 pone el contador en 0.
9. Tenemos un bucle WHILE de las líneas 10-14 que SELECCIONA el FIRSTNAME y el LASTNAME de la tabla employees e INSERTA en la tabla emp_rendimiento. Recuerde, solo se selecciona e inserta un registro en cada iteración.
10. La línea 18 restablece el delimitador nuevamente a su valor predeterminado, que es ;.
11. La línea 19 llama al procedimiento.

De manera similar, podemos usar CURSOR para recorrer todas las filas de la tabla en MySQL. Veamos el siguiente apartado.

Use CURSOR en un procedimiento almacenado para recorrer todas las filas de la tabla MySQL

Usamos un CURSOR para manejar el conjunto de resultados en un procedimiento almacenado. Nos permite recorrer un conjunto de registros (filas) devueltos por una consulta y procesar cada fila individualmente.

Uno debe tener las siguientes propiedades de CURSOR mientras lo usa.

1. El CURSOR es sensible; no es necesario que el servidor también haga la copia de la tabla de resultados.
2. El CURSOR no es actualizable porque es de solo lectura.
3. El CURSOR no es desplazable. Solo podemos atravesarlo en una dirección sin omitir y saltar registros (filas) en un conjunto de resultados.

Código de ejemplo:

DROP PROCEDURE IF EXISTS cursor_CALCPERFORMANCE;
DELIMITER ;;

CREATE PROCEDURE cursor_CALCPERFORMANCE()
BEGIN
DECLARE cursor_FIRSTNAME VARCHAR(45) DEFAULT "";
DECLARE cursor_LASTNAME VARCHAR(45) DEFAULT "";
DECLARE done INT DEFAULT FALSE;
DECLARE cursor_e CURSOR FOR SELECT FIRSTNAME,LASTNAME FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_e;
read_loop: LOOP
  FETCH cursor_e INTO cursor_FIRSTNAME, cursor_LASTNAME;
  IF done THEN
    LEAVE read_loop;
  END IF;
  INSERT INTO emp_performance (FIRSTNAME,LASTNAME)
  VALUES (cursor_FIRSTNAME, cursor_LASTNAME);
END LOOP;
CLOSE cursor_e;
END;
;;

DELIMITER ;
CALL cursor_CALCPERFORMANCE();

Una vez insertados los registros, utilice el comando SELECT para ver la salida emp_performance.

SELECT * from emp_performance;

Salida (tabla emp_rendimiento usando CURSOR):

Explicación del código usando números de línea

1. La línea 1 elimina el procedimiento existente con el mismo nombre.
2. La línea 2 cambia el delimitador a ;;.
3. La línea 4 crea el procedimiento utilizando el nombre de procedimiento dado.
4. Todas las declaraciones necesarias se escribirán entre la línea 5 y la 21.
5. La línea 5 inicia el procedimiento.
6. Las líneas 6, 7 y 8 declaran una variable denominada cursor_FIRSTNAME, cursor_LASTNAME, y done de tipo VARCHAR(45), VARCHAR(45), e INT cuyos valores DEFAULT son "", "", y FALSO, respectivamente.
7. La línea 9 declara un CURSOR asociado con la sentencia SELECT.
8. La línea 10 declara el controlador NO ENCONTRADO donde se usa la variable terminado para mostrar que el CURSOR ha llegado al final del conjunto de resultados.
9. La línea 11 abre el CURSOR.
10. En la línea 12-19, iteramos sobre la lista de FIRSTNAME y LASTNAME para INSERTAR en la tabla emp_rendimiento.
11. La línea 20 finaliza el CURSOR, mientras que la línea 21 finaliza el procedimiento almacenado.
12. La línea 24 restablece el delimitador nuevamente a su valor predeterminado, que es ;.
13. La línea 25 llama al procedimiento.

Comparación del uso de WHILE y CURSOR

Debemos conocer los pros y los contras de cada enfoque cuando tenemos múltiples formas de lograr una cosa.

Pros y contras del bucle WHILE:

Pros y Contras del CURSOR: