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 [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 , last_name , 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(last_name)+(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
