Иван Задворьев - Язык PL/SQL

вложенные таблицы (nested tables) – неограниченные коллекции, типы данных на основе которых могут создаваться как объекты баз данных и объявляться в программах PL/SQL;

массивы переменной длины (variable-size array, VARRAY) – ограниченные коллекции, типы данных на основе которых могут создаваться как объекты баз данных с помощью DDL-команд языка SQL и объявляться в программах PL/SQL.

Коллекция называется плотной, если все ее элементы, от первого до последнего, определены и им присвоены некоторые значения, включая NULL. Если же у коллекции в диапазоне индексов есть пропуски (какие-то элементы коллекции отсутствуют), то коллекция называется разреженной. Массивы VARRAY всегда являются плотными. Вложенные таблицы первоначально всегда плотные, но по мере удаления некоторых элементов становятся разреженными. Таблицы PL/SQL могут быть разреженными.

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

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

Работа с таблицей PL/SQL с помощью встроенных методов

Для иллюстрации техники работы с таблицей PL/SQL покажем, как ее объявить, затем создадим два элемента таблицы и переберем их в цикле:

SQL> DECLARE

2 TYPE t_job IS RECORD (position VARCHAR2(100),salary INTEGER);

3 TYPE t_job_table IS TABLE OF t_job INDEX BY PLS_INTEGER;

4 TYPE t_person IS RECORD (surname VARCHAR2(30),jobs t_job_table);

5 l_person t_person;

6 l_job t_job;

7 l_job_table t_job_table;

8 l_row_index PLS_INTEGER;

9 BEGIN

10 l_person.surname := 'Ильинский К.В.';

11

12 l_job := NULL;

13 l_job.position := 'инженер';

14 l_job.salary := 50000;

15 l_job_table(9) := l_job;

16

17 l_job := NULL;

18 l_job.position := 'старший инженер';

19 l_job.salary := 60000;

20 l_job_table(267) := l_job;

21

22 l_person.jobs := l_job_table;

23

24 DBMS_OUTPUT.PUT_LINE('Сотрудник: '||l_person.surname);

25 l_row_index := l_person.jobs.first();

26 WHILE l_row_index IS NOT NULL LOOP

27 DBMS_OUTPUT.PUT_LINE(l_person.jobs(l_row_index).position

28 ||' ('||l_person.jobs(l_row_index).salary||' руб.)');

29 l_row_index := l_person.jobs.next(l_row_index);

30 END LOOP;

31

32 END;

33 /

Сотрудник: Ильинский К.В.

инженер (50000 руб.)

старший инженер (60000 руб.)

PL/SQL procedure successfully completed.

Таблицы PL/SQL являются разреженными. Чтобы подчеркнуть это, в примере выше специально использованы случайно выбранные индексы 9 и 267, а не 1 и 2. Для перебора таблицы PL/SQL использованы ее встроенные методы FIRST и NEXT.

Коллекции PL/SQL имеют восемь встроенных методов.

Таблица 1.Встроенные методы коллекций PL/SQL.

Метод коллекции

Описание метода

COUNT (функция)

возвращает текущее число элементов в коллекции

DELETE (процедура)

удаляет из коллекции один или несколько элементов

EXISTS (функция)

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

EXTEND (процедура)

увеличивает количество элементов во вложенной таблице или массиве переменной длины

FIRST, LAST (функции)

возвращают индексы первого (FIRST) и последнего (LAST) элемента в коллекции

LIMIT (функция)

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

PRIOR, NEXT (функции)

возвращают индексы элементов, предшествующих заданному (PRIOR) и следующему за ним (NEXT).

TRIM (процедура)

удаляет элементы, начиная с конца коллекции

Рекомендуется перебор элементов коллекций осуществлять с помощью методов FIRST и NEXT, а не с помощью циклов со счетчиком FOR, исходя из ожидаемой плотности коллекции. Цикл FOR перебирает весь заданный диапазон индексов подряд, что может привести к ошибке – обращению к отсутствующему элементу. Метод NEXT перемещается по индексам только «живых» элементов и ошибок из-за пропусков в нумерации не будет.

Индексы-строки таблиц PL/SQL

В версии Oracle 9i появилась возможность использовать для индексирования таблиц PL/SQL символьные строки. Это очень удобно, например, для работы со справочниками, в которых и коды и термины являются строками.

Рассмотрим пример.

SQL> DECLARE

2 TYPE t_tab IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(2);

3 l_tab t_tab;

4 l_code varchar2(3) := 'MD';

5 BEGIN

6 – заполняем таблицу PL/SQL

7 l_tab('UA') := 'Украина';

8 l_tab('MD') := 'Молдавия';

9 – работаем с таблицей PL/SQL

10 l_code := 'MD'

11 DBMS_OUTPUT.PUT_LINE('1) Термин для '||l_code||' – '||l_tab(l_code));

12 l_code := 'UA'

13 IF l_tab.EXISTS('UA') THEN

14 DBMS_OUTPUT.PUT_LINE('2) Код '||l_code||' есть в справочнике');

15 END IF;

16 END;

17 /

1) Термин для MD – Молдавия

2) Код UA есть в справочнике

PL/SQL procedure successfully completed.

Массивы переменной длины и вложенные таблицы

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