Oracle数据库分批次向A表中插入B表中数据

1. 业务简介：A表是B表的明细表，现需求是B表有上百万记录，在A表中插入B表的相关记录。
2. 准备工作：oracle数据库，数据库工具pl\sql，存储过程写在包中。

3. 实现思路：
   3.1 获取B表总数据量V_CU_COUNT，设置一次提交量V_SIZE为5000，计算出总页数V_PAGE := CEIL(V_CU_COUNT / V_SIZE);，起始索引V_INDEX为1；
   3.2 每5000条数据提交一次，索引值加1，循环提交；

4. 存储过程

PROCEDURE INIT_CU_ENJOY(O_ERROR_CODE OUT VARCHAR2
                           ,O_ERROR_TEXT OUT VARCHAR2) IS
        V_CU_COUNT NUMBER;
        V_SIZE     NUMBER;
        V_PAGE     NUMBER;
        V_INDEX    NUMBER;
        V_EXCEPTION EXCEPTION;
    BEGIN
        V_INDEX := 1;
        V_SIZE  := 5000;
        SELECT COUNT(1)
        INTO   V_CU_COUNT
        FROM   CU_CUSTOMER CC
        WHERE  CC.CUSTOMER_ACC_ENJOY > 0;
        V_PAGE := CEIL(V_CU_COUNT / V_SIZE);

        BEGIN

            LOOP
                IF V_INDEX <= V_PAGE THEN
                    INSERT INTO CU_ENJOY
                        (ENJOY_ID
                        ,ENJOY_CUSTOMER
                        ,ENJOY_CATEGORY
                        ,ENJOY_AMT
                        ,ENJOY_BALANCE
                        ,ENJOY_ABSTRACT
                        ,ENJOY_REMARK)
                        SELECT SEQ_CU_ENJOY.NEXTVAL ENJOY_ID
                              ,ENJOY_CUSTOMER
                              ,ENJOY_CATEGORY
                              ,ENJOY_AMT
                              ,ENJOY_BALANCE
                              ,ENJOY_ABSTRACT
                              ,ENJOY_REMARK
                        FROM   (SELECT ROWNUM RN
                                      ,CC.CUSTOMER_ID ENJOY_CUSTOMER
                                      ,4 ENJOY_CATEGORY
                                      ,CC.CUSTOMER_ACC_ENJOY ENJOY_AMT
                                      ,CC.CUSTOMER_ACC_ENJOY ENJOY_BALANCE
                                      ,'摘要' ENJOY_ABSTRACT
                                      ,'备注' ENJOY_REMARK
                                FROM   CU_CUSTOMER CC
                                WHERE  CC.CUSTOMER_ACC_ENJOY > 0
                                AND    ROWNUM <= V_INDEX * V_SIZE)
                        WHERE  RN >= (V_INDEX - 1) * V_SIZE + 1;

                    V_INDEX := V_INDEX + 1;
                    COMMIT;
                ELSE
                    RETURN;
                END IF;
            END LOOP;

        END;

    EXCEPTION
        WHEN V_EXCEPTION THEN
            PKG_SYS.SET_ERROR('INIT_CU_ENJOY'
                             ,'插入初始化数据错误'
                             ,O_ERROR_CODE
                             ,O_ERROR_TEXT);

            ROLLBACK;
        WHEN OTHERS THEN
            PKG_SYS.SET_ERROR('INIT_CU_ENJOY'
                             ,'插入初始化数据错误'
                             ,O_ERROR_CODE
                             ,O_ERROR_TEXT);

            ROLLBACK;
    END INIT_CU_ENJOY;