第一步：set max_sp_recursion_depth=12; 第二步：创建createchilddept过程 delimiter $$ use `zhiku`$$ drop procedure if exis
第一步：set max_sp_recursion_depth=12;
第二步：创建createchilddept过程
delimiter $$
use `zhiku`$$
drop procedure if exists `createchilddept`$$
create definer=`root`@`%` procedure `createchilddept`(in rootid int,in ndepth int)
begin
 declare done int default 0;
 declare b int;
 declare cur1 cursor for select id from zk_departments where parent_id=rootid;
 declare continue handler for not found set done = 1;
 insert into tmplst values (null,rootid,ndepth);
 open cur1;
 fetch cur1 into b;
 while done=0 do
  call createchilddept(b,ndepth+1);
  fetch cur1 into b;
 end while;
 close cur1;
    end$$
delimiter ;
第三步：创建showchilddept
delimiter $$
use `zhiku`$$
drop procedure if exists `showchilddept`$$
create definer=`root`@`%` procedure `showchilddept`(in rootid int)
begin
 create temporary table if not exists tmplst
 (sno int primary key auto_increment,id int,depth int);
 delete from tmplst;
 call createchilddept(rootid,0);
 select zk_departments.id,zk_departments.name from tmplst,zk_departments where tmplst.id=zk_departments.id and zk_departments.id>rootid order by tmplst.sno;
 #select zk_user_departments.dept_id,zk_user_departments.user_id from zk_user_departments left join tmplst on tmplst.id=zk_user_departments.dept_id and zk_user_departments.dept_id>rootid order by tmplst.sno;
    end$$
delimiter ;
第四步：call showchilddept(128)
，