oracle connect by

oracle中可以用CONNECT BY子句实现递归查询，其基本语法是：

select ... from where start with connect by order siblings by ;

：过滤条件，用于对返回的所有记录进行过滤。（关联条件和过滤条件有区别，关联条件最先执行）
：根节点条件，只用于第一次过滤出根节点。 ：连接条件，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(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"表示以last_name为路径，'/'分割，记录根到节点的全路径。lpad是优化显示。

（以下来自网上）例子： 01 select a.child, 02 a.parent, 03 level "层次", 04 sys_connect_by_path(child, '->') "合并层次", 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, '是', '否') "是否子节点" 09 from test_connect_by a
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('78A') FROM dual; 08 ---------------------------------------------------------------------------------------------------------------------- 09 v_return VARCHAR2(4000); 10 BEGIN
11 SELECT SUM(DATA) INTO v_return 12 FROM (SELECT (CASE upper(substr(p_str, rownum, 1)) 13 WHEN 'A' THEN '10' 14 WHEN 'B' THEN '11' 15 WHEN 'C' THEN '12' 16 WHEN 'D' THEN '13' 17 WHEN 'E' THEN '14' 18 WHEN 'F' THEN '15' 19 ELSE substr(p_str, rownum, 1) 20 END) * power(16, length(p_str) - rownum) DATA 21 FROM dual
22 CONNECT BY rownum <= length(p_str)); 23 RETURN v_return; 24 EXCEPTION 25 WHEN OTHERS THEN 26 RETURN NULL; 27 END;