如何在将列添加到 PL/SQL 中的现有表之前检查列是否存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6351823/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a column exists before adding it to an existing table in PL/SQL?
提问by Prabu
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.
在向 oracle 数据库的表中添加列之前，如何添加简单的检查？我已经包含了用于添加列的 SQL。
ALTER TABLE db.tablename
ADD columnname NVARCHAR2(30);
回答by Rajesh Chamarthi
All the metadata about the columns in Oracle Database is accessible using one of the following views.
可以使用以下视图之一访问有关 Oracle 数据库中列的所有元数据。
user_tab_cols; -- For all tables owned by the user
user_tab_cols; -- 用户拥有的所有表
all_tab_cols; -- For all tables accessible to the user
all_tab_cols; -- 对于用户可以访问的所有表
dba_tab_cols; -- For all tables in the Database.
dba_tab_cols; -- 对于数据库中的所有表。
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..
因此，如果您正在 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;
/
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..
如果您打算将它作为脚本（不是过程的一部分）运行，最简单的方法是在脚本中包含 alter 命令并查看脚本末尾的错误，假设您没有 Begin-End for剧本..
If you have file1.sql
如果你有 file1.sql
alter table t1 add col1 date;
alter table t1 add col2 date;
alter table t1 add col3 date;
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.
并且 col2 存在，当脚本运行时，其他两列将被添加到表中，并且日志会显示“col2”已经存在的错误，所以你应该没问题。
回答by grokster
Or, you can ignore the error:
或者，您可以忽略错误：
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;
/
回答by billinkc
Normally, I'd suggest trying the ANSI-92 standard meta tables for something like this but I see now that Oracle doesn't support it.
通常，我建议尝试使用 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'
Instead, it lookslike you need to do something like
相反，看起来你需要做类似的事情
-- Oracle specific table/column query
SELECT
*
FROM
ALL_TAB_COLUMNS
WHERE
TABLE_NAME = 'tablename'
AND COLUMN_NAME = 'columnname'
I do apologize in that I don't have an Oracle instance to verify the above. If it does not work, please let me know and I will delete this post.
我很抱歉，因为我没有 Oracle 实例来验证上述内容。如果它不起作用，请告诉我，我将删除此帖子。
回答by Java_Alert
To check column exists
检查列是否存在
select column_name as found
from user_tab_cols
where table_name = '__TABLE_NAME__'
and column_name = '__COLUMN_NAME__'
