Иван Сергеевич Задворьев
Язык PL/SQL
Условные команды и команды перехода
В PL/SQL к условным командам относятся команды IF и CASE, переходы реализуются командами GOTO и NULL.
Условная команда IF
Условная команда IF позволяет проверить заданное логическое условие и, в зависимости от результатов проверки (TRUE, FALSE или UNKNOWN), выполнить различные ветви кода. В PL/SQL имеется три формы команды IF:
IF THEN END IF
IF THEN ELSE END IF
IF THEN ELSIF ELSE END IF
Границы действия команды IF определяются закрывающими ключевыми словами END IF. Альтернативная последовательность команд следует после ключевого слова ELSE, для расширения структуры ветвления дополнительно можно задать дополнительное условие после ключевого слова ELSIF.
Команда IF имеет следующий синтаксис:
IF условие 1 THEN
команды 1; – ветвь 1
ELSIF условие 2 THEN
команды 2; – ветвь 2
ELSE
команды n; – альтернативная последовательность команд (ELSE-ветвь)
END IF;
Команды первой ветви кода выполняются только тогда, когда проверка условия определяет его истинность (TRUE). Если же проверка условия определяет FALSE или UNKNOWN (например, при сравнении с NULL), то выполняются команды ELSE-ветви.
Приведем пример программы, которая выводит сообщение о классе излучения в зависимости от значения вводимого параметра длины волны (длина волны предполагается заданной в микронах).
SQL> DECLARE
2 lamda NUMBER; – Длина волны
3 text1 VARCHAR2(30) := 'Инфракрасное излучение';
4 text2 VARCHAR2(30) := 'Видимый свет';
5 text3 VARCHAR2(30) := 'Ультрафиолет';
6 – исполняемый раздел
7 BEGIN
8 lamda := &Input_Data;
9 DBMS_OUTPUT.PUT_LINE('');
10 IF (lamda > 0.65)
11 THEN DBMS_OUTPUT.PUT_LINE(text1);
12 ELSIF (lamda < 0.41)
13 THEN DBMS_OUTPUT.PUT_LINE(text3);
14 ELSE
15 DBMS_OUTPUT.PUT_LINE(text2);
16 END IF;
17 END;
/
Enter value for input_data: 0.33
old 8: lamda := &Input_Data;
new 8: lamda := 0.33;
Ультрафиолет
PL/SQL procedure successfully completed.
При сложной логике ветвления и проверке многочисленных условий рекомендуется вместо вложенных команд IF использовать условную команду CASE, так как с ней получается более понятный и компактный код.
Условная команда CASE
Команда CASE имеет две разновидности:
простая команда CASE, которая связывает одну или несколько последовательностей команд PL/SQL с некоторыми значениями (выполняемая последовательность команд выбирается при совпадении результата вычисления заданного выражения со значением, связанным с этой последовательностью команд);
поисковая команда CASE, которая выбирает для выполнения последовательность команд в зависимости от результатов проверки списка логических условий (выполняется последовательность команд, связанная с первым логическим условием в списке, результат проверки которого оказался равным TRUE).
Несмотря на громоздкое описание, работать с командой CASE обеих разновидностей просто и удобно.
Простая команда CASE имеет следующий синтаксис:
CASE выражение
WHEN результат 1 THEN
последовательность команд 1;
WHEN результат 2 THEN
последовательность команд 2;
…
ELSE
альтернативная последовательность команд;
END CASE;
Простая команда CASE обычно используется для избавления от многочисленных команд IF и конструкций ELSE в них путем формирования хорошо структурированных ветвей кода в зависимости от списка значений, которые может принимать некоторая управляющая переменная. Приведем пример поиска слова на русском языке по английскому аналогу:
SQL> DECLARE
2 english_termin VARCHAR2(20);
3 text1 VARCHAR2(30) := 'Инфракрасное излучение';
4 text2 VARCHAR2(30) := 'Видимый свет';
5 text3 VARCHAR2(30) := 'Ультрафиолет';
6 text4 VARCHAR2(30) := 'Неизвестный термин';
7 BEGIN
8 english_termin := &Input_Data;
9 CASE english_termin
10 WHEN 'Infrared radiation' THEN DBMS_OUTPUT.PUT_LINE(text1);
11 WHEN 'Visible light' THEN DBMS_OUTPUT.PUT_LINE(text2);
12 WHEN 'Ultraviolet' THEN DBMS_OUTPUT.PUT_LINE(text3);
13 ELSE DBMS_OUTPUT.PUT_LINE(text4);
14 END CASE;
15 END;
16 /
Enter value for input_data: 'Ultraviolet'
old 8: english_termin := &Input_Data;
new 8: english_termin := 'Ultraviolet';
Ультрафиолет
PL/SQL procedure successfully completed.
Поисковая команда CASE имеет следующий синтаксис:
CASE
WHEN верно логическое условие 1 THEN
последовательность команд 1;
WHEN верно логическое условие 2 THEN
последовательность команд 2;
…
ELSE
альтернативная последовательность команд;
END CASE;
Перепишем пример определения источника излучения с использованием поисковой команды CASE вместо команды IF (сравните с предыдущей версией):
SQL> DECLARE
2 lamda NUMBER;
3 text1 VARCHAR2(30) := 'Инфракрасное излучение';
4 text2 VARCHAR2(30) := 'Видимый свет';
5 text3 VARCHAR2(30) := 'Ультрафиолет';
6 BEGIN
7 lamda := &Input_Data;
8 CASE
9 WHEN (lamda > 0.65)
10 THEN DBMS_OUTPUT.PUT_LINE(text1);
11 WHEN (Lamda < 0.41)
12 THEN DBMS_OUTPUT.PUT_LINE(text3);
13 ELSE
14 DBMS_OUTPUT.PUT_LINE(text2);
15 END CASE;
16 END;
17 /
Enter value for input_data: 0.50
old 7: lamda := &Input_Data;
new 7: lamda := 0.50;
Видимый свет
PL/SQL procedure successfully completed.
Помимо условной команды CASE, в PL/SQL есть выражение CASE, которое последовательно вычисляет логические выражения из заданного списка, пока одно из них не станет истинным, а затем возвращает результат связанного с ним выражения. В этом заключается отличие выражения CASE от команды CASE – команда CASE управляет потоком других команд, а выражение CASE вычисляется и его значение может быть присвоено переменным, использовано в других выражениях и т. д.
DECLARE
lamda NUMBER := 0.50;
text1 VARCHAR2(30) := 'Инфракрасное излучение';
text2 VARCHAR2(30) := 'Видимый свет';
text3 VARCHAR2(30) := 'Ультрафиолет';
answer VARCHAR2(30);
BEGIN
answer := CASE WHEN (lamda > 0.65) THEN text1
WHEN (Lamda < 0.41) THEN text3
ELSE text2
END;
DBMS_OUTPUT.PUT_LINE(answer);
END;
Команда перехода GOTO
Команда перехода GOTO позволяет осуществить переход по метке, присутствующей в коде PL/SQL. С помощью уникального идентификатора, заключенного в двойные угловые скобки (метки), можно пометить любую часть исполняемого блока PL/SQL для перехода в это место.
SQL> DECLARE
2 s NUMBER := 1;
3 BEGIN
4 IF s = 1 THEN
5 GOTO mybranch; – переход по метке mybranch
6 ELSE
7 s := 1;
8 END IF;
9 <<mybranch>> – установка метки mybranch
10 NULL;
11 END;
12 /
PL/SQL procedure successfully completed.
Команда GOTO в языках программирования является объектом критики, поскольку чрезмерное ее применение приводит к созданию нечитаемого «спагетти-кода». Впервые эта точка зрения была отражена в статье Эдсгера Дейкстры «Доводы против команды GOTO», в которой утверждалось, что квалификация программистов обратно зависит от частоты использования команды GOTO в их программах. Многие преподаватели не принимают написанные студентами программы с командами GOTO по той причине, что наличие GOTO свидетельствует о неумении правильно структурировать исходный код.
Команда NULL
Команда NULL («пустая» команда) обычно используется как «заглушка» в месте, где надо написать какую-нибудь команду, потому что ничего не написать там нельзя по требованиям синтаксиса PL/SQL. Потом, по мере появления определенности, «заглушка» заменяется на функциональный код:
CASE sex
WHEN 'М' THEN
sex_decoded := 'male';
WHEN 'F' THEN
sex_decoded := 'female';
ELSE
NULL; – toDo: write code for exception sex not in list {F,M} ;))
END CASE;
Также команда NULL используется при обработке исключений, когда обработка какого-нибудь исключения заключается в отсутствии каких-либо действий (ничегонеделании). Такая практика «замалчивания» исключений обычно не приветствуется, так как она приводит к сложно выявляемым проблемам и неожиданным результатам работы программ.
Циклы
В языке PL/SQL имеется три вида циклов:
простой цикл, который начинается с ключевого слова LOOP и завершается командой END LOOP;
цикл WHILE с предусловием, который позволяет выполнить одну и ту же последовательность команд, пока проверяемое условие истинно;
цикл FOR со счетчиком.
Простой цикл
Простой цикл рассмотрим на примере определения числа, факториал которого является наименьшим числом, впервые превышающим заданную константу (1 000 000 000).
SQL> DECLARE
2 arg NUMBER; – Переменная для вычисления факториала
3 i NUMBER; – Переменная-счетчик
4 limit NUMBER := 1000000000; – Граница
5 text1 VARCHAR2(80) := 'n! числа, впервые превышающий 1000000000';
6
7 BEGIN
8 i := 0;
9 arg := 1;
10 LOOP
11 EXIT WHEN arg > limit;
12 arg := arg*(i+1);
13 i := i + 1;
14 END LOOP;
15 DBMS_OUTPUT.PUT_LINE(text1);
16 DBMS_OUTPUT.PUT_LINE(TO_CHAR(arg));
17 DBMS_OUTPUT.PUT_LINE('Искомое число = '||TO_CHAR(i));
18 END;
/
n! числа, впервые превышающий 1000000000
6227020800
Искомое число = 13
PL/SQL procedure successfully completed.
Из любого цикла в PL/SQL можно выйти командой EXIT с указанием логического условия выхода. В основном команда EXIT используется в простых циклах, потому что в циклах FOR и WHILE и так явно указываются условия окончания цикла, а иметь в коде больше одного условия окончания для цикла является плохим стилем программирования.
Если происходит зацикливание (выполнение бесконечного цикла без выхода из него), то программа PL/SQL «уходит в себя» («повисает»). Для прекращения выполнения такой программы в SQL*Plus следует нажать на клавиатуре комбинацию клавиш Ctrl+C:
SQL> BEGIN LOOP NULL; END LOOP; END;
2 /
^C
Цикл WHILE
Цикл WHILE с предусловием позволяет выполнить одну и ту же последовательность команд PL/SQL пока истинно проверяемое предусловие.
С помощью цикла WHILE найдем число, факториал которого является наименьшим числом, впервые превышающим 1 000 000 000 000:
SQL> DECLARE
2 arg NUMBER; – Переменная для вычисления факториала
3 i NUMBER; – Переменная-счетчик
4 limit NUMBER := 1000000000000;– Граница
5 text1 VARCHAR2(80):='n! числа, впервые превышающий 1000000000000;
6
7 BEGIN
8 i := 0;
9 arg := i;
10 WHILE arg < 1000000000000 LOOP
11 arg := arg*(i+1);
12 i := i + 1;
13 END LOOP;
14 DBMS_OUTPUT.PUT_LINE(text1);
15 DBMS_OUTPUT.PUT_LINE(TO_CHAR(arg));
16 DBMS_OUTPUT.PUT_LINE('Искомое число = '||TO_CHAR(i));
17 END;
/
n! числа,впервые превышающий 1000000000000
1307674368000
Искомое число = 15
PL/SQL procedure successfully completed.
Отметим, что если условие цикла WHILE изначально ложно (FALSE), то цикл не выполнится ни разу.
Цикл FOR
Цикл FOR («цикл со счетчиком»), используется в том случае, когда известно, сколько раз нужно выполнить итерацию цикла. Приведем пример вычисления факториала заданного числа.
SQL> DECLARE
2 arg NUMBER := 1;
3 n NUMBER := 20;
4 text1 VARCHAR2(30) := 'Факториал числа '||n||' = ';
5 BEGIN
6 FOR i IN 1..n LOOP
7 arg := arg*i;
8 END LOOP;
9 DBMS_OUTPUT.PUT_LINE(text1||TO_CHAR(arg));
10 END;
/
Факториал числа 20 = 2432902008176640000
PL/SQL procedure successfully completed.
Обратите внимание, что счетчик – управляющую переменную цикла (в данном случае i) объявлять в разделе объявлений не нужно, она объявляется автоматически с областью видимости между ключевыми словами LOOP и END LOOP.
При рассмотрении циклов FOR обычно возникают два вопроса:
есть ли возможность сделать так, чтобы значения счетчика цикла не возрастали, а уменьшались?
есть ли возможность нетривиальных, то есть не на единицу, приращений шага счетчика цикла?
Цикл с ключевым словом REVERSE
Цикл со счетчиком, кратным 10
SQL> BEGIN
2 FOR i IN REVERSE 1..5 LOOP
3 DBMS_OUTPUT.PUT_LINE(i);
4 END LOOP;
5 END;
6 /
5
4
3
2
1
PL/SQL procedure successfully completed.
SQL> BEGIN
2 FOR i IN 1..20 LOOP
3 IF MOD(i,10)=0 THEN
4 – тело цикла
5 DBMS_OUTPUT.PUT_LINE(i);
6 END IF;
7 END LOOP;
8 END;
9 /
10
20
PL/SQL procedure successfully completed.
На оба вопроса ответы положительные – такие возможности в языке PL/SQL имеются. Для обратного цикла в конструкции FOR LOOP следует указать ключевое слово REVERSE. Для нетривиальных приращений нужно внутри цикла с помощью команды IF просто пропускать шаги с ненужными значениями счетчика. Счетчик цикла всегда изменяется на единицу, просто на некоторых шагах цикла ничего делать не нужно.
Команда CONTINUE
Команда CONTINUE появилась в версии Oracle 11g. При истинности условия, записанного в конструкции WHEN команды CONTINUE, выполнение текущей итерации цикла прекращается и происходит переход на начало следующей итерации.
Благодаря команде CONTINUE можно, например, вынести проверки в начало цикла. Перепишем приведенный выше пример с нетривиальным приращением счетчика цикла FOR без помещения кода обработки в условную команду IF:
BEGIN
FOR i IN 1..20 LOOP
CONTINUE WHEN MOD(i,10)!=0;
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
Работа с коллекциями
Коллекции относятся к составным типам данных 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;