connect by level语法的理解

最新推荐文章于 2024-07-22 16:03:25 发布

iteye_20954

最新推荐文章于 2024-07-22 16:03:25 发布

阅读量465

点赞数

文章标签：数据库

本文探讨了SQL中使用connectbylevel语法在构造大量数据时的原理与常见误解，指出误用可能导致数据生成数量超出预期，并通过示例演示正确理解和应用此功能的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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-