PL/SQL 批量收集到具有稀疏键的关联数组中
我想在 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 的值为 爱丽丝 等等.这可以在 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;
相关文章