oracle存储过程在项目中的运用

昌丽思

create or replace procedure PRC_MAIL_FULL_DLV_SAMPLE IS
   
    reportStatDate varchar(8);
    sqlText varchar(2000);
    v_etl_temp_time date;
    cursor p_cursor is select sa.mail_num,            
       sa.last_query_time,                                
       mi.clct_time,                                      
       sa.full_dlv_time,                                 
       MAX_DAYS                                          
     from MAIL_FULL_DLV_SAMPLE sa, BASE_FULL_DIST fd,FACT_MAIL_INFO mi
        where  sa.RCV_CODE=fd.DLV_DISTRICT_CODE
and fd.PLAN_TYPE=0
and sysdate-mi.clct_time>PLAN_DAYS
and IF_OUTTIME=0 ;   --cursor end;
v_mailnum varchar2(20);
v_lastquerytime date;
v_clcttime date;
v_fulldlvtime date;
v_maxdays number;
  BEGIN
    v_etl_temp_time := SYSDATE;
    ---1.1、广东收寄且直封的按10天前直封封发每个邮袋抽一个邮件，插入抽样表
  
    insert into MAIL_FULL_DLV_SAMPLE (MAIL_NUM,SUM_DATE,RCV_CODE,AIR_LINE_NO,POSTPACK_CODE,IF_OUTTIME
      ,FULL_DLV_TIME,LAST_QUERY_TIME,QUERY_TIMES)
   select mi.mail_num,clct_sum_date,rcv_province_code,air_line_no,v_bag_code,0
     ,sysdate,sysdate,1 from fact_mail_info mi,(
   select min(mi.mail_num) mail_num,v_bag_code from fact_mail_info mi,gdct.CNT_ITF_YZ_FLOW_FF ff where mi.mail_num=ff.v_mailcode
   and (clct_org like '51%' or clct_org like '52%') and d_status_time>=trunc(sysdate-10) and d_status_time<trunc(sysdate-10)+1
   and mi.air_line_no is not null
   and mi.rcv_province_code in('US','RU','AU','GB','BR','CA','ES','JP','AR','NL','FR','IL','NO','UA','DE','SE','IT','PL','TR','FI')
   and v_child_status='PK_EXO' and (v_place like '51%' or v_place like '52%')    group by v_bag_code
   ) a where mi.mail_num=a.mail_num;

   ---1.2 插入钮门需要查询的表
  insert into gdct.tb_gjxb_yjhm_info (ID,MAIL_CODE,ACC_TIME,COUNTRY_CODE,COUNTRY_NAME,FLAG
    ,INSERT_TIME,COMPLETE_TIME,IS_PRIORITY)
   select gdct.seq_TB_GJXB_YJHM_INFO.nextval,

       sa.mail_num,
       mi.rcv_province_code,
        to_date(sa.sum_date,'YYYY-MM-dd'),
        dl.district_name,0 --0未处理
       ,sysdate,null,55 --优化级用
       from fact_mail_info mi,MAIL_FULL_DLV_SAMPLE sa,sys_gj_district dl
       where mi.mail_num=sa.mail_num and mi.rcv_province_code=dl.district_code
       and sa.FULL_DLV_TIME>=trunc(sysdate) and sa.FULL_DLV_TIME<trunc(sysdate)+1;

       open p_cursor;
       loop
         fetch p_cursor  into v_mailnum,v_lastquerytime,v_clcttime,v_fulldlvtime,v_maxdays;
           exit when p_cursor%notfound;
           --第一种情况  1）已经有妥投full_dlv_time，将full_dlv_time更新到MAIL_FULL_DLV_SAMPLE，并判断full_dlv_time-clct_time是否大于MAX_DAYS（最大可接收寄达天数），超过将IF_OUTTIME置为-1，不超过置为1
           if  v_fulldlvtime is not null  then  update MAIL_FULL_DLV_SAMPLE e set e.full_dlv_time=v_fulldlvtime where e.mail_num=v_mailnum;
           --full_dlv_time-clct_time是否大于MAX_DAYS（最大可接收寄达天数），超过将IF_OUTTIME置为-1，不超过置为1
               if v_fulldlvtime-v_clcttime>v_maxdays then  update MAIL_FULL_DLV_SAMPLE e set e.if_outtime=-1 where e.mail_num=v_mailnum;
               else   
                 update MAIL_FULL_DLV_SAMPLE e set e.if_outtime=1 where e.mail_num=v_mailnum;
               end if;
               -- 第二种情况 未有妥投 2）未有妥投full_dlv_time，判断当前时间-clct_time>MAX_DAYS+5天，则将IF_OUTTIME置为-1，否则判断sysdate-LAST_QUERY_TIME.LAST_QUERY_TIME>2，则将该邮件数据插入钮门待查询表，并更新LAST_QUERY_TIME的LAST_QUERY_TIME为当前时间，QUERY_TIMES+1。。
                else  
                  if  sysdate-v_clcttime>v_maxdays+5 then
                     update MAIL_FULL_DLV_SAMPLE e set e.if_outtime=-1 where e.mail_num=v_mailnum;
                   else   
                     if  sysdate-v_lastquerytime>2 then
                       --1 则将该邮件数据插入钮门待查询表，
                       insert into gdct.tb_gjxb_yjhm_info (ID,MAIL_CODE,ACC_TIME,COUNTRY_CODE,COUNTRY_NAME,FLAG
                                                          ,INSERT_TIME,COMPLETE_TIME,IS_PRIORITY)                                       
                                                  select  gdct.seq_TB_GJXB_YJHM_INFO.nextval,
                                                   sa.mail_num,
                                                   to_date(v_clcttime,'YYYY-MM-DD'),
                                                
                                                    mi.rcv_province_code,
                                                    dl.district_name,
                                                    0 ,
                                                    sysdate,
                                                    null,
                                                    55 --优化级用
                                                    from fact_mail_info mi,MAIL_FULL_DLV_SAMPLE sa,sys_gj_district dl
                                                   where mi.mail_num=sa.mail_num and mi.rcv_province_code=dl.district_code and sa.mail_num=v_mailnum;
                     --2 并更新LAST_QUERY_TIME的LAST_QUERY_TIME为当前时间，QUERY_TIMES+1。
                     update  MAIL_FULL_DLV_SAMPLE b set b.last_query_time=sysdate,b.query_times=b.query_times-1 where b.mail_num=v_mailnum;
                     end if;
                   end if;  
                  end if;
               end loop;  
   commit;
  prc_write_etl_stat_info ('prc_gjxb_sum_air_dlv','runDay_'||reportStatDate,'',v_etl_temp_time,sysdate,NULL,NULL,NULL,NULL,'');

    /* 异常处理 */
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Message: ' || SQLERRM||',sql:'||sqlText);
    dbms_output.put_line('Line No.: ' || dbms_utility.format_error_backtrace);
    ROLLBACK;
    RAISE;

END ;