mysql 存储过程游标使用笔记

kevn / 2016.11.15 / 个人经验
mysql 存储过程游标使用笔记
drop procedure if exists tmp_do ;
#设置分隔符
delimiter //
#创建过程
CREATE PROCEDURE tmp_do()
  BEGIN
    DECLARE value1 varchar(255) default '';
    DECLARE value2 varchar(255) default '';
    DECLARE admin_id INT DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    #设置游标
    DECLARE curl CURSOR FOR SELECT admin_qualification.business_license,admin_qualification.agreement,branch.id FROM branch LEFT JOIN admin_qualification on branch.id = admin_qualification.admin_id left JOIN branch_check on branch.id = branch_check.branch_id;
    #设置循环完成的变量
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    #打开游标
    OPEN curl;
      REPEAT
      FETCH curl INTO value1,value2,admin_id;
      IF NOT done THEN
        IF (ISNULL(value1) || value1= '') && (ISNULL(value2) || value2= '') THEN
            UPDATE branch_check set is_check=5,ext_is_check=5 where branch_id = admin_id;
        ELSEIF (ISNULL(value1) || value1= '') THEN
            UPDATE branch_check set is_check=5 where branch_id = admin_id;
        ELSEIF (ISNULL(value2) || value2= '') THEN
            UPDATE branch_check set ext_is_check=5 where branch_id = admin_id;
        END IF;
      END IF;
    #如果 done为1 结束循环  
    UNTIL done END REPEAT;
    #关闭游标
    CLOSE curl;
  END;//
# 设置分割付  
delimiter ;

欢迎转载,转载出自:

web前端开发http://suiyidian.cn/post-162.html
作者:梦工厂

^-^有了你的支持,才能走的更远!

换个金额

微信扫一扫打赏

up