bulk collect 在KingbaseES和Oracle的使用方法比较
2024-08-29 04:00:29
概述
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。
Oracle 使用 bulk collect 子句的用例
BULK COLLECT批量绑定 支持复合类型内部的集合类型
FETCH数据批量绑定
RETURNING 子句的批量绑定 不支持 INSERT returning 使用集合类型
动态SQL批量绑定 不支持动态 INSERT 使用集合类型
#创建自定义数据类型 —— 集合类型、复合类型
create type numberlist is table of number; create type rec_data is object
(
ID integer,
nums numberlist
); #创建用例数据表
create table t01 (id)
as
select level as id
from dual
connect by LEVEL <= 100; #Oracle使用bulk collect子句的例子
declare
v_numlst numberlist;
v_recdat rec_data ;
CURSOR cur_dat IS
select id
from t01;
begin
/*在SELECT INTO中使用BULK COLLECT*/
begin
select id bulk collect
into v_numlst
from t01
where id <= 10;
dbms_output.put_line('v_numlst: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*使用 BULK COLLECT INTO 嵌套的集合类型*/
begin
select id bulk collect
into v_recdat.NUMS
from t01
where id <= 20; dbms_output.put_line('v_recdat.NUMS: ' || v_recdat.NUMS.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*嵌套的集合类型变量赋值*/
begin
v_recdat.NUMS := v_numlst;
dbms_output.put_line('v_recdat.NUMS: ' || v_recdat.NUMS.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在FETCH INTO中使用BULK COLLECT*/
begin
OPEN cur_dat;
FETCH cur_dat BULK COLLECT
INTO v_numlst limit 30;
close cur_dat;
dbms_output.put_line('fetch cursor: ' || v_numlst.COUNT);
exception
when
others then
dbms_output.put_line(sqlerrm);
end; /*在UPDATE RETURNING INTO中使用BULK COLLECT*/
begin
update t01
set id = id + 100
where id <= 40
returning id bulk collect into v_numlst;
rollback ;
dbms_output.put_line('update returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在DELETE RETURNING INTO中使用BULK COLLECT*/
begin
delete t01
returning id bulk collect into v_numlst;
rollback;
dbms_output.put_line('delete returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在动态SQL中使用BULK COLLECT*/
begin
EXECUTE IMMEDIATE 'select id from t01 where id <= 60 '
bulk collect into v_numlst;
rollback;
dbms_output.put_line('dynsql select: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在动态UPDATE中使用BULK COLLECT*/
begin
EXECUTE IMMEDIATE 'update t01 set id = id + 1 where id <= 70 returning id into :1'
RETURNING bulk collect INTO v_numlst;
rollback;
dbms_output.put_line('dynsql update returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在动态DELETE中使用BULK COLLECT*/
begin
EXECUTE IMMEDIATE 'delete t01 where id <= 80 returning id into :1'
RETURNING bulk collect INTO v_numlst;
rollback;
dbms_output.put_line('dynsql delete returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; dbms_output.put_line('End ;');
end;
/ v_numlst: 10
v_recdat.NUMS: 20
v_recdat.NUMS: 10
fetch cursor: 30
update returning: 40
delete returning: 50
dynsql select: 60
dynsql update returning: 70
dynsql delete returning: 80
End ; PL/SQL procedure successfully completed.
KingbaseES 使用 bulk collect 子句的用例
BULK COLLECT批量绑定 不支持 复合类型内部的集合类型
FETCH数据批量绑定
RETURNING 子句的批量绑定 支持 INSERT returning 使用集合类型
动态SQL批量绑定 支持动态 INSERT 使用集合类型
#创建自定义数据类型 —— 集合类型、复合类型
create type numberlist is table of number; create type rec_data is object
(
ID integer,
nums numberlist
); #创建用例数据表
create table t01 (id)
as
select level as id
from dual
connect by LEVEL <= 100; #Oracle使用bulk collect子句的例子
declare
v_numlst numberlist;
v_recdat rec_data ;
CURSOR cur_dat IS
select id
from t01;
begin
/*在SELECT INTO中使用BULK COLLECT*/
begin
select id bulk collect
into v_numlst
from t01
where id <= 10;
dbms_output.put_line('v_numlst: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*使用 BULK COLLECT INTO 嵌套的集合类型*/
begin
select id bulk collect
into v_recdat.NUMS
from t01
where id <= 20; dbms_output.put_line('v_recdat.NUMS: ' || v_recdat.NUMS.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*嵌套的集合类型变量赋值*/
begin
v_recdat.NUMS := v_numlst;
dbms_output.put_line('v_recdat.NUMS: ' || v_recdat.NUMS.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在FETCH INTO中使用BULK COLLECT*/
begin
OPEN cur_dat;
FETCH cur_dat BULK COLLECT
INTO v_numlst limit 30;
close cur_dat;
dbms_output.put_line('fetch cursor: ' || v_numlst.COUNT);
exception
when
others then
dbms_output.put_line(sqlerrm);
end; /*在INERT RETURNING INTO中使用BULK COLLECT*/
begin
insert into t01
select * from t01 where id <=35
returning id bulk collect into v_numlst;
rollback;
dbms_output.put_line('insert returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在UPDATE RETURNING INTO中使用BULK COLLECT*/
begin
update t01
set id = id + 100
where id <= 40
returning id bulk collect into v_numlst;
rollback ;
dbms_output.put_line('update returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在DELETE RETURNING INTO中使用BULK COLLECT*/
begin
delete t01
returning id bulk collect into v_numlst;
rollback;
dbms_output.put_line('delete returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在动态SQL中使用BULK COLLECT*/
begin
EXECUTE IMMEDIATE 'select id from t01 where id <= 60 '
bulk collect into v_numlst;
rollback;
dbms_output.put_line('dynsql select: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在动态INSERT中使用BULK COLLECT*/
begin
EXECUTE IMMEDIATE 'insert into t01 select * from t01 where id<=65 returning id'
RETURNING bulk collect INTO v_numlst;
rollback;
dbms_output.put_line('dynsql insert returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在动态UPDATE中使用BULK COLLECT*/
begin
EXECUTE IMMEDIATE 'update t01 set id = id + 1 where id <= 70 returning id into :1'
RETURNING bulk collect INTO v_numlst;
rollback;
dbms_output.put_line('dynsql update returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; /*在动态DELETE中使用BULK COLLECT*/
begin
EXECUTE IMMEDIATE 'delete t01 where id <= 80 returning id into :1'
RETURNING bulk collect INTO v_numlst;
rollback;
dbms_output.put_line('dynsql delete returning: ' || v_numlst.COUNT);
exception
when others then
dbms_output.put_line(sqlerrm);
end; dbms_output.put_line('End ;');
end;
/ ANONYMOUS BLOCK
v_numlst: 10
cannot mix between single row and multi-row (BULK) in INTO list
v_recdat.NUMS: 10
fetch cursor: 30
insert returning: 35
update returning: 40
delete returning: 50
dynsql select: 60
dynsql insert returning: 65
dynsql update returning: 70
dynsql delete returning: 80
End ;
总结
BULK COLLECT INTO 的目标对象必须是集合类型。
limit减少内存占用,如果数据量较大一次性全部加载到内存中,对PGA来说压力太大,可采用limit的方法一次加载一定数量的数据,建议值通常为1000。 使用limit时注意,循环的时候如果用while cursor_name%found loop,对于最后一次fetch的数据量不足设定值,%found条件就会不成立,可以使用 集合变量.count > 0 作为判断条件。
最新文章
- JSP内置对象及常用方法
- Android 常用布局视图
- 05_动手动脑之String.equals()方法的实现代码
- DatagridView 最后编辑状态数据无法自动提交的问题
- hdu 5272 Dylans loves numbers
- android requestDisallowInterceptTouchEvent用途
- Linux_常用命令
- appium简明教程
- 绿色版Tomcat 启动 + 停止 + 随系统自动启动 - - 博客频道 - CSDN.NET
- 浅谈JavaScript的apply和call语句
- 完美解决浮动IE6 7中的兼容性BUG问题
- .net core 发布linux报错“The configured user limit (128) on the number of inotify instances has been reached”
- 大数据mapreduce俩表join之python实现
- linux的基本操作(文件压缩与打包)
- com.android.dx.command.Main with arguments
- 【Scala】Scala-None-null引发的血案
- 20145310《网络对抗》注入shellcode及Return-to-libc
- ExcelReader(解析Excel的工具类)
- SQL注入不简单?那是你没有懂它的原理~
- [EffectiveC++]item03:尽可能使用const 并且转载一篇关于const函数的博客
热门文章
- CAP:多重注意力机制,有趣的细粒度分类方案 | AAAI 2021
- Vue2自定义插件的写法-Vue.use()
- ansible-playbook批量修改密码
- Ubuntu系统iptables安全防护整改计划
- 链表设计与Java实现,手写LinkedList这也太清楚了吧!!!
- SpringCloudGateway微服务网关实战与源码分析 - 中
- Note -「Dijkstra 求解 MCMF」
- IDEA中web项目打成war包并在本地tomcat部署(超细版)
- Harbor企业级私服Docker镜像仓库搭建及应用
- 【每天学一点-02】创建Node.js的第一个应用