oracle bucket collect(批量查询)
来源:互联网 发布:数字签名常用的算法有 编辑:程序博客网 时间:2024/04/25 09:11
可以在select into,fetch into,returning into语句使用bulk collect。
注意:在使用bulk collect时,所有的into变量都必须是collections。
create table t_test as
select object_id, object_name, object_type
from dba_objects
where wner = 'TEST';
1、在select into语句中使用bulk collect
declare
type object_list is table of t_test.object_name%type;
objs object_list;
begin
select object_name bulk collect
into objs
from t_test
where rownum <= 100;
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r));
end loop;
end;
/
2、在fetch into中使用bulk collect
declare
type objecttab is table of t_test%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from t_test
where rownum <= 10;
begin
open cob;
fetch cob bulk collect
into objs;
close cob;
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r).object_name);
end loop;
end;
/
以上为把结果集一次fetch到collect中,我们还可以通过limit参数,来分批fetch数据,如下:
declare
type objecttab is table of t_test%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from t_test
where rownum <= 10000;
begin
open cob;
loop
fetch cob bulk collect
into objs limit 1000;
exit when cob%notfound;
dbms_output.put_line('count:' || objs.count || ' first:' || objs.first ||
' last:' || objs.last);
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r).object_name);
end loop;
end loop;
close cob;
end;
/
你可以根据实际来调整limit参数的大小,来达到最优的性能。limit参数会影响到PGA的使用率。
3、在returning into中使用bulk collect
declaretype id_list is table of t_test.object_id%type;
ids id_list;
type name_list is table of t_test.object_name%type;
names name_list;
begin
delete from t_test
where object_id <= 87510 returning object_id, object_name bulk collect into ids,
names;
dbms_output.put_line('deleted ' || sql%rowcount || ' rows:');
for i in ids.first .. ids.last loop
dbms_output.put_line('object #' || ids(i) || ': ' || names(i));
end loop;
end;
- oracle bucket collect(批量查询)
- oracle bucket collect(批量查询)
- Oracle 的 bulk collect用法(批量查询)
- Oracle 的 bulk collect用法——批量查询
- oracle 利用bulk collect into 批量insert
- ORACLE 批量绑定 FORALL 与 BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- Oracle BULK COLLECT INTO批量操作应用
- oracle bulk collect 提高Oracle查询效率
- 使用Bulk Collect提高Oracle查询效率
- oracle批量绑定 forall bulk collect用法以及测试案例
- 经纬财富:宿州现货白银投资的优势
- Cocos2d-x 3.0final 终结者系列教程13-贪食蛇游戏案例(全)
- java.lang.NoClassDefFoundError: com.umeng.analytics.Mobcl
- 详细解说 STL 排序(Sort)
- jQuery整理笔记目录
- oracle bucket collect(批量查询)
- CSS3支持Internet Explorer6、7和8
- MySQL binlog_format (Mixed,Statement,Row)
- CentOS下LNMP高性能的WEB服务器快速Yum搭建流程
- linux信号 - sigaction结构体浅析
- UCMA4中连接的建立
- java 遍历时间段的方法
- vs2008内存泄露检测得到完美解决
- 黑马程序员_泛型、Collections类