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<'||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 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-2012 21:55:27', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'sysdate+5/1440');
  commit;
end;
/