转：Oracle中Level函数的使用实例.

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

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