Batch insert MOD method in PL / SQL

Time:2020-8-1

After reading this chapter, you will learn the following:

  1. The MOD method is used to divide and submit the data to be inserted
  2. Use of commit

Basic block:

For variable in cursor loop
    IF MOD(SQL%ROWCOUNT,100000) = 0 THEN
        COMMIT;
    END IF;
    Commit; -- Re submit the remaining 100000 rows of data
End Loop;

An instance used to record the line of cursor traversal

--One time submission of data insertion (too much data, too long and too large memory consumption)
        BEGIN
          DELETE FROM EMP_ Syn; -- clear data to support rerun
          INSERT INTO EMP_SYN
            SELECT * FROM EMP;
          COMMIT;
        END;
        
        --Submit data in batches
        DECLARE
          CURSOR C_EMP IS
            SELECT * FROM EMP;
        BEGIN
          DELETE FROM EMP_ Syn; -- clear data (less data) to support rerun
          FOR T_EMP IN C_EMP LOOP
              INSERT INTO EMP_SYN
              SELECT *
                FROM emp;
              IF mod(C_ EMP% rowcount, 10000) = 0 then -- submit every 10000 lines inserted
                COMMIT;
              END IF;
                            Commit; --- insert the remaining data of less than 10000 lines 
          END LOOP;
        END;

Rowcount uses:
SQL% rowcount is used to record the number of changes. It must be executed after a modification class statement such as add, delete, modify, etcThe number of modifications to the last statement executed before shall prevail

BEGIN
    DELETE FROM emp;
    DBMS_ OUTPUT.PUT_ Line (SQL% rowcount); -- record the number of deleted data
    INSERT INTO emp(empno) VALUES (7777);
    DBMS_ OUTPUT.PUT_ Line (SQL% rowcount); -- record the number of inserted data
    UPDATE emp SET comm=1000 WHERE empno=7369;
    DBMS_ OUTPUT.PUT_ Line (SQL% rowcount); -- record the number of updated data
END;