oracle递归数结构数据

来源：互联网发布：服务器批量安装linux 编辑：程序博客网时间：2024/06/05 21:10

事例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 offspool classify.logpromptprompt Creating table CLASSIFYprompt =======================promptcreate 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

0 0