oracle与mysql判断字段是否存在不存在则添加

mysql information_schema.columns表名不区分大小写 =0表示不存在



delimiter //
drop procedure if exists aaa;
create procedure aaa()
begin
declare
colnum  int;

select count(*) into colnum from information_schema.columns where table_schema = DATABASE()  and table_name = '表名'  and column_name = '列名';
if colnum = 0 then
alter table 表名 add 列名VARCHAR(2);
end if;

select count(*) into colnum from information_schema.columns where table_schema = DATABASE()  and table_name = '表名'  and column_name = '列名';
if colnum = 0 then
alter table 表名 add 列名VARCHAR(2);
end if;

end //
delimiter ;

call aaa;

drop procedure if exists aaa;

oracle user_tab_columns   注：表名区分大小写

DECLARE num NUMBER;
BEGIN
SELECT count( 1 ) INTO num  FROM user_tab_columns  WHERE table_name = '表名'  AND column_name = '列名';
IF
num = 0 THEN
         execute IMMEDIATE 'ALTER TABLE 表名 ADD 列名VARCHAR2(2)';
END IF;

END;
/