mysql 存储过程游标使用笔记

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 ;