oracle PL/SQL：从表中选择到关联数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5183330/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL: Selecting from a table into an assoc array
提问by Seaux
I am trying to select data into a pl/sql associative array in one query. I know I can do this with a hardcoded key, but I wanted to see if there was some way I could reference another column (the key column) instead.
我试图在一个查询中将数据选择到 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;
I get an error saying foo must be declared when I do this. Is there some way to create my associate array in a single query or do I need to fall back on a FOR loop?
执行此操作时，我收到一条错误消息，提示必须声明 foo。有什么方法可以在单个查询中创建我的关联数组，还是需要回退到 FOR 循环？
回答by Dave Costa
Just read your comment on APC's answer, it sounds like you figured this out on your own. But I figured I'd put the answer in anyway for future searchers.
只需阅读您对 APC 答案的评论，听起来您自己就想通了。但我想我会为未来的搜索者提供答案。
This is simpler code, but does not have the speed advantage of using BULK COLLECT. Just loop through the rows returned by the query and set the elements in the associative array individually.
这是更简单的代码，但没有使用 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;
回答by APC
It would be neat if it were possible but that isn't a straightforward way of acheiving this.
如果可能的话，它会很整洁，但这不是实现这一目标的直接方法。
What we can do is load the data into a regular PL/SQL collection and then load that into an associative array. Whethter this is faster than just looping round the table is a matter of tatse: it probably doesn't matter unless we're dealing with loads of data.
我们可以做的是将数据加载到常规 PL/SQL 集合中，然后将其加载到关联数组中。这是否比只是在桌子上循环更快是一个问题：除非我们处理大量数据，否则这可能无关紧要。
Given this test data ...
鉴于此测试数据...
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>
...we can populate an associative array in two steps:
...我们可以分两步填充关联数组：
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>
The real question is probably whether populating an associative array performs better than just selecting rows in the table. Certainly if you have 11g Enterprise edition you should consider result set cachinginstead.
真正的问题可能是填充关联数组是否比仅选择表中的行表现更好。当然，如果您有 11g 企业版，您应该考虑使用结果集缓存。
回答by Michael Broughton
are you absolutely married to associative arrays? And I assume that you are doing this because you want to be able to do a lookup against the array using a character key.
你绝对嫁给了关联数组吗？我假设您这样做是因为您希望能够使用字符键对数组进行查找。
If so, have you considered implementing this as a collection type instead?
如果是这样，您是否考虑过将其实现为集合类型？
e.g.
例如
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;
/
This gives you the lookup by character key value and lets you do everything in bulk.
这使您可以按字符键值进行查找，并让您批量执行所有操作。
