继续删除的存储过程!

表结构如下:CREATE TABLE `t_plan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_name` varchar(100) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `plan_state` int(11) DEFAULT NULL,
  `plan_type` int(11) DEFAULT NULL,
  `start_time` varchar(100) NOT NULL,
  `end_time` varchar(100) NOT NULL,
  `cycle_type` varchar(20) DEFAULT '0',
  `cycle_minutes` int(11) DEFAULT '0',
  `cycle_oclock` int(11) DEFAULT '0',
  `cycle_day` int(11) DEFAULT '0',
  `cycle_date` int(11) DEFAULT '0',
  `long_time` double DEFAULT '0',
  `num` int(11) DEFAULT '0',
  `execute_type` int(11) DEFAULT '0',
  `dtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
CREATE TABLE `t_usecase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '0',
  `plan_id` int(11) NOT NULL,
  `dtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `usecase_type` int(11) DEFAULT '0',
  `ms_times` int(11) DEFAULT NULL,
  `ms_interval` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `plan_id` (`plan_id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
上面的两张表中,计划和子计划存在t_plan表中,关系用自连接实现,用例信息存在t_usecase表中,用例和计划或子计划存在外键关系.由于公司的数据库不支持innodb,所以外键基本没用,现在删除的时候,不能级连删除,需要写个存储过程实现删除,存储过程如下:CREATE DEFINER=`root`@`%` PROCEDURE `delete_plan`(
planid int
)
    COMMENT '删除计划信息'
begin
declare nLine5 int default 0;
declare done int default 0;
declare childid int;
declare planid1 int;
declare cur_5 cursor for select id from t_plan where parent_id=planid;
declare cur_6 cursor for select id from t_usecase where plan_id=planid;
declare continue handler for not found set done = 1;
delete from t_plan where id=planid;
select ROW_COUNT() into nLine5;
select nLine5;
open cur_5;
fetch cur_5 into childid;
while done=0 do
          call delete_plan(childid);
          fetch cur_5 into childid;
end while;
close cur_5;
set done=0;
open cur_6;
fetch cur_6 into planid1;
while done=0 do
          call delete_usecase(planid1);
          fetch cur_6 into planid1;
end while;
close cur_6;
end;这个存储过程实现了删除计划的时候同时删除用例,但是如果计划下面有子计划的话,就删不了了!
各位帮我看看,问题出在哪,或者有没有更好的解决方案.谢谢!

解决方案 »

  1.   

    有什么信息提示？否则别人也很难做测试啊。递归的深度max_sp_recursion_depth你设置了多少？参考一下 http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx 的实现方法。
      

  2.   


    CREATE DEFINER=`root`@`%` PROCEDURE `delete_plan`(
    planid int
    )
        COMMENT '删除计划信息'
    begin
    declare nLine5 int default 0;      #定义一个数值，用于获取删除的行数
    declare done int default 0;
    declare childid int;     #子计划编码
    declare planid1 int;    #计划编码，用于删除用例 
    declare cur_5 cursor for select id from t_plan where parent_id=planid;  #获取父编码为传入参数的子计划编码
    declare cur_6 cursor for select id from t_usecase where plan_id=planid; #获取计划编码为传入参数的用例
    #西面这4句用于删除总计划
    declare continue handler for not found set done = 1;
    delete from t_plan where id=planid;
    select ROW_COUNT() into nLine5;
    select nLine5;
    #下面使用递归删除总计划下的子计划
    open cur_5;
    fetch cur_5 into childid;
    while done=0 do
              call delete_plan(childid);#传入获得的子计划编码，调用存储过程本身
              fetch cur_5 into childid;
    end while;
    close cur_5;
    #下面用于删除计划所包含的用例
    set done=0;
    open cur_6;
    fetch cur_6 into planid1;
    while done=0 do
              call delete_usecase(planid1);#调用删除用例的存储过程
              fetch cur_6 into planid1;
    end while;
    close cur_6;
    end;
    递归的深度设了5.做的删除,没什么记录可贴啊...........
      

  3.   

    不用加注释，看得懂的。
    1。确认你的 max_sp_recursion_depth = 5;
    2。请说明过程执行时的信息
      

  4.   

    1.确认.
    2.我用MySQL-Front5.1调试的存储过程.不知道如何看存储过程执行时的信息,只能看到存储过程执行的结果!
      

  5.   

    1。 没有你的 delete_usecase 过程代码，所以精简了你的delete_plan如下，
    2。 测试结果如下
    ３。不理解为什么对t_usecase也要做递归删除？usecase之间也有父子关系？从表结构上看不象有的样子，如果没有则直接delete 即可。mysql> select id,parent_id from t_plan;
    +----+-----------+
    | id | parent_id |
    +----+-----------+
    |  1 |         0 |
    |  2 |         1 |
    |  3 |         1 |
    |  4 |         2 |
    |  5 |         3 |
    |  7 |         0 |
    +----+-----------+
    6 rows in set (0.00 sec)mysql>
    mysql> drop PROCEDURE `delete_plan`;
    Query OK, 0 rows affected (0.00 sec)mysql> delimiter //
    mysql> CREATE PROCEDURE `delete_plan`(planid int)
        -> begin
        ->  declare nLine5 int default 0;
        ->  declare done int default 0;
        ->  declare childid int;
        ->  declare planid1 int;
        ->  declare cur_5 cursor for select id from t_plan where parent_id=planid;
        ->  declare continue handler for not found set done = 1;
        ->
        ->  delete from t_plan where id=planid;
        ->  select ROW_COUNT();
        ->  open cur_5;
        ->
        ->  fetch cur_5 into childid;
        ->  while done=0 do
        ->          call delete_plan(childid);
        ->          fetch cur_5 into childid;
        ->  end while;
        ->  close cur_5;
        ->
        -> end;
        -> //
    Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
    mysql>
    mysql> set max_sp_recursion_depth=5;
    Query OK, 0 rows affected (0.00 sec)mysql> select @@max_sp_recursion_depth;
    +--------------------------+
    | @@max_sp_recursion_depth |
    +--------------------------+
    |                        5 |
    +--------------------------+
    1 row in set (0.00 sec)mysql>
    mysql> call delete_plan(1);
    +-------------+
    | ROW_COUNT() |
    +-------------+
    |           1 |
    +-------------+
    1 row in set (0.00 sec)+-------------+
    | ROW_COUNT() |
    +-------------+
    |           1 |
    +-------------+
    1 row in set (0.00 sec)+-------------+
    | ROW_COUNT() |
    +-------------+
    |           1 |
    +-------------+
    1 row in set (0.00 sec)+-------------+
    | ROW_COUNT() |
    +-------------+
    |           1 |
    +-------------+
    1 row in set (0.00 sec)+-------------+
    | ROW_COUNT() |
    +-------------+
    |           1 |
    +-------------+
    1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>
    mysql> select id,parent_id from t_plan;
    +----+-----------+
    | id | parent_id |
    +----+-----------+
    |  7 |         0 |
    +----+-----------+
    1 row in set (0.00 sec)mysql>
      

  6.   

    每次执行存储过程前都需要设置一次max_sp_recursion_depth吗?
      

  7.   


    不需要，这个个系统变量，你可以在启动参数中设置，具体请参考一下MySQL的文档。
      

  8.   

    我在数据库中测试,没什么问题,但是java程序一调用就只删一个了,我换了个思路重写了个,再帮我看看,谢谢!CREATE DEFINER=`root`@`%` PROCEDURE `delete_plan_test`(
    id int
    )
    begin
    declare num1 int default 0;
    declare num2 int default 0;
    declare num3 int default 1;
    declare usecaseid int default 0;
    declare cur_5 cursor for select pid from t_tmpc;
    drop table if exists t_tmpa;
    CREATE TEMPORARY TABLE t_tmpa(    
    pid int
    );
    drop table if exists t_tmpb;
    CREATE TEMPORARY TABLE t_tmpb(    
    pid int
    );
    drop table if exists t_tmpc;
    CREATE TEMPORARY TABLE t_tmpc(    
    caseid int
    );
    set @x = concat('insert into t_tmpa values(',id,')');
    prepare s1 from @x;
    execute s1;
    while num3>0 do
    insert into t_tmpb(pid) select t_tmpa.pid from t_tmpa,t_plan where t_tmpa.pid=t_plan.id;
    insert into t_tmpc(caseid) select usecase.id from t_tmpa,t_usecase usecase where t_tmpa.pid=usecase.plan_id;
    select count(*) into num1 from t_tmpc;
    if num1>0 then
    open cur_5;
    fetch cur_5 into usecaseid;
    while done=0 do
              call delete_usecase(usecaseid);
              fetch cur_5 into usecaseid;
    end while;
    close cur_5;
    end if;
    select count(*) into num2 from t_tmpb;
    if num2>0 then
    delete from t_plan where id in(select id from t_plan,t_tmpa where t_plan.id=t_tmpa.pid);
    insert into t_tmpa(pid) select pid from t_tmpb;
    delete from t_tmpb;
    delete from t_tmpc;
    end if;
    select count(*) into num3 from t_tmpa;
    end while;
    end;
      

  9.   

    我在数据库中测试,没什么问题,但是java程序一调用就只删一个了max_sp_recursion_depth 是SESSION级的，除非你在my.ini / my.cnf 文件中设置，否则每次新的连接都需要设置。你可以先 set global max_sp_recursion_depth = 5 ;试一下，如果可以就把这个参数设置加到 my.ini 文件中去。
      

  10.   

    可以,set global max_sp_recursion_depth = 5之后,然后在程序中删除就可以了...谢谢...
      

  11.   


    CREATE DEFINER=`root`@`%` PROCEDURE `delete_plan`(
    planid int
    )
        COMMENT '计划删除'
    begin
    declare nLine5 int default 0;     
    declare done int default 0;
    declare childid int;     
    declare planid1 int;    
    declare cur_5 cursor for select id from t_plan where parent_id=planid;  
    declare cur_6 cursor for select id from t_usecase where plan_id=planid; 
    declare continue handler for not found set done = 1;
    set max_sp_recursion_depth = 5;#可以这样设置吗?
    delete from t_plan where id=planid;
    select ROW_COUNT() into nLine5;
    select nLine5;open cur_5;
    fetch cur_5 into childid;
    while done=0 do
              call delete_plan(childid);
              fetch cur_5 into childid;
    end while;
    close cur_5;
        set done=0;
        open cur_6;
        fetch cur_6 into planid1;
        while done=0 do
                  call delete_usecase(planid1);
                  fetch cur_6 into planid1;
        end while;
        close cur_6;
    end;