Oracle Contact By的使用

浏览数：24 / 时间：2015年06月12日

1.概述

Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询

2.使用方式

2.1.通过Connect by 生成序列

Oracle 构造一个月份的天数

select to_date(‘200809‘,‘yyyymm‘)+(rownum-1) s_date from dual  
connect by 
rownum<=last_day(to_date(‘200809‘,‘yyyymm‘)) - to_date(‘200809‘,‘yyyymm‘) + 1

技术分享

生成1-10的一个序列

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10

技术分享

2.2实现树状查询结果

create table DEP
(
  DEPID      number(10) not null,
  DEPNAME    varchar2(256),
  UPPERDEPID number(10)
)
---------------------------------------------------------------------
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, ‘总经办‘, null);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, ‘开发部‘, 0);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, ‘测试部‘, 0);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, ‘Sever开发部‘, 1);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, ‘Client开发部‘, 1);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, ‘TA测试部‘, 2);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, ‘项目测试部‘, 2);
---------------------------------------------------------------------

SELECT RPAD( ‘ ‘, 2*(LEVEL-1), ‘-‘ ) || DEPNAME "DEPNAME",
CONNECT_BY_ROOT DEPNAME "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL ,
SYS_CONNECT_BY_PATH(DEPNAME, ‘/‘) "PATH"
FROM DEP
START WITH UPPERDEPID IS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;