How to use the prior action in query Oracle lists

For prior operations in a tree query, it is generally used in a connect by statement, and this prior operation is also available in the field list of select.

Look at a simple example:

Sql> CREATE TABLE T (ID number, parent_id number, name VARCHAR2 (30));

Table created.

Sql> INSERT INTO t values (1, NULL, ' a ');

1 row created.

Sql> INSERT INTO t values (2, 1, ' B ');

1 row created.

Sql> INSERT into t values (3, 1, ' C ');

1 row created.

Sql> INSERT INTO t values (4, 2, ' d ');

1 row created.

Sql> commit;

Commit complete.

Sql> select * from T;

ID parent_id NAME

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

1 A

2 1 B

3 1 C

4 2 D

Sql> Select ID, parent_id, name

2 from T

3 start with id = 1

4 connect by Prior ID = parent_id;

ID parent_id NAME

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

1 A

2 1 B

4 2 D

3 1 C

This is the longest use of the prior operation in a tree query and is used in the Connect by statement.

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45453.htm

However, prior can also be used in the select list:

Sql> Select ID, parent_id, prior ID p_id, name, prior name P_name

2 from T

3 start with id = 1

4 connect by Prior ID = parent_id;

ID parent_id p_id NAME p_name

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

1 A

2 1 1 B A

4 2 2 D b

3 1 1 c A

In addition to ordinary columns, prior can even be used for Sys_connect_by_path functions:

Sql> Select

2 ID,

3 parent_id,

4 Prior ID p_id,

5 Sys_connect_by_path (name, '/') name,

6 Prior Sys_connect_by_path (name, '/') p_name

7 from T

8 start with id = 1

9 Connect by Prior ID = parent_id;

ID parent_id p_id NAME p_name

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

1/a

2 1 1/a/b/a

4 2 2/a/b/d/a/b

3 1 1/a/c/a

With this feature, you can make a tree query implement many more flexible features.