oracle PL/SQL 批量收集到具有稀疏键的关联数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2715736/
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 bulk collect into associative array with sparse key
提问by Dan
I want to execute a SQL query inside PL/SQL and populate the results into an associative array, where one of the columns in the SQL becomes the key in the associative array. For example, say I have a table Personwith columns
我想在 PL/SQL 中执行 SQL 查询并将结果填充到关联数组中，其中 SQL 中的列之一成为关联数组中的键。例如，假设我有一个Person带有列的表
PERSON_ID INTEGER PRIMARY KEY
PERSON_NAME VARCHAR2(50)
...and values like:
...和价值观，如：
PERSON_ID | PERSON_NAME
------------------------
6 | Alice
15 | Bob
1234 | Carol
I want to bulk collect this table into a TABLE OF VARCHAR2(50) INDEX BY INTEGERsuch that the key 6in this associative array has the value Aliceand so on. Can this be done in PL/SQL? If so, how?
我想将此表批量收集到一个TABLE OF VARCHAR2(50) INDEX BY INTEGER这样的6关联数组中的键具有值Alice等等。这可以在 PL/SQL 中完成吗？如果是这样，如何？
回答by Robert Giesecke
No, you have to use either 2 collections (id, name) or one whose element type is a record.
不，您必须使用 2 个集合（id、名称）或元素类型为记录的集合。
Here's a sample of the latter:
这是后者的示例：
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;
回答by APC
If we want to specify the value in an associative array's index then we have to use this syntax:
如果我们想在关联数组的索引中指定值，那么我们必须使用以下语法：
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>
We canpopulate associative arrays with bulk collect but only if the index is an integer, and we are happy to index by (an implicit) ROWNUM, i.e not a sparse key...
我们可以使用批量收集填充关联数组，但前提是索引是整数，并且我们很乐意通过（隐式）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>
To be fair, if you need to use BULK COLLECT you are probably dealing with more data than is appropriate for an associative array.
公平地说，如果您需要使用 BULK COLLECT，您可能要处理的数据多于适合关联数组的数据。
Edit
编辑
A cheap-ish performance test of the two approaches:
两种方法的廉价性能测试：
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>
Wall-clock performance tests are notoriously ropey. But for a few hundred records, any difference is unlikely to be worth worrying about, certainly in the context of the sort of place where we might want to use an assoociative array.
众所周知，挂钟性能测试非常棘手。但是对于几百条记录，任何差异都不值得担心，当然在我们可能想要使用关联数组的地方的上下文中。
Edit 2
编辑 2
@Dan said:
@Dan 说：
t seems to me that wanting to query a decent sized number of rows into a data structure that can be used for constant-time lookup ought to be a pretty common need
在我看来，想要将相当数量的行查询到可用于恒定时间查找的数据结构中应该是一个非常普遍的需求
It really depends on your definition of "a decent sized number". Are there really that many cases where we would want to populate an associative arraywith thousands of rows, with a string index? When we get to those sorts of numbers a normal database table might be just as useful, especially on 11g Enterprise Edition with resultset caching.
这实际上取决于您对“大小合适的数字”的定义。真的有很多情况我们想要用字符串索引填充具有数千行的关联数组吗？当我们处理这些类型的数字时，普通的数据库表可能同样有用，尤其是在具有结果集缓存的 11g 企业版上。
