connect by level语法的理解

gtlions

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
---------- ---------- -----

Maclean Liu(刘相兵

Good!