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

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

Таблицы представляют собой главный инструмент моделирования данных в БД, как построенных на основе SQL вообще, так и в Oracle в частности.

Предложение CREATE TABLE

Создание таблиц осуществляется предложением CREATE TABLE категории DDL.

Пример:

CREATE TABLE proj 
(
   projno NUMBER   ( 4 )
 , pname  VARCHAR2 ( 14 )
 , bdate  DATE
 , budget NUMBER   ( 10, 2 )
);

Помимо указанных в этом примере предложение CREATE TABLE может включать чрезвычайно много других конструкций, большая часть которых связана с организацией хранения, а не с логикой данных. Полностью они приводятся в документации Oracle по SQL.

Типы данных в столбцах

Существующие в Oracle встроенные (предопределенные) типы позволяют указывать столбцам таблицы следующие виды данных:

• Числа:
  • типы NUMBER, NUMBER ( n ), NUMBER ( n, m ) (в частности, NUMBER ( *, m ))
  • типы FLOAT, REAL, NUMERIC, DECIMAL, INTEGER и другие совместимые с ANSI
  • типы BINARY_FLOAT(10.1-), BINARY_DOUBLE(10.1-)
• Строки текста:
  • типы VARCHAR2 ( n ), CHAR ( n )
  • типы NVARCHAR2 ( n ), NCHAR ( n )(9.0-)
  • типы CLOB(8-), NCLOB(8-)
  • типы STRING, CHARACTER VARYING, NATIONAL CHARACTER VARYING и другие
• Строки байтов:
  • RAW ( n )
  • BLOB(8-)
  • LONG, LONG RAW
• Моменты времени:
  • DATE
  • TIMESTAMP(9.2-), TIMESTAMP ( n )(9.2-)
  • TIMESTAMP WITH TIME ZONE(9.2-), TIMESTAMP WITH LOCAL TIME ZONE(9.2-)
• Интервалы времени:
  • INTERVAL YEAR TO MONTH(9.2-), INTERVAL DAY TO SECOND(9.2-)
• Прочие типы данных:
  • BFILE(8-)
  • ROWID, UROWID (физический и "логический физический" адреса строк или объектов в таблицах)
  • XMLTYPE(9.2-), ANYDATA(9.2-), URITYPE с подтипами(9.2-); типы для Oracle Spatial(8-), для построения геоинформационных систем; прочие (встроенные объектные)

(8-) начиная с версии 8.

(9.0-) начиная с версии 9.0.

(9.2-) начиная с версии 9.2.

(10.1-) начиная с версии 10.1.

Помимо встроенных типов Oracle с версии 8 позволяет использовать в описании столбцов типы, создаваемые самостоятельно пользователями БД. Это объектные ("структурные") типы.

Типы BLOB, CLOB, NCLOB и BFILE иногда объединяют в единую категорию типов "больших неструктурированных объектов" (объекты LOB). Для работы с ними в SQL иногда требуется прибегать к функциям встроенного пакета DBMS_LOB, хотя с версии 9.2 поводов для этого стало меньше. Их употребление в SQL связано с определенными ограничениями. Например, столбцы этих типов не могут употребляться в формировании ключа таблицы и вообще индексироваться стандартным образом. Часть подобных ограничений употребления обязаны предположительно гигантским объемам значений, а часть — особенному способу хранения, отличному от принятого для "обычных" данных.

В коде СУБД Oracle сохранились попытки ввести в некоторых случаях более разумные встроенные типы, по ряду причин не доведенные до официального предоставления. Примером может служить тип TIME для обозначения времени суток. Разрешить его употребление в своем сеансе связи с СУБД можно следующей командой ( http://citforum.ru/database/oracle/time/):

 ALTER SESSION SET EVENTS '10407 trace name context forever, level 1';

Официально этого типа не существует, а следами его фактического наличия являются возможности указывать в обычных выражениях SQL значение времени суток, например, TIME '12:30:45', и некоторые функции, например, TO_TIME и EXTRACT.

Похожим путем в версии 8.1 можно активировать типы TIMESTAMP и INTERVAL (впоследствии ставшие штатными, в отличие от TIME):

ALTER SESSION SET EVENTS '10406 trace name context forever';

Числовые типы

Тип NUMBER

• Исторически первым для Oracle числовым типом является NUMBER. Он существует в трех вариантах:
• NUMBER — для хранения чисел "самого общего вида";
• NUMBER (n) — для хранения целых с максимальной точностью мантиссы n десятичных позиций;
• NUMBER (n, m) (в частности, NUMBER (*, m )) — для хранения чисел "с фиксированной десятичной точкой" с максимальной точностью мантиссы n десятичных позиций, из них m после десятичной точки.

Формат хранения во всех случаях одинаков:

• 1-й байт — знак числа и степень 100 в двоичном виде;
• остальные байты — двоично-десятичное представление цифр мантиссы, по две десятичные цифры на байт, максимум 38 десятичных цифр.

Таким образом, все варианты типа NUMBER преобразуются к соответствующей им форме при помещении в базу, а при выборке из базы интерпретируются в соответствии с типом столбца. Проверить фактический вид хранения позволяет служебная функция DUMP:

SQL> SELECT DUMP ( 1 ) FROM dual;
DUMP(1)
------------------
Typ=2 Len=2: 193,2
SQL> SELECT DUMP ( 1000 ) FROM dual;
DUMP(1000)
------------------- 
Typ=2 Len=2: 194,11
SQL> SELECT DUMP ( 1000 - 1 ) FROM dual;
DUMP(1000-1)
-----------------------
Typ=2 Len=3: 194,10,100

В выдачах Typ = 2 сообщает числовое внутреннее обозначение для типа NUMBER, Len сообщает занимаемую при хранении длину в байтах, а десятичные значения самих байтов перечисляются следом.

Подтипы NUMBER

Тип NUMBER в Oracle не входит в стандарт ANSI/ISO SQL. Следующие типы включены в диалект SQL Oracle для совместимости со стандартом и с решениями IBM:

Содержательно эти типы в Oracle ничего не привносят и фактически являются подтипами типа NUMBER.