Как проверить, существует ли столбец, прежде чем добавлять его в существующую таблицу в PL/SQL?

Ответ 1

Все метаданные о столбцах в базе данных Oracle доступны с помощью одного из следующих представлений.

user_tab_cols; - Для всех таблиц, принадлежащих пользователю

all_tab_cols; - Для всех таблиц, доступных пользователю

dba_tab_cols; - Для всех таблиц в базе данных.

Таким образом, если вы ищете столбец типа ADD_TMS в таблице SCOTT.EMP и добавляете столбец только в том случае, если он не существует, код PL/SQL будет иметь следующий вид:

DECLARE
  v_column_exists number := 0;  
BEGIN
  Select count(*) into v_column_exists
    from user_tab_cols
    where upper(column_name) = 'ADD_TMS'
      and upper(table_name) = 'EMP';
      --and owner = 'SCOTT --*might be required if you are using all/dba views

  if (v_column_exists = 0) then
      execute immediate 'alter table emp add (ADD_TMS date)';
  end if;
end;
/

Если вы планируете запускать его как скрипт (а не как часть процедуры), самый простой способ - включить команду alter в скрипт и увидеть ошибки в конце скрипта, если у вас нет Begin-End для сценарий..

Если у вас есть file1.sql

alter table t1 add col1 date;
alter table t1 add col2 date;
alter table t1 add col3 date;

И col2 присутствует, когда скрипт запускается, два других столбца будут добавлены в таблицу, и в журнале будет отображена ошибка, говорящая, что "col2" уже существует, так что вы должны быть в порядке.

Ответ 2

Или вы можете проигнорировать ошибку:

declare
    column_exists exception;
    pragma exception_init (column_exists , -01430);
begin
    execute immediate 'ALTER TABLE db.tablename ADD columnname NVARCHAR2(30)';
    exception when column_exists then null;
end;
/

Ответ 3

Обычно я бы предложил попробовать стандартные мета-таблицы ANSI-92 для чего-то подобного, но теперь я вижу, что Oracle не поддерживает это.

-- this works against most any other database
SELECT
    * 
FROM 
    INFORMATION_SCHEMA.COLUMNS C 
    INNER JOIN 
        INFORMATION_SCHEMA.TABLES T 
        ON T.TABLE_NAME = C.TABLE_NAME 
WHERE 
    C.COLUMN_NAME = 'columnname'
    AND T.TABLE_NAME = 'tablename'

Вместо этого, похоже, вам нужно сделать что-то вроде

-- Oracle specific table/column query
SELECT
    * 
FROM
    ALL_TAB_COLUMNS 
WHERE
    TABLE_NAME = 'tablename'
    AND COLUMN_NAME = 'columnname'

Я прошу прощения за то, что у меня нет экземпляра Oracle, чтобы проверить выше. Если это не работает, пожалуйста, дайте мне знать, и я удалю этот пост.