Краткий справочник по Oracle7

Кроме того, в нем выражению "(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 включает четырнадцать встроенных исключительных ситуаций,
соответствующих типовым ошибкам, приведенным в следующей таблице: