oracle 存储过程根据传入的月份分表（根据时间月份分表），动态创建表，分区，索引

CREATE OR REPLACE PROCEDURE Pro_scada_AUTO_CREATETABLE(collection_time IN VARCHAR2,state OUT NUMBER) IS
  /**
  *本存储过程作用：
  *1、根据解析E文件的数据，自动创建当前月份的表（比如此数据日期是20190801，就会创建sgcm_scada_device_data_201908，将此数据存入此表）  
  */   
  tabName VARCHAR2(200);            ----------表名
  tabCount NUMBER;                  ----------此表的出现次数
  p_msg VARCHAR2(250);              ---------错误消息 
  collection  VARCHAR2(250);        --时间的年月 
  v_sql     varchar2(1000);
BEGIN
  state := 0;   --状态码默认0
  --获取月份
  collection := to_char(to_date(collection_time,'yyyy-mm-dd hh24:mi:ss'),'yyyymm');
  --拼接表
  tabName := 'sgcm_scada_device_data_' || collection;  
  --查看是否存在此表
  SELECT COUNT(1) INTO tabCount FROM user_tables WHERE table_name = UPPER(tabName);
  if tabCount = 0 THEN  
  -- Create table
  EXECUTE IMMEDIATE 'create table '||tabName||'
  (
    uuid                 VARCHAR2(64) not null,
    substation_name      VARCHAR2(120),
    device_name          VARCHAR2(256),
    device_id            VARCHAR2(64),
    active_power_value   NUMBER(38,5),
    active_power_state   NUMBER(2),
    reactive_power_value NUMBER(38,5),
    reactive_power_state NUMBER(2),
    collection_time      DATE,
    apparent_power       NUMBER(38,5),
    load_rate            NUMBER(38,5),
    electric_current     NUMBER(38,5),
    gear_position        NUMBER(38,5),
    bureau_name          VARCHAR2(64),
    a_current            NUMBER(38,5),
    b_current            NUMBER(38,5),
    c_current            NUMBER(38,5),
    voltage_level        VARCHAR2(64),
    rated_capacity       NUMBER(38,5) default 100
  )
  PARTITION BY RANGE (collection_time) INTERVAL (NUMTODSINTERVAL(1,''DAY'')) 
  (
     PARTITION TOP_LOG_USER_LOGIN_DETAIL_P1 VALUES LESS THAN (TO_DATE(''2010-05-01'', ''YYYY-MM-DD''))
  )';
  v_sql := 'comment on table '||tabName||' is ''主变信息''';  
  execute immediate v_sql;
  
  -- Add comments to the columns 
  v_sql := 'comment on column '||tabName||'.uuid is ''UUID''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.substation_name is ''厂站名''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.device_name is ''设备名称''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.device_id is ''设备id''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.active_power_value is ''有功值''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.active_power_state is ''有功状态''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.reactive_power_value is ''无功值''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.reactive_power_state   is ''无功状态''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.collection_time  is ''采集时间''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.apparent_power  is ''视在功率''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.load_rate  is ''负载率''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.electric_current is ''电流''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.gear_position   is ''档位''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.bureau_name  is ''供电局''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.a_current   is ''a相电流''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.b_current  is ''b相电流''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.c_current   is ''c相电流''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.voltage_level   is ''电压等级（kV）''';
  execute immediate v_sql;
  v_sql := 'comment on column '||tabName||'.rated_capacity   is ''容量''';
  execute immediate v_sql;
  -- Create/Recreate indexes 
  v_sql := 'create index INX_BUREAU_NAME'||collection||' on '||tabName||' (BUREAU_NAME)
    tablespace GZ_DDW_TBS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )';
  execute immediate v_sql;
  v_sql := 'create index INX_COLLECTION_TIME'||collection||' on '||tabName||' (COLLECTION_TIME)
    tablespace GZ_DDW_TBS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )';
  execute immediate v_sql;
  v_sql := 'create index INX_COMBINATION'||collection||' on '||tabName||' (DEVICE_ID, COLLECTION_TIME, APPARENT_POWER, ACTIVE_POWER_VALUE, REACTIVE_POWER_VALUE)
    tablespace GZ_DDW_TBS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )';
  execute immediate v_sql;
  v_sql := 'create index INX_DEVICE_ID'||collection||' on '||tabName||' (DEVICE_ID)
    tablespace GZ_DDW_TBS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )';
  execute immediate v_sql;
  v_sql := 'create index INX_DEVICE_NAME'||collection||' on '||tabName||' (DEVICE_NAME)
    tablespace GZ_DDW_TBS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )';
  execute immediate v_sql;
  v_sql := 'create index INX_SUBSTATION_NAME'||collection||' on '||tabName||' (SUBSTATION_NAME)
    tablespace GZ_DDW_TBS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )';
  execute immediate v_sql;
  v_sql := 'create index INX_VOLTAGE_LEVEL'||collection||' on '||tabName||' (DEVICE_ID, COLLECTION_TIME)
    tablespace GZ_DDW_TBS
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )';
   execute immediate v_sql;
   --表示创建动态表成功
   state := 1;
  ELSE 
    --tableCount>0，表示已经存在此月份的表
   state := 2;
 end if;
EXCEPTION
  WHEN OTHERS THEN
     --回滚
    ROLLBACK;
    --打印异常消息
    p_msg := sqlcode || sqlerrm;
    dbms_output.put_line(p_msg);
    --存储过程执行失败，记录失败信息
    INSERT INTO ECMS_PROC_LOG
      (LOGID, PROC_NAME, DES, EXETIME, ISSUCCESS, ENDTIME)
    VALUES
      (SYS_GUID(),
       'sgcm_scada_device_data',
       '修改调度数据失败，异常信息:' || p_msg,
       SYSDATE,
       -1,
       SYSDATE);
    COMMIT;
end;