ALTER TABLE ОПЕРАТОР
В этом учебном пособии вы узнаете, как использовать в Oracle/PLSQL оператор ALTER TABLE, чтобы добавить столбец, изменить столбец, удалить столбец, переименовать столбец или переименовать таблицу (с синтаксисом, примерами и практическими упражнениями).
Описание
Оператор Oracle/PLSQL ALTER TABLE используется для добавления, изменения или удаления столбца в таблице. Оператор Oracle/PLSQL ALTER TABLE также используется для переименования таблиц.
Добавить столбец в таблицу
Синтаксис
Синтаксис ALTER TABLE для добавления столбца в таблицу Oracle:
ALTER TABLE table_name
ADD column_name column-definition;
Пример
Рассмотрим на примере, как добавить столбец в таблицу Oracle с помощью оператора ALTER TABLE.
Например:
Создание, удаление и изменение структуры таблиц
Выполняется с помощью ключевых слов 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 ).
На практике синонимы заводятся с разными целями:
- присвоить таблице имя, больше подходящее ее содержанию;
- упростить имя таблицы в запросе, например, OBJECTS вместо SYS.OBJ$ ;
- замаскировать обращение к таблице в другой БД или в другой схеме для придания гибкости кода.
Удаление выполняется командой 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. Вот два примера таблиц словаря-справочника:
- USER_TABLES — перечень всех таблиц схемы пользователя и их одиночных (не множественных) свойств;
- USER_TAB_COLUMNS — перечень столбцов всех таблиц схемы пользователя и одиночных свойств столбцов.
Следующие два запроса к таблицам словаря-справочника предоставляют основные сведения о всех имеющихся в схеме пользователя таблицах и основные сведения о столбцах таблицы 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' ;
Переименование столбцов таблицы и ограничений
Чтобы переименовать столбцы таблицы, используйте предложение RENAME COLUMN инструкции ALTER TABLE .
Чтобы переименовать любое существующее ограничение для таблицы, используйте предложение RENAME CONSTRAINT инструкции ALTER TABLE .
При переименовании столбца таблицы новое имя не должно конфликтовать с именем любого существующего столбца в таблице. С предложением RENAME COLUMN не допускается включать никаких других предложений.
В примерах на рисунке используется таблица marketing с первичным ключом mktg_pk, определенным в столбце id.
CONSTRAINT mktg_pk PRIMARY KEY(team_id));
В примере a столбец id таблицы marketing переименовывается в mktg_id . В примере b mktg_pk переименовывается в new_mktg_pk .
При переименовании любого существующего ограничения для таблицы новое имя не должно конфликтовать с именами всех существующих ограничений. Для переименования создаваемых системой имен ограничений можно использовать предложение RENAME CONSTRAINT .
Далее: Проверка последовательностей
Переименование столбцов и вычисления в результирующем наборе стр. 1
Имена столбцов, указанные в предложении SELECT , можно переименовать. Это делает результаты более читабельными, поскольку имена полей в таблицах часто сокращают с целью упрощения набора. Ключевое слово AS, используемое для переименования, согласно стандарту можно и опустить, так как оно неявно подразумевается.
Консоль
Выполнить
переименует столбец ram в Mb (мегабайты), а столбец hd в Gb (гигабайты). Этот запрос возвратит объемы оперативной памяти и жесткого диска для тех компьютеров, которые имеют 24-скоростной CD-ROM:
Переименование особенно желательно при использовании в предложении SELECT выражений для вычисления значения. Эти выражения позволяют получать данные, которые не находятся непосредственно в таблицах. Если выражение содержит имена столбцов таблицы, указанной в предложении FROM , то выражение подсчитывается для каждой строки выходных данных. Так, например, чтобы вывести объем оперативной памяти в килобайтах, можно написать:
Консоль
Выполнить
Теперь будет получен следующий результат:
Иногда бывает необходимо выводить поясняющую информацию рядом с соответствующим значением. Это можно сделать, добавив строковое выражение как дополнительный столбец. Например, запрос:
Консоль
Выполнить
даст следующий результат:
Если же явно не указать имя для выражения, то будет принят способ именования по умолчанию, который зависит от используемой СУБД. Так, в MS Access будут использованы имена типа выражение1 и т. д., а выходной столбец в MS Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server вообще не будет иметь заголовка.