Введение в язык PL/SQL

По материалам сайта http://www.codenet.ru/db/ora1/
 
Содержание
 
1. Основные конструкции языка PL/SQL

2. Типы данных, переменные, константы и выражения
2.1. Типы данных, доступные в PL/SQL
2.2. Таблицы PL/SQL
2.3. Записи PL/SQL
2.4. Переменные, константы и выражения
 
3. Связь объектов PL/SQL с таблицами базы данных
3.1. Явный курсор
3.1.1. Объявление курсора
3.1.2. Открытие курсора (OPEN)
3.1.3. Выборка строк из курсора (FETCH)
3.1.4. Закрытие курсора (CLOSE)
3.1.5. Использование курсора в цикле FOR
3.1.6. Атрибуты явного курсора
3.1.7. Изменение или удаление текущей строки курсора
3.2. Неявный курсор (SQL курсор)
3.2.1. SELECT ... INTO
3.2.2. UPDATE, DELETE и INSERT
3.2.3. Атрибуты неявного курсора (SQL курсора)

4. Операторы управления выполнением программы
4.1. Операторы условного перехода (IF ...)
4.2. Метки и оператор безусловного перехода (GOTO)
4.3. Операторы цикла (LOOP, WHILE...LOOP и FOR...LOOP)
4.4. Операторы EXIT, EXIT-WHEN и NULL

5. Обработка ошибок
5.1. Встроенные исключительные ситуации
5.2. Исключительные ситуации, определяемые пользователем
5.3. Обработчик OTHERS

6. Транзакции
7. О программах PL/SQL
8. Отладка программ PL/SQL
 
Язык PL/SQL - процедурные расширения языка SQL
 
 1. Основные конструкции языка PL/SQL
 
 PL/SQL - это блочно-структурированный язык. Структура блока имеет вид:
 
 [ DECLARE
 -- описание переменных, констант и пользовательских типов данных ]
 BEGIN
 -- тело основной программы, в которую могут вкладываться другие
 -- блоки (в них также можно вкладывать блоки и т.д.)
 [ EXCEPTION
 -- драйверы для обработки исключительных ситуаций в программе ]
 END;
 
 Программы PL/SQL могут быть неименовынными (анонимными блоками), но чаще
всего используются именованные программы: процедуры, функции, пакеты и триггера
(см. п. 7).
 
 2. Типы данных, переменные, константы и выражения
 
 2.1. Типы данных, доступные в PL/SQL
 Кроме типов данных Oracle7 (см. п. 5), PL/SQL поддерживает несколько
дополнительных типов данных и позволяет использовать в своих конструкциях
некоторые основные типы с большим диапазоном.
Тип данных Описание
-------------- ---------------------------------------------------------------
BINARY_INTEGER Этот тип данных и его подтипы NATURAL и POSITIVE применяются для
 создания переменных и констант, которые хранят число со знаком.
 Двоичные целые числа могут принимать значения в диапазоне от -2
 в 31 степени до 2 в 31 степени минус 1.
BOOLEAN Принимается для создания переменных и констант, в которых
 хранятся логические значения TRUE и FALSE.
CHAR Есть подтипы CHARACTER и STRING. Максимальный размер 32767.
NUMBER Есть подтипы DEC, DECIMAL, DOUBLE, PRECISION, FLOAT, INT,
 INTEGER, NUMERIC, REAL и SMALLINT.
RECORD Используется для создания пользовательских типов записей базы
 данных.
TABLE  Служит для создания табличных типов данных PL/SQL.
VARCHAR2 Есть подтип VARCHAR. Максимальный размер 32767.
col%TYPE Используется для определения типа данных столбца или переменной
 по типу данных другого столбца или переменной, к имени которого
 или которой (col) приписан суффикс %TYPE.
tab%ROWTYPE Используется для определения типа данных записи по типу данных
 столбцов таблицы, к имени которой (tab) приписан суффикс
 %ROWTYPE.
 Рассмотрим подробнее типы данных TABLE и RECORD, позволяющие создавать
одномерные массивы и записи, широко используемые в программах PL/SQL.
 
 2.2. Таблицы PL/SQL
 Таблица PL/SQL - это одномерный массив с неограниченным числом строк. Для
объявления этого массива (таблицы PL/SQL или TABLE) необходимо сначала
определить его тип данных.
 
 Для описания типа данных TABLE используется синтаксис:
 
 TYPE type_name IS TABLE OF { column_type | variable%TYPE |
 table.column%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER;
 
где "type_name" - спецификатор типа, используемый в последующих объявлениях
таблиц PL/SQL, и "column_type" - любой из скалярных типов данных: CHAR, DATE или
NUMBER. С помощью атрибута %TYPE можно установить "type_name" соответствующим
типу данных какой-либо переменной (variable) или столбца (table.column).
 Имя (например, name_plsql_table), которое описывается табличным типом
данных, называется таблицей PL/SQL. Это описание, размещаемое в разделе DECLARE,
имеет вид:
 name_plsql_table type_name;
 Ссылки на строки таблицы PL/SQL осуществляются аналогично ссылкам на элементы
одномерного массива:
 name_plsql_table(index) ,
где index принадлежит типу BINARY_INTEGER. Например, для ссылки на третью строку
в таблице PL/SQL "ename_tab" следует написать: ename_tab(3).
 Для присвоения значения конкретной строке таблицы PL/SQL используется
синтаксис:
 name_plsql_table(index) := expr;
 Для ввода в таблицу PL/SQL значений из какого-либо столбца базовой таблицы
или представления, а также для выборки значений из таблицы PL/SQL, необходимо
использовать цикл. (Примеры таких операций приведены в п. 4.)
 
 2.3. Записи PL/SQL
 Record PL/SQL - это совокупность полей, каждое из которых должно иметь
уникальное имя (в пределах записи). Эти поля могут принадлежать различным
типам данных.
 Если создаваемая запись (sotr) соответствует описанию столбцов какой-либо
базовой таблицы (например, kadry), то ее объявление можно осуществить в разделе
DECLARE с помощью атрибута %ROWTYPE:
 
 sotr kadry%ROWTYPE;
 
 В противном случае для объявления записи необходимо сначала определить ее
тип данных. Для описания типа данных RECORD используется синтаксис:
 
 TYPE type_name IS RECORD
 ( field_name1 {field_type | variable%TYPE | table.column%TYPE
 | table%ROWTYPE} [NOT NULL],
 field_name2 {field_type | variable%TYPE | table.column%TYPE
 | table%ROWTYPE} [NOT NULL],
 ...);
 
где "type_name" - спецификатор типа, используемый в последующих объявлениях
записей PL/SQL, и "field_type" - любой тип данных. С помощью атрибута %TYPE
можно установить "type_name" соответствующим типу данных какой-либо переменной
(variable) или столбца (table.column). Атрибут %ROWTYPE позволяет определить
поле как запись, соответствующую описанию столбцов какой-либо базовой таблицы.
 
 При объявлении типа записи можно присвоить ее полям некоторые значения. Если
же для поля вводится ограничение NOT NULL (для предотвращения назначения пустых
значений), то этому полю надо обязательно присвоить значение. Например:
 
 TYPE SotrRecTyp IS RECORD (nomer NUMBER(4) NOT NULL := 1001,
 familiy CHAR(20), dolgnost CHAR(14), otdel NUMBER(3) := 102);
 
 Объявление создаваемой записи (например, name_plsql_record) производится в
разделе DECLARE и имеет вид:
 name_plsql_record type_name;
 Ссылки на отдельные поля записи осуществляются так:
 name_plsql_record.field_name;
 Для присвоения значения конкретному полю записи используется синтаксис:
 name_plsql_record.field_name := expr;
 
 Примеры использования записей в программах PL/SQL приведены ниже.
 Для ввода в таблицу PL/SQL значений из какого-либо столбца базовой таблицы
или представления, а также для выборки значений из таблицы PL/SQL, необходимо
использовать цикл. (Примеры таких операций приведены в п. 4.)
 
 2.4. Переменные, константы и выражения
 В программах PL/SQL могут использоваться переменные и константы, описываемые
в разделе DECLARE с помощью конструкции вида:
 
 variable_name [CONSTANT] type_name [NOT NULL] [ { := | DEFAULT } expr ]
 
 Например
 
 birthdate DATE;
 emp_count SMALLINT := 0;
 emp_count SMALLINT DEFAULT 0;
 acct_id VARCHAR2(5) NOT NULL := 'AP001';
 pi CONSTANT REAL := 3.14159;
 area REAL := pi * radius**2;
 valid_id BOOLEAN;
 valid_id VARCHAR2(5); -- недопустимое вторичное описание valid_id
 i, j, k SMALLINT; -- нельзя описывать список; надо:
 -- i SMALLINT; j SMALLINT; k SMALLINT;
 credit REAL(7,2);
 debit credit%TYPE; -- тип данных аналогичный типу данных "credit"
 
 Синтаксис выражения описан в п. 4.3.
 
 3. Связь объектов PL/SQL с таблицами базы данных
 Чтобы программа PL/SQL могла работать с информацией, содержащейся в базах
данных, необходимо организовать обмен между значениями столбцов таблиц баз
данных и переменными PL/SQL.
 Известно, что для выбора информации из таблиц используется SQL предложение
SELECT. При его выполнении Oracle создает специальную рабочую область,
содержащую информацию о самом SELECT, данные, которые требуются для его
выполнения (например, результаты подзапросов), и, наконец, окончательный
результат выполнения SELECT. PL/SQL имеет несколько механизмов доступа к
этой рабочей области. Одним из них является курсор, с помощью которого можно
присвоить имя этой рабочей области и манипулировать содержащейся в ней
информацией, последовательно выбирая строки результата и пересылая значения
столбцов текущей строки в переменные PL/SQL. Существуют и другие механизмы,
не требующее создания явного курсора.
 
 3.1. Явный курсор
 Курсор - это средство языка SQL, позволяющее с помощью команд OPEN, FETCH и
CLOSE получить построчный доступ к результату запроса к базе данных. (Будем
также называть курсором и сам набор строк, полученный в результате выполнения
запроса.)
 Для использования курсора его надо сначала объявить, т.е. дать ему имя и
указать (с помощью предложения SELECT), какие столбцы и строки базовых таблиц
должны быть помещены в набор строк, названный этим именем. Команда OPEN
инициализирует получение указанного набора и установку перед первой его строкой
указателя текущей строки. Команда FETCH служит для установки указателя текущей
строки на следующую запись (первый раз на строку с номером 1) и выборки из
текущей строки курсора значений указанных столбцов с пересылкой их в переменные
PL/SQL. (Выполнением FETCH в цикле можно последовательно выбрать информацию из
всех строк курсора.) Наконец, команда CLOSE позволяет закрыть (удалить из
памяти) набор строк (при этом описание курсора сохраняется и его можно снова
открыть командой OPEN).
 Существует модификация ("Курсор в цикле FOR"), позволяющая организовать
последовательный выбор строк объявленного курсора без явного использования
команд OPEN, FETCH и CLOSE.
 
 3.1.1. Объявление курсора
 Перед работой с курсором его следует объявить в разделе DECLARE или другом
допустимом разделе, используя синтаксис:
 
 CURSOR cursor_name [ (parameter [, parameter, ... ] ) ] IS SELECT ... ,
 
где
 
 cursor_name - имя курсора;
 SELECT ... - предложение SELECT, определяющее строки курсора;
 parametr - имеет следующий синтаксис:
 variable_name [IN] type_name [ { := | DEFAULT } value ] ,
 type_name - любой тип (подтип) данных PL/SQL без указания ограничений
(например, длины символьных значений).
 
 Формальные параметры курсора используются только для передачи значений в
WHERE фразу предложения SELECT с целью отбора нужных строк запроса. Передача
таких значений производится во время открытия курсора командой OPEN. Если
значения формальных параметров отсутствуют в команде OPEN и не заданы по
умолчанию (:= value или DEFAULT value), то выдается ошибка. При наличии тех и
других используются параметры из команды OPEN.
 В следующем примере использованы оба способа задания значений по умолчанию
параметрам курсора:
 
DECLARE
CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT 'Преподаватели',
 tdat DATE := '1.1.1996') IS
 SELECT (TO_CHAR(razr)||' '||imya_dolg) razr_dolg, stavka
 FROM shtat x, dolgnosti y, grup_dolg z
 WHERE x.dolgn = y.dolgn AND y.grup_dolg = z.grup_dolg AND otdel = otd
 AND tdat BETWEEN nachalo AND konec AND imya_grup_dolg = grup
 ORDER BY razr_dolg DESC;
 
Кроме того, в нем выражению "(TO_CHAR(razr)||' '||imya_dolg)" дан псевдоним
"razr_dolg", использованный во фразе ORDER BY. Oracle рекомендует создавать
псевдонимы для всех выражений фразы SELECT с целью ссылки на них при работе с
курсором.
 
 3.1.2. Открытие курсора (OPEN)
 Команда OPEN имеет следующий синтаксис
 
 OPEN cursor_name [ (value [,value]...) ];
 
где список значений ("value") используется для передачи параметров курсора и
должен по числу и типу данных совпадать с описанием этих параметров.
 Команда выполняет объявленный в курсоре SELECT ... , используя (если есть
параметры) передаваемые из OPEN значения или значения, указанные при объявлении
курсора, создавая набор строк и устанавливая указатель текущей строки перед
первой из них. Так, по команде
 OPEN s1;
будет создан набор:
 razr_dolg stavka
 Указатель текущей строки ---> ------------------------ ------
 17 Профессор 1.75
 16 Профессор 3.
 15 Доцент 9.75
 14 Доцент 4.
 13 Старший преподаватель 8.75
 11 Ассистент 1.
где использовались значения параметров, заданные при описании, а по команде:
 OPEN s1 (102,'Специалисты','1.1.1996');
будет создан другой набор:
 razr_dolg stavka
 Указатель текущей строки ---> ------------------------ ------
 13 Ведущий программист 1.
  13 Ведущий электроник 2.
 11 Электроник 1 категории 1.5
 10 Программист 2 категории 3.5
 8 Инженер 2 категории 2.
  4 Лаборант 3.
 
 3.1.3. Выборка строк из курсора (FETCH)
 Команда FETCH, используемая для продвижения на один шаг указателя текущей
строки курсора и пересылки ее значений в переменные или запись, имеет следующий
синтаксис:
 
 FETCH cursor_name INTO {variable_name1[,variable_name2]...} | record_name ;
 
 Для каждого значения столбца, возвращенного запросом, в списке INTO должна
иметься переменная или поле записи соответствующего типа данных. Такие
переменные или записи должны быть заранее описаны в декларативной части блока
PL/SQL. Например
 
CREATE PROCEDURE pr_shtat IS
CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT 'Преподаватели',
 tdat DATE := '1.1.1996') IS
 SELECT (TO_CHAR(razr)||' '||imya_dolg) razr_dolg, stavka
 FROM shtat x, dolgnosti y, grup_dolg z
 WHERE x.dolgn = y.dolgn AND y.grup_dolg = z.grup_dolg AND otdel = otd
 AND tdat BETWEEN nachalo AND konec AND imya_grup_dolg = grup
 ORDER BY razr DESC;
 sh_raz VARCHAR2(45); -- переменная для хранения значения razr_dolg
 sh_stav shtat.stavka%TYPE; -- переменная для хранения значения stavka
 raz VARCHAR(500); -- переменная, в которой будет последовательно
 -- накапливаться текст "разряд-должность"
BEGIN
 OPEN s1;
 LOOP
 FETCH s1 INTO sh_raz,sh_stav;
 EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки
 -- (см. п. 3.1.6)
 raz := raz||sh_raz||';';
 ...
 END LOOP;
 CLOSE s1;
END pr_shtat;
 
 Внутри цикла можно использовать значения переменных sh_raz и sh_stav, которые
после открытия курсора равны "17 Профессор" и "1.75", после первого прохождения
цикла - "16 Профессор" и "3.", после второго - "15 Доцент" и "9.75" и т.д.
 При выборке значений текущей строки в запись, например, с именем ShRec надо
немного изменить как описание, так и тело блока процедуры:
 ...
 ORDER BY razr DESC;
 TYPE ShRecTyp IS RECORD (raz_dol VARCHAR(45), -- описание типа данных
  stav shtat.stavka%TYPE); -- записи ShRec
 ShRec ShRecTyp; -- объявление записи ShRec
 raz VARCHAR(500); -- переменная, в которой будет последовательно
 -- накапливаться текст "разряд-должность"
BEGIN
 OPEN s1;
 LOOP
 FETCH s1 INTO ShRec;
 EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки
 raz := raz||ShRec.raz_dol||';';
 ...
 Теперь значения, получаемые ранее из sh_raz и sh_stav, можно получать из
полей ShRec.raz_dol и ShRec.stav записи ShRec.
 Целесообразность использования записей возрастает с увеличением количества
ее полей или возможности создания описания по типу уже существующей записи с
помощью атрибута %ROWTYPE.
 Наконец, если при выполнении цикла осуществлен переход за последнюю строку
набора, то значения переменных FETCH-списка будут неопределены.
 
 3.1.4. Закрытие курсора (CLOSE)
 Команда CLOSE используется для освобождения всех ресурсов, которые
поддерживались открытым курсором (при этом описание курсора сохраняется и его
можно снова открыть командой OPEN). Синтаксис команды CLOSE имеет вид:
 
 CLOSE cursor_name;
 
 3.1.5. Использование курсора в цикле FOR
 В большинстве ситуаций, которые требуют явного курсора, текст программы может
быть упрощен при использовании "курсора в цикле FOR", заменяющего команды OPEN,
FETCH и CLOSE. Курсор в цикле FOR:
 - неявно объявляет индекс цикла записью, поля которой соответствуют столбцам
 (псевдонимам) предложения SELECT ... из описания курсора;
 - передает параметры курсора (если они есть) и открывает курсор;
 - выбирает в цикле строки из полученного набора в индекс цикла (поля записи);
 - закрывает курсор после обработки всех строк набора или досрочному выходу
 из него с помощью команд EXIT или GOTO.
 Синтаксис курсора в цикле FOR имеет вид:
 
 FOR var_rec_name IN cursor_name [ (value [,value]...) ] LOOP
 ТЕЛО ЦИКЛА
 END LOOP;
 
где - var_rec_name индекс цикла, в котором при первом прохождении цикла хранится
 первая строка набора, при втором прохождении цикла - вторая строка и т.д.;
 - список значений ("value") используется для передачи параметров курсора
 (он заменяет в данном случае список из команды OPEN);
 - ТЕЛО ЦИКЛА содержит нужные строки повторяющейся части программы, в которых
 используются переменные с именами var_rec_name.column_name, а column_name
 имя столбца из перечня столбцов предложения SELECT в описании курсора.
 Например,
 
DROP PROCEDURE pr_shtat;
CREATE PROCEDURE pr_shtat IS
CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT 'Преподаватели',
 tdat DATE := '1.1.1996') IS
 SELECT otdel, (TO_CHAR(razr)||' '||imya_dolg) razr_dolg, stavka
 FROM shtat x, dolgnosti y, grup_dolg z
 WHERE x.dolgn = y.dolgn AND y.grup_dolg = z.grup_dolg AND otdel = otd
 AND tdat BETWEEN nachalo AND konec AND imya_grup_dolg = grup
 ORDER BY razr DESC;
 raz VARCHAR(500);  -- переменная, в которой будет последовательно
 -- накапливаться текст "разряд-должность"
BEGIN
 FOR s1_rec IN s1 (102,'Специалисты','1.6.1996') LOOP
 raz := raz||s1_rec.razr_dolg||';';
 ...
 END LOOP;
END pr_shtat;
 
 3.1.6. Атрибуты явного курсора
 Для анализа состояния курсора используются специальные переменные, имена
которых составляются из имени курсора и суффиксов %FOUND, %NOTFOUND, %ROWCOUNT
и %ISOPEN, называемых атрибутами курсора. Если курсор назван "cursor_name", то
эти переменные имеют имена:
 cursor_name%NOTFOUND, cursor_nane%FOUND,
 cursor_nane%ROWCOUNT и cursor_nane%ISOPEN.
 
 Значения таких переменных анализируются при выполнении программы с помощью
различных операторов управления (IF...THEN, EXIT WHEN и т.п.), которые изменяют
(при необходимости) ход выполнения программы. Следует отметить, что ссылка на
эти переменные до открытия курсора приводит к появлению сообщения INVALID_CURSOR.
 Переменная с атрибутом %ISOPEN позволяет определить, открыт ли курсор. Если
он открыт то эта переменная возвращает TRUE, иначе - FALSE. Например:
 
 IF NOT s1%ISOPEN THEN -- курсор не открыт ?
 OPEN s1; -- открыть курсор !
 IF END;
 FETCH ...
 
 Переменные с %NOTFOUND и %FOUND атрибутами показывают состояние текущей
позиции курсора (перед первой выборкой строки курсора обе переменных имеют
значение NULL). Переменная с %NOTFOUND принимает значение FALSE тогда, когда
выборка возвратила строку (при этом переменная с %FOUND принимает значение
TRUE). Если же в результате выборки строка не возвращается, то переменные с
%NOTFOUND и %FOUND принимают значения TRUE и FALSE, соответственно. Пример
использования %NOTFOUND был рассмотрен в п. 3.1.3.
 Переменная с атрибутом %ROWCOUNT содержит количество строк, выбранных из
курсора на текущий момент (при открытии курсора эта переменная содержит ноль).
В следующем примере переменная s1%ROWCOUNT ограничивает выборку из курсора s1
десятью строками:
 
 LOOP
 FETCH s1 INTO sh_raz,sh_stav;
 IF s1%ROWCOUNT > 10 THEN
 ...
 END IF;
 ...
 END LOOP;
 
 3.1.7. Изменение или удаление текущей строки курсора
 Существует два предложения, позволяющие изменить или удалить ту строку
таблицы базы данных, на которую позиционирована текущая строка курсора:
 
 UPDATE [schema.]{table | view}[@dblink] [alias]
 SET { (column [, column] ...) = (subquery)
 | column = { expr | (subquery) } }
 [, { (column [, column] ...) = (subquery)
 | column = { expr | (subquery) } } ] ...
 WHERE CURRENT OF cursor_name;
 DELETE [FROM] [schema.]{table | view}[@dblink] [alias]
 WHERE CURRENT OF cursor_name;
 
 Для этого необходимо, чтобы при объявлении курсора предложение SELECT ...
содержало фразу
 
 FOR UPDATE OF [[schema.]{table | view}.]column
 [, [[schema.]{table | view}.]column ] ... ;
 
в которой следует привести список обновляемых столбцов.
 
 3.2. Неявный курсор (SQL курсор)
 Для всех команд языка SQL, не связанных с объявлением курсора ("явным
курсором"), PL/SQL открывает курсор ("неявный курсор"), на который можно
ссылаться по курсорному имени SQL%. При работе с таким курсором нельзя
использовать команды OPEN, FETCH и CLOSE, но можно использовать атрибуты
курсора, чтобы получить информацию о текущем его состоянии.
 
 3.2.1. SELECT ... INTO
 В тех случаях, когда программе необходимо иметь значения столбцов из одной
строки таблицы, можно воспользоваться предложением SELECT ... INTO, формат
которого имеет вид:
 
 SELECT [DISTINCT | !!under!!ALL]
 { [schema.]{table | view | snapshot}.expr [c_alias] }
 [, { [schema.]{table | view | snapshot}.expr [c_alias] } ] ... }
 INTO { variable_name [, variable_name ] ... } | record_name
 FROM table_list [WHERE condition]
 [GROUP BY expr [, expr] ...] [HAVING condition]
 [ {UNION | UNION ALL | INTERSECT | MINUS} SELECT command]
 [ORDER BY {expr | c_alias | position}
 [!!under!!ASC | DESC] [, {expr | c_alias | position}
 [!!under!!ASC | DESC]] ]...
 [FOR UPDATE [OF [[schema.]{table | view}.]column
 [, [[schema.]{table | view}.]column] ...]
 [NOWAIT] ]
 
 Практически это обычный SELECT, выполняющий присвоение выбираемых значений
столбцов переменным, перечисленным во фразе INTO. Однако такое присвоение
происходит только в том случае, если "WHERE condition" обеспечивает возвращение
по запросу лишь одной строки и переменные заранее описаны в декларативной
части блока PL/SQL.
 
 3.2.2. UPDATE, DELETE и INSERT
 Эти предложения отличаются от аналогичных предложений интерактивного SQL
лишь тем, что в их выражениях (expr) могут использоваться переменные PL/SQL.
 
 3.2.3. Атрибуты неявного курсора (SQL курсора)
 Для анализа результата выполнения предложений SELECT...INTO, INSERT, UPDATE
и DELETE используются три переменные: SQL%NOTFOUND, SQL%FOUND и SQL%ROWCOUNT
(Oracle закрывает курсор SQL автоматически после выполнения SQL предложения,
что делает бессмысленным использование переменной SQL%ISOPEN, так как ее
значение всегда равно FALSE).
 Перед выполнением предложений SELECT...INTO, INSERT, UPDATE и DELETE
переменные SQL%NOTFOUND и SQL%FOUND имеют значение NULL. Переменная SQL%NOTFOUND
принимает значение TRUE, если INSERT, UPDATE и DELETE не произвели изменений
таблиц базы данных или SELECT...INTO не возвратил строк (при этом переменная
SQL%FOUND принимает значение FALSE). В противном случае переменная SQL%NOTFOUND
принимает значение FALSE, а переменная SQL%FOUND - TRUE
 Вот один из примеров использования SQL%NOTFOUND для добавления новой строки
в таблицу temp при сбое модификации:
 
UPDATE shtat SET stavka = stavka + 1 WHERE dolgn = 'доцент' AND razr = 15;
IF SQL%NOTFOUND THEN -- изменение не выполнено
INSERT INTO temp VALUES (...);
END IF;
 
 
 4. Операторы управления выполнением программы
 
 4.1. Операторы условного перехода (IF ...)
 Существует три модификации оператора условного перехода:
 
IF-THEN | IF-THEN-ELSIF
------- | -------------
 IF условие THEN | IF условие1 THEN
 последовательность команд; | 1-я последовательность команд;
 END IF; | ELSIF условие2 THEN
 | 2-я последовательность команд;
IF-THEN-ELSE | ...
------------ | ELSIF условиеN THEN
 IF условие THEN | N-я последовательность команд;
 1-я последовательность команд; | [ ELSE
 ELSE | N+1-я последовательность команд; ]
 2-я последовательность команд; | END IF;
 END IF; |
 
 Синтаксис условий приведен в п. 4.4. Во всех модификациях если "условие" или
"условие1" истинно (TRUE), то выполняется "последовательность команд" или
"1-я последовательность команд" и управление передается на первый оператор
после END IF. Если же оно ложно (FALSE), то:
 - в модификации IF-THEN управление передается на первый оператор после END IF;
 - в модификации IF-THEN-ELSE выполняется 2-я последовательность команд и
 управление передается на первый оператор после END IF;
 - в модификации IF-THEN-ELSIF проверяется условие 2; если оно истинно, то
 выполняется 2-я последовательность команд и управление передается на первый
 оператор после END IF; если условия 1 и 2 ложны, а условие 3 истинно, то
 выполняется 3-я последовательность команд и управление передается на первый
 оператор после END IF; наконец, если условия 1, 2, ..., N ложны, то
 выполняется N+1-я последовательность команд и управление передается на первый
 оператор после END IF.
 Все это справедливо, если внутри последовательности команд нет операторов,
осуществляющих переход за пределы этой последовательности.
 
 
 4.2. Метки и оператор безусловного перехода (GOTO)
 В любом месте программы может быть поставлена метка, имеющая синтаксис:
 
 <<имя_метки>>
 
 Оператор GOTO позволяет осуществить безусловный переход к метке, имя которой
должно быть уникальным внутри программы или блока PL/SQL. Например, управление
передается вниз к помеченному оператору:
 
 BEGIN
 ...
 GOTO insert_row;
 ...
 <>
 INSERT INTO shtat VALUES ...
 END;
 
 В следующем примере управление передается вверх к помеченной
последовательности операторов:
 BEGIN
 ...
 <>
 BEGIN
 UPDATE shtat SET ...
 ...
 END;
 ...
 GOTO update_row;
 ...
 END;
 
 Следует отметить, что использование GOTO (особенно в тех случаях, когда метка
предшествует оператору GOTO) может привести к сложным, нераспознаваемым кодам
ошибок, которые трудно обрабатывать. Поэтому реже используйте GOTO, тем более
что этот оператор нельзя использовать для выполнения перехода:
 - в IF-блок, LOOP-блок или в другой блок, не включающий текущий;
 - из одного предложения IF-оператора к другому;
 - из внешнего блока в SUB-блок;
 - из обработчика особых ситуаций в текущий блок.
 
 4.3. Операторы цикла (LOOP, WHILE...LOOP и FOR...LOOP)
 Циклы служат для повторяемого выполнения последовательности команд. В PL/SQL
используются три модификации операторов цикла: LOOP, WHILE...LOOP и FOR...LOOP.
 Цикл LOOP имеет следующий синтаксис:
 
 LOOP
 последовательность команд;
 END LOOP;
 
и приводит к бесконечному повторению последовательности команд, если внутри
нее нет команд EXIT (выход из цикла), RAISE (вызов обработчика исключительных
ситуаций) или GOTO (безусловный переход). Например,
 
 LOOP
 последовательность команд;
 IF условие THEN EXIT;
 END LOOP;
 
приведет к выходу из цикла после выполнения последовательности команд, как
только условие станет истинным.
 Цикл WHILE предназначен для повторения последовательности команд, пока
условие остается истинным:
 
 WHILE условие LOOP
 последовательность команд;
 END LOOP;
 
 Наиболее распространен цикл FOR, имеющий следующий синтаксис:
 
 FOR индекс IN [REVERSE] нижняя_граница..верхняя_граница LOOP
 последовательность команд;
 END LOOP;
 
 Здесь индекс (счетчик циклов) изменяется от нижней до верхней границы с
шагом 1, а при использовании "REVERSE" - от верхней до нижней границы с шагом
-1. Например,
 
 FOR i IN 1..3 LOOP -- для i = 1, 2, 3
 последовательность команд; -- цикл выполняется 3 раза
 END LOOP;
 
 FOR i IN REVERSE 1..3 LOOP -- для i = 3, 2, 1
 последовательность команд; -- цикл выполняется 3 раза
 END LOOP;
 
  Отметим, что в последнем случае пределы диапазона указываются в возрастающем,
а не убывающем порядке.
 Если нижняя граница равна верхней, последовательность выполняется один раз.
Если нижняя граница больше верхней, последовательность не выполняется, и
управление переходит к следующему за циклом оператору.
 Пределы диапазона цикла могут быть литералами, переменными или выражениями,
но должны быть целыми числами. Например, допустимы следующие диапазоны:
 
 j IN -5..5
 k IN REVERSE first..last
 step IN 0..TRUNC(high/low) * 2
 code IN ASCII('A')..ASCII('J')
 
 Объявлять индекс не нужно - он объявлен неявно как локальная переменная типа
integer.
 PL/SQL позволяет определять диапазон цикла динамически во время выполнения.
Например:
 
 SELECT COUNT(otdel) INTO shtat_count FROM shtat;
 FOR i IN 1..shtat_count LOOP
 ...
 END LOOP;
 
 Значение "shtat_count" - неизвестно во времени компиляции; предложение SELECT
определяет это значение во время выполнения.
 Индекс может использоваться в выражениях внутри цикла, но не может изменяться.
Например:
 
 FOR ctr IN 1..10 LOOP
 ...
 IF NOT finished THEN
 INSERT INTO ... VALUES (ctr, ...); -- правильно
 factor := ctr * 2; -- правильно
 ...
 ELSE
 ctr := 10; -- неправильно
 END IF;
 END LOOP;
 
 Индекс определен только внутри цикла и на него нельзя ссылаться снаружи
цикла. После выполнения цикла индекс неопределен. Например:
 
 FOR ctr IN 1..10 LOOP
 ...
 END LOOP;
 sum := ctr - 1; -- неверно
 
 Подобно PL/SQL блокам, циклы могут быть помечены. Метка устанавливается в
начале оператора LOOP, следующим образом:
 
 <>
 LOOP
 последовательность команд;
 END LOOP;
 
 Имя метки может также появляться в конце утверждения LOOP как в примере:
 <>
 LOOP
 ...
 END LOOP my_loop;
 
 Помеченные циклы используются для улучшения чтения программы (разборчивости).
 С любой формой утверждения EXIT можно завершать не только текущий цикл, но и
любой внешний цикл. Для этого маркируйте внешний цикл, который надо завершить,
и используйте метку в утверждении EXIT, следующим образом:
 
 <>
 LOOP
 ...
 LOOP
 ...
 EXIT outer WHEN ... -- завершаются оба цикла
 END LOOP;
 ...
 END LOOP outer;
 
 Если требуется преждевременно выйти из вложенного цикла FOR, маркируйте
цикл и используйте метку в утверждении EXIT. Например:
 
 <>
 FOR i IN 1..5 LOOP
 ...
  FOR j IN 1..10 LOOP
 FETCH s1 INTO ShRec;
 EXIT outer WHEN s1%NOTFOUND; -- завершаются оба цикла
 ...
 END LOOP;
 END LOOP outer;
 -- управление передается сюда
 
 4.4. Операторы EXIT, EXIT-WHEN и NULL
 EXIT используется для завершения цикла, когда дальнейшая обработка
нежелательна или невозможна. Внутри цикла можно помещать один или большее
количество операторов EXIT. Имеются две формы EXIT: EXIT и EXIT-WHEN.
 По оператору EXIT цикл завершается немедленно и управление переходит к
следующему за END LOOP оператору. Например:
 
 LOOP
 ...
 IF ... THEN
 ...
 EXIT; -- цикл завершается немедленно
 END IF;
 END LOOP;
 -- управление переходит сюда
 
 По оператору EXIT-WHEN цикл завершиться только в том случае, когда становится
истинным условие в предложении WHEN. Например:
 
 LOOP
 FETCH s1 INTO ...
 EXIT WHEN s1%NOTFOUND; -- конец цикла, если условие верно
 ...
 END LOOP;
 CLOSE s1;
 
 Оператор EXIT-WHEN позволяет завершать цикл преждевременно. Например,
следующий цикл обычно выполняется десять раз, но как только не находится
значение s1, цикл завершается независимо от того сколько раз цикл выполнился.
 
 FOR j IN 1..10 LOOP
 FETCH s1 INTO ShRec;
 EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки
 ...
 END LOOP;
 
 NULL - пустой оператор; он передает управление к следующему за ним оператору.
Однако, к нему может передаваться управление и его наличие часто улучшает
читаемость программы. Он также полезен для создания фиктивных подпрограмм для
резервирования областей определения функций и процедур при отладке программ.
 
 
 5. Обработка ошибок
 Нельзя создать приложение, которое будет безошибочно работать в любых
ситуациях: возможны аппаратные сбои, невыявленные ошибки приложения и ошибки
из-за некорректных действий пользователей приложения (клиентов). Если при этом
программная ошибка произошла в блоке PL/SQL, вложенном в другой блок, а тот,
в свою очередь, вложен в третий блок и т.д., то она может дойти до клиентского
приложения. Чтобы устранить возможную отмену большого объема ранее выполненных
операций и трафик из-за возвращаемых клиенту ошибок, чтобы посылать клиенту
точные сообщения о причине ошибки и способе ее устранения (если она все же
дошла до клиента), разработчики приложения должны предусматривать возможные
программные ошибки и создавать процедуры, адекватно реагирующие на них.
 В PL/SQL предусмотрен механизмы перехвата и обработки ошибок, возникающих
при выполнении программы. Эти механизмы называются исключительными ситуациями.
Когда программа обнаруживает заданное условие ошибки, то вызывается
соответствующая исключительная ситуация. Обработки исключительных ситуаций в
программе производится в разделе EXCEPTION (см. п. 1).
 При обнаружении исключительной ситуации, обработка основного тела программы
останавливается и управление передается соответствующему обработчику
исключительной ситуации, который определяет дальнейшие действия.
 В PL/SQL используются следующие типы исключительных ситуаций:
 - встроенные исключительные ситуации;
 - исключительные ситуации, определяемые пользователем;
 - обработчик OTHERS.
 
 5.1. Встроенные исключительные ситуации
 Oracle включает четырнадцать встроенных исключительных ситуаций,
соответствующих типовым ошибкам, приведенным в следующей таблице:
  Ошибка
Исключительная ситуация ORACLE Описание
---------------------- --------- ---------------------------------------------
CURSOR_ALREADY_OPEN ORA-06511 Попытка открытия уже открытого курсора
DUP_VAL_ON_INDEX ORA-00001 Попытка вставить дубликат значения для
  уникального индекса
INVALID_CURSOR ORA-01001 Попытка выполнения запрещенной операции с кур-
 сором (например, закрытие неоткрытого курсора)
INVALID_NUMBER ORA-01722 Отказ преобразования строки символов в число
LOGIN_DENIED ORA-01017 Неправильное имя пользователь/пароль
NO_DATA_FOUND ORA-01403 Предложение SELECT...INTO возвращает ноль строк
NOT_LOGGED_ON ORA-01012 Нет подключения к Oracle7
PROGRAM_ERROR ORA-06501 Внутренняя ошибка PL/SQL
STORAGE_ERROR ORA-06500 Пакет PL/SQL вышел из пределов памяти или если
 память разрушена
TIMEOUT_ON_RESOURCE ORA-00051 Истекло время ожидания ресурса Oracle7
TOO_MANY_ROWS ORA-01422 Предложение SELECT...INTO возвращает более
 одной строки
TRANSACTION_BACKED_OUT ORA-00061 Удаленный сервер отменил транзакцию
VALUE_ERROR ORA-06502 Арифметическая ошибка, ошибка преобразования,
  усечения или ограничения
ZERO_DIVIDE ORA-01476 Попытка деления на ноль
 
 Если в раздел EXCEPTION программы (блока) включена фраза
 
 WHEN имя_исключения THEN
 текст_обработчика_исключения;
 
с именем какого-либо встроенного исключения и возникла соответствующая ошибка,
то вместо прекращения исполнения программы и выдачи типового сообщения об
ошибке, будет исполняться созданный пользователем текст обработчика исключения.
Такой обработчик может, например, выяснить ситуацию, при которой произошло
деление на ноль, и выдать правдоподобный результат операции деления или
прервать исполнение программы и дать сообщение об изменении каких-либо данных.
В последнем случае это может быть не типовое сообщение "Вы пытаетесь делить на
ноль", а любое подготовленное пользователем сообщение, например, инструкцию
длиной до 2048 символов.
 
 Для выдачи сообщения об ошибке, обеспечения возврата в среду, из которой
вызывалась текущая программа (блок) и отмены всех действий, выполненных в
текущей транзакции, целесообразно использовать процедуру
 
 RAISE_APPLICATION_ERROR(errnum,errtext);
 
где errnum - отрицательное целое число в диапазоне -20000 .. -20999 и errtext
 - символьная строка длиной до 2048 символов.
 В приведенном ниже триггере "shtins" использованы два типа встроенных
исключительных ситуаций: NO_DATA_FOUND и TOO_MANY_ROWS.
 
DROP TRIGGER shtins;
CREATE TRIGGER shtins
BEFORE INSERT ON SHTAT
FOR EACH ROW
DECLARE
 nach DATE;
 kon DATE;
 str NUMBER;
 minraz  NUMBER;
 maxraz NUMBER;
 nach_kon EXCEPTION;
 err_str EXCEPTION;
 nach_nach EXCEPTION;
 err_razr EXCEPTION;
 err_razr_pr EXCEPTION;
 err_stavka EXCEPTION;
BEGIN
 SELECT min_razr,max_razr INTO minraz,maxraz FROM dolgnosti
 WHERE dolgn = :new.dolgn;
 IF :new.razr NOT BETWEEN minraz AND maxraz THEN RAISE err_razr; END IF;
 IF :new.razr_proc NOT BETWEEN 50 AND 100 THEN RAISE err_razr_pr; END IF;
 IF :new.stavka NOT BETWEEN 0.25 AND 100 THEN RAISE err_stavka; END IF;
 IF :new.nachalo > :new.konec THEN RAISE nach_kon; END IF;
 SELECT MAX(stroka) INTO str FROM shtat;
 IF :new.stroka <> str+1 THEN RAISE err_str; END IF;
 <> -- метка блока, в котором производится поиск строк с
  -- параметрами, аналогичными вводимым значениям
 BEGIN
 SELECT nachalo,konec INTO nach,kon FROM shtat
 WHERE OTDEL = :new.otdel AND DOLGN = :new.dolgn AND RAZR = :new.razr
 AND RAZR_PROC = :new.razr_proc AND KONEC =
  (SELECT MAX(konec) FROM shtat
 WHERE OTDEL = :new.otdel AND DOLGN = :new.dolgn
 AND RAZR = :new.razr AND RAZR_PROC = :new.razr_proc);
 IF :new.nachalo <= nach THEN RAISE nach_nach; END IF;
 IF :new.nachalo <= kon THEN
 UPDATE SHTAT SET konec = (:new.nachalo - 1)
 WHERE OTDEL =:new.otdel AND DOLGN = :new.dolgn AND RAZR =:new.razr
 AND RAZR_PROC = :new.RAZR_PROC AND konec = kon;
 END IF;
 EXCEPTION -- начало обработчика исключений блока find_strings
 WHEN NO_DATA_FOUND THEN NULL; -- вызывается, если SELECT блока find_strings
 -- не возвращает ни одной строки.
 END find_strings;
EXCEPTION -- начало обработчика исключений основной программы
 WHEN NO_DATA_FOUND THEN
 RAISE_APPLICATION_ERROR(-20003,'Должности '||:new.dolgn||' не существует !');
 WHEN err_razr THEN
 RAISE_APPLICATION_ERROR(-20004,
 'Значение разряда не попадает в "вилку" разрядов');
 WHEN err_razr_pr THEN
 RAISE_APPLICATION_ERROR(-20005,
 'Разрядный процент должен находиться в пределах 50-100');
 WHEN err_stavka THEN
 RAISE_APPLICATION_ERROR(-20006,
  'Число ставок должно находиться в пределах 0.25-100');
 WHEN nach_nach THEN
 RAISE_APPLICATION_ERROR(-20007,
 'Дата начала должна быть больше '||to_char(nach));
 WHEN TOO_MANY_ROWS THEN
 RAISE_APPLICATION_ERROR(-20008,'Много строк; обратитесь к АБД.');
 WHEN nach_kon THEN
 RAISE_APPLICATION_ERROR(-20009,
 'Дата начала не может быть больше даты конца');
 WHEN err_str THEN
 RAISE_APPLICATION_ERROR(-20010,'Неправильный номер вводимой строки');
 WHEN OTHERS THEN
 RAISE_APPLICATION_ERROR(-20011,'Какая-то другая ошибка');
END shtins;
 
 Так как в большом приложении могут часто повторяться встроенные или
пользовательские исключительные ситуации, то целесообразно создать в базе
данных таблицу (например, USERERR) с уникальными номерами (error_number) и
текстами (error_text) исключений. Это позволит избежать определения лишних
сообщений об ошибках и сделать их согласованными во всем приложении.
 При использовании такой таблицы и процедуры RAISE_APPLICATION_ERROR надо
описать в в разделе DECLARE блока две переменных (например, errnum типа NUMBER
и errtext типа VARCHAR2) и использовать в обработчике исключений конструкцию:
 
 WHEN TOO_MANY_ROWS THEN
 SELECT error_number,error_text INTO errnum,errtext FROM usererr
 WHERE error_number = 20008;
 RAISE_APPLICATION_ERROR(errnum,errtext);
 
или
 
 WHEN TOO_MANY_ROWS THEN
 SELECT error_number,error_text INTO errnum,errtext FROM usererr
 WHERE errtext LIKE 'Много строк; обр%';
 RAISE_APPLICATION_ERROR(errnum,errtext);
 
 5.2. Исключительные ситуации, определяемые пользователем
 Кроме встроенных могут быть использованы собственные исключительные
ситуации, имена которых необходимо описать в разделе DECLARE блока PL/SQL
(например, err_stavka EXCEPTION). В разделе EXCEPTION блока должен быть
описан соответствующий обработчик исключительной ситуации, например
 
 WHEN err_stavka THEN
 SELECT error_number,error_text INTO errnum,errtext FROM usererr
 WHERE errtext LIKE 'Число ставок должно находиться%';
 RAISE_APPLICATION_ERROR(errnum,errtext);
 
 В теле основной программы определяемые пользователем ошибки обычно проверяются
с помощью операторов условия (IF...THEN). Для передачи управления обработчику
пользовательской исключительной ситуации в случае обнаружения ошибки
используется оператор
 
 RAISE имя_пользовательского_исключения
 
Например
 
 IF :new.stavka NOT BETWEEN 0.25 AND 100 THEN
 RAISE err_stavka;
 END IF;
 
 5.3. Обработчик OTHERS
 Если исключительная ситуация не обрабатывается явным образом в блоке и для
ее перехвата не используется обработчик OTHERS, то PL/SQL отменяет выполняемые
блоком транзакции и возвращает необработанную исключительную ситуацию обратно
в вызывающую среду.
 Обработчик особых ситуаций OTHERS описывается последним в программе (блоке)
для перехвата всех исключительных ситуаций, которые не были описаны в этой
программе (блоке). Он может иметь вид
 
 WHEN OTHERS THEN
 RAISE_APPLICATION_ERROR(-20011,'Какая-то другая ошибка');