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 AccountingThe 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_nameFOREIGN KEY (parent_name) REFERENCES dept);The result of SELECT * FROM DEPT is:

DEP_NAME PARENT_NAME

-------- ------------Software NULLOS SoftwareFinancials SoftwareSpreadsheet FinancialsAccounting FinancialsThe 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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/219138/viewspace-913418/，如需转载，请注明出处，否则将追究法律责任。