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

本章内容：

1. FORALL语句

2. BULK COLLECT语句

3. 在SQL语句中绑定集合

 

代码如下：

1. 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
 11  BEGIN
 12     -- Populate collections
 13     FOR i IN 1..10
 14     LOOP
 15        row_num_tab(i)  := i;
 16        row_text_tab(i) := 'row '||i;
 17     END LOOP;
 18
 19     -- Populate TEST table
 20     FORALL i IN 1..10
 21        INSERT INTO test (row_num, row_text)
 22        VALUES (row_num_tab(i), row_text_tab(i));
 23
 24     COMMIT;
 25
 26     -- Check how many rows where inserted in the TEST table
 27     -- display it on the screen
 28     SELECT COUNT(*)
 29       INTO v_rows
 30       FROM TEST;
 31
 32     DBMS_OUTPUT.PUT_LINE ('There are '||v_rows||' rows in the TEST table');
 33  END;
 34  /
There are 10 rows in the TEST table

性能比较：

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_start_time INTEGER;
 10     v_end_time   INTEGER;
 11
 12  BEGIN
 13     -- Populate collections
 14     FOR i IN 1..1000
 15     LOOP
 16        row_num_tab(i)  := i;
 17        row_text_tab(i) := 'row '||i;
 18     END LOOP;
 19
 20     -- Record start time
 21     v_start_time := DBMS_UTILITY.GET_TIME;
 22
 23     -- Insert first 100 rows
 24     FOR i IN 1..1000
 25     LOOP
 26        INSERT INTO test (row_num, row_text)
 27        VALUES (row_num_tab(i), row_text_tab(i));
 28     END LOOP;
 29
 30     -- Record end time
 31     v_end_time := DBMS_UTILITY.GET_TIME;
 32
 33     -- Calculate and display elapsed time
 34     DBMS_OUTPUT.PUT_LINE ('Duration of the FOR LOOP: '||
 35        (v_end_time - v_start_time));
 36
 37     -- Record start time
 38     v_start_time := DBMS_UTILITY.GET_TIME;
 39
 40     -- Insert second 100 rows
 41     FORALL i IN 1..1000
 42        INSERT INTO test (row_num, row_text)
 43        VALUES (row_num_tab(i), row_text_tab(i));
 44
 45     -- Record end time
 46     v_end_time := DBMS_UTILITY.GET_TIME;
 47
 48     -- Calculate and display elapsed time
 49     DBMS_OUTPUT.PUT_LINE ('Duration of the FORALL statement: '||
 50        (v_end_time-v_start_time));
 51
 52     COMMIT;
 53  END;
 54  /
Duration of the FOR LOOP: 3
Duration of the FORALL statement: 0