初学PL/SQL 游标

1,显示游标的操作

  

(1) 定义游标

      根据查询的数据情况，在PL/SQL块的声明部分定义游标，语法为：

         cursor cursor_name is select_statement;

注意：

•      游标必须在PL/SQL块的声明部分进行定义；
•      游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前定义；
•      定义游标时并没有生成数据，只是将定义信息保存到数据字典中；
•      游标定义后，可以使用cursor_name%rowtype 定义变量；

 

（2） 打开游标

       为了在内存中分配缓冲区，并从数据库中检索数据，需要在PL/SQL块的执行部分打开游标，语法为：

        open cursor_name;

注意：

•      只有在打开游标时，才是真正的创建缓冲区，并从数据库检索数据；
•      游标一旦打开，就无法再次打开，除非先关闭；
•      如果游标定义中的变量值发生变化，则只能在下次打开游标时才起作用；

（3）检索游标

    语法为：

      Fetch cursor_name into variable_list|record_variable;

注意：

 

•    在使用fetch语句之前必须先打开游标，保证缓冲区有数据；
•    对游标第一次使用fetch语句时，游标指针指向第一条记录，因此操作的对象是第一条记录。操作完后，游标指针指向下一条记录；
•    游标指针只能向下移动，不能回退。
•    into子句中变量个数，顺序，数据类型必须和缓冲区中的对应。

由于游标对应的缓冲区中可能有多个记录，因此检索游标的过程是一个循环过程。

（4） 关闭游标

游标对应缓冲区的数据处理完后，应该及时关闭游标，以释放它占用的系统资源。关闭游标的语法为：

  close cursor_name;

例如：根据书名查询对应书的信息，由于查询的结果可能有多个，因此采用游标；

declare
   v_bookname book.bookname%type;
   cursor c_book is select * from book where bookName=v_bookname;
   v_book c_book%rowtype;
 begin
    v_bookname:=&x;
    open c_book;
     Loop
        fetch c_book into v_book;
        exit when c_book%notfound;
        Dbms_Output.put_line('bookId  '||v_book.bookId || '  bookname  ' || v_book.bookname);
     end Loop;
     close c_book;
 end;

2,显示游标的属性

无论显示游标还是隐式游标，都具有%isopen,%found,%notfound,%rowcount四个属性

利用游标属性可以判断当前游标的状态。

%isopen:布尔型，由于检查游标是否已经打开。若打开，则返回true,否则返回false;

%found:布尔型，判断最近一次使用fetch语句是否从缓冲区检索到数据。如果检索到数据，返回true,否则返回false;

%notfound：布尔型，与%found相反，如果没有检索到数据，返回true，否则返回false;

%rowcount:数值型，返回到目前为止从游标缓冲区检索的记录的个数。

注意：游标属性只能在PL/SQL块中使用，不能在SQL命令中使用

3,参数化显示游标

   参数化游标是指定义游标时使用参数，当使用不同的参数值打开游标时，可以生成不同的结果集。语法为：

    cursor cursor_name(parameter1 datatype [,parameter2 datatype...]) is select_statement;

在执行时打开参数化游标的语法为：

       open cursor_name(parameter1[,parameter2...]);

注意：定义游标时，只能指定参数的类型，不能指定参数化的长度，精度，刻度；

          打开带参数的游标时，实参的个数和数据类型等必须与游标定义时行参个数和数据类型相匹配；

例如查询每本书的信息：

 

 declare
    cursor c_book(v_bookname book.bookname%type) is
    select * from book where bookname=v_bookname;
    v_book c_book%rowtype;
  begin
    open c_book('hello world');
    Loop
      fetch c_book into v_book;
      exit when c_book%notfound;
      Dbms_Output.put_line('v_book bookId  ' || v_book.bookId  || '  v_book bookname  ' || v_book.bookname);
    end Loop;
    close c_book;
   
    open c_book('sssss');
    Loop
      fetch c_book into v_book;
      exit when c_book%notfound;
      Dbms_Output.put_line('v_book bookId  ' || v_book.bookId  || '  v_book bookname  ' || v_book.bookname);
    end Loop;
    close c_book;
  end;

4，显示游标的检索

由于游标对应的缓冲区中可能有多条记录，而PL/SQL中每次只能处理一条记录。因此需要采取循环的方式检索数据进行处理。

根据循环方式的不同，检索游标有三种方法：

（1） 利用简单循环检索游标  基本方式为：

       declare

           cursor cursor_name is select...;

       begin

          open cursor_name;

           Loop

                fetch ...into...;

                exit when cursor_name%notfound;

                ......

          end Loop;

           close cursor_name;

      end;

（2）利用while循环检索游标   基本方法为：

declare

     cursor cursor_name;

 begin

      open cursor_name;

      fetch...into...;

      while cursor_name%found

        Loop

            fetch...into...;

            ......

        end Loop;

        close cursor_name;

end;

注意：在循环体外进行一次fetch操作，作为第一次循环的条件

例如：根据书名进行分组，查询每本书的平均价格

 declare
   cursor c_book is select bookname,avg(bookprice) avgprice from book group by bookname;
   v_book c_book%rowtype;
   begin
     open c_book;
     fetch c_book into v_book;
     while c_book%found
      Loop
        Dbms_Output.put_line(v_book.bookname || '   ' || v_book.avgprice);
        fetch c_book into v_book;
      end Loop;
      close c_book;
   end;

（3） 利用for循环检索游标

利用for循环检索游标时，系统会自动打开，检索和关闭游标。用户只需要考虑如果处理从游标缓冲区检索出来的数据，其方法为：

declare

   cursor cursor_name is select...;

begin

    for loop_variable in cursor_name Loop

         ......

    end Loop;

end;

例如：根据书名进行分组，查询每本书的平均价格

 declare
    cursor c_book is select bookname,avg(bookprice) avgprice from book group by bookname;
   begin
     for v_book in c_book Loop
      Dbms_Output.put_line(v_book.bookname || '   ' || v_book.avgprice);
     end Loop;
   end;

利用for循环检索游标时，系统首先隐含的定义一个数据类型为cursor_name%rowtype的循环变量loop_variable.

5 ，利用游标更新或删除数据

利用游标不仅可以处理select，还可以在处理游标中当前行数据的同时，修改该行所对应的数据库中的数据。

其语法为：

  select select_list_item from table for update;

若定义游标时使用for update 子句，则在执行时，可以在update语句或delete语句中使用where current of子句，以修改或

删除游标中当前行所对应的数据库中的数据。语法为：

  where  current of cursor_name;

注意：如果游标定义时没有使用for update子句，则不能利用该游标修改或删除数据库中的数据。

例如：

declare
    cursor c_book is select * from book for update;
    v_price number;
   begin
     for v_book in c_book Loop
       case v_book.bookprice
            when 50 then v_price:=100;
            when 100 then v_price:=200;
            when 200 then v_price:=400;
            else
              v_price:=888;
       end case;
           update book set bookprice=v_price where current of c_book;
     end Loop;
   end;