3.2改写后的存储过程（基于9i）

    现在，笔者使用批量绑定的方式，也就是使用BULK COLLECT和FORALL的方式来改写上面的存储过程：P_HANDLE_PIX。

    CREATE OR REPLACE PROCEDURE P_HANDLE_PIX_NEW(
    ai_rtd out number,
    as_rmsg out varchar2)
    IS
    TYPEt_pixid IS TABLE OF pix_trans.id%TYPE INDEX BY PLS_INTEGER;
    TYPEt_pixtype IS TABLE OF pix_trans.pixtype%TYPE INDEX BY PLS_INTEGER;
    TYPEt_actcode IS TABLE OF pix_trans.actcode%TYPE INDEX BY PLS_INTEGER;

    pix_id t_pixid;
    pix_pixtype t_pixtype;
    pix_actcode t_actcode;

    succ_pix_id t_pixid;
    succ_pix_pixtype t_pixtype;
    succ_pix_actcode t_actcode;

    fail_pix_id t_pixid;

    ll_begin number;
    ll_end number;

    indx_succ number;
    indx_fail number;
    BEGIN
    SELECT to_char(sysdate,'sssss') INTO ll_begin FROM dual;
    --获得所有的pix记录
    SELECT id,pixtype,actcode
    BULK COLLECT INTO pix_id,pix_pixtype,pix_actcode
    FROM pix_trans
    WHERE status = '00';
    --如果集合为空，则退出
    IF pix_id.count=0 THEN
    ai_rtd := 0;
    as_rmsg := 'No recoreds';
    RETURN;
    END IF;
    --逐条处理记录
    indx_succ := 1;
    indx_fail := 1;
    FOR indx IN pix_id.first .. pix_id.last
    LOOP
    P_HANDLE_SOTCK(pix_id(indx),pix_pixtype(indx),pix_actcode(indx),ai_rtd,as_rmsg);
    IF ai_rtd>0 THEN
    succ_pix_id(indx_succ) := pix_id(indx);
    succ_pix_pixtype(indx_succ) := pix_pixtype(indx);
    succ_pix_actcode(indx_succ) := pix_actcode(indx);
    indx_succ := indx_succ + 1;
    ELSE
    fail_pix_id(indx_fail) := pix_id(indx);
    indx_fail := indx_fail + 1;
    END IF;
    END LOOP;
    --将处理成功的记录插入历史表
    FORALL indx IN succ_pix_id.first .. succ_pix_id.last
    INSERT INTO pix_trans_his(id,pixtype,actcode)
    VALUES (succ_pix_id(indx),succ_pix_pixtype(indx),succ_pix_actcode(indx));

    --更新记录的成功标志
    FORALL indx IN succ_pix_id.first .. succ_pix_id.last
    UPDATE pix_trans SET status = '10' WHERE id = succ_pix_id(indx);

    --更新记录的失败标志
    FORALL indx IN fail_pix_id.first .. fail_pix_id.last
    UPDATE pix_trans SET status = '10' WHERE id = fail_pix_id(indx);

    SELECT to_char(sysdate,'sssss') INTO ll_end FROM dual;
    DBMS_OUTPUT.PUT_LINE('forall:' || to_char(ll_end - ll_begin));
    END;

    我们看到，使用批量绑定的后果就是程序的可读性差了，程序代码更加复杂了。我们可以再次进行测试，看看改写后运行一次需要多长时间。

   SQL> set serveroutput on;
    SQL> declare
    2li_rtd number;
    3ls_rmsg varchar2(255);
    4begin
    5p_handle_pix_new(li_rtd,ls_rmsg);
    6end;
    7/
    for loop:11

    PL/SQL procedure successfully completed.

     可以看到现在运行一次只需要11秒。时间相比不使用批量绑定来说，缩短了9秒，也就是大约提高了45%的性能。这应该算是一个很惊人的数字了。还有没有办法改进呢？我们试试10g下是不是还可以更快。

    3.3进一步改写存储过程（基于10g）

    CREATE OR REPLACE PROCEDURE P_HANDLE_PIX_NEW_10(
    ai_rtd out number,
    as_rmsg out varchar2)
    IS
    TYPE t_pix_pointer IS TABLE OF PLS_INTEGER;
    TYPE t_pixid IS TABLE OF pix_trans.id%TYPE INDEX BY PLS_INTEGER;
    TYPE t_pixtype IS TABLE OF pix_trans.pixtype%TYPE INDEX BY PLS_INTEGER;
    TYPE t_actcode IS TABLE OF pix_trans.actcode%TYPE INDEX BY PLS_INTEGER;

    pix_id t_pixid;
    pix_pixtype t_pixtype;
    pix_actcode t_actcode;

    succ_pix_id t_pix_pointer := t_pix_pointer();
    fail_pix_id t_pix_pointer := t_pix_pointer();

    ll_begin number;
    ll_end number;

    BEGIN
    SELECT to_char(sysdate,'sssss') INTO ll_begin FROM dual;
    --获得pix信息
    SELECT id,pixtype,actcode
    BULK COLLECT INTO pix_id,pix_pixtype,pix_actcode
    FROM pix_trans
    WHERE status = '00';
    --如果集合为空，则退出
    IF pix_id.count=0 THEN
    ai_rtd := 0;
    as_rmsg := 'No recoreds';
    return;
    END IF;
    --逐条处理记录
    FOR indx IN pix_id.first .. pix_id.last
    LOOP
    P_HANDLE_STOCK(pix_id(indx),pix_pixtype(indx),pix_actcode(indx),ai_rtd,as_rmsg);
    IF ai_rtd>0 THEN
    succ_pix_id.extend;
    succ_pix_id(succ_pix_id.last) := indx;
    ELSE
    fail_pix_id.extend;
    fail_pix_id(fail_pix_id.last) := indx;
    END IF;
    END LOOP;
    --将处理成功的记录插入历史表
    FORALL indx IN VALUES OF succ_pix_id
    INSERT INTO pix_trans_his(id,pixtype,actcode)
    VALUES (pix_id(indx),pix_pixtype(indx),pix_actcode(indx));

    --更新记录的成功标志
    FORALL indx IN VALUES OF succ_pix_id
    UPDATE pix_trans SET status = '10' WHERE id = pix_id(indx);

    --更新记录的失败标志
    FORALL indx IN VALUES OF fail_pix_id
    UPDATE pix_trans SET status = '10' WHERE id = pix_id(indx);

    SELECT to_char(sysdate,'sssss') INTO ll_end FROM dual;
    DBMS_OUTPUT.PUT_LINE('forall values of:' || to_char(ll_end - ll_begin));
    END;

    在10g里，我们可以减少了两个集合，而只是引入一个失败的集合指针和一个成功的集合指针。通过失败的集合指针，我们可以找到失败的pix_trans.id，从而更新pix_trans表里对应的记录的状态；通过成功的集合指针，我们可以找到需要插入历史表的三个字段的值。

    那么这样改写以后的效果如何呢？我们来测试一下运行速度。

    SQL> set serveroutput on;
    SQL> declare
    2li_rtd number;
    3ls_rmsg varchar2(255);
    4begin
    5p_handle_pix_new_10(li_rtd,ls_rmsg);
    6end;
    7/
    forall values of:10

    PL/SQL procedure successfully completed.

    可以看到，相比9i下的写法，速度提高了1秒钟。大约是9%左右。效果并不是很明显。