Oracle_071_lesson_p21

you should be able to:
1、Interpret the concept of a hierarchical query
2、Create a tree-structured report
3、Format hierarchical data
4、Exclude branches from the tree structure

SELECT [LEVEL],column,expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;

例：
SELECT employee_id,last_name,job_id,manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;

例：
SELECT last_name||‘ reports to ‘||
PRIOR last_name "Walk Top Down"
FROM employees
START WITH last_name = ‘King‘
CONNECT BY PRIOR employee_id = manager_id ;

例：
select employee_id,manage_id,level
from emp
[可选] where employee_id <>102 裁去102号人
start with employee=100;
connnect by prior employee_id = manage_id;
[可选]and employee_id <> 102; 裁去102号人及以下所有人（tree树结构）

connect by prior 只关心其后紧跟的字段，如果是主键，则从上往下检索，如果是子键，则从下往上检索.

COLUMN org_chart FORMAT A12;
SELECT LPAD(last_name,LENGTH(lastname)+(LEVEL*2)-2,‘‘)
AS org_chart
FROM employees
START WITH first_name=‘Steven‘ AND last_name=‘King‘
CONNECT BY PRIOR employee_id=manager_id ;

Pruning Branches

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!