Use of Oracle contact by

1.Overview

Oracle "CONNECT by" is a hierarchical query clause that is typically used for queries in tree or hierarchical result sets

2. How to use 2.1. Via Connect byGenerating Sequences

Oracle constructs the number of days in a monthSelectTo_date ('200809','yyyymm')+(rownum-1) s_date fromDual Connect byrownum<=Last_day (To_date ('200809','yyyymm'))-To_date ('200809','yyyymm')+ 1

Generatea sequence of 1-10 SELECT from DUAL CONNECT by<=

2.2 Implementing tree-like query results

Create TableDEP (Depid Number(Ten) not NULL, Depnamevarchar2( the), Upperdepid Number(Ten))---------------------------------------------------------------------INSERT  intoDEP (Depid, Depname, Upperdepid)VALUES(0,'General Handling',NULL);INSERT  intoDEP (Depid, Depname, Upperdepid)VALUES(1,'Development Department',0);INSERT  intoDEP (Depid, Depname, Upperdepid)VALUES(2,'Testing Department',0);INSERT  intoDEP (Depid, Depname, Upperdepid)VALUES(3,'Sever Development Department',1);INSERT  intoDEP (Depid, Depname, Upperdepid)VALUES(4,'Client Development Department',1);INSERT  intoDEP (Depid, Depname, Upperdepid)VALUES(5,'TA Test Department',2);INSERT  intoDEP (Depid, Depname, Upperdepid)VALUES(6,'Project Testing Department',2);---------------------------------------------------------------------SELECTRpad (' ',2*( Level-1),'-')||depname "Depname", Connect_by_root depname "ROOT", Connect_by_isleaf "IsLeaf", Level, Sys_connect_by_path (Depname,'/') "PATH" fromDepstart withUpperdepid is NULLCONNECT byPRIOR Depid=Upperdepid;

Demo download

Use of Oracle contact by