orcle复制树状图存储过程

树状图复制功能 ：写了一个存储过程
create or replace procedure copy_TemplateDetail(
sourceId number, --源id
u_id number,   --更新的id
u_createUser varchar2) is 
   new_one_id number;
   new_two_id number;
   one_level number;
   sourceDetailId number;
   sourceTwoDetailId number;
   two_level number;
 begin
     select count(1) into one_level  from 表A where templateId = sourceId and parentId is null;
     DBMS_OUTPUT.put_line(one_level);
   while one_level>0 loop
     SELECT S_ASSESSMENTTEMPLATEDETAILS.Nextval into new_one_id from dual;
        insert into 表A (ID,TEMPLATEID,DETAILLEVEL,PARENTID) select new_one_id,u_id,m.DETAILLEVEL,  m.PARENTID from (select a.*,rownum rowss from 表A a where a.templateId=sourceId and a.parentId is null  ) m where m.rowss=one_level;
         select m.id into sourceDetailId  from (select a.id ,rownum as rowss from 表A a where a.templateId=sourceId and a.parentId is null order by a.displayseq) m where m.rowss= one_level;
     select count(1) into two_level from 表A  where parentId = sourceDetailId;
      one_level:= one_level - 1;
      DBMS_OUTPUT.put_line(sourceDetailId);
      while two_level>0 loop
        SELECT S_ASSESSMENTTEMPLATEDETAILS.Nextval into new_two_id from dual;
        insert into 表A (ID,TEMPLATEID,DETAILLEVEL,PARENTID) select new_two_id,u_id,m.DETAILLEVEL, m.MINSCORE,new_one_id  from (select a.*,rownum rowss from 表A a where a.templateId=sourceId and a.parentId =sourceDetailId order by a.displayseq ) m where m.rowss=two_level;
        select m.id into sourceTwoDetailId  from (select a.id ,rownum as rowss from 表A a where a.templateId=sourceId and a.parentId=sourceDetailId order by a.displayseq) m where m.rowss= two_level;
         insert into 表A (ID,TEMPLATEID,DETAILLEVEL,PARENTID) select S_ASSESSMENTTEMPLATEDETAILS.Nextval,u_id,m.DETAILLEVEL,new_two_id  from (select a.*,rownum rowss from ASSESSMENTTEMPLATEDETAILS a where a.templateId=sourceId and a.parentId =sourceTwoDetailId order by a.displayseq ) m ;
         two_level:=two_level-1;
      end LOOP;
     end LOOP;
end copy_TemplateDetail;

 

java复制树状图 

1.获取一定量的序列值

2.根据级别，id，parentid 获取一个有序的list数组

start with detaillevel = #detaillevel# and templateId=#templateId#
connect by prior id = parentId
order siblings by displaySeq

3.for循环往里面塞id和parentId了

 

 

表达的永远不是他所说的内容，而是渴望被理解的心情。

                                                                                                ——苏更生