Oracle connect by prior 处理树状表-526互联

--基础篇
--1.建表
create table department(departmentid integer,departmentname varchar2(30),upperdepartmentid integer,manager varchar2(30));
--2.建序列
create sequence department_seq  start with 1 nocycle;
--3.建触发器
create trigger tr_department_id before insert on department
for each row 
begin
  select department_seq.nextval into :new.departmentid from dual;
end;
--4.建存储过程
create or replace procedure pr_add_department(deptName in varchar2,upperDeptName in varchar2) as
  pid integer;
begin
  if nvl(upperDeptName,'0')='0' then
     pid:=0;
  else
    select departmentid into pid from department where departmentname=upperDeptName;
  end if;
  insert into department(departmentname,upperdepartmentid) values(deptName,pid);
  commit;
end;

--5.查询
select * from department;
--6.更新
update department set departmentname='会计电算化',upperdepartmentid=9 where departmentid=10;

--7.添加树状表树根
begin
  pr_add_department('江西财经学院','');
end;
--8.添加分支
begin
  --pr_add_department('信息系','江西财经学院');
  pr_add_department('会计专业','江西财经学院');
end;
--9.查询树状表  level 分层级
select departmentid,departmentname,upperdepartmentid,level
 from department
connect by prior departmentid=upperdepartmentid
start with upperdepartmentid=0