Ассоциативные массивы
Ассоциативные массивы могут быть определены только в рамках программы PL/SQL
Ни структура массива, ни данные не могут сохраняться в БД.
Каждый элемент массива отличается индексом:
Индексирование с помощью binary_integer:
1 | ...........
2 | ...........
3 | ...........
...............
10 | ...........
Индексирование с помощью varchar2:
a | ...........
b | ...........
c | ...........
...............
z | ...........
Они впервые появились в Oracle 7 и назывались по разному:
Oracle 7 - таблицы PL/SQL
Oracle 8 - индекс таблицы
Oracle 10 - ассоциативные массивы
Объявляются они так:
type name is table of type [not null]
index by type;
Тип индекса может быть:
- binary_integer
- pls_integer
- positive
- natural
- signtype
- varchar2
Тип данных элемента может быть:
- number и его подтипы
- varchar2 и его подтипы
- date
- blob
- clob
- boolean
А также:
- типы данных наследуемые из столбца таблицы
- курсорного выражения
- предопределённой переменной модуля
- определённый пользователем тип объекта или коллекции
Пример:
type clob_t is table of clob
index by pls_integer;
type empno_t is table of employees.empno%type not null
index by employees.ename%type;
Пример ассоциативного массива:
declare
type ln is table pers.f_name%TYPE
index by pls_integer;
begin
fam(20) := 'Tom';
fam(15) := 'Larry';
fam(-10) := 'Scott';
l_row := fam.FIRST;
while (l_row is not null) loop
dbms_output.put_line(fam(l_row));
l_row := fam.NEXT(l_row);
end loop;
end;
/
Ещё пример:
set serveroutput on
declare
type str_t is table of number
index by varchar2(10);
l_str str_t;
l_idx varchar2(50);
begin
l_str('JAN-MAR') := 90;
l_str('APR-JUN') := 91;
l_str('JUL-SEP') := 92;
l_str('OCT-DEC') := 93;
l_idx := l_str.FIRST;
while (l_idx is not null) loop
dbms_output.put_line('Value at index ' || l_idx || 'is' || l_str(l_idx));
l_idx := l_str.NEXT(l_idx);
end loop;
end;
/
Следующий PL/SQL блок объявляет ассоциативный массив для хранения ASCII значений чисел от 1 до 100:
set serveroutput on
declare
type ascii_t is table of varchar2(12)
index by pls_integer;
l_get_ascii ascii_t;
begin
for i in 1 .. 100 loop
l_get_ascii(i) := ascii(i);
end loop;
dbms_output.put_line(l_get_ascii(5));
end;
/
53
Ассоциативные массивы :
- не могут быть использованы в таблицах и применяются только как программные структуры
- не требуют инициализации (нет метода EXTEND)
- индексы: только строки и числа
(положительные, отрицательные и нуль)
- неявно конвертируются из:
%ROWTYPE
типов записи
объектных типов
- не могут применяться в транзакциях DML (как непостоянные коллекции)
- могут передаваться как аргумент другим локальным подпрограммам в рамках одного и того-же блока.
- ассоциативный массив, объявленный в спецификации модуля, ведет себя как массив, постоянный внутри сессии.
в структуры ассоциативных массивов
- являются ключом к использованию оператора FORALL
или фразы BULK COLLECT
которые разрешают групповую пересылку данных из БД в программный модуль.
Если в качестве индекса используется индексная строка, то порядок их обработки
зависит от параметров БД:
NLS_SORT
NLS_COMP
Пример:
DECLARE
type card is table of varchar2(5 char)
index by binary_integer;
-- конструктор использовать нельзя
cards card := card('A', 'B', 'C');
-- error 00222
-- объявим переменную
cards card;
BEGIN
-- нельзя перемещаться по ассоциативному массиву,
-- пока в нём отсутствуют элементы
dbms_output.put_line(cards(1));
-- error no data found
END;
/
Чтобы избежать подобной ошибки делайте так:
DECLARE
type card is table of varchar2(5 char)
index by binary_integer;
cards card;
BEGIN
if cards.COUNT < > 0 then
dbms_output.put_line(cards(1));
else
dbms_output.put_line('cards coll is empty.');
end if;
END;
/
Метод COUNT возвращает нулевое значение только:
- когда у ассоциативного массива отсутствуют элементы с присиоенными значениями.
- когда varray или nested table проинициализпрованы и их элементам не выделена память
Метод EXTEND не сможет выделить память для ассоциированного массива
DECLARE
type card is table of varchar2(5 char)
index by binary_integer;
cards card;
BEGIN
if cards.COUNT < > 0 then
dbms_output.put_line(cards(1));
else
cards.EXTEND;
-- error 00306
end if;
END;
/
Инициализация ассоциативных массивов
DECLARE
type mv is varray(12) of varchar2(9 char);
type calt is table of varchar2(9 char)
index by binary_integer;
month mv := mv('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
calendar calt;
-- тут month - это varray
-- calendar - это ассоциативный массив
BEGIN
if calendar.COUNT = 0 then
dbms_output.put_line('Assigment loop:');
dbms_output.put_line('---------------');
for i in month.FIRST .. month.LAST LOOP
calendar(i):= '';
-- инициализируем
dbms_output.put_line('Index[' || i || '] is [' calendar(i) || ']');
calendar(i) := month(i);
end loop;
dbms_output.put_line(chr(10));
dbms_output.put_line('Post-assigment loop:');
dbms_output.put_line('--------------------');
for i in calendar.FIRST .. calendar.LAST LOOP
dbms_output.put_line('Index[' || i || '] is [' calendar(i) || ']');
end loop;
end if;
END;
/
Числовой индекс:
type mv is varray(12) of string(9 char);
type calt is table of varchar2(9 char)
index by binary_integer;
month mv := mv('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
calendar calt;
calendar(1) := '';
calendar(1) := month(1);
calendar(1) := 'January';
-- calendar(1) - > January
Строковый индекс:
type mv is varray(12) of string(9 char);
type calt is table of varchar2(9 char)
index by varchar2(9 char);
month mv := mv('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
calendar calt;
calendar(January) := '1';
.....
calendar(December) := '12';
calendar(April) - > 4
.....
calendar(September) - > 9
Для строкового индекса процесс изменяется:
-- Присвоим значения:
if calendar.COUNT = 0 then
for i in month.FIRST .. month.LAST LOOP
calendar(month(i)):= i;
end loop;
-- Вывод значений:
for i in calendar.FIRST .. calendar.LAST LOOP
dbms_output.put_line('Index[' || i || '] is [' calendar(i) || ']');
end loop;
end if;
Инициализация сработает
А второй цикл for при выводе значений выдаст ошибку
calendar(April) - > 4
.....
calendar(September) - > 9
При попытке вызвать функции FIRST и LAST мы получим ORA-06502
Ошибка преобразования числа
Тут происходит попытка передать нечисловое значениеиндекса в переменную счетчика.
Значение индекса в виде строки переменной длины не может быть приведено к целому числу,
т.к. оно не является целым числом.
Для нечисловых значений индексов необходимо знать с чего следует начинать и как наращивать.
Методы FIRST и NEXT дают нам такое средство
Рассмотрим логику обхода уникальных строковых индексов.
Определим две переменные, которые будут использованы для управления навигацией
по ассоциативному массиву с уникальными строковыми индексами
current varchar2(9 char);
element integer;
Цикл по всем элементам фссоциативного массива:
for i in 1 .. calendar.COUNT loop
-- проверьте, является ли элемент первым в списке
if i = 1 then
-- присвойте первый символьный индекс переменной
current := calendar.FIRST;
-- используйте полученный индекс для нахождения следующего индекса
element := calendar(current);
else
-- проверьте существует ли следующее значение индекса
if calendar.NEXT(current) is not null then
-- присвойте символьный индекс переменной
current := calendar.NEXT(current);
-- используйте полученный индекс для нахождения следующего индекса
element := calendar(current);
else
exit
-- было прочитано последнее значение индекса
end if
else if
dbms_output.put_line('Index[' || current || '] is [' element || ']');
end loop;
Метод FORALL
Есть таблица t1 с полем id типа number.
Обычная вставка данных:
begin
for i in 1 .. 10000 loop
insert into t1 values(i);
end loop;
commit;
end;
/
Вставка данных из коллекции:
begin
forall i in 1 .. num_list.COUNT
insert into t1 values(num_list(i));
commit;
end;
/
Метод FORALL позволяет посылать операторы DML пакетами
Метод FORALL предлагает средство для переноса содержимого ассоциативного массива
или вложенной таблицы в объект БД.
declare
type td is table of t1.id%type
index by binary_integer;
num_list td;
begin
for i in 1 .. 10000 loop
insert into t1 values(i);
end loop;
forall i in 1 .. num_list.COUNT
insert into t1 values(num_list(i));
commit;
-- фиксируем результат каждого пакета вставок
end;
/
Метод BULK COLLECT
Метод BULK COLLECT позволяет выбирать наборы записей ,
которые можно хранить в массивах переменной длины и во вложенных таблицах.
Метод BULK COLLECT позволяет, как единое множество,
присвоить коллекцию значений %ROWTYPE или %TYPE ассоциативному массиву
или вложенной таблице.
Вложенную таблицу необходимо построить как коллекцию пустых элементов.
Память во вложенной таблице будет неявно выделять bulk collect
Для ассоциированного массива не требуется конструктора,
достаточно группового присваивания.
Выборка данных в ассоциативный массив:
declare
type td is table of t1.id%type
index by binary_integer;
num_list td;
begin
select id bulk collect into num_list from t1 order by 1;
for i in num_list.FIRST .. num_list.LAST loop
if i < = 2 or i > = 9999 then
dbms_ounput.put_line(num_list(i));
end if;
end loop;
end;
/
Ассоциативные массивы могут быть определены только в рамках программы PL/SQL
Ни структура массива, ни данные не могут сохраняться в БД.
Каждый элемент массива отличается индексом:
Индексирование с помощью binary_integer:
1 | ...........
2 | ...........
3 | ...........
...............
10 | ...........
Индексирование с помощью varchar2:
a | ...........
b | ...........
c | ...........
...............
z | ...........
Они впервые появились в Oracle 7 и назывались по разному:
Oracle 7 - таблицы PL/SQL
Oracle 8 - индекс таблицы
Oracle 10 - ассоциативные массивы
Объявляются они так:
type name is table of type [not null]
index by type;
Тип индекса может быть:
- binary_integer
- pls_integer
- positive
- natural
- signtype
- varchar2
Тип данных элемента может быть:
- number и его подтипы
- varchar2 и его подтипы
- date
- blob
- clob
- boolean
А также:
- типы данных наследуемые из столбца таблицы
- курсорного выражения
- предопределённой переменной модуля
- определённый пользователем тип объекта или коллекции
Пример:
type clob_t is table of clob
index by pls_integer;
type empno_t is table of employees.empno%type not null
index by employees.ename%type;
Пример ассоциативного массива:
declare
type ln is table pers.f_name%TYPE
index by pls_integer;
begin
fam(20) := 'Tom';
fam(15) := 'Larry';
fam(-10) := 'Scott';
l_row := fam.FIRST;
while (l_row is not null) loop
dbms_output.put_line(fam(l_row));
l_row := fam.NEXT(l_row);
end loop;
end;
/
Ещё пример:
set serveroutput on
declare
type str_t is table of number
index by varchar2(10);
l_str str_t;
l_idx varchar2(50);
begin
l_str('JAN-MAR') := 90;
l_str('APR-JUN') := 91;
l_str('JUL-SEP') := 92;
l_str('OCT-DEC') := 93;
l_idx := l_str.FIRST;
while (l_idx is not null) loop
dbms_output.put_line('Value at index ' || l_idx || 'is' || l_str(l_idx));
l_idx := l_str.NEXT(l_idx);
end loop;
end;
/
Следующий PL/SQL блок объявляет ассоциативный массив для хранения ASCII значений чисел от 1 до 100:
set serveroutput on
declare
type ascii_t is table of varchar2(12)
index by pls_integer;
l_get_ascii ascii_t;
begin
for i in 1 .. 100 loop
l_get_ascii(i) := ascii(i);
end loop;
dbms_output.put_line(l_get_ascii(5));
end;
/
53
Ассоциативные массивы :
- не могут быть использованы в таблицах и применяются только как программные структуры
- не требуют инициализации (нет метода EXTEND)
- индексы: только строки и числа
(положительные, отрицательные и нуль)
- неявно конвертируются из:
%ROWTYPE
типов записи
объектных типов
- не могут применяться в транзакциях DML (как непостоянные коллекции)
- могут передаваться как аргумент другим локальным подпрограммам в рамках одного и того-же блока.
- ассоциативный массив, объявленный в спецификации модуля, ведет себя как массив, постоянный внутри сессии.
в структуры ассоциативных массивов
- являются ключом к использованию оператора FORALL
или фразы BULK COLLECT
которые разрешают групповую пересылку данных из БД в программный модуль.
Если в качестве индекса используется индексная строка, то порядок их обработки
зависит от параметров БД:
NLS_SORT
NLS_COMP
Пример:
DECLARE
type card is table of varchar2(5 char)
index by binary_integer;
-- конструктор использовать нельзя
cards card := card('A', 'B', 'C');
-- error 00222
-- объявим переменную
cards card;
BEGIN
-- нельзя перемещаться по ассоциативному массиву,
-- пока в нём отсутствуют элементы
dbms_output.put_line(cards(1));
-- error no data found
END;
/
Чтобы избежать подобной ошибки делайте так:
DECLARE
type card is table of varchar2(5 char)
index by binary_integer;
cards card;
BEGIN
if cards.COUNT < > 0 then
dbms_output.put_line(cards(1));
else
dbms_output.put_line('cards coll is empty.');
end if;
END;
/
Метод COUNT возвращает нулевое значение только:
- когда у ассоциативного массива отсутствуют элементы с присиоенными значениями.
- когда varray или nested table проинициализпрованы и их элементам не выделена память
Метод EXTEND не сможет выделить память для ассоциированного массива
DECLARE
type card is table of varchar2(5 char)
index by binary_integer;
cards card;
BEGIN
if cards.COUNT < > 0 then
dbms_output.put_line(cards(1));
else
cards.EXTEND;
-- error 00306
end if;
END;
/
Инициализация ассоциативных массивов
DECLARE
type mv is varray(12) of varchar2(9 char);
type calt is table of varchar2(9 char)
index by binary_integer;
month mv := mv('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
calendar calt;
-- тут month - это varray
-- calendar - это ассоциативный массив
BEGIN
if calendar.COUNT = 0 then
dbms_output.put_line('Assigment loop:');
dbms_output.put_line('---------------');
for i in month.FIRST .. month.LAST LOOP
calendar(i):= '';
-- инициализируем
dbms_output.put_line('Index[' || i || '] is [' calendar(i) || ']');
calendar(i) := month(i);
end loop;
dbms_output.put_line(chr(10));
dbms_output.put_line('Post-assigment loop:');
dbms_output.put_line('--------------------');
for i in calendar.FIRST .. calendar.LAST LOOP
dbms_output.put_line('Index[' || i || '] is [' calendar(i) || ']');
end loop;
end if;
END;
/
Числовой индекс:
type mv is varray(12) of string(9 char);
type calt is table of varchar2(9 char)
index by binary_integer;
month mv := mv('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
calendar calt;
calendar(1) := '';
calendar(1) := month(1);
calendar(1) := 'January';
-- calendar(1) - > January
Строковый индекс:
type mv is varray(12) of string(9 char);
type calt is table of varchar2(9 char)
index by varchar2(9 char);
month mv := mv('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
calendar calt;
calendar(January) := '1';
.....
calendar(December) := '12';
calendar(April) - > 4
.....
calendar(September) - > 9
Для строкового индекса процесс изменяется:
-- Присвоим значения:
if calendar.COUNT = 0 then
for i in month.FIRST .. month.LAST LOOP
calendar(month(i)):= i;
end loop;
-- Вывод значений:
for i in calendar.FIRST .. calendar.LAST LOOP
dbms_output.put_line('Index[' || i || '] is [' calendar(i) || ']');
end loop;
end if;
Инициализация сработает
А второй цикл for при выводе значений выдаст ошибку
calendar(April) - > 4
.....
calendar(September) - > 9
При попытке вызвать функции FIRST и LAST мы получим ORA-06502
Ошибка преобразования числа
Тут происходит попытка передать нечисловое значениеиндекса в переменную счетчика.
Значение индекса в виде строки переменной длины не может быть приведено к целому числу,
т.к. оно не является целым числом.
Для нечисловых значений индексов необходимо знать с чего следует начинать и как наращивать.
Методы FIRST и NEXT дают нам такое средство
Рассмотрим логику обхода уникальных строковых индексов.
Определим две переменные, которые будут использованы для управления навигацией
по ассоциативному массиву с уникальными строковыми индексами
current varchar2(9 char);
element integer;
Цикл по всем элементам фссоциативного массива:
for i in 1 .. calendar.COUNT loop
-- проверьте, является ли элемент первым в списке
if i = 1 then
-- присвойте первый символьный индекс переменной
current := calendar.FIRST;
-- используйте полученный индекс для нахождения следующего индекса
element := calendar(current);
else
-- проверьте существует ли следующее значение индекса
if calendar.NEXT(current) is not null then
-- присвойте символьный индекс переменной
current := calendar.NEXT(current);
-- используйте полученный индекс для нахождения следующего индекса
element := calendar(current);
else
exit
-- было прочитано последнее значение индекса
end if
else if
dbms_output.put_line('Index[' || current || '] is [' element || ']');
end loop;
Метод FORALL
Есть таблица t1 с полем id типа number.
Обычная вставка данных:
begin
for i in 1 .. 10000 loop
insert into t1 values(i);
end loop;
commit;
end;
/
Вставка данных из коллекции:
begin
forall i in 1 .. num_list.COUNT
insert into t1 values(num_list(i));
commit;
end;
/
Метод FORALL позволяет посылать операторы DML пакетами
Метод FORALL предлагает средство для переноса содержимого ассоциативного массива
или вложенной таблицы в объект БД.
declare
type td is table of t1.id%type
index by binary_integer;
num_list td;
begin
for i in 1 .. 10000 loop
insert into t1 values(i);
end loop;
forall i in 1 .. num_list.COUNT
insert into t1 values(num_list(i));
commit;
-- фиксируем результат каждого пакета вставок
end;
/
Метод BULK COLLECT
Метод BULK COLLECT позволяет выбирать наборы записей ,
которые можно хранить в массивах переменной длины и во вложенных таблицах.
Метод BULK COLLECT позволяет, как единое множество,
присвоить коллекцию значений %ROWTYPE или %TYPE ассоциативному массиву
или вложенной таблице.
Вложенную таблицу необходимо построить как коллекцию пустых элементов.
Память во вложенной таблице будет неявно выделять bulk collect
Для ассоциированного массива не требуется конструктора,
достаточно группового присваивания.
Выборка данных в ассоциативный массив:
declare
type td is table of t1.id%type
index by binary_integer;
num_list td;
begin
select id bulk collect into num_list from t1 order by 1;
for i in num_list.FIRST .. num_list.LAST loop
if i < = 2 or i > = 9999 then
dbms_ounput.put_line(num_list(i));
end if;
end loop;
end;
/
