if exists(select 1 from DBA_TAB_COLUMNS where TABLE_NAME='TEST' and COLUMN_NAME='abs') alter table ........
楼上的正确,如果该字段不存在,select 1 from DBA_TAB_COLUMNS where TABLE_NAME='TEST' and COLUMN_NAME='abs' 结果是空
我借你的贵贴练习下procedure :) create or replace procedure AddColumn(tabName in varchar2, colName in varchar2,colType in varchar2) as cursor c_cols is select column_name from user_tab_cols where table_name=tabName; r_col c_cols%rowtype; str_sql varchar2(200); flag number(1); begin flag:=0; open c_cols; loop fetch c_cols into r_col; exit when c_cols%notfound; --dbms_output.put_line(colName); if r_col.column_name=colName then flag:=1; dbms_output.put_line('I found it!'); end if; end loop; if flag=0 then str_sql:='alter table '||tabName||' add '||colName||' '||colType; dbms_output.put_line(str_sql); execute immediate str_sql; end if; exception when others then null;
end AddColumn;SQL>execute AddColumn('T','ID','number(2)');
alter table ........
create or replace procedure AddColumn(tabName in varchar2, colName in varchar2,colType in varchar2) as
cursor c_cols is select column_name from user_tab_cols where table_name=tabName;
r_col c_cols%rowtype;
str_sql varchar2(200);
flag number(1);
begin
flag:=0;
open c_cols;
loop
fetch c_cols into r_col;
exit when c_cols%notfound;
--dbms_output.put_line(colName);
if r_col.column_name=colName then
flag:=1;
dbms_output.put_line('I found it!');
end if;
end loop;
if flag=0 then
str_sql:='alter table '||tabName||' add '||colName||' '||colType;
dbms_output.put_line(str_sql);
execute immediate str_sql;
end if; exception
when others then
null;
end AddColumn;SQL>execute AddColumn('T','ID','number(2)');