一、游标的相关概念及特性

    1.定义
        映射在结果集中某一行数据的具体位置，类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求对该行进行相应特定的操作。

    2.游标的分类
        在Oracel中，游标可以分为两大类：静态游标 和 REF游标（动态游标）。REF游标是一种引用类型，类似于指针。而静态游标又分为显式游标和隐式游标两种。
        结构图如下： 

                                                     |--隐式游标：系统自动定义的游标，用于处理select into 和DML语句。
                           |---静态游标---|
Oracle游标-----|                         |--显式游标：即用户自定义游标，专门用于处理select语句返回的多行数据。
                           |---REF游标(本文不作详细介绍)

    3.游标使用的一般过程：
        显式游标：声明--->打开--->读取--->关闭。
        隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的。

    4.游标的4个共同属性：%FOUND，%NOTFOUND，%ISOPEN，%ROWCOUNT。
       %FOUND 布尔型属性，如果SQL语句至少影响到一行数据，则该属性为TRUE，否则为FALSE。
       %NOTFOUND 布尔型属性，与%FOUND相反。
       %ISOPEN 布尔型属性，判断游标的状态，当游标已经打开时返回TRUE，游标关闭时则返回FALSE。
       %ROWCOUNT 数字型属性，返回受SQL影响的行数。
        注：当使用隐式游标的属性时，需要在属性前加上SQL。因为Oracle在创建隐式游标时，默认的游标名为SQL。比如：..... if SQL%NOTFOUND then....

二、隐式游标

    1.隐式游标定义
       隐式游标由系统自动定义，其过程由oracle控制，完全自动化。比如当DML被使用时，Oracle为每一个不属于显式游标的DML语句都创建一个隐式游标，其声明、打开、关闭都是系统自动进行。另外，隐式游标默认的名称是SQL，不能对SQL游标显式地执行OPEN,FETCH,CLOSE语句。

    2.隐式游标的属性
        类似于显示游标，隐式游标同样具有四种属性，只不过隐式游标以SQL%开头，而显示游标以Cursor_name%开头。
        并且，通过SQL%总是只能访问前一个DML操作或单行SELECT操作的游标属性，用于判断DML执行的状态和结果，进而控制程序的流程。
        SQL%ISOPEN 
                游标是否打开。当执行select into、insert、update、delete这些DML操作时，Oracle会隐含地打开游标，且在该语句执行完毕或隐含地关闭游标。
                因为是隐式游标，故SQL%ISOPEN总是FALSE。
SQL%FOUND     
                判断SQL语句是否成功执行。当有作用行时则成功执行为TRUE，否则为FALSE。
        SQL%NOTFOUND  
                判断SQL语句是否成功执行。当有作用行时否其值为FALSE，否则其值为TRUE。
        SQL%ROWCOUNT 
                在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL。
                对于SELECTINTO语句，如果执行成功，SQL%ROWCOUNT的值为1；
                如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND。
    3.隐式游标使用示例
      (1) 使用隐式游标SQL%NOTFOUND属性


SET serveroutput ON  
    BEGIN  
      UPDATE emp SET sal=1200 WHERE empno='7369';  
      IF SQL%NOTFOUND THEN  
        dbms_output.put_line('未更新任何记录');  
      ELSE  
        dbms_output.put_line('更新'||SQL%ROWCOUNT||'条记录');  
      END IF  
    END;





      (2) 隐式游标的综合应用（根据SQL游标的不同属性返回不同的结果）


DECLARE  
      v_dept emp.deptno%TYPE := &no;  
    BEGIN  
              
      IF SQL%ROWCOUNT >= 0 THEN  --判断更新前SQL%ROWCOUNT的属性  
        DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT value is ' || SQL%ROWCOUNT ||'before updated');  
      ELSE  
        DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT value is NULL before updated');  
      END IF;  
              
      UPDATE emp SET sal = sal + 200 WHERE deptno = v_dept;  
              
      IF SQL%FOUND THEN    --判断SQL%FOUND的属性  
        DBMS_OUTPUT.PUT_LINE('SQL code is executed successful');  
        DBMS_OUTPUT.PUT_LINE('SQL%Found is TRUE');  
      ELSE  
        DBMS_OUTPUT.PUT_LINE('No such department');  
        DBMS_OUTPUT.PUT_LINE('SQL%Found is FALSE');  
      END IF;  
      IF SQL%NOTFOUND THEN    --判断SQL%NOTFOUND的属性  
        DBMS_OUTPUT.PUT_LINE('SQL%NotFound is TRUE');  
      ELSE  
        DBMS_OUTPUT.PUT_LINE('SQL%NotFound is FALSE');  
      END IF;  
      
      IF SQL%ISOPEN THEN    --判断SQL%ISOPEN的属性  
        DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN is TRUE');  
      ELSE  
        DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN is FALSE');  
      END IF;  
      DBMS_OUTPUT.PUT_LINE('The rows updated is :' || SQL%ROWCOUNT ||' rows by SQL Cursor'); --判断SQL%ROWCOUNT的属性  
    END;




       操作一及结果：


Enter value for no: 10   --下面是成功更新后的结果  
    SQL%ROWCOUNT value is NULL before updated  
    SQL code is executed successful  
    SQL%Found is TRUE  
    SQL%NotFound is FALSE  
    SQL%ISOPEN is FALSE  
    The rows updated is :3 rows by SQL Cursor




       操作二及结果：


Enter value for no: 80   --下面是未成功更新后的结果  
    SQL%ROWCOUNT value is NULL before updated  
    No such department  
    SQL%Found is FALSE  
    SQL%NotFound is TRUE  
    SQL%ISOPEN is FALSE  
    The rows updated is :0 rows by SQL Cursor





       (3)SELECT INTO时使用隐式游标

SELECT INTO用于将单行结果集放置到变量之中。SELECT INTO处理的结果包括三种情况

            查询结果返回单行，SELECT INTO被成功执行；

            查询结果没有返回行，PL/SQL将抛出no_data_found异常；

            查询结果返回多行，PL/SQL将抛出too_many_rows 异常。

        对于上述两种异常发生时，类似于普通异常处理，程序控制权转移到异常处理部分(如没有异常处理则程序中断)。对于异常被激后发，SQL游标的四个属性在此将不可使用，如下面的例子：


DECLARE  
      v_ename emp.ename%TYPE;  
    BEGIN  
      SELECT ename INTO v_ename FROM emp WHERE empno=&no;  
      IF  SQL%ROWCOUNT=0 OR SQL%NOTFOUND THEN  
        DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!');  
      ELSE  
        DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename );  
      END IF;  
    EXCEPTION  
      WHEN NO_DATA_FOUND THEN  
        DBMS_OUTPUT.PUT_LINE('No data found for '||&no);  
    END;




         操作及结果：


Enter value for no:70  
    No data found for 70  
          
    Enter value for no:7788  
    The name for record 7788 is SCOTT




        从上面的演示中可以看到，当select into没有返回行时，IF  SQL%ROWCOUNT=0OR SQL%NOTFOUND THEN 语句并没有被执行。

        使用下面改进过的代码来执行，即可以将SQL游标属性判断放置到EXCEPTION部分。

DECLARE  
      v_ename emp.ename%TYPE;  
    BEGIN  
      SELECT ename INTO v_ename FROM emp WHERE empno=&no;  
      IF SQL%NOTFOUND THEN  
        DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!');  
      ELSE  
        DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename );  
      END IF;  
    EXCEPTION  
      WHEN NO_DATA_FOUND THEN  
        IF SQL%NOTFOUND THEN  
          DBMS_OUTPUT.PUT_LINE('The record '||&no||' is not exist!');  
          DBMS_OUTPUT.PUT_LINE('No data found for '||&no);  
        ELSE  
          DBMS_OUTPUT.PUT_LINE('The name for record '||&no||' is '||v_ename );  
        END IF;  
    END;



        操作及结果：


Enter value for no:80  
    The record 80 is not exist!  
    No data found for 80






三、显式游标

       1.显式游标的属性


           显式游标的也是4个属性（%FOUND，%NOTFOUND，%ISOPEN，%ROWCOUNT），与隐式游标的相同，在文章头部已作介绍。

       2.显式游标的使用步骤：声明(定义)--->打开--->读取--->关闭。
          (1) 声明游标
          格式如下：


CURSOR cursor_name [(parameter[, parameter]...)]  
    [RETURN return_type]  
    IS select_statement




          例子：


DECLARE  
       CURSOR emp_cursor(department IN number2 DEFAULT 20)  
    IS   
       SELECT empno,ename,job,sal   
       FROM emp   
       WHERE deptno=department;




打开游标

          格式：OPEN cursor_name[(VALUE[,VALUE]...)];

          例子：OPEN emp_cursor;

          (3)读取游标数据

          格式：FETCH cursor_name INTO { variable_list | record_variable };

          例子：FETCH emp_cursor INTO emp_row;

          (4)关闭游标

          格式：CLOSE cursor_name;

          例子：CLOSE emp_cursor;


       3.显式游标完整的使用示例：


[sql] ​​ view plain​​​​copy​​



  1. --下面的程序将显示声明一个游标，并在循环中使用SELECT语句提取所有部门为30的员工信息。



DECLARE  
          CURSOR emp_cursor(department IN NUMBER DEFAULT 20) IS  
            SELECT empno, ename, job, sal  
              FROM emp  
             WHERE deptno = department;  
          
          TYPE employee IS RECORD(  
            id   emp.empno%TYPE,  
            NAME emp.ename%TYPE,  
            job  emp.job%TYPE,  
            sal  emp.sal%TYPE);  
          emp_row employee;  
        BEGIN  
          OPEN emp_cursor(30);  
          FETCH emp_cursor INTO emp_row;  
          WHILE emp_cursor%FOUND LOOP  
            dbms_output.put('员工编号 ' || emp_row.id);  
            dbms_output.put(' 姓名 ' || emp_row.name);  
            dbms_output.put(' 职位 ' || emp_row.job);  
            dbms_output.put(' 薪金 ' || emp_row.sal);  
            --填充下一条记录  
            FETCH emp_cursor INTO emp_row;  
          END LOOP;  
          CLOSE emp_cursor;  
        END;



        操作及结果：


[sql] ​​ view plain​​​​copy​​



  1. --



四、游标FOR循环

        在大多数时候我们在自定义显示游标的时候都遵循下面的步骤：
        1、打开游标
        2、开始循环
        3、从游标中取值
        4、检查那一行被返回
        5、处理
        6、关闭循环
        7、关闭游标
        可以简单的把这一类代码称为游标用于循环。但还有一种循环与这种类型不相同，这就是FOR循环，用于FOR循环的游标按照正常的声明方式声明，它的优点在于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标FOR 循环的语法如下：


[sql] view plaincopy

          FOR record_name IN  
            corsor_name[(parameter[,parameter]...)]  
            | (query_difinition)  
          LOOP  
            statements  
          END LOOP;




       使用例子： 

[sql] view plaincopy

    SET serveroutput ON;  
    DECLARE  
      CURSOR mycur(vartype NUMBER) IS  
        SELECT emp_no, emp_zc FROM cus_emp_basic WHERE com_no = vartype;  
    BEGIN  
      FOR person IN mycur(000627)   
      LOOP  
        dbms_output.put_line('雇员编号:' || person.emp_no || ',地址:' ||  
                             person.emp_zc);  
      END LOOP;  
    END;




       注：在使用游标FOR循环时，一定不要使用OPEN语句、FETCH语句和CLOSE语句，否则将产生错误。





文章部分参考自：

​​ http://www.51cto.com/art/200601/16298.htm​​