分析函式學習1 level

level is a pseudo column used with CONNECT BY and denotes the node level of the tree structure.

[@more@]For example, given the following department/sub-department layering architecture, we have an Accounting department within a Financials department within a Software department, that is,

Software
OS
Financials
Spreadsheets
Accounting
The existence of a valid "parent" department can be enforced with a foreign key constraint on a department name column. This constraint ensures that IF a department has a parent, it is an existing department in the same table.

CREATE TABLE dept
(dept_name VARCHAR2(20) PRIMARY KEY,
parent_name VARCHAR2(20),
CONSTRAINT fk_dept2_parent_name
FOREIGN KEY (parent_name) REFERENCES dept);
The result of SELECT * FROM DEPT is:

DEP_NAME PARENT_NAME
-------- ------------
Software NULL
OS Software
Financials Software
Spreadsheet Financials
Accounting Financials
The following SQL statement uses LEVEL to denote the level number of the node in the tree structure.

SELECT
LEVEL, parent_name, dept_name
FROM
dept
CONNECT BY
prior dept_name = parent_name
START WITH
dept_name = 'Software'
ORDER BY LEVEL;
The result is:

LEVEL PARENT_NAME DEPT_NAME
---------- -------------------- --------------------
1 Software
2 Software OS
2 Software Financials
3 Financials Spreadsheets
3 Financials Accounting