Oracle recursive functions and concatenation, oracle recursive concatenation

Oracle recursive functions and concatenation, oracle recursive concatenation

1 SELECT  SUBSTR(SYS_CONNECT_BY_PATH(tb.name,'->'),3)   name2 FROM    table  tb3         START   WITH nvl(tb.parentid,0)=04         CONNECT BY PRIOR ID=mt.parentid 5         ;

In Oracle, The SYS_CONNECT_BY_PATH function is mainly used to distinguish all the child nodes under a parent node by a specific character, and then connect them to a column for display. Sys_connect_by_path (field name, The Connection Symbol between two fields). Note that the Connection Symbol here should not use commas. oracle will report an error. If necessary, replace it with replace, the method is as follows REPLACE (field name, original character ,','). Also, SYS_CONNECT_BY_PATH is a new function provided by oracle9i! It must be used with the connect by clause.

Example:

1. Create a table

 1 CREATE TABLE SC_DISTRICT 2 ( 3   ID         NUMBER(10)                  NOT NULL, 4   PARENT_ID  NUMBER(10), 5   NAME       VARCHAR2(255 BYTE)          NOT NULL 6 );

2. Add data

1 insert into SC _DISTRICT (ID, NAME) VALUES (1, 'sichuan province '); 2 3 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (2, 1, 'barzhong '); 4 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (, 'daobao'); 5 6 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES, 'bazhou district '); 7 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (5, 2, 'tongjiang'); 8 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (6, 2, 'pyeongchang County '); 9 10 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (, 'tongchuan Region'); 11 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (8, 3, 'xuanhanxian '); 12 13 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (9, 8, 'River loan'); 14 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (, 'sanhe township '); 15 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (, 'hujia town'); 16 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (, 'nanba Zhen '); 17 18 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (, 'dazhai township '); 19 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (, 'xiangtan Zhen '); 20 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES, 'longgang town'); 21 insert into SC _DISTRICT (ID, PARENT_ID, NAME) VALUES (16,6, 'baiyi town ');

Generate tables and data as follows:

1. query the recursive path of the administrative organization in Bazhong City. 2 select id, NAME, PARENT_ID, 3 SUBSTR (SYS_CONNECT_BY_PATH (NAME, '->'), 3) NAME_PATH 4 FROM SC _DISTRICT 5 START WITH NAME = 'barzhong' 6 CONNECT BY PRIOR ID = PARENT_ID 7 8 query results: 9 id name PARENT_ID NAME_PATH10 2 Bazhong City 1 Bazhong City 11 4 baju District 2 Bazhong City> baju District 12 5 Tongjiang County 2 Bazhong City> Tongjiang County 13 6 Pyeongchang County 2 Bazhong City> Pyeongchang County 14 13 Dazhai township 6 Bazhong City> pyeongchang County> Dazhai township 15 14 Xiangtan town 6 Bazhong City> Pyeongchang County> Xiangtan town 16 15 Longgang Town 6 Bazhong City> Pyeongchang County> Longgang Town 17 16 baili town 6 Bazhong City> Pyeongchang County> Bai Yi Town

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