Oracle学习笔记：利用oracle的 “表类型”复合变量，实现临时表的功能

情景及需求：

有A表为事实表，B为历史表

create table A (fact_id int not null primary key,name varchar2(50));

create table B (log_id int not null primary key,name varchar2(50),addtime timestamp);

　　需求：建立存储过程prc，外部程序并行周期调用。该过程从A表取得一定量的数据（记录集）备份到B表，并从A中删除该批备份的数据（记录集），同时返回该批数据（记录集）到外部程序供外部程序使用

 

分析：

要实现上述需求，首先考虑该过程用于并行处理，需要对每一个处理进程进行事务隔离——既需要实现读隔离，oracle中可以通过select ...  from A   for update 实现。而每次处理的记录数可以通过订阅存储过程的入参“rowcount int”实现。返回记录集可以通过一个“rs out ref_syscursor”实现。而关键的部分是如果实现从A到B的记录转移？通常我们可以的定义一个事物级的临时表T来实现：A->T，T[+A]->B，从A删除T，返回T的记录集，删除T。虽然oracle的临时表已经做了很多的性能优化且具有自己的特点，但仍然是和磁盘打交道的做法——如果临时表的数据量很大——这是个很不错的选择，但如果处理的中间数据量不是很大，通过内存变量结构来实现岂不是更好吗！为此sqlserver中提供表变量这一机制，而oracle中复合数据类型[或称集合数据类型]“表类型”正是解决这一问题的机制，为了具有标准sql类似的集合操作oracle提供了在pl/sql中使用的bulk collect、for all操作。

 

实现代码：

create or replace procedure prc
(
    rowcount int,
    rs       out sys_refcursor
)
as
    TYPE t_fact_id is table of int; --定义table类型
    vt_fact_id      t_fact_id; --定义table类型的变量
    v_first_fact_id int;
    v_last_fact_id  int;
begin
    --批量获取A的fact_id字段信息到t_fact_id表类型的变量vt_fact_id中
    select fact_id bulk collect
    into   vt_fact_id
    from   a
    where  rownum <= rowcount
    for    update
    order  by fact_id;

    --批量插入到B
    forall i in vt_fact_id.first .. vt_fact_id.last
        insert into b
            select a.*, sysdate
            from   a
            where  fact_id = vt_fact_id(i);

    --获取插入到表变量的A表的fact_id的最小值、最大值，主要是为了定义输出结果集的条件
    v_first_fact_id := vt_fact_id(vt_fact_id.first);
    v_last_fact_id  := vt_fact_id(vt_fact_id.last);

    --输出结果集
    open rs for
        select *
        from   a
        where  fact_id between v_first_fact_id and v_last_fact_id;

    --批量删除A中的记录
    forall i in vt_fact_id.first .. vt_fact_id.last
        delete from a
        where  fact_id = vt_fact_id(i);

    commit;

exception
    when others then
        dbms_output.put_line(sqlerrm);
        open rs for
            select *
            from   a
            where  0 = 1;
end;

 

讨论：

1.如果A表的primary key列fact_id是直接插入到B表的，其实可以使用dml(delete)的returning fact_id bulk collect into vt_fact_id来获得记录集的fact_id表类型变量数据，而不用如例子中先select from A for update了，之后从B表获得返回的结果集

2.例子中没有在正常的程序处理部分处理vt_fact_id是empty(不是null)，而是通过exception部分处理的