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

来源：互联网发布：java软件结构编辑：程序博客网时间：2024/05/30 23:21

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;
/