mysql动态游标学习(mysql存储过程游标)

MySQL动态游标学习

在MySQL存储过程中，游标是用于遍历一个结果集并对每一行进行处理的机制。它可以将结果集的某个字段的值赋给一个变量，并在每一次处理时将游标移动到下一个记录上。MySQL存储过程提供了两种类型的游标： 静态游标和动态游标。本文主要介绍动态游标的使用。

动态游标

动态游标在定义时必须使用一个SELECT语句，这个语句的结果集就是动态游标的初始结果集。一旦定义完成后，可以使用OPEN语句打开该游标，并可以在存储过程中使用FETCH语句从结果集中检索数据行。注意：因为动态游标在定义时关联了一条SELECT语句（即数据集），因此每次打开游标时，都要执行一次该SQL语句来获取最新的数据集。这是动态游标和静态游标的一个不同点。

动态游标关键语句说明

• DECLARE语句：声明需要使用动态游标，需要提供以下3个参数

  DECLARE cursor_name CURSOR FOR SELECT_statement;

  • cursor_name：游标的名称，自定义
  • SELECT_statement：需要执行的SQL查询语句

• OPEN语句：打开一个游标结果集并开始遍历行

  OPEN cursor_name;

• FETCH语句：从游标结果集中获取行，必须紧跟在OPEN语句之后。

  FETCH [NEXT|PRIOR] FROM cursor_name INTO variable

  • NEXT：从游标集合中按顺序获取下一行。
  • PRIOR：从游标集合中按顺序获取上一行。

• CLOSE语句：手动关闭游标

  CLOSE cursor_name;

• DEALLOCATE语句：释放游标并卸载其结果集，用于释放游标占用的资源。

  DEALLOCATE cursor_name;

示例 1：使用动态游标批量更新数据

假设我们有一张订单表，需要将其中的所有状态为“未付款”的订单状态更新为“已付款”。

CREATE TABLE order_table (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(20) NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL
);

我们可以编写一个存储过程，使用动态游标来扫描所有未付款的订单，并逐一将其状态更新为“已付款”。

DELIMITER //
CREATE PROCEDURE update_order_status()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE order_no VARCHAR(20);

  -- Declare dynamic cursor
  DECLARE cursor_orders CURSOR FOR
      SELECT order_no FROM order_table WHERE status = '未付款';

  -- Declare handlers for cursor
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- Open cursor
  OPEN cursor_orders;

  read_loop: LOOP
    FETCH cursor_orders INTO order_no;
    IF done THEN
        LEAVE read_loop;
    END IF;

    -- update order status
    UPDATE order_table SET status = '已付款' WHERE order_no = order_no;
  END LOOP;

  -- Close cursor
  CLOSE cursor_orders;

END//
DELIMITER ;

示例 2：动态游标遍历多结果集

我们可以使用动态游标从多个结果集中读取数据。例如，我们可以编写一个存储过程，使用动态游标分别从不同的表中获取数据，然后将它们联合返回。

DELIMITER //

-- Define stored procedure which takes two params and return combined result set
CREATE PROCEDURE get_combined_data(p_table1 VARCHAR(20), p_table2 VARCHAR(20))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT;
    DECLARE name VARCHAR(20);
    DECLARE age INT;

    -- Declare two separate dynamic cursors
    DECLARE cursor1 CURSOR FOR SELECT id, name FROM  p_table1; -- p_table1 -> First table name
    DECLARE cursor2 CURSOR FOR SELECT id, age FROM  p_table2;  -- p_table2 -> Second table name

    -- Declare handlers for cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open first cursor
    OPEN cursor1;
    -- fetch first cursor
    FETCH cursor1 INTO id, name;

    read_loop: LOOP
        -- if cursor 1 data is not completed yet
        IF NOT done THEN
            -- return top row of cursor 1
            SELECT id, name, NULL AS age;
            FETCH cursor1 INTO id, name; -- get next row of cursor 1
            -- if cursor 2 is completed
        ELSEIF done AND NOT is_cursor2_open THEN 
            -- break the loop
            LEAVE read_loop;
            -- if cursor 1 is completed and cursor 2 is not completed
        ELSEIF done AND is_cursor2_open THEN
            -- return top row of cursor 2
            SELECT NULL AS id, NULL AS name, age;
            FETCH cursor2 INTO id, age; -- get next row of cursor 2
        END IF;
    END LOOP;

    -- Close the first cursor
    CLOSE cursor1;
    -- check if second cursor was ever opened 
    IF is_cursor2_open THEN
        -- Close the second cursor
        CLOSE cursor2;
    END IF;

END//
DELIMITER ;

我们可以通过调用以下代码来执行上面的存储过程

CALL get_combined_data('employee', 'employee_age');

其中，employee表和employee_age表都有一个id字段，因此这些字段将用于将它们一起联接。每个表还有自己的一个附加字段。此存储过程将从两个表中读取数据，并创建一个联合结果集。如果游标1返回了行，则将返回id和名称，如果游标2返回了行，则将返回id和年龄。注意：在游标1返回所有行之后，游标2将读取并返回其结果。在该存储过程结束之后，两个游标都将被关闭。