PL/SQL中三种游标循环效率对比

这里主要对比以下三种格式的游标循环: 1.单条处理 open 游标; LOOP FETCH 游标 INTO 变量; EXIT WHEN 条件; END LOOP; CLOSE 游

这里主要对比以下三种格式的游标循环:

1.单条处理

open 游标;

LOOP 

FETCH 游标 INTO 变量;

EXIT WHEN  条件;

END LOOP;

CLOSE 游标;

2.批量处理

open 游标;

FETCH 游标 BULK COLLECT INTO 集合变量;

CLOSE 游标;

3.隐式游标

for x in (sql语句) loop

...--逻辑处理

end loop; 

以上为工作中常见的几种游标处理方式,一般来说批量处理的速度要最好,隐式游标的次之,单条处理的最差,但是在我的实际工作中发现大部分使用的还是第一种游标处理。

归其原因竟是对集合变量及批量处理的效率等问题不了解所致。

推荐阅读：

PL/SQL下连接远程Oracle数据库

PL/SQL“ ORA-14551: 无法在查询中执行 DML 操作”解决

这里简单的测试一下以上三种游标的效率,并分析trace文件来查看这3种处理方式的本质。 

--创建测试大表

00:09:54 SCOTT@orcl> create table big_data as select 'Cc'||mod(level,8) a,'Dd'||
mod(level,13) b from dual connect by level

Table created.

Elapsed: 00:00:05.87
00:11:17 SCOTT@orcl> select count(*) from big_data;

  COUNT(*)
----------
    999999

1 row selected.

Elapsed: 00:00:00.07

--分别执行以上三种方式的游标处理的plsql块

00:11:21 SCOTT@orcl> declare
00:17:54  2    cursor c_a is
00:17:54  3      select a from big_data;
00:17:54  4
00:17:54  5    v_a big_data.a%type;
00:17:54  6  begin
00:17:54  7    open c_a;
00:17:54  8    loop
00:17:54  9      fetch c_a into v_a;
00:17:54  10      exit when c_a%notfound;
00:17:54  11    end loop;
00:17:54  12    close c_a;
00:17:54  13  end;
00:17:56  14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.42
00:18:05 SCOTT@orcl> declare
00:19:56  2    cursor c_a is
00:19:56  3      select a from big_data;
00:19:56  4
00:19:56  5  type t_a is table of c_a%rowtype;
00:19:56  6    v_a t_a;
00:19:56  7  begin
00:19:56  8    open c_a;
00:19:56  9    --批量处理
00:19:56  10      fetch c_a bulk collect into v_a;
00:19:56  11    close c_a;
00:19:56  12  end;
00:19:57  13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.64
00:22:55 SCOTT@orcl> declare
00:23:18  2    v_a big_data.a%type;
00:23:18  3    begin
00:23:18  4    --批量处理
00:23:18  5    for x in (select a from big_data) loop
00:23:18  6      v_a:=x.a;
00:23:18  7    end loop;
00:23:18  8  end;
00:23:18  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.79

注:请保证plsql_optimize_level的参数为2,如果不为2,可能测试结果会不一样.
注意对比消耗时间,1为7.42s, 2为0.64s, 3为0.79s

请继续阅读：