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

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;