MySQL学习笔记8——游标

咱们前面学习的MySQL数据操作语句，都是针对结果集合的。也就是说，每次处理的对象都是一个数据集合。如果需要逐一处理结果集中的记录，就会非常困难。

虽然我们也可以通过筛选条件WHERE和HAVING，或者是限定返回记录的关键字LIMIT返回一条记录，但是却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录， 并对记录的数据进行处理。

这个时候，就可以用到游标。所谓的游标，也就是能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。

1、游标的使用步骤

游标只能在存储程序内使用，存储程序包括存储过程和存储函数。

关于存储过程，我们上节课刚刚学过，这里简单介绍一下存储函数。创建存储函数的语法是：

CREATE FUNCTION 函数名称 (参数) RETURNS 数据类型 程序体
1

存储函数与存储过程很像，但有几个不同点：

1. 存储函数必须返回一个值或者数据表，存储过程可以不返回。
2. 存储过程可以通过CALL语句调用，存储函数不可以。
3. 存储函数可以放在查询语句中使用，存储过程不行。
4. 存储过程的功能更加强大，包括能够执行对表的操作(比如创建表，删除表等)和事务操作，这些功能是存储函数不具备的。

因为游标在存储过程中更常用，所以我们主要学习下游标在存储过程中的使用方法。游标在存储函数中的使用方法和在存储过程中的使用方法是一样的。

-- 第一步 定义游标
DECLARE 游标名 CURSOR FOR 查询语句

-- 第二步 打开游标
OPEN 游标名称;

-- 第三步 从游标的数据结果集中读取数据；游标的查询结果集中的字段数，必须跟INTO后面的变量数一致。
FETCH 游标名 INTO 变量列表;

-- 关闭游标
CLOSE 游标名;
1
2
3
4
5
6
7
8
9
10
11

游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束， 影响系统运行的效率。
所以用完游标之后，一定要记住及时关闭游标，释放游标占用的系统资源。

假设我们有一个名为employees的表，包含员工的ID、姓名和薪水。我们想要创建一个存储过程，该过程遍历这个表，并打印出每个员工的姓名和薪水。

-- 1、创建employees表
CREATE TABLE employees (  
    id INT PRIMARY KEY,  
    name VARCHAR(100),  
    salary DECIMAL(10, 2)  
);

-- 2、插入一些示例数据
INSERT INTO employees (id, name, salary) VALUES  
(1, 'Alice', 5000.00),  
(2, 'Bob', 6000.00),  
(3, 'Charlie', 7000.00);

-- 3、创建一个存储过程来使用游标遍历employees表
DELIMITER //  
CREATE PROCEDURE PrintEmployeeInfo()  
BEGIN  
    DECLARE finished INTEGER DEFAULT 0;  
    DECLARE emp_name VARCHAR(100);  
    DECLARE emp_salary DECIMAL(10, 2);  
    -- 声明游标  
    DECLARE cur CURSOR FOR SELECT name, salary FROM employees;  
    -- 声明NOT FOUND的处理程序  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;  
  
    OPEN cur; -- 打开游标  
  
    get_employee: LOOP  
        FETCH cur INTO emp_name, emp_salary; -- 从游标中获取数据  
        IF finished THEN   
            LEAVE get_employee; -- 如果已经到达结果集的末尾，则退出循环  
        END IF;  
        -- 在这里可以对获取到的数据进行处理，比如打印出来  
        SELECT CONCAT('Employee Name: ', emp_name, ', Salary: ', emp_salary);  
    END LOOP get_employee;  
  
    CLOSE cur; -- 关闭游标  
END //  
DELIMITER ;


--/4、调用这个存储过程来打印每个员工的姓名和薪水
CALL PrintEmployeeInfo();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

2、条件处理语句

DECLARE 处理方式 HANDLER FOR 问题 操作;
1

在MySQL中，DECLARE … HANDLER FOR 语句用于定义错误或条件处理程序。当指定的错误或条件发生时，处理程序会被激活并执行相应的操作。这对于在存储过程、函数或触发器中处理运行时错误或特定条件非常有用。

以下是一个使用DECLARE … HANDLER FOR处理NOT FOUND条件的简单示例，该条件通常与游标一起使用，当游标没有更多的行可以返回时触发：

DELIMITER //  
CREATE PROCEDURE ProcessCursor()  
BEGIN  
    DECLARE done INT DEFAULT FALSE;  
    DECLARE v_name VARCHAR(255);  
    DECLARE cur CURSOR FOR SELECT name FROM some_table;  
    -- 如下处理NOT FOUND
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  

    OPEN cur;  
  
    read_loop: LOOP  
        FETCH cur INTO v_name;  
        IF done THEN  
            LEAVE read_loop;  
        END IF;  
        -- 在这里处理每一行的数据，例如打印出来  
        SELECT v_name;  
    END LOOP;  
  
    CLOSE cur;  
END //  
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

• “处理方式”有2种选择,分别是“CONTINUE"和“EXIT"，表示遇到问题，执行了语法结构中的"操作”之后，是选择继续运行程序，还是选择退出，结束程序。
• 这里执行的“操作”是"SET done=TRUE”，done是我定义的用来标识数据集中的数据是否已经处理完成的一个标记。done=TRUE, 意思是数据处理完成了。

3、流程控制语句

MySQL的流程控制语句主要用于存储过程、函数和触发器中，以控制程序的执行流程。主要有3类：

1. 跳转语句: ITERATE和LEAVE语句。
2. 循环语句: LOOP、WHILE 和REPEAT语句。
3. 条件判断语句: IF 语句和CASE语句。

接下来依次讲解一下跳转语句、 循环语句和条件判断语句。

跳转语句

• ITERATE语句：只能用在循环语句内，表示重新开始循环。
• LEAVE语句:可以用在循环语句内，或者以BEGIN和END包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。

循环语句
1、LOOP语句语法结构：

标签:LOOP
操作
END LOOP 标签;
1
2
3

关于这个语句，需要注意的是，LOOP循环不能自己结束，需要用跳转语句ITERATE或者LEAVE来进行控制。

2、WHILE语句语法结构：

WHILE 条件 DO
操作
END WHILE;
1
2
3

WHILE循环是先判断条件，再执行循环体中的操作。

3、REPEAT语法结构

REPEAT 
操作
UNTIL 条件 END REPEAT;
1
2
3

REPEAT 循环是先执行操作，后判断条件。

条件判断语句
1、IF语句语法结构：

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
1
2
3
4

这里“[ ]”中的内容是可选的。IF 语句的特点是，不同的表达式对应不同的操作。

2、CASE语句语法结构：

CASE 表达式
WHEN 值1 THEN 操作1
[WHEN 值2 THEN 操作2]
[ELSE 操作N]
END CASE;
1
2
3
4
5

这里“[ ]” 中的内容是可选的。CASE语句的特点是，表达式不同的值对应不同的操作。

4、总结

本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。

除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。