Oracle存储过程(定时更新短信状态报告)
1、定时更新异网短信状态报告，后台程序接收状态报告errorcode和标示client_id存入yw_detail表中
2、log_yw_mobile、yw_detail、和sms_mt_send_detail+日期，三表关联更新数据
3、存储过程：
-------- 异网短信更新
create or replace procedure pro_update_yw_sms_detail is
TYPE TYPE_CURSOR IS REF CURSOR;
v_detail_cur TYPE_CURSOR;
v_sql varchar2(2500); ---------执行sql
v_day varchar2(2); ---------日期(天数)
v_errorcode number(10); ---------状态报告
v_ismg_status varchar2(40); ---------状态报告
v_msgid number(20); ---------detail表标示id
v_clientId varchar(40); ---------唯一标示id
v_start_time number(10); --------统计起始时间
v_end_time number(10); -------统计结束时间
i number; -----------处理的个数
begin
i:=0;
select date_to_num(sysdate) into v_start_time from dual;
open v_detail_cur for select msgid,client_id,dd,errorcode,statu from yw_detail_info;
loop
fetch v_detail_cur into v_msgid,v_clientId,v_day,v_errorcode,v_ismg_status;
if v_detail_cur%notfound then
exit;
end if;
execute immediate 'update sms_mt_send_detail'||v_day||' set ERRORCODE = '||v_errorcode||',ISMG_STATUS='''||v_ismg_status||''' where MT_SEND_DETAIL_ID ='||v_msgid;
i:=i+1;
if i>100 then
commit;
end if;
end loop;
close v_detail_cur;
v_sql:='delete from yw_detail where client_id in (select client_id from (select client_id from yw_detail order by client_id asc) where rownum
execute immediate v_sql;
select date_to_num(sysdate) into v_end_time from dual;
insert into log_procedures(create_time,procedure_name,deal_result,flag,deal_time)
values(sysdate,'异网短信状态更新：pro_update_yw_sms_detail','处理了'||i||'个数据',1,v_end_time-v_start_time);
commit;
end pro_update_yw_sms_detail;
4、视图view：
acreate or replace view yw_detail_info as
select "MSGID","CLIENT_ID","DD","ERRORCODE","STATU" from (select logg.msgid,logg.client_id,to_char(logg.send_time,'dd') dd,decode(detail.errorcode,'DELIVRD','0','0','0','1111') errorcode,decode(detail.errorcode,'0','DELIVRD','DELIVRD','DELIVRD',detail.errorcode) statu from log_yw_mobile logg
inner join yw_detail detail on logg.client_id = detail.client_id
order by logg.client_id asc ) where rownum<1000;
5、遇到的问题：
1)、ORA-00904: "EXPIR602": invalid identifier
这个是由于update的时候sms_mt_send_detail表中的errorcode是number类型，而上面取得的v_errorcode里面含有字母，所以一直报这个错。所以视图中把DELIVRD替换成0，其他的都替换成1111，这样插入就不会有问题了。
2)、ismg_status是varchar类型，所以得把引号带上:ISMG_STATUS='''||v_ismg_status||'''
3)、三张表关联时把关联的字段建立索引，如：
create index yw_detail_clientId on yw_detail(client_id);
4)、之前本来想着update一条就delete一条，感觉效率太低，直接每次取前1000条，删除起来就方便多了。
6、Job定时执行存储过程(每过五分钟执行一次)：
begin
sys.dbms_job.submit(job => :job,
what => 'pro_update_yw_sms_detail;',
next_date => to_date('21-11- 21:55:27', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+5/1440');
commit;
end;
/
我的异常网推荐解决方案：oracle存储过程,/oracle-develop/177537.html
