Читать книгу Основы программирования в СУБД Oracle. SQL+PL/SQL. - О. А. Ткачев - Страница 20

Довольно часто значение столбца, которое должен вернуть SQL-запрос, зависит от условий, которые нужно проверять для каждой строки. Для реализации подобного выбора используются выражение CASE и функция DECODE. Используя CASE и DECODE, можно реализовать условную логику if-then-else в операторе SELECT. Выражение CASE соответствует стандарту ANSI SQL, а функция DECODE специфична для Oracle.

Выражение CASE

Практически во всех современных языках программирования используется выражение CASE. Есть два варианта выражения CASE:

– выражение CASE с параметром;

– выражение CASE с условием.

Выражение CASE с параметром имеет следующий синтаксис:

CASE {параметр}

– WHEN {значение1} THEN {результат1}

– [WHEN {значение2} THEN {результат2}

– …

– WHEN {значениеN} THEN {результатN}]

– [ELSE {результат_ELSE}]

END;

Выражение CASE выполняется следующим образом: сравниваются значение {параметр} со значениями {значение i} в предложениях WHEN и возвращает результат {результатi} первого предложения, в котором будет выполнено условие {параметр} = {значениеi}.

Следует иметь в виду, что Oracle не оценивает остальные предложения WHEN. Если ни в одном из предложений WHEN не выполняется условие {параметр} = {значениеi}, то возвращается значение {результат_ELSE}. Если предложение ELSE отсутствует, то выражение CASE вернет результат NULL.

Возвращаемый результат может быть значением или выражением. Выражения {параметр} и {значение1} должны иметь один и тот же тип данных. Все возвращаемые значения {результат2} должны иметь одинаковый тип данных.

Примечание. Выражение CASE может содержать другие выражения CASE. Единственным ограничением является то, что одно выражение CASE может иметь максимум 255 условных выражений.

Пример 3.48. Вывести данные о сотрудниках и размере их премии, которая задана в виде фиксированной суммы, размер которой зависит от отдела, где работает сотрудник

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

В этом примере отсутствует предложение ELSE, поэтому размер премии для сотрудников отделов, номеров которых нет в предложениях WHERE, имеет значение NULL.

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

Пример 3.50. Вывести данные о сотрудниках и размере их премии, которая зависит как от отдела, где работает сотрудник, так и от его должности.

Выражение CASE с условием имеет следующий синтаксис:

CASE

WHEN {условие1} THEN {результат1}

[WHEN {условие2} THEN {результат2}

…

WHEN {условиеN} THEN {результатN}]

[ELSE {результат_ELSE}]

END

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

Пример 3.51. Вывести данные о сотрудниках и размере их премии, которая зависит от зарплаты сотрудника

Пример 3.52. Вывести данные о сотрудниках и размере их премии, которая зависит от количества лет, которые проработал сотрудник

Функция DECODE

По своему назначению функция DECODE аналогична условному выражению CASE, но не поддерживается стандартом ANSI/ISO SQL. Синтаксис:

DECODE ({столбец} | {выражение}

{, {значение 1}, {результат 1}

[, {значение 2}, {результат2}

…

[, {значение N}, {результат N}]

[, {результат default}]);

Значение {столбец} | {выражение} сравнивается со значениями {значение i} и возвращается результат первого совпадения.

Если совпадения не будет, то возвращается значение {результат default}. Если {результат default} отсутствует, то функция DECODE вернет результат NULL.

Следует обратить внимание на то, что функция DECODE требует точного совпадения значений и не позволяет использовать операции сравнения>, <и сложные условия. Поэтому возможности функции DECODE уступают возможностям условного выражения CASE.

Пример 3.53. Вывести данные о сотрудниках и размер их премии, которая задана в виде фиксированной суммы, размер которой зависит от отдела, в котором работает сотрудник

SELECT department_id, employee_id, first_name, last_name,

job_id, salary,

DECODE (department_id, 10, 1000,30, 1200,60, 1500,500)

AS bonus

FROM Employees

WHERE department_id in (10,30,40,60,100)

ORDER BY department_id;

Результат выполнения этого запроса совпадает с результатом выполнения запроса из примера 3.50.

Рассмотрим еще один пример использования функции DECODE для решения задачи из примера 3.53. Особенностью этой задачи является использование операции сравнения>, которую нельзя использовать в DECODE. Но при решении этой задачи данное ограничение удается обойти. Обратите внимание на то, что число месяцев, которые проработал сотрудник, делится на 60, что соответствует пяти годам работы. Если целая часть результата равна пяти, то это означает что сотрудник проработал не менее 25, но не более 30. Последнее замечание означает, что запросы из примеров 3.53 и 3.55 не эквивалентны и запрос с использованием функции DECODE требует расширения списка значений.

Пример 3.54. Вывести данные о сотрудниках и размере их премии, которая зависит от количества лет, которые проработал сотрудник, используя функцию DECODE

SELECT department_id, employee_id, first_name, last_name, job_id,

hire_date, salary,

DECODE (TRUNC (MONTHS_BETWEEN (SYSDATE, hire_date) /60),

6,3*salary,

5,3*salary,

4, 2*salary,

3, salary,

0.5*salary) As bonus

FROM Employees

WHERE department_id IN (10,30,40,60)

ORDER BY department_id;