使用存储过程实现级连删除,代码如下,前4个都实现效果了,但最后2个没效果,各位帮忙看下,谢谢!#一:删除MS配置和相应流数据的sp
drop procedure delete_mscreate procedure delete_ms
(
msid int
)
begin
delete from t_ms_configure where id=msid;
delete from t_stream where ms_id=msid;
end
#二:删除PR配置信息和相应数据的sp
drop procedure delete_prcreate procedure delete_pr
(
prid int
)
begin
delete from t_pr_configure where id=prid;
delete from t_stream where pr_id=prid;
end
#三:删除MS_OBU信息的sp
drop procedure delete_ms_obucreate procedure delete_ms_obu
(
msobuid int
)
begin
declare done int default 0;
declare msid int;
declare cur_1 cursor for select id from t_ms_configure where obu_ms_id=msobuid;
declare continue handler for not found set done = 1;
open cur_1;
fetch cur_1 into msid;
while done=0 do
call delete_ms(msid);
fetch cur_1 into msid;
end while;
close cur_1;
delete from t_obu_ms where id=msobuid;
end#四:删除PR_OBU信息的sp
drop procedure delete_pr_obucreate procedure delete_pr_obu
(
probuid int
)
begin
declare done int default 0;
declare prid int;
declare cur_1 cursor for select id from t_pr_configure where obu_pr_id=probuid;
declare continue handler for not found set done = 1;
open cur_1;
fetch cur_1 into prid;
while done=0 do
call delete_pr(prid);
fetch cur_1 into prid;
end while;
close cur_1;
delete from t_obu_pr where id=probuid;
end#五:删除测试用例的信息
drop procedure delete_usecasecreate procedure delete_usecase
(
usecaseid int
)
begin
declare done1 int default 0;
declare done2 int default 0;
declare obuprid int;
declare obumsid int;
declare cur_1 cursor for select id from t_obu_ms where case_id=usecaseid;
declare cur_2 cursor for select id from t_obu_pr where case_id=usecaseid;
declare continue handler for not found set done1 = 1;
declare continue handler for not found set done2 = 1;
open cur_1;
fetch cur_1 into obumsid;
while done1=0 do
call delete_ms_obu(obumsid);
fetch cur_1 into obumsid;
end while;
close cur_1;
open cur_2;
fetch cur_2 into obuprid;
while done2=0 do
call delete_pr_obu(obuprid);
fetch cur_2 into obuprid;
end while;
close cur_2;
delete from t_usecase where id=usecaseid;
end#六:删除计划信息
drop procedure delete_plan
(
planid int
)
begin
declare done1 int default 0;
declare done2 int default 0;
declare childid int;
declare planid1 int;
declare cur_1 cursor for select id from t_plan where parent_id=planid;
declare cur_2 cursor for select id from t_usecase where plan_id=planid;
declare continue handler for not found set done1 = 1;
declare continue handler for not found set done2 = 1;
open cur_1;
fetch cur_1 into childid;
while done1=0 do
call delete_plan(childid);
fetch cur_1 into childid;
end while;
close cur_1;
open cur_2;
fetch cur_2 into planid1;
while done2=0 do
call delete_usecase(planid1);
fetch cur_2 into planid1;
end while;
close cur_2;
delete from t_plan where id=planid;
end
drop procedure delete_mscreate procedure delete_ms
(
msid int
)
begin
delete from t_ms_configure where id=msid;
delete from t_stream where ms_id=msid;
end
#二:删除PR配置信息和相应数据的sp
drop procedure delete_prcreate procedure delete_pr
(
prid int
)
begin
delete from t_pr_configure where id=prid;
delete from t_stream where pr_id=prid;
end
#三:删除MS_OBU信息的sp
drop procedure delete_ms_obucreate procedure delete_ms_obu
(
msobuid int
)
begin
declare done int default 0;
declare msid int;
declare cur_1 cursor for select id from t_ms_configure where obu_ms_id=msobuid;
declare continue handler for not found set done = 1;
open cur_1;
fetch cur_1 into msid;
while done=0 do
call delete_ms(msid);
fetch cur_1 into msid;
end while;
close cur_1;
delete from t_obu_ms where id=msobuid;
end#四:删除PR_OBU信息的sp
drop procedure delete_pr_obucreate procedure delete_pr_obu
(
probuid int
)
begin
declare done int default 0;
declare prid int;
declare cur_1 cursor for select id from t_pr_configure where obu_pr_id=probuid;
declare continue handler for not found set done = 1;
open cur_1;
fetch cur_1 into prid;
while done=0 do
call delete_pr(prid);
fetch cur_1 into prid;
end while;
close cur_1;
delete from t_obu_pr where id=probuid;
end#五:删除测试用例的信息
drop procedure delete_usecasecreate procedure delete_usecase
(
usecaseid int
)
begin
declare done1 int default 0;
declare done2 int default 0;
declare obuprid int;
declare obumsid int;
declare cur_1 cursor for select id from t_obu_ms where case_id=usecaseid;
declare cur_2 cursor for select id from t_obu_pr where case_id=usecaseid;
declare continue handler for not found set done1 = 1;
declare continue handler for not found set done2 = 1;
open cur_1;
fetch cur_1 into obumsid;
while done1=0 do
call delete_ms_obu(obumsid);
fetch cur_1 into obumsid;
end while;
close cur_1;
open cur_2;
fetch cur_2 into obuprid;
while done2=0 do
call delete_pr_obu(obuprid);
fetch cur_2 into obuprid;
end while;
close cur_2;
delete from t_usecase where id=usecaseid;
end#六:删除计划信息
drop procedure delete_plan
(
planid int
)
begin
declare done1 int default 0;
declare done2 int default 0;
declare childid int;
declare planid1 int;
declare cur_1 cursor for select id from t_plan where parent_id=planid;
declare cur_2 cursor for select id from t_usecase where plan_id=planid;
declare continue handler for not found set done1 = 1;
declare continue handler for not found set done2 = 1;
open cur_1;
fetch cur_1 into childid;
while done1=0 do
call delete_plan(childid);
fetch cur_1 into childid;
end while;
close cur_1;
open cur_2;
fetch cur_2 into planid1;
while done2=0 do
call delete_usecase(planid1);
fetch cur_2 into planid1;
end while;
close cur_2;
delete from t_plan where id=planid;
end
#第五个:
CREATE DEFINER=`root`@`%` PROCEDURE `delete_usecase`(
usecaseid int
)
COMMENT '删除测试用例'
begin
declare done int default 0;
declare obuprid int;
declare obumsid int;
declare cur_1 cursor for select id from t_obu_ms where case_id=usecaseid;
declare cur_2 cursor for select id from t_obu_pr where case_id=usecaseid;
declare continue handler for not found set done = 1;
open cur_1;
fetch cur_1 into obumsid;
while done=0 do
call delete_ms_obu(obumsid);
fetch cur_1 into obumsid;
end while;
close cur_1;
open cur_2;
fetch cur_2 into obuprid;
while done=0 do
call delete_pr_obu(obuprid);
fetch cur_2 into obuprid;
end while;
close cur_2;
delete from t_usecase where id=usecaseid;
end;
#第六个:
CREATE DEFINER=`root`@`%` PROCEDURE `delete_plan`(
planid int
)
COMMENT '删除计划'
begin
declare done int default 0;
declare childid int;
declare planid1 int;
declare cur_1 cursor for select id from t_plan where parent_id=planid;
declare cur_2 cursor for select id from t_usecase where plan_id=planid;
declare continue handler for not found set done = 1;
open cur_1;
fetch cur_1 into childid;
while done=0 do
call delete_plan(childid);
fetch cur_1 into childid;
end while;
close cur_1;
open cur_2;
fetch cur_2 into planid1;
while done=0 do
call delete_usecase(planid1);
fetch cur_2 into planid1;
end while;
close cur_2;
delete from t_plan where id=planid;
end;
usecaseid int
)
begin
declare done int default 0;
declare obuprid int;
declare obumsid int;
declare cur_3 cursor for select id from t_obu_ms where case_id=usecaseid;
declare cur_4 cursor for select id from t_obu_pr where case_id=usecaseid;
declare continue handler for not found set done = 1;
open cur_3;
fetch cur_3 into obumsid;
while done=0 do
call delete_ms_obu(obumsid);
fetch cur_3 into obumsid;
end while;
close cur_3;
open cur_4;
fetch cur_4 into obuprid;
while done=0 do
call delete_pr_obu(obuprid);
fetch cur_4 into obuprid;
end while;
close cur_4;
delete from t_usecase where id=usecaseid;
end;上面的存储过程使用了2个游标,但是我不太会用,它只执行了游标3的,4没执行.
begin
declare done int default 0;
declare obuprid int;
declare obumsid int;
declare cur_3 cursor for select id from t_obu_ms where case_id=usecaseid;
declare cur_4 cursor for select id from t_obu_pr where case_id=usecaseid;
declare continue handler for not found set done = 1;
open cur_3;
fetch cur_3 into obumsid;
while done=0 do
call delete_ms_obu(obumsid);
fetch cur_3 into obumsid;
end while;
close cur_3;
-- 此时的done = ?? 当然不等0啊 0200,所以需要重置一下, acmain
set done=0;
open cur_4;
fetch cur_4 into obuprid;
while done=0 do
call delete_pr_obu(obuprid);
fetch cur_4 into obuprid;
end while;
close cur_4;
delete from t_usecase where id=usecaseid;
end;