更新时间:2023-12-01 10:14:10
可使用以下视图之一访问有关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.