Pl sql exception in loop

Is it possible to CONTINUE a loop from an exception?

I have a fetch being executed inside of a loop. If this fetch fails (no data) I would like to CONTINUE the loop to the next record from within the EXCEPTION .

Is this possible?

I’m getting a ORA-06550 & PLS-00201 identifer CONTINUE must be declared

5 Answers 5

The CONTINUE statement is a new feature in 11g.

In the construct you have provided, you don’t need a CONTINUE. Once the exception is handled, the statement after the END is performed, assuming your EXCEPTION block doesn’t terminate the procedure. In other words, it will continue on to the next iteration of the user_rec loop.

You also need to SELECT INTO a variable inside your BEGIN block:

Obviously you must declare v_attr as well.

How about the ole goto statement (i know, i know, but it works just fine here 😉

Just put end_loop at very end of loop of course. The null can be substituted with a commit maybe or a counter increment maybe, up to you.

For this example you really should just use an outer join.

Notice you can use WHEN exception THEN NULL the same way as you would use WHEN exception THEN continue . Example:

Linked

Related

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.1.14.43159

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Цикл 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):

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Цикл выполняется 10 раз; счетчик увеличивается от 1 до 10:

• Цикл выполняется 10 раз; счетчик уменьшается от 10 до 1:

• Цикл не выполняется ни разу. В заголовке цикла указано ключевое слово REVERSE , поэтому счетчик цикла loop_counter изменяется от большего значения к меньшему. Однако начальное и конечное значения заданы в неверном порядке:

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

• Цикл выполняется для диапазона, определяемого значениями переменной и выражения:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Метки циклов

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

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

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

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

• Если вы написали очень длинный цикл с множеством вложенных циклов (допустим, начинающийся в строке 50, завершается в строке 725 и содержащий 16 вложенных циклов), используйте метку цикла для того, чтобы явно связать его конец с началом. Визуальная пометка поможет при отладке и сопровождении программы; без нее будет трудно уследить, какая команда LOOP соответствует каждой из команд END LOOP .
• Метку цикла можно использовать для уточнения имени управляющей переменной цикла (записи или счетчика), что также упрощает чтение программы:

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

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

Команда CONTINUE

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник