Use of Oracle Contact By, oraclecontact

Use of Oracle Contact By, oraclecontact
1. Overview

Oracle "connect by" is a hierarchical query clause, which is generally used for query of tree or hierarchical result sets.

 

2. usage method 2. 1. Use Connect by to generate a sequence

Oracle constructs the number of days of a month select to_date ('20170101', 'yyymmm') + (rownum-1) s_date from dual connect by rownum <= last_day (to_date ('20170101 ', 'yyyymm')-to_date ('20140901', 'yyyymm') + 1

Generate a sequence FROM 1 to 10 select rownum from dual connect by rownum <= 10

2.2 tree-like query results

Create table DEP (DEPID number (10) not null, DEPNAME varchar2 (256), UPPERDEPID number (10) values insert into dep (DEPID, DEPNAME, UPPERDEPID) VALUES (0, 'General authorization', null); insert into dep (DEPID, DEPNAME, UPPERDEPID) VALUES (1, 'Demo', 0); insert into dep (DEPID, DEPNAME, UPPERDEPID) VALUES (2, 'test part', 0); insert into dep (DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'sever demo', 1); insert into dep (DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'client demo', 1); insert into dep (DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'ta testing part', 2 ); insert into dep (DEPID, DEPNAME, UPPERDEPID) VALUES (6, 'Project test part', 2); explain select rpad ('', 2*(LEVEL-1 ), '-') | DEPNAME "DEPNAME", CONNECT_BY_ROOT DEPNAME "ROOT", CONNECT_BY_ISLEAF "ISLEAF", LEVEL, SYS_CONNECT_BY_PATH (DEPNAME ,'/') "PATH" from depstart with upperdepid is nullconnect by prior depid = UPPERDEPID;

Download DEMO