Oracle Recursive functions and stitching

1 SELECTSUBSTR (Sys_connect_by_path (Tb.name,' -'),3) name2  from    TableTB3START withNVL (Tb.parentid,0)=04CONNECT byPRIOR ID=Mt.parentid5;

in Oracle, the primary function of the Sys_connect_by_path function is to differentiate all child nodes under a parent node by a character, and then connect to display in a single column. sys_connect_by_path (field name, connection symbol between 2 fields), note that the connection symbol here does not use commas, Oracle will error,If you want to use it, you can replace it with replace (field name, original character, ', ') as follows.   Also, Sys_connect_by_path This function is oracle9i only new proposed! It is to be combined with the Connect by clause.

Example:

1. Create a table

1 CREATE TABLEsc_district2 (3Id Number(Ten) not NULL,4parent_id Number(Ten),5NAMEVARCHAR2(255BYTE) not NULL6 );

2. Add Data

1 INSERT  intoSc_district (Id,name)VALUES(1,'Sichuan Province');2 3 INSERT  intoSc_district (Id,parent_id,name)VALUES(2,1,'Bazhong');4 INSERT  intoSc_district (Id,parent_id,name)VALUES(3,1,'Dazhou'); 5 6 INSERT  intoSc_district (Id,parent_id,name)VALUES(4,2,'Bazhou District');7 INSERT  intoSc_district (Id,parent_id,name)VALUES(5,2,'Tongjiang County');8 INSERT  intoSc_district (Id,parent_id,name)VALUES(6,2,'Pingchang County');9 Ten INSERT  intoSc_district (Id,parent_id,name)VALUES(7,3,'Tongchuan District'); One INSERT  intoSc_district (Id,parent_id,name)VALUES(8,3,'Xuanhan County'); A  - INSERT  intoSc_district (Id,parent_id,name)VALUES(9,8,'Tahe Xiang'); - INSERT  intoSc_district (Id,parent_id,name)VALUES(Ten,8,'Sanhe Township'); the INSERT  intoSc_district (Id,parent_id,name)VALUES( One,8,'Hu Jia Zhen'); - INSERT  intoSc_district (Id,parent_id,name)VALUES( A,8,'nan Ba town'); -   - INSERT  intoSc_district (Id,parent_id,name)VALUES( -,6,'da Zhai Xiang'); + INSERT  intoSc_district (Id,parent_id,name)VALUES( -,6,'rang Tan Zhen'); - INSERT  intoSc_district (Id,parent_id,name)VALUES( the,6,'Longgang Town'); + INSERT  intoSc_district (Id,parent_id,name)VALUES( -,6,'White Town');

The resulting table and data are as follows:

1Query the recursive path of administrative organization under Bazhong2SELECTID, NAME, parent_id,3 SUBSTR (Sys_connect_by_path (NAME,‘-‘),3) Name_path4FromSc_district5 STARTWith NAME=‘Bazhong City‘6 CONNECTby PRIOR ID=parent_id78Query Result:9ID NAME parent_id Name_path102 Bazhong City1Bazhong City114 Bazhou District2 Bazhong City-Bazhou District125 Tongjiang County2 Bazhong City-Tongjiang County136 Pingchang County2 Bazhong City-Pingchang County1413 da Zhai Xiang6 Bazhong City-> Pingchang County -> da Zhai Xiang Span style= "color: #008080;" >15 6 Bazhong City -> Pingchang County -> rang beach town Span style= "color: #008080;" >16 15 Longgang Town 6 Bazhong City -> Pingchang County -> Longgang Town Span style= "color: #008080;" >17 6 Bazhong City -> Pingchang County -> White town       

Original link: http://www.cnblogs.com/wanghonghu/archive/2012/08/31/2665945.html

Oracle Recursive functions and stitching