学步园

FORALL was introduced(介绍,引进的) in Oracle 8i as part of a set(一个集合中) of
new PL/SQLfeatures(特征) for bulk fetching(批量读取) and binding(绑定).
This short article(短文) demonstrates(证明,展示) the enhancements(增强) made
to FORALL in 10g. This articleassumes(呈现,假定) that readers are familiar(常见的,熟悉的) with
the concepts(概念) ofbulk(批量) PL/SQL and FORALL
in particular(详细说明,特别的) (see the further reading section(细节部分) of
this article for details of introductory(介绍) papers(文件)).

Note(注意) that for simplicity(为简单起见), I've used
the word(单词) "array" throughout(贯穿,遍及) this article as a collective term(统称) for
collections and associative arrays(关联数组) (PL/SQL table, index-by tables). In almost(几乎,差不多) all cases(案例,情况中),
they areinterchangeable(更换的) in bulk PL/SQL processing(处理).

setup

We'll begin with(开始于) a small table to serve(服务) as
the target for the FORALL examples in this article.

SQL> CREATE TABLE tgt ( id INT, val VARCHAR2(128) );

Table created.

indices of

The INDICES OF clause allows(条款允许) us to load non-contiguous(非连续的) (sparse(稀疏的))
arrays by telling(命令,告诉) Oracle to use just the elements that
are populated(填充的元素). Remember in versions prior(版本之前) to 10g that arrays had
to be(必须) dense(密集的)and we would use iterators such as [array.FIRST .. array.LAST] or [1 .. array.COUNT] to
address them(解决这些问题) (these are still(仍然) syntactically valid of course(课程的有效语法)). The INDICES OF clause is simple(简单的) to
use as seen in the example below(下面的例子).

SQL> DECLARE 2 3 TYPE aat_rec IS TABLE OF tgt%ROWTYPE 4 INDEX BY PLS_INTEGER; 5 aa_recs aat_rec; 6 7 BEGIN 8 9 10 SELECT object_id, object_name BULK COLLECT INTO aa_recs 11 FROMall_objects 12 WHERE ROWNUM <= 10; 13 14 aa_recs.DELETE(2); 15 aa_recs.DELETE(4); 16aa_recs.DELETE(6); 17 18 19 FORALL i IN INDICES OF aa_recs 20 INSERT INTO tgt 21 VALUESaa_recs(i); 22 23 DBMS_OUTPUT.PUT_LINE( 24 TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.' 25 ); 2627 END; 28 /

7 rows inserted. PL/SQL procedure successfully completed.

Note that the array used in the INDICES OF clause does not necessarily(不一定) have to be the one that is being
loaded(正在加载). Like all versions of FORALL, it is simply a driver to tell Oracle the indices(目录) to use in any arrays referenced in the subsequent(随后的,后来的) DML
statement. We can demonstrate(证明,展示) this quite easily(很容易)
as follows(如下).

SQL> DECLARE 2 3 TYPE aat_rec IS TABLE OF tgt%ROWTYPE 4 INDEX BY PLS_INTEGER; 5 aa_recs aat_rec; 6 7 TYPE aat_id IS TABLE OF PLS_INTEGER 8 INDEX BY PLS_INTEGER; 9 aa_ids aat_id; 1011 BEGIN 12 13 14 SELECT object_id, object_name BULK COLLECT INTO aa_recs 15 FROM all_objects16 WHERE ROWNUM <= 10; 17 18 19 aa_ids(1) := NULL; --value of element is irrelevant 20aa_ids(3) := NULL; 21 aa_ids(5) := NULL; 22 23 24 FORALL i IN INDICES OF aa_ids 25 INSERT INTOtgt 26 VALUES aa_recs(i); 27 28 DBMS_OUTPUT.PUT_LINE( 29 TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.' 30 ); 31 32 END; 33 /

3 rows inserted. PL/SQL procedure successfully completed.

Note that in the previous(早先的,以前的) example, we were only interested in the indices(指标)of
the driving(操纵,驾驶) array. The values within each element were irrelevant(无关系,不相干的);
so irrelevant in fact, that we didn't even give them a value.

values of

The VALUES OF enables(使) us to load just the elements of a data array where the indicesmatch(匹配) the values of
a driving array (i.e. the data within the elements). In allprobability(可能性) this will be used far
less(远低于) than the INDICES OF clause, but it isworth(值,价值) covering
here(覆盖这里). The following example shows how we might(可能) load from the values within the elements of a driving array.

SQL> DECLARE 2 3 TYPE aat_id IS TABLE OF PLS_INTEGER 4 INDEX BY PLS_INTEGER; 5 aa_ids aat_id;6 7 TYPE aat_rec IS TABLE OF tgt%ROWTYPE 8 INDEX BY PLS_INTEGER; 9 aa_recs aat_rec; 10 11BEGIN 12 13 17 aa_ids(1) := 3; 18 aa_ids(2) := 8; 19 aa_ids(3) := 10; 20 21 22 SELECT ROWNUM,object_name BULK COLLECT INTO aa_recs 23 FROM all_objects 24 WHERE ROWNUM <= 20; 25 26 30FORALL i IN VALUES OF aa_ids 31 INSERT INTO tgt 32 VALUES aa_recs(i); 33 34DBMS_OUTPUT.PUT_LINE( 35 TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.' 36 ); 37 38 END; 39 /

3 rows inserted. PL/SQL procedure successfully completed.

Now we can look at the data in the table to see that it is elements 3,8,10 that were loaded (i.e. the values in our driving array) and not the elements 1,2,3 (i.e. the indices of the driving array elements). We can see this because we fetched(取来,取得) ROWNUM
into the ID attribute of the data array.

SQL> SELECT * FROM tgt;

ID VAL ---------- ----------------------------------- 3 SYSTEM_PRIVILEGE_MAP 8 STMT_AUDIT_OPTION_MAP 10 RE$NV_LIST 3 rows selected.

exceptions

The following is a small example of the exception we can expect if(想象,如果) an index or value in the "driving" array doesn't exist in the "data" array. We'll contrive a driving array with an index beyond the bounds of the data array and then try to load our target
table.

SQL> DECLARE 2 3 TYPE aat_rec IS TABLE OF tgt%ROWTYPE 4 INDEX BY PLS_INTEGER; 5 aa_recs aat_rec; 6 7 TYPE aat_id IS TABLE OF PLS_INTEGER 8 INDEX BY PLS_INTEGER; 9 aa_ids aat_id; 1011 BEGIN 12 13 14 SELECT object_id, object_name BULK COLLECT INTO aa_recs 15 FROM all_objects16 WHERE ROWNUM <= 10; 17 18 19 aa_ids(100) := NULL; --value irrelevant here 20 21 22 FORALL iIN INDICES OF aa_ids 23 INSERT INTO tgt 24 VALUES aa_recs(i); 25 26 EXCEPTION 27 WHEN OTHERSTHEN 28 DBMS_OUTPUT.PUT_LINE(SQLERRM); 29 END; 30 /

ORA-22160: element at index [100] does not exist PL/SQL procedure successfully completed.

Usefully, Oracle tells us the index it was trying to reference. The same exception and output would also be raised if we were to use the VALUES OF clause.

further reading

For further reading on FORALL and its evolution since 8i, see the oracle-developer.net introductory articles on PL/SQL bulk-processing in Oracle
8i and Oracle 9i.

source code

The source code for the examples in this article can be downloaded from here.