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

布满荆棘的人生 2024-03-27 17:51 64阅读 0赞

#### 文章目录 ####

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

## 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;