游标

在 MySQL 中，游标（Cursor）是一种用于遍历查询结果集的机制。游标允许逐行处理查询结果，这对于需要对每一行数据执行特定操作的情况非常有用。

基本概念

• 游标：一个游标是一个数据库对象，用于指向结果集中的某一行。
• 打开游标：使用 OPEN 语句初始化游标并关联结果集。
• 获取数据：使用 FETCH 语句从游标中提取当前行的数据。
• 关闭游标：使用 CLOSE 语句释放游标占用的资源。

声明游标

DECLARE cursor_name CURSOR FOR select_statement;

打开游标

OPEN cursor_name;

获取数据

FETCH cursor_name INTO variable_list;

关闭游标

CLOSE cursor_name;

处理结束条件

使用 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 来处理游标结束时的情况。

笔记

游标只能在存储过程或存储函数中使用，而不能单独在其他地方使用。

在一个存储过程或一个存储函数中，可以定义多个游标，但是每一个游标的名字必须是唯一的。

游标并不是一条select语句，而是被select语句查询出来的结果集。

示例

创建表

-- 创建 fruits 表
CREATE TABLE fruits (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '水果ID',
    name VARCHAR(50) COMMENT '水果名称',
    type INT COMMENT '水果类型',
    price DECIMAL(5, 2) COMMENT '价格'
);

-- 插入示例数据
INSERT INTO fruits (name, type, price) VALUES
('苹果', 1, 1.20),
('香蕉', 1, 0.90),
('橙子', 2, 0.80),
('葡萄', 2, 2.50),
('草莓', 1, 3.50),
('蓝莓', 2, 4.00);

创建一个存储过程 calculate_total_price_by_type，该过程使用游标遍历 fruits 表中的数据，并计算每个类型的水果的总价格。

CREATE PROCEDURE calculate_total_price_by_type()
BEGIN
	-- 声明变量，用于存储累计总价，初始值为 0.00
    DECLARE total_price DECIMAL(10, 2) DEFAULT 0.00;
    -- 声明变量，用于存储当前记录的类型
    DECLARE current_type INT;
    -- 声明变量，用于存储当前记录的价格
    DECLARE current_price DECIMAL(5, 2);
    -- 声明变量，用于控制循环的结束，初始值为 FALSE（false是TINYINT(0)的别名）
    DECLARE done INT DEFAULT FALSE;
    
    -- 声明游标 cur 用于遍历 fruits 表中的 type 和 price 列
    DECLARE cur CURSOR FOR SELECT type, price FROM fruits;
    -- 声明一个继续处理器，当游标没有更多数据时，将done设置为TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标,准备开始读取数据
    OPEN cur;
    -- 使用LOOP结构循环读取游标中的数据,并将循环设置别名 read_loop
    read_loop: LOOP
    	-- 每次循环使用FETCH cur INTO current_type, current_price;获取当前行的数据
        FETCH cur INTO current_type, current_price;
        -- 如果done为TRUE，表示所有数据已读取完毕，退出循环
        IF done THEN
            LEAVE read_loop;
        -- 否则，累加当前记录的价格到total_price
        END IF;
        SET total_price = total_price + current_price;
        
        -- 输出当前类型和总价
        SELECT CONCAT('Type: ', current_type, ', Total Price: ', total_price) AS result;
    END LOOP;
    
    -- 循环结束后，使用CLOSE cur;关闭游标，释放资源
    CLOSE cur;
END

解释：

1. 创建表：
   • 创建了 fruits 表，包含 id、name、type 和 price 列。
   • 向 fruits 表中插入了示例数据。
2. 设置分隔符：
   • 使用 DELIMITER // 更改分隔符，以确保整个存储过程体被正确解析。
3. 创建存储过程 calculate_total_price_by_type：
   • 声明变量 total_price、current_type、current_price 和 done。
   • 声明游标 cur 用于遍历 fruits 表中的 type 和 price。
   • 使用 OPEN cur 打开游标。
   • 使用 LOOP 循环读取每一行数据，并累加相同类型的总价。
   • 使用 LEAVE read_loop 退出循环。
   • 使用 CLOSE cur 关闭游标。
   • 输出当前类型和累计总价。
4. 重置分隔符：
   • 使用 DELIMITER ; 重置分隔符为默认值。
5. 调用存储过程：
   • 调用存储过程 calculate_total_price_by_type 并查询结果。

result
Type: 1, Total Price: 1.20
Type: 1, Total Price: 2.10
Type: 1, Total Price: 5.60
Type: 2, Total Price: 7.60
Type: 2, Total Price: 11.60
Type: 2, Total Price: 15.60

笔记

解释 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE

1. DECLARE CONTINUE HANDLER: 这是声明一个处理程序的关键字。CONTINUE 表示当处理程序被触发时，继续执行存储过程或触发器中的下一条语句。
2. FOR NOT FOUND: 这部分指定了处理程序处理的情况。NOT FOUND 通常与 SELECT INTO 语句一起使用，表示查询没有返回任何记录。
3. SET done = TRUE: 这是处理程序的具体操作。当查询未找到记录时，将变量 done 设置为 TRUE，通常用于控制循环或逻辑流程