游标详解

在Oracle中提供了两种游标类型。它们是：

Ø  静态游标

Ø  REF游标

静态游标：静态游标是在编译时知道其select语句的游标。

静态游标又分为两种类型。它们是：

l  隐式游标

l  显式游标

REF游标：游标变量是一种引用类型。引用类型类似于指针。它可以在程序运行时指定不同的存储位置。为了使用引用类型，必须声明变量，并且必须分配存储空间。除这两种类型之外，还有一种称为动态游标（主要用于DBMS_SQL程序包）的游标类型。

隐式游标

PL/SQL为所有SQL数据操纵语句（包括返回一行的查询）隐式声明游标。对于返回多行的查询，应该使用显式游标分别访问这些行。

隐式游标的属性可以用于访问有关最新执行的SQL语句的信息。隐式游标的属性包括：

Ø  %NOTFOUND

Ø  %FOUND

Ø  %ROWCOUNT

Ø  %ISOPEN

下面逐个对这些属性进行详细说明。

%NOTFOUND

如果DML语句不返回任何行，则%NOTFOUND属性值为true。否则，其属性值为false。让我们通过下列示例了解此属性的用法。

示例6：

在从order_detail表中删除一行之后，此示例使用%NOTFOUND属性检查sql cursor。对于所有DML操作，Oracle将隐式打开一个游标，并在SQL操作完成后，它将关闭游标。

BEGIN

DELETE FROM order_detail WHERE orderno = 'o201';

If sql%NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE('未找到值');

ELSE

DBMS_OUTPUT.PUT_LINE('找到值并删除之');

END IF;

END;

/

上述程序的输出结果如下所示：

未找到值

PL/SQL过程已成功完成。

%FOUND

在逻辑上，%FOUND属性与%NOTFOUND属性是相反的。如果DML语句影响一行或多行，则%FOUND属性值为true，否则其属性值为false。

%ROWCOUNT

%ROWCOUNT属性统计DML语句返回的行数。如果DML语句不影响任何行，则%ROWCOUNT将返回零。以下示例使用%ROWCOUNT属性检查游标是否获取到任何行。

示例7：

此示例检查order_master表中是否有对应于订单号“o001”的数据。

DECLARE

order_no order_master.orderno%TYPE;

BEGIN

SELECT orderno INTO order_no FROM order_master

WHERE orderno = 'o001';

IF sql%ROWCOUNT > 0 THEN

DBMS_OUTPUT.PUT_LINE('从表中选择行');

ELSE

DBMS_OUTPUT.PUT_LINE('从表中未选择行');

END IF;

END;

/

此程序的输出结果如下所示：

从表中选择行

PL/SQL过程已成功完成。

如果select语句未能返回行，则引发预定义的异常“NO_DATA_FOUND”。于是，常规执行停止，控制权转移到异常处理部分。在引发异常时，将不使用属性SQL%NOTFOUND来查明DML语句是否已经影响了行。

%ISOPEN

在执行SQL游标的关联SQL语句之后，Oracle自动关闭该SQL游标。因此，%ISOPEN的值始终为false。

显式游标

显式游标是由用户显式声明的游标。根据在游标中定义的查询，查询返回的行集可以包含零行或多行。这些行称为活动集。游标将指向活动集中的当前行。

在声明游标之后，可以使用下列语句控制游标。

Ø  Open

Ø  Fetch

Ø  Close

“open”语句执行查询，识别活动集并将游标指针置于第一行。语法如下：

OPEN <Cursor_name>;其中Cursor_name是游标名称

“fetch”语句检索当前行并使游标指针前进到下一行以获取其余行。fetch语句的语法如下：

FETCH <cursor_name> INTO < var_name>;

其中cursor_name是游标名称，var_name是变量名。

在处理活动集中的最后一行之后，借助“close”语句禁用游标。语法如下：

CLOSE <cursor_name>;

其中cursor_name是游标名称。

让我们通过创建一个示例来了解显式游标。

示例8：

此示例说明如何使用显式游标这一概念更新多行。此示例为订单号“o001”选择order_detail表中出现的不同项代码。然后，更新对应于在游标中选定的项代码的所有项的费率。

DECLARE

icode order_detail.itemcode%TYPE;

CURSOR a IS SELECT itemcode FROM order_detail

WHERE orderno = 'o001';

BEGIN

OPEN a;

LOOP

FETCH a INTO icode;

UPDATE itemfile SET itemrate = 22.05 WHERE

itemcode = icode;

EXIT WHEN a%NOTFOUND;

END LOOP;

DBMS_OUTPUT.PUT_LINE ('项费率已更新');

CLOSE a;

END;

/

上述代码的输出结果如下所示：

项费率已更新

PL/SQL过程已成功完成。

游标属性

Ø  %NOTFOUND

Ø  %FOUND

Ø  %ROWCOUNT

Ø  %ISOPEN

%NOTFOUND

在打开游标之后，将使用fetch语句从活动集中获取行，一次获取一行。属性%NOTFOUND指示fetch语句是否从活动集返回行。如果fetch语句未能返回行，则%NOTFOUND的值为true。否则，其值为false。以下示例使用%NOTFOUND属性在“fetch”未能返回行时退出循环。让我们通过一个示例来了解如何使用%NOTFOUND属性。

%NOTFOUND

在打开游标之后，将使用fetch语句从活动集中获取行，一次获取一行。属性%NOTFOUND指示fetch语句是否从活动集返回行。如果fetch语句未能返回行，则%NOTFOUND的值为true。否则，其值为false。以下示例使用%NOTFOUND属性在“fetch”未能返回行时退出循环。让我们通过一个示例来了解如何使用%NOTFOUND属性。

示例9：

试考虑下列方案。公司未提供特定项，鉴于这一原因，请求该特定项的所有订单的状态均保持为“P”，意味着在order_master表中暂挂。此后，公司发现该特定项无法得到供应，因为该项已不再生产。因此，公司决定将订单的状态更改为“D”，意味着已交付给请求该项的所有订单。此示例说明如何通过编程实现该目的。

完整代码如下。

DECLARE

order_no order_detail.orderno % TYPE;

CURSOR a IS SELECT orderno FROM order_detail WHERE itemcode ='i205';

BEGIN

OPEN a;

LOOP

Fetch a INTO order_no;

UPDATE order_master SET ostatus = 'D'

WHERE orderno = order_no;

EXIT WHEN a%NOTFOUND;

END LOOP;

CLOSE a;

END;

/

%FOUND

在逻辑上，%FOUND属性与%NOTFOUND是相反的。如果最后一条“fetch”语句成功返回行，则其属性值为true。如果最后一条“fetch”语句因没有更多可用行而失败时，则其属性值为false。

%ROWCOUNT

%ROWCOUNT属性用于返回已获取的行数。在第一次获取之前，%ROWCOUNT为零。当fetch语句返回一行时，则该数加1。下列示例说明如何使用以上讨论的属性。

示例10：

此示例打开一个名为“test”的游标以便从order_detail表获取行。使用fetch语句逐一获取行值，在%NOTFOUND属性返回true时退出程序。最后，使用%ROWCOUNT属性显示受影响的行数。

DECLARE

CURSOR test IS SELECT *FROM order_detail

WHERE orderno = 'o001';

myorderorder_detail%ROWTYPE;

BEGIN

OPEN test;

LOOP

FETCH test INTO myorder;

EXIT WHEN test%NOTFOUND;

END LOOP;

DBMS_OUTPUT.PUT_LINE('从表中获取的行数为：'|| test%ROWCOUNT);

CLOSE test;

END;

/

上述程序的样例输出结果如下所示：

从表中获取的行数为：3

PL/SQL过程已成功完成。

%ISOPEN

如果游标已经打开，则属性%ISOPEN的值为true。否则，其属性值为false。下列示例说明如何使用%ISOPEN属性打开游标。

示例11：

DECLARE

CURSOR mycur IS SELECT * FROM order_master;

BEGIN

IF NOT mycur%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('游标尚未被打开 ');

END IF;

OPEN mycur;

IF mycur%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('游标已打开 ');

END IF;

CLOSE mycur;END;

/

上述示例使用%ISOPEN属性两次。一次是在打开游标之前。由于没有打开游标，因此属性将返回false，Oracle在SQL提示符下显示消息“游标尚未被打开”。再一次使用同一属性测试在打开游标之后游标是否已打开。此时，它将返回true，并显示消息“游标已打开”。