connect by 的简单应用

一、首先从一个经典的查询序列数例子入手：

 

    select level  from dual connect by level <=10

    select level  from dual connect by 1 = 1
    上例均可查询得到1 .. N 的序列

 

    我们来分析一下其工作原理，level<=10用来控制循环的次数，即要重复多少次扫描表dual中的内容。第一次扫描得出的结果集的level都是1,第二次扫描的结果集的level都是2,依此类推。可能用文字描述的不太容易懂，下面我们通过试验来说明：

    with x as

    (select 'aa' chr from dual

    union all

    select 'bb' chr from dual)

    select level,chr,lpad(' ',(level-1)*5,'-')||chr other from x connect by level<=3;

 

    LEVEL CHR    OTHER

    ----- ------ ----------------------
    1     aa     aa
    2     aa     ---- aa
    3     aa     --------- aa
    3     bb     --------- bb
    2     bb     ---- bb
    3     aa     --------- aa
    3     bb     --------- bb
    1     bb     bb
    2     aa     ---- aa
    3     aa     --------- aa
    3     bb     --------- bb
    2     bb     ---- bb
    3     aa     --------- aa
    3     bb     --------- bb

 

    可见是全部level的树形结构，当扫描对象是dual时，即一个level只生成一条记录.

 

 

二、如何解决from dual只显示100行的问题：

    select level  from dual connect by level <=300   

    上面这条语句在各个不同的Oracle版本、不同的client下面，显示都是不同的。例如对于9i，在PLSQL DEV下只显示100行，100行以上的不显示。在SQLPlus下只显示1行，其余不显示。对于10g则无论在哪都完全显示。

    对于这个问题的解决，使用以下方法即可：

    select * from (level  from dual connect by level <=300);

 

    应用举例：
    select to_date(2008||'0101','yyyymmdd') + rownum - 1 rq,

    to_char(to_date(2008||'0101','yyyymmdd') + rownum - 1,'day') day

    from(select rownum from dual

    connect by rownum <= to_date(2008||'1231','yyyymmdd') - to_date(2008||'0101','yyyymmdd')+1);
    作用：列出所有日期及星期几，可用于查询工作日

 

 

三、start with ... connect by 用法讲解：

    构建如下table：

    ID   NAME  PID

    ---- ----- ----
    1    10    0
    2    11    1
    3    20    0
    4    12    1
    5    121   2

    code example1：

    select TBL_TEST.*,level from TBL_TEST

    start with pid=1 --可写到connect by后面

    connect by prior pid = id

 

    ID   NAME  PID  LEVEL

    ---- ----- ---- ------
    2    11    1    1
    1    10    0    2
    4    12    1    1
    1    10    0    2

 

    code example2：

 

    select TBL_TEST.*,level from TBL_TEST

    start with id=5 --可写到connect by后面

    connect by prior pid = id

 

    ID   NAME  PID  LEVEL

    ---- ----- ---- ------
    5     121  2    1
    2     11   1    2
    1     10   0    3

     说明：

    1、先从start with pid=1 句开始查询 得到 2 11 1 1 =====> level置1；
    2、根据pid = id，查询 id=1 句，得到 1 10 0 2 =====> level置2；
    3、根据pid = id，查询 id=0 句，未查询到后结束该树枝；

     注：prior pid = id 句说明 pid是id的父节点，通过pid查询id

 

 

四、sys_connect_by_path函数讲解：

 

    sys_connect_by_path函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，在一个格中显示。

    注意：一定是显示子节点的，所以在connect by prior子句中注意区分好。

 

    select level,sys_connect_by_path(id,'/') from TBL_TEST

    start with pid=1

    connect by prior pid = id;

 

    select level,sys_connect_by_path(pid,'/') from TBL_TEST

    start with id= 5

    connect by prior pid = id;

 

    可以比较这两段代码的运行结果与code example1的结果之间的差异，即可理解此函数用法

 

  在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。