sessioncursor的种类和用法
oracle 中的session cursor分为三种:显式游标(explicit cursor) 隐式游标(implicit cursor) 参考游标(ref cursor)
创新互联客户idc服务中心,提供西信服务器托管、成都服务器、成都主机托管、成都双线服务器等业务的一站式服务。通过各地的服务中心,我们向成都用户提供优质廉价的产品以及开放、透明、稳定、高性价比的服务,资深网络工程师在机房提供7*24小时标准级技术保障。
一、 隐式游标(implicit cursor)
无处不在,oracle中最常见的游标,只要执行一个SQL或者pl/sql,Oracle就会自动创建一个隐式游标,它的生命周期(open,bind,parse,execute,fetch,close)由SQL引擎或者pl/sql引擎自动控制,所有也意味着我们失去了对隐式游标的控制权。
不过还是可以通过隐式游标的下列几个属性来了解与之相关的sql信息
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
SQL%ROWCOUNT
1、 SQL%FOUND表示一条语句被执行后,受其影响而改变的记录数是否大于等于1,故通常适用于DML语句,或者select into. SQL执行前这个值为null,成功改变记录数后变为true,否则为false
declare
empno_no number(4) :=7934;
begin
delete from emp where empno=empno_no;
if sql%found then
insert into emp(empno,ename,mgr) values(8000,'JACK',7902);
end if;
commit;
end;
/
这个例子即利用 SQL%FOUND,当删除一条记录后,才插入一条记录
特别注意select into的情况,仅当返回结果只有一条记录,Oracle才不会报错,如果返回结果0,则报错no data found,如果返回结果大于1条,则报错too many rows
declare
emp1 varchar2(14);
vc_message varchar2(4000);
begin
select empno into emp1 from emp where empno = 7900;
exception
when no_data_found then
dbms_output.put_line('no data found!');
return;
when too_many_rows then
dbms_output.put_line('too many rows!');
return;
when others then
vc_message := 'E'||'_'||sqlcode||'_'||sqlerrm;
dbms_output.put_line(vc_message);
return;end;
2、SQL%NOTFOUND
与 SQL%FOUND 相反,受其影响而改变的记录数是否为0,故通常适用于DML语句,或者select into. SQL执行前这个值为null,没有改变记录数为true,改变了就是false
3、SQL%ISOPEN
隐式游标中,这个值永远为false
4、SQL%ROWCOUNT
SQL%FOUND表示一条语句被执行后,受其影响而改变的记录数,与SQL%FOUND SQL%NOTFOUND一样,这个值适用于update,delete,insert 等DML操作和select into,注意在select into中,返回值多于1时,Oracle会报错 ,这是这个值返回的是1,而不是select了多少条记录。当前 SQL%ROWCOUNT只代表上一个被执行的sql,如果有新的SQL执行,这个值会被覆盖,所以如果需要用到某条SQL执行后产生的这个值,可以在执行完后将该值放入一个变量中
二、显式游标(explicit cursor)
用于pl/sql的代码中(比如package,函数,存储过程),其生命周期中的open,fetch,close可以由我们在代码中显示控制,显式游标的四个常见属性 CURSORNAME%FOUND,CURSORNAME%NOTFOUND,CURSORNAME$ISOPEN,CURSORNAME%ROWCOUNT
1、CURSORNAME%FOUND
指定的显式游标(即cursorname的游标名字)是否有一条记录被fetch,当一个游标被open,还没有fetch,该值为null,fetch后该值为true,fetch 完所有记录后该值还是为true,这时再fetch一次,Oracle不会报错,而是该值变为false。如果该游标还没有被open就试图使用这个值,则会报invalid_cursor
declare
cursor c1 is select ename,sal from emp where rownum<10;
my_name emp.ename%type;
my_sal emp.sal%type;
begin
open c1;
loop
fetch c1 into my_name,my_sal;
if c1%found then
dbms_output.put_line('name='||my_name||',salary='||my_sal);
else
exit;
end if;
end loop;
close c1;
end;
2、 CURSORNAME$ISOPEN
指定的游标是否被open ,通常用于标准的exception处理流程,用于 close那些由于exception而导致显示游标open了却没有被正常关闭的时候
exception
when others then
if c1%isopen=ture then
close c1;
end if;
return;
end;
3、 CURSORNAME%NOTFOUND
与1相反, 指定的显式游标(即cursorname的游标名字)是否有一条记录被fetch,当一个游标被open,还没有fetch,该值为null,fetch后该值为false,fetch 完所有记录后该值还是为false,这时再fetch一次,Oracle不会报错,而是该值变为true。如果该游标还没有被open就试图使用这个值,则会报invalid_cursor
declare
cursor c1 is select ename,sal from emp where rownum<10;
my_name emp.ename%type;
my_sal emp.sal%type;
begin
open c1;
loop
fetch c1 into my_name,my_sal;
if c1%notfound then
exit;
else
dbms_output.put_line('name='||my_name||',salary='||my_sal);
end if;
end loop;
close c1;
end;
4、 CURSORNAME%ROWCOUNT 表示该游标一共被fetch了多少行记录
declare
cursor c1 is select ename from emp where rownum<10;
my_name emp.ename%type;
begin
open c1;
loop
fetch c1 into my_name;
if c1%found then
dbms_output.put_line(c1%rowcount||':='||my_name );
else
exit;
end if ;
end loop;
close c1;
end;
对显示游标四个属性的总结
1、当一个显示游标没有被open时,使用cursorname%found,cursorname%notfound,cursorname%rowcount,oracle会报错invalid_cursor
2、首次fetch时结果集返回一个空值,则cursorname%found 为false ,cursorname%notfound 为true,cursorname%rowcount 为0
最后看一个显式游标在pl/sql中的标准用法
create or replace procedure p_demo_explicit_cursor_std
2 is
3 cursor c1 is select * from emp where rownum<10;
4 emp_rec emp%rowtype;
5 begin
6 open c1;
7 fetch c1 into emp_rec;
8 while (c1%found) loop
9 dbms_output.put_line('name='||emp_rec.ename||',salary='||emp_rec.sal);
10 fetch c1 into emp_rec;
11 end loop;
12 close c1;
13 exception
14 when others then
15 --o_parm:='E'||sqlcode||sqlerrm;
16 rollback;
17 --写日志
18 RETURN;
19 end p_demo_explicit_cursor_std;
注意以下两点
显示游标的标准用法,先open再fetch,然后一个while循环逐条处理数据,最后close
在while内部循环处理完一条记录后,一定要执行fetch以跳到下一条记录,不然会死循环
三、参考游标(ref_cursor)
和显式游标一样,参考游标也是用于pl/sql的代码中(比如package,函数,存储过程),其生命周期中的open,fetch,close可以由我们在代码中显示控制,参考游标的四个常见属性 CURSORNAME%FOUND,CURSORNAME%NOTFOUND,CURSORNAME$ISOPEN,CURSORNAME%ROWCOUNT,属性也是跟显式游标一样
参考游标是这三种游标中灵活性最好的一种游标,主要体现在以下几点
1、定义方式灵活,可以有多种定义方式
第一种方式
type typ_cur_emp is ref cursor return emp%rowtype;
cur_emp typ_cur_emp;
第二种方式
type typ_result is record(ename emp.ename%type,sal emp.sal%type);
type typ_cur_strong is ref cursor return typ_result;
cur_emp typ_cur_stong;
第三种方式
type typ_cur_weak is ref cursor;
cur_emp typ_cur_weak;
第四种方式
cur_emp sys_refcursor;
2、open方式灵活,不跟具体SQL绑定,可以随时open,每次open可以对应不同的sql
declare
type typ_cur_emp is ref cursor return emp%rowtype;
cur_emp typ_cur_emp;
procedure process_emp_cv(emp_cv in typ_cur_emp) is
person emp%rowtype;
begin
dbms_output.put_line('----');
loop
fetch emp_cv into person;
exit when emp_cv%notfound;
dbms_output.put_line('name='||person.ename);
end loop;
end;
begin
open cur_emp for select * from emp where rownum<11;
process_emp_cv(cur_emp);
close cur_emp;
open cur_emp for select * from emp where ename like 'C%';
process_emp_cv(cur_emp);
close cur_emp;
end;
3、参考游标可以做为存储过程的输入参数和函数的输出参数
4、参考游标的额外用法
除了一次fetch一条记录,还可以一次性fetch多条记录
可以和显示游标嵌套使用
分享标题:sessioncursor的种类和用法
标题链接:http://scyanting.com/article/jeooii.html