Oracle SQL - LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY

Introduction

By using START WITH and CONNECT BY clause Oracle assigns several pseudo column values to every row.

You can use these pseudo column values to filter specific rows in the WHERE clause or to enhance the readability of your results in the SELECT clause.

The following are the hierarchical pseudo columns:

Column	Description
LEVEL	The level of the row in the tree structure.
CONNECT_BY_ISCYCLE	The value is 1 for each row with a child that is a parent of the same row (a cyclic reference); otherwise, the value is 0.
CONNECT_BY_ISLEAF	The value is 1 if the row is a leaf; otherwise, the value is 0.

The following code uses the LEVEL pseudo column combined with the LPAD function, adding indentation to highlight the hierarchical query results.

select  lpad(' ',2*level-1)||ename as ename
from    emp
start   with mgr is null
connect by nocycle prior empno = mgr;

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from ww w  .jav a2s .  c  o  m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> select  lpad(' ',2*level-1)||ename as ename
  2  from    emp
  3  start   with mgr is null
  4  connect by nocycle prior empno = mgr;

ENAME
--------------------------------------------------------------------------------
 KING
   JACK
     SCOTT
       ADAMS
     FORD
       SMITH
   BLAKE
     ALLEN
     WARD
     BROWN
     BREAD

ENAME
--------------------------------------------------------------------------------
     JONES
   CLARK
     MARY

14 rows selected.

SQL>

Oracle SQL - LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ISLEAF

Introduction

Demo

Related Topic