存储过程

最新推荐文章于 2024-10-23 17:19:43 发布

原创最新推荐文章于 2024-10-23 17:19:43 发布 · 118 阅读

0 ·

CC 4.0 BY-SA版权

存储过程专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了如何通过创建存储过程PRC_SUM_MAIL_EXCHANGE_HAN来计算互换局邮件及时处理率，包括数据清洗、统计关键指标如待处理数、实际接收数等，并提供了异常处理机制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace procedure PRC_SUM_MAIL_EXCHANGE_HAN(p_stat_date in date) is
/** 互换局邮件及时处理率

exec PRC_SUM_MAIL_EXCHANGE_HAN(to_date('20140501','yyyyMMdd'));

**/

stat_date varchar(10);
begin
stat_date := to_char(p_stat_date, 'yyyymmdd');

-- 删除当天统计过的数据
delete from SUM_MAIL_EXCHANGE_HANDLE_STAT where SUM_DATE = stat_date;

insert into SUM_MAIL_EXCHANGE_HANDLE_STAT
(sum_date,
dept_code,
need_rcv_cnt,
rcv_cnt,
open_cnt,
seal_cnt,
handle_cnt,
cnt_12,
cnt_24,
cnt_36,
cnt_48,
cnt_over_48
)
Select mi.clct_sum_date
,exchange_org
,count(*) --待处理数
,sum(decode(EXCHANGE_OPEN_TIME,null,0,1)) --实际接收数
,sum(decode(EXCHANGE_OPEN_TIME,null,0,1)) --开拆数
,sum(decode(EXCHANGE_SEAL_TIME,null,0,1)) --封发数
,sum(decode(EXCHANGE_SEAL_TIME, null,0,1)) --已处理数

,sum(decode(EXCHANGE_SEAL_TIME, null,0,case when EXCHANGE_SEAL_TIME-EXCHANGE_OPEN_TIME<=0.5 then 1 else 0 end))--12小时处理数
,sum(decode(EXCHANGE_SEAL_TIME, null,0,case when EXCHANGE_SEAL_TIME-EXCHANGE_OPEN_TIME<=1 then 1 else 0 end))--24小时处理数
,sum(decode(EXCHANGE_SEAL_TIME, null,0,case when EXCHANGE_SEAL_TIME-EXCHANGE_OPEN_TIME<=1.5 then 1 else 0 end))--36小时处理数
,sum(decode(EXCHANGE_SEAL_TIME, null,0,case when EXCHANGE_SEAL_TIME-EXCHANGE_OPEN_TIME<=2 then 1 else 0 end))--48小时处理数
,sum(decode(EXCHANGE_SEAL_TIME, null,0,case when EXCHANGE_SEAL_TIME-EXCHANGE_OPEN_TIME>2 then 1 else 0 end))--48小时以上处理数
from FACT_MAIL_INFO mi
where mi.clct_sum_date=stat_date and exchange_org is not null
group by mi.exchange_org,mi.clct_sum_date ;

commit;

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