oracle批量操作模板

SET SERVEROUTPU ON    --使用于PL/SQL COMMAND WINDOW或者SQL PLUS中

CREATE OR REPLACE PROCEDURE MOVE_TB_SMS AS
BEGIN
  DECLARE
    CURSOR C1 IS
      SELECT ROWID ROW_ID FROM TB_SMS WHERE CADDDATE < trunc(sysdate - 10);    --找出你需要操作的表的rowid
      C_COMMIT CONSTANT PLS_INTEGER := 30000;      --代表提交的行数
      V_COUNT NUMBER :=0;
  BEGIN
    EXECUTE IMMEDIATE 'alter session set nls_date_format=''yyyy/mm/dd hh24:mi:ss''';
    FOR C1REC IN C1 LOOP
      INSERT INTO tb_sms_history
        (ICONTENTTYPE,
         CSERVICE)
        SELECT ICONTENTTYPE,
               CSERVICE
          FROM TB_SMS
         WHERE ROWID = C1REC.ROW_ID;     --主要记录rowid两边保持一致，此处以insert为例
    
      --DELETE FROM TB_SMS WHERE ROWID = C1REC.ROW_ID;
    
      V_COUNT := V_COUNT + 1;
      IF (MOD(C1%ROWCOUNT, C_COMMIT) = 0) THEN
        COMMIT;
        DBMS_LOCK.SLEEP(5);     --这个权限可能需要单独授权，请注意，单位是s
        DBMS_OUTPUT.PUT_LINE('NOW TIME: '||SYSDATE||', NOW COMMIT: '||V_COUNT);
      END IF;
    END LOOP;
  
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('NOW TIME: '||SYSDATE||', NOW COMMIT: '||V_COUNT);
  END;
END;
/