#如果存在此存储过程,则删除
drop PROCEDURE if EXISTS xzjs;
#创建存储过程
create PROCEDURE xzjs(in c_id int,in c_xz int,in c_cq int)
begin
#声明变量
DECLARE finish int DEFAULT 0; #数据遍历结束标志
DECLARE var_id int;
DECLARE var_date varchar(15);
DECLARE var_to_work_time varchar(10);
DECLARE var_off_work_time varchar(10);
DECLARE var_to_work_status varchar(10);
DECLARE var_off_work_status varchar(10);
DECLARE var_jb_status varchar(10);
#定义游标
DECLARE cur CURSOR FOR select id,date,to_work_time,off_work_time,to_work_status,off_work_status,jb_status from new_k_q where id = c_id;
#将结束标志绑定至游标(没有数据返回,将变量finish的值设为1)
#DECLARE CONTINUE HANDLER FOR NOT FOUNDSET SET finish = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET finish = 1;
#打开游标
OPEN cur;
#遍历数据
bl:LOOP
fetch cur into var_id,var_date,var_to_work_time,var_off_work_time,var_to_work_status,var_off_work_status,var_jb_status;
#当finish=1结束遍历
IF finish = 1 THEN LEAVE bl;END IF;
#当finish=0执行语句
if finish = 0 then insert into test VALUES(var_date);end if;
END LOOP bl;
CLOSE cur;
end;
#存储过程的调用
call xzjs(2,5000,21);
mysql 存储过程--游标(取每条记录的多个字段)
最新推荐文章于 2022-10-30 19:26:09 发布