Oracle 遍历树 start with （） connect by prior （）

来源：互联网发布：mysql常用命令总结编辑：程序博客网时间：2024/05/02 19:16

自上而下遍历

select o.* from t_cc_object o start with o.objectid = 1000000000000001 connect by prior o.objectid = o.parentid

由下面的数据可知：遍历是从一个树枝遍历到底，再遍历第二个。

自下而上遍历

select o.* from t_cc_object o start with o.objectid = 1000000000005324 connect by prior o.parentid = o.objectid

数据结构：

表结构：

-- Create table
create table T_CC_OBJECT
(
OBJECTID       NUMBER(16) not null,
OBJECTTYPEID   NUMBER(16) not null,
TOTALCODE      NVARCHAR2(64),
ENABLED        NCHAR(1) default '1',
OBJECTCODE     NVARCHAR2(64),
OBJECTNAME     NVARCHAR2(128) not null,
AUTOINCREASE   NUMBER,
DESCRIPTION    NVARCHAR2(256),
PARENTID       NUMBER(16),
GENERATION     NUMBER,
OBJECTPOSITION NUMBER not null,
HASCHILDREN    NCHAR(1) default '0',
CREATETIME     DATE
)
tablespace CC_ECS
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64
    next 1
    minextents 1
    maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_CC_OBJECT
add constraint PK_T_CC_OBJECT primary key (OBJECTID)
using index
tablespace CC_ECS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-- Create/Recreate check constraints
alter table T_CC_OBJECT
add constraint CKC_ENABLED_T_CC_OBJ
check (Enabled is null or (Enabled in ('1','0')));
alter table T_CC_OBJECT
add constraint CKC_HASCHILDREN_T_CC_OBJ
check (hasChildren is null or (hasChildren in ('0','1')));
-- Create/Recreate indexes
create index IDX_OBJECT_OBJECTNAME on T_CC_OBJECT (OBJECTNAME)
tablespace CC_ECS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
create index IDX_OBJECT_PARENTID on T_CC_OBJECT (PARENTID)
tablespace CC_ECS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
create index I_CC_OBJECT_TOTALCODE on T_CC_OBJECT (TOTALCODE)
tablespace CC_ECS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);

0 0