Oracle start with connect by prior 递归查询

准备数据:组织机构表

create table TABLE_ORG
(
  id     NUMBER(10) not null,
  name   VARCHAR2(64),
  code   VARCHAR2(32),
  parent VARCHAR2(128),
  status CHAR(1)
);
-- Add comments to the columns 
comment on column TABLE_ORG.id
  is '主键';
comment on column TABLE_ORG.name
  is '组织名称';
comment on column TABLE_ORG.code
  is '本组织编码';
comment on column TABLE_ORG.parent
  is '父组织编码';

insert into table_ORG (ID, NAME, CODE, PARENT, STATUS)
values (152, '南京大学', '0', null, null);

insert into table_ORG (ID, NAME, CODE, PARENT, STATUS)
values (144, '经济管理系', '1', '0', '1');

insert into table_ORG (ID, NAME, CODE, PARENT, STATUS)
values (146, '现代经济学', '3', '1', '1');

insert into table_ORG (ID, NAME, CODE, PARENT, STATUS)
values (145, '计算机系', '2', '0', '1');

insert into table_ORG (ID, NAME, CODE, PARENT, STATUS)
values (147, '网络工程', '4', '2', '1');

start with与connect by prior语句完成递归记录，形成一棵树形结构，通常可以在具有层次结构的表中使用。
start with：表示开始的记录
connect by prior：指定与当前记录关联时的字段关系

select t.*   from table_ORG  t ;

 SELECT * from table_ORG  start with
       CODE = '0' CONNECT by  PARENT = PRIOR code    

 SELECT * from table_ORG  start with
       CODE = '0' CONNECT by  PRIOR code = PARENT  

以上两条sql意思一样,查询从code=‘0’开始，取出查出数据的code，作为parent条件查询
步骤1.select *  from   table_ORG   where  CODE = '0' 
步骤2.select *  from   table_ORG   where  PARENT  = (select code from   table_ORG   where  CODE = '0' )
步骤3.select *  from   table_ORG   where  PARENT  = (
select code   from   table_ORG   where  PARENT  = (select code from   table_ORG   where  CODE = '0' )
)
递归查询，直到查到的数据量行数为0停止递归。

常用实例1：处理资源菜单层级

----资源菜单表没有层级说明,通过oracle函数查询生成
select LEVEL,t.menuid,parentid,title  from sys_resources t 
start with  parentid is null  connect by  parentid=prior menuid;