oracle10g 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

[例2]通过CONNECT BY用于十六进度转换为十进制

CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS

----------------------------------------------------------------------------------------------------------------------

-- 对象名称: f_hex_to_dec

-- 对象描述: 十六进制转换十进制

-- 输入参数: p_str 十六进制字符串

-- 返回结果: 十进制字符串

-- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;

----------------------------------------------------------------------------------------------------------------------

v_return VARCHAR2(4000);

BEGIN

SELECT SUM(DATA) INTO v_return

FROM (SELECT (CASE upper(substr(p_str, rownum, 1))

WHEN 'A' THEN '10'

WHEN 'B' THEN '11'

WHEN 'C' THEN '12'

WHEN 'D' THEN '13'

WHEN 'E' THEN '14'

WHEN 'F' THEN '15'

ELSE substr(p_str, rownum, 1)

END) * power(16, length(p_str) - rownum) DATA

FROM dual

CONNECT BY rownum <= length(p_str));

RETURN v_return;

EXCEPTION

WHEN OTHERS THEN

RETURN NULL;

END;

说明：

1. CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历

2. 通过CASE语句，来解析十六进制中的A-F对应的10进制值

测试结果如下：

hr@MYTEST2> variable dec varchar2(32);

hr@MYTEST2> exec :dec := f_hex_to_dec('1FF');

PL/SQL procedure successfully completed.

hr@MYTEST2> print dec

DEC

----------

511

[例3]通过CONNECT BY生成序列

对于connect by，现在大多数人已经很熟悉了，connect by中的条件就表示了父子之间的连接关系，比如 connect by id=prior pid。

但如果connect by中的条件没有表示记录之间的父子关系

那会出现什么情况？

常见的，connect by会在构造序列的时候使用

用select rownum from dual connect by rownum

我们注意到，dual是一个只有一条记录的表，如果表有多条记录，将会怎样？

下面开始实验

CREATE TABLE T(ID VARCHAR2(1 BYTE));

INSERT INTO T ( ID ) VALUES ( 'A');

INSERT INTO T ( ID ) VALUES ( 'B');

INSERT INTO T ( ID ) VALUES ( 'C');

COMMIT;

然后执行以下查询：

hr@MYTEST2> column id format a2;

hr@MYTEST2> select id,level from t connect by level<2;

ID LEVEL

-- ----------

A 1

B 1

C 1

hr@MYTEST2> select id,level from t connect by level<3;

ID LEVEL

-- ----------

A 1

A 2

B 2

C 2

B 1

A 2

B 2

C 2

C 1

A 2

B 2

C 2

12 rows selected.

无需多说，我们很快可以找到其中的规律，假设表中有N条记录

则记F(N,l)为 select id,level from t connect by level

那么，

F(N,1)=N

F(N,l) = F(N,l-1)*N+N

于是可以总结出

F(N,l)=∑power(N,p), p取值为[1,l)

要解释，也很容易。

当连接条件不能限制记录之间的关系时

每一条记录都可以作为自己或者其他记录的叶子

如下所示：

A          1A          2A          3

B          3

C          3

B          2A          3

B          3

C          3

C          2

A          3

B          3

C          3

在这里，我们看到的是

Oracle采用了深度优先的