Oracle中表或视图不存在自动创建-向日葵屋

Oracle中表或视图不存在自动创建

文章目录

Oracle中表或视图不存在自动创建
- 1. 【表】不存在自动创建：
- 1. 【视图】不存在自动创建：

Oracle中表或视图不存在自动创建

1. 【表】不存在自动创建：

declare
  tableExist number;
begin
  select count(1)
    into tableExist
    from user_tables
   where table_name = upper('CA_CALLBACKDATA');
  if tableExist = 0 then
    execute immediate 'create table CA_CALLBACKDATA
      (
        ID          VARCHAR2(50) not null,
        JSONDATA    CLOB,
        CREATEDATE  DATE,
        UPDATEDATE  DATE,
        UPDATETIMES NUMBER
      )';
    execute immediate 'comment on table CA_CALLBACKDATA
      is ''Ca接口数据回写表''';
    execute immediate 'comment on column CA_CALLBACKDATA.ID
      is ''主键Id''';
    execute immediate 'comment on column CA_CALLBACKDATA.JSONDATA
      is ''Ca接口回写的Json数据''';
    execute immediate 'comment on column CA_CALLBACKDATA.CREATEDATE
      is ''创建时间''';
    execute immediate 'comment on column CA_CALLBACKDATA.UPDATEDATE
      is ''更新时间''';
    execute immediate 'comment on column CA_CALLBACKDATA.UPDATETIMES
      is ''更新的次数''';
    execute immediate 'alter table CA_CALLBACKDATA
      add constraint PK_CA_CALLBACKDATA_ID primary key (ID)';
  end if;
end;

2. 【视图】不存在自动创建：

declare viewExist number;
begin
    select count(1) into viewExist from user_views where VIEW_NAME='REPORT_APPOINTMENTSBYLOCATORS';
    if viewExist=0 then
        execute immediate
        'CREATE  VIEW REPORT_APPOINTMENTSBYLOCATORS
        AS
        SELECT        appointmentID, startDateTime, endDateTime, grid, locator, 
                      meetingAddress, contactNumber, company, contact, reasonForAppointment,
                      DtFirstCall, DtSecondCall,CustomerAnswerFirstCall, CustomerAnswerSecondCall, 
                      IsMeetOnSite, DtLastModified, AppointmentStatus, IsLocatorArriveOnsite, 
                      DtCompletedAt,DtLocatorArriveOnsite, RescheduledOn, RescheduledAt, IsRescheduled, city
        FROM   Appointment';
    end if;
end;