在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,并显示消息“游标已打开”。