自动创建表分区

create or replace package body PKG_AutoPartition is
– Author : *
– Created : *
– Purpose : 自动创建分区表
procedure Pro_Add_Partition
as
v_table_name varchar2(50); --表名
v_partition_name varchar2(50);–分区名
v_day varchar2(20);–天
v_sql_string varchar2(2000);–拼接的sql
v_add_day varchar2(20);–分区表 日期
cursor cur_part is select distinct u.table_name,max(p.partition_name) max_part_name from user_tables u,user_tab_partitions p
where u.table_name=p.table_name and u.partitioned = ‘YES’
group by u.table_name;
Begin
select to_char(sysdate,‘yyyymmdd’) into v_day from dual;–20180401
select to_char(sysdate+14,‘yyyymmdd’) into v_add_day from dual;–20180415
open cur_part;
loop
fetch cur_part into v_table_name,v_partition_name;
exit when cur_part%notfound;
if to_number(substr(v_partition_name,6)) <to_number(v_day) then

v_sql_string :=‘alter table ‘||v_table_name||’ add partition PART_’||v_add_day||
’ VALUES LESS THAN ( to_date(’’’||v_add_day||’ 23:59:59’’,’‘yyyyMMdd HH24:mi:ss’’) ) tablespace TJOFFICIALVEHICLEDB’;
execute immediate v_sql_string;
else
null;
end if;
end loop;
close cur_part;
end Pro_Add_Partition;
end PKG_AutoPartition;
– 带参数
create or replace package body PKG_GetOrbitData is
–获取轨迹信息
procedure Pro_GetOrbitDataInfo
(
vehicleid_in number, --车辆ID
starDate varchar2, --开始时间
endData varchar2, --结束时间
cur_OUT out pkg_general.ROW_CURSOR
)
AS
BEGIN
OPEN cur_OUT FOR
select * from reallocate t
where t.VEHICLEID = vehicleid_in
and t.devtime>=to_date(starDate,‘yyyy-MM-dd HH24:mi:ss’)
and t.devtime<=to_date(endData,‘yyyy-MM-dd HH24:mi:ss’)
order by t.devtime ;

end Pro_GetOrbitDataInfo;
end PKG_GetOrbitData;
–执行查询存储过程用到
create or replace package pkg_general is
TYPE ROW_CURSOR IS REF CURSOR;
end pkg_general;
create or replace package body pkg_general is
end pkg_general;