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

2021-12-24 00:00:00 sql oracle plsql

如何在向 oracle 数据库的表中添加列之前添加简单的检查?我已经包含了用于添加列的 SQL.

How do I add a simple check before adding a column to a table for an oracle db? I've included the SQL that I'm using to add the column.

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

推荐答案

可以使用以下视图之一访问有关 Oracle 数据库中列的所有元数据.

All the metadata about the columns in Oracle Database is accessible using one of the following views.

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

user_tab_cols; -- For all tables owned by the user

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

all_tab_cols ; -- For all tables accessible to the user

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

dba_tab_cols; -- For all tables in the Database.

因此，如果您要在 SCOTT.EMP 表中查找类似 ADD_TMS 的列，并且仅在该列不存在时才添加该列，则 PL/SQL 代码将遵循这些行..

So, if you are looking for a column like ADD_TMS in SCOTT.EMP Table and add the column only if it does not exist, the PL/SQL Code would be along these lines..

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- 脚本结束..

If you are planning to run this as a script (not part of a procedure), the easiest way would be to include the alter command in the script and see the errors at the end of the script, assuming you have no Begin-End for the script..

如果你有file1.sql

If you have file1.sql

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

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

And col2 is present,when the script is run, the other two columns would be added to the table and the log would show the error saying "col2" already exists, so you should be ok.

相关文章