oracle 一条龙学习

drop sequence sq_z_tree_node;

create sequence sq_z_tree_node increment by 1 start with 1;

create or replace trigger z_tree_node_pk
before insert
on z_tree_node
   for each row
   when(new.id is null)
  begin
  select  sq_z_tree_node.nextval into :new.id from dual;
end;

/
create or replace  type tree_node as table of number;
 
create or replace procedure tree_node_data(tn tree_node)
as
 level number(8);
 name  varchar2(100);
 pid number(6);
 remark varchar2(100);
 temp varchar2(100);
begin
  delete from z_tree_node;
  commit;
  level:=0;
  for i in 1..tn.count loop
 
      level:=level+1;
      remark := '这是' || level || '级部门';
      for j in 1..tn(i) loop
          if level=1 then -- 一级部门
             name:= level || '级部门'|| j 
             insert into z_tree_node(name,pId,remark) values(name,null,remark);
             commit;
          else   --非一级部门
              for k in 1..tn(i-1) loop 
                  temp:= level-1 || '级部门'|| k;
                  dbms_output.put_line(temp);
                 select id into pid from z_tree_node where rownum = k ;
                 insert into z_tree_node(name,pid,remark) values(name,pid,remark);
                 commit;
              end loop; 
          end if;
        
      end loop;

  end loop;
end;

declare
  -- 很可惜只能生成两级部门的树数据
  tn tree_node;
begin
   tn:= tree_node(2,22);
  -- Call the procedure
  tree_node_data(tn => tn);
end;

--drop procedure tree_node_data;
--drop type tree_node;