使用forall与bulk collect 提升数据割接效率

有两张表：user_info和service，两张表通过id字段关联，数据是一一对应关系

现需要割接到新系统的user_info、service和accoutt_info。

 

2000万数据，使用集合处理23分钟

使用传统fetch insert 一条一条处理1小时15分钟

 

 

CREATE OR REPLACE PROCEDURE p_userconver

AS   

        type order_rec is record(

           group1                 user_info_old.group1%TYPE,

           group2                 user_info_old.group2%TYPE,

           type                     user_info_old.type%TYPE,

           status                  user_info_old.status%TYPE,

           user_type            user_info_old.user_type%TYPE,

           login                     service_old.login%TYPE,

           created_t             service_old.created_t%TYPE,

           mod_t                  service_old.mod_t%TYPE           

        );

        TYPE type_cur IS REF CURSOR RETURN order_rec;

        cursor1  type_cur;

        TYPE table_rec  IS TABLE OF order_rec index by pls_integer;

        v_onerow  table_rec;        

        TYPE table_userid  IS TABLE OF user_info.userid%TYPE index by pls_integer;

        userids1  table_userid;

        userids1null  table_userid;

        v_errorcode      number(10);

        v_sqlerrordes    varchar2(255);

BEGIN

insert into t values(0,0,'begin:'||systimestamp,null,null);

        OPEN cursor1 FOR SELECT a.group1,a.group2,a.type,a.status,a.user_type,b.login,b.created_t,b.mod_t FROM user_info_old a,service_old b where b.id = a.id;

           LOOP

            FETCH cursor1 bulk collect INTO v_onerow limit 20000;

            userids1:=userids1null;

            for i IN v_onerow.first..v_onerow.last

            loop

               begin                                

               userids1(i):=1002||v_onerow(i).login||to_char(v_onerow(i).created_t,'YYMMDD');       

               --其它字段处理                      

               EXCEPTION

                 WHEN OTHERS THEN 

                 v_errorcode:=SQLCODE;

                 v_sqlerrordes:=SQLERRM;

                 --userids1(i):=null;

                 insert into t values(1,v_errorcode,v_sqlerrordes,v_onerow(i).login,null);                 

               end;                

            end loop;

            commit;

            begin             

            --FORALL indx IN userids1.first..userids1.last SAVE EXCEPTIONS

            FORALL indx IN indices of userids1 SAVE EXCEPTIONS

               insert  into user_info(userid, status, user_type, group2, group1, type, created_t, mod_t)

               values(userids1(indx), v_onerow(indx).status, v_onerow(indx).user_type, v_onerow(indx).group2, v_onerow(indx).group1, v_onerow(indx).type, v_onerow(indx).created_t, v_onerow(indx).mod_t);

            EXCEPTION

                 WHEN OTHERS THEN

                 v_errorcode:=SQL%BULK_EXCEPTIONS.COUNT;

                 v_sqlerrordes:=SQLERRM;

                 insert into t values(-1,-1,'SQL%BULK_EXCEPTIONS.COUNT',v_errorcode,v_errorcode);                 

            end;

            begin

            FORALL indx IN indices of userids1 SAVE EXCEPTIONS

               insert into service(login, status, type, userid, created_t, mod_t)

               values(v_onerow(indx).login, 1, 3, userids1(indx), v_onerow(indx).created_t, v_onerow(indx).mod_t);

            EXCEPTION

                 WHEN OTHERS THEN

                 v_errorcode:=SQL%BULK_EXCEPTIONS.COUNT;

                 v_sqlerrordes:=SQLERRM;

                 insert into t values(-1,-2,'SQL%BULK_EXCEPTIONS.COUNT',v_errorcode,v_errorcode);                 

            end;

            begin

            FORALL indx IN indices of userids1 SAVE EXCEPTIONS

               insert  into account_info(account_id, account_type, status, balance, password, userid, created_t, mod_t)

               values(seq_accout_id.nextval, 1, 1, 0, null, userids1(indx), v_onerow(indx).created_t, v_onerow(indx).mod_t);

            EXCEPTION

                 WHEN OTHERS THEN

                 v_errorcode:=SQL%BULK_EXCEPTIONS.COUNT;

                 v_sqlerrordes:=SQLERRM;

                 insert into t values(-1,-3,'SQL%BULK_EXCEPTIONS.COUNT',v_errorcode,v_errorcode);                 

            end;

            commit;

           EXIT WHEN cursor1%NOTFOUND;          

           END LOOP;

        CLOSE cursor1;

insert into t values(0,0,'end:'||systimestamp,null,null);

commit;

END;