nullable oracle что это
Заметка про NULL
Основные положения
Для удобства сделаем процедуру, печатающую состояние булевого параметра:
и включим опцию печати сообщений на консоль:
Привычные операторы сравнения пасуют перед NULLом:
Сравнение с NULLом
Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:
DECODE идёт против системы:
Пример с составными индексами находится в параграфе про индексы.
Логические операции и NULL
В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :
Отрицание неизвестности даёт неизвестность:
Операторы IN и NOT IN
Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL
Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE ).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.
Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL) :
Попробуем теперь с NOT IN :
Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3 :
Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.
NULL и пустая строка
Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.
NULL и пустая строка эквивалентны:
непременный атрибут жёсткого спора:
Длина пустой строки не определена:
Сравнение с пустой строкой невозможно:
Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…
Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом » (пустая строка), но не позволяют сохранить элемент с индексом NULL:
Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.
Математика NULLа
Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.
Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:
С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.
NULL и агрегатные функции
Таблица с данными. Используется ниже много раз:
Пустые значения игнорируются агрегатами:
Набор данных только из NULLов:
Пустой набор данных:
NULL в OLAP
Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:
Проверяем дуализм NULLа в многомерном кубе:
Хорошо ли вы помните nullable value types? Заглядываем «под капот»
В последнее время модной темой стали nullable reference types. Однако старые добрые nullable value types никуда не делись и всё так же активно используются. Хорошо ли вы помните нюансы работы с ними? Предлагаю освежить или проверить свои знания, ознакомившись с этой статьёй. Примеры кода на C# и IL, обращения к спецификации CLI и коду CoreCLR прилагаются. Начать предлагаю с интересной задачки.
Примечание. Если вас интересуют nullable reference types, можете познакомиться с несколькими статьями моих коллег: «Nullable Reference типы в C# 8.0 и статический анализ», «Nullable Reference не защищают, и вот доказательства».
Посмотрите на пример кода ниже, и ответьте, что будет выведено в консоль. И, что не менее важно, почему. Только давайте сразу договоримся, что вы ответите, как есть: без подсказок компиляторов, документации, вычитывания литературы или чего-то подобного. 🙂
Что ж, давайте немного порассуждаем. Возьмём несколько основных направлений мысли, которые, как мне кажется, могут возникнуть.
1. Исходим из того, что int? — ссылочный тип.
Давайте рассуждать так, что int? — это ссылочный тип. В таком случае в а будет записано значение null, оно же будет записано и в aObj после присвоения. В b будет записана ссылка на какой-то объект. Она также будет записана и в bObj после присвоения. В итоге, Object.ReferenceEquals примет в качестве аргументов значение null и ненулевую ссылку на объект, так что…
Всё очевидно, ответ — False!
2. Исходим из того, что int? — значимый тип.
А может быть вы сомневаетесь, что int? — ссылочный тип? И вы уверены в этом, несмотря на выражение int? a = null? Что ж, давайте зайдём с другой стороны и будем отталкиваться от того, что int? — значимый тип.
В таком случае выражение int? a = null выглядит немного странно, но предположим, что это опять в C# сахара сверху насыпали. Получается, что a хранит какой-то объект. b тоже хранит какой-то объект. При инициализации переменных aObj и bObj будет произведена упаковка объектов, хранимых в a и b, в результате чего в aObj и в bObj будут записаны разные ссылки. Получается, что Object.ReferenceEquals в качестве аргументов принимает ссылки на разные объекты, следовательно…
Всё очевидно, ответ — False!
Всё очевидно, ответ — False!
Для тех, кто отталкивался от значимых типов, — если у вас вдруг закрались какие-то сомнения про сравнение ссылок, то можно посмотреть документацию по Object.ReferenceEquals на docs.microsoft.com. В частности, там тоже затрагивают тему значимых типов и упаковки/распаковки. Правда, там описывается кейс, когда экземпляры значимых типов передаются непосредственно в метод, мы же упаковку вынесли отдельно, но суть та же.
When comparing value types. If objA and objB are value types, they are boxed before they are passed to the ReferenceEquals method. This means that if both objA and objB represent the same instance of a value type, the ReferenceEquals method nevertheless returns false, as the following example shows.
Казалось бы, здесь статью можно и закончить, вот только… правильный ответ — True.
Что ж, давайте разбираться.
Разбираемся
Есть два пути — простой и интересный.
Простой путь
Интересный путь
На этой тропинке нам будет недостаточно документации. Она описывает поведение, но не отвечает на вопрос ‘почему’?
Что такое на самом деле int? и null в соответствующем контексте? Почему это работает так? В IL коде используются разные команды или нет? Отличается поведение на уровне CLR? Ещё какая-то магия?
Начнём с разбора сущности int?, чтобы вспомнить основы, и постепенно дойдём до разбора первоначального кейса. Так как C# — язык достаточно «приторный», периодически будем обращаться к IL коду, чтобы смотреть в суть вещей (да, документация по C# — не наш путь сегодня).
int?, Nullable
Здесь рассмотрим основы nullable value types в принципе (что из себя представляет, во что компилируются в IL и т.п.). Ответ на вопрос из задания рассмотрен в следующем разделе.
Рассмотрим фрагмент кода.
Несмотря на то, что на C# инициализация этих переменных выглядит по-разному, для всех них будет сгенерирован один и тот же IL код.
Как видно, в C# всё от души сдобрено синтаксическим сахаром, чтобы нам с вами жилось лучше, по факту же:
С инициализацией по умолчанию мы разобрались — соответствующий IL код мы видели выше. Что же происходит здесь, когда мы хотим проинициализировать aVal значением 62?
Взглянем на IL код:
Опять же, ничего сложного — на evaluation stack загружается адрес aVal, а также значение 62, после чего вызывается конструктор с сигнатурой Nullable (T). То есть два следующих выражения будут полностью идентичны:
В этом же можно убедиться, опять взглянув на IL код:
А что же касается проверок? Например, что на самом деле представляет из себя код следующего вида?
Правильно, для понимания вновь обратимся к соответствующему IL коду.
Для удобства работы тип Nullable определяет:
Упаковка Nullable
Напомню, что при упаковке объекта значимого типа в куче будет создан новый объект. Это поведение наглядно иллюстрирует следующий фрагмент кода:
Результатом сравнения ссылок ожидаемо будет false, так как произошло 2 операции упаковки и создание двух объектов, ссылки на которые были записаны в obj1 и obj2.
Результат всё также ожидаем — false.
А теперь вместо 62 прописываем дефолтное значение.
Иии… результатом неожиданно становится true. Казалось бы, имеем всё те же 2 операции упаковки, создание двух объектов и ссылки на два разных объекта, но результат-то — true!
Ага, наверняка опять дело в сахаре, и что-то поменялось на уровне IL кода! Давайте посмотрим.
Как мы видим, везде упаковка происходит идентичным образом — значения локальных переменных загружается на evaluation stack (инструкция ldloc), после чего происходит сама упаковка за счёт вызова команды box, для которой указывается, какой, собственно, тип будем упаковывать.
Обращаемся к спецификации Common Language Infrastructure, смотрим описание команды box и находим интересное примечание касаемо nullable типов:
If typeTok is a value type, the box instruction converts val to its boxed form.… If it is a nullable type, this is done by inspecting val’s HasValue property; if it is false, a null reference is pushed onto the stack; otherwise, the result of boxing val’s Value property is pushed onto the stack.
Отсюда следует несколько выводов, расставляющих точки над ‘i’:
Состояние экземпляра перед упаковкой:
Состояние экземпляра перед упаковкой:
В исходном примере, который был в самом начале статьи, происходит точно то же самое:
Ради интереса заглянем в исходный код CoreCLR из упомянутого ранее репозитория dotnet/runtime. Нас интересует файл object.cpp, конкретно — метод Nullable::Box, который и содержит нужную нам логику:
Здесь всё то, о чём мы говорили выше. Если не храним значение — возвращаем NULL:
Иначе производим упаковку:
Заключение
Ради интереса предлагаю показать пример из начала статьи своим коллегам и друзьям. Смогут ли они дать верный ответ и обосновать его? Если нет, приглашайте их познакомиться со статьёй. Если же смогут — что ж, моё уважение!
Надеюсь, это было небольшое, но увлекательное приключение. 🙂
P.S. У кого-то мог возникнуть вопрос: а с чего вообще началось погружение в эту тему? Мы делали новое диагностическое правило в PVS-Studio на тему того, что Object.ReferenceEquals работает с аргументами, один из которых представлен значимым типом. Вдруг оказалось, что с Nullable есть неожиданный момент в поведении при упаковке. Посмотрели IL код — box как box. Посмотрели спецификацию CLI — ага, вот оно! Показалось, что это достаточно интересный кейс, про который стоит рассказать — раз! — и статья перед вами.
Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод: Sergey Vasiliev. Check how you remember nullable value types. Let’s peek under the hood.
Ограничения
Ограничения являются средством, с помощью которого БД может принуждать к выполнению бизнес-правил и гарантировать что данные соответсвуют модели сущность-связь определённой системным анализом определяя структуры данных приложения. Например, бизнес-аналитик в компании решил что каждый покупатель и каждый заказ должен определяться уникальным числом, что нельзя создать заказ до создания покупателя и каждый заказ должен иметь валидную дату и сумму большую нуля. Это может быть достигнуто путём создания первичных ключей для столбца CUSTOMER_ID таблицы CUSTOMERS и столбца ORDER_ID таблицы ORDERS, создания внешнего ключа для таблица ORDERS ссылающегося на таблицу CUSTOMERS, создания ограничения “not null” для столбца ORDER_DATE (тип данных DATE сам проверит удовлятворяет ли введённое значение типа дата или нет) и ограничения проверки для столбца ORDER_AMOUNT таблицы ORDERS.
Если какой либо DML запрос к таблице с ограничениями нарушит ограничение, тогда весь запрос будет отменем автоматически. Надо помнить что DML запрос может обновлять много строк и частично обновление было успешным до нарушения ограничения для конкретной строки. Если запрос это часть транзакции состоящей из нескольких запросов, то результат выполнения запросов которые уже выполнились успешно остаётся но не подтверждённым.
A constraint violation will force an automatic rollback of the entire statement that hit the problem, not just the single action within the statement, and not the entire transaction.
Типы ограничений
Ограничения поддерживаемые Orace это
У ограничений есть имя. Хорошей практикой считается именовать их согласно конвенции имен, но если явно не указывать имя, Oracle будет использовать сгенерированное имя.
Ограничение уникальности
Ограничение уникальности назначает столбец (или группу столбцов) для которых значение должно быть разным для каждой строки в таблице. Если ограничение настроено для одного столбца, это называется столбец-ключ (key column). Если ограничение состоит из нескольких столбцов (называется составной ключ (composite key)), столбцы не должны быть одинакового типы и располагаться последовательно в таблице.
Странностью уникального ограничения можно считать возможность добавления значения NULL в столбцы ключа; более того можно иметь неограниченное количество строк со значение NULL в столбцах ключа. Таким образом запрос строк по ключу ограничения гарантирует одну строку для значения – пока не используется NULL, в таком случае все строки со значением NULL будут результатом запроса.
It is possible to insert many rows with NULLs in a column with a unique constraint. This is not possible for a column with a primary key constraint.
Ограничения уникальности работают с помощью индексов. Когда мы определяем ограничение, Oracle проверит существует ли индекс для столбцов ограничения, и если индекс не сущетвует, он будет создан. Затем когда происходи попытка вставки новой строки, Oracle просматривает индекс на существование значения ключа; если значение сущесвтует – строка будет отклонена. Индекс ограничения – B* Tree индекс, и он не хранит значения NULL, вот почему ограничение не работает для NULL (т.е. можно добавлять много строк со значением NULL). Как мы обсуждали выше, целью индексов является не только обеспечение работоспособности механизма ограничений, но и повышение производительности запросов с секцией WHERE и объединением таблиц. Если в запросе используется WHERE key_column IS NULL – Oracle не может использовать индекс (потому что индекс не хранит значения NULL) и всегда будет использовано полное чтение таблицы.
Ограничения NOT-NULL
Ограничения NOT-NULL принуждают указать данные для столбцов ключа. Эти ограничения накладываются на столбец и иногда их называют обязательными для заполнения столбцами (mandatory columns). Если несколько столбцов должны иметь значения, то вы не можете создать одно ограничение для группы – необходимо создавать ограничения для каждого столбца.
Любая попытка записи строки без указания значения для обязательного столбца вернёт ошибку. Возможно обойти эту проверку указав директиву DEFAULT при создании таблицы.
Первичный ключ
Первичный ключ является средством для поиска любой одной строки в таблице. Реляционная парадигма утверждает что каждая таблица должна иметь первичный ключ: столбец (или группу столбцов) которые можно использовать для выделения каждой строки. Oracle позволяет создавать таблицы (как и некоторые другие РСУБД) без первичного ключа.
Реализацией первичного ключа является комбинация ограничений целостности и обязательности. Значение в столбцах ключе должно быть уникальным и не NULL. Так же как и для ограничения уникальности индекс должен существовать для столбцов ключа, иначе он будет создан. У таблицы может быть только один первичный ключ. Но у таблицы может быть сколько угодно ограничений уникальности и not-null ограничений. Если у вас есть несколько столбцов которые должны быть уникальны и обязательны вы можете использовать часть из них для создания первичного ключа, а для остальных создать ограничения уникальности и обязательности. Например таблица сотрудников: все сотрудники должны иметь уникальный номер (первичный ключ) и также значения email, номер соц. страха должны быть уникальны и обязательны для заполнения.
Unique and primary key constraints need an index. If one does not exist, one will be created automatically.
Внешние ключи
Внешний ключ определяет отношение родитель-наследник. Это ограничение назначает столбец (или столбцы) в дочерней таблице которые должны соответствовать столбцу(ам) первичного ключа родительской таблицы. Соответствующие столбцы необязательно должны иметь одинаковые названия, но они должны быть одного типа данных. Внешний ключ определяет отношения один-ко-многим в третьей нормальной форме.
Если у родительской таблицы есть ограничение уникальности вместе (или вместо) первичного ключа, эти столбцы тоже могут использоваться как основа для внешнего ключа, даже если они не обязательны (nullable).
A foreign key constraint is defined on the child table, but a unique or primary key constraint must already exist on the parent table.
Как и ограничение уникальности внешний ключ допускает значение NULL в столбцах ключа. Вы можете вставить строки в дочернюю таблицу со значением NULL в столбцах внешнего ключа – даже если в родительской таблице нет строки со значением NULL. Это создаст строки призраки (orphaned rows) и может вызвать определённые недоразмения. Как правило все столбцы ограничения уникальности и все столбцы внешнего ключа лучше определять вместе с ограничениями not-null: так же это обычно требование бизнеса.
При попытки вставить строку в дочернюю таблицу со значением ключа которого нет в родительской таблице – произойдёт ошибка выполнения запроса. Также попытка удаления строки в родительской таблице вернёт ошибку если существуют строки в дочерней которые ссылаются на эту строке. Существует два способа обойти это ограничение. Во-первых, ограничения могут быть созданы с директивой ON DELETE CASCADE. Это значит что если мы удаляется строка из родительской таблицы – Oracle автоматически найдёт все строки ссылающиеся на эту строку в дочерней таблице и удалит их тоже. Менее кардинальный способ – использование директивы ON DELETE SET NULL. В этом случае если строка родительской таблицы удаляется – Oracle найдёт все соответствующие строки в дочерней таблице и установит значение ключа в NULL. Это значит что строки в дочерней таблице станут строками-призраками – но они будут существовать в таблице. Если столбцы внешнего ключа имеют ограничение not-null – то удаление из родительской таблицы вернёт ошибку. Нельзя ни удалить ни выполнить операцию TRUNCATE для родительской таблицы, даже если нет ни одной строки в дочерней таблице. Даже если использованы ON DELETE SET NULL или ON DELETE CASCADE.
Отдельным видом внешнего ключа является внешний ключ ссылающийся сам на себя (self-referencing). Такой ключ определяет отношение где родительский и дочернии строки находятся в одной таблице. Хорошим примером является таблица сотрудников, в которой есть столбец MANAGER_ID. И менеджер и сотрудник являются сотрудниками. Если первичный ключ EMPLOYEE_ID то для внешнего ключа используем EMPLOYEE_ID как родительский столбец и MANAGER_ID как дочерний. Если у сотрудника нет менеджера, то такая строка указывает сама на себя.
Ограничения проверки значения (Check constraints)
Ограничения проверки значения используются для проверки данных на удовлетворение простым правилам, к примеру введённое значение должно попадать в период значений. Правилом должно быть выражение результатом проверки которого будет либо ПРАВДА либо ЛОЖЬ. Правила могут использовать литералы, или другие столбцы той же строки и они могут использовать некоторые функции. Сколько угодно правил можно применить к одному ограничение проверки значения, но нельзя использовать подзапросы или такие функции как SYSDATE.
The not-null constraint is in fact implemented as a preconfigured check constraint.
Определение ограничений
Ограничение можно определить при создании таблицы или добавить позже. При создании таблицы, ограничения можно указать в строке определяющей столбец используемые в ограничении, либо в конце оператора создания таблицы. В конце оператора создания вы можете определять ограничения более гибко. В строке определения столбца вы не можете создать ограничение использующее другие столбцы таблицы. При определении ограничения требующего индекс в момент создания таблицы также создастся индекс.
Расмотрим два запроса создания таблицы
1 create table dept(
2 deptno number(2,0) constraint dept_deptno_pk primary key
3 constraint dept_deptno_ck check (deptno between 10 and 90),
4 dname varchar2(20) constraint dept_dname_nn not null);
5 create table emp(
6 empno number(4,0) constraint emp_empno_pk primary key,
7 ename varchar2(20) constraint emp_ename_nn not null,
8 mgr number (4,0) constraint emp_mgr_fk references emp (empno),
11 deptno number(2,0) constraint emp_deptno_fk references dept(deptno)
12 on delete set null,
13 email varchar2(30) constraint emp_email_uk unique,
14 constraint emp_hiredate_ck check (hiredate >= dob + 365*16),
15 constraint emp_email_ck
16 check ((instr(email,’@’) > 0) and (instr(email,’.’) > 0)));
Эти примеры показывают некоторые возможности определения ограничения при создании таблицы. Остальные возможности это
Управление созданием индексов для внешних ключей и ограничений уникальности
Определение времени проверки ограничения: во время вставки (значение по умолчанию) или позже, в момент подтверждения транзакции
Включено ли ограничение (по умолчанию) или отключено
Возможно создать таблицу без ограничений а затем добавить ограничения командой ALTER TABLE. Результат будет одинаковый но определение таблицы будет разбито на несколько запросов вместо одного.
Состояния ограничений
В любой момент времени, каждое ограничение включено или выключена и проверяется или не проверяется. Любая комбинация этих двух состояний возможна
Идеальной ситуацией (и состоянием по умолчанию при определении ограничения) является ENABLE VALIDATE. Это гарантирует что данные корректны и некорректные данные не могут быть добавлены.
Статус DISABLE NOVALIDATE полезен когда загружается много данных в таблицу одним запросом. Данные могут содержать плохие значения, но вместо ошибки выполнения запроса этот статус позволит загрузить данные. Сразу после загрузки данных, переведите состояние в ENABLE NOVALIDATE чтобы предотвратить вставку других неправильных данных пока вы будете проверять уже добавленные данные на удовлетворение ограничениям. И затем опять установите правильное состояние ENABLE VALIDATE.
Примером такой задачи может быть скрипт, читающий данные из активных данных в архив. Преположим что есть ограничение NOT-NULL на столбце архивной таблицы, которого нет у столбца активной
alter table sales_archive modify constraint sa_nn1 disable novalidate;
insert into sales_archive select * from sales_current;
alter table sales_archive modify constraint sa_nn1 enable novalidate;
update sales_archive set channel=’NOT KNOWN’ where channel is null;
alter table sales_archive modify constraint sa_nn1 enable validate;
Проверка ограничений
Ограничения могут проверятся на момент выполнения запроса (ограничение IMMEDIATE) или когда подтверждается вся транзакция (DEFERRED). По умолчанию все ограничения в режиме IMMEDIATE. Это может быть альтернативой использования состояний. Можно переписать скрипт из подпункта «Состояние ограничений» таким образом
set constraint sa_nn1 deferred;
insert into sales_archive select * from sales_current;
update sales_archive set channel=’NOT KNOWN’ where channel is null;
set constraint sa_nn1 immediate;
Для поддержки отложенного (DEFERRED) ограничения оно должно быть создано используя синтаксис
alter table sales_archive add constraint sa_nn1
check (channel is not null) deferrable initially immediate;
То есть невозможно сделать ограничение отложенным если директива deferrable не была указана при создании. В нашем случае ограничение SA_NN1 по умолчанию проверяется на момент вставки строки (или изменения), но проверку можно отложить до подтверждения транзакции. Часто отложенные ограничения используются для внешних ключей. Если операции обновления и изменения затрагивают и дочернюю и родительскую таблицу, и если внешний ключ не отложенный то операция может выполниться с ошибкой в зависимости от порядка обработки строк.
Изменение состояния ограничения затрагивает все сессии. Изменение времени проверки ограничения (IMMEDIATE или DEFERRED) затрагивает текущую сессию, но начальное состояние применяется ко всем сессиям.
By default, constraints are enabled and validated, and they are not deferrable.