oracle procedure

create or replace procedure px_dh_errworkf(pmonth in varchar2) is
/*
功能：重新执行本月短号集群网业务因超时失败的工单
*/
varsql varchar2(1000);
vmonth1 varchar2(6);
vmonth2 varchar2(6);
begin
vmonth1 := to_char(add_months(to_date(pmonth, 'YYYYMM'), -1), 'YYYYMM'); --当月
vmonth2 := pmonth; --本月

/*--创建临时表
create global temporary table tmpx_sp_workf_comm on commit preserve rows
as
select workf_seq,
rec_seq,
order_seq,
region,
order_id,
order_pri,
workf_id,
SORT_ORDER,
WORKF_TYPE,
PROC_SYSTEM,
PLAT_TYPE,
TELNUM,
IMSI,
CREATE_TIME,
MAX_PROCNUM,
MAX_OVERTIME,
1 oper_type,
NE_ID,
'NORMAL' CUR_STATUS,
oprid
from mmyy.sp_workf_comm_his
where 1 = 2;
*/

varsql:='truncate table tmpx_sp_workf_comm';
Begin
Execute Immediate varsql;
dbms_output.put_line('清表成功tmpx_sp_workf_comm');
Exception
When Others Then
Null;
End;
--播入上月底失败记录
varsql := 'insert into tmpx_sp_workf_comm
select workf_seq,
rec_seq,
order_seq,
region,
order_id,
order_pri,
workf_id,
SORT_ORDER,
WORKF_TYPE,
PROC_SYSTEM,
PLAT_TYPE,
TELNUM,
IMSI,
CREATE_TIME,
MAX_PROCNUM,
MAX_OVERTIME,
1,
NE_ID,
''NORMAL'',
oprid
from mmyy.sp_workf_comm_his partition(sp_workf_comm_his_' ||vmonth1 || ')
where cur_status <> ''SUCCESS''
and workf_id in
(''SMP0209'', ''SMP0210'', ''SMP0211'', ''SMP0212'', ''SMP0213'', ''SMP0223'',
''SMP0224'', ''SMP0226'', ''SMP0228'', ''SMP0245'', ''SMP0246'', ''OCS0004'')
and CREATE_TIME >=add_months(to_date(pmonth||''26000000'',''YYYYMMDDHH24MISS''),-1)
and NE_DESC in (''操作超时'', ''超时'', ''系统忙，请稍后重试'')';

Begin
Execute Immediate varsql;
dbms_output.put_line(vmonth1||'插入记录数：'||sql%rowcount);
Exception
When Others Then
Null;
End;

--从his表中删除记录
varsql := 'delete from mmyy.sp_workf_comm_his partition(sp_workf_comm_his_' ||vmonth1 || ')
where workf_seq in (select workf_seq from tmpx_sp_workf_comm)';
Begin
Execute Immediate varsql;
dbms_output.put_line(vmonth1||'删除记录数：'||sql%rowcount);
Exception
When Others Then
Null;
End;

--播入本月底失败记录
varsql := 'insert into tmpx_sp_workf_comm
select workf_seq,
rec_seq,
order_seq,
region,
order_id,
order_pri,
workf_id,
SORT_ORDER,
WORKF_TYPE,
PROC_SYSTEM,
PLAT_TYPE,
TELNUM,
IMSI,
CREATE_TIME,
MAX_PROCNUM,
MAX_OVERTIME,
1,
NE_ID,
''NORMAL'',
oprid
from mmyy.sp_workf_comm_his partition(sp_workf_comm_his_' ||vmonth2 || ')
where cur_status <> ''SUCCESS''
and workf_id in
(''SMP0209'', ''SMP0210'', ''SMP0211'', ''SMP0212'', ''SMP0213'', ''SMP0223'',
''SMP0224'', ''SMP0226'', ''SMP0228'', ''SMP0245'', ''SMP0246'', ''OCS0004'')
and NE_DESC in (''操作超时'', ''超时'', ''系统忙，请稍后重试'')';
Begin
Execute Immediate varsql;
dbms_output.put_line(vmonth2||'插入记录数：'||sql%rowcount);
Exception
When Others Then
Null;
End;
--从his表中删除记录
varsql := 'delete from mmyy.sp_workf_comm_his partition(sp_workf_comm_his_' || vmonth2 || ')
where workf_seq in (select workf_seq from tmpx_sp_workf_comm)';
Begin
Execute Immediate varsql;
dbms_output.put_line(vmonth2||'删除插入记录数：'||sql%rowcount);
Exception
When Others Then
Null;
End;

--放入待处理表执行
insert into mmyy.sp_workf_comm
(workf_seq,
rec_seq,
order_seq,
region,
order_id,
order_pri,
workf_id,
sort_order,
workf_type,
PROC_SYSTEM,
PLAT_TYPE,
TELNUM,
IMSI,
CREATE_TIME,
MAX_PROCNUM,
MAX_OVERTIME,
oper_type,
NE_ID,
CUR_STATUS,
oprid)
select *
from tmpx_sp_workf_comm
where workf_seq in (select workf_seq from tmpx_sp_workf_comm);

dbms_output.put_line('插入待处理记录数：'||sql%rowcount);

commit;

end px_dh_errworkf;
/