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

NVL (x,y)

Возвращает x, если x не NUUL, и возвращает y, если x имеет значение NUUL, например: NVL (commission_pct,0).

Рассмотрим примеры использования функции NVL при решении конкретных задач.

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

SELECT employee_id, first_name, last_name, department_id,

salary, NVL (commission_pct,0)

FROM Employees

WHERE department_id IN (30,80)

ORDER BY department_id;

Пример 3.44. Вывести данные о сотрудниках, включая зарплату с учетом комиссионных (полная зарплата), которые работают в отделах 30 и 80, упорядочив их в порядке убывания значений зарплаты с учетом комиссионных

SELECT employee_id, first_name, last_name, department_id,

salary* (1+NVL (commission_pct,0)) AS total_salary

FROM Employees

WHERE department_id IN (30,80)

ORDER BY total_salary DESC;

Псевдонимы столбцов можно использовать в предложении ORDER BY, но нельзя использовать в предложении WHERE.

Пример 3.45. Вывести данные о сотрудниках, включая зарплату с учетом комиссионных, полная зарплата которых больше 15 000, упорядочив их в порядке убывания значений полной зарплаты

SELECT employee_id, first_name, last_name, department_id,

salary* (1+NVL (commission_pct,0)) AS total_salary

FROM Employees

WHERE total_salary> 15000

ORDER BY total_salary DESC;

Правильный вариант решения задачи 3.45:

SELECT employee_id, first_name, last_name, department_id,

salary* (1+NVL (commission_pct,0)) AS total_salary

FROM Employees

WHERE salary* (1+NVL (commission_pct,0))> 15000

ORDER BY total_salary DESC;

Функция NVL2

Расширяет возможности функции NVL. Синтаксис:

NVL2 (x,y1,y2)

Возвращает y1, если x не NUUL, и возвращает y2, если x имеет значение NUUL.

Например:

NVL2 (commission_pct, salary* (1+commission_pct), salary)

Пример 3.46. Вывести данные о сотрудниках, которые работают в отделах 30 и 80, размере премии, которую они должны получить. Размер премии, у сотрудников, которые получают комиссионные, равен зарплате с учетом комиссионных. Размер премии, у сотрудников, которые не получают комиссионные, равен зарплате, увеличенной на 30%

SELECT employee_id, first_name, last_name, department_id,

NVL2 (commission_pct, salary* (1+commission_pct), salary*1.3)

AS prize

FROM Employees

WHERE department_id IN (30,80)

ORDER BY prize DESC;

Функция COALESCE

Предназначена для обработки значений NULL и предоставляет более широкие возможности, чем функции NVL и NVL2. Позволяет отрабатывать несколько значений NULL. Синтаксис:

COALESCE (y1,y2,…yn)

Возвращает первое не NULL значение.

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

– bonus + salary * (1 + commission_pct) – если сотруднику положен бонус и он получает комиссионные;

– bonus + salary – если сотруднику положен бонус, но он не получает комиссионные;

– salary * (1 + commission_pct) – если сотруднику не положен бонус, но он получает комиссионные;

– salary – если сотруднику не положен бонус и он не получает комиссионные.

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

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

SELECT employee_id, first_name, last_name, department_id,

COALESCE (bonus + salary* (1+commission_pct),

bonus + salary, salary* (1+commission_pct), salary)

AS total_salary

FROM Employees

ORDER BY total_salary DESC;

Довольно часто значение столбца, которое должен вернуть 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.