oracle 如果列不存在则更改表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11009156/
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
Alter Table if column doesn't exist
提问by user968441
In a table, I want to check if a particular column exists or not. If the column does not exist, I want to alter the table and create that column.
在表中，我想检查特定列是否存在。如果该列不存在，我想更改表并创建该列。
I am using Oracle 11g.
我正在使用 Oracle 11g。
回答by Grzegorz W
Try this:
尝试这个：
declare p_count NUMBER;
select count(1) int p_count
from ALL_TAB_COLUMNS
where OWNER = '<SCHEMA_NAME>'
and TABLE_NAME = '<TABLE_NAME>'
and COLUMN_NAME = '<COLUMN_NAME>';
IF p_count = 0 THEN
--add your column
END IF;
Eventually (depending on the rights) You can use user_tab_columns.
最终（取决于权限）您可以使用user_tab_columns.
回答by Jeffrey Kemp
If you just want to add a column if it doesn't exist, just issue an ALTER TABLE ADD (mycolumn ...);. If the statement raises an exception (ORA-01430: column being added already exists in table), the column was already there and you can ignore the exception.
如果您只想添加不存在的列，只需发出ALTER TABLE ADD (mycolumn ...);. 如果语句引发异常 ( ORA-01430: column being added already exists in table)，则该列已经存在，您可以忽略该异常。
回答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 Satya
look into user_tab_columns table to check if the column exists , and do accordingly
查看 user_tab_columns 表以检查该列是否存在，并进行相应的操作
