游标使用实例过程

平时写数据库过程时，很多时候需要用到游标，下面给出一个游标使用实例，游标的使用很简单。

首先定义游标：

  Type t_cursor Is Ref Cursor; --声明游标
  v_cursor t_cursor; --定义游标

定义好后，在过程里就可以对定义的游标进行使用了：

open v_cursor for
    select v.next_dept_code
      from view_next_busi v
     where v.dept_code = busi_code;

  LOOP
    Fetch v_cursor
      Into v_busi_code;
    Exit When v_cursor%Notfound;
    BEGIN
     。。。。。。
    END;
  END LOOP;
  close v_cursor;

CREATE OR REPLACE PROCEDURE YK_P_VISIT_BILL(in_stat_mon date, --统计月份

                                            busi_code   varchar2 --统计的营业区域
                                            ) AS

  /*
  *上门服务统计
  */
  v_result    varchar2(5);
  v_stat_mon  number(6);
  v_busi_code varchar2(8);

  Type t_cursor Is Ref Cursor; --声明游标
  v_cursor t_cursor; --定义游标

BEGIN
  v_stat_mon := replace(to_char(in_stat_mon, 'YYYY-MM'), '-', '');

  v_result := -10;
  --首先删除本月的统计数据
  delete from YK_VISIT_BILL t where t.stat_mon = in_stat_mon;

  v_result := -20;
  --删除所有数据
  delete from YK_VISIT_BILL_RE;

  v_result := -30;
  --按区域和月份统计
  insert into YK_VISIT_BILL_RE
    (BUSINESS_PLACE_CODE,
     USER_CLASS,
     INVOICE_GET_MODE1,
     INVOICE_GET_MODE2,
     INVOICE_GET_MODE3,
     INVOICE_GET_MODE4,
     INVOICE_GET_MODE5,
     INVOICE_GET_MODE6,
     NUM)
    select A1.business_place_code 营业区域,
           A1.user_class          客户类型,
           invoice_get_mode1      邮寄平信,
           invoice_get_mode2      营业网点自取,
           invoice_get_mode3      银行自取,
           invoice_get_mode4      邮寄VIP挂号,
           invoice_get_mode5      供电公司上门派送,
           invoice_get_mode6      发票派送方式未维护户数,
           num                    客户数
      from (select t2.business_place_code,
                   t2.user_class,
                   nvl(max(decode(t2.invoice_get_mode, '1', t2.cn)), 0) invoice_get_mode1,
                   nvl(max(decode(t2.invoice_get_mode, '2', t2.cn)), 0) invoice_get_mode2,
                   nvl(max(decode(t2.invoice_get_mode, '3', t2.cn)), 0) invoice_get_mode3,
                   nvl(max(decode(t2.invoice_get_mode, '4', t2.cn)), 0) invoice_get_mode4,
                   nvl(max(decode(t2.invoice_get_mode, '5', t2.cn)), 0) invoice_get_mode5,
                   nvl(max(decode(t2.invoice_get_mode, '', t2.cn)), 0) invoice_get_mode6
            
              from (select t.business_place_code,
                           t.user_class,
                           t.invoice_get_mode,
                           count(1) cn
                      from (select (case
                                     when c.user_type1 < 20 and c.ms_mode < 3 then
                                      3 --高压