db2数据库存储过程入门6

例8：

--在存储过程中用while循环进行递归查找sid分类下的所有子分类
drop procedure test14;
create procedure test14(in sid integer)
language sql
begin

 declare num        integer;--子分类的数量
 declare global temporary table session.c_dtdoctype--子分类的临时表
 (
  id integer,
  parentid integer,
  name varchar(300)
 );
 declare global temporary table session.p_dtdoctype--父分类的临时表
 (
  id integer,
  parentid integer,
  name varchar(300)
 );
 declare global temporary table session.r_dtdoctype--结果分类的临时表
 (
  id integer,
  parentid integer,
  name varchar(300)
 )with replace;
 --查询表dtdoctype中的数据，插入到临时表session.dtdoctype中
 insert into session.p_dtdoctype select id,parentid,name from dtdoctype where parentid = sid;
 insert into session.r_dtdoctype select id,parentid,name from dtdoctype where parentid = sid;
 select count(*) into num from session.p_dtdoctype;--查询父表中是否有数据
 
 while(num>0)
 do
  delete from session.c_dtdoctype;--删除子表中的数据
  --从父表中查出子分类集合存放到子表中
  insert into session.c_dtdoctype select id,parentid,name from dtdoctype where parentid in (select id from session.p_dtdoctype);
  insert into session.r_dtdoctype select id,parentid,name from dtdoctype where parentid in (select id from session.p_dtdoctype);
  --从父表中查出子分类集合存放到子表中
  delete from session.p_dtdoctype;--删除父表中的数据
  --将子表中的数据存放在父表中
  insert into session.p_dtdoctype select * from session.c_dtdoctype;
  --查询父表中是否有数据
  select count(*) into num from session.p_dtdoctype;
  
 end while;
 
 begin
  declare Ydtdoctype cursor with return for select * from session.r_dtdoctype;
  open Ydtdoctype;--打开游标
 end;
 
 drop table session.c_dtdoctype;
 drop table session.p_dtdoctype;
 --drop table session.r_dtdoctype;
end;