plsql学习:批量 SQL

-- THE INDICES OF OPTION
--     As stated previously, the INDICES OF option enables you to loop through a sparse collection.
-- Recall that such a collection may be a nested table or an associative array. The use of the
-- INDICES OF option is illustrated in the following example:
----------------------------------------------------------------------------
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(10)  index by pls_integer;

   row_num_tab row_num_type;
   row_text_tab row_text_type;

   v_total number;
begin
  --populate collections
  for i in 1..10 loop
    row_num_tab(i) := i;
    row_text_tab(i) := 'row '||i;
  end loop;

  --delete 1,5, and 7 elements of collections
  row_num_tab.delete(1);row_text_tab.delete(1);
  row_num_tab.delete(5);row_text_tab.delete(5);
  row_num_tab.delete(7);row_text_tab.delete(7);

  --populate test table
  forall i in indices of row_num_tab
     insert into test (row_num,row_text)
     values(row_num_tab(i),row_text_tab(i));
  commit;

  select count(*) into v_total from test;

  dbms_output.put_line('there are'||v_total||'rows in the test table');
end;

======================================================================
--  THE VALUES OF OPTION
--  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 indexes that the FORALL statement can loop through. Furthermore, these indexes
--  do not need to be unique and can be listed in arbitrary order. The following example demonstrates
--  the use of the VALUES OF option:


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 V_ROW_TEXT 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 V_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;
#   ROW_NUM ROW_TEXT
#   ---------- ----------
#   2 row 2
#   3 row 3
#   4 row 4
#   6 row 6
#   8 row 8
#   9 row 9
#   10 row 10
#   
#   7 rows selected.
#   select * from test_exc;
#   ROW_NUM ROW_TEXT
#   ---------- --------------------------------------------------
#   1 row 1
#   5 row 5
#   7 row 7
#############################################