可以在 PL/SQL 塊的內部運行 SQL 語句，一般的方法是：

A 使用通常位于服務器端的 PL/SQL 引擎，做純粹的 PL/SQL 執行。但是，客戶端程序有它自己的 PL/SQL 引擎。

B 所有的 SQL DML 語句都被送到 SQL 引擎進行執行，結果和數據或 DML 執行后結果再傳送加 PL/SQL 引擎。

?

這樣導至的結果：當 SQL 語句在 PL/SQL 引擎和 SQL 引擎之間進行傳送的時候，可能會發生上下文切換。在 PL/SQL 內執行 SQL 時，一件很重要的事情就是要保證查詢最優化。由于代碼往往是 PL/SQL 代碼與 SQL 代碼混合一起的，所以其執行過程還應該包括兩種代碼的解析。

?

不同引擎之間傳送語句的操作被稱為上下文切換，而上下文切換可能導致代碼的執行達不到最佳。

?

解決這種情能問題的方法之一就是通過使用批綁定。在 Oracle 8i 中引入的批綁定，全面提高了 PL/SQL 的執行性能，在 Oracle9i 中又做了以下加強：

?????? 如果涉及到 DML 的批綁定發生異常，使用 SAVE EXEPTIONS 子句處理仍可以連續運行。

? ???? 直接在單個 DML 語句中支持批動態 SQL 處理多行數據的能力。

可以與 RETURNING 子句一起使用動態 SQL ，支持多行更新和刪除。在 oracle 9i 以前本地動態 SQL 只在單行輸出才支持 RETURNING 子句。

?

?

接下來介紹可以提高程序執行性能的批綁定的各種特性，然后介紹以下語句的技術：

?????? 批 DML

批查詢

批動態 SQL

?

批 DML

?????? 批 DML 是由使用 PL/SQL 的 index-by 表或集合作為 DML 語句的輸入、批量地執行 INSERT 、 UPDATE 或 DELETE 的語句組成的。可以使用 FORALL 語句執行批 DML ，如：

create or replace procedure batch_dml(errnum outnumber,errtext outvarchar2)
is
?? type user_record isrecord(? -- 定義記錄類型
???? id number,
???? namevarchar2( 200 ),
???? sex varchar2( 2 )
?? );
?? type user_tab istableof user_record indexbybinary_integer; -- 定義記錄類型的 index-by

?? user_recs user_tab;
begin
??? for idx in 1 .. 5 loop ? -- 初始化數據
???????? user_recs(idx).id:=idx;
???????? user_recs(idx).name:= 'xxxxx' ||idx;
???????? user_recs(idx).sex:= 'F' ;
??? endloop;
??? deletefrom t_user;
??? for idx in user_recs.first..user_recs.lastloop
??????? insertinto t_user values(user_recs(idx).id, user_recs(idx).name,user_recs(idx).sex);
??? endloop;
??? commit;
??? errnum:= 0 ;
??? errtext:= '' ;
exception
??? whenothersthen
???????? errnum:=sqlcode;
???????? errtext:=sqlerrm;?
end batch_dml;
對于這段代碼， FOR LOOP 循環每執行一次，其中的 insert 語句就會被送到 SQL 引擎一次，也就發生一次上下文切換。這意味著當數據量很大的時候阻塞就會很嚴重，而這時 insert 語句也就會影響數據庫更多的記錄行，而使用批綁定可以很大程度上提高程的執行性能。

綁定指的是在 SQL 語句中為 PL/SQL 變量賦值，而一次性地將整個值集合綁定稱為批綁定。批綁定能減少數據往返的次數，因些在 PL/SQL 和 SQL 引擎之間上下文切換次數也會減少。這樣顯著提高程序的執行性能。可以使用 FORALL 語句來執行 DML 批綁定。其語法如下：

?????? FORALL index IN index-by.first..index-by.last? [save exceptions]

???????????????????? Sql_statement;

下面我們把上面的 batch_dml 改成批綁定的 DML ，代碼如下 ：

create or replace procedure batch_dml(errnum outnumber,errtext outvarchar2)
is
?? --type user_record is record(? --?¨ò?????ààDí
??? -- id number,
?? --? name varchar2(200),
?? --? sex varchar2(2)
? --? );
?? --type user_tab is table of user_record index by binary_integer; --?¨ò?user_recordààDíμ?index-by±íààDí
?? --user_recs user_tab;
??
?? type user_id_tab istableofnumberindexbybinary_integer;
?? type user_name_tab istableofvarchar2( 20 ) indexbybinary_integer;
?? type user_sex_tab istableofvarchar2( 2 ) indexbybinary_integer;
??
?? user_id user_id_tab;
?? user_name user_name_tab;
?? user_sex user_sex_tab;
begin
??? for idx in 1 .. 50000 loop ? -- 初始化數據
???????? user_id(idx):=idx;
???????? user_name(idx):= 'xxx' ||idx;
???????? user_sex(idx):= 'F' ;
??? endloop;
??? deletefrom t_user;
??? forall idx in user_id.first..user_id.last
??????? insertinto t_user values(user_id(idx), user_name(idx),user_sex(idx));
??? commit;
??? errnum:= 0 ;
??? errtext:= '' ;
exception

??? when others then
??? ???? commit;

???? errnum:=sqlcode;
???????? errtext:=sqlerrm;?
end batch_dml;
插入 50000 條記錄僅用了 2.16 秒

?

使用 FORALL 時，多個 INSERT 、 UPDATE 和 DELETE 語句是在一個數據庫調用中完成的。這就縮短了程序的執行時間。只有單條引用了集合元素的 INSERT 、 UPDATE 或 DELETE 語句才可以使用 FORALL 進行執行。

?

SQL 出錯以后

當發生 SQL 錯誤的時候，產生錯誤的那條 SQL 操作是以一種自動回滾的方式終止的，而接著程序的執行就停止下來。只有產生錯誤的這條 SQL 才可以回滾。而一旦執行成功，前的 SQL 語句是不再回滾了。

?

SAVE EXCEPTION 子句

Oracle 9i 中增強了批 DML 在發生異常時處理的錯誤及失敗的程序執行能力。這是通過 FORALL 語句的 SAVE EXCEPTION 子句來實現，其語法如下：

?FORALL? index in lower..upper? save exceptions

?

SAVE EXCEPTIONS 子句是 oracle 9i 中引入的，它在一個隱式游標屬性 SQL%BULK_EXCEPTIONS 中保存錯誤行，允許 FORALL 語句繼續處理其余行。

下面這一段代碼是 SQL%BULK_EXCEPTIONS 的應用例子：

create or replace procedure batch_dml(errnum outnumber,errtext outvarchar2)
is

?
?? type user_id_tab istableofnumberindexbybinary_integer;
?? type user_name_tab istableofvarchar2( 20 ) indexbybinary_integer;
?? type user_sex_tab istableofvarchar2( 2 ) indexbybinary_integer;
??
?? user_id user_id_tab;
?? user_name user_name_tab;
?? user_sex user_sex_tab;
??
?? bulk_bind_excep EXCEPTION;
?? pragmaexception_init(bulk_bind_excep,- 24381 );
begin
??? for idx in 1 .. 50000 loop ? --3?ê??ˉindex-by????
???????? user_id(idx):=idx;
???????? user_name(idx):= 'xxx' ||idx;
???????? user_sex(idx):= 'F' ;
? ??endloop;
??? user_id( 40000 ):= 39999 ;
??? user_id( 10000 ):= 9999 ;
??? deletefrom t_user;
??? forall idx in user_id.first..user_id.lastsaveexceptions
??????? insertinto t_user values(user_id(idx), user_name(idx),user_sex(idx));
??? errnum:= 0 ;
??? errtext:= '' ;
exception
??? when bulk_bind_excep then
???????? for i in 1 ..sql%bulk_exceptions.countloop
???????????? dbms_output.put_line( 'Iteration ' ||SQL%bulk_exceptions(i).error_index|| 'failed with error ' ||sqlerrm(sql%bulk_exceptions(i).error_code));
?????? ??endloop;
???????? commit;
??? whenothersthen
???????? commit;
???????? errnum:=sqlcode;
???????? errtext:=sqlerrm;?
end batch_dml;
這個例子是修改了上面的程序，加上 save exceptions 異常處理，當批 dml 發生異常時也能正常運行。

?

FORALL 語句屬性

與隱式游標的屬性一樣，批綁定操作也有與之關聯的標量屬性，這些標量屬性有 %found 、 %NOTFOUND 、 %ROWCOUNT 。 %ROWCOUND 屬性是基于批綁定的 SQL 語句全部執行完成以后的。 %FOUND 、 %NOTFOUND 指的是剛剛執行的那一條 SQL 語句。

?

批查詢

可以使用 bulk collect into collection_name ，其中 collect_name 是 index-by 表、嵌套表或 Varray 的名稱。可以在 select into 、 fetch into 和 returning into 子句中使用 bulk collect 。可以在 into 例表中經引用多個類集。

? 在 select ..into 語句中使用 bulk? collect

語法如下：

Select column_name bulk collect into collect_name

例子：

create or replace procedure bulkcollectdemo(
??? errnum outnumber,
??? errtext outvarchar2
)
authidcurrent_user
is
?type uidtab istableof t_user.id%type;
?type unametab istableof t_user.name%type;
?id_tab uidtab;
?name_tab unametab;
begin
?? select a.id,a.name? bulkcollectinto id_tab,name_tab from t_user a orderby a.id;
?? for i in id_tab.first..id_tab.lastloop
????? null;
?? endloop;
?? errnum:= 0 ;
?? errtext:= '' ;
exception
?? whenothersthen
???? errnum:=sqlcode;
???? errtext:=sqlerrm;??
end;

使用 bulk collect 的主要優點是：它減少在 SQL 和 PL/SQL 引擎之間的上下文切換次數，最大程度的提高程序執行的性能，在選擇記錄里可以使用 rownum 來限制加載的行數。實現這種功能的代碼如下：

select id bulk collect into region_name where rownum<51;

?

在 Fetching 中使用 BULK COLLECT

從游標中取多行數據時，可以使用 bulk collect 。其語法如下：

?Fetch cursor_name bulk collect into collection_name

下面的例子是修改上一個存儲過程的，其代碼如下：

create or replace procedure bulkcollectdemo(
??? errnum outnumber,
??? errtext outvarchar2
)
authidcurrent_user
is
?type uidtab istableof t_user.id%type;
?type unametab istableof t_user.name%type;
?id_tab uidtab;
?name_tab unametab;
?cursor cur_tuser is
??? select id,namefrom t_user orderby id;
begin
?? open cur_tuser;
?? fetch cur_tuser bulkcollectinto id_tab,name_tab;
?? dbms_output.put_line(id_tab.count);
?? for i in id_tab.first..id_tab.lastloop
????? if i= 37 then
??????? dbms_output.put_line( '------' );
????? endif;
?? endloop;
?? close cur_tuser;
?? errnum:= 0 ;
?? errtext:= '' ;
exception
?? whenothersthen
???? errnum:=sqlcode;
???? errtext:=sqlerrm;??
end;

不能在 forall 語句中使用 select ... bulk collect 語句

在 returning into 中使使用 bulk collect

?

弄不明白暫時不管

?

?

批動態 SQL

本地動態 SQL 比靜態 SQL 更優，它的執行也更快，在 Oracle 9i 之前，只能使用 DBMS_SQL 來執行批動態 SQL 。 Oralce 9i 已經增強了本地動態 SQL ，使其支持批處理操作。

1 、通過使用 SELECT INTO 語句，將 BULK COLLECT 與 EXECUTE IMMEDIATE 結合在一起進行批查詢，其語法如下：

Execute immediate ‘select statement’ bulk collect into collection_name;

?

2 、將 execute immediate 與 forall 語句結合起來一起使用進行批 DML, 語法如下：

?? Forall index in lower..upper

??????? Execute? immediate ‘sql_statement ‘? using collection_name;

? 例子：

?????? Forall I in? region_ids.first..region_ids.last

???????? Execute immediate ‘insert into region_tab value (:ip_ids,:ip_name)’ using region_ids(i),region_names(i);

?

3 、使用 fetch..bulk collect into... 從使用本地動態 SQL 打開的游標中進行批取值，語法如下：

?????? Open cursor_variable_name for dyn_sql_statement;

Fetch cursor variable_name bulk collect into collection_name;

例子：

Cursor cur_tuser is select tname form t_user order by id;

Begin

? open cur_tuser;

? fetch cur_tuser bulk collect into? tname;

使用動態語句，把以上代碼改成如下：

Type rc is ref cursor;

Cur_tuser rc;

Begin

? Open cur_tuser form ‘select name form t_user’;

? Fetch cur_tuser bulk collect into region_names;

?

?

?