Как выбрать данные в ассоциативную коллекцию одним запросом?

Question

Пытаюсь выбрать данные в ассоциативную коллекцию PL/SQL одним запросом. Знаю, что могу сделать это ууказав лиерал ключа, но я хотел посмотреть, есть ли какой-нибудь способ, чтобы я мог ссылаться на другой столбец (столбец ключа) вместо литерала.

DECLARE
     TYPE VarAssoc IS TABLE OF varchar2(2) INDEX BY varchar2(3);
     vars VarAssoc;
BEGIN
     SELECT bar INTO vars(foo) FROM schema.table;
END;

Но получаю ошибку, говорящую, что foo должен быть объявлен.

Есть ли какой-то способ выбрать данные в ассоциативную коллекцию в одном запросе?
Или всё таки нужен цикл FOR?

_{Свободный перевод вопроса PL/SQL: Selecting from a table into an assoc array от участника @Seaux}

Answer 1

Достаточно пройтись по строкам, возвращаемым запросом, и присваивать значения элементам в ассоциативной коллекции по отдельности.

Код простой, но у него нет преимущества в производительности по сравнению с 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;

_{Свободный перевод ответа от участника @Dave Costa}

Answer 2

Ассоциативные коллекции это PL/SQL тип, его использование в SQL запросах ограничено.
Попытка заполнить ассоциативную коллекцию с символьным ключом в запросе закончится:

PLS-00657: Implementation restriction: bulk SQL with associative arrays with VARCHAR2 key is not supported.

Можно воспользоваться SQL пользовательским типом данных, который будет симулировать ассоциативную коллекцию. Это решение более гибкое, будет работать как в запросах, так и PL/SQL. Учитывая, что в таких коллекциях обычно не хранятся большие объёмы данных, то возможную неэффективность по сравнению с встроенными ассоциативными коллекциями, можно будет просто не принимать во внимание.

Вот пример реализации коллекции:

create or replace type mapT force as object (key varchar2 (8), val varchar2 (8),
    map member function mapkey return varchar2)
/
create or replace type body mapT as 
    map member function mapkey return varchar2 is 
    begin return self.key; end;
end;    
/
create or replace type mapsT force as table of mapT 
/

Пример использования:


var rc refcursor
declare 
    m mapsT;
    val varchar2 (8); 
    function get (m mapsT, key varchar2) return varchar2 is
        ret mapsT := m multiset intersect mapsT(mapT (key, null));
    begin
        return case when ret.exists(1) then ret(1).val end;
    end;
begin 
    select 
        cast (multiset (select key, val from params) as mapsT) into m 
    from dual;
    val := get (m, 'key2'); 
    open :rc for 
        select 'collection' output, m.* from table (m) m union all 
        select 'got by key', 'key2', val from dual
        order by 2 asc;
end;
/

Результат:

OUTPUT     KEY      VAL                             
---------- -------- --------------------------------
collection key1     val1                            
got by key key2     val2                            
collection key2     val2                            
collection key3     val3

БЛОГ НА HUSL

Как выбрать данные в ассоциативную коллекцию одним запросом?

Ответы (2 шт):