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 ;
本文由 kevin 创作,采用 知识共享署名4.0 国际许可协议进行许可。
本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名。