oracle connect by 小记

今天有同事问我一个问题，在不查询任何表和视图的情况下，怎样把一年的12个月的月份查出来，而且还需要有一列作为编号，类似于这样：

我曾告诉过他，oracle极其强大，几乎没有做不到的事情。话说出去了，遇到问题不能轻易退缩吧？我不是专业做oracle开发的，也不是dba，仅仅是偏爱于oracle而已，技术也粗糙的很，但有个好处，就是喜欢思考，相信oracle能完成我的需求。

一番思索之后，无解，只好求助于谷老师，发现一个知识点，之前很少接触的，那就是connect by的语法，以上的需求就可以使用这个语法来完成的：

select lpad(level,2,0) , to_number(lpad(level,2,0))||'月'   from dual connect by level<13

注解：1，lpad，这个不是平板电脑，而是“左填充”，level是oracle关键字，是处理源，2表示填充后的长度，0表示填充字符，lpad(level,2,0)表示结果又两位，如果level不足两位则在level的左边用0填充，如果超过两位，则从左边开始取两位。

2，level 是oracle的关键字,表示查询的深度，结合connect by使用

好了，这个需求满足了，那么接着我们探究一下connect by的用法。

CONNECT BY 层次查询子句，一般用于树状或者层次结果集的查询，其语法结构为：

[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition

start  width 是开始遍历的位置，比如empno is null，也就是从empno 是null的这个记录开始循环。

connect width 的condition一般是关系运算，比如empno = mgr

以用户scott下的emp表作为示例：

select rpad(' ', 2 * (level - 1), '-') || ename as username,
       connect_by_root ename "ROOT",
       connect_by_isleaf "isleaf",
       level,
       sys_connect_by_path(ename, '/')
  from emp
 start with mgr is null
CONNECT BY PRIOR empno = mgr

结果如图：

说明：

1，connect_by_root  是查出根节点，语法结构为connect_by_root field_name [alias],注意这里的别名是使用双引号的，单引号不可以。

2，connect_by_isleaf 判断是否为叶子节点，值是分为0（否）和1（是），语法结构connect_by_isleaf [alias] ，同上，别名使用双引号。

3，sys_connect_by_path 是个函数，列出当前记录的路径，当然路径的内容可以自己定义，可以使用id或者name，路径中间连接符，也是自己定义。语法结构

sys_connect_by_path(field_name,'conn_char')

4,start with 表示开始的位置，本例为mgr is null ，也可以是ename = ‘JONES’

5，CONNECT  by 是控制遍历的语句，不可缺少，prior是控制遍历的方向，若以上语句最后一行变成：

CONNECT BY PRIOR mgr = empno

则结果集变为：

也就是说，如果king自以为是，从自己开始遍历，但不向下遍历，却向上，那么他就是孤家寡人。

当然了，如果是叶子节点，通过向上遍历，可以找出它的父节点，父节点的父节点……，直到找出根节点为止。

示例2：使用connect by把十六进制数转换为十进制数

先说如果使用口算或者笔算，该如何把十六进制数转成十进制数，总该有个算法吧。别告诉我要列竖式，没那闲功夫。其实有公式的，忘记的同志们不妨温习一下。

举例：89D 为十六进制数，转成十进制。

分析：位数n=3

拆分：8*16^(n-1)+9*16(n-2)+13*(n-3)=8*256+9*16+13*1

拆分之后都变成了数学运算，这个在oracle中是可以计算的。那么使用pl/sql语言的一个函数来实现，最多的需要业务逻辑的编写了。

这里盗用一个他人写好的：

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，使用rownum作为循环变量，跟其他循环里面定义的i一样，这个是从1开始步幅为1递增的，别给弄晕了。

2，CONNECT BY rownum <= length(p_str) 这一句相当于for循环里的i<length(p_str)在for循环中，当这个条件成立时，执行循环体，这儿是当条件成立时，执行select.

3，最里面的select会把每一位（十六进制）的值（十进制）都求出来作为记录打印出来，结果如图：

使用sum（data）把所有记录相加，即可获得正确的结果。

oracle的强大不仅仅如此，正所谓深着知其深，浅者知其浅。教然后知困，学然后知不足。如果有一天你觉得自己差不多了，那正是最需要反省的时候了，穷其一生学习oracle

恐怕也说不上学全了，虚心的态度，灵活的头脑，勤奋的精神，一直学习。