?? 第五章 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 + -