oracle存储过程要在异常下删除运行中创建的三个表
CREATE OR REPLACE PROCEDURE P_CREATE_TABLE_NEW(i_user_id in NUMBER) is
  v_part char(9);
  --v_day char(8);
  v_limit char(8);
  v_table_name varchar2(50);
begin
  execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD''';

  v_part := to_char((sysdate -1), 'yyyymmdd');

  v_limit := to_char(add_months(trunc(sysdate), -3) + 1, 'yyyymmdd');
  -- for i in (select channel_id from cpis_channel_list) loop
  v_table_name := 'STAT_1DAY_VISITOR_' || i_user_id;
  execute immediate 'create table ' || v_table_name || '
  (
  ENDTIME date not null,
  CUSTOMER_ID number(5) not null,
  IP VARCHAR2(20) not null,
  WEBSITE VARCHAR2(2000) not null,
  VISIT_COUNT NUMBER(10),
  FLUX NUMBER(30,2)
  )
  partition by range (ENDTIME)
  (
  partition P' || v_part ||
  ' values less than ( to_date(''' || v_limit ||
  ''',''YYYYMMDD'')' || ')
  tablespace LOGSTAT
  )';

  execute immediate 'alter table ' || v_table_name ||
  ' add constraint PK_1DAY_VISITOR_' || i_user_id ||
  ' primary key (ENDTIME, CUSTOMER_ID, IP, WEBSITE) using index tablespace logstat_idx local';

  p_add_daily_partition(pi_table_name => v_table_name,
  pi_tablespace_name => 'logstat',
  pi_par_num => 120);

  v_table_name := 'stat_1DAY_domain_' || i_user_id;
  execute immediate 'create table ' || v_table_name || '
  (
  ENDTIME date not null,
  CUSTOMER_ID number(5) not null,
  WEBSITE VARCHAR2(2000) not null,
  DOMAIN VARCHAR2(2000) not null,
  VISIT_COUNT NUMBER(10),
  FLUX NUMBER(30,2),
  HIT_COUNT NUMBER(10),
  MISS_COUNT NUMBER(10),
  HIT_FLUX NUMBER(20,2),
  MISS_FLUX NUMBER(20,2)
  )
  partition by range (ENDTIME)
  (
  partition P' || v_part ||
  ' values less than ( to_date(''' || v_limit ||
  ''',''YYYYMMDD'')' || ')
  tablespace LOGSTAT
  )';

  execute immediate 'alter table ' || v_table_name ||
  ' add constraint PK_1DAY_domain_' || i_user_id ||
  ' primary key (ENDTIME, CUSTOMER_ID,WEBSITE, DOMAIN) using index tablespace logstat_idx local';

  p_add_daily_partition(pi_table_name => v_table_name,
  pi_tablespace_name => 'logstat',
  pi_par_num => 120);

  v_table_name := 'STAT_1DAY_URL_' || i_user_id;
  --v_table_name2 := 'stat_1h_domain_' || i.channel_id;
  execute immediate 'create table ' || v_table_name || '
  (
  ENDTIME date not null,
  CUSTOMER_ID number(5) not null,
  URL VARCHAR2(4000) not null,
  VISIT_COUNT NUMBER(10),
  FLUX NUMBER(30,2),
  HIT_COUNT NUMBER(10),
  MISS_COUNT NUMBER(10),
  HIT_FLUX NUMBER(20,2),
  MISS_FLUX NUMBER(20,2)
  )
  partition by range (ENDTIME)
  (
  partition P' || v_part ||
  ' values less than ( to_date(''' || v_limit ||<