MySQL 存储过程游标

1. 概述

MySQL 存储过程是一种在数据库中存储和执行的一组预编译的SQL语句。存储过程可以接受参数，并且可以返回一个或多个结果集。它通常用于处理复杂的业务逻辑或批量操作数据。

游标是 MySQL 存储过程中的一项重要功能，它可以用来处理返回多个结果集或者需要逐行处理结果的查询。

本文将详细介绍 MySQL 存储过程游标的使用方法，包括游标的定义、打开、关闭、查询、获取行等操作，并通过实例演示来加深理解。

2. 游标的基本概念

在 MySQL 中，游标是用来处理查询结果集的一种数据结构。它可以让存储过程通过逐行的方式读取查询结果，进行相应的处理操作。

游标常用于以下场景：

需要遍历结果集；
需要逐行处理查询结果；
需要将查询结果存储在临时表中等。

MySQL 中的游标分为显式游标和隐式游标两种类型。

显式游标需要通过 DECLARE 语句定义，然后通过 OPEN 语句打开，使用 FETCH 语句获取结果集的行，最后使用 CLOSE 语句关闭游标。

隐式游标不需要显式定义，它是在执行 SQL 语句时自动生成的，可以通过 FOR ... IN ... 语法来遍历结果集中的行。

3. 游标的创建和使用

3.1 游标的定义

在存储过程中，需要使用 DECLARE 语句来定义一个游标变量。语法如下：

DECLARE cursor_name CURSOR FOR select_statement;

其中，cursor_name 是游标的名称，select_statement 是查询语句，用于获取需要处理的结果集。

以下示例演示了如何定义一个游标：

DECLARE cur_employee CURSOR FOR SELECT id, name, age FROM employee;

这个示例中，我们定义了一个名为 cur_employee 的游标，用于获取 employee 表中的 id、name 和 age 列的数据。

3.2 打开和关闭游标

定义游标后，我们需要使用 OPEN 语句来打开游标，并使用 CLOSE 语句来关闭游标。

示例代码如下：

OPEN cursor_name;
...
CLOSE cursor_name;

在打开游标后，我们可以通过游标名称来进行相关操作。

3.3 游标的使用

在打开游标之后，我们可以使用 FETCH 语句来获取游标的结果集中的行。

使用 FETCH 语句时，需要指定从游标中获取的结果保存到哪些变量中。语法如下：

FETCH cursor_name INTO variable_list;

其中，variable_list 是变量列表，用于保存游标结果集中的每一行数据。

以下示例演示了如何使用游标获取结果集的行：

FETCH cur_employee INTO @id, @name, @age;

在上述示例中，我们将 cur_employee 游标的结果集中的一行数据分别保存到 @id、@name 和 @age 变量中。

3.4 完整示例

下面是一个完整的示例，演示了如何使用游标获取 employee 表的数据并进行逐行处理：

DELIMITER //

CREATE PROCEDURE process_employee()
BEGIN
    -- 定义游标
    DECLARE cur_employee CURSOR FOR SELECT id, name, age FROM employee;

    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE employee_id INT;
    DECLARE employee_name VARCHAR(100);
    DECLARE employee_age INT;

    -- 打开游标
    OPEN cur_employee;

    -- 获取游标数据
    FETCH cur_employee INTO employee_id, employee_name, employee_age;
    REPEAT
        -- 处理每一行数据
        -- TODO: 在这里可以对每一行数据进行进一步处理

        -- 获取下一行数据
        FETCH cur_employee INTO employee_id, employee_name, employee_age;
    UNTIL done END REPEAT;

    -- 关闭游标
    CLOSE cur_employee;

END//

DELIMITER ;

在上述示例中，我们首先定义了一个游标 cur_employee，然后声明了一些用于保存结果集数据的变量。接下来，我们打开游标，并使用 FETCH 语句获取游标的结果集中的第一行数据。在 REPEAT 循环中，我们可以对每一行数据进行进一步处理，然后再使用 FETCH 语句获取下一行数据，直到结果集遍历完毕。最后，我们关闭游标。

4. 示例代码

以下示例演示了如何使用游标生成一个简单的报表。假设我们有一个 product 表，包含产品的id、名称和价格。我们要生成一个报表，显示每个产品的名称和价格。

DELIMITER //

CREATE PROCEDURE generate_report()
BEGIN
    -- 定义游标
    DECLARE cur_product CURSOR FOR SELECT name, price FROM product;

    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE product_name VARCHAR(100);
    DECLARE product_price DECIMAL(10, 2);

    -- 打开游标
    OPEN cur_product;

    -- 输出报表标题
    SELECT "产品报表" AS `name`, "价格" AS `price`;

    -- 获取游标数据
    FETCH cur_product INTO product_name, product_price;
    REPEAT
        -- 输出每个产品的名称和价格
        SELECT product_name, product_price;

        -- 获取下一行数据
        FETCH cur_product INTO product_name, product_price;
    UNTIL done END REPEAT;

    -- 关闭游标
    CLOSE cur_product;

END//

DELIMITER ;

在上述示例中，我们首先定义了一个游标 cur_product，然后声明了一些用于保存结果集数据的变量。接下来，我们打开游标，并输出报表标题。在 REPEAT 循环中，我们使用 SELECT 语句输出每个产品的名称和价格，然后再使用 FETCH 语句获取下一行数据，直到结果集遍历完毕。最后，我们关闭游标。

使用以下语句调用存储过程生成报表：