oracle游标使用

在进行PL/sql编程时，我们都会使用游标，游标有两种，一种是显式游标，使用类似如下方式：

open 游标
loop
fetch into …;
exit when notfound;
end loop;
close 游标；

另一种是隐式游标，使用类似如下：

for 游标变量 in 游标
loop
赋值变量:=游标变量.列;
end loop;

这两种游标究竟何种性能更高，消耗资源更小呢？

我们先来做一个测试，下面分别为两种类型游标的测试代码：
显式游标代码：

DECLARE
  V_BEGIN        NUMBER(10);
  V_END          NUMBER(10);

  V_CURRTIME     NUMBER(12,2);
  V_USETIME      NUMBER(12,2);

  V_OWNER        DBA_OBJECTS.OWNER%TYPE;
  V_OBJECT_NAME  DBA_OBJECTS.OBJECT_NAME%TYPE;
  V_OBJECT_TYPE  DBA_OBJECTS.OBJECT_TYPE%TYPE;

  CURSOR GET_OBJ IS
    SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS;
BEGIN
  SELECT VALUE INTO V_BEGIN FROM V$SESS_TIME_MODEL WHERE SID=SYS_CONTEXT('USERENV','SID') AND STAT_NAME='DB CPU';
  V_CURRTIME:=DBMS_UTILITY.GET_TIME;

  OPEN GET_OBJ;
  LOOP
    FETCH GET_OBJ INTO V_OWNER,V_OBJECT_NAME,V_OBJECT_TYPE;
    EXIT WHEN GET_OBJ%NOTFOUND;
  END LOOP;
  V_USETIME:=(DBMS_UTILITY.GET_TIME-V_CURRTIME)/100;

  DBMS_LOCK.SLEEP(15);
  SELECT VALUE INTO V_END FROM V$SESS_TIME_MODEL WHERE SID=SYS_CONTEXT('USERENV','SID') AND STAT_NAME='DB CPU';

  DBMS_OUTPUT.PUT_LINE('耗时:'||TO_CHAR(V_USETIME,'0.00')||'秒，CPU消耗次数:'||TO_CHAR(V_END-V_BEGIN));
  CLOSE GET_OBJ;
EXCEPTION
  WHEN OTHERS THEN
    CLOSE GET_OBJ;
  RAISE;
END;

隐式游标代码：

DECLARE
  V_BEGIN        NUMBER(10);
  V_END          NUMBER(10);

  V_CURRTIME     NUMBER(12,2);
  V_USETIME      NUMBER(12,2);

  V_OWNER        DBA_OBJECTS.OWNER%TYPE;
  V_OBJECT_NAME  DBA_OBJECTS.OBJECT_NAME%TYPE;
  V_OBJECT_TYPE  DBA_OBJECTS.OBJECT_TYPE%TYPE;

  CURSOR GET_OBJ IS
    SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS;
BEGIN
  SELECT VALUE INTO V_BEGIN FROM V$SESS_TIME_MODEL WHERE SID=SYS_CONTEXT('USERENV','SID') AND STAT_NAME='DB CPU';
  V_CURRTIME:=DBMS_UTILITY.GET_TIME;

  FOR GET_OBJ_CUR IN GET_OBJ
    LOOP
      V_OWNER:=GET_OBJ_CUR.OWNER;
      V_OBJECT_NAME:=GET_OBJ_CUR.OBJECT_NAME;
      V_OBJECT_TYPE:=GET_OBJ_CUR.OBJECT_TYPE;
    END LOOP;
  V_USETIME:=(DBMS_UTILITY.GET_TIME-V_CURRTIME)/100;

  DBMS_LOCK.SLEEP(15);
  SELECT VALUE INTO V_END FROM V$SESS_TIME_MODEL WHERE SID=SYS_CONTEXT('USERENV','SID') AND STAT_NAME='DB CPU';
  DBMS_OUTPUT.PUT_LINE('耗时:'||TO_CHAR(V_USETIME,'0.00')||'秒，CPU消耗次数:'||TO_CHAR(V_END-V_BEGIN));
END;

代码说明：
两段代码的功能完全一样，都只是对dba_objects视图作了一次循环，通过dbms_utility.get_time差获取运行时间(单位：百分之一秒），通过$sess_time_model视图获取线程消耗的CPU次数，由于$sess_time_model每15秒刷新一次，因此，为保证执行完成后获取到的cpu消耗次数准确，通过dbms_lock.sleep方法，等待15秒。

运行结果：
显式游标方式：耗时: 2.68秒，CPU消耗次数:2687500

隐工游标方式：耗时: 0.62秒，CPU消耗次数:625000
即显式游标无论是运行性能还是对CPU资源的消耗，都明显高于隐式游标，隐式游标的性能比显式游标高4倍以上。

分析：
变量的绑入与绑出需要消耗大量的CPU资源，显式游标每fetch一次就执行一次输出变量绑定，而在执行隐式游标时，oracle作了一个类似于fetch bulk 的输出优化，所以性能及CPU消耗可以大幅提高。
因此，建议大家，在需要用到游标的情况下，永远不要使用逐行fetch的输出方式，尽可能使用cursor for loop的隐式游标方式。