游标使用总结

一.游标概念

游标是sql的一个内存工作区，由系统或用户以变量的形式定义。其作用就是临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在硬盘的表中调到计算机内存中进行处理，最终将处理结果显示出来或写回到数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标分为两种类型:显式游标和隐式游标。通常用到的select..into语句，一次只能从数据库中提取一行数据，对于这种形式的查询或者dml操作，系统都会使用一个隐式游标，但是如果要提取多行数据，就需要手工定义一个显示游标，并通过与游标相关的语句进行处理。显式游标对应一个返回结果为多行多列的select语句。

游标一旦打开，数据就从数据库中传送到游标变量中，然而应用程序从游标变量中分解出需要的数据，并进行处理。

二.隐式游标

dml操作和单行select语句会使用隐式游标，分别是:

a.      insert

b.      delete

c.      update

d.      select .. into ..

当系统使用一个隐式游标的时候，可以用过隐式游标的属性来访问操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字sql来访问，但是需要注意的是，通过sql游标名总是只能访问到前一个dml操作或单行select操作的游标属性。所以通常在执行完操作之后，立即使用sql游标名来访问属性。游标属性有以下四种：

a.      sql%isopen（布尔型）

b.      sql%isfound（布尔型）

c.      sal%notfound（布尔型）

d.      sal%rowcount（数值型）

当执行一条DML语句后，DML语句的结果保存在四个游标属性中，用于控制程序流程或者程序的状态。当运行dml语句时，plsql打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行dml语句时打开，完成后关闭。

需要注意的点：

a.      对于隐式游标，sql%isopen总是关闭的，因为隐式游标在dml语句执行时打开，结束就立刻关闭。

b.      隐式游标在select..into中使用有三种可能

B1：结果集只有一行,且select是成功的

B2：没有查询到任何结果集，NO_DATA_FOUND异常

B3：结果集中含有两行或两行以上数据,TOO_MANY_ROWS异常

使用实例：

BEGIN

  UPDATE EMP1 A SET A.ENAME = 'test'WHERE A.EMPNO = '7369';

  IF SQL%ISOPENTHEN

    DBMS_OUTPUT.PUT_LINE('opening');

  ELSE

    DBMS_OUTPUT.PUT_LINE('closing');

  END IF;

  IF SQL%FOUNDTHEN

    DBMS_OUTPUT.PUT_LINE('游标指向了有效行');

  ELSE

    DBMS_OUTPUT.PUT_LINE('sorry');

  END IF;

  DBMS_OUTPUT.PUT_LINE('更改行数:'|| SQL%ROWCOUNT);

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');

  WHEN TOO_MANY_ROWS THEN

    DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');

  WHEN OTHERSTHEN

    DBMS_OUTPUT.PUT_LINE('err');

END;

三.显示游标

显示游标的使用分为四个部分

a.      声明

Cursor [游标名](参数..) is select..;

参数为可选部分，所定义的参数可以出现在select之后的where字句中，如果定义了参数，则在打开游标时，必须传递相应的参数值。

b.      打开游标

Open [游标名](参数值…)

打开游标时，select语句的查询结果就被传递到了游标工作区

c.      提取数据

Fetch [游标名] into [变量名]

游标打开后有一个指针指向数据区,fetch语句一次返回指针所指的一行数据，要返回多行需要重复执行，可以通过循环语句实现。控制循环可以通过判断游标的属性实现。

d.      关闭游标

Close [游标名]

显示游标打开后必须显示的关闭。游标一旦关闭，所占用的资源也就释放，游标变为无效。

--FETCH游标的使用实例（loop）

DECLARE

  CURSOR C_CUR IS

    SELECT * FROM EMP;

  C_CUR_ROW C_CUR%ROWTYPE;

BEGIN

  OPEN C_CUR;

  LOOP

    FETCH C_CUR

      INTO C_CUR_ROW;

    EXIT WHEN C_CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||

                         C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);

  END LOOP;

  CLOSE C_CUR;

END

--使用游标和while循环取游标

DECLARE

  CURSOR C_CUR IS

    SELECT * FROM EMP;

  C_CUR_ROW C_CUR%ROWTYPE;

BEGIN

  OPEN C_CUR;

  FETCH C_CUR

    INTO C_CUR_ROW;

  WHILE C_CUR%FOUNDLOOP

    DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||

                         C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);

    FETCH C_CUR

      INTO C_CUR_ROW;

  END LOOP;

END;

--使用for循环简化游标的使用，此时不再需要open,fetch,close的游标操作，也不需要notfoun属性来检测是否到最后一条记录，这一切由oracle隐式的完成

DECLARE

  CURSOR C_CUR IS

    SELECT * FROM EMP;

  C_CUR_ROW C_CUR%ROWTYPE;

BEGIN

  FOR C_CUR_ROW IN C_CUR LOOP

    DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||

                         C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);

  END LOOP;

END;

;

--参数游标的使用

DECLARE

  CURSOR C_CUR(P_JOBVARCHAR2) IS

    SELECT * FROM EMP WHERE JOB= P_JOB;

  C_CUR_ROW C_CUR%ROWTYPE;

BEGIN

  FOR C_CUR_ROW IN C_CUR('SALESMAN')LOOP

    DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||

                         C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);

  END LOOP;

END;

--使用游标更新数据

DECLARE

  CURSOR C_UPDATE IS

    SELECT * FROM EMP1 FORUPDATE OF SAL;

  C_UPDATE_INFO C_UPDATE%ROWTYPE;

  SALEINFO     EMP.SAL%TYPE;

BEGIN

  FOR C_UPDATE_INFO IN C_UPDATE LOOP

    IF C_UPDATE_INFO.SAL < 1500 THEN

      SALEINFO := C_UPDATE_INFO.SAL* 1.2;

    ELSIF C_UPDATE_INFO.SAL < 2000 THEN

      SALEINFO := C_UPDATE_INFO.SAL* 1.5;

    END IF;

    UPDATE EMP1 SET SAL = SALEINFO WHERE EMPNO = C_UPDATE_INFO.EMPNO;

  END LOOP;

END;

--删选两个资格最老的人员信息定义计数器控制

DECLARE

  CURSOR C_CUR IS

    SELECT * FROM EMP ORDERBY HIREDATE;

  C_CUR_ROW C_CUR%ROWTYPE;

  TOP_N    NUMBER := 2;

BEGIN

  OPEN C_CUR;

  FETCH C_CUR

    INTO C_CUR_ROW;

  WHILE TOP_N > 0 LOOP

    DBMS_OUTPUT.PUT_LINE(C_CUR_ROW.EMPNO|| '-' || C_CUR_ROW.ENAME || '-' ||

                         C_CUR_ROW.JOB ||'-' || C_CUR_ROW.SAL);

    TOP_N := TOP_N - 1;

    FETCH C_CUR

      INTO C_CUR_ROW;

  END LOOP;

  CLOSE C_CUR;

END;