oracle 子分区清空,清空分区

create or replace procedure sp_trunc_partition

(iv_table_owner     in varchar2,

iv_table_name      in varchar2,

iv_partition_name  in varchar2,

ii_partition_value in varchar2,

iv_tablespace_name in varchar2,

oi_return out integer)

is

* @parameter oi_return            out integer      执行状态码，0 正常，其它 出错

*/

vi_result   number(10);

vv_task_pos varchar2(30);

vv_sql      varchar2(1024); -- 动态sql

vi_err_code integer; -- 错误信息代码

begin

vv_task_pos := '分区判断';

select count(*)

into vi_result

from all_tab_partitions a

where a.table_owner = upper(iv_table_owner)

and a.table_name = upper(iv_table_name)

and a.partition_name = upper(iv_partition_name);

--分区存在  list分区truncate分区资料

if vi_result > 0 then

vv_task_pos := '清空分区';

vv_sql      := 'alter table ' || iv_table_owner || '.' ||

iv_table_name || ' truncate partition ' ||

iv_partition_name;

execute immediate vv_sql;

end if;

if vi_result = 0 then

vv_task_pos := '新增分区';

vv_sql      := 'alter table ' || iv_table_owner || '.' ||

iv_table_name || ' add partition ' ||

iv_partition_name || ' values( '|| ii_partition_value|| ') tablespace ' ||

iv_tablespace_name;

execute immediate vv_sql;

end if;

oi_return := 0;

exception

when others then

vi_err_code := sqlcode;

oi_return := vi_err_code;

dbms_output.put_line(sqlcode||sqlerrm);

end sp_trunc_partition;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12961536/viewspace-1061409/，如需转载，请注明出处，否则将追究法律责任。