/привет/мир/etc: Коллекции в Oracle PL/SQL

Кандидатам на позиции, связанные с программированием на PL/SQL, на собеседованиях я задаю вопроc: "Существует ли в языке PL/SQL структура, подобная массиву? Если да, то расскажите о ней."

Из пары дестков кандидатов, с которыми довелось беседовать за три последних года, подавляющее большинство более или менее уверенно рассказывали об index-by table. Только один кандидат рассказал обо всех трех разновидностях коллекций в PL/SQL:

ассоциативный массив, или index-by table,
вложенная таблица, или nested table, и
массив переменной длинны, или varray.

О них и пойдет речь в этой статье.

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

А их особенности приведены в следующей таблице:

Характеристика	Ассоциативный массив	Вложенная таблица	Массив переменной длины
Количество элементов	не ограничено	не ограничено	ограничено при определении
Тип индекса	`VARCHAR2` или `PLS_INTEGER`	целое число, начиная с 1	целое число, начиная с 1
Может быть разреженным?	да	да, если удалить элементы	нет
Может быть `NULL`?	нет, будет пустым	да	да
Как инициализируется?	при объявлении переменной	конструктором	конструктором
Где определяется?	на уровне пакета или локально	на уровне схемы, пакета или локально	на уровне схемы, пакета или локально
Может быть атрибутом объектного типа?	нет	да, если определен на уровне схемы	да, если определен на уровне схемы
Можно сохранить в таблице?	нет	да, если определен на уровне схемы	да, если определен на уровне схемы

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

Метод коллекции	Что делает	Ограничение
`COUNT`	возвращает кол-во элементов коллекции
`DELETE[(index)]`	удаляет элемент с индексом `index`, или все элементы, если `index` не указан
`EXISTS(index)`	проверяет, существует ли элемент с индексом `index`
`EXTEND[(number [, index])]`	добавляет в конец коллекции `number` элементов со значением `NULL` или со значением элемента `index`, или один `NULL` элемент, если не указаны `number` и `index`	не используется с ассоциативным массивом
`FIRST`	возвращает индекс первого элемента
`LAST`	возвращает индекс последнего элемента
`LIMIT`	возвращает максимально возможное кол-во элементов коллекции для varray и `NULL` для nested table	не используется с ассоциативным массивом
`NEXT(index)`	возвращает индекс следующего элемента
`PRIOR(index)`	возвращает индекс предыдущего элемента
`TRIM[(number)]`	удаляет `number` последних элементов коллекции, или один, если `number` не указан	не используется с ассоциативным массивом

Разреженность коллекции означает, что при последовательном переборе всех числовых значений индекса от первого (метод FIRST) до последнего (метод LAST) для некоторых промежуточных значений индекса в коллекции может не быть элементов. Поскольку ассоциативный массив и вложенная таблица могут быть разреженными, то

для проверки существования элемента нужно использовать метод EXISTS,
для получения следующего индекса - метод NEXT,
для получения предыдущего индекса - метод PRIOR.

Элементы ассоциативного массива автоматически упорядочиваются по индексам для последовательного доступа, что видно из следующего примера. Пример демонстрирует работу с ассоциативным массивом и дает неплохое представлеие о его свойствах и методах:

set serveroutput on

declare
    type t_aa_list is table of varchar2(50) index by pls_integer;
    l_aa_list t_aa_list; -- can't be initialized with constructor
    i pls_integer;
begin
    dbms_output.put_line('-- index-by table --');

    -- index-by table is not null even before initialization
    dbms_output.put_line('Index-by table before init is ' ||
        case when l_aa_list is null then 'NULL' else 'NOT NULL' end);
    
    l_aa_list(9) := 'zyx';
    l_aa_list(5) := 'asdf';
    l_aa_list(3) := 'qwerty';
    
    l_aa_list.delete(5);
    dbms_output.put_line('Element 5 after deletion ' ||
        case when l_aa_list.exists(5) then 'exists' else 'DOES NOT exist' end);
    
    -- retrieved in a sorted order!
    i := l_aa_list.first;
    while i is not null loop
        dbms_output.put_line(i || ' : ' || l_aa_list(i));
        i := l_aa_list.next(i);
    end loop;
    
    dbms_output.put_line('count: '|| l_aa_list.count);
    l_aa_list.delete; -- delete all elements
    dbms_output.put_line('count: '|| l_aa_list.count);
    if l_aa_list is null then
        dbms_output.put_line('No! I don''t believe it!');
    end if;
end;
/
-- index-by table --
Index-by table before init is NOT NULL
Element 5 after deletion DOES NOT exist
3 : qwerty
9 : zyx
count: 2
count: 0
PL/SQL procedure successfully completed

Следующий пример демонстрирует свойства и методы вложенной таблицы. В отличие от ассоциативного массива, элементы в который добавляются просто присваиванием значения элементу с новым индексом, для добавления элементов во вложенную таблицу необходимо вызывать метод EXTEND (и затем присваивать добавленным элементам значения):

declare
    type t_nt_list is table of varchar2(50);
    l_nt_list t_nt_list;
    i pls_integer;
begin
    dbms_output.put_line('-- nested table ----');

    -- nested table is null before initialization
    dbms_output.put_line('Nested table before init is ' ||
        case when l_nt_list is null then 'NULL' else 'NOT NULL' end);

    l_nt_list := t_nt_list('qwerty', 'asdf', 'xyz');

    l_nt_list.delete(2); -- delete makes nested table sparse!
    dbms_output.put_line('Element 2 after deletion ' ||
        case when l_nt_list.exists(2) then 'exists' else 'DOES NOT exist' end);

    dbms_output.put_line('limit: '|| l_nt_list.limit);
    --l_nt_list(4) := 'hello'; -- ORA-06533: Subscript beyond count
    -- unlike varray, there is no limit to extend nested table!
    l_nt_list.extend(2);
    l_nt_list(4) := 'hello';
    l_nt_list(5) := 'world';

    i := l_nt_list.first;
    while i is not null loop
        dbms_output.put_line(i || ' : ' || l_nt_list(i));
        i := l_nt_list.next(i);
    end loop;

    dbms_output.put_line('count: '|| l_nt_list.count);
    l_nt_list.trim(2); -- delete 2 last elements
    dbms_output.put_line('count: '|| l_nt_list.count);
    l_nt_list.delete; -- delete all elements
    dbms_output.put_line('count: '|| l_nt_list.count);
    if l_nt_list is null then
        dbms_output.put_line('No! I don''t believe it!');
    end if;
end;
/
-- nested table ----
Nested table before init is NULL
Element 2 after deletion DOES NOT exist
limit: 
1 : qwerty
3 : xyz
4 : hello
5 : world
count: 4
count: 2
count: 0
PL/SQL procedure successfully completed

Следующий пример демонстрирует свойства и методы массива переменной длины. Если во вложенную таблицу после ее инициализации можно добавить сколько угодно новых элементов, то максимальный размер массива переменной длины ограничен при его определении.

declare
    type t_va_list is varray(3) of varchar2(50);
    l_va_list t_va_list;
    i pls_integer;
begin
    dbms_output.put_line('-- varray ----------');

    -- varray is null before initialization
    dbms_output.put_line('Varray before init is ' ||
        case when l_va_list is null then 'NULL' else 'NOT NULL' end);

    l_va_list := t_va_list('qwerty', 'asdf');
    -- l_va_list.delete(2); -- doesn't work with varray which is always dense!
    l_va_list(2) := null;
    dbms_output.put_line('Element 2 after assigning null ' ||
        case when l_va_list.exists(2) then 'exists' else 'DOES NOT exist' end);

    dbms_output.put_line('limit: '|| l_va_list.limit);
    --l_va_list(4) := 'hello'; -- ORA-06533: Subscript beyond count
    l_va_list.extend;
    l_va_list(3) := 'hello';
    -- extend no further than varray's limit!
    --l_va_list.extend; -- ORA-06532: Subscript outside of limit

    -- traverse varray in reverse order
    i := l_va_list.last;
    while i is not null loop
        dbms_output.put_line(i || ' : ' || l_va_list(i));
        i := l_va_list.prior(i);
    end loop;

    dbms_output.put_line('count: '|| l_va_list.count);
    l_va_list.trim(2); -- delete 2 last elements
    dbms_output.put_line('count: '|| l_va_list.count);
    l_va_list.delete; -- delete all elements
    dbms_output.put_line('count: '|| l_va_list.count);
    if l_va_list is null then
        dbms_output.put_line('No! I don''t believe it!');
    end if;
end;
/
-- varray ----------
Varray before init is NULL
Element 2 after assigning null exists
limit: 3
3 : hello
2 : 
1 : qwerty
count: 3
count: 1
count: 0
PL/SQL procedure successfully completed

Поскольку массив переменной длины не может быть разреженным, то для обхода всех его элементов можно безопасно использовать цикл FOR i IN <varray>.FIRST .. <varray>.LAST LOOP.

Теперь рассмотрим работу со столбцами таблиц, в которых хранятся вложенные таблицы и массивы переменной длины. (Хранить в таблице ассоциативные массивы нельзя.)

Прежде всего, определю соответствующие коллекционные типы и таблицу со столбцами этих типов:

SQL> CREATE TYPE t_nt_names IS TABLE OF VARCHAR2(50);
  2  /
Type created

SQL> 
SQL> CREATE TYPE t_va_names IS VARRAY(3) OF VARCHAR2(50);
  2  /
Type created

SQL> 
SQL> create table ay_test (
  2      name varchar2(30) not null,
  3      aliases  t_va_names,
  4      friends t_nt_names
  5  ) nested table friends store as ay_friends;
Table created

SQL> select table_name, nested from user_tables where table_name like 'AY%';

TABLE_NAME                     NESTED
------------------------------ ------
AY_FRIENDS                     YES
AY_TEST                        NO

Как видим, для хранения вложенных таблиц - значений столбца FRIENDS - одновременно с таблицей AY_TEST "за кулисами" была создана таблица AY_FRIENDS. А вот для хранения значений массива переменной длины отдельная таблица не требуется, - значения столбца ALIASES будут храниться непосредственно в таблице AY_TEST.

Вставим запись в таблицу AY_TEST и посмотрим, что получилось:

SQL> insert into ay_test (name, aliases, friends)
  2  values (
  3      'Andrei',
  4      t_va_names('Andrey', 'Andrew'),
  5      t_nt_names('Katya', 'Ivan')
  6  );
1 row inserted

SQL> select * from ay_test;

NAME                 ALIASES                        FRIENDS
-------------------- ------------------------------ ----------------------------
Andrei               T_VA_NAMES('Andrey', 'Andrew') T_NT_NAMES('Katya', 'Ivan')

SQL> select * from ay_friends;

ORA-22812: cannot reference nested table column's storage table

Последний запрос показывает, что выборка прямо из таблицы, в которой хранятся значения столбца типа nested table, невозможна. Для работы с вложенной таблицей и ее отдельными строками используется конструкция TABLE():

SQL> select * from table(select friends from ay_test where name = 'Andrei');
COLUMN_VALUE
--------------------------------------------------------------------------------
Katya
Ivan

SQL> update table(select friends from ay_test where name = 'Andrei')
  2  set column_value = 'Катя'
  3  where column_value = 'Katya';
1 row updated

SQL> select * from table(select friends from ay_test where name = 'Andrei');
COLUMN_VALUE
--------------------------------------------------------------------------------
Катя
Ivan

SQL> insert into table(select friends from ay_test where name = 'Andrei')
  2  values ('Маугли');
1 row inserted

SQL> select * from table(select friends from ay_test where name = 'Andrei');
COLUMN_VALUE
--------------------------------------------------------------------------------
Катя
Ivan
Маугли

SQL> delete from table(select friends from ay_test where name = 'Andrei')
  2  where column_value = 'Маугли';
1 row deleted

SQL> select * from table(select friends from ay_test where name = 'Andrei');
COLUMN_VALUE
--------------------------------------------------------------------------------
Катя
Ivan

Теперь проделаю аналогичные манипуляции со столбцом ALIASES, который хранит массивы переменной длины.

SQL> select * from table(select aliases from ay_test where name = 'Andrei');
COLUMN_VALUE
--------------------------------------------------------------------------------
Andrey
Andrew

SQL> update table(select aliases from ay_test where name = 'Andrei')
  2  set column_value = 'Андрей'
  3  where column_value = 'Andrew';

ORA-25015: cannot perform DML on this nested table view column

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

SQL> declare
  2      l_aliases t_va_names;
  3  begin
  4      -- retrieve column value
  5      select aliases into l_aliases from ay_test where name = 'Andrei';
  6  
  7      -- modify local varray
  8      for i in l_aliases.first .. l_aliases.last loop
  9          if l_aliases(i) = 'Andrew' then
 10              l_aliases(i) := 'Андрей';
 11          end if;
 12      end loop;
 13      l_aliases.extend;
 14      l_aliases(3) := 'Andorei'; -- in Japanese
 15  
 16      -- update column value
 17      update ay_test set aliases = l_aliases where name = 'Andrei';
 18  end;
 19  /
PL/SQL procedure successfully completed

SQL> select * from table(select aliases from ay_test where name = 'Andrei');
COLUMN_VALUE
--------------------------------------------------------------------------------
Andrey
Андрей
Andorei

Покажу, что конструкция TABLE() работает с вложенными таблицами и массивами переменной длины, инициализированными на месте:

SQL> select * from table(t_va_names('hello', 'world'));
COLUMN_VALUE
--------------------------------------------------------------------------------
hello
world

SQL> select * from table(t_nt_names('hello', 'world', '!'));
COLUMN_VALUE
--------------------------------------------------------------------------------
hello
world
!

С ассоциативным массивом TABLE() не работает. Более того, с ассоциативными массивами нельзя работать в SQL, можно только в PL/SQL. Ассоциативный массив нельзя инициализировать подобно тому, как инициализируются varray и nested table - у него нет инициализирующего конструктора.

Итак, были рассмотрены три разновидности коллекций PL/SQL и особенности работы с ними. За рамками статьи осталось наполнение коллекций с помощью SELECT ... BULK COLLECT INTO ... и использование коллекций с конструкцией FORALL для многократного выполнения одной команды DML с разными значениями связанных переменных. (Это был намек, с чем еще стоит поэкспериментировать самостоятельно.)

В заключение, удаляю следы моих экспериментов из БД:

SQL> drop table ay_test;
Table dropped

SQL> select * from user_tables where table_name like 'AY%';
No rows selected

SQL> drop type t_nt_names;
Type dropped

SQL> drop type t_va_names;
Type dropped

Как видим, вместе с таблицей AY_TEST была удалена и таблица AY_FRIENDS.

/привет/мир/etc

суббота, 18 июля 2015 г.

Коллекции в Oracle PL/SQL

1 комментарий:

суббота, 18 июля 2015 г.

Коллекции в Oracle PL/SQL

1 комментарий:

суббота, 18 июля 2015 г.