MySQL游标

1. 创建游标

DELIMITER //
CREATE PROCEDURE 存储过程名称([IN 参数...], [OUT 参数...], [INOUT 参数...])
BEGIN
    -- 定义游标
    DECLARE 游标名称 CURSOR
    FOR
    SELECT 行名, ... FROM 表名;
    -- 打开游标
    OPEN 游标名称;
        -- 检索每一行数据
        FETCH 游标名称 INTO 局部变量[,局部变量...];
    -- 关闭游标
    CLOSE 游标名称;
END //
DELIMITER ;

2. 使用游标数据

DELIMITER //
CREATE PROCEDURE 存储过程名称()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE result VARCHAR(1000) DEFAULT '';
    DECLARE 游标名称 CURSOR
    FOR
    SELECT 行名 FROM 表名;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    OPEN 游标名称;
    WHILE done != 1 DO
        FETCH 游标名称 INTO 局部变量[,局部变量...];
        IF done != 1 THEN
            SET result = 业务逻辑;
        END IF;
    END WHILE;
    CLOSE 游标名称;
    SELECT result;
END //
DELIMITER ;

3. 案例

3.1 结果作为字符串返回

DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
    DECLARE result VARCHAR(1000) DEFAULT '';
    DECLARE done INT DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM products;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    OPEN ordernumbers;
    WHILE done != 1 DO
        FETCH ordernumbers INTO o;
        IF done != 1 THEN
            CALL ordertotal(o,1,t);
            SET result = CONCAT_WS(',',result ,t);
        END IF;
    END WHILE;
    CLOSE ordernumbers;
    SELECT result;
END //
DELIMITER ;

CALL processorders();

3.2 结果保存到新建的表

DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
    -- 声明局部变量
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);

    -- 声明游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM products;

    -- 声明结束条件
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- 业务逻辑,创建表
    CREATE TABLE IF NOT EXISTS ordertotals(
        order_num INT, total DECIMAL(8,2)
    );

    -- 打开游标
    OPEN ordernumbers;
    
    -- 逐行读取数据
    REPEAT
        -- 获取查询后的行数据
        FETCH ordernumbers INTO o;
        -- 执行业务逻辑,调用业务存储过程
        IF done != 1 THEN
            CALL ordertotal(o,1,t);
            INSERT INTO ordertotals(order_num,total) VALUES (o,t);
        END IF;
    -- 条件满足则结束读取数据
    UNTIL done END REPEAT;

    -- 关闭游标
    CLOSE ordernumbers;
END //
DELIMITER ;

-- 调用
CALL processorders();
SELECT * FROM ordertotals;