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