初体验Oracle11g分区

无心徘徊

--创建表
create table us_pwdwronglimit
(
   msisdn varchar2(11) not null,
   limitdate date not null,
   times number(3) not null
)
partition by range (limitdate)
interval (numtodsinterval(1,'day'))
(
partition p1 values less than (to_date('2011-01-01','yyyy-mm-dd')) tablespace tbs_mread_dat
);
-- Add comments to the columns
comment on column us_pwdwronglimit.msisdn
  is '手机号码';
comment on column us_pwdwronglimit.limitdate
  is '限制日期';
comment on column us_pwdwronglimit.times
  is '错误次数';
create index  us_pwdwronglimit_local on us_pwdwronglimit(msisdn) tablespace tbs_mread_idx local;
--删除分区数据业务
CREATE OR REPLACE PROCEDURE PRO_PWDWRONGLIMIT_CTRL
is
names         varchar2(1024);    --分区名称
dates         varchar2(1024);     --分区日期
temp          varchar2(1024);
v_temp        varchar2(1024);
v_temp_sys    varchar2(1024);
v_sql         varchar2(1024);
v_cs          varchar2(1024);
v_error       varchar2(1024);
TYPE t_ref_cursor IS REF CURSOR;
  c t_ref_cursor;
--获取分区列表
begin
v_cs := 'select PARTITION_NAME,HIGH_VALUE
  from USER_TAB_PARTITIONS
where TABLE_NAME = upper(''us_pwdwronglimit'') and PARTITION_NAME <> ''P1''';
  open c for v_cs;
  fetch c
  into names,dates;
   while c% found loop
       --获得分区时间
       v_temp := 'select to_char('||dates||',''yyyy-MM-dd'') from dual';
       execute immediate v_temp into temp ;
      
       --获取昨天
       v_temp := 'select to_char(sysdate-1,''yyyy-mm-dd'') from dual';
        execute immediate v_temp into v_temp_sys ;

        --时间比较
       if (to_date(temp,'yyyy-MM-dd') <= to_date(v_temp_sys,'yyyy-MM-dd'))
       then
             ---删除分区
            v_sql := 'alter table us_pwdwronglimit truncate partition ' ||names;
             execute immediate v_sql;
       end if;
   --取下一条数据
   fetch c  into names,dates;
   end loop;
   close c;
   commit;

   exception
   when others then
   v_error := sqlcode || ' - ' || sqlerrm;
   prc_iread_sys_writelog (2,4,'PRO_PWDWRONGLIMIT_CTRL',v_error,'');
rollback;
end PRO_PWDWRONGLIMIT_CTRL;
/
begin
  sys.dbms_scheduler.create_job(job_name            => 'REG_PWDWRONGLIMIT',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'PRO_PWDWRONGLIMIT_CTRL',
                                start_date          => to_date('01-01-2011 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
                                end_date            => to_date(null),
                                job_class           => 'DBMS_JOB$',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;
/