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); ---------执行sqlv_day varchar2(2); ---------日期（天数）v_errorcode number(10); ---------状态报告v_ismg_status varchar2(40); ---------状态报告v_msgid number(20); ---------detail表标示idv_clientId varchar(40); ---------唯一标示idv_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<'||i||')'; 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 asselect "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-2012 21:55:27', 'dd-mm-yyyy hh24:mi:ss'), interval => 'sysdate+5/1440'); commit;end;/?