oracle sql 判断字段是否存在

set define off;
declare
  is_exist  NUMBER;
  alter_sql VARCHAR2(4000) := 'alter table tableName add columnName VARCHAR2(100 BYTE) DEFAULT null';
begin
  select count(1) into is_exist from dba_tab_columns where table_name = tableName and column_name=columnName;
  if is_exist = 0 then
    execute immediate alter_sql;
  end if;
end;
/
update jgrid set columnName= ‘jsFunction(obj)' where whereclause;

commit;

 

declare is_exist  NUMBER;
  alter_sql VARCHAR2(4000) := 'alter table tableName add CONSTRAINT constraintName unique ( columnName1,columnName2)';
begin
  select count(1) into is_exist from dba_objects where object_name = constraintName;
  if is_exist = 0 then
    execute immediate alter_sql;
  end if;
end;
/

declare
  is_exist  NUMBER;
  alter_sql VARCHAR2(4000) := '
  CREATE SEQUENCE  seqname MINVALUE 1
  MAXVALUE 1000000000000000000000000000
  INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE';
begin
  select count(1) into is_exist from dba_objects where object_name = seqname  and object_type <> 'SYNONYM';
  if is_exist = 0 then
    execute immediate alter_sql;
  end if;
end;
/