Connect by prior start... Connect... (Oracle)

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.