– 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 условных выражений.