FORALL

FORALL wasintroduced(介绍,引进的)in Oracle8ias part of a set(一个集合中)of new PL/SQLfeatures(特征)forbulk fetching(批量读取)andbinding(绑定). Thisshort article(短文)<wbr><strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">demonstrates(证明,展示)</span></strong>the<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">enhancements(增强)</span></strong>made to FORALL in 10g. This article<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">assumes(呈现,假定)</span></strong>that readers are<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">familiar(常见的,熟悉的)</span></strong>with the<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">concepts(概念)</span></strong>of<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">bulk(批量)</span></strong>PL/SQL and FORALL in<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">particular(详细说明,特别的)</span></strong>(see the<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">further reading section(细节部分)</span></strong>of this article for details of<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">introductory(介绍) papers(文件)</span></strong>).</wbr>

Note(注意)thatfor simplicity(为简单起见), I've used the word(单词) "array"throughout(贯穿,遍及)this article as acollective<wbr><strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">term(统称)</span><wbr></wbr></strong>for collections and<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">associative arrays(关联数组)</span></strong>(PL/SQL table, index-by tables). In<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">almost(几乎,差不多)</span></strong>all<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">cases(案例,情况中)</span></strong>, they are<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">interchangeable(更换的)</span></strong>in bulk PL/SQL<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">processing(处理)</span></strong>.</wbr>

setup

We'llbegin with(开始于)a small table toserve(服务)as the target for the FORALL examples in this article.

SQL>CREATETABLEtgt(idINT,valVARCHAR2(128));

Table created.

indices of

The INDICES OFclause allows(条款允许)us to loadnon-contiguous(非连续的)(sparse(稀疏的)) arrays bytelling(命令,告诉)Oracle to use just theelements that are populated(填充的元素). Remember inversions prior(版本之前)to 10g that arrayshad to be(必须)<wbr><strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">dense(密集的)</span></strong>and we would use iterators such as [array.FIRST .. array.LAST] or [1 .. array.COUNT]<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">to address them(解决这些问题)</span></strong>(these are still(仍然)<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">syntactically valid of course(课程的有效语法)</span></strong>). The INDICES OF clause is<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">simple(简单的)</span></strong>to use as seen in the<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">example below(下面的例子)</span></strong>.</wbr>

SQL>DECLARE<wbr></wbr>23TYPEaat_recISTABLEOFtgt%ROWTYPE4INDEXBYPLS_INTEGER;5aa_recs aat_rec;67BEGIN8910SELECTobject_id,object_nameBULKCOLLECTINTOaa_recs11FROMall_objects12WHEREROWNUM<=10;1314aa_recs.DELETE(2);15aa_recs.DELETE(4);16aa_recs.DELETE(6);171819FORALLiININDICESOFaa_recs20INSERTINTOtgt21VALUESaa_recs(i);2223DBMS_OUTPUT.PUT_LINE(24TO_CHAR(SQL%ROWCOUNT)||' rows inserted.'25);2627END;28/

7 rows inserted. PL/SQL procedure successfully completed.

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

SQL>DECLARE23TYPEaat_recISTABLEOFtgt%ROWTYPE4INDEXBYPLS_INTEGER;5aa_recs aat_rec;67TYPEaat_idISTABLEOFPLS_INTEGER8INDEXBYPLS_INTEGER;9aa_ids aat_id;1011BEGIN121314SELECTobject_id,object_nameBULKCOLLECTINTOaa_recs15FROMall_objects16WHEREROWNUM<=10;171819aa_ids(1):=NULL;--value of element is irrelevant20aa_ids(3):=NULL;21aa_ids(5):=NULL;222324FORALLiININDICESOFaa_ids25INSERTINTOtgt26VALUESaa_recs(i);2728DBMS_OUTPUT.PUT_LINE(29TO_CHAR(SQL%ROWCOUNT)||' rows inserted.'30);3132END;33/

3 rows inserted. PL/SQL procedure successfully completed.

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

values of

The VALUES OFenables(使)us to load just the elements of a data array where the indicesmatch(匹配)the<wbr><strong>values</strong><wbr>of a driving array (i.e. the data within the elements). In all<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">probability(可能性)</span></strong>this will be used<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">far less(远低于)</span></strong>than the INDICES OF clause, but it is<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">worth(值,价值)</span></strong><wbr><strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">covering here(覆盖这里)</span></strong>. The following example shows how we<strong><span style="color:#ED1C24; word-wrap:normal; word-break:normal; line-height:21px">might(可能)</span></strong>load from the values within the elements of a driving array.</wbr></wbr></wbr>

SQL>DECLARE23TYPEaat_idISTABLEOFPLS_INTEGER4INDEXBYPLS_INTEGER;5aa_ids aat_id;67TYPEaat_recISTABLEOFtgt%ROWTYPE8INDEXBYPLS_INTEGER;9aa_recs aat_rec;1011BEGIN121317aa_ids(1):=3;18aa_ids(2):=8;19aa_ids(3):=10;202122SELECTROWNUM,object_nameBULKCOLLECTINTOaa_recs23FROMall_objects24WHEREROWNUM<=20;252630FORALLiINVALUESOFaa_ids31INSERTINTOtgt32VALUESaa_recs(i);3334DBMS_OUTPUT.PUT_LINE(35TO_CHAR(SQL%ROWCOUNT)||' rows inserted.'36);3738END;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 wefetched(取来,取得)ROWNUM into the ID attribute of the data array.

SQL>SELECT*FROMtgt;

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>DECLARE23TYPEaat_recISTABLEOFtgt%ROWTYPE4INDEXBYPLS_INTEGER;5aa_recs aat_rec;67TYPEaat_idISTABLEOFPLS_INTEGER8INDEXBYPLS_INTEGER;9aa_ids aat_id;1011BEGIN121314SELECTobject_id,object_nameBULKCOLLECTINTOaa_recs15FROMall_objects16WHEREROWNUM<=10;171819aa_ids(100):=NULL;--value irrelevant here202122FORALLiININDICESOFaa_ids23INSERTINTOtgt24VALUESaa_recs(i);2526EXCEPTION27WHENOTHERSTHEN28DBMS_OUTPUT.PUT_LINE(SQLERRM);29END;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<wbr><a href="http://www.oracle-developer.net/display.php?id=101" style="text-decoration:none; color:rgb(65,100,111)">Oracle 8i</a><wbr>and<wbr><a href="http://www.oracle-developer.net/display.php?id=201" style="text-decoration:none; color:rgb(65,100,111)">Oracle 9i</a>.</wbr></wbr></wbr>

source code

The source code for the examples in this article can be downloaded from<wbr><a href="http://www.oracle-developer.net/content/code/308.zip" style="text-decoration:none; color:rgb(65,100,111)">here</a>.</wbr>