?? 第十一章 本地動(dòng)態(tài)sql - pl-sql用戶指南與參考 - whatiswhat.htm
字號(hào):
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> dynamic_string<BR>[<STRONG>INTO</STRONG> {define_variable[, define_variable]... | record}]<BR>[USING [<STRONG>IN</STRONG> | <STRONG>OUT</STRONG> | <STRONG>IN</STRONG> <STRONG>OUT</STRONG>] bind_argument<BR> [, [<STRONG>IN</STRONG> | <STRONG>OUT</STRONG> | <STRONG>IN</STRONG> <STRONG>OUT</STRONG>] bind_argument]...]<BR>[{RETURNING | <STRONG>RETURN</STRONG>} <STRONG>INTO</STRONG> bind_argument[, bind_argument]...];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>dynamic_string是代表一條SQL語句或一個(gè)PL/SQL塊的字符串表達(dá)式，define_variable是用于存放被選出的字段值的變量，record是用戶定義或%ROWTYPE類型的記錄，用來存放被選出的行記錄。輸入bind_argument參數(shù)是一個(gè)表達(dá)式，它的值將被傳入(IN模式)或傳出(OUT模式)或先傳入再傳出(IN
OUT模式)到動(dòng)態(tài)SQL語句或是PL/SQL塊中。一個(gè)輸出bind_argument參數(shù)就是一個(gè)能保存動(dòng)態(tài)SQL返回值的變量。</P>
<P>除了多行查詢外，動(dòng)態(tài)字符串可以包含任何SQL語句(不含終結(jié)符)或PL/SQL塊(含終結(jié)符)。字符串中可以包括用于參數(shù)綁定的占位符。但是，不可以使用綁定參數(shù)為動(dòng)態(tài)SQL傳遞模式對(duì)象。
</P>
<P>在用于單行查詢時(shí)，INTO子句要指明用于存放檢索值的變量或記錄。對(duì)于查詢檢索出來的每一個(gè)值，INTO子句中都必須有一個(gè)與之對(duì)應(yīng)的、類型兼容的變量或字段。在用于DML操作時(shí)，RETURNING
INTO子句要指明用于存放返回值的變量或記錄。對(duì)于DML語句返回的每一個(gè)值，INTO子句中都必須有一個(gè)與之對(duì)應(yīng)的、類型兼容的變量或字段。
</P>
<P>我們可以把所有的綁定參數(shù)放到USING子句中。默認(rèn)的參數(shù)模式是IN。對(duì)于含有RETURNING子句的DML語句來說，我們可以把OUT參數(shù)放到RETURNING
INTO之后，并且不用指定它們的參數(shù)模式，因?yàn)槟J(rèn)就是OUT。如果我們既使用了USING又使用RETURNING
INTO，那么，USING子句中就只能包含IN模式的參數(shù)了。 </P>
<P>運(yùn)行時(shí)，動(dòng)態(tài)字符串中的綁定參數(shù)會(huì)替換相對(duì)應(yīng)的占位符。所以，每個(gè)占位符必須與USING子句和/或RETURNING
INTO子句中的一個(gè)綁定參數(shù)對(duì)應(yīng)。我們可以使用數(shù)字、字符和字符串作為綁定參數(shù)，但不能使用布爾類型(TRUE，F(xiàn)ALSE和NULL)。要把空值傳遞給動(dòng)態(tài)字符串，我們就必須使用工作區(qū)。
</P>
<P>動(dòng)態(tài)SQL支持所有的SQL類型。所以，定義變量和綁定變量都可以是集合、LOB，對(duì)象類型實(shí)例和引用。作為一項(xiàng)規(guī)則，動(dòng)態(tài)SQL是不支持PL/SQL特有的類型的。這樣，它就不能使用布爾型或索引表。
</P>
<P>我們可以重復(fù)為綁定變量指定新值執(zhí)行動(dòng)態(tài)SQL語句。但是，每次都會(huì)消耗很多資源，因?yàn)镋XECUTE
IMMEDIATE在每次執(zhí)行之前都需要對(duì)動(dòng)態(tài)字符串進(jìn)行預(yù)處理。 </P>
<P class=title2>1、動(dòng)態(tài)SQL實(shí)例</P>
<P>下面的PL/SQL塊包含了幾個(gè)動(dòng)態(tài)SQL的例子： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> sql_stmt <STRONG>VARCHAR2</STRONG>(200);<BR> plsql_block <STRONG>VARCHAR2</STRONG>(500);<BR> emp_id <STRONG>NUMBER</STRONG>(4) := 7566;<BR> salary <STRONG>NUMBER</STRONG>(7, 2);<BR> dept_id <STRONG>NUMBER</STRONG>(2) := 50;<BR> dept_name <STRONG>VARCHAR2</STRONG>(14) := <EM>'PERSONNEL'</EM>;<BR> LOCATION <STRONG>VARCHAR2</STRONG>(13) := <EM>'DALLAS'</EM>;<BR> emp_rec emp%<STRONG>ROWTYPE</STRONG>;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'CREATE TABLE bonus (id NUMBER, amt NUMBER)'</EM>;<BR><BR> sql_stmt := <EM>'INSERT INTO dept VALUES (:1, :2, :3)'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING dept_id, dept_name, LOCATION;<BR><BR> sql_stmt := <EM>'SELECT * FROM emp WHERE empno = :id'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> <STRONG>INTO</STRONG> emp_rec<BR> USING emp_id;<BR><BR> plsql_block := <EM>'BEGIN emp_pkg.raise_salary(:id, :amt); END;'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> plsql_block<BR> USING 7788, 500;<BR><BR> sql_stmt :=<BR> <EM>'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING emp_id<BR> RETURNING <STRONG>INTO</STRONG> salary;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'DELETE FROM dept WHERE deptno = :num'</EM><BR> USING dept_id;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'ALTER SESSION SET SQL_TRACE TRUE'</EM>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>下例中，過程接受一個(gè)數(shù)據(jù)表名(如"emp")和一個(gè)可選的WHERE子句(如"sal >
2000")。如果我們沒有提供WHERE條件，程序會(huì)刪除指定表中所有的行，否則就會(huì)按照給定的條件刪除行：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PROCEDURE</STRONG> delete_rows(<BR> table_name <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG>,<BR> condition <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG> <STRONG>DEFAULT</STRONG> <STRONG>NULL</STRONG><BR>) <STRONG>AS</STRONG><BR> where_clause <STRONG>VARCHAR2</STRONG>(100) := <EM>' <STRONG>WHERE</STRONG> '</EM> || condition;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>IF</STRONG> condition <STRONG>IS</STRONG> <STRONG>NULL</STRONG> <STRONG>THEN</STRONG><BR> where_clause := <STRONG>NULL</STRONG>;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'<STRONG>DELETE</STRONG> <STRONG>FROM</STRONG> '</EM> || table_name || where_clause;<BR><STRONG>EXCEPTION</STRONG><BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>2、USING子句的向后兼容</P>
<P>當(dāng)動(dòng)態(tài)INSERT、UPDATE或DELETE語句有一個(gè)RETURNING子句時(shí)，輸出綁定參數(shù)可以放到RETURNING
INTO或USING子句的后面。XXXXXXXXXX在新的應(yīng)用程序中要使用RETURNING
INTO，而舊的應(yīng)用程序可以繼續(xù)使用USING，如下例： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> sql_stmt <STRONG>VARCHAR2</STRONG>(200);<BR> my_empno <STRONG>NUMBER</STRONG>(4) := 7902;<BR> my_ename <STRONG>VARCHAR2</STRONG>(10);<BR> my_job <STRONG>VARCHAR2</STRONG>(9);<BR> my_sal <STRONG>NUMBER</STRONG>(7, 2) := 3250.00;<BR><STRONG>BEGIN</STRONG><BR> sql_stmt := <EM>'UPDATE emp SET sal = :1 WHERE empno = :2 '</EM><BR> || <EM>'RETURNING ename, job INTO :3, :4'</EM>;<BR><BR> <EM>/* Bind returned values through USING clause. */</EM><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING my_sal, my_empno, <STRONG>OUT</STRONG> my_ename, <STRONG>OUT</STRONG> my_job;<BR><BR> <EM>/* Bind returned values through RETURNING <STRONG>INTO</STRONG> clause. */</EM><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING my_sal, my_empno<BR> RETURNING <STRONG>INTO</STRONG> my_ename, my_job;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>3、指定參數(shù)模式</P>
<P>使用USING子句時(shí)，我們不需要為輸入?yún)?shù)指定模式，因?yàn)槟J(rèn)的就是IN；而RETURNING
INTO子句中我們是不可以指定輸出參數(shù)的模式的，因?yàn)槎x中它就是OUT模式。看一下下面的例子： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> sql_stmt <STRONG>VARCHAR2</STRONG>(200);<BR> dept_id <STRONG>NUMBER</STRONG>(2) := 30;<BR> old_loc <STRONG>VARCHAR2</STRONG>(13);<BR><STRONG>BEGIN</STRONG><BR> sql_stmt := <EM>'DELETE FROM dept WHERE deptno = :1 RETURNING loc INTO :2'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING dept_id<BR> RETURNING <STRONG>INTO</STRONG> old_loc;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在適當(dāng)?shù)臅r(shí)候，我們必須為綁定參數(shù)指定OUT或IN OUT模式。例如，假定我們想調(diào)用下面的過程： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PROCEDURE</STRONG> create_dept(<BR> deptno <STRONG>IN</STRONG> <STRONG>OUT</STRONG> <STRONG>NUMBER</STRONG>,<BR> dname <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG>,<BR> loc <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG><BR>) <STRONG>AS</STRONG><BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> deptno_seq.<STRONG>NEXTVAL</STRONG><BR> <STRONG>INTO</STRONG> deptno<BR> <STRONG>FROM</STRONG> DUAL;<BR><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> dept<BR> <STRONG>VALUES</STRONG> (deptno, dname, loc);<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>要從動(dòng)態(tài)PL/SQL塊調(diào)用過程，就必須為與形參關(guān)聯(lián)的綁定參數(shù)指定IN OUT模式，如下： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> plsql_block <STRONG>VARCHAR2</STRONG>(500);<BR> new_deptno <STRONG>NUMBER</STRONG>(2);<BR> new_dname <STRONG>VARCHAR2</STRONG>(14) := <EM>'ADVERTISING'</EM>;<BR> new_loc <STRONG>VARCHAR2</STRONG>(13) := <EM>'NEW YORK'</EM>;<BR><STRONG>BEGIN</STRONG><BR> plsql_block := <EM>'BEGIN create_dept(:a, :b, :c); END;'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> plsql_block<BR> USING <STRONG>IN</STRONG> <STRONG>OUT</STRONG> new_deptno, new_dname, new_loc;<BR><BR> <STRONG>IF</STRONG> new_deptno > 90 <STRONG>THEN</STRONG> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title1>四、使用OPEN-FOR、FETCH和CLOSE語句</P>
<P>我們可以使用三種語句來處理動(dòng)態(tài)多行查詢：OPEN-FOR，F(xiàn)ETCH和CLOSE。首先，用OPEN打開多行查詢的游標(biāo)變量。然后，用FETCH語句把數(shù)據(jù)從結(jié)果集中取出來。當(dāng)所有的數(shù)據(jù)都處理完以后，就可以用CLOSE語句關(guān)閉游標(biāo)變量了。
</P>
<P class=title2>1、打開游標(biāo)變量</P>
<P>OPEN-FOR語句可以把游標(biāo)變量和一個(gè)多行查詢關(guān)聯(lián)起來，然后執(zhí)行查詢，確定結(jié)果集，并把游標(biāo)放到結(jié)果集的第一行，然后把%ROWCOUNT值初始化為零。
</P>
<P>與OPEN-FOR的靜態(tài)形式不同的是，動(dòng)態(tài)形式有一個(gè)可選的USING子句。在運(yùn)行時(shí)，USING子句中的綁定變量可以替換動(dòng)態(tài)SELECT語句中相對(duì)應(yīng)的占位符，語法如下：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>OPEN</STRONG> {cursor_variable | :host_cursor_variable} <STRONG>FOR</STRONG> dynamic_string<BR>[USING bind_argument[, bind_argument]...];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>其中，cursor_variable是一個(gè)弱類型(沒有返回類型)的游標(biāo)變量，host_cursor_variable是聲明在PL/SQL主環(huán)境中的游標(biāo)變量，dynamic_string是字符串表達(dá)式，代表一個(gè)多行查詢。
</P>
<P>在下面的例子中，我們聲明一個(gè)游標(biāo)變量，并把它和動(dòng)態(tài)SELECT語句關(guān)聯(lián)起來： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> empcurtyp <STRONG>IS</STRONG> <STRONG>REF</STRONG> <STRONG>CURSOR</STRONG>; <EM>-- define weak REF CURSOR type</EM><BR><BR> emp_cv empcurtyp; <EM>-- declare cursor variable</EM><BR> my_ename <STRONG>VARCHAR2</STRONG>(15);<BR> my_sal <STRONG>NUMBER</STRONG> := 1000;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> emp_cv <STRONG>FOR</STRONG> <EM>-- open cursor variable</EM><BR> <EM>'SELECT ename, sal FROM emp WHERE sal > :s'</EM> USING my_sal;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>其中綁定參數(shù)的值只在游標(biāo)變量打開時(shí)計(jì)算一次。所以，如果我們想使用一個(gè)新的綁定值進(jìn)行查詢，就必須重新打開游標(biāo)變量。
</P>
<P class=title2>2、從游標(biāo)變量取得數(shù)據(jù)</P>
<P>FETCH語句可以從多行查詢的結(jié)果集中返回單獨(dú)的一行數(shù)據(jù)，并把數(shù)據(jù)內(nèi)容賦值給INTO子句后的對(duì)應(yīng)的變量，然后屬性%ROWCOUNT增加一，游標(biāo)移到下一行，語法如下：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FETCH</STRONG> {cursor_variable | :host_cursor_variable}<BR> <STRONG>INTO</STRONG> {define_variable[, define_variable]... | record};
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>繼續(xù)上面的例子，我們把從游標(biāo)變量emp_cv取得的數(shù)據(jù)放到變量my_ename和my_sal：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -