《Oracle PL/SQL实例精讲》学习笔记19——批量SQL（第二部分）

本章内容：

1. FORALL语句（使用FORALL语句、SAVE EXCEPTIONS选项、INDICES OF选项、VALUES OF选项）

2. BULK COLLECT语句

3. 在SQL语句中绑定集合

基本概念：

1. SAVE EXCEPTIONS选项

The SAVE EXCEPTIONS option enables the FORALL statement to continue even when a  corresponding SQL statement causes an exception. These exceptions are stored in the  cursor attribute called SQL%BULK_EXCEPTIONS. The SQL%BULK_EXCEPTIONS  attribute is a collection of records in which each record consists of two fields,  ERROR_INDEX and ERROR_CODE. The ERROR_INDEX field stores the number of the  iteration of the FORALL statement during which an exception was encountered, and the  ERROR_CODE stores the Oracle error code corresponding to the raised exception.  The number of exceptions that occurred during the execution of the FORALL statement  can be retrieved via SQL%BULK_EXCEPTIONS.COUNT. Although the individual error  messages are not saved, they can be looked up via the SQLERRM function.  The following example uses a FORALL statement with the SAVE EXCEPTIONS  option.

2. INDICES OF选项

 The INDICES OF option enables you to loop through a sparse  collection. Recall that such collection may be a nested table or an associative array. The  use of the INDICES OF option is illustrated in the following example.

3. VALUES OF选项

The VALUES OF option specifies that the values of the loop counter in the FORALL  statement are based on the values of the elements of the specified collection. Essentially,  this collection is a group of indices that the FORALL statement can loop through.  Furthermore, these indices do not need to be unique and can be listed in arbitrary order.  The following example demonstrates the use of the VALUES OF option.

代码如下：

1. 使用带SAVE EXCEPTIONS选项的FORALL语句

SQL> DECLARE
  2     -- Define collection types and variables
  3     TYPE row_num_type  IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;
  4     TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
  5
  6     row_num_tab  row_num_type;
  7     row_text_tab row_text_type;
  8
  9     -- Define user-defined exception and associated Oracle
 10     -- error number with it
 11     errors EXCEPTION;
 12     PRAGMA EXCEPTION_INIT(errors, -24381);
 13
 14     v_rows NUMBER;
 15  BEGIN
 16     -- Populate collections
 17     FOR i IN 1..10
 18     LOOP
 19        row_num_tab(i)  := i;
 20        row_text_tab(i) := 'row '||i;
 21     END LOOP;
 22
 23     -- Modify 1, 5, and 7 elements of the V_ROW_TEXT collection
 24     -- These rows will cause exception in the FORALL statement
 25     row_text_tab(1) := RPAD(row_text_tab(1), 11, ' ');
 26     row_text_tab(5) := RPAD(row_text_tab(5), 11, ' ');
 27     row_text_tab(7) := RPAD(row_text_tab(7), 11, ' ');
 28
 29
 30     -- Populate TEST table
 31     FORALL i IN 1..10 SAVE EXCEPTIONS
 32        INSERT INTO test (row_num, row_text)
 33        VALUES (row_num_tab(i), row_text_tab(i));
 34     COMMIT;
 35
 36  EXCEPTION
 37     WHEN errors
 38     THEN
 39        -- Display total number of records inserted in the TEST table
 40        SELECT count(*)
 41          INTO v_rows
 42          FROM test;
 43
 44        DBMS_OUTPUT.PUT_LINE ('There are '||v_rows||' records in the TEST table');
 45
 46        -- Display total number of exceptions encountered
 47        DBMS_OUTPUT.PUT_LINE ('There were '||SQL%BULK_EXCEPTIONS.COUNT||' exceptions');
 48
 49        -- Display detailed exception information
 50        FOR i in 1.. SQL%BULK_EXCEPTIONS.COUNT LOOP
 51           DBMS_OUTPUT.PUT_LINE ('Record '||
 52              SQL%BULK_EXCEPTIONS(i).error_index||' caused error '||i||': '||
 53              SQL%BULK_EXCEPTIONS(i).error_code||' '||
 54              SQLERRM(-SQL%BULK_EXCEPTIONS(i).error_code));
 55        END LOOP;
 56  END;
 57  /
There are 7 records in the TEST table
There were 3 exceptions
Record 1 caused error 1: 12899 ORA-12899: value too large for column  (actual: ,
maximum: )
Record 5 caused error 2: 12899 ORA-12899: value too large for column  (actual: ,
maximum: )
Record 7 caused error 3: 12899 ORA-12899: value too large for column  (actual: ,
maximum: )

2. 使用带INDICES OF选项的FORALL语句

SQL> DECLARE
  2     -- Define collection types and variables
  3     TYPE row_num_type  IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;
  4     TYPE row_text_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  5
  6     row_num_tab  row_num_type;
  7     row_text_tab row_text_type;
  8
  9     v_rows NUMBER;
 10  BEGIN
 11     -- Populate collections
 12     FOR i IN 1..10
 13     LOOP
 14        row_num_tab(i)  := i;
 15        row_text_tab(i) := 'row '||i;
 16     END LOOP;
 17
 18     -- Delete 1, 5, and 7 elements of collections
 19     row_num_tab.DELETE(1); row_text_tab.DELETE(1);
 20     row_num_tab.DELETE(5); row_text_tab.DELETE(5);
 21     row_num_tab.DELETE(7); row_text_tab.DELETE(7);
 22
 23     -- Populate TEST table
 24     FORALL i IN INDICES OF row_num_tab
 25        INSERT INTO test (row_num, row_text)
 26        VALUES (row_num_tab(i), row_text_tab(i));
 27     COMMIT;
 28
 29     SELECT COUNT(*)
 30       INTO v_rows
 31       FROM test;
 32
 33     DBMS_OUTPUT.PUT_LINE ('There are '||v_rows||' rows in the TEST table');
 34  END;
 35  /
There are 7 rows in the TEST table

3. 使用带VALUES OF选项

 

-- For Example ch18_5a.sql
CREATE TABLE TEST_EXC
   (row_num  NUMBER
   ,row_text VARCHAR2(50));

TRUNCATE TABLE TEST;

DECLARE
   -- Define collection types and variables
   TYPE row_num_type  IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;
   TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
   TYPE exc_ind_type  IS TABLE OF PLS_INTEGER  INDEX BY PLS_INTEGER;

   row_num_tab  row_num_type;
   row_text_tab row_text_type;
   exc_ind_tab  exc_ind_type;

   -- Define user-defined exception and associated Oracle 
   -- error number with it
   errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(errors, -24381);

BEGIN
   -- Populate collections
   FOR i IN 1..10 
   LOOP
      row_num_tab(i)  := i;
      row_text_tab(i) := 'row '||i;
   END LOOP;

   -- Modify 1, 5, and 7 elements of the ROW_TEXT_TAB collection
   -- These rows will cause exception in the FORALL statement
   row_text_tab(1) := RPAD(row_text_tab(1), 11, ' ');
   row_text_tab(5) := RPAD(row_text_tab(5), 11, ' ');
   row_text_tab(7) := RPAD(row_text_tab(7), 11, ' ');

   -- Populate TEST table
   FORALL i IN 1..10 SAVE EXCEPTIONS
      INSERT INTO test (row_num, row_text)
      VALUES (row_num_tab(i), row_text_tab(i));
   COMMIT;

EXCEPTION
   WHEN errors 
   THEN
      -- Populate EXC_IND_TAB collection to be used in the VALUES OF 
      -- clause
      FOR i in 1..SQL%BULK_EXCEPTIONS.COUNT 
      LOOP 
         exc_ind_tab(i) := SQL%BULK_EXCEPTIONS(i).error_index;
      END LOOP;

      -- Insert records that caused exceptions in the TEST_EXC table
      FORALL i in VALUES OF exc_ind_tab
         INSERT INTO test_exc (row_num, row_text)
         VALUES (row_num_tab(i), row_text_tab(i));
   COMMIT;
END;

select * from test

select * from test_exc