sql - 如何在 PL/SQL 中将列添加到现有表之前检查列是否存在？

Question

在向 Oracle 数据库的表中添加列之前，如何添加简单的检查？我已包含用于添加列的 SQL。

ALTER TABLE db.tablename 
  ADD columnname NVARCHAR2(30);

Answer 1

有关 Oracle 数据库中列的所有元数据都可以使用以下 View 之一访问。

user_tab_cols ; -- 对于用户拥有的所有表

all_tab_cols ; -- 对于用户可访问的所有表

dba_tab_cols ; -- 对于数据库中的所有表。

因此，如果您正在 SCOTT.EMP 表中查找类似 ADD_TMS 的列，并且仅在该列不存在时才添加该列，那么 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 -脚本结束..

如果你有file1.sql

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

并且 col2 存在，当脚本运行时，其他两列将被添加到表中，并且日志将显示错误说“col2”已经存在，所以你应该没问题。