Язык PL/SQL

Коллекция называется ограниченной, если заранее определены границы возможных значений индексов ее элементов. В противном случае коллекция называется неограниченной.

В PL/SQL есть три вида коллекций:

ассоциативные массивы (associative array) – неограниченные коллекции, объявляемые только в программах PL/SQL (поэтому в литературе иногда эти коллекции называются таблицами 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 следует использовать переменные таких же типов данных.

Рассмотрим объектные расширения 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();

41 WHILE l_row_index IS NOT NULL LOOP

42 DBMS_OUTPUT.PUT_LINE(' ' ||l_elective_courses(l_row_index));

43 l_row_index := l_elective_courses.NEXT(l_row_index);

44 END LOOP;

45

46 END;

47 /

Студент: Ильин

Курсовые на младших курсах:

Курсовая на 1 курсе: оценка 4

Курсовая на 2 курсе: оценка 4

Курсовые на старших курсах:

Курсовая на 3 курсе:

Курсовая на 4 курсе: оценка 5

Курсовая на 5 курсе: оценка 5

Факультативы (всего 2):

Оптимизация баз данных

Теория надежности

PL/SQL procedure successfully completed.

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

Обработка исключений

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

Понятие исключения

Исключением (exception) в PL/SQL называется ситуация, которая не должна возникать при нормальном выполнении программы PL/SQL.

Существует два типа исключений PL/SQL:

системные исключения (run-time system exceptions), которые автоматически инициируются виртуальной машиной PL/SQL при возникновении программных ошибок этапа выполнения;

пользовательские исключения (user-defined exceptions), объявляемые программистом в коде PL/SQL и используемые при реализации бизнес-логики.

Программной ошибкой этапа выполнения (run-time program error) называется ситуация, когда наблюдается неожиданное поведение программы, затрудняющее или делающее невозможным достижение целей пользователя. Примерами программных ошибок могут служить попытки деления на ноль, ошибки преобразования символов в числа, ошибки выполнения предложений SQL.

Пользовательские исключения инициируются в программах PL/SQL в том случае, когда на прикладном уровне возникли отклонения от стандартного процесса обработки данных. Например, при обработке поступивших данных встретился чек с отрицательной суммой покупки или не в рублях. С точки зрения правил бизнес-логики это такая же ошибка, как и деление на ноль с точки зрения правил арифметики. Для попытки деления на ноль в ходе выполнения программы системное исключение будет автоматически инициировано виртуальной машиной PL/SQL, потому что она «знает» правила арифметики. Для поступающих ошибочных платежей инициировать пользовательское исключение должен в своем коде программист PL/SQL, потому что он знает правила бизнес-логики вида «Платежи принимаются только в рублях, на положительные суммы с точностью до копеек», «Платежи принимаются только для открытой смены контрольно-кассовой машины (ККМ)» и так далее.

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

Правила работы с исключениями:

пользовательские исключения объявляются в разделах объявлений блоков PL/SQL и имеют имена;

системные исключения имен не имеют, они характеризуются номером ошибки;

имеется возможность объявить пользовательское исключение и с помощью директивы компилятору связать его с некоторым номером ошибки.

Несколько исключений для часто возникающих в программах PL/SQL ошибок объявлено во встроенном пакете STANDARD с привязкой к соответствующим номерам ошибок. Эти исключения называются предопределенными исключениями PL/SQL (predefined exception) и их можно использовать в любых программах PL/SQL без дополнительных объявлений.

Таблица 2. Предопределенные исключения PL/SQL.

Исключение

Описание исключения (номер ошибки)

INVALID_CURSOR

ссылка на несуществующий курсор (ORA-01001)

NO_DATA_FOUND

не найдены данные командой

SELECT INTO (ORA-01403)

DUP_VAL_ON_INDEX

попытка вставить в столбец с ограничением

на уникальность значение-дубликат (ORA-00001)

TOO_MANY_ROWS

команда SELECT INTO возвращает

более одной строки (ORA-01422)

VALUE_ERROR

арифметическая ошибка, ошибка преобразования

или усечения чисел и строк (ORA-06502)

INVALID_NUMBER

ошибка преобразования строки в число (ORA-01722)

PROGRAM_ERROR

внутренняя ошибка PL/SQL (ORA-06501)

ZERO_DIVIDE

попытка деления на ноль (ORA-01476)

Схема обработки исключений в Java

В языке программирования Java при описании работы с исключениями используется бейсбольная терминология. При возникновении исключения бросается (throws) объект-исключение. Этот объект как бейсбольный мяч пролетает через исходный код, появившись сначала в том методе, где произошло исключение. В одном или нескольких местах кода объект-исключение пытаются (try) поймать (catch) и обработать. Причем исключение можно обработать в одном месте кода полностью, а можно обработать исключение частично, выбросить его из обработчика снова, поймать в другом месте и обрабатывать дальше.

Приведем пример кода на Java с попыткой поймать два исключения – связанные с ошибками арифметических вычислений и нарушением правил работы с массивами (выход индекса массива за границы диапазона):

try{

…

}

catch(ArithmeticException ae){

System.out.println("From Arithm.Exc. catch: "+ae);

}

catch(ArraylndexOutOfBoundsException arre){

System.out.println("From Array.Exc.catch: "+arre);

}

}

Схема обработки исключений в PL/SQL

Работа с исключениями в PL/SQL очень похожа на то, как это делается в Java.

Для обработки исключений предназначен последний раздел блока PL/SQL – раздел обработки исключений. Этот последний раздел блока после ключевого слова EXCEPTION похож на то, что в Java указывается после ключевого слова catch. Перед обсуждением правил обработки исключений приведем небольшой пример с комментариями в коде.

DECLARE

a INTEGER;

BEGIN

a := 1;

a := 2/0; – бросается предопределенное исключение ZERO_DIVIDE

a := 3; – над этой командой пролетает, команда не выполняется

a := 4; – над этой командой тоже пролетает, команда не выполняется

– управление передается в раздел обработки исключений,

– начинаем «примерку» обработчиков

EXCEPTION

– не подходит по имени ловимого исключения к прилетевшему ZERO_DIVIDE

WHEN PROGRAM_ERROR THEN

DBMS_OUTPUT.PUT_LINE('Программная ошибка');

– оба имени ловимых исключений не подходят к прилетевшему ZERO_DIVIDE

WHEN INVALID_NUMBER OR VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE('Ошибка работы с числами и строками');

– подходит по имени к ZERO_DIVIDE (поймали), заходим внутрь обработчика

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('Ошибка деления на ноль');

– OTHERS ловит все, что не поймали другие до него,

– но сюда в этом случае «примерка» не дошла, раньше поймали

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('При выполнении произошла ошибка '||SQLERRM);

END;

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

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

Действия в разделе обработки исключений

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

определение, какой обработчик в разделе ловит прилетевшее исключение («примерка» обработчиков);

обработка исключения подходящим обработчиком.

«Примерка» обработчиков осуществляется по именам исключений – перед каждым обработчиком указывается список имен исключений, которые он ловит.

Если исключение не имеет имени или его имя не соответствует ни одному из имен исключений, указанных в разделе обработки исключений, то оно обрабатывается OTHERS-обработчиком, если он имеется. OTHERS-обработчик в разделе обработки исключений указывается последним и на него возлагается задача поймать все то, что не поймали другие обработчики перед ним – и системные исключения и пользовательские исключения с любыми именами.

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

если никакой обработчик исключению не подошел, то исключение со статусом «не обработано» бросается в дальнейший полет уже в родительском блоке (блоке, предыдущим по вложенности) с того места кода, где заканчивается вложенный блок;

если в результате «примерки» нашелся подходящий исключению обработчик, то управление передается ему.

Работа обработчика свою очередь может завершиться тремя исходами:

команды обработчика успешно выполнились, исключение получает статус «обработано» и управление передается родительскому блоку в то место кода, где заканчивается вложенный блок;

в процессе работы обработчика принято решение, что обрабатывать исключение надо не в этом обработчике, тогда исключение здесь же в обработчике инициируется повторно вызовом команды RAISE без параметров;

в ходе выполнения команд обработчика инициировано новое исключение (такое бывает, например, если в обработчике ошибки регистрируются в специальной таблице, а для нее кончилось место), прилетевшее исходное исключение тогда получает статус «обработано».

Два последних исхода работы обработчика предполагают, что из блока даже с подходящим обработчиком исключение бросится дальше – либо то же самое (после вызова команды RAISE), либо уже другое. Могло прилететь пользовательское исключение, обработаться со своей ошибкой, поэтому из обработчика бросится и в родительском блоке полетит дальше уже системное исключение, как в примере с ошибкой добавления строки в специальную таблицу журнала ошибок.

Примеры обработки исключений

Рассмотрим примеры полетов исключений в программе из трех вложенных блоков:

BEGIN – начало блока1

команда1_блока1;

команда2_блока1;

команда3_блока1;

BEGIN – начало блока2

команда1_блока2;

команда2_блока2 l_int := 1/TO_NUMBER(l_var) (l_var='1' или '0' или 'a')

команда3_блока2;

EXCEPTION

WHEN ZERO_DIVIDE THEN

команда1_zero_блока2;

команда2_zero_блока2;

END; – конец блока2

команда4_блока1;

команда5_блока1;

EXCEPTION

WHEN ZERO_DIVIDE THEN

команда1_zero_блока1;

команда2_zero_блока1;

WHEN OTHERS THEN

команда1_others_блока1;

команда2_others_блока1;

END; – конец блока 1

Пусть команда2_блока2 имеет вид l_int:=1/TO_NUMBER(l_var); где l_int – целочисленная переменная, l_var – символьная.

l_var='1' (без исключений)

l_var='0' (zero divide)

l_var='a' (conversion error)

команда1_блока1;

команда2_блока1;

команда3_блока1;

команда1_блока2;

команда2_блока2;

команда3_блока2;

команда4_блока1;

команда5_блока1;

команда1_блока1;

команда2_блока1;

команда3_блока1;

команда1_блока2;

команда2_блока2(error);

– в блоке 2

– ловится ZERO_DIVIDE:

команда1_zero_блока2;

команда2_zero_блока2;

–продолжение блока 1:

команда4_блока1;

команда5_блока1;

команда1_блока1;

команда2_блока1;

команда3_блока1;

команда1_блока2;

команда2_блока2(error);

–в блоке 2 ошибка

–преобразования

–не ловится, т.к.

–там только ZERO_DIVIDE

– в блоке 1

– ZERO_DIVIDE

– второй раз не ловит,

– а ловит OTHERS

– (он же все ловит):

команда1_others_блока1;

команда2_others_блока1;

Рассмотрим три случая в зависимости от значения, которое принимает переменная l_var ('1', или '0', или 'a').

Когда l_var=1 (первый столбец таблицы) исключения не инициируются выполняются все команды из разделов выполнения в той последовательности, как они записаны в коде.

В случае ошибки деления на ноль (второй столбец таблицы, l_var='0') в команде2_блока2 выполнение блока 2 прекращается, все остальные команды в блоке 2 после нее не выполняются, управление передается в раздел EXCEPTION блока 2, где пытаются поймать исключение деления на ноль (ZERO_DIVIDE). Подходящий обработчик в разделе обработки исключений блока 2 есть, поэтому исключение ловится в блоке 2, в котором успешно выполняются команды обработчика. После успешной обработки продолжается выполнение команд блока 1, родительского для блока 2, в котором произошла обработка исключения.