Есть два типа управляющих команд ветвления в PL/SQL: условные команды и команды перехода. Команды первого типа, присутствующие почти во всех программах, управляют последовательностью выполнения программного кода в зависимости от заданных условий. В языке PL/SQL к этой категории относятся команды IF-THEN-ELSE и CASE . Также существуют так называемые CASE-выражения , которые иногда позволяют обойтись без команд IF и CASE . Значительно реже используются команды второго типа: GOTO (безусловный переход) и NULL (не выполняет никаких действий).

Команды IF

Команда IF реализует логику условного выполнения команд программы. С ее помощью можно реализовать конструкции следующего вида:

• Если оклад находится в пределах от $10 000 до $20 000, начислить премию в размере $1500.
• Если коллекция содержит более 100 элементов, удалить лишнее.

Команда IF существует в трех формах, представленных в следующей таблице.

Комбинация IF-THEN

Общий синтаксис конструкции IF-THEN выглядит так:

IF условие THEN ... последовательность исполняемых команд... END IF;

Здесь условие - это логическая переменная, константа или логическое выражение с результатом TRUE , FALSE или NULL . Исполняемые команды между ключевыми словами THEN и END IF выполняются, если результат проверки условия равен TRUE , и не выполняются - если он равен FALSE или NULL .

Трехзначная логика

Логические выражения могут возвращать три возможных результата. Когда все значения в логическом выражении известны, результат равен TRUE или FALSE. Например, истинность или ложность выражений вида

(2 < 3) AND (5 < 10)

сомнений не вызывает. Однако иногда оказывается, что некоторые значения в выражении неизвестны. Это может быть связано с тем, что соответствующие столбцы базы данных содержат NULL или остались пустыми. Каким должен быть результат выражений с NULL , например:

2 < NULL

Так как отсутствующее значение неизвестно, на этот вопрос можно дать только один ответ: «Неизвестно». В этом и заключается суть так называемой трехзначной логики - возможными результатами могут быть не только TRUE и FALSE , но и NULL .

Если вы захотите больше узнать о трехзначной логике, я рекомендую статью Лекса де Хаана и Джонатана Генника «Nulls: Nothing to Worry About» из Oracle Magazine. Также полезную информацию можно найти в книге С. Дж. Дейта «Database in Depth: Relational Theory for the Practitioner». Мы еще вернемся к трехзначной логике в этой статье.

Следующая условная команда IF сравнивает два числовых значения. Учтите, что если одно из них равно NULL , то и результат всего выражения равен NULL (если переменная salary равна NULL , то give_bonus не выполняется):

IF salary > 40000 THEN give_bonus (employee_id,500); END IF;

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

IF salary > 40000 OR salary IS NULL THEN give_bonus (employee_id,500); END IF;

В этом примере условие salary IS NULL дает результат TRUE , если salary не содержит значения, и результат FALSE во всех остальных случаях.

Для обнаружения возможных значений NULL и их обработки удобно применять такие операторы, как IS NULL и IS NOT NULL, или функции COALESCE и NVL2 . Для каждой переменной в каждом написанном вами логическом выражении подумайте, что произойдет, если эта переменная содержит NULL .

Ключевые слова IF, THEN и END IF не обязательно размещать в отдельных строках. В командах IF разрывы строк не важны, поэтому приведенный выше пример можно было бы записать так:

IF salary > 40000 THEN give_bonus (employee_id,500); END IF;

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

IF salary > 40000 THEN INSERT INTO employee_bonus (eb_employee_id, eb_bonus_amt) VALUES (employee_id, 500); UPDATE emp_employee SET emp_bonus_given=1 WHERE emp_employee_id=employee_id; END IF;

И та же команда вполне нормально читается при разбиении на строки:

IF salary > 40000 THEN INSERT INTO employee_bonus (eb_employee_id, eb_bonus_amt) VALUES (employee_id, 500); UPDATE emp_employee SET emp_bonus_given=1 WHERE emp_employee_id=employee_id; END IF;

Вопрос удобочитаемости становится еще более важным при использовании ключевых слов ELSE и ELSIF , а также вложенных команд IF . Поэтому, чтобы сделать логику команд IF максимально наглядной, мы рекомендуем применять все возможности отступов и форматирования. И те программисты, которым придется сопровождать ваши программы, будут вам очень признательны.

Конструкция IF-THEN-ELSE

Конструкция IF-THEN-ELSE применяется при выборе одного из двух взаимоисключающих действий. Формат этой версии команды IF:

IF условие THEN ... последовательность команд для результата TRUE ... ELSE ... последовательность команд для результата FALSE/NULL ... END IF;

Здесь условие - это логическая переменная, константа или логическое выражение. Если его значение равно TRUE , то выполняются команды, расположенные между ключевыми словами THEN и ELSE , а если FALSE или NULL - команды между ключевыми словами ELSE и END IF .

Важно помнить, что в конструкции IF-THEN-ELSE всегда выполняется одна из двух возможных последовательностей команд. После выполнения соответствующей последовательности управление передается команде, которая расположена сразу после ключевых слов END IF .

Следующая конструкция IF-THEN-ELSE расширяет пример IF-THEN , приведенный в предыдущем разделе:

IF salary <= 40000 THEN give_bonus (employee_id, 0); ELSE give_bonus (employee_id, 500); END IF;

в этом примере сотрудники с окладом более 40 000 получат премию в 500, а остальным премия не назначается. Или все же назначается? Что произойдет, если у сотрудника по какой-либо причине оклад окажется равным NULL ? В этом случае будут выполнены команды, следующие за ключевым словом ELSE , и работник получит премию, положенную только высокооплачиваемому составу. Поскольку мы не можем быть уверены в том, что оклад ни при каких условиях не окажется равным NULL , нужно защититься от подобных проблем при помощи функции NVL:

IF NVL(salary,0) <= 40000 THEN give_bonus (employee_id, 0); ELSE give_bonus (employee_id, 500); END IF;

Функция NVL возвращает нуль, если переменная salary равна NULL . Это гарантирует, что работникам с окладом NULL будет начислена нулевая премия (не позавидуешь!).

ЛОГИЧЕСКИЕ ФЛАГИ

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

IF:customer.order_total > max_allowable_order THEN order_exceeds_balance:= TRUE; ELSE order_exceeds_balance:= FALSE; END IF;

можно воспользоваться следующим, гораздо более простым выражением (при условии, что ни одна из переменных не равна NULL):

Order_exceeds_balance:= :customer.order_total > max_allowable_order;

Теперь если где-либо в программном коде потребуется проверить, не превышает ли сумма заказа (order_total) максимально допустимое значение (max_allowable_order), достаточно простой и понятной конструкции IF:

IF order_exceeds_balance THEN ...

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

Конструкция IF-THEN-ELSIF

Данная форма команды IF удобна для реализации логики с несколькими альтернативными действиями в одной команде IF . Как правило, ELSIF используется с взаимоисключающими альтернативами (то есть при выполнении команды IF истинным может быть только одно из условий). Обобщенный синтаксис этой формы IF выглядит так:

IF условие-1 THEN команды-1 ELSIF condition-N THEN команды-N END IF;

Некоторые программисты пытаются записывать ELSIF в виде ELSEIF или ELSE IF . Это очень распространенная синтаксическая ошибка.

Формально конструкция IF-THEN-ELSIF представляет собой один из способов реализации функций команды CASE в PL/SQL. Конечно, если вы используете Oracle9i, лучше воспользоваться командой CASE , о которой будет рассказано далее в следующей статье.

В каждой секции ELSIF (кроме секции ELSE) за условием должно следовать ключевое слово THEN . Секция ELSE в IF-ELSIF означает «если не выполняется ни одно из условий», то есть когда ни одно из условий не равно TRUE , выполняются команды, следующие за ELSE . Следует помнить, что секция ELSE не является обязательной - конструкция IFELSIF может состоять только из секций IF и ELSIF . Если ни одно из условий не равно TRUE , то никакие команды блока IF не выполняются.

IF salary BETWEEN 10000 AND 20000 THEN give_bonus(employee_id, 1500); ELSIF salary BETWEEN 20000 AND 40000 THEN give_bonus(employee_id, 1000); ELSIF salary > 40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END IF;

Ловушки синтаксиса IF

Запомните несколько правил, касающихся применения команды IF:

• Каждая команда IF должна иметь парную конструкцию END IF . Все три разновидности данной команды обязательно должны явно закрываться ключевым словом END IF .
• Не забывайте разделять пробелами ключевые слова END и IF . Если вместо END IF ввести ENDIF , компилятор выдаст малопонятное сообщение об ошибке:

• Ключевое слово ELSIF должно содержать только одну букву « E ». Если вместо ключевого слова ELSIF указать ELSEIF , компилятор не воспримет последнее как часть команды IF . Он интерпретирует его как имя переменной или процедуры.
• Точка с запятой ставится только после ключевых слов END IF . После ключевых слов THEN, ELSE и ELSIF точка с запятой не ставится. Они не являются отдельными исполняемыми командами и, в отличие от END IF , не могут завершать команду PL/SQL. Если вы все же поставите точку с запятой после этих ключевых слов, компилятор выдаст сообщение об ошибке.

Условия IF-ELSIF всегда обрабатываются от первого к последнему. Если оба условия равны TRUE , то выполняются команды первого условия. В контексте текущего примера для оклада $20000 будет начислена премия $1500, хотя оклад $20 000 также удовлетворяет условию премии $1000 (проверка BETWEEN включает границы). Если какое-либо условие истинно, остальные условия вообще не проверяются.

Команда CASE позволяет решить задачу начисления премии более элегантно, чем решение IF-THEN-ELSIF в этом разделе (см. раздел «Команды и выражения CASE »).

И хотя в команде IF-THEN-ELSIF разрешены перекрывающиеся условия, лучше избегать их там, где это возможно. В моем примере исходная спецификация немного неоднозначна в отношении граничных значений (таких, как 20 000). Если предположить, что работникам с низшими окладами должны начисляться более высокие премии (что на мой взгляд вполне разумно), я бы избавился от а BETWEEN и воспользовался логикой «меньше/больше» (см. далее). Также обратите внимание на отсутствие секции ELSE - я опустил ее просто для того, чтобы показать, что она не является обязательной:

IF salary >= 10000 AND salary <= 20000 THEN give_bonus(employee_id, 1500); ELSIF salary > 20000 AND salary <= 40000 THEN give_bonus(employee_id, 1000); ELSIF salary > 40000 THEN give_bonus(employee_id, 400); END IF;

Принимая меры к предотвращению перекрывающихся условий в IF-THEN-ELSIF , я устраняю возможный (и даже вероятный) источник ошибок для программистов, которые будут работать с кодом после меня. Я также устраняю возможность введения случайных ошибок в результате переупорядочения секций ELSIF . Однако следует заметить, что если значение salary равно NULL , никакой код выполнен не будет, потому что секции ELSE отсутствует.

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

Вложенные команды IF

Команды IF можно вкладывать друг в друга. В следующем примере представлены команды IF с несколькими уровнями вложенности:

IF условие1 THEN IF условие2 THEN команды2 ELSE IF условие3 THEN команды3 ELSIF условие4 THEN команды4 END IF; END IF; END IF;

Сложную логику часто невозможно реализовать без вложенных команд IF , но их использование требует крайней осторожности. Вложенные команды IF , как и вложенные циклы, затрудняют чтение программы и ее отладку. И если вы собираетесь применить команды IF более чем с тремя уровнями вложения, подумайте, нельзя ли пересмотреть логику программы и реализовать требования более простым способом. Если такового не найдется, подумайте о создании одного или нескольких локальных модулей, скрывающих внутренние команды IF . Главное преимущество вложенных структур IF заключается в том, что они позволяют отложить проверку внутренних условий . Условие внутренней команды IF проверяется только в том случае, если значение выражения во внешнем условии равно TRUE . Таким образом, очевидной причиной вложения команд IF может быть проверка внутреннего условия только при истинности другого. Например, код начисления премий можно было бы записать так:

IF award_bonus(employee_id) THEN IF print_check (employee_id) THEN DBMS_OUTPUT.PUT_LINE("Check issued for " || employee_id); END IF; END IF;

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

Ускоренное вычисление

В PL/SQL используется ускоренное вычисление условий; иначе говоря, вычислять все выражения в условиях IF не обязательно. Например, при вычислении выражения в следующей конструкции IF PL/SQL прекращает обработку и немедленно выполняет ветвь ELSE , если первое условие равно FALSE или NULL:

IF условие1 AND условие2 THEN ... ELSE ... END IF;

PL/SQL прерывает вычисление, если условие_1 равно FALSE или NULL , потому что ветвь THEN выполняется только в случае истинности всего выражения, а для этого оба подвыражения должны быть равны TRUE . Как только обнаруживается, что хотя бы одно подвыражение отлично от TRUE , дальнейшие проверки излишни - ветвь THEN все равно выбрана не будет.

Изучая поведение ускоренного вычисления в PL/SQL, я обнаружил нечто интересное: его поведение зависит от контекста выражения. Возьмем следующую команду:

My_boolean:= condition1 AND condition2

В отличие от случае с командой IF , если условие1 равно NULL , ускоренное вычисление применяться не будет. Почему? Потому что результат может быть равен NULL или FALSE в зависимости от условия2. Для команды IF оба значения NULL и FALSE ведут к ветви ELSE , поэтому ускоренное вычисление возможно. Но для присваивания должно быть известно конечное значение, и ускоренное вычисление в этом случае может (и будет) происходить только в том случае, если условие1 равно FALSE .

Аналогичным образом работает ускоренное вычисление в операциях OR: если первый операнд OR в конструкции IF равен TRUE , PL/SQL немедленно выполняет ветвь THEN:

IF условие1 OR условие2 THEN ... ELSE ... END IF;

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

IF простое_условие AND сложное_условие THEN ... END IF;

Сначала проверяется простое_условие, и если его результата достаточно для определения конечного результата операции AND (то есть если результат равен FALSE), более затратное условие не проверяется, а пропущенная проверка улучшает быстродействие приложения.

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

Тип возвращаемой величины будет такой же (INTEGER , DOUBLE или STRING), как и у первой возвращаемой величины (выражение после первого оператора THEN).

Последнее обновление: 26.05.2018

CASE

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

CASE WHEN условие_1 THEN результат_1 WHEN условие_2 THEN результат_2 ................................. WHEN условие_N THEN условие_N END

Возьмем для примера следующую таблицу Products:

CREATE TABLE Products (Id INT AUTO_INCREMENT PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price DECIMAL NOT NULL);

Выполним запрос к этой таблице и используем функцию CASE:

SELECT ProductName, ProductCount, CASE WHEN ProductCount = 1 THEN "Товар заканчивается" WHEN ProductCount = 2 THEN "Мало товара" WHEN ProductCount = 3 THEN "Есть в наличии" ELSE "Много товара" END AS Category FROM Products;

Функция IF

Функция IF в зависимости от результата условного выражения возвращает одно из двух значений. Общая форма функции выглядит следующим образом:

IF(условие, значение_1, значение_2)

Если условие, передаваемое в качестве первого параметра, верно, то возвращается первое значение, иначе возвращается второе значение. Например:

SELECT ProductName, Manufacturer, IF(ProductCount > 3, "Много товара", "Мало товара") FROM Products;

IFNULL

Функция IFNULL проверяет значение некоторого выражения. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:

IFNULL(выражение, значение)

Например, возьмем следующую таблицу

CREATE TABLE Clients (Id INT AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, Phone VARCHAR(20) NULL, Email VARCHAR(20) NULL); INSERT INTO Clients (FirstName, LastName, Phone, Email) VALUES ("Tom", "Smith", "+36436734", NULL), ("Bob", "Simpson", NULL, NULL);

И применим при получении данных функцию IFNULL:

SELECT FirstName, LastName, IFNULL(Phone, "не определено") AS Phone, IFNULL(Email, "неизвестно") AS Email FROM Clients;

COALESCE

Функция COALESCE принимает список значений и возвращает первое из них, которое не равно NULL:

COALESCE(выражение_1, выражение_2, выражение_N)

Например, выберем из таблицы Clients пользователей и в контактах у них определим либо телефон, либо электронный адрес, если они не равны NULL:

SELECT FirstName, LastName, COALESCE(Phone, Email, "не определено") AS Contacts FROM Clients;

То есть в данном случае возвращается телефон, если он определен. Если он не определен, то возвращается электронный адрес. Если и электронный адрес не определен, то возвращается строка "не определено".