Oracle_071_lesson_p22
Hierarchical Retrieval 层次检索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 , column, expr...
FROM table
;
例：
SELECT employee_id, last_name, job_id, manager_id
FROM employees
STARTWITHemployee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;
例：
SELECTlast_name||' reports to '||
PRIOR last_name "Walk Top Down"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id ;
例：
selectemployee_id , last_name , manage_id , level
fromemp
[可选] whereemployee_id 102 裁去102号人
startwithemployee=100;
connnectbyprior employee_id = manage_id;
[可选]and employee_id102;裁去102号人以下所有人（tree树结构）
connectbyprior只关心其后紧跟的字段，如果是主键，则从上往下检索，如果是子键，则从下往上检索.
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
页:
[1]