Explain the Oracle function sys_connect_by_path,

Explain the Oracle function sys_connect_by_path,

Oracle function sys_connect_by_path

Syntax:
Oracle function: sys_connect_by_path is mainly used for tree query (hierarchical query) and Multi-column forwarding. The syntax is generally:
Select... sys_connect_by_path (column_name, 'connect _ symbol') from table
Start with... connect by... prior
Understanding:
For a database, the root node is not necessarily a top-level node designed in the database, but a place where start with starts. The sys_connect_by_path function traverses nodes starting from start with, and records the nodes it traverses. The start with function is regarded as the root node. The traversal path is based on the Separator in the function, to form a new string. The sys_connect_by_path function uses connect by to find the next record until the corresponding record cannot be found in the iteration. Similar to recursion, connect by specifies a recursion (join) condition. If the condition is not met, the recursion ends.

1. Find all subordinate employees of an employee.

Start with ename = 'King' connect by prior empno = mgr;
I understand it as follows: first, the field in the database: empno -- empname -- mgr; here, the number of 'King' is used as the management number starting from 'King, then the employee numbered as the administrator in 'King' is his subordinates (cyclic mode ).

2. Find all managers of an employee.

Start with ename = 'King' connect by prior mgr = empno;
I understand this as follows: first, the field in the database: empno -- empname -- mgr; here, starting from 'King', the number of 'King' is used as the employee number, then gmr in The 'King' number is his boss (iterative method ).

The following is an experiment:

-- Creat table emp
Create table emp (empno varchar2 (5), ename varchar2 (8), mgr varchar2 (8 ));
-- INSERT DATA
Insert into emp (EMPNO, ENAME, MGR)
Values ('1', 'Jim ', '7 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('2', 'Tom ', '7 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('3', 'Tim', '7 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('4', 'lily', '7 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('5', 'Mary ', '7 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('6', 'tid', '7 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('7', 'King', '10 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('8', 'kenvin ', '10 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('9', 'shema ', '8 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('10', 'john', '0 ');

Insert into emp (EMPNO, ENAME, MGR)
Values ('0', 'root ','');

SELECT * FROM emp;

 

Select sys_connect_by_path (ename, '>') tree from emp start with ename = 'King' connect by prior empno = mgr;

 

Select sys_connect_by_path (ename, '/') tree from emp start with ename = 'King' connect by prior empno = mgr; -- it can be understood that the query '> 'and'/'of king employees are only an output format.

 

Select sys_connect_by_path (ename, '/') tree from emp start with ename = 'King' connect by empno = prior mgr; -- it can be understood that the lead effect of king query is the same as that of connect by prior mgr = empno

Select sys_connect_by_path (ename, '/') tree, level from emp start with ename = 'King' connect by prior mgr = empno; -- can be understood as the query leader of King

 

Conclusion (for your understanding): connect by prior column1 = column2 or connect by column2 = prior column1 can all be written,

It mainly depends on who prior is written before, and who is written before prior is used as the parent value (reference value ), that is to say, [connect by prior column1 = column2] means to query records whose values are column1 Based on the record row queried by the start with condition and with column1 as the reference value.