МН объемный / объемный SQL собираются в ассоциативный массив с редким ключом
Я хочу выполнить SQL-запрос, внутри МН / SQL и заполнить результаты в ассоциативный массив, где один из столбцов в SQL становится ключом в ассоциативном массиве. Например, скажите, что у меня есть таблица Person со столбцами
PERSON_ID INTEGER PRIMARY KEY
PERSON_NAME VARCHAR2(50)
... и значения как:
PERSON_ID | PERSON_NAME
------------------------
6 | Alice
15 | Bob
1234 | Carol
Я хочу увеличить объем, собирают эту таблицу в a TABLE OF VARCHAR2(50) INDEX BY INTEGER таким образом, что ключ 6 в этом ассоциативном массиве имеет значение Alice и так далее. В этом можно выполнить МН / SQL? Если так, как?
2 ответа
Нет, вы должны использовать либо две коллекции (id, name), либо одну, тип элемента которой является записью.
Вот пример последнего:
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;
Если мы хотим указать значение в индексе ассоциативного массива, мы должны использовать следующий синтаксис:
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>
Мы можем заполнить ассоциативные массивы с помощью массового сбора, но только если индекс является целым числом, и мы рады индексировать по (неявному) 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>
Честно говоря, если вам нужно использовать BULK COLLECT, вы, вероятно, имеете дело с большим количеством данных, чем требуется для ассоциативного массива.
Править
Дешевый тест производительности двух подходов:
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>
Тесты производительности настенных часов, как известно, ненадежны. Но для нескольких сотен записей вряд ли стоит беспокоиться о каких-либо различиях, особенно в контексте того места, где мы могли бы захотеть использовать ассоциативный массив.
Редактировать 2
@Dan сказал:
Мне кажется, что я хочу запросить количество строк приличного размера в структуру данных, которую можно использовать для {{ 1}} поиск в постоянном времени должен быть довольно распространенной потребностью
. Это действительно зависит от вашего определения «числа приличного размера». Неужели так много случаев, когда мы хотели бы заполнить ассоциативный массив тысячами строк со строковым индексом? Когда мы дойдем до такого рода чисел, обычная таблица базы данных может оказаться столь же полезной, особенно в 11g Enterprise Edition с кэшированием набора результатов .