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

Типы 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 этими двумя смыслами в жизни недостаточно (хотя и хватает для описания 99% возникающих ситуаций). По этим причинам, хотя на первый взгляд возможность опустить значение в столбце за его отсутствием может и показаться привлекательной, последующая работа с такими данными способна принести программисту значительно больше неприятностей из-за необходимости учета при составлении запросов неформализованных в схеме сведений о данных, усложнения запросов и возникающих рисков ошибиться при программировании. Примеры подобных неприятностей обозначены в разных местах текста ниже.

Стандарт SQL допускает NULL, но оговаривает, что во имя надежности обращения к данным программисту следует всячески избегать пропусков значений в столбцах либо уж употреблять их лишь в смысле "значение неизвестно" (unknown). Стандарт называет NULL особым значением, общим для всех типов, и обрекает себя тем самым на критику со стороны специалистов, полагающих, что правильнее назвать NULL символом Смотри книгу Дейт К. Дж., Дарвен Х. Основы будущих систем баз данных. Третий манифест. Перевод с английского. Изд. 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 требуют самостоятельного оформления, пример коего последует позже.

Уточнения возможных значений в столбцах

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

Пример:

CREATE TABLE projx 
(
   projno NUMBER   (4)  NOT NULL
 , pname  VARCHAR2 (14) CHECK (SUBSTR(pname,1,1) BETWEEN 'A' AND 'Z')
 , bdate  DATE          DEFAULT TRUNC ( SYSDATE )
 , budget NUMBER   (10,2)
);

Такие правила делятся на две категории: значения данных по умолчанию и "ограничения" (или же "правила") "целостности".

Приписка DEFAULT выражение к определению столбца указывает на значение, которое будет заноситься СУБД в поле добавляемой строки, если программист в операции INSERT никакого значения для этого поля не привел.

Упражнение. Проверьте результаты изменений данных:

INSERT INTO projx ( projno, bdate ) VALUES ( 15, SYSDATE + 1 );
INSERT INTO projx ( projno )        VALUES ( 16 );
SELECT * FROM projx;
UPDATE projx SET bdate = DATE '2009-09-17' WHERE projno = 15;
SELECT * FROM projx;

К "ограничениям целостности" в примере выше относятся уточнения описаний столбцов, оформленные с помощью слов NOT NULL и CHECK. Более систематично они вместе с другими разрешенными ограничениями целостности будут описаны в соответствующем разделе ниже.

Свойства столбцов, не связанные со значениями

Шифрование при хранении

С версии 10.2 Enterprise Edition, (а) при установленном дополнении к СУБД Advanced Security Option и (б) при наличии предварительно созданного на сервере "бумажника" Oracle Wallet можно потребовать автоматического ("прозрачного") шифрования значений столбца при помещении их в БД и автоматической дешифровки при извлечении. Пример указания трех разных технических способов шифрования для трех разных столбцов:

  ...
, sal      NUMBER ( 7, 2 ) ENCRYPT
, comm     NUMBER ( 7, 2 ) ENCRYPT NO SALT
, hiredate DATE ENCRYPT USING 'AES256' IDENTIFIED BY 'SecretWord'
  ...

Свойство "хранить в зашифрованном виде" — нефиксированное: его можно добавлять или отменять для столбцов существующей таблицы, при том что эти действия будут сопровождаться автоматической правкой ранее заведенных в таблице данных.

"Прозрачному" шифрованию подлежат только основные встроенные типы (с версии 11 плюс LOB, хотя и отдельным способом), и на употребление этой возможности наложены некоторые ограничения.

Виртуальные столбцы

Версия 11 разрешила объявлять в таблице виртуальные (мнимые) столбцы. Значения в них не хранятся в БД самостоятельно, а вычисляются автоматически при запрашивании на основе действительных значений других полей строки (тем самым они не нарушают 3-ю нормальную форму). Например, в таблице EMP могло бы иметься такое определение:

  ...
, sal      NUMBER ( 7, 2 )
, comm     NUMBER ( 7, 2 )
, earnings AS ( sal + comm )
  ...

Дополнительные ключевые слова помогут при этом сделать запись яснее, не меняя сути формулировки, например:

... earnings AS GENERATED ALWAYS ( sal + comm ) ...

Виртуальные столбцы, подобно действительным, можно индексировать (индекс при этом получается с "функционально преобразованным значением ключа") и использовать в формулировании ограничений целостности.

Создание таблиц по результатам запроса к БД

Второй способ завести таблицы в SQL состоит не в явном перечислении столбцов и их свойств, а в ссылке на результат запроса к БД:

CREATE TABLE dept_copy AS SELECT * FROM dept;

Запрос следует после ключевого слова AS и выше указан чрезвычайно простым, но имеет право быть и сколь угодно сложным. Сначала он вычисляется СУБД, после чего в БД создается таблица со структурой полученного результата (с воспроизведением всех столбцов с их типами), и эта новая таблица тут же заполняется данными результата. Такой способ создания таблицы позволяет экономить усилия программиста и время, когда новую таблицу нужно создать на основе уже имеющихся данных.

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

CREATE TABLE emps ( name, department ) 
AS 
SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno
;

Иногда, как возможно в первом примере, команду CREATE TABLE ... AS используют для "копирования" существующей таблицы, указав запрос в форме SELECT * FROM таблица. В таких случаях не следует забывать, что в новой таблице из структурных свойств старой окажутся вспроизведены только типы столбцов и свойства NULL/NOT NULL, но не ограничения целостности и не DEFAULT. При этом воспроизведение свойств NULL/NOT NULL столбцов довольно необычно, так как оно не имеет смысла для запросов с более общей формулировкой.

Именование таблиц и столбцов

Правила именования таблиц и столбцов:

Имя объекта в предложении SQL, не заключенное в двойные кавычки ("обычное"), попадает в словарь-справочник БД, будучи приведенным к верхнему регистру. Двойные кавычки предотвращают повышение регистра.

Упражнение. Выполните последовательно и сравните ответы СУБД:

CREATE TABLE t ( a NUMBER, a VARCHAR2 ( 1 ) );
CREATE TABLE t ( a NUMBER, "a" VARCHAR2 ( 1 ) );
CREATE TABLE t ( a NUMBER, "a" VARCHAR2 ( 1 ) );
CREATE TABLE "t" ( a NUMBER, "a" VARCHAR2 ( 1 ) );

Замечания

Oracle допускает в SQL ссылки не только на односоставные имена таблиц, но и на составные.

Так, имя таблицы в команде SQL может быть уточнено именем схемы, в которой числится эта таблица, например: SCOTT.DEPT, SYS.OBJ$. В действительности, если в запросе приведено односоставное имя, то при обработке Oracle самостоятельно дополнит его именем схемы. Обычно это будет имя схемы, с которой работает программа (что в Oracle равнозначно имени пользователя), но при желании такое подразумеваемое расширение имени таблицы можно заменить в пределах отдельного сеанса на имя любой другой существующей в данный момент в БД схемы, например:

ALTER SESSION SET CURRENT_SCHEMA = yard;

После этого обращения просто к EMP будут считаться обращениями к YARD.EMP, а не к SCOTT.EMP, как по умолчанию. Этим иногда пользуются при разработке приложения для придания ему гибкости. Подобная подмена умолчательного имени схемы не влечет несанкционированного доступа к объектам чужой схемы, так как будет означать только попытку обращения к чужому объекту. Окажется ли успешным обращение, определяется совсем другим механизмом полномочий доступа (прав).

Дополнительно в обращении к таблице можно указать имя заведенной предварительно ссылки на другую БД, с которой установлена связь, и тогда имя может в конечном итоге оказаться трехсоставным, например: SCOTT.EMP@PERSONNELDB. Этим обозначено обращение к таблице EMP в схеме SCOTT БД, именованной как PERSONNELDB.

Удаление таблиц

Удаление таблицы выполняется командой DROP TABLE имя_таблицы.

Сведения о таблице СУБД хранит в двух отдельных местах БД: в справочной части БД (в словаре-справочнике) — описание и в виде самостоятельной структуры (сегмента) в файлах табличного пространства — содержимое, данные.

До версии 10 единственным способом удаления по команде DROP TABLE было удаление из словаря-справочника сведений о таблице и удаление сегмента с данными из "рабочей части" БД. С версии 10 возможен (и действует автоматически по умолчанию) второй вариант, когда по команде DROP TABLE описание таблицы и сегмент с ее данными получают новое системное имя, после чего таблица под прежним именем перестает существовать, но фактически какое-то время может быть еще восстановлена. Этот второй способ удаления таблицы воплощает идею "мусорной корзины".

Простое удаление

Пример простой команды удаления таблицы:

DROP TABLE dept_copy2;

Если на столбцы таблицы определены ссылки внешними ключами других таблиц, СУБД не позволит выполнить DROP TABLE и вернет ошибку. (Замечания: (а) это связано не с наличием конкретных ссылающихся строк, а с наличием самого правила внешнего ключа; (б) внешний ключ, ссылающийся на значения столбцов "собственной" таблицы, не препятствует ее удалению). Конструкция CASCADE CONSTRAINTS в команде DROP TABLE позволит-таки удалить таблицу, но при этом СУБД удалит сначала "мешающее" правило внешнего ключа. Столбцы другой, оставшейся таблицы в результате сохранят свои значения, но они уже не будут обременены ограничением ссылочной целостности. Фактически использование CASCADE CONSTRAINTS равносильно последовательному удалению всех правил внешнего ключа, имеющих адресатом таблицу (таковых может быть несколько), и выполнению в завершение простой команды DROP TABLE.

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

DROP TABLE dept_copy2 CASCADE CONSTRAINTS;

Мусорная корзина

С версии 10 смысл команды DROP изменился. В основном случае после нее и описание, и данные таблицы продолжают храниться на своих местах, но под новыми, присвоенными системой автоматически именами. Для пользователя таблица, как и прежде, пропала, однако на деле все, что нужно для ее восстановления, если такая необходимость возникнет, продолжает храниться в БД. Тем самым для таблиц реализована техника мусорной корзины (recycle bin), хорошо известная по файловым системам.

Список содержимого мусорной корзины можно получить из системной таблицы USER_RECYCLEBIN (публичный синоним — RECYCLEBIN):

SELECT object_name, original_name, droptime FROM user_recyclebin;  

Восстановить таблицу по исходному имени (поле ORIGINAL_NAME из USER_RECYCLEBIN) можно, например, так:

FLASHBACK TABLE dept_copy2 TO BEFORE DROP;

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

Для удаления из мусорной корзины нужно использовать команду PURGE, например:

PURGE TABLE dept_copy3;
PURGE RECYCLEBIN;

Чтобы таблица удалялись сразу безвозвратно, следует использовать конструкцию PURGE в команде DROP, например:

DROP TABLE dept_copy3 PURGE;

Умолчательное помещение таблицы в мусорную корзину можно отменить и вернуться к старой обработке команды DROP. Для этого нужно задать значение OFF параметру СУБД RECYCLEBIN. Последний допускает динамическую установку на уровне СУБД (ALTER SYSTEM …) и на уровне сеанса (ALTER SESSION …), например:

ALTER SESSION SET RECYCLEBIN = OFF;

Как и в файловых системах, в Oracle мусорная корзина хранит содержимое до поры до времени. В основном она — инструмент восстановления данных после ошибочных действий пользователя, возникших в результате проведения опытов с базой или по неосторожности.

Изменение структуры таблиц

Изменение структуры и других свойств существующей таблицы необязательно требует удаления ее и воссоздания в переделанном виде. Часто оно осуществимо "по живому", что менее хлопотно для программиста и быстрее. Oracle не чинит чрезмерных препятствий таким изменениям и, как правило, допустит их, если они:

В общем допустимы следующие структурные изменения: добавление и удаление столбцов, изменение типа столбца, добавление и убирание ограничений целостности данных. Все они осуществляются разновидностями команды ALTER TABLE.

С версии 11.2 Oracle предлагает к тому же особую технику редакций объектов хранения для внесения изменений в схему данных. Эта техника не рассматривается непосредственно здесь, но в одном из разделов ниже.

Кроме того, с версии 9 Oracle дает возможность переопределять структуру существующих таблиц не средствами SQL (в принципе достаточными для этой цели), а программно с помощью встроенного системного пакета DBMS_REDEFINITION. Делается это исключительно по технологическим соображениям с тем, чтобы время недоступности данных при перестройке было по возможности малым (формально переопределение происходит online, то есть без прекращения доступности вовсе). Главным образом это актуально для таблиц с большими объемами данных при существующих жестких требованиях к доступности. Такая техника в настоящем тексте, посвященном SQL, естественно, не затрагивается.

Добавление столбца

Добавление столбца не связано ни с какими возможными логическими противоречиями и осуществимо практически всегда.

Пример:

ALTER TABLE projx ADD ( pcode VARCHAR2 ( 1 ) );

Единственным логическим препятствием к добавлению столбца служит достижение предельного количества столбцов в таблице Oracle — 1000 (значение взято из документации по Oracle).

Следующее замечание касается не виртуальных (с версии 11), а реальных добавляемых столбцов.

В типичном случае такой добавляемый столбец не будет иметь значений, и его добавление не потребует правки в БД существующих строк таблицы, то есть совершится со скоростью внесения изменений в описание таблицы в словаре-справочнике. Однако если для нового столбца указано свойство DEFAULT, потребуется внести значение в новое поле у всех имеющихся строк. Если таблица велика, на это может уйти много времени. С версии 11 действует оптимизация такого исправления данных для случаев, когда вместе с DEFAULT для столбца одновременно указано NOT NULL. В самом деле, наличие этих свойств обоих сразу позволяет отказаться от фактического добавления значения в каждую строку таблицы, а вместо этого единожды сохранить значение выражения, указанного во фразе DEFAULT, в описании таблицы, а при последующих запросах к строке имитировать наличие этого значения в добавленном поле. С версии 11 СУБД Oracle так и поступает, экономя вдобавок дисковое пространство.

Изменение типа столбца

Выполняется с использованием слова MODIFY, например:

ALTER TABLE projx MODIFY ( pcode NUMBER ( 6 ) );

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

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

Тип DATE хранится как TIMESTAMP ( 0 ) и однороден с ним в указанном смысле, а вот TIMESTAMP WITH TIME ZONE не однороден не только с DATE, но и TIMESTAMP и допускает взаимные замены с ними только на пустом столбце.

К сказанному имеется оговорка. В силу особенностей хранения данных типов LOB менять тип столбца на них или из них в остальные нельзя даже на пустом столбце. При необходимости такой столбец придется удалить и воссоздать с другим типом.

Добавление и упразднение ограничений целостности

Выполняется с помощью ключевых слов ADD и DROP или же MODIFY (с отчасти различной областью применимости).

Пример употребления слова MODIFY для добавления и для снятия ограничения NOT NULL:

ALTER TABLE projx MODIFY ( pcode NOT NULL );
ALTER TABLE projx MODIFY ( pcode NULL );

Пример употребления слов ADD и DROP с целью добавления и снятия ограничения первичного ключа:

ALTER TABLE projx ADD PRIMARY KEY ( projno );
ALTER TABLE projx DROP PRIMARY KEY;

Другие примеры и пояснения последуют далее в самостоятельном разделе.

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

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

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

Удаление столбца

Возможно с версии Oracle 8.

Примеры:

ALTER TABLE projx DROP ( pcode );
ALTER TABLE projx DROP COLUMN pcode;

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

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

ALTER TABLE projx DROP ( pcode ) CASCADE CONSTRAINTS;

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

Средства повышения эффективности удаления столбца

Следующие замечания не касаются удаления виртуальных столбцов (с версии 11), но только реальных. Удаление реального столбца влечет физическое переформатирование всех строк таблицы, что может составить технологическую проблему в случае больших объемов данных. Для нее предлагается два решения.

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

ALTER TABLE projx DROP ( pcode ) CHECKPOINT 1000;

Если в процессе удаления произошел сбой, процедуру можно продолжить командой

ALTER TABLE projx DROP COLUMNS CONTINUE;

или командой

ALTER TABLE projx DROP COLUMNS CONTINUE CHECKPOINT 1000;

Другой способ — объявить сначала столбец "неиспользуемым", что не приведет к физическому перебору имеющихся записей:

ALTER TABLE projx SET UNUSED COLUMN pcode CASCADE CONSTRAINTS;

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

ALTER TABLE projx DROP UNUSED COLUMNS;

Переименования

Неудачно или несовременно названную таблицу можно переименовать, например:

ALTER TABLE emp RENAME TO employee;

Другой способ:

RENAME employee TO emp;

Команда RENAME по сравнению с более сфокусированной ALTER TABLE … RENAME является более общей, так как позволяет переименовать помимо таблиц объекты некоторых других типов: представление данных (view), генератор последовательности (sequence) и частный синоним. Ее название унаследовано языком SQL от реляционной модели, где имеется одноименная операция.

Пример переименования столбца:

ALTER TABLE projx RENAME COLUMN pcode TO project_code;

При переименовании объекта СУБД пометит свойства зависимых от данного объектов (например, хранимых процедур на PL/SQL, обращающихся к данной таблице) признаком INVALID, что вызовет потребность их перекомпиляции перед очередным обращением к ним. Ссылки же на таблицу из внешних программ находятся вне компетенции БД; изменение имен пройдет для таких программ незаметно, но в то же время они потеряют свою работоспособность. Это обстоятельство существенно уменьшает ценность операции переименования в реальной практике.

Переименовать таблицу можно и непосредственной правкой таблицы OBJ$ словаря-справочника (см. далее). Такой же подход позволяет переименовать и столбцы таблиц путем внесения правки в таблицу COL$. Однако применять его следует только опытным пользователям и с осторожностью. Он осуществим только для пользователей, имеющих доступ к этим таблицам схемы SYS, и к тому же не изменит состояния зависимых от таблицы подпрограмм на значение INVALID.

Использование синонимов для именования таблиц

Синонимы позволяют завести дополнительные имена для обращения к таблице, не обесценивая основного имени:

CREATE SYNONYM members FOR emp;
SELECT * FROM emp;
SELECT * FROM members;

Теперь, обнаружив обращение к MEMBERS, СУБД определит по своей справочной информации, что это синоним имени EMP, и обратится фактически к EMP. Возможность прямого обращения по имени EMP в тексте команды SQL при этом не теряется, и на работе старых программ появление у таблицы синонимов никак не скажется. Это, однако, не касается команд DDL ALTER/DROP TABLE, где ссылаться следует только на истинное имя таблицы (в полном соответствии с синтаксисом, так как в этих командах используется именно ключевое слово TABLE).

На практике синонимы заводятся с разными целями:

Удаление выполняется командой DROP SYNONYM:

DROP SYNONYM members;

Синоним, заведенный в схеме (например, SCOTT), как и таблица, сам становится объектом схемы, доступным изначально только пользователю — хозяину схемы или же администратору с соответствующим полномочием. Однако Oracle позволяет создавать еще и PUBLIC SYNONYM: "внесхемный", общедоступный синоним. Публичные синонимы активно используются в административной части БД Oracle, но нередко и обычными разработчиками в своих целях. В силу того, что пространства имен публичных и схемных синонимов разные, возможны "спорные" ситуации:

CREATE PUBLIC SYNONYM syn1 FOR dept;
-- публичный синоним
CREATE SYNONYM syn1 FOR emp;
-- собственный синоним схемы
SELECT * FROM syn1;
-- DEPT или EMP ?

По существующему правилу разрешения имен Oracle отдаст в последнем запросе предпочтение схемному ("частному") синониму. Получается, что появление частного синонима в некоторых случаях способно изменять смысл существовавшего в программе до этого запроса, о чем не следует забывать разработчику.

Создание синонимов в Oracle требует привилегий (полномочий) CREATE SYNONYM и CREATE PUBLIC SYNONYM, изначально отсутствующих у пользователя SCOTT. В жизни, чтобы обеспечить пользователя синонимами, не обязательно выдавать ему эти привилегии. Создать пользователю Oracle синоним способен, например, администратор.

Использование синонимов для таблиц — это частный случай. В общем синонимы можно создавать и для некоторых прочих хранимых в БД объектов, например, для процедур или функций.

Справочная информация о таблицах и прочих объектах в БД

Место хранения справочной информации об объектах, составляющих БД ("хранимых в БД"), называется в базах данных словарем-справочником (data dictionary). Русский термин-перевод пришел в эту область ИТ из более старой лексики, где обозначает "справочную книгу, которая содержит собрание слов (словосочетаний, идиом и т. д.), расположенных по определенному принципу, и дает сведения об их значениях, употреблении, происхождении; информацию о понятиях и предметах, ими обозначаемых и др.". В БД этот термин, не утратив самой общей сути, стал значительно более конкретен. Иногда вместо него пользуются термином системный каталог.

В Oracle словарь-справочник реализован набором таблиц в составе самой БД, составляющих "справочную часть" БД. Количество таких таблиц в любой БД Oracle — несколько сотен. Официальный их перечень приведен в документации по Oracle. Вот два примера таблиц словаря-справочника:

Следующие два запроса к таблицам словаря-справочника предоставляют основные сведения о всех имеющихся в схеме пользователя таблицах и основные сведения о столбцах таблицы PROJ. Три команды COLUMN предназначены для SQL*Plus и задают приемлемый формат выдачи на экран:

COLUMN table_name FORMAT A30
SELECT
  table_name
, status 
FROM
  user_tables
;
COLUMN column_name FORMAT A30
COLUMN data_type   FORMAT A15
SELECT
  column_name
, data_type
, data_length
, data_precision
FROM
  user_tab_columns
WHERE
  table_name = 'PROJ'
;

Возможности словаря-справочника дополняются способностью Oracle хранить в нем "комментарии", краткие пояснения к сведениям о таблицах и столбцах. Для заведения комментария в Oracle SQL имеется особая команда COMMENT:

COMMENT ON TABLE proj IS 'Проекты в фирме';
COMMENT ON COLUMN proj.budget IS 'Утвержденный бюджет проекта';

Наблюдать имеющиеся комментарии можно через таблицы словаря-справочника USER_COL_COMMENTS и USER_TAB_COMMENTS:

SELECT comments 
FROM   user_tab_comments 
WHERE  table_name = 'PROJ'
;
SELECT comments 
FROM   user_col_comments 
WHERE  table_name = 'PROJ' 
 AND   column_name = 'BUDGET'
;