Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use connect by/level/start with in oracle, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Level, connect_by_isleaf, connect_by_iscycle pseudo columns:

Level is the level that this data belongs to. For example, PRESIDENT is 1 and Manager is 2.

Connect_by_isleaf is the value at the end of the tree, or there are no leaves under this branch.

The branch that connect_by_iscycle caused the endless cycle.

Through START WITH. . . CONNECT BY. . . Clause to implement the hierarchical query of SQL.

Since Oracle 9i, the content from the parent node to the current line can be displayed as "path" or a list of hierarchical elements through the SYS_CONNECT_BY_PATH function.

Since Oracle 10g, there are more new features about hierarchical queries. For example, sometimes users are more concerned with the lowest-level content in each hierarchical branch.

Then you can use the pseudo-column function CONNECT_BY_ISLEAF to determine whether the current row is a leaf. If it is a leaf, it will display "1" in the pseudo column.

If it is not a leaf but a branch (for example, the current content is the parent of another line), "0" is displayed.

In versions prior to Oracle 10g, if a loop appeared in your tree (such as a child node referencing a parent node), Oracle would report an error: "ORA-01436: CONNECT BY loop in user data". The query operation cannot be performed without deleting the reference to the parent.

In Oracle 10g, arbitrary query operations can be performed as long as "NOCYCLE" is specified. There is also a pseudo column associated with this keyword-CONNECT_BY_ISCYCLE. If the contents of a parent node are referenced in the current row and a loop appears in the tree, the pseudo column of the row will show "1", otherwise it will show "0".

Syntax 1 connect by [nocycle] start with

Syntax 2 start with connect by [nocycle]

Experiment: scott users under the emp table

SQL > select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

14 rows selected.

Find the leader with employee number 7876:

Select level,e.* from emp e connect by prior e.mgr = e.empno start with e.empno = 7876 order by level desc

LEVEL EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

4 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

3 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

2 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

1 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

"start with"-this identifies all level=1 nodes in the tree

Connect by-- describes how to walk from the parent nodes above to their children and their childrens children.

Easiest to use an example on emp. If we start with "where mgr is null", we generate the set of employees that have no mgr (they are the top of the tree).

If we connect by prior empno = / * current * / mgr that will take all of the prior records (the start with at first) and find all records such that the mgr column equals their empno (find all the records of people managed by the people we started with).

Using the with statement to optimize query results: optimization level

With an as

(select max (level) + 1 lvl

From emp e

Connect by prior e.mgr = e.empno

Start with e.empno = 7876

Order by level desc)

The highest level of select a.lvl plus 1

Level current level

Optimized grade of a.lvl-level

E.* from a

Emp e connect by prior e.mgr = e.empno start with e.empno = 7876 order by level desc

The highest level plus 1 the current level optimized level EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

5 4 1 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

5 3 2 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

5 2 3 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

5 1 4 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

Find all subordinates with employee number 7839 (7839 is king):

Select level rating, e.*

From emp e

Connect by prior e.empno = e.mgr

Start with e.empno = 7839

Grade EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

1 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

2 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

3 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

4 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

3 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

4 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

2 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

3 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

3 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

3 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

3 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

3 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

2 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10

3 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

-- construct the whole hierarchy

Select lpad ('', level*2,'') | | ename ename,empno,mgr

From emp

Start with mgr is null

Connect by prior empno = mgr

ENAME EMPNO MGR

KING 7839

JONES 7566 7839

SCOTT 7788 7566

ADAMS 7876 7788

FORD 7902 7566

SMITH 7369 7902

BLAKE 7698 7839

ALLEN 7499 7698

WARD 7521 7698

MARTIN 7654 7698

TURNER 7844 7698

JAMES 7900 7698

CLARK 7782 7839

MILLER 7934 7782

14 rows selected.

So, king is the start with set then jones blake and clark fall under him. Each of them becomes the prior record in turn and their trees are expanded.

Use connect by with level to construct a virtual row:

Select level from dual connect by level

< 5; LEVEL ---------- 1 2 3 4 使用rownum实现类似的功能: select rownum from dual connect by level < 5; ROWNUM ---------- 1 2 3 4 select level from dual connect by rownum KING 2 0 ->

KING- > JONES

3 0-> KING- > JONES- > SCOTT

4 1-> KING- > JONES- > SCOTT- > ADAMS

3 1-> KING- > JONES- > FORD

20-> KING- > BLAKE

3 1-> KING- > BLAKE- > ALLEN

3 1-> KING- > BLAKE- > WARD

3 1-> KING- > BLAKE- > MARTIN

3 1-> KING- > BLAKE- > TURNER

3 1-> KING- > BLAKE- > JAMES

RANK LEAF_IS_OR_NOT PATH

20-> KING- > CLARK

3 1-> KING- > CLARK- > MILLER

13 rows selected.

Sys_connect_by_path starts traversing from where start with starts, and notes the node to which it traverses. The place where start with starts is regarded as the root node, and the traversal path is based on the

Delimiter, to form a new string, this function is powerful.

Example:

-- query the table first:

ENAME 13 rows selected.SQL > select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

13 rows selected.

-- then query and use start with conncet by prior

Select ename

From scott.emp

Start with ename = 'KING'

Connect by prior empno = mgr

ENAME

-

KING

JONES

SCOTT

ADAMS

FORD

BLAKE

ALLEN

WARD

MARTIN

TURNER

JAMES

ENAME

-

CLARK

MILLER

13 rows selected.

-- finally use sys_connect_by_path:

Select sys_connect_by_path (ename,'- >') "path"

From scott.emp

Start with ename = 'KING'

Connect by prior empno = mgr

Path

-

-> KING

-> KING- > JONES

-> KING- > JONES- > SCOTT

-> KING- > JONES- > SCOTT- > ADAMS

-> KING- > JONES- > FORD

-> KING- > BLAKE

-> KING- > BLAKE- > ALLEN

-> KING- > BLAKE- > WARD

-> KING- > BLAKE- > MARTIN

-> KING- > BLAKE- > TURNER

-> KING- > BLAKE- > JAMES

Path

-

-> KING- > CLARK

-> KING- > CLARK- > MILLER

13 rows selected.

5. Prune branches and nodes:

By filtering out the data with the number 7566 (trimming node), he means to cut off the node, but it does not destroy the tree structure, and its child nodes can still be displayed normally.

Select level as rank

Connect_by_isleaf as leaf_is_or_not

Lpad ('', level * 2-1) | | sys_connect_by_path (ename,'= >') path

E.*

From emp e

Where e.empno! = 7566

Connect by prior e.empno = e.mgr

Start with e.mgr is null

RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

10 = > KING 7839 KING PRESIDENT 17-NOV-81 5000 10

30 = > KING= > JONES= > SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20

4 1 = > KING= > JONES= > SCOTT= > ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20

3 1 = > KING= > JONES= > FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20

20 = > KING= > BLAKE 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

3 1 = > KING= > BLAKE= > ALLEN 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

3 1 = > KING= > BLAKE= > WARD 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

3 1 = > KING= > BLAKE= > MARTIN 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

3 1 = > KING= > BLAKE= > TURNER 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

3 1 = > KING= > BLAKE= > JAMES 7900 JAMES CLERK 7698 03-DEC-81 950 30

20 = > KING= > CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10

RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

3 1 = > KING= > CLARK= > MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10

12 rows selected.

-- cut off the node with the number 7698 and its child nodes:

Select level as rank

Connect_by_isleaf as leaf_is_or_not

Lpad ('', level * 2-1) | | sys_connect_by_path (ename,'= >') path

E.*

From emp e

Connect by prior e.empno = e.mgr

And e.empno! = 7698

Start with e.mgr is null

RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

10 = > KING 7839 KING PRESIDENT 17-NOV-81 5000 10

20 = > KING= > JONES 7566 JONES MANAGER 7839 02-APR-81 2975 20

30 = > KING= > JONES= > SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20

4 1 = > KING= > JONES= > SCOTT= > ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20

3 1 = > KING= > JONES= > FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20

20 = > KING= > CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10

3 1 = > KING= > CLARK= > MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10

7 rows selected.

6. For the use of connect_by_root, connect_by_root is added to oracle10g. Through this operation, you can get the field of the root record of the tree query.

SQL > select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

13 rows selected.

-- use connect_by_root to query

Select level as rank

Connect_by_isleaf as leaf_is_or_not

Connect_by_root ename

Lpad ('', level * 2-1) | | sys_connect_by_path (ename,'= >') path

E.*

From emp e

Connect by prior e.empno = e.mgr

Start with e.mgr is null

RANK LEAF_IS_OR_NOT CONNECT_BY PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

10 KING = > KING 7839 KING PRESIDENT 17-NOV-81 5000 10

20 KING= > KING= > JONES 7566 JONES MANAGER 7839 02-APR-81 2975 20

30 KING= > KING= > JONES= > SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20

4 1 KING= > KING= > JONES= > SCOTT= > ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20

3 1 KING= > KING= > JONES= > FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20

20 KING= > KING= > BLAKE 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

3 1 KING= > KING= > BLAKE= > ALLEN 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

3 1 KING= > KING= > BLAKE= > WARD 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

3 1 KING= > KING= > BLAKE= > MARTIN 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

3 1 KING= > KING= > BLAKE= > TURNER 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

3 1 KING= > KING= > BLAKE= > JAMES 7900 JAMES CLERK 7698 03-DEC-81 950 30

RANK LEAF_IS_OR_NOT CONNECT_BY PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

20 KING= > KING= > CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10

3 1 KING= > KING= > CLARK= > MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10

13 rows selected.

The above sql is equivalent to the following:

Select level as rank

Connect_by_isleaf as leaf_is_or_not

Connect_by_root (ename) root_ename

Lpad ('', level * 2-1) | | sys_connect_by_path (ename,'= >') path

E.*

From emp e

Connect by prior e.empno = e.mgr

Start with e.mgr is null

RANK LEAF_IS_OR_NOT ROOT_ENAME PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

10 KING = > KING 7839 KING PRESIDENT 17-NOV-81 5000 10

20 KING= > KING= > JONES 7566 JONES MANAGER 7839 02-APR-81 2975 20

30 KING= > KING= > JONES= > SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20

4 1 KING= > KING= > JONES= > SCOTT= > ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20

3 1 KING= > KING= > JONES= > FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20

20 KING= > KING= > BLAKE 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

3 1 KING= > KING= > BLAKE= > ALLEN 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

3 1 KING= > KING= > BLAKE= > WARD 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

3 1 KING= > KING= > BLAKE= > MARTIN 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

3 1 KING= > KING= > BLAKE= > TURNER 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

3 1 KING= > KING= > BLAKE= > JAMES 7900 JAMES CLERK 7698 03-DEC-81 950 30

RANK LEAF_IS_OR_NOT ROOT_ENAME PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

20 KING= > KING= > CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10

3 1 KING= > KING= > CLARK= > MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10

13 rows selected.

For hierarchical queries, if you use order by to sort, for example, order by last_name is to first finish the hierarchy to get level, and then sort by last_name, which destroys the hierarchy, such as paying special attention to the depth of a row, sorting by level, will also destroy the hierarchy. In oracle10g, the sort of siblings keyword has been added.

Syntax: order siblings by

It protects the hierarchy and sorts by expre in each level.

Select level as rank

Connect_by_isleaf as leaf_is_or_not

Lpad ('', level * 2-1) | | sys_connect_by_path (ename,'= >') path

E.*

From emp e

Connect by prior e.empno = e.mgr

Start with e.mgr is null

Order siblings by e.ename

RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

10 = > KING 7839 KING PRESIDENT 17-NOV-81 5000 10

20 = > KING= > BLAKE 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

3 1 = > KING= > BLAKE= > ALLEN 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

3 1 = > KING= > BLAKE= > JAMES 7900 JAMES CLERK 7698 03-DEC-81 950 30

3 1 = > KING= > BLAKE= > MARTIN 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

3 1 = > KING= > BLAKE= > TURNER 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

3 1 = > KING= > BLAKE= > WARD 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

20 = > KING= > CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10

3 1 = > KING= > CLARK= > MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10

20 = > KING= > JONES 7566 JONES MANAGER 7839 02-APR-81 2975 20

3 1 = > KING= > JONES= > FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20

RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

30 = > KING= > JONES= > SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20

4 1 = > KING= > JONES= > SCOTT= > ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20

13 rows selected.

Connect_by_iscycle (1 will be returned if there is a loop, otherwise 0 will be returned)

The connect_by_iscycle pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.

You can specify connect_by_iscycle only if you have specified the nocycle parameter of the connect by clause. Nocycle enables oracle to return the results of a query that would otherwise fail because of a connect by loop in the data.

Thank you for reading this article carefully. I hope the article "how to use connect by/level/start with in oracle" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.