Oracle SQL uses with recursive analysis (for example) and oracle Recursion

Oracle SQL uses with recursive analysis (for example) and oracle Recursion

The WITH recursion provided by Oracle is not just a repetition of tree-like queries. In fact, recursive WITH statements can provide more flexible tree-like Query functions.

The following describes the CYCLE statement. This statement not only supports CONNECT_BY_ISCYCLE and connec by nocycle for tree query, but also provides more powerful functions.

SQL> SELECT * FROM V $ VERSION;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

SQL> CREATE TABLE T_TREE (

2 id number primary key,

3 FATHER_ID NUMBER,

4 NAME VARCHAR2 (30 ));

The table has been created.

SQL> INSERT INTO T_TREE VALUES (1, 0, 'A ');

One row has been created.

SQL> INSERT INTO T_TREE VALUES (2, 1, 'bc ');

One row has been created.

SQL> INSERT INTO T_TREE VALUES (3, 1, 'de ');

One row has been created.

SQL> INSERT INTO T_TREE VALUES (4, 1, 'fg ');

One row has been created.

SQL> INSERT INTO T_TREE VALUES (5, 2, 'hij ');

One row has been created.

SQL> INSERT INTO T_TREE VALUES (6, 4, 'klm ');

One row has been created.

SQL> INSERT INTO T_TREE VALUES (7, 6, 'nopq ');

One row has been created.

SQL> INSERT INTO T_TREE VALUES (8, 5, 'rstu ');

One row has been created.

SQL> COMMIT;

Submitted.

SQL> SELECT * FROM T_TREE;

ID FATHER_ID NAME

--------------------------------------------------

1 0

2 1 BC

3 1 DE

4 1 FG

5 2 HIJ

6 4 KLM

7 6 NOPQ

8 5 RSTU

Eight rows have been selected.

Modify record 1 to point its FATHER_ID to itself, and then execute recursive WITH query:

SQL> UPDATE T_TREE SET FATHER_ID = 1 WHERE ID = 1;

1 row updated.

SQL> SELECT * FROM T_TREE;

ID FATHER_ID NAME

--------------------------------------------------

1 1

2 1 BC

3 1 DE

4 1 FG

5 2 HIJ

6 4 KLM

7 6 NOPQ

8 5 RSTU

Eight rows have been selected.

SQL> WITH A (ID, FATHER_ID, NAME)

2 (select id, FATHER_ID, NAME

3 FROM T_TREE

4 where id = 1

5 UNION ALL

6 select B. ID, B. FATHER_ID, B. NAME

7 from a, T_TREE B

8 where a. ID = B. FATHER_ID)

9 SELECT * from;

ERROR:

ORA-32044: loops detected when performing recursive WITH queries

Unselected row

If an error is returned for Recursive WITH queries that do not use the CYCLE statement, CYCLE is used to avoid errors caused by loops:

SQL> WITH A (ID, FATHER_ID, NAME)

2 (select id, FATHER_ID, NAME

3 FROM T_TREE

4 where id = 1

5 UNION ALL

6 select B. ID, B. FATHER_ID, B. NAME

7 from a, T_TREE B

8 where a. ID = B. FATHER_ID)

9 cycle id set DUP_ID TO 'y' DEFAULT 'n'

10 SELECT * from;

ID FATHER_ID NAME D

---------------------------------------------------

1 1 A N

1 1 A Y

2 1 BC N

3 1 DE N

4 1 FG N

5 2 HIJ N

6 4 KLM N

7 6 NOPQ N

8 5 RSTU N

Nine rows have been selected.

Note that this result is inconsistent with the result obtained BY using connect by nocycle. The record with ID 1 will appear twice:

SQL> SELECT A. *, CONNECT_BY_ISCYCLE FLAG

2 FROM T_TREE

3 start with id = 1

4 connect by nocycle prior id = FATHER_ID;

ID FATHER_ID NAME FLAG

------------------------------------------------------------

1 1 A 1

2 1 BC 0

5 2 HIJ 0

8 5 RSTU 0

3 1 DE 0

4 1 FG 0

6 4 KLM 0

7 6 NOPQ 0

Eight rows have been selected.

However, this does not have any impact. By filtering the result set, you can easily obtain the result obtained by NOCYCLE.

In addition to the same ID and FATHER_ID, if the tree query forms a loop, you can also use the CYCLE statement to solve the problem:

SQL> UPDATE T_TREE SET FATHER_ID = 7 WHERE ID = 1;

1 row updated.

SQL> WITH A (ID, FATHER_ID, NAME)

2 (select id, FATHER_ID, NAME

3 FROM T_TREE

4 where id = 1

5 UNION ALL

6 select B. ID, B. FATHER_ID, B. NAME

7 from a, T_TREE B

8 where a. ID = B. FATHER_ID)

9 cycle id set DUP_ID TO 'y' DEFAULT 'n'

10 SELECT * from;

ID FATHER_ID NAME D

---------------------------------------------------

1 7 A N

2 1 BC N

3 1 DE N

4 1 FG N

5 2 HIJ N

6 4 KLM N

7 6 NOPQ N

8 5 RSTU N

1 7 A Y

Nine rows have been selected.

Moreover, the CYCLE statement can not only judge whether the current connect by loop exists, but also determine whether duplicate data exists in any column of the same branch:

SQL> UPDATE T_TREE SET FATHER_ID = 0 WHERE ID = 1;

1 row updated.

SQL> INSERT INTO T_TREE VALUES (9, 2, 'bc ');

One row has been created.

SQL> SELECT * FROM T_TREE;

ID FATHER_ID NAME

--------------------------------------------------

9 2 BC

1 0

2 1 BC

3 1 DE

4 1 FG

5 2 HIJ

6 4 KLM

7 6 NOPQ

8 5 RSTU

Nine rows have been selected.

SQL> WITH A (ID, FATHER_ID, NAME)

2 (select id, FATHER_ID, NAME

3 FROM T_TREE

4 where id = 1

5 UNION ALL

6 select B. ID, B. FATHER_ID, B. NAME

7 from a, T_TREE B

8 where a. ID = B. FATHER_ID)

9 cycle name set DUP_NAME TO 'y' DEFAULT 'n'

10 SELECT * from;

ID FATHER_ID NAME D

---------------------------------------------------

1 0 A N

2 1 BC N

3 1 DE N

4 1 FG N

9 2 BC Y

5 2 HIJ N

6 4 KLM N

7 6 NOPQ N

8 5 RSTU N

Nine rows have been selected.

SQL> UPDATE T_TREE SET FATHER_ID = 5 WHERE ID = 9;

1 row updated.

SQL> WITH A (ID, FATHER_ID, NAME)

2 (select id, FATHER_ID, NAME

3 FROM T_TREE

4 where id = 1

5 UNION ALL

6 select B. ID, B. FATHER_ID, B. NAME

7 from a, T_TREE B

8 where a. ID = B. FATHER_ID)

9 cycle name set DUP_NAME TO 'y' DEFAULT 'n'

10 SELECT * from;

ID FATHER_ID NAME D

---------------------------------------------------

1 0 A N

2 1 BC N

3 1 DE N

4 1 FG N

5 2 HIJ N

6 4 KLM N

9 5 BC Y

7 6 NOPQ N

8 5 RSTU N

Nine rows have been selected.

The column NAME is determined here. If the column NAME is repeated in the same tree region, the column Y of CYCLE will occur.

If duplicate columns exist on two different trees, the CYCLE is Y:

SQL> UPDATE T_TREE SET NAME = 'de' WHERE ID = 9;

1 row updated.

SQL> WITH A (ID, FATHER_ID, NAME)

2 (select id, FATHER_ID, NAME

3 FROM T_TREE

4 where id = 1

5 UNION ALL

6 select B. ID, B. FATHER_ID, B. NAME

7 from a, T_TREE B

8 where a. ID = B. FATHER_ID)

9 cycle name set DUP_NAME TO 'y' DEFAULT 'n'

10 SELECT * from;

ID FATHER_ID NAME D

---------------------------------------------------

1 0 A N

2 1 BC N

3 1 DE N

4 1 FG N

5 2 HIJ N

6 4 KLM N

9 5 DE N

7 6 NOPQ N

8 5 RSTU N

Nine rows have been selected.