WHERE product code = ;p code AND :date of interest BETWEEN date from AND date to;

/ * пример 2 - используется один столбец, действительный по дате */ SELECT р1.price FROM prices pi WHERE pi.product code = :p code AND pl.date from =

{SELECT MAX(p2.date from) FROM prices p2 WHERE p2.product code = :p code AND p2.date from <= :date of interest);

Как обозначать открытость диапазона - большими или неопределенными значениями?

Допустим, что мы решили не исключать из таблицы столбец, содержащий конечную дату. Какое же значение он должен содержать в тех строках, где она неизвестна? Если цена меняется, то мы знаем, что можем получить дату вступления ее в силу. Однако обычно при объявлении новой цены дата окончания ее действия не сообщается или говорится, что она действует до следующего уведомления. Иногда мы можем получить и такую информацию: до 28 апреля товар продается по цене $14,99, а потом - по цене $19,99. В этом случае конечная дата также ?ie задана, и мы должны справиться с этой проблемой. В предыдущих примерах у текущих цен в столбце DATE TO стояло неопределенное значение. Единственный выход в данном случае - использовать искусственно большое значение, например, 31-DEC-4712 (максимальная дата, которую в Oracle может содержать столбец типа DATE). Конечно, это значение достаточно велико, чтобы пережить и вас, и систему, в которой вы сейчас работаете.

Если вы решили использовать большую дату, но считаете, что значение 31-DEC-4712 вам не подходит, возьмите другое значение, однако постарайтесь ввести его в качестве стандарта, которым будут пользоваться все разработчики системы. Это сделает код удобочитаемым и позволит избежать некоторых ловушек. Каких именно? Например, в качестве конечной даты широко используются значения 01-JAN-4000 и 31-DEC-99. Второй вариант потенциально опасен, так как минимум одна база данных из тех. где он используется, должна работать 1 января 2000 года. К сожалению, этого не произойдет, если не принять срочных профилактических мер. (См. замечания по этому вопросу в приложении Б.)

Что же выбрать - большое или неопределенное значение? Как видно из следующего примера, использование первого метода упрощает синтаксис типового запроса и делает запрос более легким для понимания.

/* использование искусственной большой даты */

AND :date of sale BETWEEN p.date from AND p.date to /* использование неопределенного значения */ :,т ,

AND :date of sale BETWEEN p.date frotn AND

NVL(p.date to, TO DATE(Ol-DEC-4712, DD-MON-YYYY))

/* альтернативная форма: явная проверка на неопределенное значение */

AND ( (:date of sale BETWEEN p.date frotn AND p.date to)

OR (:date of sale >= p.date frotn AND p.date to IS NOLL))

Однако давайте рассмотрим, с какими проблемами мы столкнемся, если будем хранить в столбце DATE FROM искусственно большую дату. Во-первых, это значение каким-то образом необходимо поместить в таблицу. В нынешнее время дружественных компьютерных систем не принято, чтобы пользователь вручную вводил такое значение в поле экранной формы. С позиции пользователя логичнее оставить поле конечной даты пустым, если его значение неизвестно или на данный момент не ограничено.

Если пользователь не заполнит поле конечной даты, в приложении можно проверить это и в процессе вставки или обновления заменить пустое (неопределенное) значение большим. Более того, мы можем создать триггер на этой таблице, который конвертирует неопределенное значение в большое при выполнении операции INSERT или UPDATE, как показано в следующем примере. Этот подход гарантирует, что все приложения будут использовать в качестве большой даты одно и то же значение.

CREATE OR REPLACE TRIGGER price3 null date to

BEFORE INSERT OR UPDATE OF date to ON prices FOR EACH ROW

BEGIN

IF :new.date to IS NULL THEN

:new.date to := TO DATE(Ol-DEC-4712,DD-MON-YYYY); ENF IF; END;

Примечание

Категорически не рекомендуем выполнять эту операцию с помощью DEFAULT при определении столбца DATE TO. Это сработает только при выполнении операции INSERT (но не UPDATE) и только в случае, если вообще не задано никакое значение. Если приложение задает неопределенное значение явно, то в столбце будет неопределенное значение, а не значение по умолчанию.

В общем случае мы не советуем использовать триггер для разрушения значений столбца, т.е. для размещения в нем значения, отличного от заданного пользователем. В этом случае мы считаем, что пользователь не задал значение, даже если оператор INSERT содержит для этого поля неопределенное значение.

И еще несколько слов в заключение. Такое использование триггера подчеркивает важность перехода от версии 7.0 к версии 7.1, где допускается наличие нескольких триггеров для событий с одинаковыми временными параметрами. С помощью этого триггера можно конвертировать

неопределенные значения независимо от других триггеров, например, реализующих правила изменения цен.

Что же произойдет теперь, когда пользователь запросит запись, которую он только что ввел? Если мы ничего не сделаем, то в оставленном пустым поле появится странная дата. Не помню, чтобы я вводил ее , - подумает. пользователь и будет пытаться определить, кто же подмещивает что-то в его; кофе. Следовательно, эту дату необходимо скрыть от пользователей. Поскольку мы не можем создать триггер для операции SELECT, нам придется использовать один из следующих подходов:

1. Заставить каждое приложение конвертировать большую дату в неопределенное значение всякий раз, когда это значение необходимо вывести на экран.

2. Обеспечить, чтобы запросы использовали представления, содержащие соответствующую функцию DECODE.

3. Произвести выборку информации посредством хранимых процедур (инкапсуляция); при этом соответствующее преобразование выполнит хранимая процедура.

Затраты на реализацию этих методов - небольшая плата за преимущества, которые дает использование больших значений и индексов, включающих конечную дату (столбец DATE TO).

В большинстве наблюдаемых нами реализаций применяется первое решение. Мы предпочитаем третье решение. Второе и третье решения выгод1ю отличаются тем, что приложению не нужно знать о существовании правила больших значений. Более того, ему вовсе не обязательно знать, какое именно большое значение используется.

Таким образом, мы рекомендуем всегда заносить большую дату в столбец DATE TO для открытых диапазонов дат. Единственное, о чем нужно помнить - если у пользователей есть какие-либо средства, позволяющие вводить SQL-запросы, то пользователи должны знать об этом методе или их следует ограничить запросами на доступ через представления, скрывающие большое значение.

Сейчас мы перейдем к заключительному пункту дискуссии о неопределенных и больших значениях и обратим ваше внимание на тот факт, что, хотя неопределенные значения и могут встречаться в составных индексных ключах, оптимизатор Oracle не использует индекс для поиска неопределенного значения. Поэтому, если вы собираетесь использовать в запросах поиск по открытым диапазонам дат, настоятельно рекомендуем воспользоваться методом 1 или методом 3. Функция DECODE в методе 2 нарушит (по меньшей мере, частично) применяемое индексирование и, как вы узнаете из следующего раздела, может полностью нарушить индексный поиск дат.