Какой тип коллекции выбрать

#sql #oracle #plsql

Прочитав книгу по PL/SQL я так и не понял в полной мере, какой тип коллекции лучше
использовать, если не требуется ее использование в SQL командах, не нужно к ней применять
операции над множествами и так далее. Потому, что главным преимуществом ассоциативной
коллекции  перед вложенной в вышеописанной ситуации, как правило, позиционируется то,
что ее не нужно расширить. Но хотелось бы понять, как это влияет на производительность,
и какая  коллекция будет более совершенной. Так же хотелось бы понять, какие плюсы
дает varray по сравнению с вложенной таблицей.  

Вопрос можно переформулировать вот так: почему бы в любой ситуации просто не использовать
вложенные таблицы, так как они являются самыми функциональными? 
    

Ответы

Ответ 1

Всё зависит от того, где и как коллекция используется, универсальной нет. 

В PL/SQL коде лучше использовать ассоциативную коллекциию. Немного производительней,
не надо расширять, не только числленный индех, но недоступна в SQL и нет конструктора.
Также, со слов Oracle, они эфективней конвертируются в хост переменные.

В SQL, если надо сохранять как столбец в таблицах, varrays и nested tables несколько
отличаются - varrays сохраняются как LOB, nested tables в собственной таблице (отсюда
и название). Некоторые DML доступны для nested tables, но недоступны для varrays.

Пример ассоциативной коллекции с varchar2 ключом и инициализацией из таблицы
(используется схема примеров sh):

declare 
    type items_type is table of varchar2(64) index by varchar2(64);
    items items_type;

    function items_ctor(stmt varchar2) return items_type is
         type pair_row is record (
            key varchar2(64),
            value varchar2(64)
            );
        type refcur_type IS REF CURSOR; --return pair_row;    
        rc refcur_type;
        type pairs_type is table of pair_row index by pls_integer;
        pairs pairs_type;
        ret items_type;
    begin
        open rc for stmt;
        fetch rc bulk collect into pairs;
        for idx in pairs.first..pairs.last loop
            ret(pairs(idx).key) := pairs(idx).value; 
        end loop;
        return ret;
    end;

begin
    items := items_ctor(q'[
        select 'E' || to_char(prod_id) prod_code,  prod_name
        from products
        where prod_name like 'E%'
        ]');

    <> declare key varchar2(32) := items.first; 
    begin while key is not null loop
        dbms_output.put_line('item(' || key || ')=' || items(key));
        key := items.next(key);
    end loop; end foreach;
end;
/


Подробнее в оф. док. "5 PL/SQL Collections and Records".