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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.