Oracle CONNECT BY的用法
Oracle CONNECT BY的用法
Oracle中可以通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询.
自从Oracle 9i开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。
自从Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”,
如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。
在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点),
Oracle 就会报出一个错误提示:“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。
而在 Oracle 10g 中,只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE,
如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。
The start with .. connect by clause can be used to select data that has a hierarchical relationship
(usually some sort of parent->child, boss->employee or thing->parts).
It is also being used when an sql execution plan is explained.
syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-condition
level
With level it is possible to show the level in the hierarchical relation of all the data.
--oracle 9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.
--oracle 10g
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
下面举例说明:
[例1]
创建一个部门表,这个表有4个字段,分别对应部门ID,部门名称,上级部门ID ,本部门直属员工人数。
create table DEP
(
DEPID number(10) not null,
DEPNAME varchar2(32),
UPPERDEPID number(10),
NUMOFEMP number(10)
) ;
下面插入一些数据,结果如下:
- hr@MYTEST2> select * from dep;
- DEPID DEPNAME UPPERDEPID NUMOFEMP
- ---------- --------------- ---------- ----------
- 0 Dev Center 2
- 1 DevA 0 3
- 2 DevA Team1 1 10
- 3 DevA Team2 1 8
- 4 DevB 0 2
- 5 DevB Team1 4 12
- 6 Test Center 1
- 7 Test Team1 6 5
- 8 Test Team2 6 5
- 9 rows selected.
现在根据“CONNECT BY”来实现树状查询:
- column depname format a15;
- column rootdep format a15;
- column path format a30;
- select rpad(' ', 2*(level-1), '-') || depname "DEPNAME",
- connect_by_root depname "ROOTDEP",
- connect_by_isleaf "ISLEAF",
- level,
- sys_connect_by_path(depname, '/') "PATH"
- from dep
- start with upperdepid is null
- connect by prior depid = upperdepid
- /
结果如下:
- DEPNAME ROOTDEP ISLEAF LEVEL PATH
- --------------- --------------- ---------- ---------- ---------------------------
- Dev Center Dev Center 0 1 /Dev Center
- -DevA Dev Center 0 2 /Dev Center/DevA
- ---DevA Team1 Dev Center 1 3 /Dev Center/DevA/DevA Team1
- ---DevA Team2 Dev Center 1 3 /Dev Center/DevA/DevA Team2
- -DevB Dev Center 0 2 /Dev Center/DevB
- ---DevB Team1 Dev Center 1 3 /Dev Center/DevB/DevB Team1
- Test Center Test Center 0 1 /Test Center
- -Test Team1 Test Center 1 2 /Test Center/Test Team1
- -Test Team2 Test Center 1 2 /Test Center/Test Team2
下面计算Dev Center和Test Center部门的总人数:
- select ROOTDEPID, sum(numofemp) "TOTALEMP"
- from (select connect_by_root depid "ROOTDEPID", numofemp from dep
- start with upperdepid is null
- connect by prior depid = upperdepid)
- group by ROOTDEPID
- /
结果如下:
- ROOTDEPID TOTALEMP
- --------- ----------
- 6 11
- 0 37
|
评论暂时关闭