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

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 следует использовать переменные таких же типов данных.

Рассмотрим объектные расширения Oracle и работу с ними в PL/SQL на следующем примере.

Пусть есть таблица students со сведениями о студентах, у которой первые три столбца имеют скалярные типы данных, а столбцы course_works (курсовые работы) и elective_courses (факультативы) объявлены как массив переменной длины и вложенная таблица.

Считаем, что студенты учатся максимум 6 лет (могут меньше) и на каждом курсе может быть только одна курсовая работа (на каких-то курсах курсовых работ может не быть). Из сказанного следует, что

больше 6 курсовых работ точно быть не может;

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

Массивы переменной длины как раз и предназначены для представления упорядоченных множеств (списков) с заданным ограничением на максимальное число элементов. На физическом уровне в базах данных Oracle такие массивы хранятся в строках таблицы, рядом со значениями скалярных типов.

Что же касается факультативов, то заранее известной верхней оценки их числа для одного студента нет и обеспечить упорядочение их названий по какому-то правилу не требуется. В этих условиях для хранения данных о факультативах целесообразно использовать вложенные таблицы – в ячейку студента Ильина вкладывается одностолбцовая таблица со списком прослушанных им факультативов, в ячейку студента Варина вкладывается другая таблица факультативов и так далее.

SQL> CREATE TYPE t_course_works AS VARRAY(6) OF INTEGER;

2 /

Type created.

SQL> CREATE TYPE t_elective_courses AS TABLE OF VARCHAR2(100);

2 /

Type created.

SQL> CREATE TABLE students(id INTEGER,

2 surname VARCHAR(100),

3 name VARCHAR(100),

4 course_works t_course_works,

5 elective_courses t_elective_courses)

6 NESTED TABLE elective_courses STORE AS elective_courses_tab;

Table created.

SQL> INSERT INTO students VALUES(18,'Ильин','Виктор',

2 t_course_works(4,4,NULL,5,5),

3 t_elective_courses('Оптимизация баз данных',

4 'Теория надежности'));

1 row created.

SQL> SET FEEDBACK ON

SQL> SELECT * FROM students;

ID SURNAME NAME COURSE_WORKS

– – – –

18 Ильин Виктор T_COURSE_WORKS(4, 4, NULL, 5, 5)

ELECTIVE_COURSES

–

T_ELECTIVE_COURSES('Оптимизация баз данных', 'Теория надежности')

1 row selected.

На физическом уровне в базе данных для столбца elective_courses будет неявно создана вспомогательная таблица (мы дали ей имя elective_courses_tab), в которой будут храниться все строки всех вложенных таблиц столбца elective_courses. Эти строки будут ссылаться на строки основной таблицы students, то есть фактически с помощью основной и вспомогательной таблиц и механизма ключей будет классическим способом моделироваться отношение «один ко многим» между студентами и факультативами. Рассмотрим теперь, как с массивами VARRAY и вложенными таблицами работают в коде PL/SQL. Напишем программу, которая выводит сведения о студенте, его оценки за курсовые работы на младших и старших курсах отдельно, а также о список прослушанных студентом факультативов.

SQL> DECLARE

2 l_surname students.surname%TYPE;

3 l_course_works t_course_works;

4 l_elective_courses t_elective_courses;

5 l_row_index PLS_INTEGER;

6 l_student_id students.id%TYPE := 18;

7 BEGIN

8

9 SELECT surname,course_works,elective_courses

10 INTO l_surname,l_course_works,l_elective_courses

11 FROM students WHERE id=l_student_id;

12

13 DBMS_OUTPUT.PUT_LINE('Студент: '||l_surname);

14

15 IF l_course_works.EXISTS(1) or l_course_works.EXISTS(2) THEN

16 DBMS_OUTPUT.PUT_LINE('Курсовые на младших курсах:');

17 ELSE

18 DBMS_OUTPUT.PUT_LINE('Курсовые на младших курсах отсутствуют')

19 END IF;

20

21 FOR i in 1..2 LOOP

22 IF l_course_works.EXISTS(i) THEN

23 DBMS_OUTPUT.PUT_LINE(' Курсовая на '||i||' курсе: ' ||

24 ' оценка '||l_course_works(i));

25 END IF;

26 END LOOP;

27

28 DBMS_OUTPUT.PUT_LINE('Курсовые на старших курсах:');

29

30 l_row_index := l_course_works.NEXT(2);

31 WHILE l_row_index IS NOT NULL LOOP

32 DBMS_OUTPUT.PUT_LINE(' Курсовая на '||l_row_index

33 ||' курсе: оценка ' ||l_course_works(l_row_index));

34 l_row_index := l_course_works.NEXT(l_row_index);

35 END LOOP;

36

37 DBMS_OUTPUT.PUT_LINE('Факультативы (всего '

38 ||l_elective_courses.COUNT()||'):');

39

40 l_row_index := l_elective_courses.FIRST();