В этом блоге я расскажу Вам об управляющих структуры 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
.
Рис. 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
.
Общий синтаксис цикла 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
.