?http://www.jzxue.com/shujuku/oracle/201109/21-8976.html

通常我們獲取游標(biāo)數(shù)據(jù)是用 fetch some_cursor into var1, var2 的形式，當(dāng)游標(biāo)中的記錄數(shù)不多時不打緊。然而自 Oracle 8i 起，Oracle 為我們提供了 fetch bulk collect 來批量取游標(biāo)中的數(shù)據(jù)，存中即是合理的。它能在讀取游標(biāo)中大量數(shù)據(jù)的時候提升效率，就像 SNMP 協(xié)議中，V2 版比 V1 版新加了 GET-BULK PDU 一樣，也是用來更高效的批量取設(shè)備上的節(jié)點值（原來做過網(wǎng)管軟件開發(fā)，故聯(lián)想到此）。

　　fetch bulk collect into 的使用格式是：fetch some_cursor collect into col1, col2 limit xxx。col1、col2 是聲明的集合類型變量，xxx 為每次取數(shù)據(jù)塊的大小（記錄數(shù)），相當(dāng)于緩沖區(qū)的大小，可以不指定 limit xxx 大小。下面以實際的例子來說明它的使用，并與逐條取記錄的 fetch into 執(zhí)行效率上進行比較。測試環(huán)境是 Oracle 10g　 10.2.1.0，查詢的聯(lián)系人表 sr_contacts 中有記錄數(shù) 1802983 條，游標(biāo)中以 rownum 限定返回的記錄數(shù)。

　　使用 fetch bulk collect into 獲取游標(biāo)數(shù)據(jù)

　　declare　　　　

　　

　　 --聲明需要集合類型及變量，參照字段的 type 來聲明類型　　　　　　

　 type id_type is table of sr_contacts.sr_contact_id%type;　　　　　
　 v_id id_type;　　　　　
　　　　　　　
　 type phone_type is table of sr_contacts.contact_phone%type;　　　　　
　 v_phone phone_type;　　　　　
　　　　　　　
　 type remark_type is table of sr_contacts.remark%type;　　　　　
　 v_remark remark_type;　　　

　　 cursor all_contacts_cur is --用 rownum 來限定取出的記錄數(shù)來測試　　　　　　

　　　　 select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;　　　　　　
　　

begin　　　　
　　　　　　　　　
　　　 open all_contacts_cur;　　　　　
　　　 loop　　　　　
　　　　　　　 fetch all_contacts_cur bulk collect into v_id,v_phone,v_remark limit 256;　　　　　
　　　　　　　 for i in 1..v_id.count loop --遍歷集合　　　　　
　　　　　　　　　　　 --用 v_id(i)/v_phone(i)/v_remark(i) 取出字段值來執(zhí)行你的業(yè)務(wù)邏輯　　　　　
　　　　　　　　　　　 null; --這里只放置一個空操作，只為測試循環(huán)取數(shù)的效率　　　　
　　　　　　　 end loop;　　　　　
　　　　　　　 exit when all_contacts_cur%notfound; --exit 不能緊接 fetch 了，不然會漏記錄　　　　　
　　　 end loop;　　　　　
　　　 close all_contacts_cur;　　　　　
end;　　　
declare　　

　　 --聲明需要集合類型及變量，參照字段的 type 來聲明類型　　　

　 type id_type is table of sr_contacts.sr_contact_id%type;　　
　 v_id id_type;　　
　　　　
　 type phone_type is table of sr_contacts.contact_phone%type;　　
　 v_phone phone_type;　　
　　　　
　 type remark_type is table of sr_contacts.remark%type;　　
　 v_remark remark_type;　

　　 cursor all_contacts_cur is --用 rownum 來限定取出的記錄數(shù)來測試　　　

　　　　 select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;　　　

begin　
　　　　　　
　　　 open all_contacts_cur;　　
　　　 loop　　
　　　　　　　 fetch all_contacts_cur bulk collect into v_id,v_phone,v_remark limit 256;　　
　　　　　　　 for i in 1..v_id.count loop --遍歷集合　　
　　　　　　　　　　　 --用 v_id(i)/v_phone(i)/v_remark(i) 取出字段值來執(zhí)行你的業(yè)務(wù)邏輯　　
　　　　　　　　　　　 null; --這里只放置一個空操作，只為測試循環(huán)取數(shù)的效率　
　　　　　　　 end loop;　　
　　　　　　　 exit when all_contacts_cur%notfound; --exit 不能緊接 fetch 了，不然會漏記錄　　
　　　 end loop;　　
　　　 close all_contacts_cur;　　
end;　
　　使用 fetch into 逐行獲取游標(biāo)數(shù)據(jù)

　　declare　

　　 --聲明變量，參照字段的 type 來聲明類型　　　

　 v_id sr_contacts.sr_contact_id%type;　　
　 v_phone sr_contacts.contact_phone%type;　　
　 v_remark sr_contacts.remark%type;　　　
cursor all_contacts_cur is　 --用 rownum 來限定取出的記錄數(shù)來測試　　　

　　　　 select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;　　　

begin　
　　　　　　
　　　 open all_contacts_cur;　　
　　　 loop　　
　　　　　　　 fetch all_contacts_cur into v_id,v_phone,v_remark;　　
　　　　　　　 exit when all_contacts_cur%notfound;　　　　　　
　　　　　　　 --用 v_id/v_phone/v_remark 取出字段值來執(zhí)行你的業(yè)務(wù)邏輯　　
　　　　　　　 null; --這里只放置一個空操作，只為測試循環(huán)取數(shù)的效率　　
　　　 end loop;　　
　　　 close all_contacts_cur;　　
end;　
declare
　　 --聲明變量，參照字段的 type 來聲明類型

　 v_id sr_contacts.sr_contact_id%type;
　 v_phone sr_contacts.contact_phone%type;
　 v_remark sr_contacts.remark%type;
　　 cursor all_contacts_cur is　 --用 rownum 來限定取出的記錄數(shù)來測試

　　　　 select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;begin
　　　
　　　 open all_contacts_cur;
　　　 loop
　　　　　　　 fetch all_contacts_cur into v_id,v_phone,v_remark;
　　　　　　　 exit when all_contacts_cur%notfound;　　　
　　　　　　　 --用 v_id/v_phone/v_remark 取出字段值來執(zhí)行你的業(yè)務(wù)邏輯
　　　　　　　 null; --這里只放置一個空操作，只為測試循環(huán)取數(shù)的效率
　　　 end loop;
　　　 close all_contacts_cur;
end;
　　執(zhí)行性能比較

　　看看測試的結(jié)果，分別執(zhí)行五次所耗費的秒數(shù)：

　　當(dāng) rownum <= 100000 時：

　　fetch bulk collect into 耗時：0.125秒, 0.125秒, 0.125秒, 0.125秒, 0.141秒

　　fetch into 耗時：　　　　　　　　　　　　　　　　 1.266秒, 1.250秒, 1.250秒, 1.250秒, 1.250秒

　　當(dāng) rownum <= 1000000 時：

　　fetch bulk collect into 耗時：1.157秒, 1.157秒, 1.156秒, 1.156秒, 1.171秒

　　fetch into 耗時：　　　　　　　　　　　　　 12.128秒, 12.125秒, 12.125秒, 12.109秒, 12.141秒

　　當(dāng) rownum <= 10000 時：

　　fetch bulk collect into 耗時：0.031秒, 0.031秒, 0.016秒, 0.015秒, 0.015秒

　　fetch into 耗時：　　　　　　　　　　　　　　　　 0.141秒, 0.140秒, 0.125秒, 0.141秒, 0.125秒

　　當(dāng) rownum <= 1000 時：

　　fetch bulk collect into 耗時：0.016秒, 0.015秒, 0.016秒, 0.016秒, 0.015秒

　　fetch into 耗時：　　　　　　　　　　　　　　　　 0.016秒, 0.031秒, 0.031秒, 0.032秒, 0.015秒

　　從測試結(jié)果來看游標(biāo)的記錄數(shù)越大時，用 fetch bulk collect into 的效率很明顯示，趨于很小時就差不多了。

　　注意了沒有，前面使用 fetch bulk collect into 時前為每一個查詢列都定義了一個集合，這樣有些繁瑣。我們之前也許用過表的 %rowtype 類型，同樣的我們也可以定義表的 %rowtype 的集合類型。看下面的例子，同時在這個例子中，我們借助于集合的 first、last 屬性來代替使用 count　 屬性來進行遍歷。

　　declare　

　　 --聲明需要集合類型及變量，參照字段的 type 來聲明類型　　　

　 type contacts_type is table of sr_contacts%rowtype;　　
　 v_contacts contacts_type;　　　

　　 cursor all_contacts_cur is --用 rownum 來限定取出的記錄數(shù)來測試　　　

　　　　 select * from sr_contacts where rownum <= 10000;　　　

begin　
　　　　　　
　　　 open all_contacts_cur;　　
　　　 loop　　
　　　　　　　 fetch all_contacts_cur bulk collect into v_contacts limit 256;　　
　　　　　　　 for i in v_contacts.first .. v_contacts.last loop --遍歷集合　　
　　　　　　　　　　　 --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark　　
　　　　　　　　　　　 --的形式來取出各字段值來執(zhí)行你的業(yè)務(wù)邏輯　　
　　　　　　　　　　　 null; --這里只放置一個空操作，只為測試循環(huán)取數(shù)的效率　　
　　　　　　　 end loop;　　
　　　　　　　 exit when all_contacts_cur%notfound;　　
　　　 end loop;　　
　　　 close all_contacts_cur;　　
end;　
declare
--聲明需要集合類型及變量，參照字段的 type 來聲明類型

　 type contacts_type is table of sr_contacts%rowtype;
　 v_contacts contacts_type;

　　 cursor all_contacts_cur is --用 rownum 來限定取出的記錄數(shù)來測試

　　　　 select * from sr_contacts where rownum <= 10000;begin
　　　
　　　 open all_contacts_cur;
　　　 loop
　　　　　　　 fetch all_contacts_cur bulk collect into v_contacts limit 256;
　　　　　　　 for i in v_contacts.first .. v_contacts.last loop --遍歷集合
　　　　　　　　　　　 --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark
　　　　　　　　　　　 --的形式來取出各字段值來執(zhí)行你的業(yè)務(wù)邏輯
　　　　　　　　　　　 null; --這里只放置一個空操作，只為測試循環(huán)取數(shù)的效率
　　　　　　　 end loop;
　　　　　　　 exit when all_contacts_cur%notfound;
　　　 end loop;
　　　 close all_contacts_cur;
end;
　　關(guān)于 limit 參數(shù)

　　你可以根據(jù)你的實際來調(diào)整 limit 參數(shù)的大小，來達到你最優(yōu)的性能。limit 參數(shù)會影響到 pga 的使用率。而且也可以在 fetch bulk 中省略 limit 參數(shù)，寫成

fetch all_contacts_cur bulk collect into v_contacts;
　　有些資料中是說，如果不寫 limit 參數(shù)，將會以數(shù)據(jù)庫的 arraysize　 參數(shù)值作為默認(rèn)值。在 sqlplus 中用 show arraysize　 可以看到該值默認(rèn)為 15，set arraysize 256 可以更改該值。而實際上我測試不帶 limit 參數(shù)時，外層循環(huán)只執(zhí)行了一輪，好像不是 limit 15，所以不寫 limit 參數(shù)時，可以去除外層循環(huán)，begin-end 部分可寫成：

begin　
　　　 open all_contacts_cur;　　
　　　 fetch all_contacts_cur bulk collect into v_contacts;　　
　　　 for i in v_contacts.first .. v_contacts.last loop --遍歷集合　　
　　　　　　　 --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark　　
　　　　　　　 --的形式來取出各字段值來執(zhí)行你的業(yè)務(wù)邏輯　　
　　　　　　　 null; --這里只放置一個空操作，只為測試循環(huán)取數(shù)的效率　　
　　　　　　　 dbms_output.put_line(2000);　　
　　　 end loop;　　
　　　 close all_contacts_cur;　　
end;　
begin
　　　 open all_contacts_cur;
　　　 fetch all_contacts_cur bulk collect into v_contacts;
　　　 for i in v_contacts.first .. v_contacts.last loop --遍歷集合
　　　　　　　 --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark
　　　　　　　 --的形式來取出各字段值來執(zhí)行你的業(yè)務(wù)邏輯
　　　　　　　 null; --這里只放置一個空操作，只為測試循環(huán)取數(shù)的效率
　　　　　　　 dbms_output.put_line(2000);
　　　 end loop;
　　　 close all_contacts_cur;
end;
　　bulk collect 的其他用法(總是針對集合)

　　select into 語句中，如：

SELECT sr_contact_id,contact_phone BULK COLLECT INTO v_id,v_phone
　　　　 FROM sr_contacts WHERE ROWNUM <= 100;
dbms_output.put_line('Count:'||v_id.count||', First:'||v_id(1)||'|'||v_phone(1));
　　returning into 語句中，如：

DELETE FROM sr_contacts WHERE sr_contact_id < 30
　　　 RETURNING sr_contact_id, contact_phone BULK COLLECT INTO v_id, v_phone;
dbms_output.put_line('Count:'||v_id.count||', First:'||v_id(1)||'|'||v_phone(1));
　　forall 的 bulk dml 操作，它大大優(yōu)于 for 集合后的操作

fetch all_contacts_cur bulk collect into v_contacts;
forall i in 1 .. v_contacts.count
--forall i in v_contacts.first .. v_contacts.last　　
--forall i in indices of v_contacts --10g以上,可以是非連續(xù)的集合　　
insert into sr_contacts(sr_contact_id,contact_phone,remark)
　　　 values(v_contacts(i).sr_contact_id,v_contacts(i).contact_phone,v_contacts(i).remark);　
　　 --或者是單條的 delete/update 操作

?

總結(jié)

以上是生活随笔為你收集整理的Oracle 中使用 fetch bulk collect into 批量效率的读取的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。