oracle中进行简单树查询(递归查询) ，PRIOR、CONNECT_BY_ROOT的使用 .

select AD_DEPARTMENT_CODE, AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_NAME

  from (select AD_DEPARTMENT_CODE,

               AD_DEPARTMENT_UP_LEVEL,

               AD_DEPARTMENT_NAME

          from AD_DEPT_ARCHITECTURE

         orderby AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_CODE)

 start withAD_DEPARTMENT_CODE = '0'

connect by priorAD_DEPARTMENT_CODE = AD_DEPARTMENT_UP_LEVEL;

• 生成媒体频道栏目父子关系树状关系

SELECT APR_CODE, APR_CODE_UP, NAMES

  FROM (SELECT A.MEDIA_CODE || '|' || A.CHANNEL_CODE || '|' || A.COLUMN_CODE AS APR_CODE,

               A.MEDIA_CODE_UP || '|' || A.CHANNEL_CODE_UP || '|' ||

               A.COLUMN_CODE_UP AS APR_CODE_UP,

               DECODE((B.MEDIA_NAME || '-' || C.CHANNEL_CODE || '-' ||

                      D.COLUMN_NAME),

                      '--',

                      '产品关系',

                      (B.MEDIA_NAME || '|' || C.CHANNEL_CODE || '|' ||

                      D.COLUMN_NAME)) NAMES

          FROMCOLUMNS D, CHANNEL C, MEDIA B, AD_PRODUCT_RELATION A

         WHERE A.MEDIA_CODE = B.MEDIA_CODE(+)

           AND A.MEDIA_CODE = C.MEDIA_CODE(+)

           AND A.CHANNEL_CODE = C.CHANNEL_CODE(+)

           AND A.MEDIA_CODE = D.MEDIA_CODE(+)

           AND A.CHANNEL_CODE = D.CHANNEL_CODE(+)

           AND A.COLUMN_CODE = D.COLUMN_CODE(+))

 STARTWITH APR_CODE = '0|0|0'

CONNECTBYPRIOR APR_CODE = APR_CODE_UP

这里利用'-'和'|'来进行区分，找出了树状结构的根

为了构建树，

（1）先建立了树结构的根，字段这里都设为“0”或者“00”

（2）然后再根据需要填充数据

（3）利用上边的oracle的PRIOR，CONNECT_BY_ROOT 等进行层次查询

• 递归查询时：由于数据类型不正确而导致“ORA-01436: CONNECT BY loop in user data”

错误原因：在数据库表TRASFER中，TRASFER字段为number类型，而TAPE_NO字段为Varchar2类型。由于在进行父子关系判定中“TRANSFER = TAPE_NO”两者的字段类型不同而导致：ORA-01436

正确语句：

SELECT TRANSFER, TAPE_NO, TRANSFER_NAME

  FROM (SELECT TRANSFER, TAPE_NO, TRANSFER_NAME

          FROM TRANSFER

         ORDERBY TAPE_NO, TRANSFER)

 STARTWITH TRANSFER = 0

CONNECTBYPRIORTO_CHAR(TRANSFER) = TAPE_NO

PL/SQL基础：阶层查询

作者：itpub cxck  2007-05-08

内容导航：

 ORACLE 10g新增了阶层查询操作符PRIOR，CONNECT_BY_ROOT

■PRIOR
阶层查询的CONNECY BY condition的条件式需要用到PRIOR来指定父节点，
作为运算符，PRIOR和加(+)减(-)运算的优先级相同。

■阶层查询
语法：START WITH condition CONNECT BY NOCYCLE condition

START WITH 指定阶层的根
CONNECT BY 指定阶层的父/子关系
NOCYCLE 存在CONNECT BY LOOP的纪录时，也返回查询结果。
condition ... PRIOR expr = expr 或者 ... expr = PRIOR expr
例：
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and
PRIOR account_mgr_id = customer_id ...

■CONNECT_BY_ROOT
查询指定根的阶层数据。

■CONNECT BY子句的例子
通过CONNECT BY子句定义职员和上司的关系。
SQL>SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
112 Urman 108
113 Popp 108
200 Whalen 101

■LEVEL的例子
通过LEVEL虚拟列表示节点的关系。
SQL>SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ----------
101 Kochhar 100 1
108 Greenberg 101 2
109 Faviet 108 3
110 Chen 108 3
111 Sciarra 108 3
112 Urman 108 3
113 Popp 108 3

■START WITH子句的例子
通过START WITH指定根节点，ORDER SIBLINGS BY保持阶层的顺序。

hr.employees里，Steven King是公司的最高责任者，没有上司，他有一个叫John Russell的下属是部门80的管理者。
更新employees表，把Russell设置成King的上司，这样就产生了CONNECT BY LOOP。

■CONNECT_BY_ROOT的例子
1，查询110部门的职员，上司，职员和上司之间级别差及路径。

2，使用GROUP BY语句，查询110部门的职员以及该职员下属职员的工资和。

1

对于oracle进行简单树查询(递归查询)

练习： 通过子节点获得顶节点
select FIRST_VALUE(deptid)
  OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from  persons.dept   start with deptid=76 connect by prior paredeptid=deptid