一、什么是游标
1、游标的概念
可以将游标(Cursor)形象地看成一个变动的光标。它实际上是一个指针,它在一段oracle存放数据查询结果集或者数据操作结果集的内存中,这个指针可以指向结果集中的任何一条记录。这样就可以得到它所指向的数据了,但初始时它指向首记录。这种模型很像编程中的数组。
2、游标的类型
oracle游标分为静态游标和REF游标两类。其中,静态游标就像一个数据快照,打开游标后的结果集是对数据库数据的一个备份,数据不随着对表执行DML操作后而改变。从这个特性来说,结果集是静态的。
静态游标包含两中类型:
(1)、显式游标
是指在使用之前必须有着明确的游标声明和定义,这样的游标定义会关联数据查询语句,通常会返回一行或者多行。关闭游标后就不能再对其进行操作了。显示游标由用户自定义来完成,一切由用户控制。
(2)、隐式游标
和显式游标不同,它被PL/SQL自动管理,也被称为SQL游标,由oracle自动管理。该游标用户无法控制,但能得到它的属性信息。
二、显式游标
1、游标语法
CURSOR cursor_name [ parameter_name datatype, ... ] IS select_statement;
2、游标的使用步骤
(1)声明游标(并关联一个查询)
DECLARE CURSOR cursor_name IS select_statement;
(2)打开游标(游标一旦打开,结果集都是静态的)
OPEN cursor_name;
(3)读取游标
读取数据要利用FETCH语句完成,它可以把游标指向位置的记录放入到PL/SQL声明的变量中。正常情况下,FETCH语句要和循环语句一起使用,这样指针会不断前进,直到某个条件不符合要求而退出。
FETCH cursor_name INTO Record_name;
(4)关闭游标
CLOSE cursor_name;
3、游标中的LOOP语句
declare
cursor info_cursor is select id,position,strain from CONSTANTINFO where id <11;
--使用%type可以取得表中的字段类型,使用%rowtype可以申明基于某个表的行类型
cur_id CONSTANTINFO.id%TYPE;
cur_pos CONSTANTINFO.position%TYPE;
cur_str CONSTANTINFO.strain%TYPE;
begin
--打开游标
open info_cursor;
--循环获取记录集
loop
fetch info_cursor into cur_id,cur_pos,cur_str;
--使用游标属性:记录集到尾,没有记录退出循环
exit when info_cursor%notfound;
--输出游标获取的记录集
dbms_output.put_line(cur_id ||'-----'|| cur_pos ||'-----'|| cur_str);
end loop;
--关闭游标
close info_cursor;
--游标中使用异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到数据!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('数据过多!');
END;
4、使用BULK COLLECT和FOR语句的游标——实现批量读取数据
使用FETCH INTO语句提取数据,这种方式是单条数据提取,在数据量很大的情况下执行效率并不理想。而FETCH...BULK COLLECT INTO语句可以批量提取数据,在数据量大的情况下它的执行效率相当快。
declare
cursor info_cursor is select id,position,strain from CONSTANTINFO where id <11;
--定义和表STRACONSTANTINFO行对象一致的集合类型constantinfo_rd,用于存放批量得到的数据
type constantinfo_tab is table of CONSTANTINFO%rowtype;
constantinfo_rd constantinfo_tab;
begin
--打开游标
open info_cursor;
--循环获取记录集
loop
fetch info_cursor bulk collect into constantinfo_rd limit 2;
for i in 1..constantinfo_rd.count loop
dbms_output.put_line('数据ID:'||constantinfo_rd(i).id ||' 位置:'||
constantinfo_rd(i).position ||' 应变:'|| constantinfo_rd(i).strain);
end loop;
--记录集到尾,没有记录退出循环
exit when info_cursor%notfound;
end loop;
--关闭游标
close info_cursor;
--游标中使用异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到数据!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('数据过多!');
END;
5、使用CURSOR FOR LOOP——使用更加简单
游标有很多机会都是迭代结果集,在PL/SQL这个过程中可以使用更简单的方式实现,CURSOR FOR LOOP不需要特别的声明变量,它可以提出行对象类型的数据。这种方式在隐式游标中使用更显方便。
declare
--声明游标关联查询
CURSOR info_cursor IS select id,position,strain from CONSTANTINFO where id<11;
begin
--把游标返回数据放到curcfl中,该类型是个%ROWTYPE类型
FOR curcfl IN info_cursor
--迭代输出数据
LOOP
DBMS_OUTPUT.PUT_LINE('数据ID:'||curcfl.id ||' 位置:'|| curcfl.position ||' 应变:'|| curcfl.strain);
END LOOP;
--游标中使用异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到数据!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('数据过多!');
end;
6、显式游标的属性
(1)%ISOPEN:用于判断游标是否打开,打开则返回TRUE,否则FALSE;
(2)%FOUND:用来检测行数据是否有效。有效则返回TRUE,否则反之;
(3)%NOTFOUND:与%FOUND正好相反,没有提取出数据返回TRUE,否则反之;
(4)%ROWCOUNT:累计到当前为止使用FETCH提取数据的行数。较为常用,使用它可以知道当前已经返回多少行数据了
7、带参数的游标
在使用显式游标时是可以指定参数的,指定的参数包括参数的顺序和参数的类型。参数可以传递给游标在查询中使用。这样就方便了用户根据不同的查询条件进行查询。也方便了游标在存储过程中的使用。
DECLARE
--使用%type可以取得表中的字段类型,并且声明了变量赋了值;使用%rowtype可以申明基于某个表的行类型
cur_id CONSTANTINFO.id%TYPE := '100';
cur_pos CONSTANTINFO.position%TYPE := '10000';
cur_rcd CONSTANTINFO%ROWTYPE;
--声明游标,包括两个参数和类型
CURSOR info_cursor(iid VARCHAR2,pos NUMBER) IS SELECT * FROM constantinfo WHERE id < iid AND position < pos;
BEGIN
--打开游标,并且给参数赋予变量
OPEN info_cursor(cur_id,cur_pos);
--循环获取记录集
LOOP
FETCH info_cursor INTO cur_rcd;
--使用游标属性:记录集到尾,没有记录退出循环
EXIT WHEN info_cursor%NOTFOUND;
--输出游标获取的记录集
DBMS_OUTPUT.PUT_LINE(cur_rcd.id ||'-----'|| cur_rcd.position ||'-----'|| cur_rcd.strain);
END LOOP;
--关闭游标
CLOSE info_cursor;
--游标中使用异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到数据!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('数据过多!');
END;
三、隐式游标
1、隐式游标的特点
隐式游标没有像显式游标那样声明一个游标名称,而是直接使用了SQL名称,这是隐式游标的默认名称,可直接使用。
(1)隐式游标由PL/SQL自动管理;
(2)隐式游标的默认名称是SQL;
(3)SELECT语句或DML操作都会长产生隐式游标的;
(4)隐式游标的属性始终是最新执行的SQL语句的。
declare
--使用%type可以取得表中的字段类型.
cur_id CONSTANTINFO.id%TYPE;
cur_pos CONSTANTINFO.position%TYPE;
cur_str CONSTANTINFO.strain%TYPE;
begin
select id,position,strain from CONSTANTINFO WHERE id < 11;
loop
--使用隐式游标属性,记录集到尾,没有记录退出循环
IF SQL%FOUND;
--输出游标获取的记录集
dbms_output.put_line(cur_id ||'-----'|| cur_pos ||'-----'|| cur_str);
END IF;
EXIT WHEN SQL%NOTFOUND;
end loop;
--游标中使用异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到数据!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('数据过多!');
END;
2、游标中使用异常处理
上述的所有SQL中都添加了异常处理的代码。当数据库中的记录发生变化时使用SELECT INTO语句返回的结果有可能不是单条记录,这时候会出现脚本中断并报错的现象,因此需要使用异常处理:
--游标中使用异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到数据!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('数据过多!');
3、隐式游标的属性
(1)%ISOPEN:用于判断游标是否打开。HOWEVER,在隐式游标中该属性永远返回FALSE,它由ORACLE自己控制;
(2)%FOUND:当DML操作对数据有影响的时候返回TRUE,SELECT INTO语句返回数据时返回TRUE,否则反之;
(3)%NOTFOUND:与%FOUND正好相反;
(4)%ROWCOUNT:该属性反映出DML操作对数据影响的行数。