每月切表存储过程雏形

有些表数据量很大，每个月初要切出去。想写一个存储过程达到这个目地。

原来的存储过程涉及到跨年就会出现年份不正确的问题。

每月切monitor_data表，切后的表名格式为monitor_data_yyyymm(monitor_data_201112)

declare
  r_time date;
  string_ varchar(200);
begin
  select sysdate-1 into r_time from dual;
  string_:=to_char(r_time,'yyyy')||to_char(r_time,'mm');
  execute immediate 'alter  table MONITOR_DATA rename to MONITOR_DATA_'||string_;
end;
/

另外一种笨方法：更加直观。

declare
 
r_time date;
 
r_time2 varchar2(20);
 
string_ varchar(200);
begin
 
select sysdate-1 into r_time from dual;
 
select to_char(r_time,'yyyy')||to_char(r_time,'mm') into r_time2 from dual;
 
string_:=r_time2;
 
execute immediate 'alter table MONITOR_DATA rename to MONITOR_DATA_'||string_;
 
dbms_output.put_line(string_);
end;
/

思路来源

declare
 
r_time date;
 
r_time2 varchar2(20);
 
string_ varchar(200);
begin
 
select sysdate-1 into r_time from dual;
 
select to_char(r_time,'yyyy')||to_char(r_time,'mm') into r_time2 from dual;
 
dbms_output.put_line(r_time2);
end;
/