1.1 PL/SQL中的DML

在讲解游标之前，我们先回顾一下之前所学习的SQL知识，特别是使用频率最高的数据操作语言（DML）。

下表列出了Oracle支持的DML：

语句	描述
INSERT	向表中插入（添加）数据行
UPDATE	更新存储在表中的数据
DELETE	删除行
SELECT FOR UPDATE	禁止其他用户访问DML语句正在处理的行。
LOCK TABLE	禁止其他用户在表中使用DML语句

1.1.1 插入数据（INSERT）

首先看一下INSERT的语法：

1、 插入单行数据：

    INSERT INTO table [(col1, col2, ..., coln)]

       VALUES (val1, val2, ..., valn);

2、插入多行数据：

    INSERT INTO table [(col1, col2, ..., coln)]

    AS

       SELECT ...;

表名（table）后面的列目录是可选的，缺省的列目录是所有的列名，包括comlumn_id，comlumn_id可以在数据字典视图ALL_TAB_COLUMNS,USER_TAB_COLUMNS，或者DBA_TAB_COLUMNS中找到。

插入行的数据的数量和数据类型必须和列的数量和数据类型相匹配。不符合列定义的数据类型将对插入值实行隐式数据转换。NULL字符串将一个NULL值插入适当的列中。关键字NULL常常用于表示将某列定义为NULL值。

下面的两个例子是等价的。

insert into emp(empno,ename,job,mgr) values(1111,'test1',NULL,7900);

或

insert into emp(empno,ename,job,mgr) values(1111,'test1','',7900);

上机练习：

 

1.1.2 更新数据（UPDATE）

UPDATE命令用于修改表中的数据。语法如下：

    UPDATE table

       SET col1 = val1

           [, col2 = val2, ... colN = valN]

    [WHERE where clause];

上机练习：

 

1.1.3 删除数据（DELETE）

DELETE语句用来从表中删除一行或多行数据，该命令包含两个语句：

1、关键字DELETE FROM后跟准备从中删除数据的表名。

2、WHERE后跟删除条件

基本语法如下：

    DELETE FROM table

     [WHERE where-clause];

上机练习：

 

1.1.4 DML语句的游标属性

Oracle会存储最近一次执行的DML语句的属性，如下表所示：

属性名	描述
SQL%FOUND	如果一条或多条数据行被成功修改（创建、更新、删除），则返回“TRUE”。
SQL%NOTFOUND	如果没有数据行被DML语句修改，则返回TRUE。
SQL%ROWCOUNT	返回被DML语句修改的数据行数。
SQL%ISOPEN	对于隐式游标，总是返回FALSE，因为Oracle自动打开和关闭隐式游标。

我们可以在PL/SQL中引用这些属性来控制DML流程。

上机练习：

 

 

1.1.5 DML语句的返回信息

在使用INSERT、UPDATE和DELETE语句时，可以使用RETURNING子句来获得DML的执行结果，请看下面的例子：

上机练习：

练习1、建立一个叫test1的存储过程，然后执行

 

执行后输入结果：

 

1.1.6 DML和异常

在一个程序块中，如果DML语句发生了异常（这几乎是PL/SQL最常见的异常），程序是否回滚这一块程序中所有DML语句，取决于你的异常处理。

让我们来考虑下面的一个过程：

   CREATE OR REPLACE PROCEDURE empty_library (

       pre_empty_count OUT PLS_INTEGER)

    IS

    BEGIN

       -- The tabCount function returns the number

       -- of rows in the specified table

       pre_empty_count := tabcount ('books');

 

       DELETE FROM books;

       RAISE NO_DATA_FOUND;

    END;

注意上面的代码，我们在唤起“NO_DATA_FOUND”异常前，返回了一个整数值表示books表中的纪录数。现在，让我们使用一段匿名块来引用这个过程，从而演示一下效果：

DECLARE

       table_count   NUMBER := -1;

    BEGIN

       INSERT INTO books VALUES (...);

       empty_library (table_count);

    EXCEPTION

       WHEN OTHERS THEN

          DBMS_OUTPUT.put_line (tabcount ('books'));

          DBMS_OUTPUT.put_line (table_count);

    END;

输出为：

0

-1

注意：我们发现虽然有异常发生，但是books表里面的纪录还是被删除了。Oracle不会自动地进行回滚。但是，奇妙的是，变量table_count却回到了初始值。

1. 所以为了避免混乱，我们一般都要自己写回滚块。下面是要注意的几个事项：

2. 如果你的程序块是一个独立自治的事务，那么当异常发生时，你就必须执行一个commit或rollback。

3. 你可以使用savepoin来控制回滚的范围。但要注意savepoint的设置范围。

如果异常被传递到最外层（比如说，它跑到“非控制”区域），那么，对于大部分PL/SQL的执行环境（比如SQL*Plus），一个不可预见或没经检查的回滚将会自动执行，造成了一些错误变更。

1.1.7 DML和纪录

从Oracle 9i Release2开始，你就可以在insert和update语句中使用纪录了。比如下面的代码例子：

    CREATE OR REPLACE PROCEDURE set_book_info (book_in IN books%ROWTYPE)

    IS

    BEGIN

       INSERT INTO books VALUES book_in;

    EXCEPTION

       WHEN DUP_VAL_ON_INDEX

       THEN

          UPDATE books SET ROW = book_in

           WHERE isbn = book_in.isbn;

    END;

这样带来的好处有两个：

1、 简化代码，用时髦的话说，就是把代码提高到了“记录层”。

2、 增加了程序的健壮性，使用“%ROWTYPE”，当表结构发生变化时，需要维护的地方就少一些。

1.1.7.1 基于“记录”（record）的INSERT语句

你能够声明一个基于某张表的记录变量，然后直接把这个变量的值插入到表里去。

参看以下代码：

 

需要注意的是，在INSERT语句中的VALUES子句后面是不能跟“（）”的，如果你按照一般的规范写了以下的代码：

INSERT INTO dept VALUES(my-dept);

那么Oracle就会报以下错误：

 

此外，记录变量的结构必须和表结构完全一致，这也要我们在使用时多加小心。

1.1.7.2 基于“记录”（record）的UPDATE语句

With Oracle9i Database Release 2, you can also perform updates of an entire row with a record. The following example inserts a row into the books table with a %ROWTYPE record. Notice that I use a new keyword, ROW, to indicate that I am updating the entire row with a record:

同插入语句一样，你也可以使用记录变量对整条记录进行更新。下面的这个例子就是对DEPT表中的一条记录进行更新，其中使用了一个关键字：ROW，表示对整一条记录进行更新：

 

下面有两点关于利用记录变量更新数据表的限制：

1、 你必须使用ROW语法来更新整个记录，不能仅仅更新某些字段组。

2、 在更新语句中不能使用子查询。

1.1.7.3 记录变量与RETURNING子句结合使用

如果DML语句影响了一条记录，你可以把这条记录的数值返回给一个记录或者组合变量。如下代码所示：

 

在RETURNING子句中，我们还必须把每个列名写出来，因为Oracle还没有支持“*”的写法。

1.1.7.4 使用记录变量插入或更新的限制

使用记录变量来插入或更新，需要注意以下几点：

在SET子句的左边，你必须使用ROW关键字。而且不能有其他的SET子句 ，也就是说，你不能在更新一条记录的同时更新一个单独的列。

如果你插入一条记录时，不能只给某个单独列赋值。

你不能插入或更新一个含有内嵌记录的记录变量。

你不能在动态SQL的DML语句中使用记录变量。因为这需要Oracle支持PL/SQL记录类型和SQL语句的绑定，目前只支持SQL类型的绑定。

1.2 

Oracle的数据库管理系统提供了一套非常健壮的事务管理模型。你的应用决定哪一些代码是一整块的逻辑单元，需要提交或者回滚。一个事务在第一局SQL语句执行就隐式地开始了，直到COMMIT或ROLLBACK，或者在ROLLBACK TO SAVEPOINT之后继续。

Oracle提供了以下事务控制语句：

COMMIT

提交从上一个COMMIT或ROLLBACK开始的变更，并且释放所有的锁。

ROLLBACK

消除所有从上一个COMMIT或ROLLBACK开始的变更，并且释放所有的锁。

ROLLBACK TO SAVEPOINT

回滚到特定的保存点状态，并且释放所有代码范围内所形成的锁。

SAVEPOINT

创建一个“保存点”，让你可以进行部分回滚。

SET TRANSACTION

允许你开始一个只读或可读写的会话，创建一个隔离层，或者把当前会话分配给一个特定的回滚段。

LOCK TABLE

允许你在某种特定方式下所定整张数据库表。它会超越普通应用于表的行级别锁。

下面将会对以上命令做一个详细的讨论。

1.2.1 COMMIT语句

当你COMMIT，你就把你会话中对数据库的更改永久保留了下来。一旦你提交，你所做的更改就会对其他的用户或会话可见。COMMIT语句的语法是：

COMMIT [WORK] [COMMENT text];

WORK关键字是可选的，能够用来增加可读性。

COMMENT关键字确定了一个与当前事务联系的注释。这个注释必须用单引号括起来，而且长度不能超过50个字符。注释文本一般是由分布式事务使用，在二相提交框架中，可用来分析可疑事务。它与事务ID一起存储在数据字典中。

注意，COMMIT释放你会话中所有的行级别和表级别的锁，比如一条SELECT FOR UPDATE语句。它还会清楚所有从上一个COMMIT或ROLLBACK开始设定的保存点。

一旦你COMMIT了你的变更，你就不能使用ROLLBACK语句回滚它们。

下面的语句都是COMMIT命令的有效使用：

    COMMIT;

    COMMIT WORK;

    COMMIT COMMENT 'maintaining account balance'.

1.2.2 ROLLBACK语句

当你使用了ROLLBACK时，你就会把当前会话事务中对数据库所作的全部或一些变更回退到初始状态。为什么你要撤销变更？从SQL的角度看来，ROLLBACK给你一个消除错误的机会。比如在下面的语句中：

DELETE FROM orders;

ROLLBACK的语法如下：

ROLLBACK [WORK] [TO [SAVEPOINT ] savepoint_name];

There are two basic ways to use ROLLBACK: without parameters or with the TO clause to indicate a savepoint at which the ROLLBACK should stop. The parameterless ROLLBACK undoes all outstanding changes in your transaction.

有两种方式来使用ROLLBACK：一种是没有参数的，另一种是含有保存点参数的。

没有参数的ROLLBACK会回滚所有事务中未完成的改变。而ROLLBACK TO会回退到用户指定的保存点状态。

下面是ROLLBACK的正确用法：

    ROLLBACK;

    ROLLBACK WORK;

    ROLLBACK TO begin_cleanup;

注意，当你回滚到一个特定的保存点时，所有在这个保存点之后的保存点都将被撤销，但是这个特定的保存就不会。这意味着你的事务，如果发生了其他错误，可以从一个保存点回滚到相同的保存点。

一旦你执行了INSERT、UPDATE或DELETE语句，PL/SQL就会自动且隐式地生成一个保存点，如果你的DML语句发生错误，就会自动地回滚到这个隐式保存点。但是用这种方式，只能回滚最后一条DML语句。

1.2.3 SAVEPOINT

SAVEPOINT gives a name to, and marks a point in, the processing of your transaction. This marker allows you to ROLLBACK TO that point, erasing any changes and releasing any locks issued after that savepoint, but preserving any changes and locks that occurred before you marked the savepoint.

SAVEPOINT命令可以在你的事务过程中创建一个保存点。这样就允许你把事务回滚到这个点上，从而撤销所做的修改以及释放这个保存点之后的锁，但是保留了在这个保存点之前的所有变更和锁。

SAVEPOINT的语法是

SAVEPOINT savepoint_name;

在这里“savepoint_name”是一个不需要声明的标志符，所以保存点的名字必须符合Oracle的标志符命名标准。

要注意的是，savepoint（保存点）是不会被PL/SQL程序块所限制的。如果你在一个会话中使用了保存点，那么会话就会回退到这个点上，而不会考虑这个保存点是在哪个过程、函数或是匿名块执行的。因此，如果你在一个递归程序中使用了保存点，虽然在每一层递归中都回生成一个保存点，但是你只能回滚到最近标记的那个保存点上。

1.3 与游标

关于PL/SQL的特点，就在于它紧密地与数据库结合在一起，包括改变数据库内的数据以及从数据库中提取信息。本小节主要探讨了PL/SQL如何从Oracle数据库中查询数据，以及如何让查询出来的数据可用。

当你执行了一条PL/SQL程序中的SQL语句时，Oracle RDBMS就会分配一块私有的内存区域给这条语句，并且在系统全局区（SGA）中管理这条语句所提取的数据。这一块私有内存区含有这条SQL语句相关的信息以及被这条语句影响或检索的数据结果集。

PL/SQL提供了数种方式来操纵上述的私有内存区和其中的数据，这些方式包含以下：

1、隐式游标；

2、显式游标；

3、游标变量；

4、游标表达式；

5、动态SQL。

本节将会重点阐述隐式游标、显式游标以及与游标相关的一些特性。

1.3.1 游标的基础概念

简单来说，你可以把游标看成是指向数据表的一种指针，如下面的这个游标就与EMP表的全部列关联在一起：

CURSOR emp_cur IS SELECT * FROM EMP;

一旦申明了这样一个游标，你就可以打开它，如下：

OPEN emp_cur;

然后还可以从中取出记录：

FETCH emp_cur INTO emp_rec;

最后，你还要关闭这个游标：

CLOSE emp_cur;

当然，你所声明的游标也不必要和数据表中的整条记录关联起来，也可以仅仅和其中的某些列关联，如下所示：

CURSOR joke_feedback_cur

       IS

          SELECT J.name, R.laugh_volume, C.name

            FROM joke J, response R, comedian C

           WHERE J.joke_id = R.joke_id

             AND J.joker_id = C.joker_id;

在这里，上述游标实际上并不是作为一种指针与数据库中实际数据表相互作用，这个游标实际上是指向一个虚拟的数据表或视图，这个虚拟的数据表或视图是由游标后面的SQL语句所表示。如果上述SQL语句返回了20条记录，那么这个游标的功能就相当于一个指向这20条记录的指针。

1.3.1.1 典型查询操作

在PL/SQL中，查询主要遵循以下几个步骤（如果你使用显式游标的话，也可以自己制定各个步骤）：

1、解析

第一步就是处理一条SQL语句，并进行解析，以确认它是有效的，然后制定执行计划（关于执行计划的内容在《Oracle SQL 调优》的教材中详述）。

2、绑定

当你进行绑定，你就把你程序中的变量（称为宿主变量）与SQL语句中的占位符关联起来。PL/SQL引擎自己就会执行这些绑定。但是如果你想使用绑定变量的话，就必须显式地要求对一个变量值进行绑定。

3、打开游标

当你打开了一条游标，绑定变量就会决定SQL语句的结果集。指向当前记录的指针就会指向第一行。有时候你不必显式地打开一个游标，PL/SQL引擎会自动地执行这个步骤。

4、执行

在执行步骤中，SQL语句就会在SQL引擎中运行。

5、提取

如果你正在执行一个查询，那么FETCH命令就会检索并返回这个游标结果集的下一条记录。每一次提取，PL/SQL就会把指针向前移动一次。要注意的是，当你操作的是显式游标的话，如果结果集中已经没有记录，那么使用FETCH是不会做任何事情的（包括报错）。

6、关闭

这一步就关闭游标并且释放这个游标使用的所有内存。一旦关闭，这个游标就不再拥有结果集。

下图显示了查询数据中的几个步骤：

 

1.3.1.2 游标属性简介

Oracle为游标提供了6种属性，下面简单介绍其中常用的4种：

名称	描述
%FOUND	如果记录返回成功，则返回True，否则返回False
% NOTFOUND	如果记录返回不成功，则返回True，否则返回False
% ROWCOUNT	实时返回这一点上从游标提取的记录数
%ISOPEN	如果游标开启则返回True，否则返回False。

1、%FOUND属性

%FOUND属性反映了你最近对游标执行FETCH命令的状态。如果针对显式游标最近执行的FETCH命令返回了一条记录，则这个属性为True；如果没有记录返回，则为False。

如果游标还没有打开，一个对%FOUND属性的引用就会引发一个INVALID_CURSOR（无效游标）的异常。你可以获取每一个打开了的显式游标的%FOUND属性值。

在下面的范例中，是%FOUND属性最常用的方式，从游标中取记录，然后循环更新数据表，每一次循环开始时都要检查游标中是否还有记录，如果没有的话就跳出循环。在更新数据表之后，又再一次检查游标的%FOUND属性，如果返回为True，则显示接下来要更新的内容。

    OPEN caller_cur;

    LOOP

       FETCH caller_cur INTO caller_rec;

       EXIT WHEN NOT caller_cur%FOUND;

 

       UPDATE call

          SET caller_id = caller_rec.caller_id

        WHERE call_timestamp < SYSDATE;

       IF SQL%FOUND THEN

          DBMS_OUTPUT.PUT_LINE (

             'Calls updated for ' || caller_rec.caller_id);

       END IF;

    END LOOP;

    CLOSE caller_cur;

2、%NOTFOUND属性

%NOTFOUND属性与%FOUND刚好相反。如果显式游标不能返回下一条记录，则为True，否则为False。如果游标是因为别的错误不能返回记录，则一个异常就会被引发。

如果游标还没有被打开，则与%FOUND属性一样，发出一个INVALID_CURSOR的异常，你也可以检查每一个游标的%NOTFOUND属性。

和上面的程序相比，在跳出循环的语句那里，我更倾向于使用%NOTFOUND属性，这样程序更具有可读性，如下所示：

EXIT WHEN caller_cur%NOTFOUND;

3、%ROWCOUNT属性

%ROWCOUNT属性返回游标已经提取的记录数，到引用这个属性为止。当你刚打开游标时，它的%ROWCOUNT属性值为0。如果引用是一个没有打开的游标的%ROWCOUNT属性，则会报错：INVALID_CURSOR。当提取一条记录后，%ROWCOUNT属性值也会相应地加1。

使用%ROWCOUNT属性可以来确认有多少记录已经被提取。在处理了特定记录数之后，可以停止你的程序。

4、%ISOPEN

如果游标是打开的，那么%ISOPEN属性返回True，否则返回False。

1.3.2 隐式游标

每一次当你执行SQL DML语句时，Oracle PL/SQL就会声明并管理一个隐式游标。比如：SELECT INTO语句从数据库中直接返回数据给PL/SQL中的数据结构。这一类的游标就被称为隐式游标，Oracle会为你自动执行与之相关的许多操作，比如说定位一个游标，打开游标，提取数据等等。

一个隐式游标就是一个包含了以下特征的SELECT语句：

The SELECT statement appears in the executable section of your block; it is not defined in the declaration section, as explicit cursors are.

1、SELECT语句是放在你程序块中的执行段，而不是像显式游标那样在声明段定义。

2、查询语句包含了INTO子句。INTO子句是PL/SQL语言的一部分，主要用来把从数据库而来的数据传递给本地PL/SQL数据结构。

3、你不必打开、获取或关闭SELECT语句，所有这些操作，Oracle都自动地为你做了。

下面是一个普遍使用的隐式查询结构：

    SELECT column_list

      [BULK COLLECT] INTO PL/SQL variable list

      ...rest of SELECT statement...

如果你使用一个隐式游标，Oracle将会自动地为你执行打开、提取和关闭等动作，这些动作不属于你程序控制范围之内。但是，通过检查隐式游标的属性，你能够获取最近一条执行了的SQL语句的信息，这在后面将有详述。

1.3.2.1 隐式游标的范例

一个最简单的例子就是查找特定的字段或记录，如下所示：

例1：取EMP表中的雇员名

 

例2：取EMP表中的一条记录

 

除了上面两个例子，你还可以在隐式游标中返回更加复杂的数据结构，比如说对象和集合。

1.3.2.2 隐式游标的错误处理

SELECT语句的隐式游标就像一种黑盒子。你输入SQL语句到数据库中的SQL引擎，然后它返回一条信息。你不能获取里面的各个单独步骤地信息。但是你也会被Oracle自动触发的异常所纠缠。

如果一个查询不能返回符合你标准的记录，那么Oracle就会触发一个NO_DATA_FOUND的异常，这也是最常见的异常。

如果SELECT语句返回了多于一条的记录，Oracle也会触发TOO_MANY_ROWS异常。

当这些异常情景中的任何一个发生，当前程序块的执行就会被中止，程序控制就会交给异常处理模块。所以，任何时候你使用了隐式游标，安全起见，你都要把这些异常（至少上述两种）包含到错误处理模块中来。

在下面这个例子中，我根据雇员的编号来查询雇员名成，并且捕捉可能出现的异常，如果数据完整性被破坏，程序就会触发异常，到上一层的处理程序中去：

 

一般来说，你应该在使用了隐式游标的情况下，一定要在异常处理块中至少捕捉NO_DATA_FOUND 和 TOO_MANY_ROWS两种异常。因为，我们不知道数据结构在未来是否有变化，为了保证程序健壮性，所以必须对有可能出现的异常进行捕捉。此外，对于不同的错误可进行不同的处理。NO_DATA_FOUND 和 TOO_MANY_ROWS两种错误就应有不同的处理方式，可根据不同的应用要求进行处理。

1.3.2.3 隐式游标属性

隐式游标的属性和普通游标属性类似（详见前述），需要注意的是，如果你的会话没有执行SQL DML语句，那么所有的游标属性就会返回空值（NULL）。但是，如果隐式游标有数值，那么这些数值是与最近执行的SQL DML语句所关联，而与你程序的模块或是逻辑控制无关。

下面的这个例子能让我们更加清楚隐式游标的这种特性：

create or replace procedure remove_from_emp(i_empNo in emp.empNo%TYPE) 

is

begin

  Delete from emp t 

     where t.empno = i_empNo;  

end remove_from_emp;

 

create or replace procedure show_emp_count is

   l_count integer;

begin

   select count(*) into l_count from emp;   

   remove_from_emp(0000);

   --没有编号为0000的员工

   dbms_output.put_line(SQL%ROWCOUNT);   

end show_emp_count;

我们会发现，不论有EMP表中有多少条记录，程序的返回值都是0。这是因为根本就没有编号为“0000”的员工记录。我们把程序改一下，把SELECT语句修改成UPDATE语句。如下所示：

create or replace procedure upd_emp_rec is

   begin

   UPDATE emp set deptno=0 where deptno=20;

   dbms_output.put_line(SQL%ROWCOUNT);  

   ROLLBACK;

end show_emp_count;

 

输出结果为5，表示有5行记录被更新。

1.3.3 显式游标

一个显式游标就是在你代码的声明段中，显式地定义一个SELECT语句，并且给这个SELECT语句赋予名称。显然，对于其他的DML语句，如：UPDATE、DELETE和INSERT语句，是不存在游标定义的。

使用显式游标，你可以具体地掌控PL/SQL中检索数据的各个步骤。你可以决定什么时候打开游标，提取数据以及关闭游标。你还可以决定要提取多少条记录，对提取出来的游标进行再加工等等。显式游标的控制粒度对于开发人员来说是一个非常有价值的工具。

让我们来看看一个例子。下面是一个函数，它可以计算在某个部门内某个员工的工资排名：

  1  create or replace function Find_Sal_Rank(i_empNo in number) return number is

  2    Result number(3);

  3    l_emprec emp%rowtype;

  4    Cursor rank_cur is

  5      select * from emp t where t.empno = i_empNo;

  6  begin

  7    open rank_cur;

  8    fetch rank_cur into l_emprec;

  9    if rank_cur%notfound then

 10      dbms_output.put_line('emp not found!');

 11      return -1;

 12    end if;

 13    select rank(l_emprec.sal) within group(order by sal desc)

 14      into Result

 15        from emp t where t.deptno=l_emprec.deptno;

 16    close rank_cur;

 17    return(Result);

 18  end Find_Sal_Rank;

输出结果为：

 

有关游标的操作与行数的对应关系如下所示：

行号	游标操作说明
4-5	声明一个游标。
7	打开游标。
8	从游标中提取记录。
9	检查游标属性，进行异常处理。
13-15	根据游标提取出来的数据，计算该名员工的工资排名。
16	关闭游标。

1.3.3.1 声明游标

声明游标的语法如下所示：

CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]

       [ RETURN return_specification ]

       IS SELECT_statement

           [FOR UPDATE [OF [column list]];

在这里，cursor_name使游标的名称，return_specification规定了游标返回的数据类型（返回项可选），而SELECT_statement是任意一个有效的SQL SELECT语句。你可以给游标传递参数，这在后面的章节中有详述。一旦你声明了一个游标，你就可以打开或关闭它。

下面是一些简单的例子：

1、无参数的游标，检索公司的ID

CURSOR company_cur IS  

       SELECT company_id FROM company;

2、有参数的游标，根据输入参数检取公司ID

    CURSOR name_cur (company_id_in IN NUMBER)

    IS

       SELECT name FROM company

        WHERE company_id = company_id_in;

3、带返回子句的游标

CURSOR emp_cur RETURN employee%ROWTYPE

    IS

       SELECT * FROM employee

        WHERE department_id = 10;

1.3.3.2 命名游标

一个显式游标的名字可以为30个字符长的符合PL/SQL标志符规范的字符串。我们要注意，一个游标的名字其实并不是一个变量，它只是一个未声明的标志符，用来引用或指向一个查询结果集。你不能给游标赋值，或者在表达式中使用游标。你只能对游标使用OPEN，FETCH和CLOSE操作，以及引用它特有的一些游标属性。

1.3.3.3 打开显式游标

使用游标的第一步就是在声明段定义一个游标。接下来的一步就是打开这个游标。OPEN语句的语法如下：

OPEN cursor_name [ ( argument [, argument ...] ) ];

不论你是何时取出第一条记录，在Oracle RDBMS的读取一致性模型中，所获取的记录在打开游标时就已经确定下来了。换句话说，从你打开游标到你关闭游标的这段时间中，所有提取的数据将会忽略游标打开之后的插入、更新和删除操作。

更进一步，如果你在游标声明中的SELECT语句中使用了“FOR UPDATE”关键字，那么所被选中的记录都会在游标的打开期间被锁定。

如果你试图打开一个已经打开了的游标，则你会得到一个如下的错误：

ORA-06511: PL/SQL: cursor already open 

在你试图打开一个游标之前，你可以使用游标的%ISOPEN属性来检查该游标是否被打开。如下代码所示：

    IF NOT company_cur%ISOPEN

    THEN

       OPEN company_cur;

    END IF;

在下一节“显式游标的属性”中，将会阐述不同的属性，并且如何在程序中使用它们。

注意：如果你使用了OPEN FOR LOOP形式的循环结构，你就不用专门去打开游标了，Oracle的PL/SQL引擎自动完成。

1.3.3.4 从显式游标中提取数据

在程序中，游标其实可以看成是一张“虚拟”的表，里面包含了SELECT子句检索出来的数据。几乎在所有的程序中，定义并且打开一个游标的目的就是为了对游标进行数据提取，操纵等工作。

PL/SQL provides a FETCH statement for this action.。The general syntax for a FETCH is:

PL/SQL提供了FETCH语句来提取数据。FETCH语句的一般语法形式如下：

FETCH cursor_name INTO record_or_variable_list;

在这里cursor_name是指提取数据游标的名字，record_or_variable_list是Oracle数据结构（列表）——它们用来存储提取出来的数据。你可以把提取数据赋予一个或多个变量。

1. 显式游标的范例

以下的几个例子示范了几种数据提取的方式：

提取数据到一个记录中：

    DECLARE

       CURSOR company_cur is SELECT ...;

       company_rec company_cur%ROWTYPE;

    BEGIN

       OPEN company_cur;

       FETCH company_cur INTO company_rec;

提取数据到一个变量中去：

    FETCH new_balance_cur INTO new_balance_dollars;

注意：在编程中，应尽量使用记录（Record）类型的变量，与提取数据到多个变量的方式相比，使用记录变量能够让代码更简洁些。

2. 超量提取

当你把游标中所有的数据都提取出来之后，继续提取，这个时候，PL/SQL不会抛出任何异常。它仅仅是什么都不做。因为已经没有数据可供提取，所以FETCH语句不再会改变INTO子句里面变量的值，也不会把这些变量设为空值（NULL）。

对于超量提取的情况，我们推荐使用测试%FOUND 或者 %NOTFOUND 属性的方法。

1.3.3.5 在显式游标中使用列匿名

在某些情况下，我们为了程序的可读性，在SELECT语句中使用了列匿名，如下所示：

SELECT company_name, SUM (inv_amt) total_sales

      FROM company C, invoice I

     WHERE C.company_id = I.company_id

       AND I.invoice_date BETWEEN '01-JAN-2001' AND '31-DEC-2001';

以上语句的total_sales就是一个列匿名，它表示了inv_amt列的总和。

在显示游标中使用列匿名与对于正常的列是一样的，如下范例所示：

在EMP表根据员工的部门编号分组，显示每个部门的员工工资总和。

 

注意：引用游标的属性%NOTFOUND一般是在FETCH语句之后，因为只有在FETCH之后，游标的属性才会发生变化。

1.3.3.6 关闭显式游标

当使用游标时，一定要确认在使用完毕之后关闭了游标以节省系统开销。以下是CLOSE命令的语法：

CLOSE cursor_name;

这里cursor_name就是要关闭的游标的名字。

下面有一些有关关闭显式游标特别需要考虑的地方：

如果你在程序中打开了一个游标，当你使用完毕之后一定要确定将其关闭。不然的话，你可能会引起一个内存泄露。严格来说，一个游标（有点像一种另类的数据结构）应该在其使用范围结束后能自动关闭或被销毁。事实上，在许多情况下PL/SQL在过程、函数或匿名块调用结束时，检查并暗中关闭了任何打开了的游标。但是，在某些情况下，由于效率的关系，PL/SQL并不会立即检查和关闭在PL/SQL块中已经打开的游标。再如，REF CURSOR类型就是被设计用来不能隐式自动关闭的。

OTN提供了一些关于PL/SQL关闭游标的细节分析，大家可以上网学习，这里就不赘述了。

如果你在包级别中声明了一个游标，然后在某个程序或块中打开了它，那么这个游标就会一直保持打开状态，直到你明确地关闭了它又或者直到你的回话结束。因此，对于任何包级别的游标，你一定要在使用完了它们之后及时地显式地关闭，如下所示：

    BEGIN

       OPEN my_package.my_cursor;

 

       ... Do stuff with the cursor

 

       CLOSE my_package.my_cursor;

    EXCEPTION

       WHEN OTHERS

       THEN

          CLOSE my_package.my_cursor;

    END;

如果你打开了一个SELECT FOR UPDATE查询，则你一定要在使用结束之后立即关闭它，因为这类查询会造成行级别锁。

在关闭游标之前，你可以通过检查游标的%ISOPEN属性避免发生异常，如下所示：

    IF company_cur%ISOPEN

    THEN

       CLOSE company_cur;

    END IF;

如果你遗漏了太多的游标处于打开状态，你就可能超出数据库所设置的初始化参数——OPEN_CURSOR。如果这种情况发生，你就会遇到一个“可怕”的错误信息：

ORA-01000: maximum open cursors exceeded 

如果你遇到这种情况，你就的检查你的基于包级别的游标使用，从而确定它们在使用结束之后已经关闭。

1.3.3.7 显式游标的属性

Oracle给游标赋予了四种属性，你可以通过如下形式来引用它们：

cursor%attribute

下表是游标属性的返回值：

名字	描述
cursor%FOUND	如果记录被成功取出，则返回TRUE。
cursor%NOTFOUND	如果记录没有被成功取出，则返回TRUE。
cursor%ROWCOUNT	返回在这个时刻从游标中提取的记录数目
cursor%ISOPEN	如果游标已经打开就返回TRUE

以下是一些关于使用游标属性的建议:

1、如果你试图在游标打开或关闭之前使用%FOUND，%NOTFOUND或 %ROWCOUNT属性，Oracle会抛出一个INVALID_CURSOR error (ORA-01001)异常；

2、如果在第一次提取（FETCH）之后，属性将会返回：%FOUND = FALSE，%NOTFOUND = TRUE和%ROWCOUNT = 0；

3、如果你使用了批量集合（BULK COLLECT），则从%ROWCOUNT属性中取出的值就可能不是1或0，事实上，它返回提取到相应集合变量中的记录数。要想了解更多的内容，可参看下一小节批量集合。

下面我们看一下在一个程序包中如何使用游标的属性：

--定义了一个包，其中包含一个游标

create or replace package empinfo is  

     --定义游标，取出部门编号为20的员工信息

     CURSOR emp_cur is 

        select t.empno,t.ename,t.job,t.hiredate from emp t

          where t.deptno=20;       

end empinfo;

下面在SQL*Plus中的匿名块中使用这个游标：

 

1.3.3.8 游标的参数

PL/SQL允许你把参数传递给游标。使用原理和过程以及函数差不多。使用带参数游标的好处是什么呢？有以下两点：

1、增加了游标的重用性。

一般来说，在游标定义中的Where子句中，如果把条件写死，则非常不利于代码的重用，增加了工作量。使用参数的话，可以灵活在每次打开游标时定义不同的条件。

2、避免了作用域的问题。

如果你使用游标参数，那么游标所返回的结果就不再绑定于某个程序或块的变量。如果你的程序含有“内嵌块”（一般都有），则你可以在高级别的块里定义参数游标，然后在子程序块中使用，在使用时就可以与当前块的变量相结合。

你可以按你所需设定相应数目的游标参数。当你打开游标（OPEN），你必须为每一个参数指定一个变量，除了有默认值的后缀参数。

什么时候你应该给游标加上参数呢？对于过程和函数来说，有以下一些建议。如果我要在不同的地方使用相同的游标，而且每个地方都要有特定的WHERE条件，则就应该使用一个参数游标。

下面我们就来看一看游标参数的例子：

范例

在一个过程里面定义一个带参数的游标，如下所示：

 

执行结果：

 

 

事实上，我们在使用带参数的游标时，可以使用多种打开方式：

如：

OPEN emp_cur(20);

OPEN emp_cur(20+n);--这里n是一个number型变量

OPEN emp_cur(nvl(i_deptno,0));

……