转自 http://www.cnblogs.com/lovemoon714/archive/2012/02/29/2371544.html
1、概念
游标是指向SQL处理的内存区的句柄或指针。当使用一个PL/SQL块来执行DML语句或只返回一行结果的SELECT语句时,系统将自动创建一个隐式游标。如果SQL语句返回多个结果,就必须创建一个显示游标。
--游标的属性 --(1)cur_name%rowcount :指出处理的行数 -- (2) cur_name%found :处理了一行或多行返回TRUE否则FALSE 如 WHILE CUR%FOUND中 --(3)cur_name%notfound :如果没有处理行返回TRUE,否则FALSE 如 EXIT WHEN CUR%NOTFOUND --(4)cur_name%isopen :如果处理之后不关闭游标,则为TRUE,关闭后为FALSE。发生在隐式游标中时 -- 总是为FALSE;
2、隐式游标例程
1 declare 2 tname student.name%type; 3 tage student.age%type; 4 begin 5 select name,age into tname,tage from 6 student where id='S001'; 7 --如果返回多行,或零行,执行将报错。 8 dbms_output.put_line('姓名='||tname||' 年龄='||tage); 9 end;
3、显示游标
3.1、定义游标
-- 定义游标(1) -- CURSOR cursor_name IS select_statement; cursor c_stu is select * from student;
---------------------------------------------------------------------------------------
-- 定义系统游标(2) ,使用参照3 -- cursor_name SYS_REFCURSOR;系统游标 c_stu sys_refcursor;
---------------------------------------------------------------------------------------
--定义游标(3) --先在包中定义游标,及用于检索的结构体, --这里的结构体相当于 游标%rowtype create or replace package p_stu as type c_stu is ref cursor; type rc_stu is record ( name student.name%type, age student.age%type ); end; --使用包中的游标 declare c_student p_stu.c_stu; crw_stu p_stu.rc_stu; begin open c_student for select name,age from student; loop fetch c_student into crw_stu; exit when c_student%notfound; dbms_output.put_line('姓名='||crw_stu.name||' 年龄='||crw_stu.age); end loop; end;
3.2、一些使用游标的例子
--使用 FOR 循环遍历游标,不需要明确打开和关闭游标
declare --定义一个游标 cursor c_stu is select name,age from student; --定义一个游标变量 cw_stu c_stu%rowtype; begin for cw_stu in c_stu loop dbms_output.put_line('姓名='||cw_stu.name||' 年龄='||cw_stu.age); end loop; end;
--使用 fetch 必须明确打开和关闭游标
declare --定义一个游标 cursor c_stu is select name,age from student; --定义一个游标变量 cw_stu c_stu%rowtype; begin open c_stu; --明确打开游标 loop fetch c_stu into cw_stu; exit when c_stu%notfound; dbms_output.put_line('姓名='||cw_stu.name||' 年龄='||cw_stu.age); end loop; close c_stu; --明确关闭游标 end;
--使用 while fetch 遍历数据 %found属性
declare --定义一个游标 cursor c_stu is select name,age from student; --定义一个游标变量 cw_stu c_stu%rowtype; begin open c_stu; --明确打开游标 fetch c_stu into cw_stu; --填充第一行数据,以便WHILE条件检索 while c_stu%found loop dbms_output.put_line('姓名='||cw_stu.name||' 年龄='||cw_stu.age); fetch c_stu into cw_stu; --每次检索前都需要填充数据 end loop; close c_stu; --明确关闭游标 end;
3.3、在存储过程中返回游标
-- 注意:在declare块中使用存储过程返回的游标时: -- (1)不能定义系统游标变量,编译错误。如:cw_Stu C_Stu%rowtype; -- (2)可以使用结构体变量,但不能使用for循环如:for rw_stu in c_stu loop -- 将提示 c_stu '不是过程或尚未定义'。 -- (3)游标不可显示打开或关闭,如 open c_stu;表达式类型错误。
(1)返回系统游标
---- ***1、返回系统游标的过程 sys_cursor create or replace procedure pro_SysCur(CId in varchar2,Cur_Stu out sys_refcursor) as begin open Cur_Stu for select name,age from student where ClassId=CId; end;
-- 使用 -- 测试结果: -- (1)不能定义系统游标变量,编译错误。如:cw_Stu C_Stu%rowtype; -- (2)可以使用结构体变量,但不能使用for循环如:for rw_stu in c_stu loop -- 将提示 c_stu '不是过程或尚未定义'。 -- (3)游标不可显示打开或关闭,如 open c_stu;表达式类型错误。 declare C_Stu Sys_RefCursor; type rec_stu is record( tname student.name%type, tage student.age%type ); rw_stu rec_stu; begin pro_syscur('C001',c_stu); loop --也可以写成 fetch c_stu into rw_stu.tname,rw_stu.tage; --或直接定义表字段类型变量 fetch c_stu into rw_stu; exit when c_stu%notfound; dbms_output.put_line('姓名='||rw_stu.tname||' 年龄='||rw_stu.tage); end loop; end;
(1)返回自定义游标
--第一步,在包中定义游标,及用于遍历的结构体 create or replace package pack_stu as type c_pubsur is ref cursor; type re_stu is record( tname student.name%type, tage student.age%type ); num number; end;
--第二步,将存储过程的返回类型设为上面包中游标类型 create or replace procedure p_stu(cid in varchar2,c_s out pack_stu.c_pubsur) as begin open c_s for select name,age from student where classid=cid; end;
--第三步,使用游标,注意事项与系统游标一致。 declare c_stu pack_stu.c_pubsur; cw_stu pack_stu.re_stu; begin p_stu('C001',c_stu); loop fetch c_stu into cw_stu; exit when c_stu%notfound; dbms_output.put_line('姓名='||cw_stu.tname||' 年龄='||cw_stu.tage); end loop; end;