Учебники

Использование условных выражений

Май 22, 2019

Общие функции используются для обработки значений NULL в базе данных. Целью общих функций обработки NULL является замена значений NULL альтернативными значениями. Мы кратко рассмотрим эти функции ниже.

NVL

Функция NVL заменяет альтернативное значение значением NULL.

Синтаксис: 

NVL( Arg1, replace_with )

В синтаксисе оба параметра являются обязательными. Обратите внимание, что функция NVL работает со всеми типами данных. А также, что тип данных исходной строки и замены должен быть в совместимом состоянии, то есть либо одинаковым, либо неявно конвертируемым в Oracle.

Если arg1 является символьным значением, то oracle преобразует замещающую строку в тип данных, совместимый с arg1, перед их сравнением и возвращает VARCHAR2 в наборе символов expr1. Если arg1 является числовым, то Oracle определяет аргумент с наибольшим числовым приоритетом, неявно преобразует другой аргумент в этот тип данных и возвращает этот тип данных.

Оператор SELECT ниже будет отображать ‘n / a’, если сотрудник еще не назначен на какую-либо работу, т.е. JOB_ID равен NULL. В противном случае будет отображаться фактическое значение JOB_ID. 

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

В качестве усовершенствования по сравнению с NVL, Oracle представила функцию для замены значения не только для значений столбцов NULL, но и для столбцов NOT NULL. Функция NVL2 может использоваться для замены альтернативного значения на NULL, а не на значение NULL.

Синтаксис: 

NVL2( string1, value_if_NOT_null, value_if_null )

Оператор SELECT ниже будет отображать «Bench», если JOB_CODE для сотрудника равен NULL. Для определенного ненулевого значения JOB CODE будет отображаться постоянное значение Job Assigned. 

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

Функция NULLIF сравнивает два аргумента expr1 и expr2. Если expr1 и expr2 равны, он возвращает NULL; иначе он возвращает expr1. В отличие от другой нулевой функции обработки, первый аргумент не может быть NULL.

Синтаксис: 

NULLIF (expr1, expr2)

Обратите внимание, что первый аргумент может быть выражением, которое оценивается как NULL, но не может быть литералом NULL. Оба параметра являются обязательными для выполнения функции.

Приведенный ниже запрос возвращает NULL, поскольку оба входных значения 12 равны. 

SELECT	NULLIF (12, 12)
FROM DUAL;

Аналогичным образом, приведенный ниже запрос возвращает ‘SUN’, поскольку обе строки не равны. 

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

COALESCE

Функция COALESCE, более общая форма NVL, возвращает первое ненулевое выражение в списке аргументов. Требуется минимум два обязательных параметра, но максимальные аргументы не имеют ограничений

Синтаксис: 

COALESCE (expr1, expr2, ... expr_n )

Рассмотрим приведенный ниже запрос SELECT. Он выбирает первое ненулевое значение, введенное в поля адреса для сотрудника. 

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

Интересно, что работа функции COALESCE аналогична конструкции IF..ELSIF..ENDIF. Запрос выше может быть переписан как — 

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

Условные функции

Oracle предоставляет условные функции DECODE и CASE для наложения условий даже в операторе SQL.

Функция ДЕКОД

Функция является SQL-эквивалентностью условного процедурного оператора IF..THEN..ELSE. DECODE работает со значениями / столбцами / выражениями всех типов данных.

Синтаксис: 

DECODE (expression, search, result [, search, result]... [, default])

Функция DECODE сравнивает выражение с каждым поисковым значением по порядку. Если между выражением и аргументом поиска существует равенство, он возвращает соответствующий результат. В случае отсутствия совпадения возвращается значение по умолчанию, если оно определено, иначе NULL. В случае любого несовпадения совместимости типов, oracle внутренне делает возможное неявное преобразование для возврата результатов.

По сути, Oracle считает, что при работе с функцией DECODE два нуля эквивалентны. 

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

Если выражение равно нулю, Oracle возвращает результат первого поиска, который также равен нулю. Максимальное количество компонентов в функции DECODE составляет 255. 

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

CASE выражение

Выражения CASE работают по той же концепции, что и DECODE, но отличаются синтаксисом и использованием.

Синтаксис: 

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

Поиск Oracle начинается слева и двигается вправо, пока не найдет истинное условие, а затем возвращает выражение результата, связанное с ним. Если не найдено ни одного условия истинного, а предложение ELSE существует, Oracle возвращает результат, определенный с помощью else. В противном случае Oracle возвращает ноль.

Максимальное количество аргументов в выражении CASE — 255. Все выражения учитываются в этом пределе, включая начальное выражение простого выражения CASE и необязательное выражение ELSE. Каждая пара WHEN … THEN считается двумя аргументами. Чтобы избежать превышения этого предела, вы можете вкладывать выражения CASE так, чтобы само return_expr было выражением CASE.

91
Share: