Язык PL/SQL

Приведем конкретный пример. В базе данных CRM-системы была таблица clients, в которой имелся столбец inn. На момент разработки системы клиентами могли быть только юридические лица, у которых длина ИНН составляет 10 символов. Со временем компания стала обслуживать и физических лиц, у которых длина ИНН 12 символов. Администратор базы данных изменил тип данных столбца inn таблицы clients с VARCHAR2(10) на VARCHAR2(12) и в таблице стал появляться строки с длинными ИНН. Так как в коде PL/SQL все переменные для работы с ИНН были объявлены как VARCHAR2(10), то при считывании из базы данных ИНН физических лиц в программах PL/SQL стали происходить ошибки. Если бы переменные для ИНН в свое время были объявлены с привязкой к столбцу inn с помощью атрибута %TYPE, то они автоматически «расширились» бы сами и ошибок на стадии выполнения не происходило бы.

Без перечисления столбцов результирующих выборок SQL-запросов пишется очень компактный код вида

l_person person%ROWTYPE;

SELECT * INTO l_person FROM person WHERE id=13243297;

print(l_person);

SQL-запрос выбирает все столбцы таблицы person, и у объявленной с помощью %ROWTYPE переменной l_person будет ровно столько же атрибутов, сколько столбцов у таблицы person, с такими же именами и типами данных, в том же порядке следования. Значения всех столбцов считываемой строки таблицы присвоятся соответствующим атрибутам записи PL/SQL. Если в таблице person в будущем появится новый столбец, он автоматически «подхватится» и SQL-запросом (SELECT *) и объявлением переменной l_person в программе PL/SQL. Никаких изменений в код вносить не потребуется, автоматическая перекомпиляция программы произойдет при первом обращении к ней.

Объявлять переменные как записи PL/SQL с помощью атрибута %ROWTYPE можно не только на основе какой-то одной таблицы, но и на основе столбцов результирующих выборок произвольных SQL-запросов. Для этого записи PL/SQL объявляются на основе явных курсоров, рассматриваемых далее.

Структура программы PL/SQL

Структура блока

В PL/SQL, как и в большинстве процедурных языков программирования, наименьшей единицей группировки исходного кода является блок. Он представляет собой фрагмент кода, определяющий границы выполнения кода и области видимости для объявлений. Блоки могут вкладываться друг в друга.

Разделы блока PL/SQL

Блок PL/SQL состоит из четырех разделов:

раздел заголовка;

раздел объявлений;

исполняемый раздел;

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

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

Синтаксически блок PL/SQL выглядит следующим образом:

раздел заголовка

DECLARE

раздел объявлений

BEGIN

исполняемый раздел

EXCEPTION

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

END;

В разделе заголовка указываются:

тип блока (процедура, функция);

имя блока (имя процедуры, функции);

имена параметров, их типы данных и режимы передачи значений.

В разделе объявлений объявляются пользовательские типы данных, переменные и константы, которые потом используются в исполняемом разделе и разделе обработки исключений. В исполняемом разделе реализуется собственно логика программы. В вырожденном случае там может присутствовать только одна «пустая» команда NULL. Обработка исключений рассматривается далее в отдельном параграфе.

Ключевые слова BEGIN и END в языке PL/SQL являются операторными скобками, подобными символам { и } в других языках программирования и отмечают начало исполняемого раздела и конец блока. Каждая команда в PL/SQL должна завершаться точкой с запятой (символом ; ).

Виды блоков PL/SQL

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

именованные блоки (с разделом заголовка);

анонимные блоки (без раздела заголовка).

Именованные блоки в свою очередь тоже бывают двух видов:

именованные блоки хранимых в базе данных программ (процедур, функций, пакетов и триггеров);

именованные блоки в разделах объявлений других блоков (анонимных или именованных).

Хранимые программы (stored programs) являются объектами базы данных Oracle и создаются DDL-командой CREATE, после которой записывается именованный блок PL/SQL. Имя блока, указанное в разделе заголовка, будет являться именем объекта базы данных.

Анонимные блоки (anonymous blocks) раздела заголовка не имеют. Если блок не имеет раздела заголовка, то он не имеет и имени, которое в этом разделе указывается, поэтому такие блоки и называются анонимными.

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

Анонимный блок-сценарий file1.sql

Вложенные анонимные блоки

в именованном блоке хранимой программы

DECLARE

i INTEGER;

– именованный блок процедуры proc1

– в разделе анонимного блока

PROCEDURE proc1 IS

BEGIN

NULL;

END;

BEGIN

– вызов процедуры proc1

proc1;

END;

– именованный блок процедуры proc2

CREATE PROCEDURE proc2 AS

BEGIN

– родительский анонимный блок,

– вложенный в именованный proc2:

DECLARE

BEGIN

– еще один анонимный блок

– вложенный в родительский:

DECLARE

BEGIN

NULL;

END; – конец вложенного блока

END; – конец родительского блока

END;– конец именованного блока proc2

Комментарии

В любом месте исходного кода на PL/SQL могут быть комментарии, однострочные и многострочные.

Однострочные комментарии начинаются с двух дефисов (символы –). Весь текст после двух дефисов и до конца строки рассматривается как комментарий и игнорируется компилятором. Если два дефиса стоят в начале строки, то комментарием является вся строка.

Многострочный комментарий размещается между начальным (/*) и конечным (*/) ограничителями. Вложение многострочных комментариев друг в друга не допускается.

Тема комментирования исходного кода заслуживает отдельного рассмотрения. Авторы книги являются сторонниками следующего принципа: «Комментируйте неочевидные участки кода. Не комментируйте очевидные».

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

Переменные и константы PL/SQL

Приведем пример анонимного блока, в котором объявлены одна константа и две переменные, а в исполняемом разделе выполняются действия по вычислению натуральных логарифмов чисел 2 и 3.

/* Вычисление

двух логарифмов */

SQL> DECLARE

2 header1 CONSTANT VARCHAR2(20) := 'Логарифм двух равен ';

3 header2 CONSTANT VARCHAR2(20) := 'Логарифм трех равен ';

4 arg INTEGER := 2;

5 – исполняемый раздел

6 BEGIN

7 DBMS_OUTPUT.PUT_LINE(header1||LN(arg));

8 arg := arg+1;

9 DBMS_OUTPUT.PUT_LINE(header2||LN(arg));

10 END;

11 /

Логарифм двух равен .6931471805599453094172321214581765680782

Логарифм трех равен 1.09861228866810969139524523692252570465

PL/SQL procedure successfully completed.

В разделе объявлений можно объявлять как переменные, так и константы (с помощью ключевого слова CONSTANT). Константа отличается от переменной тем, что ее значение нельзя изменять после объявления. Если указать константу в левой части оператора присваивания и т. п., то это будет определено как ошибка еще на этапе компиляции. Переменным присваивать значения можно в любом разделе, в том числе прямо при ее объявлении в разделе объявлений. По умолчанию переменная инициализируется «пустым» значением NULL.

Имена констант, переменных, пользовательских типов данных в грамматике PL/SQL называются идентификаторами. К идентификаторам предъявляются следующие требования:

идентификатор должен состоять только из букв, цифр и символов _$#

идентификатор должен начинаться с буквы;

длина идентификатора должна быть до 30 символов;

идентификатор не должен быть зарезервированным словом.

Примеры недопустимых идентификаторов:

2_name (начинается не с буквы, правильно – l_second_name);

l_exchange_rate_on_current_date (длина свыше 30 символов).

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

В языке PL/SQL переменные, константы и пользовательские типы данных являются локальными для блока, в котором они объявлены. Когда выполнение блока будет завершено, все эти объекты внутри программы становятся недоступными. Можно сказать, что у каждого объявленного в программе PL/SQL элемента имеется некоторая область видимости – участок программы, в котором можно ссылаться на этот элемент (блок, в котором элемент объявлен, и все вложенные в него блоки).

Структуры управления вычислениями

Известно, что для реализации алгоритмов на процедурном языке программирования требуется наличие в нем трех следующих структур управления вычислениями, причем должна быть возможность вкладывать все структуры друг в друга произвольным образом:

последовательность команд (выполнение команд согласно их упорядоченности);

выбор (проверка условия и выполнение той или иной последовательности команд в зависимости от истинности или ложности условия);

повторение (выполнение последовательности команд до тех пор, пока условие повторения принимает истинное значение).

Рис. 1. Структуры управления вычислениями.

Команды, указанные в коде PL/SQL, выполняются последовательно. Такая схема называется потоком команд, то есть первая требуемая структура управления вычислениями (последовательность) в PL/SQL имеется. Рассмотрим языковые конструкции 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

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. Так как основная операция с коллекцией – это перебор и обработка всех ее элементов, то естественно познакомиться с особенностями работы с коллекциями после того, как рассмотрены циклы.

Виды и свойства коллекций