Connect by prior start .... connect... when SQL queries are used, a table is often used when there is a limit relationship.
Signature method:
General Limit Method: Select * From table_name WHERE clause 1 connect by clause 2 start with Clause 3
Where connect by clause 2 and start with Clause 3 are placed in the first and second orders, which does not affect the result of querying logs.
[Where Clause 1] is the record selected by the "connect by clause 2 and start with Clause 3, is the structure of the organizational unit that does not take into account.
[Condition 2] is the condition for specifying the structure and the excessive condition for the branch, in this example, the logs that match the condition and all the sub-nodes under the condition are generated.
[Condition 3] is a condition that is limited to the start point of the search. If a top-down search condition is used as the root node, A bottom-up search is a condition defined as a self-built vertex.
Example:
Create Table
Create Table sfis1.sys _ program_func
(
Func_code varchar2 (30 byte ),
Func_name varchar2 (30 byte ),
Func_text varchar2 (30 byte ),
Func_type varchar2 (1 byte ),
Parent_code varchar2 (30 byte ),
Creater varchar2 (30 byte ),
Creatdata date,
Is_enable varchar2 (1 byte) default 1
)
Insert data
For more direct data display, we have attached the "keep tables" table.
Func_code |
Func_name |
Func_text |
Func_type |
Parent_code |
Is_enable |
Sys_func |
Sys_func |
Systme |
1 |
Root |
1 |
Wip_func |
Wip_func |
WIP |
1 |
Root |
1 |
Lab_func |
Lab_func |
Label Center |
1 |
Root |
1 |
Sap_func |
Sap_func |
SAP tools |
1 |
Root |
1 |
Menu_func |
Menu_func |
Menu management |
2 |
Sys_func |
1 |
Rout_func |
Rout_func |
Route Management |
2 |
Wip_func |
1 |
Performance_func |
Performance_func |
Command Management |
2 |
Lab_func |
1 |
Inte_func |
Inte_func |
Interface Management |
2 |
Sap_func |
1 |
|
|
|
|
|
|
SQL statement:
Select func_code, func_text, parent_code, is_enable from sys_program_func
Where is_enable = '1'
Start with func_code in (select func_code from sys_program_func
Where func_type = '1 ')
Connect by sys_program_func.parent_code = prior sys_program_func.func_code
Let's talk about this in the case of graphic analytics. If you want to learn and make progress together, you can leave a message to me for improvement.