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 и имеют имена;