Эта статья обеспечивает обзор функций, доступных для обработки нулевых значений.
Большинство примеров в этой статье требует следующей таблицы.
DROP TABLE null_test_tab;
CREATE TABLE null_test_tab (
id NUMBER,
col1 VARCHAR2(10),
col2 VARCHAR2(10),
col3 VARCHAR2(10),
col4 VARCHAR2(10)
);
INSERT INTO null_test_tab values (1, 'ONE', 'TWO', 'THREE', 'FOUR');
INSERT INTO null_test_tab values (2, NULL, 'TWO', 'THREE', 'FOUR');
INSERT INTO null_test_tab values (3, NULL, NULL, 'THREE', 'FOUR');
INSERT INTO null_test_tab values (4, NULL, NULL, 'THREE', 'THREE');
COMMIT;
Если мы запрашиваем данные в таблице, мы видим следующий вывод
SQL> SELECT * FROM null_test_tab ORDER BY id;
ID COL1 COL2 COL3 COL4
---------- ---------- ---------- ---------- ----------
1 ONE TWO THREE FOUR
2 TWO THREE FOUR
3 THREE FOUR
4 THREE THREE
4 rows selected.
SQL>
Помните, сравнения с null всегда приводят к null, таким образом, запросы не могут использовать операторы сравнения, такие как "=" или"! = ".
SQL> SELECT * FROM null_test_tab WHERE col1 = NULL ORDER BY id;
no rows selected
SQL>
Вместо этого они должны использовать операторы IS NULL или NOT IS NULL
SQL> SELECT * FROM null_test_tab WHERE col1 IS NULL ORDER BY id;
ID COL1 COL2 COL3 COL4
---------- ---------- ---------- ---------- ----------
2 TWO THREE FOUR
3 THREE FOUR
4 THREE THREE
3 rows selected.
SQL>
NVL
Функция NVL позволяет вам заменять null значения значением по умолчанию. Если значение в первом параметре - null, функция возвращает значение во втором параметре. Если первый параметр - какое-либо значение кроме нуля, это значение возвращается неизменным.
Мы знаем, что COL1 в тестовой таблице содержит null во всех строках кроме первой. Используя функцию NVL мы заменяем нулевые значения 'НУЛЕМ'.
SQL> SELECT id, NVL(col1, 'ZERO') AS output FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 ONE
2 ZERO
3 ZERO
4 ZERO
4 rows selected.
SQL>
DECODE
Функция DECODE не определена специально для того, чтобы обработать null значения, но она может использоваться похожим способом с NVL, как показано следующим примером.
SQL> SELECT id, DECODE(col1, NULL, 'ZERO', col1) AS output FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 ONE
2 ZERO
3 ZERO
4 ZERO
4 rows selected.
SQL>
NVL2
Функция NVL2 принимает три параметра. Если первое значение параметра не null, она возвращает значение во втором параметре. Если первое значение параметра - null, она возвращает третий параметр.
Следующий запрос показывает NVL2 в действии.
SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 TWO
2 THREE
3 THREE
4 THREE
4 rows selected.
SQL>
У первой строки в тестовой таблице есть не null значение в COL1, таким образом, значение COL2 возвращено. Все другие строки содержат null в COL1, таким образом, значение COL3 возвращено.
COALESCE
COALESCE функция была представлена в Oracle 9i. Это принимает два или больше параметра и возвращает первое не null значение в списке. Если все параметры содержат null значения, то возвращает null.
SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 ONE
2 TWO
3 THREE
4 THREE
4 rows selected.
SQL>
NULLIF
Функция NULLIF была представлена в Oracle 9i. Она принимает два параметра и возвращает null, если оба параметра равны. Если они не равны, будет возвращено значение первого параметра.
В нашей тестовой таблице значения COL3 и COL4 равны в строке 4, таким образом, мы ожидаем, что будет возвращен null для этой строки, используя следующий запрос.
SQL> SELECT id, NULLIF(col3, col4) AS output FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 THREE
2 THREE
3 THREE
4
4 rows selected.
SQL>
LNNVL
Функция LNNVL была доступна по крайней мере c Oracle 9i, но была недокументирована до Oracle 11g. Она используется в WHERE, чтобы оценить условие. Если это условие оценивает в ложь или неизвестность, то возвращает true. Если условие оценивается к истине, то возвращает false.
SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id;
ID COL3
---------- ----------
1 THREE
1 row selected.
SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 = 'TWO') ORDER BY id;
ID COL3
---------- ----------
3 THREE
4 THREE
2 rows selected.
SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 != 'TWO') ORDER BY id;
ID COL3
---------- ----------
1 THREE
2 THREE
3 THREE
4 THREE
4 rows selected.
SQL>
NANVL
Функция NANVL была представлена в Oracle 10i для использования с BINARY_FLOAT и типами данных BINARY_DOUBLE, которые могут содержать специальное предложение "Не Число" или значение "NaN". Функция подобна NVL.
DROP TABLE nanvl_test_tab;
CREATE TABLE nanvl_test_tab (
id NUMBER,
col1 BINARY_DOUBLE
);
INSERT INTO nanvl_test_tab VALUES (1, 1234.5678);
INSERT INTO nanvl_test_tab VALUES (2, 'INF');
INSERT INTO nanvl_test_tab VALUES (3, '-INF');
INSERT INTO nanvl_test_tab VALUES (4, 'NaN');
COMMIT;
В результате запроса мы видим следующие данные.
SELECT * FROM nanvl_test_tab ORDER BY id;
ID COL1
---------- ----------
1 1.235E+003
2 Inf
3 -Inf
4 Nan
4 rows selected.
SQL>
Затем, мы запрашиваем данные снова, но преобразовываем любые значения "'NaN'" в "0" использованием функции NANVL.
SELECT id, col1, NANVL(col1, 0) AS output FROM nanvl_test_tab;
ID COL1 OUTPUT
---------- ---------- ----------
1 1.235E+003 1.235E+003
2 Inf Inf
3 -Inf -Inf
4 Nan 0
4 rows selected.
SQL>
SYS_OP_MAP_NONNULL
Мы видели, что сравнение "null = null" будет всегда возвращать false, но иногда вы хотите, чтобы оно возвратило true. Возможно заставить это произойти, используя NVL и DECODE функции, но это преобразовывает нулевое значение в другое значение, так что вы надеетесь, что никогда не будет присутствовать в столбце или переменной.
SELECT id, 'col1=col2'
FROM null_test_tab
WHERE NVL(col1, '!null!') = NVL(col2, '!null!');
ID 'COL1=COL
---------- ---------
3 col1=col2
4 col1=col2
2 rows selected.
SQL>
SELECT id, 'col1=col2'
FROM null_test_tab
WHERE DECODE(col1, NULL, '!null!', col1) = DECODE(col2, NULL, '!null!', col2);
ID 'COL1=COL
---------- ---------
3 col1=col2
4 col1=col2
2 rows selected.
SQL>
Альтернатива должна использовать недокументированную функцию SYS_OP_MAP_NONNULL, чтобы позволить нулевые соответствия
SELECT id, 'col1=col2'
FROM null_test_tab
WHERE SYS_OP_MAP_NONNULL(col1) = SYS_OP_MAP_NONNULL(col2);
ID 'COL1=COL
---------- ---------
3 col1=col2
4 col1=col2
2 rows selected.
SQL>
Помните, это - недокументированная функция, так строго говоря она не должна использоваться в производственном приложении.
| < Предыдущая | Следующая > |
|---|
