В первой части статьи была сделана попытка понять, что есть NULL, и установлены два аспекта этого явления: в зависимости от контекста, NULL удобно интерпретировать либо как признак отсутствия значения, либо как неизвестное значение. Были рассмотрены: отсутствие значения в столбце таблицы, и как этого избежать с помощью ограничений целостности; взаимоотношения NULL и строковых типов VARCHAR2 и CHAR; использование NULL в выражениях и в качестве агрумента встроенных функций Oracle, скалярных и агрегатных.
Во второй части я рассмотрю взаимоотношения NULL и значений типа BOOLEAN в PL/SQL; эффекты, имеющие отношение к NULL и результатам подзапросов, а также к оператору IN; сортировку по столбцу, часть значений в котором отсутствует.
Итак, начну с рассмотрения взаимоотношений NULL и значений типа BOOLEAN в PL/SQL. Поскольку dbms_output.put_line() не справляется с выводом на печать булевых значений, то я создам процедуру для вывода булевых значений вместе с опциональным сообщением:
SQL> CREATE OR REPLACE PROCEDURE printb (
2 b BOOLEAN,
3 m VARCHAR2 DEFAULT NULL) IS
4 BEGIN
5 IF b IS NULL THEN
6 dbms_output.put_line(TRIM(m || ' NULL'));
7 ELSIF b THEN
8 dbms_output.put_line(TRIM(m || ' TRUE'));
9 ELSE
10 dbms_output.put_line(TRIM(m || ' FALSE'));
11 END IF;
12 END;
13 /
Procedure created
С помощью созданной процедуры выведу таблицы истинности для операций NOT, AND и OR:
SQL> BEGIN
2 printb(NOT FALSE, 'NOT FALSE =');
3 printb(NOT TRUE, 'NOT TRUE =');
4 END;
5 /
NOT FALSE = TRUE
NOT TRUE = FALSE
PL/SQL procedure successfully completed
SQL> BEGIN
2 printb(FALSE AND FALSE, 'FALSE AND FALSE =');
3 printb(FALSE AND TRUE, 'FALSE AND TRUE =');
4 printb(TRUE AND FALSE, 'TRUE AND FALSE =');
5 printb(TRUE AND TRUE, 'TRUE AND TRUE =');
6 END;
7 /
FALSE AND FALSE = FALSE
FALSE AND TRUE = FALSE
TRUE AND FALSE = FALSE
TRUE AND TRUE = TRUE
PL/SQL procedure successfully completed
SQL> BEGIN
2 printb(FALSE OR FALSE, 'FALSE OR FALSE =');
3 printb(FALSE OR TRUE, 'FALSE OR TRUE =');
4 printb(TRUE OR FALSE, 'TRUE OR FALSE =');
5 printb(TRUE OR TRUE, 'TRUE OR TRUE =');
6 END;
7 /
FALSE OR FALSE = FALSE
FALSE OR TRUE = TRUE
TRUE OR FALSE = TRUE
TRUE OR TRUE = TRUE
PL/SQL procedure successfully completed
Но в булевых операциях в качестве операндов могут участвовать также NULL'ы, что дает нам следующие дополнительные варианты для операций NOT, AND и OR:
SQL> BEGIN
2 printb(NOT NULL, 'NOT NULL =');
3 END;
4 /
NOT NULL = NULL
PL/SQL procedure successfully completed
SQL> BEGIN
2 printb(NULL AND NULL, 'NULL AND NULL =');
3 printb(FALSE AND NULL, 'FALSE AND NULL =');
4 printb(NULL AND FALSE, 'NULL AND FALSE =');
5 printb(TRUE AND NULL, 'TRUE AND NULL =');
6 printb(NULL AND TRUE, 'NULL AND TRUE =');
7 END;
8 /
NULL AND NULL = NULL
FALSE AND NULL = FALSE
NULL AND FALSE = FALSE
TRUE AND NULL = NULL
NULL AND TRUE = NULL
PL/SQL procedure successfully completed
SQL> BEGIN
2 printb(NULL OR NULL, 'NULL OR NULL =');
3 printb(FALSE OR NULL, 'FALSE OR NULL =');
4 printb(NULL OR FALSE, 'NULL OR FALSE =');
5 printb(TRUE OR NULL, 'TRUE OR NULL =');
6 printb(NULL OR TRUE, 'NULL OR TRUE =');
7 END;
8 /
NULL OR NULL = NULL
FALSE OR NULL = NULL
NULL OR FALSE = NULL
TRUE OR NULL = TRUE
NULL OR TRUE = TRUE
PL/SQL procedure successfully completed
Операция NOT NULL ожидаемо дает NULL в результате. Однако, логические операции AND и OR с операндами NULL не всегда дают в результате NULL! В чем здесь дело?
Вспомним, что результатом операции AND будет FALSE, если хотя бы один из операндов FASLE. Это правило работает и в случае, когда второй операнд неизвестен, то есть, NULL. Аналогично, результатом операции OR будет TRUE, если хотя бы один из операндов TRUE; значение второго операнда не влияет на результат, будь оно известно или неизвестно.
Мы рассмотрели булевы операции с NULL в контексте присваивания, когда результат операции присваивается переменной. В приведенных примерах выполняется присваивание параметру b процедуры printb. Другой контекст, в котором используются булевы выражения в PL/SQL, - это условный контекст, когда результат логического выражения управляет последовательностью выполнения команд. В условном контексте NULL работает как FASLE.
Продемонстрирую это:
SQL> BEGIN
2 IF NULL THEN
3 dbms_output.put_line('Не выполнится');
4 END IF;
5 WHILE NOT NULL LOOP
6 dbms_output.put_line('Не выполнится');
7 END LOOP;
8 LOOP
9 EXIT WHEN NULL;
10 dbms_output.put_line('Я здесь');
11 EXIT WHEN NULL OR TRUE;
12 END LOOP;
13 END;
14 /
Я здесь
PL/SQL procedure successfully completed
Условие в предложении EXIT WHEN NULL OR TRUE; в строке 11 срабатывает, согласно выведенной выше таблице истинности для операции OR.
От взаимоотношений NULL и BOOLEAN перейдем к рассмотрению подзапросов, результат которых интерпретируется как NULL.
Интересно, что следующие два запроса дают одинаковый результат, хотя в первом из них подзапрос не возвращает ни одной строки, а во втором - возвращает одну строку с одним пустым столбцом:
SQL> SELECT * FROM dual WHERE (SELECT * FROM dual WHERE 1 != 1) IS NULL;
DUMMY
-----
X
SQL> SELECT * FROM dual WHERE (SELECT NULL FROM dual) IS NULL;
DUMMY
-----
X
Еще одна демонстрация этого эффекта:
SQL> SELECT
2 NVL((SELECT * FROM dual WHERE 1 != 1), 'NULL') no_rows,
3 NVL((SELECT NULL FROM dual), 'NULL') null_column
4 FROM dual;
NO_ROWS NULL_COLUMN
------- -----------
NULL NULL
В контексте оценки, является ли результат подзапроса NULL'ом, ноль строк (то есть, отсутствие результата) и одна строка со столбцом, содержащим NULL, трактуются одинаково. Однако, дело обстоит иначе с точки зрения оператора EXISTS:
SQL> SELECT * FROM dual WHERE EXISTS (SELECT * FROM dual WHERE 1 != 1);
DUMMY
-----
SQL> SELECT * FROM dual WHERE EXISTS (SELECT NULL FROM dual);
DUMMY
-----
X
SQL> SELECT * FROM dual WHERE NOT EXISTS (SELECT * FROM dual WHERE 1 != 1);
DUMMY
-----
X
SQL> SELECT * FROM dual WHERE NOT EXISTS (SELECT NULL FROM dual);
DUMMY
-----
И с таким поведением трудно не согласиться.
Оператор IN не сопоставляет NULL ни одному из значений в списке, включая NULL:
SQL> SELECT * FROM dual WHERE NULL IN (SELECT * FROM dual WHERE 1 != 1);
DUMMY
-----
SQL> SELECT * FROM dual WHERE NULL IN (SELECT NULL FROM dual);
DUMMY
-----
SQL> SELECT * FROM dual WHERE NULL IN (1, 2, NULL);
DUMMY
-----
Это поведение согласуется с поведением CASE expr WHEN, рассмотренным ранее, а также с тем фактом, что сравнение с NULL всегда дает NULL. Но попробуем использовать оператор IN в ограничении целостности CHECK для ограничения возможных значений столбца:
SQL> ALTER TABLE items ADD (
2 kind NUMBER(1) CHECK (kind IN (1, 2))
3 );
Table altered
Столбец и ограничение целостности добавлены, несмотря на то, что в столбце отсутствуют значения. Можно было бы ожидать, что ограничение целостности не позволит столбцу kind быть пустым и не будет добавлено. Ведь при отсутствии значения в столбце условие NULL IN (1, 2) не выполняется! И все же добавленный столбец пуст, и мы можем вставить новую строку с пустым значением для столбца kind:
SQL> INSERT INTO items VALUES (10, 'линейка', NULL);
1 row inserted
SQL> SELECT * FROM items ORDER BY id;
ID NAME KIND
---------- -------------------- ----
1 скрепка
2
3
4 ножницы
5
6 степлер
8 дырокол
9
10 линейка
9 rows selected
Есть ли этому рациональное объяснение? Пожалуй. Условие в ограничении целостности kind IN (1, 2) нарушено тогда, когда выполняется противоположное ему условие, NOT (kind IN (1, 2)) или kind NOT IN (1, 2). Но в случае, когда kind есть NULL, не выполняются ни прямое, ни противоположное условия!
SQL> SELECT * FROM dual WHERE NULL IN (1, 2);
DUMMY
-----
SQL> SELECT * FROM dual WHERE NULL NOT IN (1, 2);
DUMMY
-----
Поэтому для того, чтобы гарантировать присутствие значений в столбце, недостаточно оператора IN в ограничении целостности CHECK. Нужно либо добавить дополнительное условие AND kind IS NOT NULL, либо сочетать ограничение целостности CHECK с NOT NULL для данного столбца. Последний вариант мне кажется предпочтительным.
Тысячу раз прав был Остин О'Мэлли, когда говорил: дыра это просто ничто, но в ней можно сломать себе шею.
Теперь отсортируем значения в таблице items по столбцу name:
SQL> SELECT id, name FROM items ORDER BY name;
ID NAME
---------- --------------------
8 дырокол
10 линейка
4 ножницы
1 скрепка
6 степлер
3
9
5
2
9 rows selected
Как видим, отсутствие значения в столбце при упорядочивании по столбцу трактуется как наибольшее значение. Чтобы строки с отсутствующими значениями оказались первыми, воспользуемся опцией NULLS FIRST:
SQL> SELECT id, name FROM items ORDER BY name NULLS FIRST;
ID NAME
---------- --------------------
2
3
9
5
8 дырокол
10 линейка
4 ножницы
1 скрепка
6 степлер
9 rows selected
Противоположностью NULLS FIRST является опция NULLS LAST, впрочем, ее действие совпадает с поведением по умолчанию.
В заключение, удаляю таблицы и процедуру, использовавшиеся в экспериментах:
DROP TABLE nullrows;
DROP TABLE items;
DROP PROCEDURE printb;
Комментариев нет:
Отправить комментарий