问实时数据表到历史数据表的数据丢失.数据表

EN

Create Table real_data(
record_time timestamp(3),
ac_1 Float,
ac_2 Float,
ac_3 Float,
ac_4 Float,
ac_5 Float,
ac_6 Float,
ac_7 Float,
ac_8 Float,
ac_9 Float,
ac_10 Float,
ac_11 Float,
ac_12 Float,
ac_13 Float,
ac_14 Float,
ac_15 Float,
ac_16 Float
)
Tablespace data_test;

Create Table history_data(
record_time timestamp(3),
ac_1 Float,
ac_2 Float,
ac_3 Float,
ac_4 Float,
ac_5 Float,
ac_6 Float,
ac_7 Float,
ac_8 Float,
ac_9 Float,
ac_10 Float,
ac_11 Float,
ac_12 Float,
ac_13 Float,
ac_14 Float,
ac_15 Float,
ac_16 Float
)
Tablespace data_test
PARTITION BY RANGE(record_time)  
INTERVAL(numtodsinterval(1，'day'))  
(  
  PARTITION P1 VALUES LESS THAN (TO_DATE('2016-08-01', 'YYYY-MM-DD'))  
);  

alter table history_data add constraint RECORD_DATE primary key (RECORD_TIME);

create or replace procedure copy_to_history_test is 
d_top_backup_date  timestamp(3);
begin 

select top_backup_date into d_top_backup_date from real_data_top_backup_date;   

Insert Into history_data  Select *  From real_data where record_time <d_top_backup_date; 

delete from real_data where record_time <d_top_backup_date;  

Update real_data_top_backup_date Set top_backup_date=(d_top_backup_date+1/24/60);

commit; 

end copy_to_history_test;

Declare  
time_index Number;
start_time Timestamp(3);
tmp_time Timestamp(3); 
tmp_value1 Float;
tmp_value2 Float;
tmp_value3 Float;
tmp_value4 Float;
tmp_value5 Float;
tmp_value6 Float;
tmp_value7 Float;
tmp_value8 Float;
tmp_value9 Float;
tmp_value10 Float;
tmp_value11 Float;
tmp_value12 Float;
tmp_value13 Float;
tmp_value14 Float;
tmp_value15 Float;
tmp_value16 Float;


Begin

--initiaze the variable  
time_index:=0;      
 SELECT to_timestamp('2016-08-01 00:00:00:000', 'yyyy-mm-dd h24:mi:ss:ff')  Into start_time  FROM DUAL;  

      While time_index<(50*60*60*24*7)
       Loop
         -- add 20 millionseconds 
         SELECT start_time+numtodsinterval((0.02*time_index),'SECOND') Into tmp_time  FROM DUAL;
        -- dbms_output.put_line(tmp_time);
         -- create random number
         select dbms_random.value Into tmp_value1 from dual ;
         select dbms_random.value Into tmp_value2 from dual ;
         select dbms_random.value Into tmp_value3 from dual ;
         select dbms_random.value Into tmp_value4 from dual ;
         select dbms_random.value Into tmp_value5 from dual ;
         select dbms_random.value Into tmp_value6 from dual ;
         select dbms_random.value Into tmp_value7 from dual ;
         select dbms_random.value Into tmp_value8 from dual ;
         select dbms_random.value Into tmp_value9 from dual ;
         select dbms_random.value Into tmp_value10 from dual ;
         select dbms_random.value Into tmp_value11 from dual ;
         select dbms_random.value Into tmp_value12 from dual ;
         select dbms_random.value Into tmp_value13 from dual ;
         select dbms_random.value Into tmp_value14 from dual ;
         select dbms_random.value Into tmp_value15 from dual ;
         select dbms_random.value Into tmp_value16 from dual ;
         --dbms_output.put_line(tmp_value);

        -- Insert Into ac_data (sensor_id,data,record_time) Values(sensor_index,tmp_value,tmp_time);
        Insert Into real_data Values(tmp_time,tmp_value1,tmp_value2,tmp_value3,tmp_value4,tmp_value5,tmp_value6,tmp_value7,tmp_value8,tmp_value9,tmp_value10,tmp_value11,tmp_value12,tmp_value13,tmp_value14,tmp_value15,tmp_value16);
        if mod(time_index,50)=0 then
        commit; 
        dbms_lock.sleep(1);
        End If;

         time_index:=time_index+1;
       End Loop;

 --  dbms_output.put_line(c);
  Exception
   WHEN OTHERS THEN
    log_write('insert data failure!');
End;