Основы программирования в СУБД Oracle. SQL+PL/SQL. - страница 22

Функция NVL

Позволяет заменить значение NULL фактическим значением. Синтаксис:

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, это правило начисления зарплаты можно реализовать следующим образом.