Oracle数据库游标的高效实用用法【企业SQL开发实战】

在Oracle数据库中，使用游标进行数据库开发，效率非常高！在oracle概念中，游标是SQL的一个内存工作区，由系统或者用户以变量的形式定义。它的作用就是用于临时存储从数据库中提取的数据块。由于其存在在内存共享区域中，所以数据库可以多次调用，不必进行IO请求，所以大大提高了数据库的查询与存取效率！下面分享介绍oracle游标比较实用的两种用法：一种是利用游标直接在函数里返回结果集，另外一种是通过游标更新法，快速关联多表进行匹配更新。

1，返回查询结果的游标
–定义游标类型

CREATE OR REPLACE PACKAGE PKG_TYPES AS
  TYPE REFCURSOR IS REF CURSOR;
END;

–创建函数返回游标

CREATE OR REPLACE Function USP_XS_GetWinLotteryDate Return PKG_TYPES.REFCURSOR Is
  refcus PKG_TYPES.REFCURSOR;
  v_sql Varchar(2000);
Begin
  begin
    v_sql := 'select l.ordercode,l.cuscode,''LQ1210001'' as PayTypeCode,l.email,l.mobile,l.cusname ,l.rate
                    from mbs7_oms.WZ_LOTTERYWINNINGS l
                    join mbs7_oms.xs_orderstatus o
                    on l.ordercode = o.ordercode
                    where l.type = 0
                    and l.isactivate = 0
                    and o.iscancel = 0
                    and o.outdate <= sysdate-29
                    and o.outdate>to_date(''2012-01-01'',''yyyy-mm-dd'')
                    and rownum <=300';
    Open refcus For v_sql;
    Return refcus;
  exception
    when others then
      raise;
  end;
End USP_XS_GetWinLotteryDate;

带变量的游标返回：

CREATE OR REPLACE FUNCTION "USP_XS_TAURUSWUCJ" (billcode in varchar2)
    Return Sys_Refcursor As REFCUR Sys_Refcursor;
    v_sql varchar2(4000);
Begin
  begin
v_sql := 'select billcode, wm_concat(to_char(c.suppliername)) colorname
               from PO_ApproveSupplier d
               join cg_supplier c
                 on d.suppliercode = c.suppliercode
                 where d.billcode = :1
              group by billcode';
  Open REFCUR For v_sql using billcode;
  Return REFCUR;
  Close REFCUR;
  exception
    when others then
      raise;
End;
End USP_XS_TAURUSWUCJ;

二，多表关联游标更新法
将普通update：执行20分钟

update re_servicesubrtn r
   set r.rootsubid =
       (select b.rootsubid
          from xs_orderbusiness b
         where r.servicecode = b.billcode
           and r.servicesubrtnid = b.billsubid
           and rownum < 2);

将以上SQL需求，改写为多表关联的游标更新法，如下：执行4分钟

begin
  for cr in (select r.rowid, b.rootsubid
               from mbs7_oms.xs_orderbusiness b, mbs7_oms.re_servicesubrtn r
              where r.servicecode = b.billcode
                and r.servicesubrtnid = b.billsubid) loop
    update mbs7_oms.re_servicesubrtn
       set rootsubid = cr.rootsubid
     where rowid = cr.rowid;
  end loop;
end;

可以定义为PLSQL块，对游标更新结果输出报告：

DECLARE V_COUNT NUMBER := 0 ;
        COUNT_LOOP NUMBER := 1;
BEGIN
  FOR CR IN (SELECT CUSCODE , CASE WHEN SEX = '女' THEN 1 WHEN SEX = '男' THEN 0 ELSE NULL END SEX FROM dba_wqw.kh_customer_sex)
  LOOP
      UPDATE MBS7_CRM.KH_CUSTOMER SET sex = CR.SEX WHERE cuscode = CR.CUSCODE ;
      V_COUNT := V_COUNT + SQL%ROWCOUNT ;
      IF V_COUNT >= 10000 THEN
         DBMS_OUTPUT.PUT_LINE('第' || LPAD(COUNT_LOOP , 8 , '0') ||'次更新，已经更新行数'|| ':' ||V_COUNT ||';') ;
         V_COUNT := 0 ;
         COUNT_LOOP := COUNT_LOOP + 1 ;
         COMMIT ;
      END IF ;
  END LOOP ;
  DBMS_OUTPUT.PUT_LINE('最后一次更新，已经更新行数：'|| V_COUNT ||';') ;
  COMMIT ;
END;