Кроме того, в нем выражению "(TO_CHAR(razr)||' '||imya_dolg)" дан псевдоним "razr_dolg", использованный во фразе ORDER BY. Oracle рекомендует создавать псевдонимы для всех выражений фразы SELECT с целью ссылки на них при работе с курсором. 12.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. 12.3.1.3. Выборка строк из курсора (FETCH) Команда FETCH, используемая для продвижения на один шаг указателя текущей строки курсора и пересылки ее значений в переменные или запись, имеет следующий синтаксис: FETCH cursor_name INTO {variable_name1[,variable_name2]...} | record_name ; Для каждого значения столбца, возвращенного запросом, в списке INTO должна иметься переменная или поле записи соответствующего типа данных. Такие  - 31 - переменные или записи должны быть заранее описаны в декларативной части блока 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; -- выход при отсутствии возвращаемой строки -- (см. п. 12.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-списка будут неопределены.  - 32 - 12.3.1.4. Закрытие курсора (CLOSE) Команда CLOSE используется для освобождения всех ресурсов, которые поддерживались открытым курсором (при этом описание курсора сохраняется и его можно снова открыть командой OPEN). Синтаксис команды CLOSE имеет вид: CLOSE cursor_name; 12.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; 12.3.1.6. Атрибуты явного курсора Для анализа состояния курсора используются специальные переменные, имена которых составляются из имени курсора и суффиксов %FOUND, %NOTFOUND, %ROWCOUNT и %ISOPEN, называемых атрибутами курсора. Если курсор назван "cursor_name", то эти переменные имеют имена: cursor_name%NOTFOUND, cursor_nane%FOUND, cursor_nane%ROWCOUNT и cursor_nane%ISOPEN.  - 33 - Значения таких переменных анализируются при выполнении программы с помощью различных операторов управления (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 был рассмотрен в п. 12.3.1.3. Переменная с атрибутом %ROWCOUNT содержит количество строк, выбранных из курсора на текущий момент (при открытии курсора эта переменная содержит ноль). В следующем примере переменная s1%ROWCOUNT ограничивает выборку из курсора s1 десятью строками: LOOP FETCH s1 INTO sh_raz,sh_stav; IF s1%ROWCOUNT > 10 THEN ... END IF; ... END LOOP; 12.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 ] ... ; в которой следует привести список обновляемых столбцов.  - 34 - 12.3.2. Неявный курсор (SQL курсор) Для всех команд языка SQL, не связанных с объявлением курсора ("явным курсором"), PL/SQL открывает курсор ("неявный курсор"), на который можно ссылаться по курсорному имени SQL%. При работе с таким курсором нельзя использовать команды OPEN, FETCH и CLOSE, но можно использовать атрибуты курсора, чтобы получить информацию о текущем его состоянии. 12.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. 12.3.2.2. UPDATE, DELETE и INSERT Эти предложения отличаются от аналогичных предложений интерактивного SQL лишь тем, что в их выражениях (expr) могут использоваться переменные PL/SQL. 12.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;  - 35 - 12.4. Операторы управления выполнением программы 12.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. Все это справедливо, если внутри последовательности команд нет операторов, осуществляющих переход за пределы этой последовательности. 12.4.2. Метки и оператор безусловного перехода (GOTO) В любом месте программы может быть поставлена метка, имеющая синтаксис: <<имя_метки>> Оператор GOTO позволяет осуществить безусловный переход к метке, имя которой должно быть уникальным внутри программы или блока PL/SQL. Например, управление передается вниз к помеченному оператору: BEGIN ... GOTO insert_row; ... <> INSERT INTO shtat VALUES ... END;  - 36 - В следующем примере управление передается вверх к помеченной последовательности операторов: BEGIN ... <> BEGIN UPDATE shtat SET ... ... END; ... GOTO update_row; ... END; Следует отметить, что использование GOTO (особенно в тех случаях, когда метка предшествует оператору GOTO) может привести к сложным, нераспознаваемым кодам ошибок, которые трудно обрабатывать. Поэтому реже используйте GOTO, тем более что этот оператор нельзя использовать для выполнения перехода: - в IF-блок, LOOP-блок или в другой блок, не включающий текущий; - из одного предложения IF-оператора к другому; - из внешнего блока в SUB-блок; - из обработчика особых ситуаций в текущий блок. 12.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;  - 37 - Здесь индекс (счетчик циклов) изменяется от нижней до верхней границы с шагом 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; -- неверно  - 38 - Подобно 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; -- управление передается сюда 12.4.4. Операторы EXIT, EXIT-WHEN и NULL EXIT используется для завершения цикла, когда дальнейшая обработка нежелательна или невозможна. Внутри цикла можно помещать один или большее количество операторов EXIT. Имеются две формы EXIT: EXIT и EXIT-WHEN. По оператору EXIT цикл завершается немедленно и управление переходит к следующему за END LOOP оператору. Например: LOOP ... IF ... THEN ... EXIT; -- цикл завершается немедленно END IF; END LOOP; -- управление переходит сюда  - 39 - По оператору 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 - пустой оператор; он передает управление к следующему за ним оператору. Однако, к нему может передаваться управление и его наличие часто улучшает читаемость программы. Он также полезен для создания фиктивных подпрограмм для резервирования областей определения функций и процедур при отладке программ. 12.5. Обработка ошибок Нельзя создать приложение, которое будет безошибочно работать в любых ситуациях: возможны аппаратные сбои, невыявленные ошибки приложения и ошибки из-за некорректных действий пользователей приложения (клиентов). Если при этом программная ошибка произошла в блоке PL/SQL, вложенном в другой блок, а тот, в свою очередь, вложен в третий блок и т.д., то она может дойти до клиентского приложения. Чтобы устранить возможную отмену большого объема ранее выполненных операций и трафик из-за возвращаемых клиенту ошибок, чтобы посылать клиенту точные сообщения о причине ошибки и способе ее устранения (если она все же дошла до клиента), разработчики приложения должны предусматривать возможные программные ошибки и создавать процедуры, адекватно реагирующие на них. В PL/SQL предусмотрен механизмы перехвата и обработки ошибок, возникающих при выполнении программы. Эти механизмы называются исключительными ситуациями. Когда программа обнаруживает заданное условие ошибки, то вызывается соответствующая исключительная ситуация. Обработки исключительных ситуаций в программе производится в разделе EXCEPTION (см. п. 12.1). При обнаружении исключительной ситуации, обработка основного тела программы останавливается и управление передается соответствующему обработчику исключительной ситуации, который определяет дальнейшие действия. В PL/SQL используются следующие типы исключительных ситуаций: - встроенные исключительные ситуации; - исключительные ситуации, определяемые пользователем; - обработчик OTHERS. 12.5.1. Встроенные исключительные ситуации Oracle включает четырнадцать встроенных исключительных ситуаций, соответствующих типовым ошибкам, приведенным в следующей таблице: