oracle批量处理范例

Oracle批量处理范例

PL SQL和SQL用的是不同的引擎来处理的，SQL使用SQL引擎，PLSQL使用PLSQL引擎。在PLSQL块，存储过程，函数中，PLSQL和SQL是共同存在的，变量，赋值，循环等用的是PLSQL引擎，但是游标中FETCH一条记录是通过SQL引擎的，因此循环的时候，PLSQL和SQL引擎会进行大量的上下文切换，减少切换可以提高效率。

1.批量检索

DECLARE
   CURSOR_EMP               SYS_REFCURSOR;
   V_SQL                    VARCHAR2 (4000);

   TYPE NAMELIST IS TABLE OF VARCHAR2 (20);

   V_ENAME                  NAMELIST;
   CN_FETCH_SIZE   CONSTANT PLS_INTEGER := 1000;
BEGIN
   V_SQL := 'SELECT ENAME FROM SCOTT.EMP WHERE EMPNO > :1';

   OPEN CURSOR_EMP FOR V_SQL USING 7698;

   LOOP
      FETCH CURSOR_EMP
      BULK COLLECT INTO V_ENAME
      LIMIT CN_FETCH_SIZE;

      FOR I IN 1 .. V_ENAME.COUNT
      LOOP
         DBMS_OUTPUT.PUT_LINE (V_ENAME (I));
      END LOOP;

      EXIT WHEN V_ENAME.COUNT < CN_FETCH_SIZE;
   END LOOP;

   CLOSE CURSOR_EMP;
END;

2.批量处理

2.1.批量检索，单条处理

/* Formatted on 2021-09-24 上午 09:39:38 (QP5 v5.163.1008.3004) */
DECLARE
   TYPE TYPE_RECORD_TEST02 IS RECORD
   (
      ID         MONKEY.TEST02.ID%TYPE,
      ADD_DATE   MONKEY.TEST02.ADD_DATE%TYPE,
      NAME       MONKEY.TEST02.NAME%TYPE,
      RID        UROWID
   );

   TYPE TYPE_RESULT IS VARRAY (1000) OF TYPE_RECORD_TEST02;

   RESULT                   TYPE_RESULT;
   CN_BATCH_SIZE   CONSTANT PLS_INTEGER := 1000;
   CUR_TEST02               SYS_REFCURSOR;
   V_SQL    VARCHAR2 (4000) := 'SELECT ID,ADD_DATE,NAME,ROWID FROM MONKEY.TEST02 WHERE ID >:1';
BEGIN
   OPEN CUR_TEST02 FOR V_SQL USING 2;

   LOOP
      FETCH CUR_TEST02
      BULK COLLECT INTO RESULT
      LIMIT CN_BATCH_SIZE;

      FOR I IN 1 .. RESULT.COUNT
      LOOP
         UPDATE MONKEY.TEST02
            SET ADD_DATE = ADD_DATE + 1
          WHERE ROWID = RESULT (I).RID;
      END LOOP;

      EXIT WHEN RESULT.COUNT < CN_BATCH_SIZE;
   END LOOP;

   CLOSE CUR_TEST02;

   COMMIT;
END;

2.2.批量检索，批量处理

/* Formatted on 2021-09-24 上午 09:56:49 (QP5 v5.163.1008.3004) */
DECLARE
   TYPE TYPE_TEST02_ID_LIST IS TABLE OF MONKEY.TEST02.ID%TYPE;

   TYPE TYPE_TEST02_ADD_DATE_LIST IS TABLE OF MONKEY.TEST02.ADD_DATE%TYPE;

   TYPE TYPE_TEST02_NAME_LIST IS TABLE OF MONKEY.TEST02.NAME%TYPE;

   TYPE TYPE_TEST02_ROWID_LIST IS TABLE OF UROWID;

   ID_LIST                  TYPE_TEST02_ID_LIST;
   ADD_DATE_LIST            TYPE_TEST02_ADD_DATE_LIST;
   NAME_LIST                TYPE_TEST02_NAME_LIST;
   ROWID_LIST               TYPE_TEST02_ROWID_LIST;
   CN_BATCH_SIZE   CONSTANT PLS_INTEGER := 1000;
   CUR_TEST02               SYS_REFCURSOR;
   V_SQL   VARCHAR2 (4000) := 'SELECT ID,ADD_DATE,NAME,ROWID FROM MONKEY.TEST02 WHERE ID >:1';
BEGIN
   OPEN CUR_TEST02 FOR V_SQL USING 2;

   LOOP
      FETCH CUR_TEST02
      BULK COLLECT INTO ID_LIST, ADD_DATE_LIST, NAME_LIST, ROWID_LIST
      LIMIT CN_BATCH_SIZE;

      FOR I IN 1 .. ROWID_LIST.COUNT
      LOOP
         ADD_DATE_LIST (I) := ADD_DATE_LIST (I) + 1;
      END LOOP;

      FORALL I IN 1 .. ROWID_LIST.COUNT
         EXECUTE IMMEDIATE
            'UPDATE MONKEY.TEST02 SET ADD_DATE=:1 WHERE ROWID=:2'
            USING ADD_DATE_LIST (I), ROWID_LIST (I);

      EXIT WHEN ROWID_LIST.COUNT < CN_BATCH_SIZE;
   END LOOP;

   CLOSE CUR_TEST02;

   COMMIT;
END;