记存储过程之游标使用

1. 大颗粒事务以及句柄跳出形式

 

事务包含了整个处理过程，属于大颗粒的事务，会阻塞其它的写操作，建议如下：

1、取消显式事务（即去掉“SET autocommit=0”和“commit”这两句），让每个update操作自动提交，但需要结合业务场景确定能否去掉显式事务，如果实在不能取消，则改到晚上等很少人用系统的时间去执行。

2、使用句柄的方式来跳出循环，会隐藏一个问题，当循环体里任何一个sql没有记录都会导致循环退出，因此不建议这样来写游标。以下游标写法供参数：

-- 创建存储过程
create procedure test11()
    begin
        declare stopflag int default 0;
        declare invNum VARCHAR(255);
        declare billCode VARCHAR(255);
        declare totalMoney VARCHAR(255);
        declare invId VARCHAR(255);
        declare empId VARCHAR(255);
        declare createTime VARCHAR(255);
        declare createUserId VARCHAR(255);
        declare createUserName VARCHAR(255);
        declare i int default 2021080300;
        -- 创建一个游标变量(invNum_cur)，declare invNum_cur cursor  -- 查询出所需要的所有数据存入临时区
        -- 游标是保存查询结果的临时区域
        -- 游标变量invNum_cur保存了查询的临时结果，实际上就是结果集
       declare invNum_cur cursor for 
             SELECT t.INVOICE_NUMBER,t1.BILL_CODE ,inv.PRICE_TAX_TOTAL,inv.ID as INVOICE_ID,empo.ID as EMPOWER_ID ,t1.CREATE_TIME,t1.CREATE_USER_ID,t1.CREATE_USER_NAME 
       FROM acc_account_file_info t left JOIN acc_account_info  t1  on t.ACCOUNT_ID = t1.ID LEFT JOIN INV_INVOICE_INFO inv on inv.INVOICE_NUM = t.INVOICE_NUMBER LEFT JOIN INV_INVOICE_EMPOWER_INFO empo ON inv.ID = empo.INVOICE_ID 
       WHERE t.DELETE_STATUS = 'NO' AND t1.BILL_CODE is not null AND t.INVOICE_NUMBER in 
             (    SELECT info.INVOICE_NUM FROM INV_INVOICE_INFO info LEFT JOIN INV_USAGE_RECORD useage ON info.ID = useage.INVOICE_ID AND useage.IS_DELETE = 'NO'
         LEFT JOIN INV_INVOICE_TYPE_INFO type ON info.BILL_TYPE = type.INVOICE_CODE LEFT JOIN sys_files file ON file.ID = info.SYS_FILED_ID LEFT JOIN INV_INVOICE_EMPOWER_INFO empo ON info.ID = empo.INVOICE_ID 
       WHERE info.IS_DELETE = 'NO' AND type.IS_DELETE = 'NO' AND info.YEAR = '2021' AND empo.IS_DELETE = 'NO' AND info.BILL_STATUS = 'NO' GROUP BY info.ID  ) order by t1.CREATE_TIME desc;

        -- 当游标变量中保存的结果都查询一遍(遍历),到达结尾，将变量stopflag设置为1，用于循环中判断是否结束
        declare continue handler for not found set stopflag=1;
        -- 打开游标
        open invNum_cur; 
                SET autocommit=0;
                 -- 开始循环
        read_loop:LOOP
       -- 提取游标中的数据
       fetch invNum_cur into invNum,billCode,totalMoney,invId,empId,createTime,createUserId,createUserName;
       -- 声明何时结束循环
       IF stopflag THEN
       LEAVE read_loop;
       END IF;
       -- 循环时的事件
        set i=i+1;
                -- 更新主表使用金额
                 update INV_INVOICE_INFO set USE_MONEY = totalMoney,BILL_STATUS = 'YES',LAST_UPDATE_TIME = '2021-08-03' where INVOICE_NUM = invNum;
        -- 更新授权表使用金额
                 update INV_INVOICE_EMPOWER_INFO set USE_MONEY = totalMoney,LAST_UPDATE_TIME = '2021-08-03' where ID = empId;
                -- 插入使用记录
                INSERT INTO INV_USAGE_RECORD (ID,IS_DELETE , INVOICE_ID , ORDER_NO ,USE_DATE_TIME,USE_USER_ID,USE_USER_NAME,EMPOWER_ID,USE_MONEY,CREATE_DATE_TIME) 
        VALUES (i,'NO', invId,billCode,createTime,createUserId,createUserName,empId,totalMoney,'2021-08-03');
        END LOOP;
        commit;
        -- 关闭游标
        close invNum_cur;
        end;

-- 创建执行存储过程
call test11();

-- 删除存储过程
DROP PROCEDURE test11;

-- 查询数据是否成功（如果有154条数据则表示修改成功）
SELECT * from INV_USAGE_RECORD where CREATE_DATE_TIME = '2021-08-03';

SELECT * from INV_INVOICE_INFO where LAST_UPDATE_TIME = '2021-08-03';

SELECT * from INV_INVOICE_EMPOWER_INFO where LAST_UPDATE_TIME = '2021-08-03';

2、不被中断执行且每次提交事务（for循环形式）

-- 创建存储过程
create procedure test11()
    begin
        declare stopflag int default 0;
        declare invNum VARCHAR(255);
        declare billCode VARCHAR(255);
        declare totalMoney VARCHAR(255);
        declare invId VARCHAR(255);
        declare empId VARCHAR(255);
        declare createTime VARCHAR(255);
        declare createUserId VARCHAR(255);
        declare createUserName VARCHAR(255);
        declare i int default 2021080300;
                declare v_i int;
                declare v_tablerCount int;
        -- 创建一个游标变量(invNum_cur)，declare invNum_cur cursor  -- 查询出所需要的所有数据存入临时区
        -- 游标是保存查询结果的临时区域
        -- 游标变量invNum_cur保存了查询的临时结果，实际上就是结果集
       declare invNum_cur cursor for 
             SELECT t.INVOICE_NUMBER,t1.BILL_CODE ,inv.PRICE_TAX_TOTAL,inv.ID as INVOICE_ID,empo.ID as EMPOWER_ID ,t1.CREATE_TIME,t1.CREATE_USER_ID,t1.CREATE_USER_NAME 
       FROM acc_account_file_info t left JOIN acc_account_info  t1  on t.ACCOUNT_ID = t1.ID LEFT JOIN INV_INVOICE_INFO inv on inv.INVOICE_NUM = t.INVOICE_NUMBER LEFT JOIN INV_INVOICE_EMPOWER_INFO empo ON inv.ID = empo.INVOICE_ID 
       WHERE t.DELETE_STATUS = 'NO' AND t1.BILL_CODE is not null AND t.INVOICE_NUMBER in 
             (    SELECT info.INVOICE_NUM FROM INV_INVOICE_INFO info LEFT JOIN INV_USAGE_RECORD useage ON info.ID = useage.INVOICE_ID AND useage.IS_DELETE = 'NO'
         LEFT JOIN INV_INVOICE_TYPE_INFO type ON info.BILL_TYPE = type.INVOICE_CODE LEFT JOIN sys_files file ON file.ID = info.SYS_FILED_ID LEFT JOIN INV_INVOICE_EMPOWER_INFO empo ON info.ID = empo.INVOICE_ID 
       WHERE info.IS_DELETE = 'NO' AND type.IS_DELETE = 'NO' AND info.YEAR = '2021' AND empo.IS_DELETE = 'NO' AND info.BILL_STATUS = 'NO' GROUP BY info.ID  ) order by t1.CREATE_TIME desc;
                
        set v_i = 1;
        -- 打开游标
        open invNum_cur; 
                -- 计算游标行数
                SELECT count(1) into v_tablerCount FROM acc_account_file_info t left JOIN acc_account_info  t1  on t.ACCOUNT_ID = t1.ID LEFT JOIN INV_INVOICE_INFO inv on inv.INVOICE_NUM = t.INVOICE_NUMBER LEFT JOIN INV_INVOICE_EMPOWER_INFO empo ON inv.ID = empo.INVOICE_ID 
        WHERE t.DELETE_STATUS = 'NO' AND t1.BILL_CODE is not null AND t.INVOICE_NUMBER in 
              (    SELECT info.INVOICE_NUM FROM INV_INVOICE_INFO info LEFT JOIN INV_USAGE_RECORD useage ON info.ID = useage.INVOICE_ID AND useage.IS_DELETE = 'NO'
          LEFT JOIN INV_INVOICE_TYPE_INFO type ON info.BILL_TYPE = type.INVOICE_CODE LEFT JOIN sys_files file ON file.ID = info.SYS_FILED_ID LEFT JOIN INV_INVOICE_EMPOWER_INFO empo ON info.ID = empo.INVOICE_ID 
        WHERE info.IS_DELETE = 'NO' AND type.IS_DELETE = 'NO' AND info.YEAR = '2021' AND empo.IS_DELETE = 'NO' AND info.BILL_STATUS = 'NO' GROUP BY info.ID  ) order by t1.CREATE_TIME desc;
                SELECT v_tablerCount;
                while (v_i <= v_tablerCount) do
                    fetch invNum_cur into invNum,billCode,totalMoney,invId,empId,createTime,createUserId,createUserName;
                    -- 循环时的事件
                    set i=i+1;
                    -- 更新主表使用金额
                    update INV_INVOICE_INFO set USE_MONEY = totalMoney,BILL_STATUS = 'YES',LAST_UPDATE_TIME = '2021-08-03' where INVOICE_NUM = invNum;
                    -- 更新授权表使用金额
                    update INV_INVOICE_EMPOWER_INFO set USE_MONEY = totalMoney,LAST_UPDATE_TIME = '2021-08-03' where ID = empId;
                    -- 插入使用记录
                    INSERT INTO INV_USAGE_RECORD (ID,IS_DELETE , INVOICE_ID , ORDER_NO ,USE_DATE_TIME,USE_USER_ID,USE_USER_NAME,EMPOWER_ID,USE_MONEY,CREATE_DATE_TIME) 
                    VALUES (i,'NO', invId,billCode,createTime,createUserId,createUserName,empId,totalMoney,'2021-08-03');
                    -- 计数器加1
                    set v_i = v_i + 1;
                end while;
        -- 关闭游标
        close invNum_cur;
        end;
delimiter ;
-- 创建执行存储过程
call test11();

-- 删除存储过程
DROP PROCEDURE test11;

-- 查询数据是否成功（如果结果1与 结果2，结果3 ，结果4 数据条数相同 则表示修改成功）
SELECT * from INV_USAGE_RECORD where CREATE_DATE_TIME = '2021-08-03';

SELECT * from INV_INVOICE_INFO where LAST_UPDATE_TIME = '2021-08-03';

SELECT * from INV_INVOICE_EMPOWER_INFO where LAST_UPDATE_TIME = '2021-08-03';

参考示例：

第一种：

CREATE PROCEDURE `add_consumption`()
    -> BEGIN
    ->   -- 定义需要接收游标数据的变量
    ->   DECLARE id int(11);
    ->   DECLARE origin int(11);
    ->   -- 定义遍历数据结束标志
    ->   DECLARE done BOOLEAN DEFAULT 0;
    ->   -- 定义游标
    ->   DECLARE cur CURSOR FOR SELECT
    ->     player.playerid as id,
    ->     player.origin as origin
    ->   FROM player;
    ->   -- 将结束标志绑定到游标
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    ->   -- 打开游标
    ->   OPEN cur;
    ->     -- 关闭事务自动提交
    ->     SET autocommit=0;
    ->     -- 开始循环
    ->     read_loop:LOOP
    ->       -- 提取游标中的数据
    ->       FETCH cur INTO id,origin;
    ->       -- 声明何时结束循环
    ->       IF done THEN
    ->         LEAVE read_loop;
    ->       END IF;
    ->       -- 循环时的事件
    ->       IF origin=0
    ->       THEN
    ->         INSERT INTO consumption VALUES (0,1525467600);
    ->       ELSE
    ->         INSERT INTO consumption_other VALUES(0,1525467600);
    ->       END IF;
    ->     END LOOP;
    ->     commit;
    ->     -- 关闭游标
    ->   CLOSE cur;
    -> END
    -> //

 

第二种：

#用来重命名的sp
drop procedure if exists sp_cursor_test;
delimiter //
create procedure sp_cursor_test()
begin
        DECLARE v_userId bigint;
        DECLARE v_nickName varchar(50);
        DECLARE v_tablerCount int;
        DECLARE v_i int;
        #定义游标
        DECLARE cursor_UserId CURSOR FOR
                SELECT UserID,NickName FROM t_p_renamenick WHERE IsExist = 1;
        set v_i = 1;
        #打开游标
        OPEN cursor_UserId;
        #计算游标行数（不要使用句柄的方式，因为在遍历游标过程中任何查询有“NOT FOUND”都会导致定义的游标退出）
        SELECT count(1) into v_tablerCount FROM t_p_renamenick WHERE IsExist = 1;
        #循环所有的行
        while (v_i <= v_tablerCount) do
                FETCH cursor_UserId INTO v_userId, v_nickName;
                select v_userId, v_nickName;
                #计数器加1
                set v_i = v_i + 1;
                #循环结束
        end while;
        #关闭游标
        CLOSE cursor_UserId;
end//
delimiter ;