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

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

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

Пример:

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 столбцов довольно необычно, так как оно не имеет смысла для запросов с более общей формулировкой.

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

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

• Две таблицы одной схемы (или принадлежащие одному владельцу, что в Oracle одно и то же) должны иметь разные имена.
• Два столбца одной таблицы должны иметь разные имена.
• Длина имени может достигать не более 30 знаков (в версии 11 словарь-справочник стал допускать имена длиною до 128 знаков, однако для обычных объектов внутренняя программная логика ориентируется на прежнее ограничение).
• "Обычное" имя может состоять только из букв, цифр и символов _, $, # и начинаться с буквы. Будучи заключены в двойные кавычки, имена могут состоять из любых символов.
• Имена не должны совпадать с зарезервированными в Oracle словами.
• Русские названия допускаются без ограничения употребления, если только для БД указана одна из "русских" кодировок.

Имя объекта в предложении 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 ) );

Замечания

1. Заключение имени в двойные кавычки — обычно мера вынужденная, так как влечет обязательность указания двойных кавычек и в дальнейшем, после создания таблицы (иначе СУБД все время будет пытаться повысить регистр символов), то есть неудобства употребления.
2. Понятие "зарезервированное слово" в силу разных причин определено в Oracle нечетко. В Oracle SQL и в PL/SQL множества зарезервированных слов, большей частью совпадая, все же различаются. Например, слово TIMESTAMP можно использовать для именования столбца.
3. Использование русских букв в именах не влечет никаких неприятностей со стороны СУБД. Тем не менее внешние по отношению к СУБД программы не всегда умеют их правильно обрабатывать. В силу этого к русским именам таблиц и столбцов в Oracle следует относиться настороженно.

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.