oracle存储过程中insert语句性能如何调优？

[layznet]

功能：根据给定的文件名和要插入的记录数N，在表中插入N条记录，其中N甚至会超过1亿。

CREATE OR REPLACE PROCEDURE INSERT_REPORT(
                    FILE_NAME  VARCHAR2,
                    LINE_NO NUMBER) IS
   CURRENT_NO NUMBER := 0;
BEGIN
  DELETE FROM T_USER_REPORT;
  CURRENT_NO := CURRENT_NO+1;
  WHILE CURRENT_NO<=LINE_NO LOOP
        INSERT INTO T_USER_REPORT(FILENAME,LINENO,COLUMNNO,RESULTCODE) VALUES(FILE_NAME,CURRENT_NO,0,'00');
        CURRENT_NO := CURRENT_NO+1;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END INSERT_REPORT;

这段存储过程如何优化，才能使插入数据更快，性能更好呢？我想到使用类似JDBC中PreparedStatement，但是在存储过程中又不知如何操作。

[yjytiantang]

最好判断一下多少行commit一下。

[orbcle]

用批量提交，具体的业务你再完善一下

CREATE OR REPLACE PROCEDURE INSERT_REPORT(
                    FILE_NAME  VARCHAR2,
                    LINE_NO NUMBER) IS
   CURSOR CUR_FILE IS
     SELECT FILE_NAME,LEVEL FROM DUAL 
     CONNECT BY LEVEL <=LINE_NO;

   TYPE T_FILE_NAME IS TABLE OF VARCHAR2 INDEX BY PLS_INTEGER;
   TYPE T_ROW_NUM IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   
   TBL_FILE_NAME T_FILE_NAME;
   TBL_ROW_NUM T_ROW_NUM;
   V_BULK_CNT INTEGER:=10000;--自己定义多少行提交一次
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_USER_REPORT';
 
  OPEN CUR_FILE;
  LOOP 
  EXIT WHEN CUR_FILE%NOTFOUND;
  FETCH CUR_FILE BULK COLLECT INTO 
        TBL_FILE_NAME,
        TBL_ROW_NUM LIMIT V_BULK_CNT;
  BEGIN
  <<BULK_INSERT>>
  FORALL I IN INDICES OF TBL_FILE_NAME SAVE EXCEPTIONS
     INSERT/*+NOLOGGING*/ INTO T_USER_REPORT(FILENAME,LINENO,COLUMNNO,RESULTCODE) 
     VALUES(TBL_FILE_NAME(I),TBL_ROW_NUM(I),0,'00');
  COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
       ROLLBACK;
       RAISE_APPLICATION_ERROR(-20001,'ERROR...');
  END BULK_INSERT;
  END LOOP;
END INSERT_REPORT;

[碧水幽幽泉]

关注！

[studentaccp]

尽可能多的commit 多释放资源