Создание, удаление и изменение структуры таблиц

Типы BINARY_FLOAT и BINARY_DOUBLE

Эти два типа представляют собой второй после NUMBER используемый в Oracle формат хранения чисел, определяемый стандартом IEEE 754 для 32- и 64- разрядного внутреннего представления. Стандарт IEEE 754 реализован аппаратно во многих видах процессоров и программно в ряде языков программирования (Java).

Стандарт IEEE 754 определяет больше, чем просто формат хранения чисел. Он, например, предусматривает особые значения "не число" (Not a Number) и +/? бесконечность. В Oracle точно воспроизведен формат хранения, но не все прочие подробности — стандарта IEEE 754. Возможность сослаться на особые "значения" дают следующие обозначения:

BINARY_FLOAT_NAN 
BINARY_FLOAT_INFINITY 
BINARY_DOUBLE_NAN 
BINARY_DOUBLE_INFINITY 

Например:

SQL> SELECT -BINARY_FLOAT_INFINITY FROM dual;
-BINARY_FLOAT_INFINITY
----------------------
                  -Inf

Из-за несовместимости форматов простая передача данных из вида NUMBER в BINARY_FLOAT/DOUBLE и обратно может приводить к потере точности. Зато при общении СУБД посредством этих двух форматов с внешними средами, работающими на основе стандарта IEEE 754, точность, наоборот, будет сохраняться.

Строки текста

"Обычные" строки ("короткие" и в основной кодировке БД) хранятся в полях типов VARCHAR2 ( n ) и CHAR ( n ) . n задает для VARCHAR2 максимально допустимое число хранимых символов (длиною до 4000 байт с версии 8 и до 2000 байт ранее), а для CHAR — всегда одно и то же, фиксированное (до 2000 байт).

"Короткие" строки в дополнительной (так называемой "национальной") кодировке БД, которая всегда многобайтовая, хранятся в полях типов NVARCHAR2 (n) и NCHAR (n).

Для многобайтовой кодировки длину можно указывать как в символах (CHAR n), так и в байтах (BYTE n): по умолчанию считается последнее. Хотя это делается и нечасто, но при создании БД основной кодировкой может быть объявлена многобайтовая, поэтому определения вида VARCHAR2 ( CHAR 5 ) или CHAR ( BYTE 10 ) также приемлемы.

Типы CLOB и NCLOB (character LOB, large object, и national character LOB) используются для хранения "больших" строк текста длиною до 4 Гб — 1 до версии 9 включительно и до нескольких Тб начиная с версии 10. Сами значения этих типов обычно хранятся отдельно от обычных данных таблицы и доступ к ним осуществляется посредством так называемых "локаторов", однако для программирования на SQL эти подробности часто могут быть незаметны.

Тип VARCHAR2 не определен стандартом SQL и отличается от типа VARCHAR тем, что полагает строку из нуля символов отсутствующей строкой. Тип CLOB воплощен в Oracle в соответствии со стандартом.

Типы STRING, CHARACTER VARYING, NATIONAL CHARACTER VARYING и другие, совместимые с ANSI/ISO, реализуются с помощью VARCHAR2 и NVARCHAR2 и в Oracle вторичны.

Строки байтов

Тип RAW ( n ) аналогичен VARCHAR2 ( n ) с разницей максимально разрешенной длины — вплоть до 2000 байтов.

Тип BLOB (binary LOB) аналогичен CLOB и по сути описывает файл (поток байтов), возможно, очень большой и размещаемый в БД (речь при этом не идет о воспроизведении в БД Oracle файловой системы). С версии 11 такое помещение файла в БД Oracle может сопровождаться сокращением объемов хранения и оказаться выгодным с точки зрения компактности.

Тип BFILE (binary file) аналогичен BLOB, но только сам поток байтов хранится вне БД, а именно в файле. Утилитарно можно полагать его типом ссылки на файл ОС, где работает СУБД, то есть "на сервере". Тип может показаться удобным, так как облегчает доступ к данным, достижимым из Oracle, со стороны посторонних программ; однако он имеет ту особенность, что сами данные, расположенные вне БД, не затрагиваются процедурами резервного копирования и восстановления, а также командами управления транзакциями. В БД хранится только ссылка на внешний файл.

LONG и LONG RAW — устаревшие типы, сохраняемые ради обратной совместимости. Например, они встречаются в некоторых системных таблицах, спроектированных в прежние времена.

Моменты и интервалы времени

Тип DATE в Oracle не совпадает с одноименным типом в стандарте SQL и рассчитан на хранение одновременно шести компонентов момента времени: года, месяца, числа, часа, минут и секунд. По сути, он имеет в Oracle собственную, нестандартную реализацию. Шестикомпонентность типа DATE доставляет неудобства, когда требуется сохранить в БД только дату или только время суток.

Типы TIMESTAMP стандартны. В основном варианте TIMESTAMP хранит те же шесть компонент, что и DATE, но с точностью до наносекунд. Максимально допускаемую точность можно намеренно ограничить, указав в скобках n от 0 до 9. В расширенных вариантах тип включает еще зону времени (часовой пояс).

Интервальные типы INTERVAL YEAR TO MONTH и INTERVAL DAY TO SECOND соответствуют стандарту и позволяют хранить "грубые" интервалы (исчисляемые годами и месяцами) и "точные" (исчисляемые днями, часами, минутами и секундами).

Общие свойства типов

У типов данных в Oracle имеются общие свойства:

• у всех типов дополнительно ко множеству допустимых величин наличествует особый символ NULL;
• большинство типов допускает сравнение на равенство.

Пропущенные значения и NULL

NULL можно воспринимать формально (и механически следовать правилам выполнения операций с NULL), но пытаться интерпретировать эти обозначения в столбце таблицы можно по-разному: как "значение отсутствует" (например, "сотрудник не получал комиссионных") и как "неизвестно какое" из допустимых "значение" (например, "сотрудник получил какие-то комиссионные, неизвестные БД"). К сожалению это не одно и то же, равно как и наделения NULL этими двумя смыслами в жизни недостаточно (хотя и хватает для описания 99% возникающих ситуаций). По этим причинам, хотя на первый взгляд возможность опустить значение в столбце за его отсутствием может и показаться привлекательной, последующая работа с такими данными способна принести программисту значительно больше неприятностей из-за необходимости учета при составлении запросов неформализованных в схеме сведений о данных, усложнения запросов и возникающих рисков ошибиться при программировании. Примеры подобных неприятностей обозначены в разных местах текста ниже.

Стандарт SQL допускает NULL, но оговаривает, что во имя надежности обращения к данным программисту следует всячески избегать пропусков значений в столбцах либо уж употреблять их лишь в смысле "значение неизвестно" (unknown). Стандарт называет NULL особым значением, общим для всех типов, и обрекает себя тем самым на критику со стороны специалистов, полагающих, что правильнее назвать NULL символом^{1 Смотри книгу Дейт К. Дж., Дарвен Х. Основы будущих систем баз данных. Третий манифест. Перевод с английского. Изд. 2, 2004. Примечательно, что один из критиков — автор этой книги, в свое время входил в состав одного из национальных комитетов ISO по стандартизации SQL; это красноречиво характеризует демократические институты, к числу которых относится ISO.}, так как он не удовлетворяет признакам значения. Действительно, если x "имеет значение" NULL, то x = x не истина, что довольно необычно. Это уже проблема не интерпретации, а принятых правил употребления.

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

Что касается реляционной теории, то первые 10 лет своего существования она не рассматривала пропущенных значений вовсе и не имела дела с NULL, после чего мнения специалистов разошлись: одни (в их числе Э. Кодд) посчитали возможным (хотя и нежелательным на деле) использование пропущенных значений, а другие настаивали на их недопущении. То есть разрешение значениям в базе отсутствовать, вместе с вытекающей из этого необходимостью громоздкого аппарата их учета, можно считать одним из предлагаемых расширений, притом спорным, реляционной модели.

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

Например, если требуется учесть возможность отсутствия комиссионных у работника, атрибут "комиссионные" можно исключить из отношения "сотрудники" и перенести в дополнительное отношение:

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

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

Сравнение значений на равенство

Сравнение значений на равенство — более безобидное и очевидное свойство типов в Oracle. Однако для некоторых встроенных "сложных" типов, не говоря уже об объектных, созданных программистом, оно не выполняется. Например, Oracle не позволяет сравнить в запросе SQL два значения типов LOB или XMLTYPE. В приводимом ниже доказательстве команда VARIABLE предназначена для определения в SQL*Plus переменной указанного типа и не имеет отношения к SQL:

VARIABLE x CLOB
VARIABLE y CLOB
SELECT 'ok' FROM dual WHERE :x = :y;
-- ошибка !

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