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 ;