create or replace package abc_zys is
procedure daily_census;
end abc_zys;
--建一个包,包中有存储过程daily_census。不涉及任何参数。
create or replace package body abc_zys is
procedure daily_census
as
type t_cursor is ref cursor;
R t_cursor; ——建立动态游标
v_tableName varchar2(30) := 'abc_check_';
v_tableName2 varchar2(30) := 'abcre_check_';
isp_id number;
sum_a number;
sum_b number;
send_cmpp number;
send_cmpp_success number;
send_sgip number;
send_sgip_success number;
send_smgp number;
send_smgp_success number;
send_cmpp_95599 number;
send_cmpp_su95 number;
send_sgip_95599 number;
send_sgip_su95 number;
send_smgp_95599 number;
send_smgp_su95 number;
v_sql varchar2(4000);
v_content varchar2(4000);
v_sql2 varchar2(4000);
BEGIN
v_tableName := v_tableName || to_char(sysdate - 1,'mmdd');
v_tableName2 := v_tableName2 || to_char(sysdate - 1,'mmdd');
v_SQL := 'select isp_id,sum(a),sum(b) from (
select isp_id,sum(msg_count) a,sum(case when send_status=''01'' then msg_count else 0 end) b from '||v_tableName||' t group by isp_id union all
select isp_id,sum(msg_count),sum(case when send_status=''01'' then msg_count else 0 end) from abcre_log.'||v_tableName2||' t group by isp_id)
group by isp_id';
open R for v_SQL; ——用游标遍历上述SQL语句
loop
fetch R
into isp_id, sum_a, sum_b;
exit when R%notfound;
case
when isp_id=0 then
send_cmpp:=sum_a;
send_cmpp_success:=sum_b;
when isp_id=1 then
send_sgip:=sum_a;
send_sgip_success:=sum_b;
when isp_id=3 then
send_smgp:=sum_a;
send_smgp_success:=sum_b;
when isp_id=7 then
send_smgp_95599:=sum_a;
send_smgp_su95:=sum_b;
when isp_id=8 then
send_sgip_95599:=sum_a;
send_sgip_su95:=sum_b;
end case;
end loop;
close R;
v_sql2:= 'select sum(msg_count) ,sum(case when send_status=''01'' then msg_count else 0 end) from abcmas_log.'||v_tableName2||' t' ;
execute immediate v_sql2 into send_cmpp_95599,send_cmpp_su95;
v_content:='您好,'||to_char(sysdate - 1,'mmdd')||'日 1069移动 总数 '||send_cmpp||'; 成功数 '||send_cmpp_success
||';1069电信 总数: '||send_smgp||'; 成功数:'|| send_smgp_success||'; 1069联通 总数: '||send_sgip||';成功数: '
||send_sgip_success||'; 95599联通 总数: '||send_sgip_95599||';成功数: '||send_sgip_su95||';95599电信 总数: '||
send_smgp_95599||';成功数: '||send_sgip_su95||'.'||'95599移动(mas)总数: '||send_cmpp_95599||';成功数: '||send_cmpp_su95||'.';
abc_job.writeWaitDataTO216('13621193249',v_content,0);
end daily_census;
end abc_zys;