PL/SQL使用稀疏键将大量数据收集到关联数组中 [英] PL/SQL bulk collect into associative array with sparse key
问题描述
我想在PL/SQL中执行SQL查询并将结果填充到关联数组中,其中SQL中的列之一成为关联数组中的键.例如,假设我有一个带有列的表Person
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 Person
with columns
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中完成吗?如果可以,怎么办?
I want to bulk collect this table into a TABLE OF VARCHAR2(50) INDEX BY INTEGER
such that the key 6
in this associative array has the value Alice
and so on. Can this be done in PL/SQL? If so, how?
推荐答案
否,您必须使用2个集合(id,名称)或一个元素类型为记录的集合.
No, you have to use either 2 collections (id, name) or one whose element type is a record.
这里是后者的示例:
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;
这篇关于PL/SQL使用稀疏键将大量数据收集到关联数组中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!