使用job和存储过程定期的删除库中的数据

 

CREATE OR REPLACE PROCEDURE sp_kpi_Dropdata
 IS
   vv_starttime    NUMBER(10);
   vn_min_days     NUMBER;
   vn_hour_days    NUMBER;
   vv_sqlstr       VARCHAR2(4000);
BEGIN

   vn_min_days    := 2; --days
   vn_hour_days   := 7; --days

   BEGIN
      -- table's history data
     FOR v_cursor IN (SELECT TABLE_NAME
     --当前用户可访问的所有分区表的详细分区信息
     FROM ALL_tab_partitions a
     WHERE TABLE_NAME  in                         

('PB_BSC_KQI_RTKPI_HOU','PB_BSC_KQI_RTKPI_MIN','PB_CELLGROUP_KQI_RTKPI_HOU','PB_CELLGROUP_KQI_RTKPI_MIN'
      ,'PB_CITY_KQI_RTKPI_HOU','PB_CITY_KQI_RTKPI_MIN','PB_MSC_KQI_RTKPI_HOU','PB_MSC_KQI_RTKPI_MIN' )) 
  
    loop
         IF (v_cursor.TABLE_NAME like '%MIN')  THEN
            vv_starttime := (trunc(sysdate)-vn_min_days-1-to_date('19700101','yyyymmdd'))*86400-1;
         ELSIF (v_cursor.TABLE_NAME like '%HOU') THEN
            vv_starttime := (trunc(sysdate)-vn_hour_days-1-to_date('19700101','yyyymmdd'))*86400-1;
         END IF;

            BEGIN
               vv_sqlstr := 'delete from '||
                            v_cursor.table_name || ' where starttime<= ' ||
                            vv_starttime;

               EXECUTE IMMEDIATE vv_sqlstr;
                    commit;
            EXCEPTION
               WHEN OTHERS THEN
                     rollback;

            END;
      END LOOP;

end;
END;
/