Oracle中游标的使用

一、 游标的概念：

      游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

 

二、游标分类：

      游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 

     游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。 

 

  1、 隐式游标：DML操作和单行SELECT语句会使用隐式游标，它们是： 

              * 插入操作：INSERT。 

              * 更新操作：UPDATE。 

              * 删除操作：DELETE。 

              * 单行查询操作：SELECT ... INTO ...。 

         当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如下所示。

 

隐式游标的属性	返回值类型	意    义
SQL%ROWCOUNT	整型	代表DML语句成功执行的数据行数
SQL%FOUND	布尔型	值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND	布尔型	与SQL%FOUND属性返回值相反
SQL%ISOPEN	布尔型	DML执行过程中为真，结束后为假

【例1】　使用隐式游标的属性，判断对雇员工资的修改是否成功（ 通过SQL%FOUND属性判断修改是否成功，并给出相应信息）。 

 

SQL> set serveroutput on

SQL> begin

  2  update emp set sal=sal+2300 where id=1;

  3  if SQL%FOUND then

  4     dbms_output.put_line('成功修改员工工资!');

  5  commit;

  6  else

  7     dbms_output.put_line('修改员工工资失败!');

  8  end if;

  9  end;

 10  /

成功修改员工工资!

PL/SQL 过程已成功完成。

 

2、 显式游标 ： 游标的定义和操作分成以下4个步骤。

    （1） 声明游标 ：在DECLEAR部分按以下格式声明游标： 

                              CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] 

                              IS SELECT语句; 

           参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。 SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。 

     （2） 打开游标 ：在可执行部分，按以下格式打开游标： 

                       OPEN 游标名[(实际参数1[，实际参数2...])]; 

         打开游标时，SELECT语句的查询结果就被传送到了游标工作区。 

  

     （3） 提取数据 ：在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。 

                            FETCH 游标名 INTO 变量名1[，变量名2...]; 

                    或 

                           FETCH 游标名 INTO 记录变量; 

       游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。 下面对这两种格式进行说明： 

       第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。 

       第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。 定义记录变量的方法如下： 

                   变量名 表名|游标名%ROWTYPE； 

             其中的表必须存在，游标名也必须先定义。

 

      （4） 关闭游标 

                        CLOSE 游标名; 

        显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

 

   【例2】用游标提取emp表中1号雇员的名称和薪水。 

 

SQL> set serveroutput on

SQL> declare

  2    v_ename varchar2(10);

  3    v_job varchar2(10);

  4    cursor emp_cursor is   --声明游标

  5      select ename,sal from emp where id=1;

  6  begin

  7   open emp_cursor;  --打开游标

  8  fetch emp_cursor into v_ename,v_job;  --提取数据

  9  dbms_output.put_line(v_ename||','||v_job);

10  close emp_cursor;   --关闭游标

11  end;

12  /

James,12300

 

【例3】显示工资最高的前3名雇员的名称和工资。

    

SQL> set serveroutput on

SQL> declare

  2    v_ename varchar2(20);

  3    v_sal number(5);

  4  cursor emp_cursor is select ename,sal from emp order by sal desc;

  5  begin

  6    open emp_cursor;

  7  for i in 1..3 loop

  8     fetch emp_cursor into v_ename,v_sal;

  9     dbms_output.put_line(v_ename||','||v_sal);

 10  end loop;

 11  close emp_cursor;

 12  end;

 13  /

James,12300

孙权,9000

周瑜,8500

 

【例4】 使用特殊的FOR循环形式显示全部雇员的编号和名称。

    

SQL> set serveroutput on

SQL> declare

  2   cursor emp_cursor is

  3    select id,ename from emp;

  4  begin

  5  for emp_record in emp_cursor loop

  6    dbms_output.put_line(emp_record.id||'  '||emp_record.ename);

  7  end loop;

  8  end;

  9  /

1  James

2  Tom

3  Smith

4  刘备

5  关羽

6  张飞

7  孙权

8  周瑜

【例5】 另一种形式的游标循环。  

SQL>  set serveroutput on

SQL>  begin

  2     for re in(select ename from emp) loop

  3       dbms_output.put_line(re.ename);

  4     end loop;

  5  end;

  6  /

James

Tom

Smith

刘备

关羽

张飞

孙权

周瑜

  说明：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。

 

    （5） 显式游标属性

        虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。显式游标的属性如下所示。

 

显式游标的属性	返回值类型	意    义
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND	布尔型	与%FOUND属性返回值相反
%ISOPEN	布尔型	游标已经打开时值为真，否则为假

可按照以下形式取得游标的属性： 

                    游标名%属性 

要判断游标emp_cursor是否处于打开状态，可以使用属性emp_cursor%ISOPEN。如果游标已经打开，则返回值为“真”，否则为“假”。

 

【训练1】  使用游标的属性练习。 

SQL> set serveroutput on

SQL> declare

  2    v_ename varchar2(20);

  3  cursor emp_cursor is

  4    select ename from emp;

  5  begin

  6    open emp_cursor;

  7  if emp_cursor%ISOPEN then

  8  loop

  9     fetch emp_cursor into v_ename;

10    exit when emp_cursor%NOTFOUND;

11     dbms_output.put_line(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);

12  end loop;

13  else

14    dbms_output.put_line('用户信息：游标没有打开！');

15  end if;

16  close emp_cursor;

17  end;

18  /

说明：本例使用emp_cursor%ISOPEN判断游标是否打开；使用emp_cursor%ROWCOUNT获得到目前为止FETCH语句返回的数据行数并输出；使用循环来获取数据，在循环体中使用FETCH语句；使用emp_cursor%NOTFOUND判断FETCH语句是否成功执行，当FETCH语句失败时说明数据已经取完，退出循环。 

     

  【训练2】  带参数的游标。

 

SQL>  set serveroutput on

SQL>  declare

  2     v_empid number(5);

  3     v_ename varchar2(20);

  4   cursor emp_cursor(pid number,jname varchar2) is

  5    select id,ename from emp

  6    where id = pid and ename = jname;

  7  begin

  8    open emp_cursor(6,'张飞');

  9   loop

 10     fetch emp_cursor into v_empid,v_ename;

 11   exit when emp_cursor%NOTFOUND;

 12   dbms_output.put_line(v_empid||','||v_ename);

 13  end loop;

 14  end;

 15  /

 【训练2】  通过变量传递参数给游标。

      

SQL>  set serveroutput on

SQL>  declare

  2     v_id number(5);

  3    v_ename varchar2(20);

  4    v_sal number(5);

  5   cursor emp_cursor is

  6    select id,ename from emp

  7    where sal>v_sal;

  8  begin

  9    v_sal:=5000;

10   open emp_cursor;

11  loop

12    fetch emp_cursor into v_id,v_ename;

13    exit when emp_cursor%NOTFOUND;

14    dbms_output.put_line(v_id||','||v_ename);

15  end loop;

16  end;

17  /