PLSQL基础（三）游标

游标有两种:

显示游标，隐式游标

 

显示游标是用CURSOR...IS命令定义的游标，它可以对查询语句(SELECT)返回的多条记录进行处理，而隐式游标是在执行插入(INSERT),删除(DELETE),修改（UPDATE）和返回单条记录的查询（SELECT）语句时由PLSQL自动定义的。

 

显示游标的操作

1)打开游标    2）推进游标     3）关闭游标

 

 

声明游标：

DECLARE

    v_auths auths%ROWTYPE;

    v_code auths.author_code%TYPE;

    CURSOR c_auths IS

     SELECT * FROM auths WHERE author_code = v_code;

 

打开游标:

v_code = 'A00001';

OPEN c_auths;

 

声明游标

DECLARE

    CURSOR c_Auths(P_code auths.author_code%TYPE) IS

    SELECT * FROM auths WHERE author_code = P_code;

打开游标

OPEN c_Auths（'A00001');

 

当打开显示游标后，就可以使用FETCH语句来推进游标，返回查询结果中的一行。没执行完一条FETCH语句后，显示游标会自动指向查询结果集的下一行。

FETCH c_Auths INTO c_Auths;

 

当整个结果集都检索完以后，应当关闭游标，关闭游标用来通知PLSQL游标操作已经结束，并且释放游标所占用的资源.

CLOSE cursor_name;

 

 

游标的属性

游标有四个属性 %FOUND,%NOTFOUND,%ISOPEN,%ROWCOUNT

 

显式游标的推荐循环

DECLARE

    v_Salary  Auths.salary%TYPE;

    v_Code    Auths.author_code%TYPE;

    CURSOR c_salary IS

    SELECT salary,author_code FROM auths WHERE author_code <= 'A00006';

BEGIN

     OPEN c_salary;

    LOOP

         FETCH c_salary INTO v_Salary,v_Code;

         EXIT WHEN c_salary%NOTFOUNT;

         IF v_salary <= 2000 THEN

              UPDATE auths SET salary = salary + 50 WHERE author_code = v_Code;

         END IF;

     END LOOP;

 

     CLOSE c_salary;

   COMMIT;

END;

 

无论是使用LOOP...END LOOP语句还是使用WHILE...LOOP语句来完成游标的推进循环，都必须使用OPEN,FETCH,CLOSE语句来控制游标的打开，推进和关闭，PLSQL还提供了一种简单类型的循环，可以自动控制游标的打开，推进和关闭,这叫做游标的FOR循环.

DECLARE

    CURSOR c_salary IS

    SELECT salary FROM auths WHERE author_code <= 'A00006';

BEGIN

--开始游标FOR循环，隐含地打开c_salary游标

    FOR v_salary IN c_calary LOOP

          IF v_salary.salary <= 200 THEN

               UPDATE auths SET salary = salary + 50 WHERE salary = v_salary.salary;

          END IF;

    END LOOP;

    COMMIT;

END;

 

上例中v_salary没有在块的定义部分声明，该变量被PLSQL编译器隐含的声明了,该变量的类型为c_salary%ROWTYPE,其作用域只在循环内部。

 

 

 

隐式游标

显示游标仅仅是用来控制返回多行的SELECT语句，而隐式游标是指向处理所有的SQL语句的环境区域的指针，隐式游标也叫SQL游标，与显示游标不同的是，SQL游标不能通过专门的命令打开或关闭，PLSQL隐式的打开SQL游标，并在它内部处理SQL语句，然后关闭它。

SQL游标用来处理INSERT,UPDATE,DELETE以及返回一行的SELECT ...INTO语句。一个SQL游标不管是打开还是关闭，OPEN,FETCH,CLOSE命令都不能操作它。

SQL游标与显示游标类似，也有%FOUND,%NOTFOUND,%ISOPEN,%ROWTYPE属性，SQL游标的属性通常是返回执行INSERT,DELETE,UPDATE或SELECT...INTO语句时的信息，当打开SQL游标之前，SQL游标的属性都是NULL.

%FOUND

当使用INSERT,DELETE或者UPDATE语句处理一行或多行，或执行SELECT INTO语句返回一行时，%FOUND属性返回TRUE,否则返回FALSE.

注意:如果执行SELECT INTO语句时返回多行，则会产生TOO_MANEY_ROWS异常，并将控制权转移到异常处理部分，%FOUND属性并不返回TRUE.如果执行SELECT INTO语句时返回0行，会产生NO_DATE_FOUND异常，%FOUND属性并不返回FALSE.

 

%NOTFOUND

%NOTFOUND恰与%FOUND属性相反，当使用INSERT,DELETE或者UPDATE语句处理的函数为0时，%NOTFOUND属性返回TRUE,否则返回FALSE.

 

%ISOPEN

因为在执行了DML语句后，Oracle会自动关闭SQL游标，所以%ISOPEN总会FALSE.

 

%ROWCOUNT

该属性返回执行INSERT,DELETE或UPDATE语句返回的行数,或返回执行SELECT INTO语句时查询的行数，如果INSERT,DELETE,UPDATE或SELECT INTO语句返回的行数为0，则%ROWTYPE属性返回0.

BEGIN  

      UPDATE auths SET entry_date_time = SYSDATE WHERE author_code = 'A00007';

     --如果UPDATE语句中修改的行不存在(SQL%NOTFOUND返回TURE)

 IF SQL%NOTFOUND THEN

      INSERT INTO auths values(......)......

END IF;

END;

我们使用SQL%ROWCOUNT完成与上例相同的功能。

BEGIN

    UPDATE auths SET entry_date_time = SYSDATE WHERE author_code = 'A00007';

     IF SQL%ROWCOUNT = 0 THEN

         INSERT INTO ......

    END IF;

END;

 

 

 

游标变量

到目前为止前面所有显示游标的例子都是静态游标----即游标与一个SQL语句关联，并且该SQL语句在编译时已经确定，而游标变量是一个引用类型（REF）的变量，当程序运行时使用游标变量可以指定不同的查询，所以游标变量的使用比静态游标更灵活。

我们可以使用一个没有指定结果集类型的游标变量来指定多个不同类型的查询.

DECLARE

--定义游标变量类型t_CurRef,该变量类型没有指定结果集类型，所以该游标变量类型的变量可以返回不同的PLSQL记录类型

        TYPE t_CurRef IS REF COUSOR

--声明一个游标变量类型的变量

          c_CursorRef t_CurRef;

--定义PLSQL记录类型

         TPYE t_AuthorRec IS RECORD

        (

                AuthorCode auths.author_code%TYPE;

                Name auths.name%TYPE

        );

       

        TYPE t_ArticleRec IS RECODE

         (

                 AuthorCode article.author_code%TYPE

                 Title     article.Title%TYPE

           );

  --声明两个记录类型变量

   v_Author t_AuthorRec;

   v_Ariticle  t_ArticleRec;

BEGIN

--打开游标变量c_CursorRef,返回t_AuthorRec类型的记录

    OPEN  c_CursorRef FOR

     SELECT author_code,name FROM auths;

    

     FECTCH c_CursorRef INTO v_Author;

     WHILE c_CursorRef%FOUND LOOP

           ...

           FETCH c_CursorRef INTO v_Author;

     END LOOP;

   

     CLOSE c_CursorRef;

 

     OPEN c_CursorRef FOR

     SELECT  author_code,title FROM ariticle

      FETCH c_CursorRef INTO v_Article;

     WHILE c_CursorRef%FOUND LOOP

                   ...

            FETCH c_CursorRef INTO v_Article；

    END LOOP;

   

    CLOSE c_CursorRef;

END;

 

 

注意,在上例中，第一次关闭游标变量是可以省略的，因为在第二次打开游标变量是，就将第一次的查询丢失掉了.