一个存储过程

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;