#MySQL 存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS `perfgfd` //
CREATE PROCEDURE `perfgfd`()
BEGIN
– 定义变量
DECLARE policy VARCHAR(2000) DEFAULT '';
DECLARE businid VARCHAR(2000) DEFAULT '';
DECLARE s INTEGER DEFAULT 0;
– 定义游标,并将sql结果集赋值到游标中
DECLARE report CURSOR FOR select policy,businid from sertttt
– 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
– 打开游标
open report;
– 将游标中的值赋值给变量,
– 注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
fetch report into policy,businid;
-- 当s不等于1,也就是未遍历完时,会一直循环
while s<>1 do
select * from sertttt where policyNo=policy and businessId=businid ;
– 将游标中的值再赋值给变量,供下次循环使用
fetch report into policy,businid;
– 当s等于1时表明遍历以完成,退出循环
end while;
– 关闭游标
close report;
END //
DELIMITER ;