Циклы в PL/SQL: LOOP, FOR, WHILE и CONTINUE

Циклы в PL/SQL: LOOP, FOR, WHILE и CONTINUE

Циклы For, While, Loop и continue в PL/SQLВ этом блоге я расскажу Вам об управляющих структуры PL/SQL, называемых циклами и предназначенных для многократного выполнения программного кода. Также мы рассмотрим команду CONTINUE, появившуюся в Oracle 11g. PL/SQL поддерживает циклы трех видов: простые LOOP (бесконечные), FOR и WHILE. Каждая разновидность циклов предназначена для определенных целей, имеет свои нюансы и правила использования. Из представленных ниже таблиц вы узнаете, как завершается цикл, когда проверяется условие его завершения и в каких случаях применяются циклы того или иного вида.



Свойство Описание
Условие завершения цикла Код выполняется многократно. Как остановить выполнение тела цикла?
Когда проверяется условие завершения цикла Когда выполняется проверка условия завершения — в начале или в конце цикла? К каким последствиям это приводит?
В каких случаях используется данный цикл Какие специальные факторы необходимо учитывать, если цикл подходит для вашей ситуации?

 

Основы циклов языка PL/SQL

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

 

Примеры разных циклов

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

Простой цикл начинается с ключевого слова LOOP и завершается командой END LOOP. Выполнение цикла прерывается при выполнении команды EXIT, EXIT WHEN или RETURN в теле цикла (или при возникновении исключения): 

PROCEDURE display_multiple_years (
   start_year_in IN PLS_INTEGER
   ,end_year_in IN PLS_INTEGER
   )
IS
   l_current_year PLS_INTEGER := start_year_in;
BEGIN
   LOOP
      EXIT WHEN l_current_year > end_year_in;
      display_total_sales (l_current_year);
      l_current_year := l_current_year + 1;
   END LOOP;
END display_multiple_years;

Цикл FOR существует в двух формах: числовой и курсорной. В числовых циклах FOR программист задает начальное и конечное целочисленные значения, а PL/SQL перебирает все промежуточные значения, после чего завершает цикл:

PROCEDURE display_multiple_years (
   start_year_in IN PLS_INTEGER
   ,end_year_in IN PLS_INTEGER
   )
IS
BEGIN
   FOR l_current_year IN start_year_in .. end_year_in
   LOOP
      display_total_sales (l_current_year);
   END LOOP;
END display_multiple_years;

Курсорная форма цикла FOR имеет аналогичную базовую структуру, но вместо границ числового диапазона в ней задается курсор или конструкция SELECT:

PROCEDURE display_multiple_years (
   start_year_in IN PLS_INTEGER
   ,end_year_in IN PLS_INTEGER
)
IS
BEGIN
   FOR l_current_year IN (
      SELECT * FROM sales_data
      WHERE year BETWEEN start_year_in AND end_year_in)
   LOOP
      -- Процедуре передается запись, неявно объявленная
      -- с типом sales_data%ROWTYPE...
      display_total_sales (l_current_year);
   END LOOP;
END display_multiple_years; 

Цикл WHILE имеет много общего с простым циклом. Принципиальное отличие заключается в том, что условие завершения проверяется перед выполнением очередной итерации. Возможны ситуации, в которых тело цикла не будет выполнено ни одного раза: 

PROCEDURE display_multiple_years (
   start_year_in IN PLS_INTEGER
   ,end_year_in IN PLS_INTEGER
)
IS
   l_current_year PLS_INTEGER := start_year_in;
BEGIN
   WHILE (l_current_year <= end_year_in)
   LOOP
      display_total_sales (l_current_year);
      l_current_year := l_current_year + 1;
   END LOOP;
END display_multiple_years;

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

 

Структура циклов PL/SQL

Несмотря на различия между разными формами циклических конструкций, каждый цикл состоит из двух частей: ограничителей и тела цикла.

Ограничители — ключевые слова, определяющие начало цикла, условие завершения, и команда END LOOP, завершающая цикл. Тело цикла — последовательность исполняемых команд внутри границ цикла, выполняемых на каждой итерации.

На рис. 1 изображена структура цикла WHILE.

 Цикл WHILE языка PL/SQL

Рис. 1. Цикл WHILE и его тело

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

 

Простой цикл PL/SQL

Структура простого цикла является самой элементарной среди всех циклических конструкций. Такой цикл состоит из ключевого слова LOOP, исполняемого кода (тела цикла) и ключевых слов END LOOP

LOOP
   исполняемые_команды
END LOOP;

Цикл начинается командой LOOP, а заканчивается командой END LOOP. Тело цикла должно содержать как минимум одну исполняемую команду. Свойства простого цикла описаны в следующей таблице.

Свойство Описание
Условие завершения цикла Если в теле цикла выполняется команда EXIT. В противном случае цикл выполняется бесконечно
Когда проверяется условие завершения цикла В теле цикла и только при выполнении команды EXIT или EXIT WHEN. Таким образом, тело цикла (или его часть) всегда выполняется как минимум один раз
В каких случаях используется данный цикл (1) Если не известно, сколько раз будет выполняться тело цикла; (2) тело цикла должно быть выполнено хотя бы один раз

Простой цикл удобно использовать, когда нужно гарантировать хотя бы однократное выполнение тела цикла (или хотя бы его части). Так как цикл не имеет условия, которое бы определяло, должен ли он выполняться или нет, тело цикла всегда будет выполнено хотя бы один раз.

Простой цикл завершается только в том случае, если в его теле выполняется команда EXIT (или ее «близкий родственник» — EXIT WHEN) или же в нем инициируется исключение (оставшееся необработанным).

 

Завершение простого цикла: EXIT и EXIT WHEN

Если вы не хотите, чтобы программа «зациклилась», в теле цикла следует разместить команду EXIT или EXIT WHEN:

EXIT;
EXIT WHEN условие;

Здесь условие — это логическое выражение.

В следующем примере команда EXIT прерывает выполнение цикла и передает управление команде, следующей за командой END LOOP. Функция account_balance возвращает остаток денег на счету с идентификатором account_id. Если на счету осталось менее 1000 долларов, выполняется команда EXIT и цикл завершается. В противном случае программа снимает с банковского счета клиента сумму, необходимую для оплаты заказов.

LOOP
   balance_remaining := account_balance (account_id);
   IF balance_remaining < 1000
   THEN
      EXIT;
   ELSE
      apply_balance (account_id, balance_remaining);
   END IF;
END LOOP;

Команда EXIT может использоваться только в цикле LOOP. В PL/SQL для выхода из цикла также предусмотрена команда EXIT WHEN, предназначенная для завершения цикла с проверкой дополнительного условия. В сущности, EXIT WHEN сочетает в себе функции IF-THEN и EXIT. Приведенный пример можно переписать с использованием EXIT WHEN

LOOP
   /* Вычисление баланса */
   balance_remaining := account_balance (account_id);
   /* Условие встраивается в команду EXIT */
EXIT WHEN balance_remaining < 1000;
   /* Если цикл все еще выполняется, с баланса списываются средства */
   apply_balance (account_id, balance_remaining);
END LOOP;

Как видите, во второй форме для проверки условия завершения команда IF не нужна. Логика проверки условия встраивается в команду EXIT WHEN. Так в каких же случаях следует использовать команду EXIT WHEN, а в каких — просто EXIT?

  • Команда EXIT WHEN подойдет, когда условие завершения цикла определяется одним выражением. Предыдущий пример наглядно демонстрирует этот сценарий.
  • При нескольких условиях завершения цикла или если при выходе должно быть определено возвращаемое значение, предпочтительнее IF или CASE с EXIT.

В следующем примере удобнее использовать команду EXIT. Фрагмент кода взят из функции, сравнивающей содержимое двух файлов:

   ...
   IF (end_of_file1 AND end_of_file2)
   THEN
      retval := TRUE;
      EXIT;
   ELSIF (checkline != againstline)
   THEN
      retval := FALSE;
      EXIT;
   ELSIF (end_of_file1 OR end_of_file2)
   THEN
      retval := FALSE;
      EXIT;
   END IF;
END LOOP;

 

Моделирование цикла REPEAT UNTIL

В PL/SQL отсутствует традиционный цикл REPEAT UNTIL, в котором условие проверяется после выполнения тела цикла (что гарантирует выполнение тела как минимум один раз). Однако этот цикл легко моделируется простым циклом следующего вида:

LOOP
   ... тело цикла ...
   EXIT WHEN логическое_условие;
END LOOP; 

Здесь логическое_условие — логическая переменная или выражение, результатом проверки которого является значение TRUE или FALSE (или NULL).

 

Бесконечный цикл

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

LOOP
   сбор_данных;
END LOOP;

Но каждый программист, имевший дело с зацикливанием, подтвердит: бесконечный цикл обычно поглощает значительную часть ресурсов процессора. Проблема решается приостановкой выполнения между итерациями (и, разумеется, максимально возможной эффективностью сбора данных):

LOOP
   сбор_данных;
   DBMS_LOCK.sleep(10); -- ничего не делать в течение 10 секунд
END LOOP;

Во время приостановки программа практически не расходует ресурсы процессора.

 

Цикл WHILE

Условный цикл WHILE выполняется до тех пор, пока определенное в цикле условие остается равным TRUE. А поскольку возможность выполнения цикла зависит от условия и не ограничивается фиксированным количеством повторений, он используется именно в тех случаях, когда количество повторений цикла заранее не известно.

Прерывание бесконечного цикла

На практике возможна ситуация, в которой бесконечный цикл потребуется завершить. Если цикл выполняется в анонимном блоке в SQL*Plus, скорее всего, проблему можно решить вводом терминальной комбинации завершения (обычно Ctrl+C). Но реальные программы чаще выполняются в виде сохраненных процедур, и даже уничтожение процесса, запустившего программу (например, SQL*Plus), не приведет к остановке фоновой задачи. Как насчет команды ALTER SYSTEM KILL SESSION? Хорошая идея, но в некоторых версиях Oracle эта команда не уничтожает зациклившиеся сеансы (почему — никто не знает). Как же «прикончить» выполняемую программу?

Возможно, вам придется прибегнуть к таким средствам операционной системы, как команда kill в Unix/Linux и orakill.exe в Microsoft Windows. Для выполнения этих команд необходимо знать идентификатор процесса «теневой задачи» Oracle; впрочем, нужную информацию легко получить при наличии привилегий чтения для представлений V$SESSION и V$PROCESS. Но даже если неэлегантное решение вас не пугает, приходится учитывать другой фактор: в режиме сервера это, вероятно, приведет к уничтожению других сеансов. Лучшее решение, которое я могу предложить, — вставить в цикл своего рода «интерпретатор команд», использующий встроенный в базу данных механизм межпроцессных коммуникаций — «каналов» (pipes): 

DECLARE
   pipename CONSTANT VARCHAR2(12) := 'signaler';
   result INTEGER;
   pipebuf VARCHAR2(64);
BEGIN
   /* Создание закрытого канала с известным именем */
   result := DBMS_PIPE.create_pipe(pipename);
   LOOP
      data_gathering_procedure;
      DBMS_LOCK.sleep(10);
      /* Проверка сообщений в канале */
      IF DBMS_PIPE.receive_message(pipename, 0) = 0
      THEN
         /* Интерпретация сообщения с соответствующими действиями */
         DBMS_PIPE.unpack_message(pipebuf);
         EXIT WHEN pipebuf = 'stop';
      END IF;
   END LOOP;
END;

Использование DBMS_PIPE не оказывает заметного влияния на общую загрузку процессора.

Простая вспомогательная программа может уничтожить зациклившуюся программу, отправив по каналу сообщение «stop»:

DECLARE
   pipename VARCHAR2 (12) := 'signaler';
   result INTEGER := DBMS_PIPE.create_pipe (pipename);
BEGIN
   DBMS_PIPE.pack_message ('stop');
   result := DBMS_PIPE.send_message (pipename);
END;

По каналу также можно отправлять другие команды — например, команду увеличения или уменьшения интервала ожидания. Кстати говоря, в приведенном примере используется закрытый канал, так что сообщение STOP должно отправляться с той же учетной записи пользователя, которая выполняет бесконечный цикл. Также следует заметить, что пространство имен базы данных для закрытых каналов глобально по отношению ко всем сеансам текущего пользователя. Следовательно, если вы захотите, чтобы в бесконечном цикле выполнялось сразу несколько программ, необходимо реализовать дополнительную логику для (1) создания имен каналов, уникальных для каждого сеанса, и (2) определения имен каналов для отправки команды STOP.

Работаем с циклами  For, While, Loop и continue в PL/SQL на примерах 

Общий синтаксис цикла WHILE:

WHILE условие
LOOP
   исполняемые_команды
END LOOP;

Здесь условие — логическая переменная или выражение, результатом проверки которого является логическое значение TRUE, FALSE или NULL. Условие проверяется при каждой итерации цикла. Если результат оказывается равным TRUE, тело цикла выполняется.

Если же результат равен FALSE или NULL, то цикл завершается, а управление передается исполняемой команде, следующей за командой END LOOP. Основные свойства цикла WHILE приведены в таблице.

Свойство Описание
Условие завершения цикла Если значением логического выражения цикла является FALSE или NULL
Когда проверяется условие завершения цикла Перед первым и каждым последующим выполнением тела цикла. Таким образом, не гарантируется даже однократное выполнение тела цикла WHILE
В каких случаях используется данный цикл (1) Если не известно, сколько раз будет выполняться тело цикла; (2) возможность выполнения цикла должна определяться условием; (3) тело цикла может не выполняться ни одного раза

Условие WHILE проверяется в начале цикла и в начале каждой его итерации, перед выполнением тела цикла. Такого рода проверка имеет два важных последствия:

  • Вся информация, необходимая для вычисления условия, должна задаваться перед первым выполнением цикла.
  • Может оказаться, что цикл WHILE не будет выполнен ни одного раза.

Следующий пример цикла WHILE взят из файла datemgr.pkg. Здесь используется условие, представленное сложным логическим выражением. Прерывание цикла WHILE вызвано одной из двух причин: либо завершением списка масок даты, которые применяются для выполнения преобразования, либо успешным завершением преобразования (и теперь переменная date_converted содержит значение TRUE): 

WHILE mask_index <= mask_count AND NOT date_converted
LOOP
   BEGIN
      /* Попытка преобразования строки по маске в записи таблицы */
      retval := TO_DATE (value_in, fmts (mask_index));
      date_converted := TRUE;
   EXCEPTION
      WHEN OTHERS
      THEN
         mask_index:= mask_index+ 1;
   END;
END LOOP;

 

Цикл FOR со счетчиком

В PL/SQL существует два вида цикла FOR: с числовым счетчиком и с курсором. Цикл со счетчиком — это традиционный, хорошо знакомый всем программистам цикл FOR, поддерживаемый в большинстве языков программирования. Количество итераций этого цикла известно еще до его начала; оно задается в диапазоне между ключевыми словами FOR и LOOP.

Диапазон неявно объявляет управляющую переменную цикла (если она не была явно объявлена ранее), определяет начальное и конечное значения диапазона, а также задает направление изменения счетчика (по возрастанию или по убыванию).

 

Общий синтаксис цикла FOR:

FOR счетчик IN [REVERSE] начальное_значение .. конечное_значение
LOOP
   исполняемые_команды
END LOOP;

Между ключевыми словами LOOP и END LOOP должна стоять хотя бы одна исполняемая команда. Свойства цикла FOR с числовым счетчиком приведены в следующей таблице.

Свойство Описание
Условие завершения цикла Числовой цикл FOR безусловно завершается при выполнении количества итераций, определенного диапазоном значений счетчика. (Цикл может завершаться и командой EXIT, но делать этого не рекомендуется)
Когда проверяется условие завершения цикла После каждого выполнения тела цикла компилятор PL/SQL проверяет значение счетчика. Если оно выходит за пределы заданного диапазона, выполнение цикла прекращается. Если начальное значение больше конечного, то тело цикла не выполняется ни разу
В каких случаях используется данный цикл Если тело цикла должно быть выполнено определенное количество раз, а выполнение не должно прерываться преждевременно

 

Правила для циклов FOR с числовым счетчиком

При использовании цикла FOR с числовым счетчиком необходимо следовать некоторым правилам:

  • Не объявляйте счетчик цикла. PL/SQL автоматически неявно объявляет локальную переменную с типом данных INTEGER. Область действия этой переменной совпадает с границей цикла; обращаться к счетчику за пределами цикла нельзя.
  • Выражения, используемые при определении диапазона (начального и конечного значений), вычисляются один раз. Они не пересчитываются в ходе выполнения цикла. Если изменить внутри цикла переменные, используемые для определения диапазона значений счетчика, его границы останутся прежними.
  • Никогда не меняйте значения счетчика и границ диапазона внутри цикла. Это в высшей степени порочная практика. Компилятор PL/SQL либо выдаст сообщение об ошибке, либо проигнорирует изменения — в любом случае возникнут проблемы.
  • Чтобы значения счетчика уменьшались в направлении от конечного к начальному, используйте ключевое слово REVERSE. При этом первое значение в определении диапазона (начальное_значение) должно быть меньше второго (конечное_значение). Не меняйте порядок следования значений — просто поставьте ключевое слово REVERSE.

 

Примеры циклов FOR с числовым счетчиком

Следующие примеры демонстрируют некоторые варианты синтаксиса циклов FOR с числовым счетчиком.

  • Цикл выполняется 10 раз; счетчик увеличивается от 1 до 10:
        FOR loop_counter IN 1 .. 10
        LOOP
           ... исполняемые_команды ...
        END LOOP;
  • Цикл выполняется 10 раз; счетчик уменьшается от 10 до 1:
        FOR loop_counter IN REVERSE 1 .. 10
        LOOP
           ... исполняемые_команды ...
        END LOOP;
  • Цикл не выполняется ни разу. В заголовке цикла указано ключевое слово REVERSE, поэтому счетчик цикла loop_counter изменяется от большего значения к меньшему. Однако начальное и конечное значения заданы в неверном порядке:
        FOR loop_counter IN REVERSE 10 .. 1
        LOOP
           /* Тело цикла не выполнится ни разу! */
           ...
        END LOOP;

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

  • Цикл выполняется для диапазона, определяемого значениями переменной и выражения:
        FOR calc_index IN start_period_number ..
                       LEAST (end_period_number, current_period)
        LOOP
           ... исполняемые команды ...
        END LOOP;

В этом примере количество итераций цикла определяется во время выполнения программы. Начальное и конечное значения вычисляются один раз, перед началом цикла, и затем используются в течение всего времени его выполнения.

 

Нетривиальные приращения

В PL/SQL не предусмотрен синтаксис задания шага приращения счетчика. Во всех разновидностях цикла FOR с числовым счетчиком значение счетчика на каждой итерации всегда увеличивается или уменьшается на единицу.

Если приращение должно быть отлично от единицы, придется писать специальный код. Например, что нужно сделать, чтобы тело цикла выполнялось только для четных чисел из диапазона от 1 до 100? Во-первых, можно использовать числовую функцию MOD, как в следующем примере: 

FOR loop_index IN 1 .. 100
LOOP
   IF MOD (loop_index, 2) = 0
   THEN
      /* Число четное, поэтому вычисления выполняются */
      calc_values (loop_index);
   END IF;
END LOOP;

Также возможен и другой способ — умножить значение счетчика на два и использовать вдвое меньший диапазон:

FOR even_number IN 1 .. 50
LOOP
   calc_values (even_number*2);
END LOOP;

В обоих случаях процедура calc_values выполняется только для четных чисел. В первом примере цикл FOR повторяется 100 раз, во втором — только 50.

Но какое бы решение вы ни выбрали, обязательно подробно его опишите. Комментарии помогут другим программистам при сопровождении вашей программы.

 

Цикл FOR c курсором

Курсорная форма цикла FOR связывается с явно заданным курсором (а по сути, определяется им) или инструкцией SELECT, заданной непосредственно в границах цикла. Используйте эту форму только в том случае, если вам нужно извлечь и обработать все записи курсора (впрочем, при работе с курсорами это приходится делать довольно часто).

Цикл FOR с курсором — одна из замечательных возможностей PL/SQL, обеспечивающая тесную и эффективную интеграцию процедурных конструкций с мощью языка доступа к базам данных SQL. Его применение заметно сокращает объем кода, необходимого для выборки данных из курсора, а также уменьшает вероятность возникновения ошибок при циклической обработке данных — ведь именно циклы являются одним из основных источников ошибок в программах.

Базовый синтаксис цикла FOR с курсором: 

FOR запись IN { имя_курсора | (команда_SELECT) }
LOOP
   исполняемые команды
END LOOP;

Здесь запись — неявно объявленная запись с атрибутом %ROWTYPE для курсора имя_курсора.

Не объявляйте явно запись с таким же именем, как у индексной записи цикла. В этом нет необходимости, поскольку запись объявляется автоматически, к тому же это может привести к логическим ошибкам. О том, как получить доступ к информации о записях, обработанных в цикле FOR после его выполнения, рассказывается далее в этом блоге.

В цикле FOR можно также задать не курсор, а непосредственно SQL-инструкцию SELECT, как показано в следующем примере: 

FOR book_rec IN (SELECT * FROM books)
LOOP
   show_usage (book_rec);
END LOOP;

Мы не рекомендуем использовать эту форму, поскольку встраивание инструкций SELECT в «неожиданные» места кода затрудняет его сопровождение и отладку.

Свойства цикла FOR с использованием курсора приведены в следующей таблице.

Свойство Описание
Условие завершения цикла Выборка всех записей курсора. Цикл можно завершить и командой EXIT, но поступать так не рекомендуется
Когда проверяется условие завершения цикла После каждого выполнения тела цикла компилятор PL/SQL осуществляет выборку очередной записи. Если значение атрибута курсора %NOTFOUND% оказывается равным TRUE, цикл завершается. Если курсор не возвратит ни одной строки, тело цикла никогда не будет выполнено
В каких случаях используется данный цикл При необходимости выбрать и обработать каждую запись курсора

 

Примеры цикла FOR с курсором

Допустим, нам необходимо обновить счета владельцев всех животных, живущих в специальном отеле. Следующий пример включает анонимный блок, в котором для выбора номера комнаты и идентификатора животного используется курсор occupancy_cur. Процедура update_bill вносит все изменения в счет: 

1 DECLARE
2    CURSOR occupancy_cur IS
3    SELECT pet_id, room_number
4      FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);
5    occupancy_rec occupancy_cur%ROWTYPE;
6 BEGIN
7    OPEN occupancy_cur;
8    LOOP
9       FETCH occupancy_cur INTO occupancy_rec;
10      EXIT WHEN occupancy_cur%NOTFOUND;
11      update_bill
12         (occupancy_rec.pet_id, occupancy_rec.room_number);
13    END LOOP;
14    CLOSE occupancy_cur;
15 END;

Этот код последовательно и явно выполняет все необходимые действия: мы определяем курсор (строка 2), явно объявляем запись для этого курсора (строка 5), открываем курсор (строка 7), начинаем бесконечный цикл (строка 8), производим выборку записи из курсора (строка 9), проверяем условие выхода из цикла (конец данных) по атрибуту %NOTFOUND курсора (строка 10) и, наконец, выполняем обновление (строка 11). После этого программист должен закрыть курсор (строка 14). Вот что получится, если переписать тот же код с использованием цикла FOR с курсором: 

DECLARE
   CURSOR occupancy_cur IS
      SELECT pet_id, room_number
        FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);
BEGIN
   FOR occupancy_rec IN occupancy_cur
   LOOP
      update_bill (occupancy_rec.pet_id, occupancy_rec.room_number);
   END LOOP;
END;

Как все просто и понятно! Исчезло объявление записи. Исчезли команды OPEN, FETCH и CLOSE. Больше не нужно проверять атрибут %NOTFOUND. Нет никаких сложностей с организацией выборки данных. По сути, вы говорите PL/SQL: «Мне нужна каждая строка таблицы, и я хочу, чтобы она была помещена в запись, соответствующую курсору». И PL/SQL делает то, что вы хотите, как это должен делать любой современный язык программирования.

Курсору в цикле FOR, как и любому другому курсору, можно передавать параметры. Если какой-либо из столбцов списка SELECT определяется выражением, обязательно определите для него псевдоним. Для обращения к конкретному значению в записи курсора в пределах цикла необходимо использовать «точечный» синтаксис (имя_записи.имя_столбца — например, occupancy_rec.room_number), так что без псевдонима к столбцу-выражению обратиться не удастся.

 

Метки циклов

Циклу можно присвоить имя при помощи метки. Метка цикла в PL/SQL имеет стандартный формат:

 <<имя_метки>>

Метка располагается непосредственно перед командой LOOP:

<<all_emps>>
FOR emp_rec IN emp_cur
LOOP
   ...
END LOOP;

Эту же метку можно указать и после ключевых слов END LOOP, как в следующем примере:

<<year_loop>>
WHILE year_number <= 1995
LOOP
   <<month_loop>>
   FOR month_number IN 1 .. 12
   LOOP
      ...
   END LOOP month_loop;
   year_number := year_number + 1;
END LOOP year_loop;

Метки циклов могут пригодиться в нескольких типичных ситуациях:

  • Если вы написали очень длинный цикл с множеством вложенных циклов (допустим, начинающийся в строке 50, завершается в строке 725 и содержащий 16 вложенных циклов), используйте метку цикла для того, чтобы явно связать его конец с началом. Визуальная пометка поможет при отладке и сопровождении программы; без нее будет трудно уследить, какая команда LOOP соответствует каждой из команд END LOOP.
  • Метку цикла можно использовать для уточнения имени управляющей переменной цикла (записи или счетчика), что также упрощает чтение программы:
       <<year_loop>>
       FOR year_number IN 1800..1995
       LOOP
         <<month_loop>>
         FOR month_number IN 1 .. 12
         LOOP
           IF year_loop.year_number = 1900 THEN ... END IF;
         END LOOP month_loop;
      END LOOP year_loop; 
  • При использовании вложенных циклов метки упрощают чтение кода и повышают эффективность их выполнения. При желании выполнение именованного внешнего цикла можно прервать при помощи команды EXIT с заданной в нем меткой цикла:
        EXIT метка_цикла;
        EXIT метка_цикла WHEN условие;
 

Но обычно применять метки циклов подобным образом не рекомендуется, так как они ухудшают структуру логики программы (по аналогии с GOTO) и усложняют отладку. Если вам потребуется использовать подобный код, лучше изменить структуру цикла, а возможно, заменить его простым циклом или WHILE.

 

Команда CONTINUE

В Oracle11g появилась новая возможность для работы с циклами: команда CONTINUE. Он используется для выхода из текущей итерации цикла и немедленного перехода к следующей итерации. Как и EXIT, эта команда существует в двух формах: безусловной (CONTINUE) и условной (CONTINUE WHEN).

Простой пример использования CONTINUE WHEN для пропуска итераций с четными значениями счетчика: 

BEGIN
   FOR l_index IN 1 .. 10
   LOOP
      CONTINUE WHEN MOD (l_index, 2) = 0;
      DBMS_OUTPUT.PUT_LINE ('Счетчик = ' || TO_CHAR (l_index));
   END LOOP;
END;
/

Результат:

Счетчик = 1
Счетчик = 3
Счетчик = 5
Счетчик = 7
Счетчик = 9

Конечно, того же эффекта можно добиться при помощи команды IF, но команда CONTINUE предоставляет более элегантный и понятный способ представления реализуемой логики.

Команда CONTINUE чаще всего применяется для модификации существующего кода с внесением целенаправленных изменений и немедленным выходом из цикла для предотвращения побочных эффектов.

Так ли плоха команда CONTINUE?

Когда я впервые узнал о команде CONTINUE, на первый взгляд мне показалось, что она представляет очередную форму неструктурированной передачи управления по аналогии с GOTO, поэтому ее следует по возможности избегать (я прекрасно обходился без нее годами!). Чарльз Уэзерелл, один из руководителей группы разработки PL/SQL, развеял мои заблуждения:

Уже давно (еще в эпоху знаменитого манифеста Дейкстры «о вреде goto») конструкции exit и continue были проанализированы и отнесены к структурным средствам передачи управления. Более того, команда exit была признана в одной из авторитетных работ Кнута как способ корректного прерывания вычислений.

Бем и Якопини доказали, что любая программа, использующая произвольные синхронные управляющие элементы (например, циклы или goto), может быть переписана с использованием циклов while, команд if и логических переменных в полностью структурной форме. Более того, преобразование между «плохой» неструктурированной версией и «хорошей» структурированной версией в программе может быть автоматизировано. К сожалению, новая «хорошая» программа может на порядок увеличиваться в размерах из-за необходимости введения многочисленных логических переменных и копирования кода во множественные ветви if. На практике в реальных программах такое увеличение размера встречается редко, но для моделирования эффекта continue и exit часто применяется копирование кода. Оно создает проблемы с сопровождением, потому что если в будущем программу потребуется модифицировать, программист должен помнить, что изменить нужно все копии вставленного кода.

Команда continue полезна тем, что она делает код более компактным и понятным, а также сокращает необходимость в логических переменных, смысл которых трудно понять с первого взгляда. Чаще всего она используется в циклах, в которых точная обработка каждого элемента зависит от подробных структурных тестов. Заготовка цикла может выглядеть так, как показано ниже; обратите внимание на команду exit, которая проверяет, не пора ли завершить обработку. Также стоит заметить, что последняя команда continue (после условия5) не является строго необходимой. С другой стороны, включение continue после каждого действия упрощает добавление новых действий в произвольном порядке без нарушения работоспособности других действий. 

LOOP
   EXIT WHEN условие_выхода;
   CONTINUE WHEN условие1;
   CONTINUE WHEN условие2;
   подготовительная_фаза;
   IF условие4 THEN
      выполнено_действие4;
      CONTINUE;
   END IF;
   IF условие5 THEN
      выполнено_действие5;
      CONTINUE; -- Не является строго необходимой.
   END IF;
END LOOP;

Без команды continue мне пришлось бы реализовать тело цикла следующим образом:

LOOP
   EXIT WHEN exit_condition_met;
   IF condition1
      THEN
         NULL;
      ELSIF condition2
      THEN
         NULL;
      ELSE
         setup_steps_here;
      IF condition4 THEN
         action4_executed;
      ELSIF condition5 THEN
      action5_executed;
      END IF;
   END IF;
END LOOP;

Даже в этом простом примере команда continue позволяет обойтись без нескольких секций elsif, сокращает уровень вложенности и наглядно показывает, какие логические проверки (и сопутствующая обработка) должны выполняться на том же уровне. В частности, continue существенно сокращает глубину вложенности. Умение правильно использовать команду continue безусловно помогает программистам PL/SQL писать более качественный код.

Также команда CONTINUE пригодится для завершения внутренних циклов и немедленного продолжения следующей итерации внешнего цикла. Для этого циклам присваиваются имена при помощи меток. Пример: 

BEGIN
   <<outer>>
   FOR outer_index IN 1 .. 5
   LOOP
      DBMS_OUTPUT.PUT_LINE (
         'Внешний счетчик = ' || TO_CHAR (outer_index));

      <<inner>>
      FOR inner_index IN 1 .. 5
      LOOP
         DBMS_OUTPUT.PUT_LINE (
            ' Внутренний счетчик = ' || TO_CHAR (inner_index));
         CONTINUE outer;
      END LOOP inner;
   END LOOP outer;
END;
/

 Результат:

Внешний счетчик = 1
   Внутренний счетчик = 1
Внешний счетчик = 2
   Внутренний счетчик = 1
Внешний счетчик = 3
   Внутренний счетчик = 1
Внешний счетчик = 4
   Внутренний счетчик = 1
Внешний счетчик = 5
   Внутренний счетчик = 1

 

Полезные советы по работе с циклами в PL/SQL

Циклы — очень мощные и полезные конструкции, но при их использовании необходима осторожность. Именно циклы часто создают проблемы с быстродействием программ, и любая ошибка, возникшая в цикле, повторяется ввиду многократности его выполнения. Логика, определяющая условие остановки цикла, бывает очень сложной. В этом разделе приводятся несколько советов по поводу того, как сделать циклы более четкими и понятными, а также упростить их сопровождение.

 

Используйте понятные имена для счетчиков циклов

Не заставляйте программиста, которому поручено сопровождать программу, с помощью сложной дедукции определять смысл начального и конечного значения счетчика цикла FOR. Применяйте понятные и информативные имена переменных и циклов, и тогда другим программистам (да и вам самим некоторое время спустя) легко будет разобраться в таком коде.

Как можно понять следующий ход, не говоря уже о его сопровождении?

FOR i IN start_id .. end_id
LOOP
   FOR j IN 1 .. 7
   LOOP
      FOR k IN 1 .. 24
      LOOP
         build_schedule (i, j, k);
      END LOOP;
   END LOOP;
END LOOP;

Трудно представить, зачем использовать однобуквенные имена переменных, словно сошедшие со страниц учебника начального курса алгебры, но это происходит сплошь и рядом. Вредные привычки, приобретенные на заре эпохи программирования, искоренить невероятно сложно. А исправить этот код очень просто — достаточно присвоить переменным более информативные имена: 

FOR focus_account IN start_id .. end_id
LOOP
   FOR day_in_week IN 1 .. 7
   LOOP
      FOR month_in_biyear IN 1 .. 24
      LOOP
         build_schedule (focus_account, day_in_week, month_in_biyear);
      END LOOP;
   END LOOP;
END LOOP;

С содержательными именами переменных сразу видно, что внутренний цикл просто перебирает месяцы двухлетнего периода (12 × 2 = 24).

 

Корректно выходите из цикла

Один из фундаментальных принципов структурного программирования звучит так: «один вход, один выход»; иначе говоря, программа должна иметь одну точку входа и одну точку выхода. Первая часть в PL/SQL реализуется автоматически. Какой бы цикл вы ни выбрали, у него всегда только одна точка входа — первая исполняемая команда, следующая за ключевым словом LOOP. Но вполне реально написать цикл с несколькими точками выхода. Однако так поступать не рекомендуется, поскольку цикл с несколькими путями выхода трудно отлаживать и сопровождать.

При завершении цикла следует придерживаться следующих правил:

  • Не используйте в циклах FOR и WHILE команды EXIT и EXIT WHEN. Цикл FOR должен завершаться только тогда, когда исчерпаны все значения диапазона (целые числа или записи). Команда EXIT в цикле FOR прерывает этот процесс, а следовательно, идет вразрез с самим назначением цикла FOR. Точно так же условие окончания цикла WHILE задается в самой команде WHILE и нигде более задавать или дополнять его не следует.
  • Не используйте в циклах команды RETURN и GOTO, поскольку это вызывает преждевременное и неструктурированное завершение цикла. Применение указанных команд может выглядеть заманчиво, так как они сокращают объем кода. Однако спустя некоторое время вы потратите больше времени, пытаясь понять, изменить и отладить такой код.

Рассмотрим суть этих правил на примере цикла FOR с курсором. Как вы уже видели, данный цикл облегчает перебор возвращаемых курсором записей, но не подходит для случаев, когда выход из цикла определяется некоторым условием, основанным на данных текущей записи. Предположим, что в цикле записи курсора просматриваются до тех пор, пока сумма значений определенного столбца не превысит максимальное значение, как показано в следующем примере. Хотя это можно сделать с помощью цикла FOR и курсора, выполнив внутри этого цикла команду EXIT, поступать так не следует. 

1 DECLARE
2    CURSOR occupancy_cur IS
3       SELECT pet_id, room_number
4          FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);
5    pet_count INTEGER := 0;
6 BEGIN
7    FOR occupancy_rec IN occupancy_cur
8    LOOP
9       update_bill
10         (occupancy_rec.pet_id, occupancy_rec.room_number);
11      pet_count := pet_count + 1;
12      EXIT WHEN pet_count >= pets_global.max_pets;
13   END LOOP;
14 END;

В заголовке цикла FOR явно указано, что его тело должно быть выполнено n раз (где n — количество итераций в цикле со счетчиком или количество записей в цикле с курсором). Команда EXIT в цикле FOR (строка 12) изменяет логику его выполнения, и в результате получается код, который трудно понять и отладить.

Поэтому если нужно прервать цикл на основании информации текущей записи, лучше воспользоваться циклом WHILE или простым циклом, чтобы структура кода лучше отражала ваши намерения.

 

Получение информации о выполнении цикла FOR

Циклы FOR — удобные, четко формализованные структуры, которые выполняют в программе большую «административную» работу (особенно циклы с курсором). Однако у них есть и существенный недостаток: позволяя Oracle выполнять работу за нас, мы ограничиваем собственные возможности доступа к конечным результатам цикла после его завершения.

Предположим, нам нужно узнать, сколько записей обработано в цикле FOR с курсором, и затем использовать это значение в программе. Было бы очень удобно написать примерно такой код:

BEGIN
   FOR book_rec IN books_cur (author_in => 'FEUERSTEIN,STEVEN')
   LOOP
      ... обработка данных ...
   END LOOP;
   IF books_cur%ROWCOUNT > 10 THEN ...

Но попытавшись это сделать, мы получим сообщение об ошибке, поскольку курсор неявно открывается и закрывается Oracle. Как же получить нужную информацию из уже завершившегося цикла? Для этого следует объявить переменную в том блоке, в который входит цикл FOR, и присвоить ей значение в теле цикла — в таком случае переменная останется доступной и после завершения цикла. Вот как это делается:

DECLARE
   book_count PLS_INTEGER := 0;
BEGIN
   FOR book_rec IN books_cur (author_in => 'FEUERSTEIN,STEVEN')
   LOOP
      ... обработка данных ...
      book_count := books_cur%ROWCOUNT;
   END LOOP;
   IF book_count > 10 THEN ...

 

Команда SQL как цикл

На самом деле команда SQL (например, SELECT) тоже может рассматриваться как цикл, потому что она определяет действие, выполняемое компилятором SQL с набором данных. В некоторых случаях при реализации определенной задачи можно даже выбрать между использованием цикла PL/SQL и команды SQL. Давайте рассмотрим пример, а затем сделаем некоторые выводы о том, какое решение лучше.

Предположим, наша программа должна перенести информацию о выбывших из отеля животных из таблицы occupancy в таблицу occupancy_history. Опытный программист PL/SQL сходу выбирает цикл FOR с курсором. В теле цикла каждая выбранная из курсора запись сначала добавляется в таблицу occupancy_history, а затем удаляется из таблицы occupancy

DECLARE
   CURSOR checked_out_cur IS
      SELECT pet_id, name, checkout_date
     v FROM occupancy WHERE checkout_date IS NOT NULL;
BEGIN
   FOR checked_out_rec IN checked_out_cur
   LOOP
      INSERT INTO occupancy_history (pet_id, name, checkout_date)
         VALUES (checked_out_rec.pet_id, checked_out_rec.name,
                 checked_out_rec.checkout_date);
      DELETE FROM occupancy WHERE pet_id = checked_out_rec.pet_id;
   END LOOP;
END;

Программа работает, но является ли данное решение единственным? Конечно же, нет. Ту же логику можно реализовать с помощью команд SQL INSERT-SELECT FROM с последующей командой DELETE

BEGIN
   INSERT INTO occupancy_history (pet_id, NAME, checkout_date)
   SELECT pet_id, NAME, checkout_date
      FROM occupancy WHERE checkout_date IS NOT NULL;
   DELETE FROM occupancy WHERE checkout_date IS NOT NULL;
END;

Каковы преимущества такого подхода? Код стал короче и выполняется более эффективно благодаря уменьшению количества переключений контекста (переходов от исполняемого ядра PL/SQL к исполняемому ядру SQL и обратно). Теперь обрабатываются только одна команда INSERT и одна команда DELETE.

Однако у «чистого» SQL-подхода имеются свои недостатки. Команда SQL обычно действует по принципу «все или ничего». Иначе говоря, если при обработке хотя бы одной записи occupancy_history происходит ошибка, то отменяются все инструкции INSERT и DELETE и ни одна запись не будет вставлена или удалена. Кроме того, приходится дважды записывать условие WHERE. В данном примере это не очень важно, но в более сложных запросах данное обстоятельство может иметь решающее значение. А первоначальный цикл FOR позволяет избежать дублирования потенциально сложной логики в нескольких местах.

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

BEGIN
   FOR checked_out_rec IN checked_out_cur
   LOOP
      BEGIN
         INSERT INTO occupancy_history ...
         DELETE FROM occupancy ...
      EXCEPTION
         WHEN OTHERS THEN
            log_checkout_error (checked_out_rec);
      END;
   END LOOP;
END;
;

PL/SQL позволяет обрабатывать записи по одной и для каждой из них выполнять необходимые действия (которые могут базироваться на сложной процедурной логике, зависящей от содержимого конкретной записи). В таких случаях удобнее использовать комбинацию PL/SQL и SQL. Но если ваша задача позволяет ограничиться одним SQL, лучше им и воспользоваться — код получится и короче, и эффективнее.

Продолжить выполнение после ошибок в командах SQL можно двумя способами: (1) использовать конструкцию LOG ERRORS со вставкой, обновлением и удалением в Oracle10g Release 2 и выше; и (2) использовать конструкцию SAVE EXCEPTIONS в командах FOR ALL.

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 1944 просмотров sepia Sun, 08 Jul 2018, 07:33:47
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 1382 просмотров Rasen Fasenger Mon, 22 Oct 2018, 04:44:08
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 1544 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 2976 просмотров Анатолий Wed, 23 May 2018, 18:54:01


OraCool аватар
OraCool ответил в теме #9499 08 окт 2019 04:45
Sepia постарался на славу. Молодца!)
borisen аватар
borisen ответил в теме #9450 27 сен 2019 15:49
Sepia, просто молодца! Прекрасное руководство!
ildergun аватар
ildergun ответил в теме #9409 09 мая 2019 16:11
Круто! Детальнее мануала не находил)
apv аватар
apv ответил в теме #9245 07 окт 2018 09:00
Отличное описание циклов языка PL/SQL с примерами. Автору - спасибо!
AidaU аватар
AidaU ответил в теме #9215 16 сен 2018 08:41
Четкое и грамотное изложение. Хороши примеры циклов For, While, Loop. Sepia, спасибо!

Comments on Циклы в PL/SQL: LOOP, FOR, WHILE и CONTINUE

Будьте первым прокомментировавшим
Пожалуйста, авторизуйтесь, для комментирования