Oracle Connect by usage article ____oracle

Source: Internet
Author: User
1. Basic Grammar

SELECT * from table [start and Condition1]
    connect by [prior] Id=parentid1

It is commonly used to find data that has a parent-child relationship, that is, a tree-structured data, and its return data can clearly differentiate each layer of data.

The start with Condition1 is used to limit the first layer of data, or to call the root node data, to find the second tier of data based on this part of the data, and then to find the third tier of data with the second tier of data. Connect by [Prior] Id=parentid This section is used to indicate what kind of relationship Oracle is looking for when looking for data, for example, to find the second tier of data with the ID of the first level of data to match the ParentID field recorded in the table, If this condition is set up then the data found is the second layer of data, the same as the third layer of the fourth layer ... And so on are all matched by this.

Prior also has a usage:

SELECT * from table [start and Condition1]
    connect by id= [Prior] Parentid1

Other features

The level keyword, which represents the hierarchy number in the tree structure, the first layer is the number 1, the second level is 2, incremented sequentially. Connect_by_root method, the value of any field in the result set of the first level assembly point can be obtained; connect_by_root (field name). 2.demo Example

2.1 Finding leaf nodes from the root node

Select t.*, Level, Connect_by_root (ID) from
  tab_test T-
 start with = 0
CONNECT by prior = T.fid;

2.2 Find the upper node from the leaf node

--First, modify prior keyword location
select t.*, Level, Connect_by_root (ID) from
  tab_test T-
 start with = 4
CONNECT by = Prior t.fid;

--the second, the sequence of the Id=fid logical relationships after the prior keyword is switched to
select t.*, Level, Connect_by_root (ID) from
  tab_test T
 start with = 4
connect by prior T.fid =;

3. Common examples

3.1 The level two agency code that the person belongs to

Select distinct deptno from
where dept_level = ' 2 '
     connect by dept_no = Prior parent_dept_no
   start with Dept_no in  (select  deptno from  emp where name= ' Zhang Shan ';)
3.2 String Segmentation

Like splitting 01#02#03#04, this regular string.

Select Regexp_substr (' 01#02#03#04 ', ' [^#]+ ', 1, rownum) as Newport to 
    dual connect by rownum <= Regexp_count (' 01# 02#03#04 ', ' [^#]+ '];

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: 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.