selectAD_DEPARTMENT_CODE, AD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_NAME

 from(selectAD_DEPARTMENT_CODE,

              AD_DEPARTMENT_UP_LEVEL,

              AD_DEPARTMENT_NAME

         fromAD_DEPT_ARCHITECTURE

        orderbyAD_DEPARTMENT_UP_LEVEL, AD_DEPARTMENT_CODE)

 start withAD_DEPARTMENT_CODE ='0'

connect by priorAD_DEPARTMENT_CODE = AD_DEPARTMENT_UP_LEVEL;

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

SELECTAPR_CODE, APR_CODE_UP, NAMES

 FROM(SELECTA.MEDIA_CODE ||'|'|| A.CHANNEL_CODE ||'|'|| A.COLUMN_CODEASAPR_CODE,

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

              A.COLUMN_CODE_UPASAPR_CODE_UP,

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

                     D.COLUMN_NAME),

                     '--',

                     '产品关系',

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

                     D.COLUMN_NAME)) NAMES

         FROMCOLUMNSD, CHANNEL C, MEDIA B, AD_PRODUCT_RELATION A

        WHEREA.MEDIA_CODE = B.MEDIA_CODE(+)

          ANDA.MEDIA_CODE = C.MEDIA_CODE(+)

          ANDA.CHANNEL_CODE = C.CHANNEL_CODE(+)

          ANDA.MEDIA_CODE = D.MEDIA_CODE(+)

          ANDA.CHANNEL_CODE = D.CHANNEL_CODE(+)

          ANDA.COLUMN_CODE = D.COLUMN_CODE(+))

 STARTWITHAPR_CODE ='0|0|0'

CONNECTBYPRIORAPR_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

正确语句：

SELECTTRANSFER, TAPE_NO, TRANSFER_NAME

 FROM(SELECTTRANSFER, TAPE_NO, TRANSFER_NAME

         FROMTRANSFER

        ORDERBYTAPE_NO, TRANSFER)

 STARTWITHTRANSFER =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 WITHcondition 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