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

概述

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 作为判断条件。