代码之家 › 专栏 › 技术社区 › Dan

pl/sql批量收集到具有稀疏键的关联数组中

associative-array plsql oracle

Dan · 技术社区 · 16 年前

我想在pl/sql中执行一个SQL查询,并将结果填充到一个关联数组中,其中SQL中的一列将成为关联数组中的键。例如,假设我有一张桌子 Person 带列

PERSON_ID   INTEGER      PRIMARY KEY
PERSON_NAME VARCHAR2(50)

…价值观包括:

 PERSON_ID  |  PERSON_NAME
 ------------------------
 6          |  Alice
 15         |  Bob
 1234       |  Carol

我想把这张桌子批量收集成 TABLE OF VARCHAR2(50) INDEX BY INTEGER 这样钥匙 6 在这个关联数组中 Alice 等等。这可以用pl/sql实现吗?如果是,如何?

2 回复 | 直到 15 年前

Robert Giesecke 16 年前

不,您必须使用2个集合(ID、名称)或元素类型为记录的集合。

下面是后者的示例:

  cursor getPersonsCursor is
    SELECT ID, Name
    FROM   Persons
    WHERE  ...;

  subtype TPerson is getPersonsCursor%rowtype;
  type TPersonList is table of TPerson;
  persons TPersonList;
begin

open getPersonsCursor;
fetch getPersonsCursor
  bulk collect into persons;
close getPersonsCursor;

if persons.Count > 0 then
  for i in persons.First .. persons.Last loop
    yourAssocArray(persons(i).ID) := persons(i).Name;
  end loop;
end if;

APC 16 年前

如果要在关联数组的索引中指定值,则必须使用以下语法:

SQL> declare
  2      type n_array is table of varchar2(30)
  3           index by binary_integer;
  4      emp_names n_array;
  5  begin
  6      for r in ( select ename, empno from emp )
  7      loop
  8          emp_names(r.empno) := r.ename;
  9      end loop;
 10
 11      dbms_output.put_line('count='||emp_names.count()
 12                               ||'::last='||emp_names.last());
 13      dbms_output.put_line(emp_names(8085));
 14
 15  end;
 16  /
count=19::last=8085
TRICHLER

PL/SQL procedure successfully completed.

SQL>

我们可以使用大容量收集填充关联数组,但前提是索引是整数,并且我们很乐意使用(隐式)rownum索引,即不是稀疏键…

SQL> declare
  2      type n_array is table of varchar2(30)
  3           index by binary_integer;
  4      emp_names n_array;
  5  begin
  6      select ename
  7      bulk collect into emp_names
  8      from emp ;
  9
 10      dbms_output.put_line('count='||emp_names.count()
 11                               ||'::last='||emp_names.last());
 12      dbms_output.put_line(emp_names(19));
 13
 14  end;
 15  /
count=19::last=19
FEUERSTEIN

PL/SQL procedure successfully completed.

SQL>

公平地说,如果您需要使用批量收集,那么您可能处理的数据可能比关联数组要多。

编辑

两种方法的廉价ISH性能测试:

SQL> declare
  2      type n_array is table of varchar2(30)
  3           index by binary_integer;
  4      emp_names n_array;
  5      s_time pls_integer;
  6      e_time pls_integer;
  7  begin
  8      s_time := dbms_utility.get_time;
  9      select ename
 10      bulk collect into emp_names
 11      from big_emp
 12      where rownum <= 500;
 13      dbms_output.put_line('bulk collect elapsed time = '
 14                              ||to_char(dbms_utility.get_time - s_time));
 15      s_time := dbms_utility.get_time;
 16      for r in ( select ename, empno from big_emp
 17                 where rownum <= 500 )
 18      loop
 19          emp_names(r.empno) := r.ename;
 20      end loop;
 21      dbms_output.put_line('sparse array elapsed time = '
 22                              ||to_char(dbms_utility.get_time - s_time));
 23  end;
 24  /

bulk collect elapsed time = 0
sparse array elapsed time = 0

PL/SQL procedure successfully completed.

SQL>

挂钟性能测试是出了名的糟糕。但是对于几百条记录来说,任何差异都不太值得担心,当然是在我们可能希望使用关联数组的情况下。

编辑2

@丹说:

在我看来,我想问一个大小合适的行数可用于持续时间查找应该是相当普遍的需求

这真的取决于你对“一个合适的数字”的定义。我们真的想在很多情况下 关联数组 有数千行,有字符串索引?当我们处理这些数字时,一个普通的数据库表可能同样有用,特别是在 11g Enterprise Edition with resultset caching 。