作者：天飞的鹊桥会大美女 | 来源：互联网 | 2014-07-13 17:52
oracle中可以用CONNECTBY子句实现递归查询，其基本语法是：select...from<TableName>where<Conditional-1>startwith<Conditional-2>connectby<Conditional-3>ordersibl
oracle中可以用CONNECT BY子句实现递归查询，其基本语法是：
where
start with
connect by
order siblings by ;
：过滤条件，用于对返回的所有记录进行过滤。（关联条件和过滤条件有区别，关联条件最先执行） www.2cto.com
：根节点条件，只用于第一次过滤出根节点。
：连接条件，nocycle关键字，防止循环；prior操作符，用于层级条件，没有prior操作符不会发生层级关联，一个connect by语句中可以有多个prior条件，也可以有其他普通条件，但是prior不能用于sequence序列。
：同级节点排序条件。
执行顺序：
1、语句中有多表关联，先执行关联，无论是join还是where条件中的关联条件。
2、执行start with的条件，选出第一个节点。
3、执行connect by 的条件，层级关联，选出子节点。
4、执行where中的过滤条件，排除结果集中不满足条件的记录，但是不会因为排除一条记录而把它对应的子节点排除。
5、执行order siblings by的排序条件，对同级节点排序。
伪劣：
level：标记层级级数，最上层节点为1，之后为2、3……。
CONNECT_BY_ISCYCLE：标记此节点是否为某一个祖先节点的父节点，导致循环，1为是，0为否。
CONNECT_BY_ISLEAF ：标记此节点是否为叶子节点，即没有子节点，1为是，0为否。
CONNECT_BY_ROOT：标记此节点的祖先节点，后面加列名或表达式，取祖先节点的记录值。
SYS_CONNECT_BY_PATH（column,char） 函数：记录根节点到此节点的路径，column是每个节点的路径值，以char分割。column和char都必须是char,varchar2,nchar,或者nvarchar2。
例如：LPAD(&＃39; &＃39;, 2*level-1)||SYS_CONNECT_BY_PATH(last_name, &＃39;/&＃39;) "Path"表示以last_name为路径，&＃39;/&＃39;分割，记录根到节点的全路径。lpad是优化显示。
（以下来自网上）例子：
01
select a.child,
02
a.parent,
03
level "层次",
04
sys_connect_by_path(child, &＃39;->&＃39;) "合并层次",
05
prior a.child "父节点",
06
connect_by_root a.child "根节点",
07
decode(connect_by_isleaf, 1, a.child, null) "子节点",
08
decode(connect_by_isleaf, 1, &＃39;是&＃39;, &＃39;否&＃39;) "是否子节点"
09
from test_connect_by a www.2cto.com
10
start with a.parent is null --从parent为空开始扫描
11
connect by prior a.child = a.parent --以child为父列连接parent
12
order siblings by child desc --对层次排序
13
;
一个特殊的使用：
生成1到10的序列：
01
SQL> SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;
02
ROWNUM ----------
03
1
04
2
05
3
06
4
07
5
08
6
09
7
10
8
11
9
12
10
13
14
10 rows selected
借助这个功能，拆分字符串的每一个字符：
01
CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
02
----------------------------------------------------------------------------------------------------------------------
03
-- 对象名称: f_hex_to_dec
04
-- 对象描述: 十六进制转换十进制
05
-- 输入参数: p_str 十六进制字符串
06
-- 返回结果: 十进制字符串
07
-- 测试用例: SELECT f_hex_to_dec(&＃39;78A&＃39;) FROM dual;
08
----------------------------------------------------------------------------------------------------------------------
09
v_return VARCHAR2(4000);
10
BEGIN www.2cto.com
11
SELECT SUM(DATA) INTO v_return
12
FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
13
WHEN &＃39;A&＃39; THEN &＃39;10&＃39;
14
WHEN &＃39;B&＃39; THEN &＃39;11&＃39;
15
WHEN &＃39;C&＃39; THEN &＃39;12&＃39;
16
WHEN &＃39;D&＃39; THEN &＃39;13&＃39;
17
WHEN &＃39;E&＃39; THEN &＃39;14&＃39;
18
WHEN &＃39;F&＃39; THEN &＃39;15&＃39;
19
ELSE substr(p_str, rownum, 1)
20
END) * power(16, length(p_str) - rownum) DATA
21
FROM dual www.2cto.com
22
CONNECT BY rownum <= length(p_str));
23
RETURN v_return;
24
EXCEPTION
25
WHEN OTHERS THEN
26
RETURN NULL;
27
END;
作者 hulubo
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有