oracle 查询判断语句

1.查询表是否存在，如果不存在，就添加

select count(*)
    into v_count
    from user_all_tables
   where table_name = 'A';
  if v_count = 0 then
    execute immediate 'create table A(ID NUMBER not null';
  end If;

 

2.查询表的主键是否存在，不存在就添加主键

select count(*)
    into v_count
    from user_cons_columns a
   where a.constraint_name = 'PK_A'
     and a.table_name = 'A;
  if v_count = 0 then
    execute immediate 'alter table A
                        add constraint PK_A primary key (ID)
                        using index
                        tablespace DAAN_DATA
                        pctfree 10
                        initrans 2
                        maxtrans 255
                        storage
                        (
                          initial 64K
                          minextents 1
                          maxextents unlimited
                        )';
  end if;

3.查询表的序列是否存在，不存在的话，添加

select count(*)
    into v_count
    from user_sequences
   where sequence_name = 'SEQ_A_ID';
  if v_count = 0 then
    execute immediate 'create sequence SEQ_A_ID
            minvalue 1 maxvalue 9999999999999999 start with 61 increment by 1 cache 20';
  end if;

 

4.判断表是否存在该字段，不存在的话，就添加

select COUNT(*)
    into v_count
    from user_tab_columns
   where table_name = 'A'
     and column_name = 'CREATE_TIME';
  if v_count = 0 then
    execute immediate 'alter table A add(CREATE_TIME DATE default SYSDATE not null)';
  end if;

5.判断表是否存在该条数据，如果不存在，就添加，存在，就修改

merge into A a
  using (select count(*) num from A where id = 1) c
  on (c.num <> 0)

  WHEN MATCHED THEN

  update set a.id=2 where a.id=1
  WHEN not MATCHED THEN
    insert
      (id)
    values
      (1);
  commit;