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;
本文相关DEMO下载
------------------------------------------分割线------------------------------------------
免费下载地址在 http://linux.linuxidc.com/
用户名与密码都是www.linuxidc.com
具体下载目录在 /2015年资料/1月/22日/Oracle Contact By的使用
下载方法见 http://www.linuxidc.com/Linux/2013-07/87684.htm
------------------------------------------分割线------------------------------------------
更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址:http://www.linuxidc.com/Linux/2015-01/112242.htm