1.新建一个test.sql,适用于新增字段 (DML) ,但不确定是否含有该字段,内容如下:
num number;
begin
SELECT COUNT(1) into num FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'table1' AND COLUMN_NAME = 'cols1';
if num=0 then
execute immediate'ALTER TABLE table1 MODIFY (cols2 NUMBER(1),
cols3 NUMBER(1),
cols4 NUMBER(1))
ADD (cols5 varchar2(9),
cols1 varchar2(6));';
end if;
2.新建一个test2.sql,适用于创建表 (DDL) ,但不确定是否存在该表,内容如下:
num number;
begin
select count(1) into num from user_tables where TABLE_NAME = 'GP_PUR_CONTRACT_USEPLAN';
if num=0 then
execute immediate'
create table table2
(
cols1 varchar2(18) not null,
cols2 NUMBER(1) not null,
cols3 NUMBER(1),
cols4 NUMBER(1))
)
end if;