Oracle学习笔记之五sp1，PL/SQL之BULK COLLECT

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;