Basic syntax used in Oracle Structured Query

If you want to know that connect by is the basic syntax used in Oracle structured queries, you can click the following article to learn about its usage in actual operations, I have a better understanding. The following is a detailed description of the text.

 
 

  
  
select ... from tablename start by cond1  
  
  
connect by cond2  
  
  
where cond3;  
 
 

Simply put, a tree structure is stored in a table. For example, if a table has two fields: id and parentid, a tree structure can be formed by indicating who the parent of each record is. You can use the preceding syntax to query all records of the tree.

COND1 is a limit statement for the root node. Of course, you can relax the limit conditions to obtain multiple root nodes. Actually, it is multiple trees. COND2 is the connection condition, where PRIOR represents the previous record. For example, connect by prior id = PRAENTID indicates that the ID of the previous record is the PRAENTID of this record, that is, the father of this record is the previous record. COND3 is a filter condition used to filter all returned records.

In the connect by statement, the PRIOR and start with keywords used in Oracle structured queries are optional. The PRIORY operator must be placed before one of the two columns of the connection relationship. For the parent-child relationship between nodes, the PRIOR operator represents the parent node on one side and the child node on the other side to determine whether the order of the tree structure is top-down or bottom-up.

In addition to column names, you can also use column expressions in the join relationship. The start with clause is optional to identify the node used as the root node for searching the tree structure.

If this clause is omitted, all rows that meet the query conditions are used as the root node.

The complete example is as follows:

 
 

  
  
SELECT PID,ID,NAME FROM T_WF_ENG_WFKIND START 
WITH PID =0 CONNECT BY PRIOR ID = PID 
 
 

The above uses start with for downstream recursive queries on the upper layer... connect by prior... this method, but sometimes it is necessary to reverse recursive queries from the lower layer to the upper layer when required. This is a statement change: for example, to implement

 
 

  
  
select * from table where id in 
('0','01','0101','0203','0304') ; 
 
 

Now we want to recursive the first-level 03 of 0304, and the second-level 02 of 0203 to recursive, and 01 already exists, with the highest level 0. this table is not only the data, but now I only need to ('0', '01', '123', '123', '123', '02 ', '03 '). The statement can be written in this way.

 
 

  
  
SELECT PID,ID,NAME FROM V_WF_WFKIND_TREE WHERE 
ID IN (SELECT DISTINCT(ID) ID FROM V_WF_WFKIND_TREE 
CONNECT BY PRIOR PID = ID START WITH ID IN 
('0','01','0101','0203','0304') ); 
 
 

The value in start with id in can also replace the SELECT subquery statement. note that the difference between the upper-layer and lower-layer recursion lies in start... connect by prior... the Order and the tiny changes of ID = PID and PID = ID!

The above content is about the basic syntax used in the connect by structured Oracle query. I hope you will get some benefits.