📄 第五章 pl-sql集合与记录（2） - pl-sql用户指南与参考 - whatiswhat.htm
字号:
DELETE语句使用批量绑定，就要用PL/SQL的FORALL语句。</P>
<P>如果要在SELECT语句中使用批量绑定，我们就要在SELECT语句后面加上一个BULK
COLLECT子句来代替INTO子句。 </P>
<UL>
<LI>例一：对DELETE语句应用批量绑定 </LI></UL>
<P>下面的DELETE语句只往SQL引擎中发送一次，即使是执行了三次DELETE操作：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> numlist <STRONG>IS</STRONG> VARRAY(20) <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG>;<BR><BR> depts numlist := numlist(10, 30, 70); <EM>-- department numbers</EM><BR><STRONG>BEGIN</STRONG><BR> <STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> depts.FIRST .. depts.LAST<BR> <STRONG>DELETE</STRONG> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> deptno = depts(i);<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>例二：对INSERT语句应用批量绑定 </LI></UL>
<P>下例中，我们把5000个零件编号和名称放到索引表中。所有的表元素都向数据库插入两次：第一次使用FOR循环，然后使用FORALL语句。实际上，FORALL版本的代码执行速度要比FOR语句版本的快得多。</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>SQL</STRONG>> <STRONG>SET</STRONG> SERVEROUTPUT <STRONG>ON</STRONG><BR><STRONG>SQL</STRONG>> <STRONG>CREATE</STRONG> <STRONG>TABLE</STRONG> parts (pnum <STRONG>NUMBER</STRONG>(4), pname <STRONG>CHAR</STRONG>(15));<BR>Table created.<BR><STRONG>SQL</STRONG>> GET test.sql<BR>1 <STRONG>DECLARE</STRONG><BR>2 <STRONG>TYPE</STRONG> NumTab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG>(4) <STRONG>INDEX</STRONG> <STRONG>BY</STRONG> <STRONG>BINARY_INTEGER</STRONG>;<BR>3 <STRONG>TYPE</STRONG> NameTab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>CHAR</STRONG>(15) <STRONG>INDEX</STRONG> <STRONG>BY</STRONG> <STRONG>BINARY_INTEGER</STRONG>;<BR>4 pnums NumTab;<BR>5 pnames NameTab;<BR>6 t1 <STRONG>NUMBER</STRONG>(5);<BR>7 t2 <STRONG>NUMBER</STRONG>(5);<BR>8 t3 <STRONG>NUMBER</STRONG>(5);<BR>9<BR>10<BR>11 <STRONG>BEGIN</STRONG><BR>12 <STRONG>FOR</STRONG> j <STRONG>IN</STRONG> 1..5000 <STRONG>LOOP</STRONG> <EM>-- load index-by tables</EM><BR>13 pnums(j) := j;<BR>14 pnames(j) := <EM>'Part No. '</EM> || TO_CHAR(j);<BR>15 <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR>16 t1 := dbms_utility.get_time;<BR>17 <STRONG>FOR</STRONG> i <STRONG>IN</STRONG> 1..5000 <STRONG>LOOP</STRONG> <EM>-- use FOR loop</EM><BR>18 <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> parts <STRONG>VALUES</STRONG> (pnums(i), pnames(i));<BR>19 <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR>20 t2 := dbms_utility.get_time;<BR>21 <STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> 1..5000 <EM>-- use FORALL statement</EM><BR>22 <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> parts <STRONG>VALUES</STRONG> (pnums(i), pnames(i));<BR>23 get_time(t3);<BR>24 dbms_output.put_line(<EM>'Execution Time (secs)'</EM>);<BR>25 dbms_output.put_line(<EM>'---------------------'</EM>);<BR>26 dbms_output.put_line(<EM>'FOR loop: '</EM> || TO_CHAR(t2 - t1));<BR>27 dbms_output.put_line(<EM>'FORALL: '</EM> || TO_CHAR(t3 - t2));<BR>28* <STRONG>END</STRONG>;<BR><STRONG>SQL</STRONG>> /<BR>Execution Time (secs)<BR><EM>---------------------</EM><BR>FOR loop: 32<BR>FORALL: 3<BR></TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title1>十三、使用FORALL语句</P>
<P>关键字FORALL能让PL/SQL引擎在将集合发送到SQL引擎之前，批量导入集合元素。虽然FORALL也包含了迭代的模式，但它并不是简单的FOR循环。它的使用语法如下：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FORALL</STRONG> index <STRONG>IN</STRONG> lower_bound..upper_bound<BR> sql_statement;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>index只能在FORALL语句块内作为集合下标使用。SQL语句必须是引用了集合元素的INSERT、UPDATE或DELETE语句。bound的有效范围是连续的索引号。在这个范围内，SQL引擎为每个索引号执行一次SQL语句。</P>
<UL>
<LI>例一：使用FORALL操作集合的部分内容 </LI></UL>
<P>如下例所示，FORALL循环的边界值可作用于集合的部分内容，不必是全部的元素：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> numlist <STRONG>IS</STRONG> VARRAY(10) <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG>;<BR><BR> depts numlist := numlist(20, 30, 50, 55, 57, 60, 70, 75, 90, 92);<BR><STRONG>BEGIN</STRONG><BR> <STRONG>FORALL</STRONG> j <STRONG>IN</STRONG> 4 .. 7 <EM>-- bulk-bind only part of varray</EM><BR> <STRONG>UPDATE</STRONG> emp<BR> <STRONG>SET</STRONG> sal = sal * 1.10<BR> <STRONG>WHERE</STRONG> deptno = depts(j);<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>例二：使用集合下标索引的批量绑定 </LI></UL>
<P>SQL语句能引用一个以上的集合。但是PL/SQL引擎的批量绑定只能为一个集合添加下标索引。所以，在下面的例子中，对于传递给函数median的集合sals，并没有使用到批量绑定。</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> 1..20<BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp2 <STRONG>VALUES</STRONG> (enums(i), names(i), median(sals), ...);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>例三：使用FORALL为对象表添加数据 </LI></UL>
<P>除了关系表之外，FORALL语句还可以操作对象表，如下例所示：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> pnum <STRONG>AS</STRONG> OBJECT(<BR> n <STRONG>NUMBER</STRONG><BR>);<BR>/<BR><BR><STRONG>CREATE</STRONG> <STRONG>TABLE</STRONG> partno <STRONG>OF</STRONG> pnum;<BR><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> numtab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG>;<BR><BR> nums numtab := numtab(1, 2, 3, 4);<BR><BR> <STRONG>TYPE</STRONG> pnumtab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> pnum;<BR><BR> pnums pnumtab := pnumtab(pnum(1), pnum(2), pnum(3), pnum(4));<BR><STRONG>BEGIN</STRONG><BR> <STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> pnums.FIRST .. pnums.LAST<BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> partno<BR> <STRONG>VALUES</STRONG> (pnums(i));<BR> <STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> nums.FIRST .. nums.LAST<BR> <STRONG>DELETE</STRONG> <STRONG>FROM</STRONG> partno<BR> <STRONG>WHERE</STRONG> n = 2 * nums(i);<BR> <STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> nums.FIRST .. nums.LAST<BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> partno<BR> <STRONG>VALUES</STRONG> (100 + nums(i));<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>1、FORALL语句对回滚的影响</P>
<P>在FORALL语句中，如果SQL语句引起了一个未捕获异常，以前对数据库的所有操作都会被回滚。但是，如果我们捕获到被抛出的异常并加以处理，此次之前的操作就不会被回滚。举一个例子，假设我们创建了数据表用来存储部门编号和职别：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TABLE</STRONG> emp2 (deptno <STRONG>NUMBER</STRONG>(2), job <STRONG>VARCHAR2</STRONG>(15));
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>下一步，为刚才建立的数据表添加一些记录：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp2 <STRONG>VALUES</STRONG>(10, <EM>'Clerk'</EM>);<BR><STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp2 <STRONG>VALUES</STRONG>(10, <EM>'Clerk'</EM>);<BR><STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp2 <STRONG>VALUES</STRONG>(20, <EM>'Bookkeeper'</EM>); <EM>-- 10-char job title</EM><BR><STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp2 <STRONG>VALUES</STRONG>(30, <EM>'Analyst'</EM>);<BR><STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp2 <STRONG>VALUES</STRONG>(30, <EM>'Analyst'</EM>);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>然后，我们用下面的UPDATE语句为特定的职称加上七位字符串' (temp)'：</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> depts numlist := numlist(10, 20, 30);<BR><STRONG>BEGIN</STRONG><BR> <STRONG>FORALL</STRONG> j <STRONG>IN</STRONG> depts.FIRST .. depts.LAST<BR> <STRONG>UPDATE</STRONG> emp2<BR> <STRONG>SET</STRONG> job = job || <EM>' (temp)'</EM><BR> <STRONG>WHERE</STRONG> deptno = depts(j);<BR> <EM>-- raises a "value too large" exception</EM><BR><STRONG>EXCEPTION</STRONG><BR> <STRONG>WHEN</STRONG> <STRONG>OTHERS</STRONG> <STRONG>THEN</STRONG><BR> <STRONG>COMMIT</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>SQL引擎会执行UPDATE语句三次，第一次成功，但在第二次会因字符串值'Bookkeeper
(temp)'太长而无法赋给job字段，所以就会执行失败。这种情况下，只有第二条语句回滚。</P>
<P>只要有SQL语句抛出异常，FORALL语句就会终止执行。在上面的例子中，第二个UPDATE语句抛出了异常，第三个语句就不会被执行了。</P>
<P
class=title2>2、使用%BULK_ROWCOUNT属性来计算FORALL语句所影响到的行数</P>
<P>处理SQL数据操作语句时，SQL引擎会隐式地打开一个名为SQL的游标。这个游标的标量属性%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT，能够提供最近一次执行的SQL数据操作语句信息。</P>
<P>SQL游标还有一个专门为FORALL设计的复合属性%BULK_ROWCOUNT。这个属性有些像索引表。它的第i个元素保存了第i次的
INSERT或UPDATE或DELETE语句所影响到的行数。如果第i次操作没有行被影响，%BULK_ROWCOUNT(i)就返回零。下面来看一个例子：</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> depts numlist := numlist(10, 20, 50);<BR><STRONG>BEGIN</STRONG><BR> <STRONG>FORALL</STRONG> j <STRONG>IN</STRONG> depts.FIRST .. depts.LAST<BR> <STRONG>UPDATE</STRONG> emp<BR> <STRONG>SET</STRONG> sal = sal * 1.10<BR> <STRONG>WHERE</STRONG> deptno = depts(j);<BR> <EM>-- Did the 3rd UPDATE statement affect any rows?</EM><BR> <STRONG>IF</STRONG> <STRONG>SQL</STRONG>%BULK_ROWCOUNT(3) = 0 <STRONG>THEN</STRONG> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>FORALL语句和%BULK_ROWCOUNT属性使用同样的下标索引。如果FORALL使用下标索引的范围在5到10的话，那么%
BULK_ROWCOUNT的也是。对于插入操作来说，%BULK_ROWCOUNT的结果一般是1，但是对于INSERT
...
SELECT这样的结构来说，%BULK_ROWCOUNT的值就有可能大于1。例如，下面的FORALL语句在循环插入数据的过程中，每次插入的行的个数都是不固定的，%BULK_ROWCOUNT可以记录每次插入的行数：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>SET</STRONG> SERVEROUTPUT <STRONG>ON</STRONG>;<BR><BR><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> num_tab <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG>;<BR><BR> deptnums num_tab;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> deptno<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> deptnums<BR> <STRONG>FROM</STRONG> dept;<BR><BR> <STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> 1 .. deptnums.COUNT<BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp_by_dept<BR> <STRONG>SELECT</STRONG> empno, deptno<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> deptno = deptnums(i);<BR><BR> <STRONG>FOR</STRONG> i <STRONG>IN</STRONG> 1 .. deptnums.COUNT <STRONG>LOOP</STRONG><BR> <EM>-- Count how many rows were inserted for each department; that is,</EM><BR> <EM>-- how many employees are in each department.</EM><BR> DBMS_OUTPUT.put_line( <EM>'Dept '</EM><BR> || deptnums(i)<BR> || <EM>': inserted '</EM><BR> || <STRONG>SQL</STRONG>%BULK_ROWCOUNT(i)<BR> || <EM>' records'</EM>);<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> DBMS_OUTPUT.put_line(<EM>'Total records inserted ='</EM> || <STRONG>SQL</STRONG>%ROWCOUNT);<BR><STRONG>END</STRONG>;<BR>/
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>我们还可以在批量绑定中使用标量属性%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。例如，%ROWCOUNT会返回所有的SQL语句处理的总行数。</P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -