Bulk Collect特性可以让我们在PL/SQL中能使用批查询,批查询在某些情况下能显著提高查询效率。
-
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。
-
可以在select into,fetch into,returning into语句使用bulk collect。
-
注意在使用bulk collect时,所有的into变量都必须是collections。
1. 背景
以前曾经做过一个需求,数据库中有两张表,A表是2千万记录,B表是1千万条记录,它们之间存在某种联系,要求程序将它们关联起来,以A表为基准。
然后就是PL/SQL写了个程序(参考方式一代码),运行巨慢,且会报内存不足(在自己的笔记本上跑的)。后来用了BULK COLLECT,有了很大改善。以前的代码已经没有了运行的环境,代码也只保留了框架下来,贴在这里,做个记号。
方式一,最早的实现方式:
DECLARE --定义一个RECORD类型用来存储记录,为节省内存,只存储需要处理的字段 TYPE SIM_RECORD is record( SYSCODE SIM.SYSCODE%TYPE, CUSNAME SIM.CUSNAME%TYPE, CUSADDR SIM.CUSADDR%TYPE, PHONE SIM.PHONE%TYPE ); V_SIM_RECORD SIM_RECORD; --定义若干用到的其它变量 v_start_syscode number := 1; v_process_records number := 1000000; v_count number := 0; --定义游标 CURSOR SIMCursor IS SELECT SYSCODE,CUSNAME,CUSADDR,PHONE FROM SIM WHERE SYSCODE>=v_start_syscode AND SYSCODE<v_start_syscode+v_process_records; BEGIN --打开游标 OPEN SIMCursor; LOOP --读取游标 FETCH SIMCursor INTO V_SIM_RECORD; EXIT WHEN SIMCursor%NOTFOUND; --防止更新出现异常,所以将DML语句进行封装,以避免某条记录出现错误而导致退出循环 BEGIN --主处理语句,省略 --取得值通过:V_SIM_RECORD.SYSCODE等这样的方式获取 --每处理20000条记录,输出一些统计信息 v_count := v_count+1; IF mod(v_count,20000)=0 THEN dbms_output.put_line(V_SIM_RECORD.SYSCODE||'['||v_count||']'); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('sqlerrm-->' ||sqlerrm); END; END LOOP; --关闭游标 CLOSE SIMCursor; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('sqlerrm-->' ||sqlerrm); END;
方式二,在select into语句中使用bulk collect
DECLARE --定义存储数据的数据结构(collections) TYPE SIM_TABLE IS TABLE OF SIM%ROWTYPE; T_SIM_TABLE SIM_TABLE; --定义若干用到的其它变量 v_start_syscode number := 1; v_process_records number := 1000000; v_count number := 0; BEGIN --在select into语句中使用bulk collect,一次性取得 SELECT * BULK COLLECT INTO T_SIM_TABLE FROM SIM WHERE SYSCODE>=v_start_syscode AND SYSCODE<v_start_syscode+v_process_records; FOR i IN 1 .. T_SIM_TABLE.count LOOP --防止更新出现异常,所以将DML语句进行封装,以避免某错误记录导致退出循环 BEGIN --主处理语句,省略 --取得值通过:T_SIM_TABLE(i).PHONE等这样的方式获取 --每处理20000条记录,输出一些统计信息 v_count := v_count+1; IF mod(v_count,20000)=0 THEN dbms_output.put_line(T_SIM_TABLE(i).PHONE||'['||v_count||']'); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('sqlerrm-->' ||sqlerrm); END; END LOOP; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('sqlerrm-->' ||sqlerrm); END;
方式三,在fetch into中使用bulk collect
DECLARE --定义存储数据的数据结构(collections) TYPE SIM_TABLE IS TABLE OF SIM%ROWTYPE; T_SIM_TABLE SIM_TABLE; --定义若干用到的其它变量 v_start_syscode number := 1; v_process_records number := 1000000; v_count number := 0; --定义游标 CURSOR SIMCursor IS SELECT * FROM SIM where SYSCODE>=v_start_syscode AND SYSCODE<v_start_syscode+v_process_records; BEGIN OPEN SIMCursor; LOOP EXIT WHEN SIMCursor%NOTFOUND; --在fetch into中使用bulk collect,分批次取得 FETCH SIMCursor BULK COLLECT INTO T_SIM_TABLE LIMIT 50000; FOR i IN 1 .. T_SIM_TABLE.count LOOP --防止更新出现异常,所以将DML语句进行封装,以避免某错误记录导致退出循环 BEGIN --主处理语句,省略 --取得值通过:T_SIM_TABLE(i).PHONE等这样的方式获取 --每处理20000条记录,输出一些统计信息 v_count := v_count+1; IF mod(v_count,20000)=0 THEN dbms_output.put_line(T_SIM_TABLE(i).PHONE||'['||v_count||']'); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('sqlerrm-->' ||sqlerrm); END; END LOOP; END LOOP; CLOSE SIMCursor; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('sqlerrm-->' ||sqlerrm); END;