bulk collect 在KingbaseES和Oracle的使用方法比较

最新推荐文章于 2024-04-26 13:29:49 发布
最新推荐文章于 2024-04-26 13:29:49 发布
阅读量727 收藏
点赞数
文章标签： mysql 数据库 sql java mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lyu1026/article/details/124464735
版权

概述

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 ;

总结

  1. BULK COLLECT INTO 的目标对象必须是集合类型。

  2. limit减少内存占用，如果数据量较大一次性全部加载到内存中，对PGA来说压力太大，可采用limit的方法一次加载一定数量的数据，建议值通常为1000。 使用limit时注意，循环的时候如果用while cursor_name%found loop，对于最后一次fetch的数据量不足设定值，%found条件就会不成立，可以使用 集合变量.count > 0 作为判断条件。

Kingbase 研究院
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
基于keras的YOLOv3的代码详解
何少的博客
04-25 1万+
（一）test_single_image.py 默认输入图片尺寸为[416,416]。 # coding: utf-8 from __future__ import division, print_function import tensorflow as tf import numpy as np import argparse import cv2 from utils.misc_uti...
Oracle BULK COLLECT INTO批量操作应用
个人公众号：Alen的数据库学习笔记
07-19 2378
在Oracle开发中，有时我们会使用BULK COLLECT INTO批量操作大量的数据，如果没有LIMIT限制，则Oracle会读取查询的所有记录行到内存中，这样很容易造成PGA溢出而导致出现错误。所以使用LIMIT关键字可以避免PGA溢出。 示例： SQL> DECLARE 2 TYPE typ_emp IS TABLE OF emp%ROWTYPE INDEX BY PLS
oracle 11g 下使用bulk collect
我的编程路-精致技术
11-26 920
oracle 11g 下使用bulk collect ... declare S_RQ DATE ; CURSOR S IS SELECT RQ BULK COLLECT INTO S_RQ FROM MIS_HIS_R_FEE_MX; ... 此处报错 pl-00497 无法混合INTO列表中的单行与多行 ...
MySQL优化：摘录自手册 MySQL 5.6 Reference Manual :: 8 Optimization
wulin76的博客
09-26 2869
http://user.qzone.qq.com/892054726/blog/1439803064 http://dev.mysql.com/doc/refman/5.6/en/optimize-overview.html 8.1 Optimization Overview Optimizing at the Database Level  The most im
Returning 子句的使用 (附录 bulk collect)---20180206
Richie's 残酷舞台
02-06 710
——Part 1  在对数据表进行dml操作（Insert、Update和delete）的时候，有时会需要获取到进行操作的数据。最简单的方法就是在DML操作之前或者之后进行一下select操作，将数据获取到。此外，还可以使用一种更为简洁的方法，就是使用Oracle SQL的returning into子句。   Returning Into简介   在进行insert、updat
Oracle Bulk 与 性能优化 说明
ijavagos
06-30 202
一. Bulk 概述 本来只想测试一下Bulk Collect 和update性能的，但发现Bulk 的东西还是很多的，在OTN上搜了一些，整理如下。 1.1 Bulk Binding 和 Bulk SQL From: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_packages.htm...
Oracle BULK COLLECT批量取数据解决方法
12-16
代码如下: — Created on 2010/11/04 by WANGNAN declare — Local variables here i integer; TYPE t_table IS TABLE OF VARCHAR2(10 ) INDEX BY VARCHAR2 (2); v_t_table t_table; TYPE t_pg3 IS TABLE OF asis.PG...
Oracle批量查询、删除、更新使用BULK COLLECT提高效率
09-09
BULK COLLECT(成批聚合类型)和数组集合type类型is table of 表%rowtype index by binary_integer用法笔记。对oracle批量查询 删除 更新使用bulk collect提高效果的相关知识感兴趣的朋友通过本文一起学习吧
Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据
07-25
Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据
转:// Oracle PL/SQL 优化与调整 -- Bulk 说明
weixin_34019929的博客
07-14 140
一. Bulk概述 本来只想测试一下Bulk Collect和update性能的，但发现Bulk的东西还是很多的，在OTN上搜了一些，整理如下。 1.1 Bulk Binding和Bulk SQL From:http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_package...
oracle return into 用法
weixin_33730836的博客
11-30 339
CREATE TABLE t1 (id NUMBER(10),description VARCHAR2(50),CONSTRAINT t1_pk PRIMARY KEY (id)); CREATE SEQUENCE t1_seq; INSERT INTO t1 VALUES (t1_seq.nextval, 'ONE'); INSERT INTO t1 VALUES (t1_seq.next...
oracle中bulk collect into用法
热门推荐
10-09 2万+
通过bulk collect减少loop处理的开销 采用bulk collect可以将查询结果一次性地加载到collections中。 而不是通过cursor一条一条地处理。 可以在select into,fetch into,returning into语句使用bulk collect。 注意在使用bulk collect时，所有的into变量都必须是collections.
oracle批量DML(bulk connect into & forall)
abby_db的博客
12-03 938
批量dml会用到这两个，主要原理是加速SQL执行速度，原理是利用内存运算和减少sql引擎和plsql引擎的交互伪代码如下： 1.不利用游标限制单次引擎的交互 declare --定义记录变量或者rowtype类型 type rec is record(a varchar2(10), b varchar2(8)); --定义嵌套表或者关联数组
报错Cannot mix different versions of joi schemas解决方法
小助手爱编程
10-08 4494
版本更新了，以前的用不了 1.用 npm i joi 命令重新下载 2.修改一起的代码，改为 constjoi=require('joi'); 然后就不报错了
MySQL-多表查询
qq_57157023的博客
04-25 988
MySQL多表查询
Mysql_数据库事务
最新发布
weixin_69252724的博客
04-26 1181
事务就是由单独单元的一个或多个sql语句组成，在这个单元中，每个sql语句都是相互依赖的。而整个单独单元是作为一个不可分割的整体存在，类似于化学当中的原子（一种不可分割的最小单位）。往通俗的讲就是，事务就是一个整体，里面的内容要么都执行成功，要么都不成功。不可能存在部分执行成功而部分执行不成功的情况。就是说如果单元中某条sql语句一旦执行失败或者产生错误，那么整个单元将会回滚(返回最初状态)。
88张表-Mysql
2301_80151755的博客
04-25 233
【代码】88张表-Mysql。
问题-MySQL将较大的SQL文件导入MySQL
qq_1905369638
04-25 435
迁移数据的时候，我们有时候会用sqlyog等数据库工具导入到新数据库。可能插入的SQL语句太大，出现导入一半失败的情况。明明代码没错，这让人摸不着头脑。
举个Oracle的bulk collect into的例子
06-08
以下是一个Oracle的bulk collect into的例子： ``` DECLARE TYPE emp_tab_type IS TABLE OF employees%ROWTYPE; emp_tab emp_tab_type; BEGIN SELECT * BULK COLLECT INTO emp_tab FROM employees WHERE hire...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值