Oracle Cursor详解与实例

文章由LinuxBoy分享于2019-03-30 03:03:14热评（155）

Oracle Cursor详解与实例

摘要：详细介绍Oracle数据库中关于游标的定义和使用。通过实例操作来深入了解cursor的用法和用处。

一：相关概念

1、concept

When Oracle Database executes aSQL statement , it stores the result set and processing information in anunnamed private SQL area . A pointer to this unnamed area , called a cursor ,let you retrieve the rows of the result set one at a time . Cursor attributesreturn information about the state of the cursor .

2、概念：

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。Cursor类型：静态游标——分为显式（explicit）游标和隐式（implicit）游标、REF游标——动态游标、是一种引用类型、类似于指针。

二：具体类型及使用

1、implicit cursor

1） explanation：Everytime you run either a SQL DML statement or a PL/SQLSELECTINTO statement, PL/SQLopens an implicit cursor. You can get information about this cursor from itsattributes, but you cannot control it. After the statement runs, the databasecloses the cursor; however, its attribute values remain available until anotherDML orSELECTINTO statement runs.

2） implicit cursor（隐式游标）由系统自动打开和关闭、当我们执行一个SQL DML时、系统会自动打开一个cursor、当执行完毕之后系统会关闭cursor、我们不能直接控制cursor、但是却可以通过implicit Cursor的属性来了解操作的状态和结果、从而达到流程的控制——Cursor的属性包括：

i、SQL%ROWCOUNT整形——代表DML语句成功执行的行数

ii、SQL%FOUND布尔型——值为true时代表插入、删除、更新或查询操作成功

iii、SQL%NOTFOUND布尔型——与上面相反

v、SQL%ISOPEN布尔型——DML执行过程中为真、否则为假

3）示例：

begin
update student set sname='chy' WHERE sno='1';
if sql%isopen then
dbms_output.put_line('cursor is opening !');
else
dbms_output.put_line('cursor is closed !');
end if;
if sql%found then
dbms_output.put_line('DML is successed !');
else
dbms_output.put_line('DML is failed !');
end if;
if sql%notfound then
dbms_output.put_line('DML is failed !');
else
dbms_output.put_line('DML is successed !');
end if;
dbms_output.put_line(sql%rowcount||' is the number of result !');
exception
when no_data_found then
dbms_output.put_line('Sorry No data');
when too_many_rows then
dbms_output.put_line('Too Many rows');
end;

2、explicit cursor

1）explanation：PL/SQLalso lets you declare explicit cursors. An explicit cursor has a name and isassociated with a query (SQLSELECT statement)—usually one that returns multiplerows. After declaring an explicit cursor, you must open it (with the OPENstatement), fetch rows one at a time from the result set (with the FETCHstatement), and close the cursor (with the CLOSE statement). After closing thecursor, you can neither fetch records from the result set nor see the cursorattribute values.

很直白的说明了显示游标的用处、以及用法。

2）explicit cursor的属性包含：

游标的属性返回值类型意义

%ROWCOUNT 整型获得FETCH语句返回的数据行数

%FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假

%NOTFOUND 布尔型与%FOUND属性返回值相反

%ISOPEN 布尔型游标已经打开时值为真，否则为假

3）对于explicit Cursor使用分四个步骤：

a 定义游标——Cursor [ Cursor Name[param_name, param_type]] IS select xxx from xxxwhere xxx;

b 打开游标——Open [ Cursor Name[varialbe_value] ] ;

c 操作游标——Fetch [ Cursor Name ];

d 关闭游标——Close [ Cursor Name ] ;

4）具体使用显示游标,遍历循环游标步骤：

a）使用显示游标

i、声明游标：划分存储区域，注意此时并没有执行Select语句。CURSOR游标名(参数列表) [返回值类型] IS Select 语句；

ii、打开游标：执行Select语句，获得结果集存储到游标中，此时游标指向结果集头，而不是第一条记录。open游标名(参数列表);

iii、获取记录��移动游标取一条记录 fetch 游标名 into 临时记录或属性类型变量；

v、关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。

close 游标名；

b）遍历循环游标

i、for循环游标

循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。

……

for 变量名 In 游标名

loop

数据处理语句；

end loop；

ii、loop循环游标

……

loop

fetch 游标名 into 临时记录或属性类型变量；

exit when 游标名%notfound；

end loop；

iii、while循环

……

open 游标名

fetch 游标名into临时记录或属性类型变量；

while 游标名%foundloop

-- do something

fetch 游标名into临时记录或属性类型变量；

end loop;

……

close 游标名

5）常见显式Cursor用法：

i、使用for循环来使用cursor：

declare
cursor cur is select * from t_user where age = 22;
userinfo t_user%rowtype;
begin
for userinfo in cur loop
exit when cur%notfound;
dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

ii、使用fetch来使用cursor： exp2

declare
cursor cur is select * from t_user where age = 22;
userinfo t_user%rowtype;
begin
open cur;
loop
exit when cur%notfound;
fetch cur into userinfo;
dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
close cur;
end;

推荐文章：

Oracle动态采样详解

Oracle Cursor详解与实例