【PL/SQL】初试 bulk collect

最新推荐文章于 2021-04-05 06:43:36 发布

转载最新推荐文章于 2021-04-05 06:43:36 发布 · 67 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/29415

本文介绍了一种使用BulkCollect批查询优化SQL查询效率的方法，并通过对比游标查询和DISTINCT查询展示了其优势。

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

SQL> create table yang(last_name varchar2(20),first_name varchar2(10),salary number(10));

Table created
Executed in 1.388 seconds

SQL> begin
2 for i in 1000..100999 loop
3 insert into yang (last_name,first_name,salary) values('qilong'||(i-1000),'yang'||(100999-i),i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed
Executed in 4.852 seconds

SQL> select count(*) from yang;
COUNT(*)
----------
    100000
Executed in 0.047 seconds
SQL> select count(1) from yang;

COUNT(1)
----------
    100000
Executed in 0.032 seconds
---常规的distinct用法。
SQL> select count (distinct last_name) from yang;
COUNT(DISTINCTLAST_NAME)
------------------------
                  100000
Executed in 0.124 seconds
SQL>

-----使用游标
SQL> declare
2    all_rows number(10);
3    temp_last_name yang.last_name%type;
4 begin
5    all_rows:=0;
6    temp_last_name:=' ';
7    for cur in (select last_name from yang order by last_name) loop
8        if cur.last_name!=temp_last_name then
9         all_rows:=all_rows+1;
10        end if;
11        temp_last_name:=cur.last_name;
12    end loop;
13    dbms_output.put_line('all_rows are '||all_rows);
14 end;
15 /
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 0.156 seconds

游标需要0.156 秒才能查出该表中有100000个不重复的Last_name值，所耗时间是Distinct查询多0.032秒。
--使用Bulk Collect批查询来实现
SQL> declare
2    all_rows number(10);
3    --首先，定义一个Index-by表数据类型
4    type last_name_tab is table of yang.last_name%type index by binary_integer;
5    last_name_arr last_name_tab;
6    --定义一个Index-by表集合变
7    temp_last_name yang.last_name%type;
8 begin
9    all_rows:=0;
10    temp_last_name:=' ';
11    --使用Bulk Collect批查询来充填集合变量
12    select last_name bulk collect into last_name_arr from yang;
13    for i in 1..last_name_arr.count loop
14        if temp_last_name!=last_name_arr(i) then
15         all_rows:=all_rows+1;
16        end if;
17        temp_last_name:=last_name_arr(i);
18    end loop;
19   dbms_output.put_line('all_rows are '||all_rows);
20 end;
21 /
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 0.078 seconds
--从上面执行结果，我们可以看到，
Bulk Collect批查询只需要0.078秒就能查出该表中有100000个不重复的Last_name值，
所耗时间只有游标查询的1/2，同时它比Distinct常规查询的速度也要快。