Code Analysis of an Oracle recursive query instance

Oracle DatabaseOfRecursive queryIt is widely used. For recursive queries, we must first define the recursive function, its parameters and return values, and then implement the query. This article introduces this process through an example of code. Next we will introduce it.

I. Tree table structure:

Node ID parent ID node name

Ii. formula:

 
 

  
  
Select node ID, node name, level
  
  
 
  
  
From table
  
  
 
  
  
Connect by prior node ID = parent node ID
  
  
 
  
  
Start with parent node ID = node Value
 
 

Note:

1. The common tree structure is the company's organization, region ......

2. Find the structure above the node ID or above, and change the order of "Node ID = parent node ID.

3. Level is a special field of Oracle, indicating the meaning of "layer. The next node of the current node ID is "1 ".

Test SQL:

Description 1. Calculate all subnodes and layers below 002 (or above) (dynamic: always starts from 1), but does not include itself.

NOTE 2: If you want to find a node larger than 002, you can change the conditions on both sides of "connect by prior topno = no" and "=.

 
 

  
  
select departno,departname,level  
  
  
 
  
  
from dept  
  
  
 
  
  
connect by prior departno=topno 
  
  
 
  
  
start with topno='002'; 
 
 

Test data:

 
 

  
  
Create table Dept (
  
  
 
  
  
Optional no varchar2 (10 ),
  
  
 
  
  
Invalid name varchar2 (20 ),
  
  
 
  
  
TopNo varchar2 (10 ));
  
  
 
  
  
Insert into Dept values ('001', 'board of directors ', '0 ');
  
  
 
  
  
Insert into Dept values ('002 ', 'President office', '001 ');
  
  
 
  
  
Insert into Dept values ('003 ', 'Finance Department', '001 ');
  
  
 
  
  
Insert into Dept values ('004 ', 'marketing Department', '002 ');
  
  
 
  
  
Insert into Dept values ('005 ', 'public relations', '002 ');
  
  
 
  
  
Insert into Dept values ('006 ', 'sales Department', '002 ');
  
  
 
  
  
Insert into Dept values ('007 ', 'sales office', '006 ');
  
  
 
  
  
Insert into Dept values ('008 ', 'business expansion data', '004 ');
  
  
 
  
  
Insert into Dept values ('009', 'sales core', '007 ');
 
 

Forward query, for example:

 
 

  
  
select distinct departno,departname,level  
  
  
 
  
  
from dept  
  
  
 
  
  
connect by prior topno=departno 
  
  
 
  
  
start with  
  
  
 
  
  
departno='005'; 
 
 

In fact, the query result is 005,002,001 for both the user and the superior.

Summary:The key to recursion is to define a recursive function. The key to a recursive function is to define its parameters and return values. Parameters are the most important. How can we define parameters? What is the process of recursion? Find its parent id based on its id, find its child based on its parent id, and find its child based on its child. Therefore, the analysis of this parameter must be an id, because only the input id knows how to find this id.

We have introduced so much about recursive queries in Oracle databases. Thank you for your support!