简单oracle存储过程，使用游标-优快云博客

本文链接：https://blog.youkuaiyun.com/body13_13/article/details/7683662
create or replace procedure P_COUNT_MOBILELIST_RLOG 
AS
  tc_success_count number; -- 电信成功总数 
  tc_fail_count number; -- 电信失败总数 
  tc_un_count number; -- 电信未知总数
  tc_mobilelist_rlog mobilelist_rlog%rowtype; -- 电信表对象
  tc_tmp number;
  -- 定义电信游标
  cursor tc_cursor is select * from mobilelist_rlog 
  where ch_id in (select id from channel where ch_id= 0) and seq_mobilelist in (select seq from mobilelist) and COUNT_FLAG=0 and rownum < 20000;
  
  cm_success_count number; -- 移动成功总数 
  cm_fail_count number; -- 移动失败总数 
  cm_un_count number; -- 移动未知总数
  cm_mobilelist_rlog mobilelist_rlog%rowtype; -- 移动表对象
  cm_tmp number;
  -- 定义移动游标
  cursor cm_cursor is select * from mobilelist_rlog 
  where ch_id in (select id from channel where ch_id= 1) and seq_mobilelist in (select seq from mobilelist) and COUNT_FLAG=0 and rownum < 20000;
  
  un_success_count number; -- 联通成功总数 
  un_fail_count number; -- 联通失败总数 
  un_un_count number; -- 联通未知总数
  un_mobilelist_rlog mobilelist_rlog%rowtype; -- 联通表对象
  un_tmp number;
  -- 定义联通游标
  cursor un_cursor is select * from mobilelist_rlog 
  where ch_id in (select id from channel where ch_id= 2) and seq_mobilelist in (select seq from mobilelist) and COUNT_FLAG=0 and rownum < 20000;
BEGIN
  -- 开始遍历电信游标
  dbms_output.put_line('电信开始'); -- 打印
  tc_success_count:=0;
  tc_fail_count:=0;
  tc_un_count:=0;
  tc_tmp:=0;
  open tc_cursor; -- 打开游标
  loop
    fetch tc_cursor into tc_mobilelist_rlog; -- 将游标写入电信表对象
    EXIT WHEN tc_cursor%NOTFOUND; --游标取不到数据则退出
    -- 查询此日期数据是否存在,日期不存在，则做插入操作
    select count(*) into tc_tmp from mobilelist_rlog_tmp where count_date = to_date(to_char(tc_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd'); 
    if (tc_tmp = 0) then 
      insert into MOBILELIST_RLOG_TMP (seq,COUNT_DATE) VALUES ("seq_mobilelist_rlog_tmp".nextVal,to_date(to_char(tc_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd'));
      commit;
      --dbms_output.put_line('插入日期成功');
    end if;
    --exception when others then
      --dbms_output.put_line('some errors');
    --rollback; 
    if (tc_mobilelist_rlog.rpt_str = 'DELIVRD' or tc_mobilelist_rlog.rpt_str = '发送成功。') then -- 发送成功
      tc_success_count:=tc_success_count+1;
      -- 日期存在，则做更新操作
      update MOBILELIST_RLOG_TMP set TC_SUCCSEE = TC_SUCCSEE + 1 where COUNT_DATE = to_date(to_char(tc_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd'); 
      -- 将原表中的这条记录改为已统计
      update mobilelist_rlog set COUNT_FLAG = 1 where seq = tc_mobilelist_rlog.seq;
    elsif (tc_mobilelist_rlog.rpt_str != 'DELIVRD' or tc_mobilelist_rlog.rpt_str != '发送成功。' and tc_mobilelist_rlog.rpt_str is not null) then -- 发送失败
      tc_fail_count:=tc_fail_count+1;
      update MOBILELIST_RLOG_TMP set TC_FAIL = TC_FAIL + 1 where COUNT_DATE = to_date(to_char(tc_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd');
      -- 将原表中的这条记录改为已统计
      update mobilelist_rlog set COUNT_FLAG = 1 where seq = tc_mobilelist_rlog.seq;
    elsif (tc_mobilelist_rlog.rpt_str is null) then -- 未知状态
      tc_un_count:=tc_un_count+1;
      update MOBILELIST_RLOG_TMP set TC_UN = TC_UN + 1 where COUNT_DATE = to_date(to_char(tc_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd');
      -- 将原表中的这条记录改为已统计
      update mobilelist_rlog set COUNT_FLAG = 1 where seq = tc_mobilelist_rlog.seq;
    end if;
  commit; 
  end loop;
  close tc_cursor;
  dbms_output.put_line('成功'||tc_success_count); -- 打印
  dbms_output.put_line('失败'||tc_fail_count); -- 打印
  dbms_output.put_line('未知'||tc_un_count); -- 打印
  dbms_output.put_line('电信结束'); -- 打印
  -- 结束电信游标
  
  -- 开始移动游标
  dbms_output.put_line('移动开始'); -- 打印
  cm_success_count:=0;
  cm_fail_count:=0;
  cm_un_count:=0;
  cm_tmp:=0;
  open cm_cursor; -- 打开游标
  loop
    fetch cm_cursor into cm_mobilelist_rlog; -- 将游标写入移动表对象
    EXIT WHEN cm_cursor%NOTFOUND; --游标取不到数据则退出
    select count(*) into cm_tmp from mobilelist_rlog_tmp where count_date = to_date(to_char(cm_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd'); -- 查询此日期数据是否存在
    if (cm_tmp = 0) then 
      -- 日期不存在，做新增操作
      insert into MOBILELIST_RLOG_TMP (seq,COUNT_DATE) VALUES ("seq_mobilelist_rlog_tmp".nextVal,to_date(to_char(cm_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd'));
      commit; 
    end if;  
    if (cm_mobilelist_rlog.rpt_str = 'DELIVRD' or cm_mobilelist_rlog.rpt_str = '发送成功。') then -- 发送成功
      cm_success_count:=cm_success_count+1;
      update MOBILELIST_RLOG_TMP set cm_succsee = cm_succsee + 1 where COUNT_DATE = to_date(to_char(cm_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd');
      -- 将原表中的这条记录改为已统计
      update mobilelist_rlog set COUNT_FLAG = 1 where seq = cm_mobilelist_rlog.seq;
    elsif (cm_mobilelist_rlog.rpt_str != 'DELIVRD' or cm_mobilelist_rlog.rpt_str != '发送成功。' and cm_mobilelist_rlog.rpt_str is not null) then -- 发送失败
      cm_fail_count:=cm_fail_count+1;
      update MOBILELIST_RLOG_TMP set cm_fail = cm_fail + 1 where COUNT_DATE = to_date(to_char(cm_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd');
      -- 将原表中的这条记录改为已统计
      update mobilelist_rlog set COUNT_FLAG = 1 where seq = cm_mobilelist_rlog.seq;
    elsif (cm_mobilelist_rlog.rpt_str is null) then -- 未知状态
      cm_un_count:=cm_un_count+1;
      update MOBILELIST_RLOG_TMP set cm_un = cm_un + 1 where COUNT_DATE = to_date(to_char(cm_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd');
      -- 将原表中的这条记录改为已统计
      update mobilelist_rlog set COUNT_FLAG = 1 where seq = cm_mobilelist_rlog.seq;
    end if;
  commit; 
  end loop;
  close cm_cursor;
  dbms_output.put_line('成功'||cm_success_count); -- 打印
  dbms_output.put_line('失败'||cm_fail_count); -- 打印
  dbms_output.put_line('未知'||cm_un_count); -- 打印
  dbms_output.put_line('移送结束'); -- 打印
  -- 结束移动游标
  
  -- 开始联通游标
  dbms_output.put_line('联通开始'); -- 打印
  un_success_count:=0;
  un_fail_count:=0;
  un_un_count:=0;
  un_tmp:=0;
  open un_cursor; -- 打开游标
  loop
    fetch un_cursor into un_mobilelist_rlog; -- 将游标写入联通表对象
    EXIT WHEN un_cursor%NOTFOUND; --游标取不到数据则退出
    select count(*) into un_tmp from mobilelist_rlog_tmp where count_date = to_date(to_char(un_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd'); -- 查询此日期数据是否存在
    if (un_tmp = 0) then 
      -- 日期不存在，做新增操作
      insert into MOBILELIST_RLOG_TMP (seq,COUNT_DATE) VALUES ("seq_mobilelist_rlog_tmp".nextVal,to_date(to_char(un_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd'));
      commit; 
    end if;  
    if (un_mobilelist_rlog.rpt_str = 'DELIVRD' or un_mobilelist_rlog.rpt_str = '发送成功。') then -- 发送成功
      un_success_count:=un_success_count+1;
      update MOBILELIST_RLOG_TMP set un_succsee = un_succsee + 1 where COUNT_DATE = to_date(to_char(un_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd');
      -- 将原表中的这条记录改为已统计
      update mobilelist_rlog set COUNT_FLAG = 1 where seq = un_mobilelist_rlog.seq;
    elsif (un_mobilelist_rlog.rpt_str != 'DELIVRD' or un_mobilelist_rlog.rpt_str != '发送成功。' and un_mobilelist_rlog.rpt_str is not null) then -- 发送失败
      un_fail_count:=un_fail_count+1;
      update MOBILELIST_RLOG_TMP set un_fail = un_fail + 1 where COUNT_DATE = to_date(to_char(un_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd');
      -- 将原表中的这条记录改为已统计
      update mobilelist_rlog set COUNT_FLAG = 1 where seq = un_mobilelist_rlog.seq;
    elsif (un_mobilelist_rlog.rpt_str is null) then -- 未知状态
      un_un_count:=un_un_count+1;
      update MOBILELIST_RLOG_TMP set un_un = un_un + 1 where COUNT_DATE = to_date(to_char(un_mobilelist_rlog.sendtime,'yyyy-MM-dd'),'yyyy-MM-dd');
      -- 将原表中的这条记录改为已统计
      update mobilelist_rlog set COUNT_FLAG = 1 where seq = un_mobilelist_rlog.seq;
    end if;
  commit;
  end loop;
  close un_cursor;
  dbms_output.put_line('成功'||un_success_count); -- 打印
  dbms_output.put_line('失败'||un_fail_count); -- 打印
  dbms_output.put_line('未知'||un_un_count); -- 打印
  dbms_output.put_line('联通结束'); -- 打印
  -- 结束联通游标
  
  -- 异常处理
  exception when others then
  rollback;
  commit;
  pdmt_errlog(sqlcode,'P_COUNT_MOBILELIST_RLOG',sqlerrm);
  commit;
END P_COUNT_MOBILELIST_RLOG;