The usage of start with in oracle Database, oraclestart

The usage of start with in oracle Database, oraclestart

Oracle provides the start with connect by syntax structure to implement recursive queries.

1. A simple example:

SQL> select * from test;

BILL_MONTH DAY_NUMBER MSISDN

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

200803 1 13800

200803 3 13800

200803 2 13800

200803 2 13801

200803 4 13804

200803 5 13804

200803 7 13804

200803 8 13804

200803 6 13802

200803 6 13801

200803 7 13801

200803 8 13801

12 rows selected

SQL>

SQL> select * from test

2 start with day_number = 1

3 connect by prior day_number = day_number-1 and prior msisdn = msisdn

4;

BILL_MONTH DAY_NUMBER MSISDN

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

200803 1 13800

200803 2 13800

200803 3 13800

SQL>

The preceding statement finds the data starting from 1 and increasing from day_number to 1.

2. start with connect by syntax structure

As shown in the preceding example, the syntax structure is start with condition connect by condition (including the prior keyword)

The range of seed data given by start with conditon. The conditions for recursive query are provided after connect by. The prior keyword indicates the parent data, and the prior condition indicates the conditions for the child data to meet the parent data.

In the following start with connect by structure, it indicates that the day_number of the parent data is equal to the day_number-1 of the child Data and the msisdn of the parent data is equal to the msisdn of the child Data.

Start with day_number = 1

Connect by prior day_number = day_number-1 and prior msisdn = msisdn