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

最新推荐文章于 2023-11-01 13:15:10 发布

原创最新推荐文章于 2023-11-01 13:15:10 发布 · 390 阅读

1 ·

CC 4.0 BY-SA版权

Data-mysql+oracle+redis+mongo 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了使用 Oracle SQL 实现自上而下及自下而上的数据遍历方法，并提供了具体的 SQL 语句示例。通过 START WITH 和 CONNECT BY PRIOR 子句实现层次遍历查询，适用于具有层次结构的数据表。

自上而下遍历

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
);