connect by level语法的理解
connect by level的语法由于构造大量的数据的时候特别有用，但是如果没有真正理解其中的含义，滥用就会导致生产大量的数据，通常情况下错误的理解是基数记录和基数记录的笛卡尔积，笛卡尔积是没错，但是两个因子并不都是基数记录，其中一个是上1个level的记录，知道这点很总要，否则会生产大量的超出你预想的记录数出来，具体看测试过程。
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 t4';
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-