Oracle存储过程--动态增加和删除数据库表字段

（1）增加字段 参数表名、字段名、字段类型
create or replace procedure TRYADDTABCOLUMN(tabName in varchar2, colName in varchar2, colType in varchar2) is
n_col int;
stmt VARCHAR(2000);
begin
select count(*) into n_col from cols
where table_name = upper(tabName) and column_name = upper(colName);
if n_col<1 then
stmt :='alter table ‘||tabName||’ add ‘||colName||’ '||colType;
execute immediate stmt;
end if;
end TRYADDTABCOLUMN;

（2）删除字段 参数表名、字段名、字段类型
create or replace procedure TRYDROPTABCOLUMN(tabName in varchar2, colName in varchar2) is
n_col int;
stmt VARCHAR(2000);
begin
select count(*) into n_col from cols
where table_name = upper(tabName) and column_name = upper(colName);
if n_col>0 then
stmt :='alter table ‘||tabName||’ drop column '||colName;
execute immediate stmt;
end if;
end TRYDROPTABCOLUMN;

（3）此存储过程会先判断将要插入的字段是否在表中已经存在，如存在则忽略，不存在时再增加列。
– 调用示例
CALL tryaddtabcolumn(‘TTRD_TRADE_FLOW’,‘EXE_MARKET’,‘VARCHAR2(20)’);
COMMENT on column TTRD_TRADE_FLOW.EXE_MARKET is ‘交易平台’;