PL/SQL：从表中选择到关联数组中

Question

我正在尝试在一个查询中将数据选择到 pl/sql 关联数组中。我知道我可以使用硬编码密钥来做到这一点，但我想看看是否有某种方法可以引用另一列（密钥列）。

DECLARE
TYPE VarAssoc IS TABLE OF varchar2(2) INDEX BY varchar2(3);
vars VarAssoc;
BEGIN
SELECT foo, bar INTO vars(foo) FROM schema.table;
END;

我收到一条错误消息，提示我执行此操作时必须声明 foo 。有没有某种方法可以在单个查询中创建我的关联数组，或者我是否需要依靠 FOR 循环？

Answer 1

看看你对 APC 答案的评论，听起来你自己想出了这个办法。但我想无论如何我都会为未来的搜索者提供答案。

这是更简单的代码，但不具有使用 BULK COLLECT 的速度优势。只需循环查询返回的行并单独设置关联数组中的元素即可。

DECLARE
  TYPE VarAssoc IS TABLE OF varchar2(200) INDEX BY varchar2(30);
  vars VarAssoc;
BEGIN
  FOR r IN (SELECT table_name,tablespace_name FROM user_tables) LOOP
    vars(r.table_name) := r.tablespace_name;
  END LOOP;

  dbms_output.put_line( vars('JAVA$OPTIONS') );
END;

Answer 2

如果可能的话那就太好了，但这并不是实现这一目标的直接方法。

我们可以做的是将数据加载到常规 PL/SQL 集合中，然后将其加载到关联数组中。这是否比仅仅在桌子上循环更快是一个问题：除非我们要处理大量数据，否则这可能并不重要。

给定这个测试数据……

SQL> select * from t23
  2  order by c1
  3  /

C1 C2
-- ---
AA ABC
BB BED
CC CAR
DD DYE
EE EYE
ZZ ZOO

6 rows selected.

SQL>

我们可以分两步填充关联数组：

SQL> set serveroutput on
SQL>
SQL> declare
  2      type varassoc is table of varchar2(3) index by varchar2(2);
  3      vars varassoc;
  4
  5      type nt is table of t23%rowtype;
  6      loc_nt nt;
  7
  8  begin
  9      select * bulk collect into loc_nt from t23;
 10      dbms_output.put_line('no of recs = '||sql%rowcount);
 11
 12      for i in loc_nt.first()..loc_nt.last()
 13      loop
 14          vars(loc_nt(i).c1) := loc_nt(i).c2;
 15      end loop;
 16
 17      dbms_output.put_line('no of vars = '||vars.count());
 18
 19      dbms_output.put_line('ZZ = '||vars('ZZ'));
 20
 21  end;
 22  /
no of recs = 6
no of vars = 6
ZZ = ZOO

PL/SQL procedure successfully completed.

SQL>

真正的问题可能是填充关联数组是否比仅选择表中的行更好。当然，如果您有 11g 企业版，您应该考虑结果集缓存 相反。

Answer 3

你真的已经和关联数组结婚了吗？我假设您这样做是因为您希望能够使用字符键对数组进行查找。

如果是这样，您是否考虑过将其实现为集合类型？

例如，

CREATE OR REPLACE TYPE VAR_ASSOC as OBJECT(
  KEYID   VARCHAR2(3),
  DATAVAL VARCHAR2(2)
)
/

CREATE OR REPLACE TYPE VAR_ASSOC_TBL AS TABLE OF VAR_ASSOC
/

CREATE OR REPLACE PROCEDURE USE_VAR_ASSOC_TBL
AS
  vars Var_Assoc_tbl; 
  -- other variables...
BEGIN 
    select cast ( multiset (
                        select foo as keyid,
                               bar as dataval
                        from   schema.table
                           ) as var_Assoc_tbl
                )
     into vars
     from dual;   
     -- and later, when you want to do your lookups
     select  ot.newfoo 
            ,myvars.dataval
            ,ot.otherval
     into   ....       
     from   schema.other_Table ot
     join   table(vars) as myvars
     on     ot.newfoo = myvars.keyid;
end;
/     

这使您可以按字符键值进行查找，并允许您批量执行所有操作。