迹忆客 专注技术分享

今天，我们将学习在存储过程中使用 WHILE 和 CURSOR 来循环遍历 MySQL 表的所有行。 我们还将探讨每种技术的优缺点，以区分哪种技术适用于何种情况。

遍历 MySQL 表的所有行

我们将了解可用于循环遍历 MySQL 表的所有行以从特定或多个表读取/插入数据的各种方法。 让我们通过代码示例来学习它们中的每一个。

为此，我们有两个表：employees，另一个是 emp_performance。 employees 表具有 EMP_ID、FIRSTNAME、LASTNAME、GENDER 和 AGE 作为属性（也称为列名）。

emp_performance 表有 PERFORM_ID、FIRSTNAME、LASTNAME 和 PERFORMANCE 字段，其中 FIRSTNAME 和 LASTNAME 与 employees 表相同。

试想一下，我们必须从 employees 表中复制 FIRSTNAME 和 LASTNAME，并将它们插入到 emp_performance 表中，以计算每个员工每月的 PERFORMANCE。

必须有一种方法可以从 employees 表中选择必要的值，插入到 emp_performance 表中，然后继续进行 PERFORMANCE 计算。 您还可以创建 employees 和 emp_perfomance 来继续我们的工作； 代码如下。

示例代码：

#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));

我们可以在存储过程中使用 WHILE 和 CURSOR 循环遍历 employees 表的所有行并插入到 emp_performance 表中。

在存储过程中使用 WHILE 循环遍历 MySQL 表的所有行

现在，我们准备好了桌子。 因此，我们可以编写并执行以下过程，从 employees 表中选择 FIRSTNAME 和 LASTNAME，并将其插入到 emp_performance 表中。

示例代码：

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();

插入记录后，使用 SELECT 命令查看 emp_performance 输出。

SELECT * from emp_performance;

输出（使用 WHILE 循环的 emp_performance 表）：

PERORM_ID    FIRSTNAME    LASTNAME    PERFORMANCE
1    Mehvish    Ashiq    NULL
2    Thomas    Christopher    NULL
3    John    Daniel    NULL
4    Saira    James    NULL

执行 CALCPERFORMANCE 存储过程后，将只填充 PERFORM_ID、FIRSTNAME 和 LASTNAME。

使用行号的代码解释

1. 第 1 行删除任何已使用 CALPCERFORMANCE 名称创建的过程。
2. 第 2 行将默认分隔符更改为 ;;。 这样，当我们试图编写一个过程来完成特定任务时，SQL 将不会运行每一行。
3. 第 4 行使用提供的过程名称创建过程。
4. 此过程的所有必要语句都将写在第 5 行和第 15 行之间。
5. 第 5 行开始该过程。
6. 第 6 行和第 7 行声明了一个名为 length 的变量和一个 INT 类型的计数器，其 DEFAULT 值分别为 0 和 0。
7. 在第 8 行，我们从 employees 表中选择计数并将该值分配给长度变量。
8. 第 9 行将计数器设置为 0。
9. 第 10-14 行有一个 WHILE 循环，它从 employees 表中选择 FIRSTNAME 和 LASTNAME，然后将其插入到 emp_performance 表中。 请记住，每次迭代只选择并插入一条记录。
10. 第 18 行再次将分隔符重置为其默认值，即 ;。
11. 第 19 行调用该过程。

同样，我们可以使用 CURSOR 来循环遍历 MySQL 中的所有表行。 让我们看看下面的部分。

在存储过程中使用 CURSOR 循环遍历 MySQL 表的所有行

我们使用 CURSOR 来处理存储过程中的结果集。 它让我们遍历查询返回的一组记录（行）并单独处理每一行。

使用它时必须具有 CURSOR 的以下属性。

1. CURSOR 是敏感的； 服务器也没有必要制作结果表的副本。
2. CURSOR 不可更新，因为它是只读的。
3. CURSOR 不可滚动。 我们只能单向遍历，不能跳过和跳转一个结果集中的记录（行）。

示例代码：

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();

插入记录后，使用 SELECT 命令查看 emp_performance 输出。

SELECT * from emp_performance;

输出（使用 CURSOR 的 emp_performance 表）：

PERORM_ID    FIRSTNAME    LASTNAME    PERFORMANCE
1    Mehvish    Ashiq    NULL
2    Thomas    Christopher    NULL
3    John    Daniel    NULL
4    Saira    James    NULL

使用行号的代码解释

1. 第 1 行删除具有相同名称的现有过程。
2. 第 2 行将分隔符更改为 ;;。
3. 第 4 行使用给定的过程名称创建过程。
4. 所有需要的语句都写在第 5 行和第 21 行之间。
5. 第 5 行开始该过程。
6. 第 6、7 和 8 行声明了一个名为 cursor_FIRSTNAME、cursor_LASTNAME 的变量，并且完成了 VARCHAR(45)、VARCHAR(45) 和 INT 类型，其 DEFAULT 值分别为“”、“”和 FALSE。
7. 第 9 行声明了一个与 SELECT 语句关联的 CURSOR。
8. 第 10 行声明 NOT FOUND 处理程序，其中 finished 变量用于显示 CURSOR 已到达结果集的末尾。
9. 第 11 行打开 CURSOR。
10. 在第 12-19 行中，我们遍历 FIRSTNAME 和 LASTNAME 的列表以插入到 emp_performance 表中。
11. 第 20 行结束 CURSOR，而第 21 行结束存储过程。
12. 第 24 行再次将分隔符重置为其默认值，即 ;。
13. 第 25 行调用该过程。

WHILE 和 CURSOR 用法的比较

当我们有多种方法来完成一件事时，我们必须知道每种方法的优缺点。

WHILE 循环的优点和缺点：

CURSOR 的优点和缺点：