概念
游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。
类型
Cursor类型包含三种: 隐式Cursor,显式Cursor和Ref Cursor(动态Cursor)。
一、隐式游标
1).对于Select …INTO…语句,一次只能从数据库中获取到一条数据,对于这种类型的DML Sql语句,就是隐式Cursor。例如:Select /Update / Insert/Delete操作。
2)作用:可以通过隐式Cusor的属性来了解操作的状态和结果,从而达到流程的控制。Cursor的属性包含:
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真,结束后为假
create or replace procedure jc_test2(in_sex in abc.sex%TYPE) is
str varchar2(3000);
str2 varchar2(3000);
begin
-- 隐形游标
update abc a set a.tname='New Name' where a.id = 1;
if SQL%Found then
dbms_output.put_line('update success');
else
dbms_output.put_line('update fail');
end if;
end jc_test2;
二、显式Cursor:
(1) 对于从数据库中提取多行数据,就需要使用显式Cursor。显式Cursor的属性包含:
游标的属性 返回值类型 意 义
%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真,否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真,否则为假
(2) 对于显式游标的运用分为四个步骤:
定义游标---Cursor [Cursor Name] IS;
打开游标---Open [Cursor Name];
操作数据---Fetch [Cursor name]
关闭游标---Close [Cursor Name],这个Step绝对不可以遗漏。
(3)以下是常见显式Cursor用法。
-- 显性游标
-- 打开游标
// declare
cursor mycursor2 is
select * from abc;
abcInfo mycursor2%rowtype;
//代码块
open mycursor2;
loop
-- 遍历游标
fetch mycursor2 into abcInfo;
exit when mycursor2%notfound;
dbms_output.put_line(abcInfo.Id||' '||abcInfo.Tname);
end loop;
-- 关闭游标
if mycursor2%isopen then
close mycursor2;
end if;
三、动态游标(Refer Cursor)
1) 与隐式Cursor,显式Cursor的区别:Ref Cursor是可以通过在运行期间传递参数来获取数据结果集。而另外两种Cursor,是静态的,在编译期间就决定数据结果集。
2) Ref cursor的使用:
Type [Cursor type name] is ref cursor
Define 动态的Sql语句
Open cursor
操作数据---Fetch [Cursor name]
Close Cursor
-- 动态游标
// 定义
type dynamic_cursor_type is ref cursor;
dynamic_cursor dynamic_cursor_type;
dy_abc_info abc%rowtype;
// 代码块
open dynamic_cursor for select * from abc;
loop
fetch dynamic_cursor into dy_abc_info;
exit when dynamic_cursor%notfound;
dbms_output.put_line('---- '||dy_abc_info.Id||' '||dy_abc_info.Tname);
end loop;
-- 关闭游标
if dynamic_cursor%isopen then
close dynamic_cursor;
end if;
附加 sys_refcursor是oracle9i以后系统定义的一个refcursor,主要用在过程中返回结果集,无需 对其关闭
CREATE OR REPLACE PROCEDURE SP_OTHERS_REPORT_21(afacility_id in integer,
dateStart in Date,
o_cur OUT SYS_REFCURSOR,
o_cur2 OUT SYS_REFCURSOR,
o_cur3 OUT SYS_REFCURSOR) aS
.................