📄 第五章 pl-sql集合与记录（2） - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<P>%FOUND和%NOTFOUND只是针对最后一次SQL语句执行的结果。但是，我们可以利用%BULK_ROWCOUNT来推断出每个单独语句的处理结果。当%BULK_ROWCOUNT(i)为零时，%FOUND和%NOTFOUND就分别为FALSE和TRUE。
</P>
<P class=title2>3、使用%BULK_EXCEPTIONS属性来控制FORALL异常</P>
<P>PL/SQL为FORALL语句提供了一个异常控制机制。这个机制能让使用批量绑定的操作保存异常信息并不中断地执行直至完成操作。</P>
<P>为了让批量绑定在错误发生时还能够继续执行，需要在FORALL语句中添加关键字SAVE
EXCEPTIONS，语法如下： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FORALL</STRONG> index <STRONG>IN</STRONG> lower_bound..upper_bound SAVE EXCEPTIONS<BR>{insert_stmt | update_stmt | delete_stmt}
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>执行时发生的所有的异常信息都会保存在新的游标属性%BULK_EXCEPTIONS中。%BULK_EXCEPTIONS是一个记录类型集合，每个记录有两个域，分别是ERROR_INDEX和ERROR_CODE，前者保存FORALL语句的"循环"索引，后者保存对应的Oracle错误编号。</P>
<P>存放在%BULK_EXCEPTIONS中的值总是与最近一次FORALL语句执行的结果相关，异常的个数存放在%BULK_EXCEPTIONS
的COUNT属性中，%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。</P>
<P>我们不使用关键字SAVE
EXCEPTIONS时，如果异常发生，FORALL语句就会停止执行，SQL%BULK_EXCEPTIONS.COUNT的值就是一，SQL%
BULK_EXCEPTIONS中只包含一条记录；如果没有异常发生，SQL%BULK_EXCEPTIONS.COUNT的值就是零。下面的例子演示了
%BULK_EXCEPTIONS的一些用法： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> numlist <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG>;<BR><BR> num_tab numlist := numlist(10, 0, 11, 12, 30, 0, 20, 199, 2, 0, 9, 1);<BR> ERRORS <STRONG>NUMBER</STRONG>;<BR> dml_errors <STRONG>EXCEPTION</STRONG>;<BR> <STRONG>PRAGMA</STRONG> EXCEPTION_INIT(dml_errors, -24381);<BR><STRONG>BEGIN</STRONG><BR> <STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> num_tab.FIRST .. num_tab.LAST SAVE EXCEPTIONS<BR> <STRONG>DELETE</STRONG> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> sal > 500000 / num_tab(i);<BR><STRONG>EXCEPTION</STRONG><BR> <STRONG>WHEN</STRONG> dml_errors <STRONG>THEN</STRONG><BR> ERRORS := <STRONG>SQL</STRONG>%BULK_EXCEPTIONS.COUNT;<BR> DBMS_OUTPUT.put_line(<EM>'Number of errors is '</EM> || ERRORS);<BR><BR> <STRONG>FOR</STRONG> i <STRONG>IN</STRONG> 1 .. ERRORS <STRONG>LOOP</STRONG><BR> DBMS_OUTPUT.put_line( <EM>'Error '</EM><BR> || i<BR> || <EM>' occurred during '</EM><BR> || <EM>'iteration '</EM><BR> || <STRONG>SQL</STRONG>%BULK_EXCEPTIONS(i).ERROR_INDEX);<BR> DBMS_OUTPUT.put_line( <EM>'Oracle error is '</EM><BR> || <STRONG>SQLERRM</STRONG>(-<STRONG>SQL</STRONG>%BULK_EXCEPTIONS(i).ERROR_CODE));<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>例子中，PL/SQL在i等于2、6、10时会抛出预定义异常ZERO_DIVIDE。当批量绑定完成时，SQL%
BULK_EXCEPTIONS.COUNT就会返回3，SQL%BULK_EXCEPTIONS的内容就是(2,1476)，(6,1476)和
(10,1476)。如果想得到错误消息，我们可以把SQL%BULK_EXCEPTIONS(i).ERROR_CODE传递给错误报告函数
SQLERRM，这样就能得到下面的输出结果： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>Number of errors is 3<BR>Error 1 occurred during iteration 2<BR>Oracle error is ORA-01476: divisor is equal to zero<BR>Error 2 occurred during iteration 6<BR>Oracle error is ORA-01476: divisor is equal to zero<BR>Error 3 occurred during iteration 10<BR>Oracle error is ORA-01476: divisor is equal to zero
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title1>十四、利用BULK COLLECTION子句为集合赋值</P>
<P>关键字BULK
COLLECT会通知SQL引擎在将数据返回给PL/SQL引擎之前，把输出的数据批量地绑定到一个集合。我们可以在SELECT
INTO、FETCH INTO和RETURNING INTO子句中使用BULK COLLECT。语法如下：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>... <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> collection_name[, collection_name] ...
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>SQL引擎能批量绑定出现在INTO列表后的所有集合。对应的字段可以保存为标量类型或复合类型的值，其中也包括对象类型。在下面的例子中，SQL引擎在把数据返回给PL/SQL引擎之前，它将完整的empno和ename绑定到嵌套表中：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> numtab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> emp.empno%<STRONG>TYPE</STRONG>;<BR><BR> <STRONG>TYPE</STRONG> nametab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> emp.ename%<STRONG>TYPE</STRONG>;<BR><BR> enums numtab; <EM>-- no need to initialize</EM><BR> names nametab;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> empno, ename<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> enums, names<BR> <STRONG>FROM</STRONG> emp;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>接下来的例子中，SQL引擎会批量地把对象字段的值放到嵌套表中： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> coords <STRONG>AS</STRONG> OBJECT(<BR> x <STRONG>NUMBER</STRONG>,<BR> y <STRONG>NUMBER</STRONG><BR>);<BR><BR><STRONG>CREATE</STRONG> <STRONG>TABLE</STRONG> grid (num <STRONG>NUMBER</STRONG>, loc coords);<BR><STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> grid<BR> <STRONG>VALUES</STRONG> (10, coords(1, 2));<BR><STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> grid<BR> <STRONG>VALUES</STRONG> (20, coords(3, 4));<BR><BR><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> coordstab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> coords;<BR><BR> pairs coordstab;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> loc<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> pairs<BR> <STRONG>FROM</STRONG> grid;<BR><EM> -- now pairs contains (1,2) and (3,4)</EM><BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>SQL引擎会为我们初始化和扩展集合(但是，它不能把变长数组的长度扩大到超过变长数组的最大长度值)。然后从索引1开始，连续地插入元素并覆盖先前已存在的元素。
</P>
<P>SQL引擎能批量绑定整个字段。所以，如果一个数据表中有50000行记录，引擎就会一次性加载50000个值到目标集合中去。但是，我们可以使用伪列ROWNUM来限制要处理的行记录个数。下例中，我们把每次处理的记录个数限制为100：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> sallist <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> emp.sal%<STRONG>TYPE</STRONG>;<BR><BR> sals sallist;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> sal<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> sals<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> <STRONG>ROWNUM</STRONG> <= 100;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>1、从游标中批量取得数据的例子</P>
<UL>
<LI>插入一个或多个集合 </LI></UL>
<P>我们可以从游标中批量取得数据并绑定到一个或多个集合中去： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> namelist <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> emp.ename%<STRONG>TYPE</STRONG>;<BR><BR> <STRONG>TYPE</STRONG> sallist <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> emp.sal%<STRONG>TYPE</STRONG>;<BR><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> ename, sal<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> sal > 1000;<BR><BR> names namelist;<BR> sals sallist;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> c1;<BR><BR> <STRONG>FETCH</STRONG> c1<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> names, sals;<BR><STRONG>END</STRONG>; </TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>绑定记录类型的集合 </LI></UL>
<P>我们可以批量取得数据并绑定到记录类型的集合中去： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> deptrectab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> dept%<STRONG>ROWTYPE</STRONG>;<BR><BR> dept_recs deptrectab;<BR><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> deptno, dname, loc<BR> <STRONG>FROM</STRONG> dept<BR> <STRONG>WHERE</STRONG> deptno > 10;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> c1;<BR><BR> <STRONG>FETCH</STRONG> c1<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> dept_recs;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>2、使用LIMIT子句限制FETCH操作批量取得的数据个数 </P>
<P>可选的LIMIT子句只允许出现在FETCH操作语句的批量中，它能够帮助我们限制批量取得的数据数量，语法如下：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FETCH</STRONG> ... <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> ... [LIMIT rows];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>其中rows可以是文字，变量或表达式，但它的计算结果必须是一个数字。否则的话，PL/SQL就会抛出预定义异常VALUE_ERROR。如果
rows是非正数，PL/SQL会抛出INVALID_NUMBER异常。在必要的时候，PL/SQL还会将数字四舍五入到rows最接近的整数。</P>
<P>在下面的例子中，每次FETCH操作都会取出10条记录放到索引表empno中去，之前的数据内容会被当前的数据所覆盖：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> numtab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG><BR> <STRONG>INDEX</STRONG> <STRONG>BY</STRONG> <STRONG>BINARY_INTEGER</STRONG>;<BR><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> empno<BR> <STRONG>FROM</STRONG> emp;<BR><BR> empnos numtab;<BR> ROWS <STRONG>NATURAL</STRONG> := 10;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> c1;<BR><BR> <STRONG>LOOP</STRONG><BR> <EM>/* The following statement fetches 10 rows (or less). */</EM><BR> <STRONG>FETCH</STRONG> c1<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> empnos LIMIT ROWS;<BR><BR> <STRONG>EXIT</STRONG> <STRONG>WHEN</STRONG> c1%NOTFOUND;<BR> ...<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <STRONG>CLOSE</STRONG> c1;<BR><STRONG>END</STRONG>;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -