oracle递归数结构数据

事例SQL语句

select level,sys_connect_by_path(code,'/') path,t.*  from classify t WHERE 1=1 start with id=''  connect by PID= prior ID 

上述语句中，level表示当前数据所在的层级，sys_connect_by_path函数展示出当前数据的所在路径，start with是树节点开始的地方，可以是根节点，也可以是叶子节点，connect by 来递归出所有节点，prior表示前一条记录，where是将对结果集进行筛选。

建表SQL语句

set define off
spool classify.log

prompt

prompt Creating table CLASSIFY
prompt =======================
prompt

create table SXGISMIS.CLASSIFY
(
  code             NVARCHAR2(30),
  type             NVARCHAR2(30),
  id               NVARCHAR2(50) not null,
  description      NVARCHAR2(50),
  class            NVARCHAR2(30),
  pid              NVARCHAR2(50),
  remark           NVARCHAR2(500),
  associated_value NVARCHAR2(500)
)
tablespace SXGISMIS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 128K
    next 1M
    minextents 1
    maxextents unlimited
  );
comment on column SXGISMIS.CLASSIFY.code
  is '分级编码';
comment on column SXGISMIS.CLASSIFY.type
  is '专题';
comment on column SXGISMIS.CLASSIFY.id
  is '分级主键';
comment on column SXGISMIS.CLASSIFY.description
  is '分级描述';
comment on column SXGISMIS.CLASSIFY.class
  is '类别';
comment on column SXGISMIS.CLASSIFY.pid
  is '父级主键';
comment on column SXGISMIS.CLASSIFY.remark
  is '备注';
comment on column SXGISMIS.CLASSIFY.associated_value
  is '关联值';
alter table SXGISMIS.CLASSIFY
  add constraint PK_CLASSIFY primary key (ID)
  using index 
  tablespace SXGISMIS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


spool off