Oracle数据库--实用操作(4)游标

游标简介

原理：逐行处理查询结果，以编程的方式访问数据。用游标提取一行，数据保存到游标中，一次处理一行。

应用场景：在代码中需要接收多行查询出来的记录，借助游标遍历结果集。常与过程一起使用。

游标的类型：隐式、显示、REF游标

一、隐式游标

      在PL/SQL中使用DML语句时自动创建隐式游标,即：所有的SQL 语句在上下文区内部都是可执行的，因此都有一个游标指向上下文区，此游标就是所谓的SQL游标(SQL cursor)，与显式游标不同，SQL游标不被程序打开和关闭，通过检查隐式游标的属性可以获得最近执行的DML 语句的信息

      隐式游标的属性有：SQL+属性

1.       %FOUND – SQL 语句影响了一行或多行时为 TRUE

2.       %NOTFOUND – SQL 语句没有影响任何行时为TRUE

3.       %ROWCOUNT – SQL 语句影响的行数

4.       %ISOPEN  - 游标是否打开，始终为FALSE

SQL> SETSERVEROUTPUT ON

SQL> BEGIN

   UPDATE toys SET toyprice=270

   WHERE toyid= 'P005';

   IF SQL%FOUND THEN

            DBMS_OUTPUT.PUT_LINE(‘表已更新');

   END IF;

     END;       --只有在DML 语句影响一行或多行时，才返回True

隐式游标用的很少，而且Oracle数据库自动创建。

二、显式游标

显式游标在PL/SQL 块的声明部分定义查询，该查询可以返回多行

  

显式游标的使用：

        如果要使用创建好的游标，需要先打开游标，语法结构如下：

open 游标名;

       打开游标的过程有以下两个步骤：

（1）将符合条件的记录送入内存。

 

（2）将指针指向第一条记录。

游标打开了要关闭：CLOSE 游标名；

游标提取数据的操作（每次只能提取一行）

如果要提取游标中的数据，需要使用fetch命令，语法形式如下。

fetch 游标名 into 变量名1, 变量名2,……;

或fetch 游标名into 记录型变量名;

示例代码

set serveroutput on

declare   tempsalscott.emp.sal%type;

         cursormycursor is

         select* from scott.emp

         wheresal>tempsal;

         cursorrecordmycursor%rowtype;

begin       tempsal:=800;

         openmycursor;

         fetchmycursor into cursorrecord;   --抓取每行数据into到变量cursorrecord

         dbms_output.put_line(cursorrecord.deptno);

end;

 

带参数的显式游标

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

                   desig    VARCHAR2(20);

                   emp_codeVARCHAR2(5);

                   empnm    VARCHAR2(20);

                   CURSORemp_cur(desigparam VARCHAR2) IS  --参数desigparam

                    SELECT empno, ename FROM employee

                    WHERE designation=desigparam;

    BEGIN

                   desig:='&desig';

                   OPENemp_cur(desig);    --open时，带参数

                   LOOP

                            FETCHemp_cur INTO emp_code,empnm;

                            EXITWHEN emp_cur%NOTFOUND;

                            DBMS_OUTPUT.PUT_LINE(emp_code||''||empnm);

                END LOOP;

                   CLOSEemp_cur;

    END;

循环游标

      循环游标用于简化游标处理代码

      当用户需要从游标中提取所有记录时使用

      不需要显示打开和关闭游标

      循环游标的语法如下：

FOR<record_index> IN <cursor_name>

LOOP

   <executable statements>

END LOOP;

注意：<record_index> 名字可以不需要定义，直接使用

因为是Oracle隐含定义的变量名

 

SQL> SET SERVER OUTPUT ON

SQL> DECLARE

           CURSOR mytoy_cur IS

             SELECT toyid, toyname, toyprice

             FROM toys;

    BEGIN

           FOR toy_rec IN mytoy_cur

           LOOP

                 DBMS_OUTPUT.PUT_LINE(

                             ‘玩具编号：'||' '||toy_rec.toyid||' '           

           ||‘玩具名称：'||' '||toy_rec.toyname||' '

           ||‘玩具单价：'||' '||toy_rec.toyprice);

         END LOOP;

    END;

三、REF 游标

      REF 游标和游标变量用于处理运行时动态执行的 SQL 查询

      创建游标变量需要两个步骤：

1.       声明 REF 游标类型

2.       声明 REF 游标类型的变量

      用于声明 REF 游标类型的语法为：

         TYPE<ref_cursor_name> IS REF CURSOR

         [RETURN<return_type>];

DECLARE

         TYPE toys_curtype IS REF CURSOR  --声明ref游标

         RETURN toys%ROWTYPE;

        toys_curvar toys_curtype;

         toys_rec toys%ROWTYPE;

    BEGIN

       OPEN toys_curvar FOR

         SELECT * FROM toys;

       FETCH toys_curvar INTO toys_rec;

      ...

       CLOSE toys_curvar;

    END;

例：

...................................................................................................................................

CREATE OR REPLACE PROCEDUREUPDATE_SAL99  --创建  存储过程。

(

 P_MIN IN NUMBER       --参数

, P_MAX IN NUMBER

, P_dno IN NUMBER

)

AS

 V_average number;   --声明变量

 V_sal emp.sal%type;

 cursor C_emp(n number) is     --声明游标

 select sal from emp where deptno=n for update;

BEGIN

 select avg(sal) into V_average from emp whereDEPTNO=P_dno;  --查出员工平均工资，赋给变量V_average

 

 open c_emp(P_dno);  --打开游标

 

 loop                                  --利用游标，循环读取每行数据

  fetch c_emp into  V_sal;

  exit when c_emp%notfound;   --当游标为空，则退出循环 

   ifv_sal > V_average  then                                   --如果>平均工资，给员工工资增长P_max

     update emp set sal=sal+p_max where current of c_emp;

  else

     update emp set sal=sal+p_min where current of c_emp;

  endif;

  endloop;

 

 close c_emp;

 commit;   --提交

 exception

  whenothers then

 rollback;       --异常处理：任何异常都  回滚

 

 NULL;

END UPDATE_SAL99;

...........................................................................................

--调用

 execute UPDATE_SAL99(20,10000,20000);  --调用过程，传递实参

 select ename, sal from emp where deptno = 20;

 

游标变量的优点和限制

      游标变量的功能强大，可以简化数据处理。

      游标变量的优点有：

1.       可从不同的 SELECT 语句中提取结果集

2.       可以作为过程的参数进行传递

3.       可以引用游标的所有属性

4.       可以进行赋值运算

      使用游标变量的限制：

1.       不能在程序包中声明游标变量

2.       FOR UPDATE子句不能与游标变量一起使用

3.       不能使用比较运算符

总结：

      游标用于处理查询结果集中的数据

      游标类型有：隐式游标、显式游标和 REF游标

      隐式游标由 PL/SQL自动定义、打开和关闭

      显式游标用于处理返回多行的查询

      显式游标可以删除和更新活动集中的行

      要处理结果集中所有记录时，可使用循环游标

      在声明 REF游标时，不需要将 SELECT语句与其关联