Next, I will introduce the recursive with subquery method of Oracle 11gr2 in detail, as follows:

```
SQL> with emp_data(ename,empno,mgr,l)
as
(select ename, empno, mgr, 1 lvl from emp where mgr is null
union all
select emp.ename, emp.empno, emp.mgr, ed.l+1
from emp, emp_data ed
where emp.mgr = ed.empno
)
SEARCH DEPTH FIRST BY ename SET order_by
select l,
lpad('*' ,2*l, '*')||ename nm
from emp_data
order by order_by
/
```

L NM

—- —————

1 **KING

2 ****BLAKE

3 ******ALLEN

3 ******JAMES

3 ******MARTIN

3 ******TURNER

3 ******WARD

2 ****CLARK

3 ******MILLER

2 ****JONES

3 ******FORD

4 ********SMITH

3 ******SCOTT

4 ********ADAMS

14 rows selected.

I don’t know how it really works. In my imagination, I can play many new tricks more than the original sys connect by path, such as accumulating by path, more flexible pruning conditions,

With subqueries, also known as CTE (common table expression), are part of the ANSI SQL-99 standard. Oracle introduces with subquery from 9i, which is called subquery factoring.

With subqueries act like inline views. The definition of the inline view is written after the SQL from and can only be referenced once. The with subquery needs to be defined before the reference. Once it is defined, it can be referenced repeatedly by name in the subsequent part of the whole query. From this point of view, it is similar to a temporary table.

Starting with version 11gr2, Oracle supports recursive with, which allows for self reference in the definition of a with subquery. This is nothing new. Other databases such as DB2, Firebird, Microsoft SQL server and PostgreSQL support this feature before oracle. However, for Oracle users, this recursive feature is still very desirable, and many new functions that can’t be done in the past or are very difficult to be achieved can be easily realized by using it. In this chapter, we will explore this exciting new feature and compare it with previous implementation methods (mainly connect by hierarchical query).

Let’s first look at the syntax of this recursive with subquery:

WITH

① query_name ([c_alias [, c_alias]…])

② AS (subquery)

③ [search_clause]

④ [cycle_clause]

⑤ [,query_name ([c_alias [, c_alias]…]) AS (subquery) [search_clause] [cycle_clause]]…

① This is the name of the subquery. Unlike in the past, all the column names of the subquery must be written in parentheses.

② Subquery after as is the query statement, and the recursive part is written here.

③ Traversal order clause, which can specify depth first or breadth first traversal order.

④ Loop clause, used to abort the loop that occurs in traversal.

⑤ If there are other recursive subqueries, the definition is the same as above.

Subquery consists of two members: anchor member and recursive member. They must be combined with union all, and anchor member must be written in front of recursive member.

Anchor member is used to locate the entry of recursion. Anchor member is a select statement, and it cannot contain its own name. This is equivalent to start with in connect by query. The typical writing method is:

Select… From table to traverse where… (start condition)

A recursive member is also a select statement, which is used to define the relationship between the superior and the subordinate. It must contain its own name (i.e. query [name]) and can only be referenced once. Recursion is embodied in the reference to itself. A typical way is to make a connection between query_name and other tables (generally speaking, the tables you want to traverse). The connection conditions indicate the relationship between the upper and lower levels. It must be noted that not all data so far in this query name is visible, only the latest layer of data added in the last recursion. A reference to the query_name column is equivalent to the prior operator in connect by. When you can’t find the lower level that meets the conditions, the traversal will stop; if you have other recursion exit conditions, you can also write them together in where. When the where doesn’t meet the conditions, the traversal will stop, which is the pruning operation when traversing trees and graphs. The earlier it stops, the more efficient it is.

This recursive member is the place where programmers play their creativity. In the past, things that could not be done in connect by, such as summation along the path, quadrature and other operations, are now easy. Sys connect by path is also easy to implement with string concatenation ‘|’.

Search clause and cycle clause are shown in the following examples.

Let’s take a look at an example of the usage of a recursive with subquery.

Example 1:

Let’s take a simple example to find the parent-child relationship from the EMP table of Scott / tiger

Traditional connect by:

```
SELECT empno
,ename
,job
,mgr
,deptno
,level
,SYS_CONNECT_BY_PATH(ename,'\') AS path
,CONNECT_BY_ROOT(ename) AS top_manager
FROM EMP
Start with Mgr is null -- Mgr column is empty, indicating that there is no superior, and the employee is already at the highest level. This is the starting point of hierarchical query
CONNECT BY PRIOR empno= mgr;
```

New recursive with method:

```
With t (empno, ename, job, Mgr, deptno, the_level, path, top_manager) as (- -- structure must be written out
Select empno, ename, job, Mgr, deptno -- write the anchor query first, and use the condition of start with
, 1 as the "level
, '\' ||ename -- the first section of the path
Original connect by root
FROM EMP
Where Mgr is null -- original start with condition
Union all -- the following is the recursive part
Select e.empno, e.ename, e.job, e.mgr, e.deptno -- a new layer of data to be added, from the EMP table to be traversed
, 1 + T.The "level" -- recursion level, add 1 on the original basis. This is equivalent to the level pseudo column in the connect by query
, t.path| \ '|e.ename
Directly inherit the original data, because there is only one root node in each path
From t, EMP e -- a typical writing method that makes a connection between the subquery itself and the table to be traversed
Where t.empno = e.mgr -- original connect by condition
）---- with definition ends
SELECT * FROM T
;
```

Query results:

```
EMPNO ENAME JOB MGR DEPTNO THE_LEVEL PATH TOP_MANAGE
------ ---------- --------- ------ ------- ---------- -------------------------- ----------
7839 KING PRESIDENT 10 1 \KING KING
7566 JONES MANAGER 7839 20 2 \KING\JONES KING
7698 BLAKE MANAGER 7839 30 2 \KING\BLAKE KING
7782 CLARK MANAGER 7839 10 2 \KING\CLARK KING
7499 ALLEN SALESMAN 7698 30 3 \KING\BLAKE\ALLEN KING
7521 WARD SALESMAN 7698 30 3 \KING\BLAKE\WARD KING
7654 MARTIN SALESMAN 7698 30 3 \KING\BLAKE\MARTIN KING
7788 SCOTT ANALYST 7566 20 3 \KING\JONES\SCOTT KING
7844 TURNER SALESMAN 7698 30 3 \KING\BLAKE\TURNER KING
7900 JAMES CLERK 7698 30 3 \KING\BLAKE\JAMES KING
7902 FORD ANALYST 7566 20 3 \KING\JONES\FORD KING
7934 MILLER CLERK 7782 10 3 \KING\CLARK\MILLER KING
7369 SMITH CLERK 7902 20 4 \KING\JONES\FORD\SMITH KING
7876 ADAMS CLERK 7788 20 4 \KING\JONES\SCOTT\ADAMS KING
```

14 rows selected.

From the result set’s the level and path columns, you can clearly see how the data is stacked layer by layer.

Example 2:

Construct the sequence of equal difference numbers:

Connect by:

This is a very special usage, because there is no hierarchy, only the termination condition of traversal. Like this kind of connect by, I strongly recommend running on a result set with only one row (such as from dual, such as a sub query after aggregation). Running on a multi row set is difficult to control and requires a clear mind.

(all of the following rownum can be changed to level, with the same effect):

```
SELECT ROWNUM n
,ROWNUM*2 n2
,DATE '2010-1-1'+ROWNUM-1 dt
,ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon
FROM DUAL
CONNECT BY ROWNUM<=10;
```

result:

N N2 DT MON

———- ———- ———– ———–

1 2 2010-01-01 2010-01-01

2 4 2010-01-02 2010-02-01

3 6 2010-01-03 2010-03-01

4 8 2010-01-04 2010-04-01

5 10 2010-01-05 2010-05-01

6 12 2010-01-06 2010-06-01

7 14 2010-01-07 2010-07-01

8 16 2010-01-08 2010-08-01

9 18 2010-01-09 2010-09-01

10 20 2010-01-10 2010-10-01

10 rows selected.

This simple and elegant writing method was first published by mikito harakiri (a Japanese by name) on asktom website (http://asktom.oracle.com), and now it has become popular in Oracle community all over the world. Before this method is found, the general method is to get rownum from a large set (table or view):

```
SELECT ROWNUM n, ROWNUM*2 n2, DATE '2010-1-1'+ROWNUM-1 dt, ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon
From all objects -- all objects is a large system view, which contains enough rows to satisfy the general sequence construction
WHERE ROWNUM<=10;
```

Here’s how to write recursively with:

```
WITH t(n,n2,dt,mon) AS (
Select 1, 2, to "date ('2010-1-1 ','yyyy-mm-dd'), to" date ('2010-1-1 ','yyyy-mm-dd') from dual --- construct the first one first
UNION ALL
Select T.N + 1 -- increment 1
, t.n2 + 2 ---- increasing by 2
, DT + 1 ---- next day
, add? Months (Mon, 1) --- next month
From T -- no connection, because no need, all data can be derived from anchor members
WHERE t.n<10
)
SELECT * FROM T;
```

Everything goes according to the rules, but it still goes wrong:

```
, add? Months (Mon, 1) --- next month
*
ERROR at line 6:
ORA-01790: expression must have same datatype as corresponding expression
```

Change to string:

```
WITH t(n,n2,dt,mon) AS (
Select 1, 2, '2010-01-01', '2010-01-01' from dual -- use string to represent date
UNION ALL
Select T.N + 1 -- increment 1
, t.n2 + 2 ---- increasing by 2
, to char (to date (t.dt, 'yyyy-mm-dd') + 1, 'yyyy-mm-dd') -- first convert to date type, then convert to string type after calculation
, to char (add ﹣ months (to ﹣ date (t.mon, 'yyyy-mm-dd'), 1), 'yyyy-mm-dd') -- calculate next month by the same method as above
FROM t
WHERE t.n<10
)
SELECT * FROM T;
```

I was surprised to see this result:

N N2 DT MON

———- ———- ———- ———-

1 2 2010-01-01 2010-01-01

2.4 2009-12-31 2010-02-01 – DT is decreasing!

3 6 2009-12-30 2010-03-01

4 8 2009-12-29 2010-04-01

5 10 2009-12-28 2010-05-01

6 12 2009-12-27 2010-06-01

7 14 2009-12-26 2010-07-01

8 16 2009-12-25 2010-08-01

9 18 2009-12-24 2010-09-01

10 20 2009-12-23 2010-10-01

10 rows selected.

This is the bug of oracel version 11.2.0.1.0, which should be corrected in later versions.

I can’t help but think of other ways to get around:

```
WITH t(n) AS (
Select 1 from dual -- Construct the first
UNION ALL
Select T.N + 1 -- only integer sequence
FROM t
WHERE t.n<10
)
SELECT n
,n*2 n2
, date '2010-1-1' + n-1 DT -- date operation in the final query
,ADD_MONTHS(DATE '2010-1-1', n-1) mon
FROM T;
```

That’s right:

N N2 DT MON

———- ———- ———– ———–

1 2 2010-01-01 2010-01-01

2 4 2010-01-02 2010-02-01

3 6 2010-01-03 2010-03-01

4 8 2010-01-04 2010-04-01

5 10 2010-01-05 2010-05-01

6 12 2010-01-06 2010-06-01

7 14 2010-01-07 2010-07-01

8 16 2010-01-08 2010-08-01

9 18 2010-01-09 2010-09-01

10 20 2010-01-10 2010-10-01

10 rows selected.

It seems that there is bug in the calculation of date. The solution is to construct an integer sequence first, and then use the integer sequence to construct a date sequence in the final query.

From a single result set connect by example:

```
SELECT ROWNUM rn,cnt
From (select count (*) CNT from EMP) --- aggregate result set with only one row
CONNECT BY ROWNUM<=cnt;
```

result:

RN CNT

———- ———-

1 14

2 14

3 14

4 14

5 14

6 14

7 14

8 14

9 14

10 14

11 14

12 14

13 14

14 14

14 rows selected.

Recursive with:

```
WITH t(n,cnt) AS (
Select 1, count (*) CNT from EMP -- Construct the first one first
UNION ALL
Select T.N + 1 -- increment 1
, t.cnt -- this CNT column is obtained from the first layer without any modification
From T -- no connection, because no connection is needed
Where T.N < t.cnt -- here we see the function of CNT, which is used to terminate traversal
)
SELECT * FROM t;
```

The results are the same as above.

Example 3:

Arrangement and combination of independent events: a cloth bag contains the same number of small balls of four colors. Randomly take four times from the cloth bag and put them back after each time. Now, what is the probability that the total number of colors in four results is equal to 3?

Traditional connect by:

```
WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
, replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
, count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
, T3 as (- --- Set T3 breaks the color combination contained in path in T2 into four lines
SELECT id,cnt,SUBSTR(PATH,rn,1) color
From T2, T -- Cartesian product, used to change one line in T2 into four lines
)
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
FROM t3
GROUP BY id,cnt
Having count (distinct color) = 3 -- each ID contains three colors
;
```

result:

PROB

———-

.5625

This example shows the skill of connect by to simulate permutation and combination. Each layer traversal represents an extraction action, because each time is completely independent. In connect by, only the extraction times (traversal layers) are limited without other conditions. Sys connect by path can string the data of all levels accessed up to now. In level = n, the arrangement and combination of the first n layers are included. You can use this query to see the result set T2 generated in the middle:

```
WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
, replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
, count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
SELECT * FROM t2;
```

ID PATH CNT

———- ———- ———-

1 1111 256

2 1112 256

3 1113 256

4 1114 256

5 1121 256

6 1122 256

7 1123 256

8 1124 256

9 1131 256

10 1132 256

11 1133 256

… (other results are omitted)

256 rows selected.

It can be seen that the path column already contains all the possible results of four extractions, each of which is given a unique number ID.

If you are curious, you can see the next result set T3:

```
WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
, replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
, count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
, T3 as (- --- Set T3 breaks the color combination contained in path in T2 into four lines
SELECT id,cnt,SUBSTR(PATH,rn,1) color
From T2, T -- Cartesian product, used to change one line in T2 into four lines
)
SELECT * FROM t3;
```

ID CNT COLO

———- ———- —-

1 256 1

1 256 1

1 256 1

1 256 1

2 256 1

2 256 1

2 256 1

2 256 2

3 256 1

3 256 1

3 256 1

3 256 3

4 256 1

4 256 1

4 256 1

4 256 4

… (other results are omitted)

1024 rows selected.

You can see that each row in the T2 set has been split into four rows for subsequent aggregation operations.

Finally, let’s look at the main query of calculating probability:

```
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
FROM t3
GROUP BY id,cnt
HAVING COUNT(DISTINCT color)=3;
```

Count (distinct color) can calculate the number of colors that are not repeated in each ID, and filter those IDS whose number is not 3 in having.

Group by ID, CNT means to group by ID. Because the CNTs of all rows are the same (all equal to 256), we will not change the result of grouping by adding it in the group. The purpose of adding CNT is to reference in the query.

The last two consecutive levels of count function means to aggregate the grouping results into one row and calculate the number of rows of ID satisfying the condition. Divide by CNT to get the probability we want.

This example is an example of unconditional traversal on the result set of multiple rows. As mentioned above, special care should be taken, because there is no relationship between the upper level and the lower level. As the number of layers increases, the amount of data increases significantly.

Recursive with:

```
WITH T AS (
Select rownum RN -- construct a result set of 1,2,3,4 first
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 (distinct_colors, LVL) as (- -- two columns: all non repeating colors, hierarchy
Select '\' ||rn, 1 -- the first layer is the most basic table of four colors
FROM t
UNION ALL
Select case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has not appeared before
Then T2. Distinct|colors| \ '\' |t.rn --- spell
Else t2.distinct_colors
END
, t2.lvl + 1 -- increasing number of layers
FROM t, t2
Where T2. LVL < 4 -- the condition of recursive exit: the number of times reaches the limit
)
Select count (case when length (distinct colors) - length (replace (distinct colors, '\')) = 3 then 1 end) --- three slashes appear
/COUNT(*)
FROM t2
Where LVL = 4 -- similar to connect by, we only need to observe the data of the last layer, which already contains the colors of all layers
;
```

In the recursive with subquery T2, we see that it uses a case expression to splice colors that have not appeared before into distinct_colors. This case is the beauty of recursion with. Using sys connect by path can’t achieve conditional splicing.

At last, we use a skill to convert the number of colors into the number of slashes, because when we construct data, each color is preceded by a slash. In order to find out the number of times a character appears in a string, we use the following method:

First, find the total length of the string;

Use the replace function to remove the character from the string, and then find the length again;

The difference between the two lengths is the number of characters removed.

Case function sets the mark that meets the condition to 1, otherwise it is null. Then another count function can calculate the number of rows that meet the condition, because null is not included in count.

The nesting of count and case is also a common skill in aggregation.

For the calculation of this color number, we can also make conditional accumulation in the process of recursion, so that we can directly use:

```
WITH T AS (
Select rownum RN -- construct a result set of 1,2,3,4 first
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 (distinct colors, LVL, distinct colors, CNT) as (- -- two columns: all unrepeated colors, levels, number of unrepeated colors
Select '\' |rn, 1,1 -- the first layer is the most basic table of four colors
FROM t
UNION ALL
Select case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has not appeared before
Then T2. Distinct|colors| \ '\' |t.rn --- spell
Else t2.distinct_colors
END
, t2.lvl + 1 -- increasing number of layers
, case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has never appeared
Then t2.distinct? Colors? CNT + 1 --- color number accumulation
Else t2.distinct ﹣ colors ﹣ CNT ---- the number of colors has not changed
END
FROM t, t2
Where T2. LVL < 4 -- the condition of recursive exit: the number of times reaches the limit
)
Select count (case when distinct? Colors? CNT = 3 then 1 end) -- three slashes appear
/COUNT(*)
FROM t2
Where LVL = 4 -- similar to connect by, we only need to observe the data of the last layer, which already contains the colors of all layers
;
```

Example 4:

Construct a second order sequence of equal difference numbers: the difference of the items in this sequence is an sequence of equal difference numbers

For example: 1,3,6,10,15,21

Use connect by:

```
SELECT LEVEL, SUM(LEVEL) OVER(ORDER BY LEVEL) n
FROM DUAL
CONNECT BY LEVEL<=10;
```

result:

LEVEL N

———- ———-

1 1

2 3

3 6

4 10

5 15

6 21

7 28

8 36

9 45

10 55

10 rows selected.

Because there is only one path, it is easy to use the analysis function sum.

Recursive with:

```
WITH t(lvl,n) AS (
Select 1,1 from dual -- Construct the first one first
UNION ALL
Select t.lvl + 1, t.lvl + 1 + T.N ---- the increase of N itself is an equal difference sequence, i.e. the new t.lvl
From T -- no connection, because no connection is needed
Where t.lvl < 10 ---- stop when you find 10
)
SELECT * FROM T;
```

result:

LVL N

———- ———-

1 1

2 3

3 6

4 10

5 15

6 21

7 28

8 36

9 45

10 55

10 rows selected.

Example 5:

Construction of Fibonacci series: it refers to such a series. From the third term, each term is equal to the sum of the first two terms.

1,1,2,3,5,8,13,21,……

The traditional connect by method can’t work out, but the model supported by 10g or above can be easily constructed:

```
SELECT rn,n
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=10)
MODEL RETURN UPDATED ROWS
DIMENSION BY (rn)
MEASURES (1 n)
RULES (
N [any] order by RN = decode (CV (RN), 1,1,2,1, n [cv() - 2] + n [cv() - 1]) -- Construct the first two with decode, and assign the rest as the sum of the last two
)
/
```

RN N

———- ———-

1 1

2 1

3 2

4 3

5 5

6 8

7 13

8 21

9 34

10 55

10 rows selected.

Write recursively with:

```
WITH t(n,last_n,cnt) AS (
Select 1,0,1 from dual -- Construct the first one first
UNION ALL
Select T.N + t.last_n, T.N, t.cnt + 1 -- sum of the first two
From T -- no connection, because no connection is needed
Where t.cnt < 10 ---- stop when you find 10
)
SELECT n FROM T;
```

N

———-

1

1

2

3

5

8

13

21

34

55

10 rows selected.

Example 6:

Arrangement and combination:

All combination C (3,5) of 3 out of 5:

Connect by:

```
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
Connect by RN < prior RN and level < = 3 -- force to sort in descending order, so that other combinations with the same order but different order are excluded
;
```

XMLPATH

————–

,5,4,3

,5,4,2

,5,4,1

,5,3,2

,5,3,1

,5,2,1

,4,3,2

,4,3,1

,4,2,1

,3,2,1

Recursive with:

```
WITH t AS (
SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
)
, T2 (RN, xmlpath, LVL) as (- --- three columns: current node value, path, number of layers
Select RN, '|||||||||||||||||||||||||||||
UNION ALL
Select t.rn, t2.xmlpath|, '| t.rn, T2. LVL + 1 -- the current node is spliced into the access path, and the number of layers is increased
FROM t2, t
WHERE t2.rn<t.rn AND t2.lvl<3
)
SELECT xmlpath FROM t2 WHERE lvl=3;
```

XMLPATH

———–

,1,2,3

,1,2,4

,1,2,5

,1,3,4

,1,3,5

,1,4,5

,2,3,4

,2,3,5

,2,4,5

,3,4,5

10 rows selected.

For example, P (3,5) can be written as follows:

```
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY NOCYCLE rn<>PRIOR rn AND LEVEL<=3;
```

XMLPATH

———-

,1,2,3

,1,2,4

,1,2,5

,1,3,2

,1,3,4

,1,3,5

,1,4,2

,1,4,3

,1,4,5

,1,5,2

,1,5,3

,1,5,4

,2,1,3

,2,1,4

… (other results are omitted)

60 rows selected.

Compared with the combination writing method just now, RN < prior RN becomes nocycle RN < > prior RN, which means that as long as RN does not appear, we need all the permutations, not just the descending ones. Note the nocycle in it. This one is only available on 10g.

What happens if you don’t write this nocycle?

```
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY rn<>PRIOR rn AND LEVEL<=3;
```

ERROR:

ORA-01436: CONNECT BY loop in user data

As you can see, this nocycle is very important. Oracle does not allow loops in the traversal order.

In recursive with, nocycle is written as follows:

```
WITH t AS (
SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
)
, T2 (RN, xmlpath, LVL) as (- --- three columns: current node value, path, number of layers
Select RN, '|||||||||||||||||||||||||||||
UNION ALL
Select t.rn, t2.xmlpath|, '| t.rn, T2. LVL + 1 -- the current node is spliced into the access path, and the number of layers is increased
FROM t2, t
WHERE t2.rn<>t.rn AND t2.lvl<3
)
Cycle RN set cycle flag to 'y' default 'n' --- this cycle flag is a pseudo column name and value defined by itself, which can play the same role as connect by iscycle
SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';
```

result:

XMLPA

SQL> with emp_data(ename,empno,mgr,l)

2 as

3 (select ename, empno, mgr, 1 lvl from emp where mgr is null

4 union all

5 select emp.ename, emp.empno, emp.mgr, ed.l+1

6 from emp, emp_data ed

7 where emp.mgr = ed.empno

8 )

9 SEARCH DEPTH FIRST BY ename SET order_by

10 select l,

11 lpad(‘*’ ,2*l, ‘*’)||ename nm

12 from emp_data

13 order by order_by

14 /

L NM

—- —————

1 **KING

2 ****BLAKE

3 ******ALLEN

3 ******JAMES

3 ******MARTIN

3 ******TURNER

3 ******WARD

2 ****CLARK

3 ******MILLER

2 ****JONES

3 ******FORD

4 ********SMITH

3 ******SCOTT

4 ********ADAMS

14 rows selected.

I don’t know how it really works. In my imagination, I can play many new tricks more than the original sys connect by path, such as accumulating by path, more flexible pruning conditions,

With subqueries, also known as CTE (common table expression), are part of the ANSI SQL-99 standard. Oracle introduces with subquery from 9i, which is called subquery factoring.

With subqueries act like inline views. The definition of the inline view is written after the SQL from and can only be referenced once. The with subquery needs to be defined before the reference. Once it is defined, it can be referenced repeatedly by name in the subsequent part of the whole query. From this point of view, it is similar to a temporary table.

Starting with version 11gr2, Oracle supports recursive with, which allows for self reference in the definition of a with subquery. This is nothing new. Other databases such as DB2, Firebird, Microsoft SQL server and PostgreSQL support this feature before oracle. However, for Oracle users, this recursive feature is still very desirable, and many new functions that can’t be done in the past or are very difficult to be achieved can be easily realized by using it. In this chapter, we will explore this exciting new feature and compare it with previous implementation methods (mainly connect by hierarchical query).

Let’s first look at the syntax of this recursive with subquery:

WITH

① query_name ([c_alias [, c_alias]…])

② AS (subquery)

③ [search_clause]

④ [cycle_clause]

⑤ [,query_name ([c_alias [, c_alias]…]) AS (subquery) [search_clause] [cycle_clause]]…

① This is the name of the subquery. Unlike in the past, all the column names of the subquery must be written in parentheses.

② Subquery after as is the query statement, and the recursive part is written here.

③ Traversal order clause, which can specify depth first or breadth first traversal order.

④ Loop clause, used to abort the loop that occurs in traversal.

⑤ If there are other recursive subqueries, the definition is the same as above.

Subquery consists of two members: anchor member and recursive member. They must be combined with union all, and anchor member must be written in front of recursive member.

Anchor member is used to locate the entry of recursion. Anchor member is a select statement, and it cannot contain its own name. This is equivalent to start with in connect by query. The typical writing method is:

Select… From table to traverse where… (start condition)

A recursive member is also a select statement, which is used to define the relationship between the superior and the subordinate. It must contain its own name (i.e. query [name]) and can only be referenced once. Recursion is embodied in the reference to itself. A typical way is to make a connection between query_name and other tables (generally speaking, the tables you want to traverse). The connection conditions indicate the relationship between the upper and lower levels. It must be noted that not all data so far in this query name is visible, only the latest layer of data added in the last recursion. A reference to the query_name column is equivalent to the prior operator in connect by. When you can’t find the lower level that meets the conditions, the traversal will stop; if you have other recursion exit conditions, you can also write them together in where. When the where doesn’t meet the conditions, the traversal will stop, which is the pruning operation when traversing trees and graphs. The earlier it stops, the more efficient it is.

This recursive member is the place where programmers play their creativity. In the past, things that could not be done in connect by, such as summation along the path, quadrature and other operations, are now easy. Sys connect by path is also easy to implement with string concatenation ‘|’.

Search clause and cycle clause are shown in the following examples.

Let’s take a look at an example of the usage of a recursive with subquery.

Example 1:

Let’s take a simple example to find the parent-child relationship from the EMP table of Scott / tiger

Traditional connect by:

```
SELECT empno
,ename
,job
,mgr
,deptno
,level
,SYS_CONNECT_BY_PATH(ename,'\') AS path
,CONNECT_BY_ROOT(ename) AS top_manager
FROM EMP
Start with Mgr is null -- Mgr column is empty, indicating that there is no superior, and the employee is already at the highest level. This is the starting point of hierarchical query
CONNECT BY PRIOR empno= mgr;
```

New recursive with method:

```
With t (empno, ename, job, Mgr, deptno, the_level, path, top_manager) as (- -- structure must be written out
Select empno, ename, job, Mgr, deptno -- write the anchor query first, and use the condition of start with
, 1 as the "level
, '\' ||ename -- the first section of the path
Original connect by root
FROM EMP
Where Mgr is null -- original start with condition
Union all -- the following is the recursive part
Select e.empno, e.ename, e.job, e.mgr, e.deptno -- a new layer of data to be added, from the EMP table to be traversed
, 1 + T.The "level" -- recursion level, add 1 on the original basis. This is equivalent to the level pseudo column in the connect by query
, t.path| \ '|e.ename
Directly inherit the original data, because there is only one root node in each path
From t, EMP e -- a typical writing method that makes a connection between the subquery itself and the table to be traversed
Where t.empno = e.mgr -- original connect by condition
）---- with definition ends
SELECT * FROM T
;
```

Query results:

EMPNO ENAME JOB MGR DEPTNO THE_LEVEL PATH TOP_MANAGE

—— ———- ——— —— ——- ———- ————————– ———-

7839 KING PRESIDENT 10 1 \KING KING

7566 JONES MANAGER 7839 20 2 \KING\JONES KING

7698 BLAKE MANAGER 7839 30 2 \KING\BLAKE KING

7782 CLARK MANAGER 7839 10 2 \KING\CLARK KING

7499 ALLEN SALESMAN 7698 30 3 \KING\BLAKE\ALLEN KING

7521 WARD SALESMAN 7698 30 3 \KING\BLAKE\WARD KING

7654 MARTIN SALESMAN 7698 30 3 \KING\BLAKE\MARTIN KING

7788 SCOTT ANALYST 7566 20 3 \KING\JONES\SCOTT KING

7844 TURNER SALESMAN 7698 30 3 \KING\BLAKE\TURNER KING

7900 JAMES CLERK 7698 30 3 \KING\BLAKE\JAMES KING

7902 FORD ANALYST 7566 20 3 \KING\JONES\FORD KING

7934 MILLER CLERK 7782 10 3 \KING\CLARK\MILLER KING

7369 SMITH CLERK 7902 20 4 \KING\JONES\FORD\SMITH KING

7876 ADAMS CLERK 7788 20 4 \KING\JONES\SCOTT\ADAMS KING

14 rows selected.

From the result set’s the level and path columns, you can clearly see how the data is stacked layer by layer.

Example 2:

Construct the sequence of equal difference numbers:

Connect by:

This is a very special usage, because there is no hierarchy, only the termination condition of traversal. Like this kind of connect by, I strongly recommend running on a result set with only one row (such as from dual, such as a sub query after aggregation). Running on a multi row set is difficult to control and requires a clear mind.

```
(all of the following rownum can be changed to level, with the same effect):
SELECT ROWNUM n
,ROWNUM*2 n2
,DATE '2010-1-1'+ROWNUM-1 dt
,ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon
FROM DUAL
CONNECT BY ROWNUM<=10;
```

result:

N N2 DT MON

———- ———- ———– ———–

1 2 2010-01-01 2010-01-01

2 4 2010-01-02 2010-02-01

3 6 2010-01-03 2010-03-01

4 8 2010-01-04 2010-04-01

5 10 2010-01-05 2010-05-01

6 12 2010-01-06 2010-06-01

7 14 2010-01-07 2010-07-01

8 16 2010-01-08 2010-08-01

9 18 2010-01-09 2010-09-01

10 20 2010-01-10 2010-10-01

10 rows selected.

This simple and elegant writing method was first published by mikito harakiri (a Japanese by name) on asktom website (http://asktom.oracle.com), and now it has become popular in Oracle community all over the world. Before this method is found, the general method is to get rownum from a large set (table or view):

```
SELECT ROWNUM n, ROWNUM*2 n2, DATE '2010-1-1'+ROWNUM-1 dt, ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon
From all objects -- all objects is a large system view, which contains enough rows to satisfy the general sequence construction
WHERE ROWNUM<=10;
```

Here’s how to write recursively with:

```
WITH t(n,n2,dt,mon) AS (
Select 1, 2, to "date ('2010-1-1 ','yyyy-mm-dd'), to" date ('2010-1-1 ','yyyy-mm-dd') from dual --- construct the first one first
UNION ALL
Select T.N + 1 -- increment 1
, t.n2 + 2 ---- increasing by 2
, DT + 1 ---- next day
, add? Months (Mon, 1) --- next month
From T -- no connection, because no need, all data can be derived from anchor members
WHERE t.n<10
)
SELECT * FROM T;
Everything goes according to the rules, but it still goes wrong:
, add? Months (Mon, 1) --- next month
*
ERROR at line 6:
ORA-01790: expression must have same datatype as corresponding expression
```

Change to string:

```
WITH t(n,n2,dt,mon) AS (
Select 1, 2, '2010-01-01', '2010-01-01' from dual -- use string to represent date
UNION ALL
Select T.N + 1 -- increment 1
, t.n2 + 2 ---- increasing by 2
, to char (to date (t.dt, 'yyyy-mm-dd') + 1, 'yyyy-mm-dd') -- first convert to date type, then convert to string type after calculation
, to char (add ﹣ months (to ﹣ date (t.mon, 'yyyy-mm-dd'), 1), 'yyyy-mm-dd') -- calculate next month by the same method as above
FROM t
WHERE t.n<10
)
SELECT * FROM T;
```

I was surprised to see this result:

N N2 DT MON

———- ———- ———- ———-

1 2 2010-01-01 2010-01-01

2.4 2009-12-31 2010-02-01 – DT is decreasing!

3 6 2009-12-30 2010-03-01

4 8 2009-12-29 2010-04-01

5 10 2009-12-28 2010-05-01

6 12 2009-12-27 2010-06-01

7 14 2009-12-26 2010-07-01

8 16 2009-12-25 2010-08-01

9 18 2009-12-24 2010-09-01

10 20 2009-12-23 2010-10-01

10 rows selected.

This is the bug of oracel version 11.2.0.1.0, which should be corrected in later versions.

I can’t help but think of other ways to get around:

```
WITH t(n) AS (
Select 1 from dual -- Construct the first
UNION ALL
Select T.N + 1 -- only integer sequence
FROM t
WHERE t.n<10
)
SELECT n
,n*2 n2
, date '2010-1-1' + n-1 DT -- date operation in the final query
,ADD_MONTHS(DATE '2010-1-1', n-1) mon
FROM T;
```

That’s right:

———- ———- ———– ———–

1 2 2010-01-01 2010-01-01

2 4 2010-01-02 2010-02-01

3 6 2010-01-03 2010-03-01

4 8 2010-01-04 2010-04-01

5 10 2010-01-05 2010-05-01

6 12 2010-01-06 2010-06-01

7 14 2010-01-07 2010-07-01

8 16 2010-01-08 2010-08-01

9 18 2010-01-09 2010-09-01

10 20 2010-01-10 2010-10-01

10 rows selected.

It seems that there is bug in the calculation of date. The solution is to construct an integer sequence first, and then use the integer sequence to construct a date sequence in the final query.

From a single result set connect by example:

```
SELECT ROWNUM rn,cnt
From (select count (*) CNT from EMP) --- aggregate result set with only one row
CONNECT BY ROWNUM<=cnt;
```

result:

RN CNT

———- ———-

1 14

2 14

3 14

4 14

5 14

6 14

7 14

8 14

9 14

10 14

11 14

12 14

13 14

14 14

14 rows selected.

Recursive with:

```
WITH t(n,cnt) AS (
Select 1, count (*) CNT from EMP -- Construct the first one first
UNION ALL
Select T.N + 1 -- increment 1
, t.cnt -- this CNT column is obtained from the first layer without any modification
From T -- no connection, because no connection is needed
Where T.N < t.cnt -- here we see the function of CNT, which is used to terminate traversal
)
SELECT * FROM t;
```

The results are the same as above.

Example 3:

Arrangement and combination of independent events: a cloth bag contains the same number of small balls of four colors. Randomly take four times from the cloth bag and put them back after each time. Now, what is the probability that the total number of colors in four results is equal to 3?

Traditional connect by:

```
WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
, replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
, count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
, T3 as (- --- Set T3 breaks the color combination contained in path in T2 into four lines
SELECT id,cnt,SUBSTR(PATH,rn,1) color
From T2, T -- Cartesian product, used to change one line in T2 into four lines
)
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
FROM t3
GROUP BY id,cnt
Having count (distinct color) = 3 -- each ID contains three colors
;
```

result:

PROB

———-

.5625

This example shows the skill of connect by to simulate permutation and combination. Each layer traversal represents an extraction action, because each time is completely independent. In connect by, only the extraction times (traversal layers) are limited without other conditions. Sys connect by path can string the data of all levels accessed up to now. In level = n, the arrangement and combination of the first n layers are included. You can use this query to see the result set T2 generated in the middle:

```
WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
, replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
, count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
SELECT * FROM t2;
```

ID PATH CNT

———- ———- ———-

1 1111 256

2 1112 256

3 1113 256

4 1114 256

5 1121 256

6 1122 256

7 1123 256

8 1124 256

9 1131 256

10 1132 256

11 1133 256

… (other results are omitted)

256 rows selected.

It can be seen that the path column already contains all the possible results of four extractions, each of which is given a unique number ID.

If you are curious, you can see the next result set T3:

```
WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
, replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
, count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
, T3 as (- --- Set T3 breaks the color combination contained in path in T2 into four lines
SELECT id,cnt,SUBSTR(PATH,rn,1) color
From T2, T -- Cartesian product, used to change one line in T2 into four lines
)
SELECT * FROM t3;
```

ID CNT COLO

———- ———- —-

1 256 1

1 256 1

1 256 1

1 256 1

2 256 1

2 256 1

2 256 1

2 256 2

3 256 1

3 256 1

3 256 1

3 256 3

4 256 1

4 256 1

4 256 1

4 256 4

… (other results are omitted)

1024 rows selected.

You can see that each row in the T2 set has been split into four rows for subsequent aggregation operations.

Finally, let’s look at the main query of calculating probability:

```
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
FROM t3
GROUP BY id,cnt
HAVING COUNT(DISTINCT color)=3;
```

Count (distinct color) can calculate the number of colors that are not repeated in each ID, and filter those IDS whose number is not 3 in having.

Group by ID, CNT means to group by ID. Because the CNTs of all rows are the same (all equal to 256), we will not change the result of grouping by adding it in the group. The purpose of adding CNT is to reference in the query.

The last two consecutive levels of count function means to aggregate the grouping results into one row and calculate the number of rows of ID satisfying the condition. Divide by CNT to get the probability we want.

This example is an example of unconditional traversal on the result set of multiple rows. As mentioned above, special care should be taken, because there is no relationship between the upper level and the lower level. As the number of layers increases, the amount of data increases significantly.

Recursive with:

```
WITH T AS (
Select rownum RN -- construct a result set of 1,2,3,4 first
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 (distinct_colors, LVL) as (- -- two columns: all non repeating colors, hierarchy
Select '\' ||rn, 1 -- the first layer is the most basic table of four colors
FROM t
UNION ALL
Select case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has not appeared before
Then T2. Distinct|colors| \ '\' |t.rn --- spell
Else t2.distinct_colors
END
, t2.lvl + 1 -- increasing number of layers
FROM t, t2
Where T2. LVL < 4 -- the condition of recursive exit: the number of times reaches the limit
)
Select count (case when length (distinct colors) - length (replace (distinct colors, '\')) = 3 then 1 end) --- three slashes appear
/COUNT(*)
FROM t2
Where LVL = 4 -- similar to connect by, we only need to observe the data of the last layer, which already contains the colors of all layers
;
```

In the recursive with subquery T2, we see that it uses a case expression to splice colors that have not appeared before into distinct_colors. This case is the beauty of recursion with. Using sys connect by path can’t achieve conditional splicing.

At last, we use a skill to convert the number of colors into the number of slashes, because when we construct data, each color is preceded by a slash. In order to find out the number of times a character appears in a string, we use the following method:

First, find the total length of the string;

Use the replace function to remove the character from the string, and then find the length again;

The difference between the two lengths is the number of characters removed.

Case function sets the mark that meets the condition to 1, otherwise it is null. Then another count function can calculate the number of rows that meet the condition, because null is not included in count.

The nesting of count and case is also a common skill in aggregation.

For the calculation of this color number, we can also make conditional accumulation in the process of recursion, so that we can directly use:

```
WITH T AS (
Select rownum RN -- construct a result set of 1,2,3,4 first
FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 (distinct colors, LVL, distinct colors, CNT) as (- -- two columns: all unrepeated colors, levels, number of unrepeated colors
Select '\' |rn, 1,1 -- the first layer is the most basic table of four colors
FROM t
UNION ALL
Select case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has not appeared before
Then T2. Distinct|colors| \ '\' |t.rn --- spell
Else t2.distinct_colors
END
, t2.lvl + 1 -- increasing number of layers
, case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has never appeared
Then t2.distinct? Colors? CNT + 1 --- color number accumulation
Else t2.distinct ﹣ colors ﹣ CNT ---- the number of colors has not changed
END
FROM t, t2
Where T2. LVL < 4 -- the condition of recursive exit: the number of times reaches the limit
)
Select count (case when distinct? Colors? CNT = 3 then 1 end) -- three slashes appear
/COUNT(*)
FROM t2
Where LVL = 4 -- similar to connect by, we only need to observe the data of the last layer, which already contains the colors of all layers
;
```

Example 4:

Construct a second order sequence of equal difference numbers: the difference of the items in this sequence is an sequence of equal difference numbers

For example: 1,3,6,10,15,21

Use connect by:

```
SELECT LEVEL, SUM(LEVEL) OVER(ORDER BY LEVEL) n
FROM DUAL
CONNECT BY LEVEL<=10;
```

result:

LEVEL N

———- ———-

1 1

2 3

3 6

4 10

5 15

6 21

7 28

8 36

9 45

10 55

10 rows selected.

Because there is only one path, it is easy to use the analysis function sum.

Recursive with:

```
WITH t(lvl,n) AS (
Select 1,1 from dual -- Construct the first one first
UNION ALL
Select t.lvl + 1, t.lvl + 1 + T.N ---- the increase of N itself is an equal difference sequence, i.e. the new t.lvl
From T -- no connection, because no connection is needed
Where t.lvl < 10 ---- stop when you find 10
)
SELECT * FROM T;
```

result:

LVL N

———- ———-

1 1

2 3

3 6

4 10

5 15

6 21

7 28

8 36

9 45

10 55

10 rows selected.

Example 5:

Construction of Fibonacci series: it refers to such a series. From the third term, each term is equal to the sum of the first two terms.

1,1,2,3,5,8,13,21,……

The traditional connect by method can’t work out, but the model supported by 10g or above can be easily constructed:

```
SELECT rn,n
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=10)
MODEL RETURN UPDATED ROWS
DIMENSION BY (rn)
MEASURES (1 n)
RULES (
N [any] order by RN = decode (CV (RN), 1,1,2,1, n [cv() - 2] + n [cv() - 1]) -- Construct the first two with decode, and assign the rest as the sum of the last two
)
```

/

RN N

———- ———-

1 1

2 1

3 2

4 3

5 5

6 8

7 13

8 21

9 34

10 55

10 rows selected.

Write recursively with:

```
WITH t(n,last_n,cnt) AS (
Select 1,0,1 from dual -- Construct the first one first
UNION ALL
Select T.N + t.last_n, T.N, t.cnt + 1 -- sum of the first two
From T -- no connection, because no connection is needed
Where t.cnt < 10 ---- stop when you find 10
)
SELECT n FROM T;
```

N

———-

1

1

2

3

5

8

13

21

34

55

10 rows selected.

Example 6:

Arrangement and combination:

All combination C (3,5) of 3 out of 5:

Connect by:

```
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
Connect by RN < prior RN and level < = 3 -- force to sort in descending order, so that other combinations with the same order but different order are excluded
;
```

XMLPATH

————–

,5,4,3

,5,4,2

,5,4,1

,5,3,2

,5,3,1

,5,2,1

,4,3,2

,4,3,1

,4,2,1

,3,2,1

Recursive with:

```
WITH t AS (
SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
)
, T2 (RN, xmlpath, LVL) as (- --- three columns: current node value, path, number of layers
Select RN, '|||||||||||||||||||||||||||||
UNION ALL
Select t.rn, t2.xmlpath|, '| t.rn, T2. LVL + 1 -- the current node is spliced into the access path, and the number of layers is increased
FROM t2, t
WHERE t2.rn<t.rn AND t2.lvl<3
)
SELECT xmlpath FROM t2 WHERE lvl=3;
```

XMLPATH

———–

,1,2,3

,1,2,4

,1,2,5

,1,3,4

,1,3,5

,1,4,5

,2,3,4

,2,3,5

,2,4,5

,3,4,5

10 rows selected.

For example, P (3,5) can be written as follows:

```
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY NOCYCLE rn<>PRIOR rn AND LEVEL<=3;
```

XMLPATH

———-

,1,2,3

,1,2,4

,1,2,5

,1,3,2

,1,3,4

,1,3,5

,1,4,2

,1,4,3

,1,4,5

,1,5,2

,1,5,3

,1,5,4

,2,1,3

,2,1,4

… (other results are omitted)

60 rows selected.

Compared with the combination writing method just now, RN < prior RN becomes nocycle RN < > prior RN, which means that as long as RN does not appear, we need all the permutations, not just the descending ones. Note the nocycle in it. This one is only available on 10g.

What happens if you don’t write this nocycle?

```
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY rn<>PRIOR rn AND LEVEL<=3;
```

ERROR:

ORA-01436: CONNECT BY loop in user data

As you can see, this nocycle is very important. Oracle does not allow loops in the traversal order.

In recursive with, nocycle is written as follows:

```
WITH t AS (
SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
)
, T2 (RN, xmlpath, LVL) as (- --- three columns: current node value, path, number of layers
Select RN, '|||||||||||||||||||||||||||||
UNION ALL
Select t.rn, t2.xmlpath|, '| t.rn, T2. LVL + 1 -- the current node is spliced into the access path, and the number of layers is increased
FROM t2, t
WHERE t2.rn<>t.rn AND t2.lvl<3
)
Cycle RN set cycle flag to 'y' default 'n' --- this cycle flag is a pseudo column name and value defined by itself, which can play the same role as connect by iscycle
SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';
```

result:

XMLPA

The above is the recursive with subquery method of Oracle 11gr2 introduced by Xiaobian to you. I hope it can help you. If you have any questions, please leave me a message and Xiaobian will reply to you in time. Thank you very much for your support of the developepaer website!