PL/SQL学习笔记：游标[转]

1.什么是游标

 游标（Cursor）是一个指向上下文去的句柄（handle）或指针。
 游标可分为显示游标＆隐示游标两种。

2.显示游标

    显示游标用来处理多于一行的SELECT语句。
    2.2显示游标的处理包括4歌PL/SQL步骤，如下：
         1）声明游标。
         2）为查询打开游标。
         3）将结果提取（fetch）到PL/SQL变量中。
         4）关闭游标。
     下面是一个关于显示游标处理的完整事例：

     DECLARE
        v_Pet_id occupancy.pet_id%TYPE;
        /*声明游标*/
        CURSOR occupancy_cur IS
           SELECT pet_id, room_number
             FROM occupancy WHERE pet_id = v_Pet_id;
        occupancy_rec occupancy_cur%ROWTYPE;
     BEGIN
        v_Pet_id := 22;
        /*为查询打开游标*/
        OPEN occupancy_cur;
        LOOP
           /*将结果提取（fetch）到PL/SQL变量中*/
           FETCH occupancy_cur INTO occupancy_rec;
           /*如果前面FETCH没有返回一个行，那么occupancy_cur%NOTFOUND
             就会返回一个True，从而跳出当前的循环*/
           EXIT WHEN occupancy_cur%NOTFOUND;
           update_bill
               (occupancy_rec.pet_id, occupancy_rec.room_number);
         END LOOP;
         /*关闭游标*/
         CLOSE occupancy_cur;
      END;

     另外，游标也可以这样声明：   

     DECLARE
        /*声明游标*/
        CURSOR occupancy_cur(p_Pet_id occupancy.pet_id%TYPE) IS
           SELECT pet_id, room_number
             FROM occupancy WHERE pet_id = v_Pet_id;
        occupancy_rec occupancy_cur%ROWTYPE;
     BEGIN
        /*为查询打开游标*/
        OPEN occupancy_cur(22);
        LOOP
           /*将结果提取（fetch）到PL/SQL变量中*/
           FETCH occupancy_cur INTO occupancy_rec;
           /*如果前面FETCH没有返回一个行，那么occupancy_cur%NOTFOUND
             就会返回一个True，从而跳出当前的循环*/
           EXIT WHEN occupancy_cur%NOTFOUND;
           update_bill
               (occupancy_rec.pet_id, occupancy_rec.room_number);
         END LOOP;
         /*关闭游标*/
         CLOSE occupancy_cur;
      END;

3.隐式游标

    隐式游标用于处理INSERT、UPDATE、DELETE＆单行的SELECT..INTO语句。
    隐式游标是指向处理所有的SQL语句的环境区域的指针，隐式游标也叫SQL游标。与显式游标不同的是，SQL游标不能通过专门的命令打开火关闭。PL/SQL隐式地打开SQL游标，并在它内部处理SQL语句，然后关闭它。
    下面具体介绍SQL游标的属性：

1)%FOUND
    当使用Insert，Delete或Update语句处理一行或多行，或执行SELECT INTO语句返回一行时，%FOUND返回TRUE，否则返回FALSE。
    注意，如果执行SELECT INTO语句时返回多行，则会产生TOO_MANY_ROWS异常，并将控制权转到异常处理部分，%FOUND属性并不返回TRUE。如果执行SELECT INTO语句时返回0行，会产生NO_DATA_FOUND异常，%FOUND属性并不返回FALSE。

2)%NOTFOUND与%FOUND属性相反，当使用INSERT、DELETE或UPDATE语句处理的行数为0时，%NOTFOUND属性返回TRUE,否则返回FALSE。例：

BEGIN
  UPDATE auths
     SET entry_date_time = SYSDATE
   WHERE author_cod = 'A00017';

  IF SQL%NOTFOUND THEN
     INSERT INTO auths(author_code, name, sex, birthdate,salary, entry_date_time)
     VALUES ('A00017','Tom', 1, '30-APR-40',98.5,SYSDATE);
  END IF;
END;

3)%ISOPEN
    因为在执行了DML语句后，ORacle会自动关闭SQL游标，所以SQL游标的%ISOPEN总为FALSE。

4)%ROWCOUNT
该属性返回执行INSERT、DELETE或UPDATE语句返回的行数，或返回执行SELECT INTO语句时查询出的行数。如果INSERT、DELETE、UPDATE或SELECT语句返回的行数为0，则%ROWCOUNT属性返回0。但如果SELECT INTO语句返回的行数为多航，则产生TOO_MANY_ROWS异常，并将控制权转到异常处理部分，而不是去判断%ROWCOUNT属性。 例：

BEGIN
  UPDATE auths
     SET entry_date_time = SYSDATE
   WHERE author_cod = 'A00017';

  IF SQL%ROWCOUNT=0 THEN
     INSERT INTO auths(author_code, name, sex, birthdate,salary, entry_date_time)
     VALUES ('A00017','Tom', 1, '30-APR-40',98.5,SYSDATE);
  END IF;
END;

4.游标变量
    到目前为止前面所有显式游标的例子都式静态游标－－即游标于一个SQL语句关联，并且该SQL语句在编译时已经确定。游标变量式一个引用类型(REF)的变量，它与C或Pascal中的指针类似。当程序运行时使用游标变量可以指定不同的查询，所以游标变量的使用比静态游标更灵活。
    下面是一个游标变量的例子：

DECLARE
  /*定义一个游标变量*/
  TYPE t_CurRef IS REF CURSOR;
  /*声明一个游标变量类型的变量*/
  c_CursorRef t_curref;
  /*定义PL/SQL记录类型t_AuthorRec,该类型的变量用来接收游标变量的返回值*/
  TYPE t_AuthorRec IS RECORD(
       AuthorCode auths.author_code%TYPE,
       Name auths.name%TYPE);
  /*定义PL/SQL记录类型t_ArticleRec,该类型的变量也用来接受游标变量的返回值*/
  TYPE t_ArticleRec IS RECORD(
       AuthorCode auths.author_code%TYPE,
       Title auths.title%TYPE);
  /*声明两个记录类型变量*/
  v_Author t_AuthorRec;
  v_Article t_ArticleRec;
BEGIN
  --打开游标变量c_CursorRef,返回t_AuthorRec;
  OPEN c_CursorRef FOR
  SELECT author_code, name FROM auths
    WHERE author_code IN ('A0001','A0002','A0003','A0004','A0005');
  --推进游标变量
  FETCH c_CursorRef INTO v_Author ;
  --游标变量的推进循环
  WHILE c_CursorRef%FOUND LOOP
    --将作家代码＆相应的作家名字输出到屏幕上。
    DBMS_OUTPUT.PUT(v_Author.AuthorCode||':'||v_Author.Name||'  ');
    FETCH c_CursorRef INTO v_Author;
  END LOOP;
  DBMS_OUTPUT.NEW_LINE;
  --关闭游标变量，仅仅将游标变量指定的资源释放掉，
  --游标变量本身的存储控件没有释放掉。
  CLOSE c_CursorRef;
 
  --在此打开游标变量，返回t_ArticleRec类型的记录。
  OPEN c_CursorRef FOR
    SELECT author_code, title FROM article
     WHERE author_code in ('A0001','A0002','A0003','A0004','A0005');

  FETCH c_CursorRef INTO v_Article;

  WHILE c_CursorRef%FOUND LOOP
    --将作家代码＆他的文章输出到屏幕上。
    DBMS_OUTPUT.PUT(v_Article.AuthorCode||':'||v_Article.Title||'  ');
    FETCH c_CursorRef INTO v_Article;
  END LOOP;
 
  CLOSE c_CursorRef;
END;