Mysql存储过程游标循环操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
LABEL_PROC:
BEGIN
-- 任务表关联时使用的key值
DECLARE v_faxkey VARCHAR(50) default '';
-- 任务表中的传真状态
DECLARE v_taskstate int default 0;
DECLARE v_ID int default 0;
DECLARE v_userName VARCHAR(50) default '';
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ID FROM childfax where ParentId=p_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- 如果id不在合法范围,直接返回-1
if( p_id<=0 ) then
select -1 into result;
leave LABEL_PROC;
end if;
-- id 指定记录不存在,直接返回 -2
if(not exists(select * from parentfax where id=p_id) ) then
select -2 into result;
leave LABEL_PROC;
end if;
if(select count(*) from faxtask where ParentId = p_id and status=1 > 0) then
select -3 into result;
select '正在发送中,不能删除';
leave LABEL_PROC;
end if;
-- 设置子传真会删除状态
UPDATE childfax set faxstate=-1 where ParentId=p_id;
-- 设置份传真为删除状态
UPDATE parentfax set FaxState=-1 where id=p_id;
-- 删除任务表中数据
delete from faxtask where parentid = p_id;
OPEN cur1;
REPEAT
FETCH cur1 INTO v_ID;
IF NOT done THEN
select username into v_userName from user_childfax_view where id=v_ID;
call pro_add_log(v_ID,2,concat(v_userName,' 删除') );
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
-- 返回执行成功结果
select 1 into result;
END LABEL_PROC
BEGIN
-- 任务表关联时使用的key值
DECLARE v_faxkey VARCHAR(50) default '';
-- 任务表中的传真状态
DECLARE v_taskstate int default 0;
DECLARE v_ID int default 0;
DECLARE v_userName VARCHAR(50) default '';
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ID FROM childfax where ParentId=p_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- 如果id不在合法范围,直接返回-1
if( p_id<=0 ) then
select -1 into result;
leave LABEL_PROC;
end if;
-- id 指定记录不存在,直接返回 -2
if(not exists(select * from parentfax where id=p_id) ) then
select -2 into result;
leave LABEL_PROC;
end if;
if(select count(*) from faxtask where ParentId = p_id and status=1 > 0) then
select -3 into result;
select '正在发送中,不能删除';
leave LABEL_PROC;
end if;
-- 设置子传真会删除状态
UPDATE childfax set faxstate=-1 where ParentId=p_id;
-- 设置份传真为删除状态
UPDATE parentfax set FaxState=-1 where id=p_id;
-- 删除任务表中数据
delete from faxtask where parentid = p_id;
OPEN cur1;
REPEAT
FETCH cur1 INTO v_ID;
IF NOT done THEN
select username into v_userName from user_childfax_view where id=v_ID;
call pro_add_log(v_ID,2,concat(v_userName,' 删除') );
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
-- 返回执行成功结果
select 1 into result;
END LABEL_PROC