Команда CASE
позволяет выбрать для выполнения одну из нескольких последовательностей команд. Эта конструкция присутствует в стандарте SQL с 1992 года, хотя в Oracle SQL она не поддерживалась вплоть до версии Oracle8i, а в PL/SQL — до версии Oracle9i Release 1. Начиная с этой версии, поддерживаются следующие разновидности команд CASE
:
- Простая команда
CASE
— связывает одну или несколько последовательностей команд PL/SQL с соответствующими значениями (выполняемая последовательность выбирается с учетом результата вычисления выражения, возвращающего одно из значений). - Поисковая команда
CASE
— выбирает для выполнения одну или несколько последовательностей команд в зависимости от результатов проверки списка логических значений. Выполняется последовательность команд, связанная с первым условием, результат проверки которого оказался равнымTRUE
.
NULL или UNKNOWN?
В статье, посвященной оператору IF, вы могли узнать, что результат логического выражения может быть равен TRUE
, FALSE
или NULL
.
В PL/SQL это утверждение истинно, но в более широком контексте реляционной теории считается некорректным говорить о возврате NULL
из логического выражения. Реляционная теория говорит, что сравнение с NULL
следующего вида:
2 < NULL
дает логический результат UNKNOWN
, причем значение UNKNOWN
не эквивалентно NULL
. Впрочем, вам не стоит особенно переживать из-за того, что в PL/SQL для UNKNOWN
используется обозначение NULL
. Однако вам следует знать, что третьим значением в трехзначной логике является UNKNOWN
. И я надеюсь, что вы никогда не попадете впросак (как это бывало со мной!), используя неправильный термин при обсуждении трехзначной логики с экспертами в области реляционной теории.
Кроме команд CASE
, PL/SQL также поддерживает CASE
-выражения. Такое выражение очень похоже на команду CASE
, оно позволяет выбрать для вычисления одно или несколько выражений. Результатом выражения CASE
является одно значение, тогда как результатом команды CASE
является выполнение последовательности команд PL/SQL.
Простые команды CASE
Простая команда CASE
позволяет выбрать для выполнения одну из нескольких последовательностей команд PL/SQL в зависимости от результата вычисления выражения. Он записывается следующим образом:
CASE выражение WHEN результат_1 THEN команды_1 WHEN результат_2 THEN команды_2 ... ELSE команды_else END CASE;
Ветвь ELSE
здесь не обязательна. При выполнении такой команды PL/SQL сначала вычисляет выражение, после чего результат сравнивается с результат_1
. Если они совпадают, то выполняются команды_1
. В противном случае проверяется значение результат_2 и т. д.
Приведем пример простой команды CASE
, в котором премия начисляется в зависимости от значения переменной employee_type
:
CASE employee_type WHEN 'S' THEN award_salary_bonus(employee_id); WHEN 'H' THEN award_hourly_bonus(employee_id); WHEN 'C' THEN award_commissioned_bonus(employee_id); ELSE RAISE invalid_employee_type; END CASE;
В этом примере присутствует явно заданная секция ELSE
, однако в общем случае она не обязательна. Без секции ELSE
компилятор PL/SQL неявно подставляет такой код:
ELSE RAISE CASE_NOT_FOUND;
Иначе говоря, если не задать ключевое слово ELSE
и если никакой из результатов в секциях WHEN
не соответствует результату выражения в команде CASE
, PL/SQL инициирует исключение CASE_NOT_FOUND
. В этом и заключается отличие данной команды от IF
. Когда в команде IF
отсутствует ключевое слово ELSE
, то при невыполнении условия не происходит ничего, тогда как в команде CASE
аналогичная ситуация приводит к ошибке.
Интересно посмотреть, как с помощью простой команды CASE
реализовать описанную в начале главы логику начисления премий. На первый взгляд это кажется невозможным, но подойдя к делу творчески, мы приходим к следующему решению:
CASE TRUE WHEN salary >= 10000 AND salary <=20000 THEN give_bonus(employee_id, 1500); WHEN salary > 20000 AND salary <= 40000 THEN give_bonus(employee_id, 1000); WHEN salary > 40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END CASE;
Здесь важно то, что элементы выражение и результат могут быть либо скалярными значениями, либо выражениями, результатами которых являются скалярные значения.
Вернувшись к команде IF...THEN...ELSIF
, реализующей ту же логику, вы увидите, что в команде CASE
определена секция ELSE
, тогда как в команде IF–THEN–ELSIF
ключевое слово ELSE
отсутствует. Причина добавления ELSE
проста: если ни одно из условий начисления премии не выполняется, команда IF
ничего не делает, и премия получается нулевой. Команда CASE
в этом случае выдает ошибку, поэтому ситуацию с нулевым размером премии приходится программировать явно.
Чтобы предотвратить ошибки CASE_NOT_FOUND
, убедитесь в том, что при любом значении проверяемого выражения будет выполнено хотя бы одно из условий.
Приведенная выше команда CASE TRUE
кому-то покажется эффектным трюком, но на самом деле она всего лишь реализует поисковую команду CASE
, о которой мы поговорим в следующем разделе.
Поисковая команда CASE
Поисковая команда CASE
проверяет список логических выражений; обнаружив выражение, равное TRUE
, выполняет последовательность связанных с ним команд. В сущности, поисковая команда CASE
является аналогом команды CASE TRUE
, пример которой приведен в предыдущем разделе. Поисковая команда CASE
имеет следующую форму записи:
CASE WHEN выражение_1 THEN команды_1 WHEN выражение_2 THEN команда_2 ... ELSE команды_else END CASE; Она идеально подходит для реализации логики начисления премии: CASE WHEN salary >= 10000 AND salary <=20000 THEN give_bonus(employee_id, 1500); WHEN salary > 20000 AND salary <= 40000 THEN give_bonus(employee_id, 1000); WHEN salary > 40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END CASE;
Поисковая команда CASE
, как и простая команда, подчиняется следующим правилам:
- Выполнение команды заканчивается сразу же после выполнения последовательности исполняемых команд, связанных с истинным выражением. Если истинными оказываются несколько выражений, то выполняются команды, связанные с первым из них.
- Ключевое слово
ELSE
не обязательно. Если оно не задано и ни одно из выражений не равноTRUE
, инициируется исключениеCASE_NOT_FOUND
. - Условия
WHEN
проверяются в строго определенном порядке, от начала к концу.
Рассмотрим еще одну реализацию логики начисления премии, в которой используется то обстоятельство, что условия WHEN
проверяются в порядке их записи. Отдельные выражения проще, но можно ли сказать, что смысл всей команды стал более понятным?
CASE WHEN salary > 40000 THEN give_bonus(employee_id, 500); WHEN salary > 20000 THEN give_bonus(employee_id, 1000); WHEN salary >= 10000 THEN give_bonus(employee_id, 1500); ELSE give_bonus(employee_id, 0); END CASE;
Если оклад некоего сотрудника равен 20 000, то первые два условия равны FALSE
, а третье — TRUE
, поэтому сотрудник получит премию в 1500 долларов. Если же оклад равен 21 000, то результат второго условия будет равен TRUE
, и премия составит 1000 долларов. Выполнение команды CASE
завершится на второй ветви WHEN
, а третье условие даже не будет проверяться. Стоит ли использовать такой подход при написании команд CASE
— вопрос спорный. Как бы то ни было, имейте в виду, что написать такую команду возможно, а при отладке и редактировании программ, в которых результат зависит от порядка следования выражений, необходима особая внимательность.
Логика, зависящая от порядка следования однородных ветвей WHEN
, является потенциальным источником ошибок, возникающих при их перестановке. В качестве примера рассмотрим следующую поисковую команду CASE
, в которой при значении salary
, равном 20 000, проверка условий в обеих ветвях WHEN
дает TRUE
:
CASE WHEN salary BETWEEN 10000 AND 20000 THEN give_bonus(employee_id, 1500); WHEN salary BETWEEN 20000 AND 40000 THEN give_bonus(employee_id, 1000); ...
Представьте, что программист, занимающийся сопровождением этой программы, легкомысленно переставит ветви WHEN
, чтобы упорядочить их по убыванию salary
. Не отвергайте такую возможность! Программисты часто склонны «доводить до ума» прекрасно работающий код, руководствуясь какими-то внутренними представлениями о порядке. Команда CASE
с переставленными секциями WHEN
выглядит так:
CASE WHEN salary BETWEEN 20000 AND 40000 THEN give_bonus(employee_id, 1000); WHEN salary BETWEEN 10000 AND 20000 THEN give_bonus(employee_id, 1500); ...
На первый взгляд все верно, не так ли? К сожалению, из-за перекрытия двух ветвей WHEN
в программе появляется коварная ошибка. Теперь сотрудник с окладом 20 000 получит премию 1000 вместо положенных 1500. Возможно, в некоторых ситуациях перекрытие между ветвями WHEN
желательно и все же его следует по возможности избегать. Всегда помните, что порядок следования ветвей важен, и сдерживайте желание доработать уже работающий код — «не чините то, что не сломано».
Поскольку условия WHEN
проверяются по порядку, можно немного повысить эффективность кода, поместив ветви с наиболее вероятными условиями в начало списка. Кроме того, если у вас есть ветвь с «затратными» выражениями (например, требующими значительного процессорного времени и памяти), их можно поместить в конец, чтобы свести к минимуму вероятность их проверки. За подробностями обращайтесь к разделу «Вложенные команды IF
».
Поисковые команды CASE
используются в тех случаях, когда выполняемые команды определяются набором логических выражений. Простая команда CASE
используется тогда, когда решение принимается на основании результата одного выражения.
Вложенные команды CASE
Команды CASE
, как и команды IF
, могут быть вложенными. Например, вложенная команда CASE
присутствует в следующей (довольно запутанной) реализации логики начисления премий:
CASE WHEN salary >= 10000 THEN CASE WHEN salary <= 20000 THEN give_bonus(employee_id, 1500); WHEN salary > 40000 THEN give_bonus(employee_id, 500); WHEN salary > 20000 THEN give_bonus(employee_id, 1000); END CASE; WHEN salary < 10000 THEN give_bonus(employee_id,0); END CASE;
В команде CASE
могут использоваться любые команды, так что внутренняя команда CASE
легко заменяется командой IF
. Аналогичным образом, в команду IF
может быть вложена любая команда, в том числе и CASE
.
Выражения CASE
Выражения CASE
решают ту же задачу, что и команды CASE
, но только не для исполняемых команд, а для выражений. Простое выражение CASE
выбирает для вычисления одно из нескольких выражений на основании заданного скалярного значения. Поисковое выражение CASE
последовательно вычисляет выражения из списка, пока одно из них не окажется равным TRUE
, а затем возвращает результат связанного с ним выражения.
Синтаксис этих двух разновидностей выражений CASE
:
Простое_выражение_Case := CASE выражение WHEN результат_1 THEN результирующее_выражение_1 WHEN результат_2 THEN результирующее_выражение_2 ... ELSE результирующее_выражение_else END; Поисковое_выражение_Case := CASE WHEN выражение_1 THEN результирующее_выражение_1 WHEN выражение_2 THEN результирующее_выражение_2 ... ELSE результирующее_выражение_else END;
Выражение CASE
возвращает одно значение — результат выбранного для вычисления выражения. Каждой ветви WHEN
должно быть поставлено в соответствие одно результирующее выражение (но не команда). В конце выражения CASE
не ставится ни точка с запятой, ни END CASE
. Выражение CASE
завершается ключевым словом END
.
Далее приводится пример простого выражения CASE
, используемого совместно с процедурой PUT_LINE
пакета DBMS_OUTPUT
для вывода на экран значения логической переменной.
(Напомним, что программа PUT_LINE
не поддерживает логические типы напрямую.) В этом примере выражение CASE
преобразует логическое значение в символьную строку, которая затем выводится процедурой PUT_LINE
:
DECLARE boolean_true BOOLEAN := TRUE; boolean_false BOOLEAN := FALSE; boolean_null BOOLEAN; FUNCTION boolean_to_varchar2 (flag IN BOOLEAN) RETURN VARCHAR2 IS BEGIN RETURN CASE flag WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' ELSE 'NULL' END; END; BEGIN DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_true)); DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_false)); DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_null)); END;
Для реализации логики начисления премий можно использовать поисковое выражение CASE
, возвращающее величину премии для заданного оклада:
DECLARE salary NUMBER := 20000; employee_id NUMBER := 36325; PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE(emp_id); DBMS_OUTPUT.PUT_LINE(bonus_amt); END; BEGIN give_bonus(employee_id, CASE WHEN salary >= 10000 AND salary <= 20000 THEN 1500 WHEN salary > 20000 AND salary <= 40000 THEN 1000 WHEN salary > 40000 THEN 500 ELSE 0 END); END;
Выражение CASE
может применяться везде, где допускается использование выражений любого другого типа. В следующем примере CASE-выражение используется для вычисления размера премии, умножения его на 10 и присваивания результата переменной, выводимой на экран средствами DBMS_OUTPUT
:
DECLARE salary NUMBER := 20000; employee_id NUMBER := 36325; bonus_amount NUMBER; BEGIN bonus_amount := CASE WHEN salary >= 10000 AND salary <= 20000 THEN 1500 WHEN salary > 20000 AND salary <= 40000 THEN 1000 WHEN salary > 40000 THEN 500 ELSE 0 END * 10; DBMS_OUTPUT.PUT_LINE(bonus_amount); END;
В отличие от команды CASE
, если условие ни одной ветви WHEN
не выполнено, выражение CASE
не выдает ошибку, а просто возвращает NULL
.