Connect by level syntax

 

The connect by level syntax is particularly useful when constructing a large amount of data. However, if you do not really understand the meaning of the syntax, misuse will lead to the production of a large amount of data, generally, the incorrect understanding is the Cartesian product of the Base record and the base record. Cartesian product is correct, but not both of them are the base record. One of them is the record of the last level, it is important to know this, otherwise it will produce a large number of records that exceed your expectation, depending on the test process.

SQL> select * from v $ version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Prod

PL/SQL Release 10.2.0.4.0-Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0-Production

NLSRTL Version 10.2.0.4.0-Production

 

SQL> Select * From t3;

 

ID NAME

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

1111 aaaa

2222 bbbb

SQL> Create Table t4 As Select ''level_ B, t3. * From t3 Where 1 = 2;

 

Table created

SQL> Create Table t5 As Select ''level_ B, t3. * From t3 Where 1 = 2;

 

Table created

 

SQL>

SQL> Select * From t4;

 

LEVEL_ B ID NAME

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

SQL> Select * From t5;

 

LEVEL_ B ID NAME

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

 

SQL>

SQL> Select * From t3;

 

ID NAME

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

1111 aaaa

2222 bbbb

SQL> Create Table t4 As Select 0 level_ B, t3. * From t3 Where 1 = 2;

 

Table created

SQL> Create Table t5 As Select 0 level_ B, t3. * From t3 Where 1 = 2;

 

Table created

 

SQL>

SQL> Select * From t4;

 

LEVEL_ B ID NAME

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

SQL> Select * From t5;

 

LEVEL_ B ID NAME

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

 

SQL>

SQL> Declare

2 v_Level Number: = 6;

3 Begin

4 Execute Immediate 'truncate table 4 ';

5 Execute Immediate 'truncate table t5 ';

6 Insert Into T4

7 Select Level, T3. * From T3 Connect By Level <= v_Level;

8 For I In 1 .. v_Level Loop

9 If I = 1 Then

10 Insert Into T5

11 Select I, T3. * From T3;

12 Else

13 Insert Into T5

14 Select I, T3. * From T3, T5 Where t5.level _ B = I-1;

15 End If;

16 End Loop;

17 End;

18/

 

PL/SQL procedure successfully completed

 

SQL> commit;

 

Commit complete

 

SQL>

SQL> Select Count (*) From T4;

 

COUNT (*)

----------

126

SQL> Select Count (*) From T5;

 

COUNT (*)

----------

126

SQL> Select *

2 From T4

3 Minus

4 Select * From T5;

 

LEVEL_ B ID NAME

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

SQL> Select *

2 From T5

3 Minus

4 Select * From T4;

 

LEVEL_ B ID NAME

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

-The End-

 

By Gtlions