Sys_connect_by_path function usage Oracle

Select ename
From Scott. EMP
Start
With ename = 'King'
Connect by prior empno = Mgr;

--
The result is as follows:

King
Jones
Scott
Adams
Ford

Smith
Blake
Allen
Ward
Martin
Turner

James



And:



Select
Sys_connect_by_path (ename, '>') "path"
From Scott. EMP
Start
With ename = 'King'
Connect by prior empno = Mgr;


 

-- The result is:



> King
> King> Jones

> King> Jones> Scott
> King> Jones> Scott> Adams

> King> Jones> Ford
> King> Jones> Ford> Smith

> King> Blake
> King> Blake> Allen
> King> Blake> ward

> King> Blake> Martin
> King> Blake> Turner
> King> Blake> James

> King> Clark
> King> Clark> Miller
Select
Ename
From Scott. EMP
Start with ename = 'King'
Connect
Prior empno = Mgr;

-- The result is:

King
Jones
Scott
Adams
Ford
Smith
Blake
Allen
Ward
Martin
Turner
James

 

And:

 

Select sys_connect_by_path (ename, '>') "path"
From Scott. EMP
Start
With ename = 'King'
Connect by prior empno = Mgr;

 

-- The result is:

 

> King
> King> Jones
> King> Jones> Scott
> King> Jones> Scott> Adams
> King> Jones> Ford
> King> Jones> Ford> Smith
> King> Blake
> King> Blake> Allen
> King> Blake> ward
> King> Blake> Martin
> King> Blake> Turner
> King> Blake> James
> King> Clark
> King> Clark> Miller

In fact, sys_connect_by_path is a new function provided by Oracle9i!
It must be used with the connect by clause!
The
One parameter is a field in the form of a tree, and the second parameter is the delimiter used to separate the parent and its child!

Start with indicates the node to be traversed!

Connect by prior indicates the parent-child relationship!

Example:

View plaincopy to clipboardprint?
Select max (
Substr (
Sys_connect_by_path (column_name ,',')

, 2)
)
From (select column_name, rownum rn from
User_tab_columns where table_name = 'aa _ test ')
Start with Rn = 1
Connect by Rn = rownum;
Select max (
Substr (
Sys_connect_by_path (column_name ,',')
, 2)
)
From
(Select column_name, rownum rn from user_tab_columns where table_name
= 'Aa _ test ')
Start with Rn = 1 connect by Rn = rownum;

 

Is to use the column, split it into a row, and then remove the first, take only the largest data.

---------------------------------------------

The following is an example of another person:

1. hierarchical relationship

View plaincopy to clipboardprint?
SQL> Create Table dept (deptno
Number, deptname varchar2 (20), mgrno number );

Table
Created.

SQL> insert into dept values (1, 'head office ', null );
 

1 row created.

SQL> insert into Dept
Values (2, 'zhejiang branch ', 1 );

1 row created.

SQL>
Insert into dept values (3, 'hangzhou branch ', 2 );

1 row created.
 

SQL> commit;

Commit complete.

SQL>
Select max (substr (sys_connect_by_path (deptname, ','), 2) from Dept
Connect by prior deptno = mgrno;

Max (substr (sys_connect_by_path (deptname, ','), 2 ))

--------------------------------------------------------------------------------

Head Office, Zhejiang Branch, Hangzhou Branch
SQL> Create Table dept (deptno
Number, deptname varchar2 (20), mgrno number );

Table created.

SQL> insert into dept values (1, 'head office ', null );

1 row created.

SQL> insert into dept values (2, 'zhejiang branch ', 1 );

1 row created.

SQL> insert into dept values (3, 'hangzhou branch ', 2 );

1 row created.

SQL> commit;

Commit complete.

SQL> select max (substr (sys_connect_by_path (deptname, ','), 2) from
Dept connect by prior deptno = mgrno;

Max (substr (sys_connect_by_path (deptname, ','), 2 ))
--------------------------------------------------------------------------------
Total
Company, Zhejiang Branch, Hangzhou Branch

2. row-column Conversion

For example, you can concatenate all columns of a table into one row and separate them with commas:

View plaincopy to clipboardprint?
SQL> select
Max (substr (sys_connect_by_path (column_name, ','), 2 ))
From (select
Column_name, rownum rn from user_tab_columns where table_name = 'dept ')
Start
With Rn = 1 connect by Rn = rownum;

Max (substr (sys_connect_by_path (column_name, ','), 2 ))

--------------------------------------------------------------------------------

Deptno, deptname, mgrno