oracle 查询判断语句.docxVIP

oracle 查询判断语句 1.查询表是否存在，如果不存在，就添加 select count(*)into v_countfrom user_all_tableswhere table_name = A;if v_count = 0 thenexecute immediate create table A(ID NUMBER not null;end If; 2.查询表的主键是否存在，不存在就添加主键 select count(*)into v_countfrom user_cons_columns awhere a.constraint_name = PK_Aand a.table_name = A;if v_count = 0 thenexecute immediate alter table Aadd constraint PK_A primary key (ID)using index tablespace DAAN_DATApctfree 10initrans 2maxtrans 255storage(initial 64Kminextents 1maxextents unlimited);end if; 3.查询表的序列是否存在，不存在的话，添加 select count(*)into v_countfrom user_sequenceswhere sequence_name = SEQ_A_ID;if v_count = 0 thenexecute immediate create sequence SEQ_A_IDminvalue 1 maxvalue 9999999999999999 start with 61 increment by 1 cache 20;end if; 4.判断表是否存在该字段，不存在的话，就添加 select COUNT(*)into v_countfrom user_tab_columnswhere table_name = Aand column_name = CREATE_TIME;if v_count = 0 thenexecute immediate alter table A add(CREATE_TIME DATE default SYSDATE not null);end if; 5.判断表是否存在该条数据，如果不存在，就添加，存在，就修改 merge into A ausing (select count(*) num from A where id = 1) con (c.num 0) WHEN MATCHED THEN update set a.id=2 where a.id=1WHEN not MATCHED THENinsert(id)values(1);commit;