有没有一种方法可以将一个PL/SQL数组变量的内容批量复制到另一个变量中? [英] Is there a way to bulk copy the contents of one PL/SQL array variable into another?

问题描述

说我的包中有2个相同类型的表变量.请注意，这些类型对于程序包而言是本地的.

Say I have 2 table variables of the same type within my package. Note that these types are local to the package.

TYPE MyTableType is table of some_table%ROWTYPE;
table1 MyTableType;
table2 MyTableType;

我在软件包中有一个过程，可以将一些数据加载到table1和table2中.这样做之后的某个时候，我想将当前在table1中的所有内容添加到table2中.

And I have a procedure in the package which loads some data into table1 and table2. At some point after doing so, I want to add everything currently in table1 into table2.

是否有比循环table1和.extend -ing table2更好的方法，然后在每次迭代中设置.last元素的值?

Is there any better way to do this than looping table1 and .extend-ing table2, then setting the value of the .last element on every iteration?

我正在寻找某种快速的批量操作(如果存在).是吗?

I'm looking for some sort of speedy bulk operation, if it exists. Does it?

推荐答案

22:02:22 SYSTEM@dwal> ed
Wrote file S:\spool\dwal\BUFFER_SYSTEM_386.sql

  1   declare
  2     type t is table of dual%rowtype;
  3     c1 t := t();
  4     c2 t := t();
  5   begin
  6     c1.extend;
  7     c1(1).dummy := 'a';
  8     c2.extend(2);
  9     c2(1).dummy := 'b';
 10     c2(2).dummy := 'c';
 11     c2 := c1 multiset union all c2;
 12     for i in c2.first .. c2.last loop
 13       dbms_output.put_line(c2(i).dummy);
 14     end loop;
 15*  end;
22:02:41 SYSTEM@dwal> /
a
b
c

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26

upd :尽管体积庞大，但我似乎没有机会对多集运算进行基准测试，aaaaaand:

upd: I never had a chance to benchmark multiset operations, aaaaaand, despite being bulk they seem to be actually slower:

22:14:56 SYSTEM@dwal> ed
Wrote file S:\spool\dwal\BUFFER_SYSTEM_331.sql

  1  declare
  2    cnt int := 1e5;
  3    type t is table of dual%rowtype;
  4    c1 t := t();
  5    c2 t := t();
  6    timer int;
  7    procedure prebuild as
  8    begin
  9      c1.delete;
 10      c2.delete;
 11      c1.extend(cnt);
 12      c2.extend(cnt);
 13      for i in 1 .. cnt loop
 14        c1(i).dummy := dbms_random.string('l', 1);
 15        c2(i).dummy := dbms_random.string('l', 1);
 16      end loop;
 17    end;
 18  begin
 19    -- 1
 20    prebuild;
 21    timer := dbms_utility.get_cpu_time;
 22    for i in 1 .. cnt loop
 23      c2.extend;
 24      c2(c2.last) := c1(i);
 25    end loop;
 26    dbms_output.put_line(dbms_utility.get_cpu_time - timer);
 27    -- 2
 28    prebuild;
 29    timer := dbms_utility.get_cpu_time;
 30    c2 := c2 multiset union all c1;
 31    dbms_output.put_line(dbms_utility.get_cpu_time - timer);
 32    -- 3
 33    prebuild;
 34    timer := dbms_utility.get_cpu_time;
 35    c2.extend(c1.count);
 36    for i in 1 .. cnt loop
 37      c2(c2.count - c1.count + i) := c1(i);
 38    end loop;
 39    dbms_output.put_line(dbms_utility.get_cpu_time - timer);
 40* end;
22:15:00 SYSTEM@dwal> /
15
25
10

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.38

这些数字非常稳定.第三种方法，它只将第二个集合扩展一次，然后追加到第二个集合，似乎是最快的方法.

These numbers are quite stable. Third approach, which extends second collection only once, and then appends to it, seems to be the fastest one.

这篇关于有没有一种方法可以将一个PL/SQL数组变量的内容批量复制到另一个变量中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！