oracle 每月自动创建下个月的数据表

1.自动建表的存储过程

create or replace procedure sp_createtab_t_data_sensor
Authid Current_User
as
tabname varchar(200);
seqname varchar(200);
tabCount number;
seqCount number;
begin
select 'T_DATA_SENSOR_' || to_char(sysdate+1, 'yyyymm') into tabname from dual;
select 'SEQ_DATA_SENSOR_' || to_char(sysdate+1, 'yyyymm') into seqname from dual;
select  count(1) into  tabCount  from user_tables  where  table_name=tabname;
SELECT count(1) into seqCount FROM All_Sequences where SEQUENCE_NAME=seqname;
--如果存在,则删除该表
if  tabCount>0  then
   -- dbms_output.put_line('表或视图不存在!');
    execute   immediate  'drop table  '||tabname;                                           
end   if;
--创建
 execute immediate 'create table '||tabname||'
(
  SENSORID NUMBER(10) not null,
  VALUE    NUMBER(10,4),
  UNIT     VARCHAR2(50),
  GASNAME  VARCHAR2(50),
  STATUS   VARCHAR2(50),
  REVDATE  DATE not null,
  ID       NUMBER(30) not null,
  AID      NUMBER not null,
  DATADATE DATE default SYSDATE not null
)';
--如果存在,则删除该序列
if  seqCount>0  then
   -- dbms_output.put_line('表或视图不存在!');
    execute   immediate  'drop SEQUENCE  '|| seqname;                                           
end   if;
--tabCount := 0;
--seqCount := 0;
execute immediate'create sequence ' || seqname;
commit;
end;

2.job定时任务执行（每月最后一天的凌晨一点执行）

declare 
-- Local variables here 
v_jobnum number;   
begin 
-- Test statements here 
dbms_job.submit(v_jobnum,'sp_createtab_t_data_sensor;',sysdate,'TRUNC(LAST_DAY(SYSDATE))+1/24'); 
dbms_output.put_line('job '||v_jobnum||' has been submitted.'|| to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') ); 
dbms_job.run(v_jobnum,TRUE);  
COMMIT;   
end;