PLSQL开发笔记和小结（4）——PLSQL游标和goto语句（转载）

*****************************************

  PLSQL游标和goto语句

*****************************************

备注：下面提到的游标为静态cursor，包括显示和隐式。

游标，从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，他的作用域是全局的，但也必须明白，静态cursor也只有pl/sql代码才可以使用它。静态游标变量是在定义时就必须指定SQL语句。

cursor 游标(结果集)用于提取多行数据，定义后不会有数据，使用后才有。一旦游标被打开，就无法再次打开(可以先关闭，再打开)。

    declare

          cursor c_student is  select * from book;

    begin

          open c_student;

          close c_student;

    end;

第二种游标的定义方式，用变量控制结果集的数量。

    declare

          v_id binary_integer;

          cursor c_student is select * from book where id>v_id;

    begin

          v_id:=10;

          open c_student;

          close c_student;

    end;

第三种游标的定义方式，带参数的游标，用的最多。

    declare

          cursor c_student(v_id binary_integer) is select * from book where id>v_id;

    begin

          open c_student(10);

          close c_student;

    end;

游标的使用，一定别忘了关游标。

    declare

          v_student book%rowtype;

          cursor c_student(v_id binary_integer) is select * from book where id>v_id;

    begin

          open c_student(10);

          fetch c_student into v_student;

          close c_student;

          dbms_output.put_line(v_student.name);

    end;

如何遍历游标fetch

  游标的属性 %found,%notfound,%isopen,%rowcount。

  %found，若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常。

  %notfound，与%found行为相反。

  %isopen，判断游标是否打开。

  %rowcount，当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001。

注：

no_data_found和%notfound的用法是有区别的，小结如下

1）SELECT…INTO 语句触发 no_data_found；

2）当一个显式光标(静态和动态)的where子句未找到时触发%notfound；

3）当UPDATE或DELETE 语句的where子句未找到时触发sql%notfound；

4）在光标的提取(Fetch)循环中要用%notfound或%found来确定循环的退出条件，不要用no_data_found。

下面是几个实例：

create table BOOK

(

  ID       VARCHAR2(10) not null,

  BOOKNAME VARCHAR2(10) not null,

  PRICE    VARCHAR2(10) not null,

  CID      VARCHAR2(10) not null

);

--insert

create or replace procedure say_hello(i_name in varchar2,o_result_msg out varchar2)

as

     v_price varchar2(100);

     e_myException exception;

     begin

        insert into book(id,bookname,price) values (1,2,3);

        o_result_msg := 'success';

     exception

        when others then

             rollback;

             o_result_msg := substr(sqlerrm, 1, 200);

     end;

--update or delete

create or replace procedure say_hello(i_name in varchar2,o_result_msg out varchar2)

as

     v_price varchar2(100);

     e_myException exception;

     begin

        update book set price = '55' where bookname = i_name;

        delete from book where bookname = i_name;

        if sql%notfound then

           raise e_myException;

        end if;

        /*

       if sql%rowcount = 0 then--写法2

           raise e_myException;

        end if;

       */

        o_result_msg := 'success';

     exception

        when e_myException then

             rollback;

             o_result_msg := 'update or delete dail';

     end;

--select

create or replace procedure say_hello(i_name in varchar2,o_result_msg out varchar2)

as

     v_price varchar2(100);

     e_myException exception;

     begin

        select price into v_price from book where bookname = i_name;

        o_result_msg := 'success';

     exception

        when no_data_found then

             rollback;

             o_result_msg := 'select into dail';

     end;

loop方式遍历游标

    declare

          v_bookname varchar2(100);

          cursor c_book(i_id number) is select bookname from book where id = i_id;

    begin

        Open c_book(10);

        Loop

            Fetch c_book into v_bookname;

            exit when c_book%notfound;

            update book set price = '33' where bookname = v_bookname;

        End Loop;

        Close c_book;

    end;

    或

    declare

          v_bookname varchar2(100);

          cursor c_book(i_id number) is select bookname from book where id = i_id;

    begin

          Open c_book(10);

          Fetch c_book into v_bookname;

          While c_book%found Loop

              update book set price = '33' where bookname = v_bookname;

               Fetch c_book into v_bookname;

          End Loop;

          Close c_book;

    end;

while循环遍历游标，注意，第一次游标刚打开就fetch，%found为null，进不去循环，解决方法：while nvl(c_book%found,true) loop

    declare

         v_bookname varchar2(100);

         cursor c_book(i_id number) is select bookname from book where id = i_id;

    begin

         Open  c_book(10);

         while nvl(c_book%found,true) loop

--或这种写法：while c_book%found is null or c_book%found loop

             Fetch c_book into v_bookname;

             update book set price = '33' where bookname = v_bookname;

         End Loop;

         Close c_book;

    end;

for循环遍历，最简单，用的最多，不需要声明v_bookname,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式)

    declare

         cursor c_book(i_id number) is select bookname from book where id = i_id;

    begin

         for cur in c_book(10) loop

             update book set price = '53' where bookname = cur.bookname;

         end loop;

    end;

goto例子，一般不推荐使用goto，会使程序结构变乱

    declare

         i number:=0;

    begin

         if i=0 then

             goto hello;

         end if;

         <<hello>>

         begin

             dbms_output.put_line('hello');

             goto over;

         end;

         <<world>>

         begin

             dbms_output.put_line('world');

             goto over;

         end;

         <<over>>

             dbms_output.put_line('over');

     end;

 

转载自：http://www.blogjava.net/cheneyfree/archive/2008/07/19/216090.html