PL/SQL 批量插入

• 批量插入

CREATE TABLE t(a INT CHECK(a < 3));

DECLARE
 TYPE t_list IS TABLE OF NUMBER(2);
 t_list_1 t_list := t_list(1,4,2,5,34,34,1,0,-1);
BEGIN 
  FORALL i IN 1..t_list_1.count SAVE EXCEPTIONS 
    INSERT INTO t VALUES (t_list_1(i));
  COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line( 'SQL%bulk_exceptions.COUNT: ' || SQL%bulk_exceptions.COUNT  ); 
    FOR i IN 1 .. SQL%bulk_exceptions.COUNT    
      LOOP  
         DBMS_OUTPUT.  
          put_line(  
                       'Record '  
                    || SQL%bulk_exceptions( i ).error_index  
                    || ' caused error '  
                    || i  
                    || ': '  
                    || SQL%bulk_exceptions( i ).error_code  
                    || ' '  
                    || SQLERRM( -SQL%bulk_exceptions( i ).error_code ) );  
      END LOOP;  
    ROLLBACK;
END;
/

• 失败结果记录

SQL%bulk_exceptions.COUNT: 4
Record 2 caused error 1: 2290 ORA-02290: check constraint (.) violated
Record 4 caused error 2: 2290 ORA-02290: check constraint (.) violated
Record 5 caused error 3: 2290 ORA-02290: check constraint (.) violated
Record 6 caused error 4: 2290 ORA-02290: check constraint (.) violated

• 使用DBMS_ERRLOG包存储插入失败记录

BEGIN
   DBMS_ERRLOG.create_error_log (dml_table_name => 't');
END;
/

DECLARE
 TYPE t_list IS TABLE OF NUMBER(2);
 t_list_1 t_list := t_list(1,4,2,5,34,34,1,0,-1);
 TYPE err IS TABLE OF err$_t%ROWTYPE;
 err_rec err;
 Write_file_name VARCHAR2(50);
 --file_handle utl_file.file_type;
 Write_content VARCHAR2(1024);
 file_handle utl_file.file_type;
BEGIN 
  EXECUTE IMMEDIATE 'delete from err$_t';
  FORALL i IN 1..t_list_1.count-- SAVE EXCEPTIONS 
    INSERT INTO t VALUES (t_list_1(i))
    LOG ERRORS REJECT LIMIT UNLIMITED;
  COMMIT;
  --COMMIT;
  --EXCEPTION
  --WHEN OTHERS THEN
  /*
  SELECT * BULK COLLECT INTO err_rec FROM err$_t;
  FOR i IN 1..err_rec.count LOOP
    --DBMS_OUTPUT.put_line( 'error - a: ' || err_rec(i).a  ); 
    write_file_name := to_char(SYSDATE,'YYYYMMDD')||'.txt';
    file_handle := utl_file.fopen('temp',write_file_name,'t');
    write_content := 'asdfaserqwer';
    --write file
    IF utl_file.is_open(file_handle) THEN
     utl_file.put_line(file_handle,write_content);
    END IF;
    --close file
    utl_file.fclose(file_handle);
  END LOOP;
  --DBMS_ERRLOG.create_error_log (dml_table_name => 't');
  */
END;
/