Oracle database inserts data in table B into table A in batches

Keywords: Database Oracle SQL Stored Procedure

  1. Business description: table A is the detailed statement of table B. now it is required that table B has millions of records. Insert the relevant records of table B in table A.
  2. Preparation: oracle database, database tool pl\sql, stored procedures written in the package.
  3. Implementation ideas:
    3.1 get the total data volume of table B, V ﹣ Cu ﹣ count, set the one-time submission volume V ﹣ size to 5000, and calculate the total pages V ﹣ page: = ceil (V ﹣ Cu ﹣ count / V ﹣ size); and the initial index V ﹣ index is 1;
    3.2 submit every 5000 pieces of data, add 1 to index value, and submit circularly;

  4. stored procedure

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
                                      ,'abstract' ENJOY_ABSTRACT
                                      ,'Remarks' 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'
                             ,'Insert initialization data error'
                             ,O_ERROR_CODE
                             ,O_ERROR_TEXT);

            ROLLBACK;
        WHEN OTHERS THEN
            PKG_SYS.SET_ERROR('INIT_CU_ENJOY'
                             ,'Insert initialization data error'
                             ,O_ERROR_CODE
                             ,O_ERROR_TEXT);

            ROLLBACK;
    END INIT_CU_ENJOY;

Posted by name1090 on Sat, 04 Apr 2020 13:06:07 -0700