ORACLE renewal history Query

ORACLE renewal history Query

ORACLE supports regular query operations by using the CTE internal audit method, and also has its own unique query method. In ORACLE, this method is called continuous data query.

This example illustrates the two query methods through a simple example.

Number of rows:

CREATE TABLE TBL_TEST( ID NUMBER,  NAME VARCHAR2(100),  PID NUMBER); /BEGININSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');END; /

Requirement: ID = all the sublinks under the 1 Link (the values are 1, 2, 4, and 5 in the table)

The method for ORACLE-specific volume tracing query is as follows:

SELECT * FROM TBL_TestSTART with id = 1 -- start row connect by prior id = pid; -- prior indicates the previous row (parent row)

CTE mode:

WITH cte(ID,NAME,PID) AS(    SELECT ID,NAME,PID    FROM TBL_TEST    WHERE ID = 1UNION ALL    SELECT b.ID,b.NAME,b.PID    FROM cte a    INNER JOIN TBL_TEST b ON a.id = b.PID)SELECT * FROM cte;

The above two methods have all found positive results.

The Start With of the first method is equivalent to the part above union all of the second method. connect by is equivalent to the part below union all.

The CTE method is portable. It is also supported by SQL SERVER and DB2.

You can refer to the following articles for more examples of the CTE method:

SQL audit validation

Checking your initial experiences