ORACLE的游标不是一个指针，而是一块内存区域,SQL语句在这块内存中执行。OPEN一个游标也就是申请一块内存。
常规上我们把游标分为三类：隐式(implicit cursor)的，显示(explicit cursor)的，与动态游标(Ref cursor)。
游标经历：声明(declare), 打开（open）, 从游标中获取记录(Fetch into),关闭(close)。简单的过程，完美的人生。
一、隐式游标，于世无争。
任何给定的PL/SQL块都会OPEN一个隐式游标，除非已经为他定义了一个显示游标。
ORACLE自为执行declare ,open, Fetch, close的过程。
隐式游标用于Insert,Delete,Update 和select into .(注意不是SELECT，SELECT只能用显示游标,除非用于for loop语句)
Ex:
1.
declare
begin
update anran set name= 'guojj';
dbms_output.put_line( 'update '|| sql%rowcount || ' records');
end;
begin
update anran set name= 'guojj';
dbms_output.put_line( 'update '|| sql%rowcount || ' records');
end;
2. (只能取得一行记录)
declare
l_empName anran. name%type;
begin
select name
into l_empName
from anran;
dbms_output.put_line(l_empName);
end;
l_empName anran. name%type;
begin
select name
into l_empName
from anran;
dbms_output.put_line(l_empName);
end;
3.(
for loop)
declare
begin
for me in ( select id, name from anran)
loop
dbms_output.put_line(me.id || ' : ' || me. name);
end loop;
end;
begin
for me in ( select id, name from anran)
loop
dbms_output.put_line(me.id || ' : ' || me. name);
end loop;
end;
二．显式游标，只为
SLELECT
生。
声明方式：
CURSOR 游标名 ( 参数 ) [返回值类型] IS Select 语句
Declare，open, Fetch, close，四个过程缺一不可，当然你可以不FETCH，那就不知道你要干什么了
Ex:
Declare
-- declare
Cursor students is
Select * from anran where rownum<6 order by 1;
stu anran%rowtype;
Begin
--open
Open students;
--fetch
Fetch students into stu;
Loop
If students%found then
Dbms_output.put_line( 'it is ' || students%rowcount);
Fetch students into stu;
Elsif students%notfound then
Exit;
End if;
End loop;
If students%isopen then
--close
Close students;
End if;
End;
-- declare
Cursor students is
Select * from anran where rownum<6 order by 1;
stu anran%rowtype;
Begin
--open
Open students;
--fetch
Fetch students into stu;
Loop
If students%found then
Dbms_output.put_line( 'it is ' || students%rowcount);
Fetch students into stu;
Elsif students%notfound then
Exit;
End if;
End loop;
If students%isopen then
--close
Close students;
End if;
End;
三．
REF CURSOR游标，强中自有强中手。
动态游标，在运行的时候才能确定游标使用的查询。
定义方式：TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]
cursor_name ref_cursor_name.
类比C++语言：
定义一个int类量有两种方式: 1. int a
2.int *p; p=new int;//运行时分配内存
ex:
Declare
type ref_cursor is ref cursor;
students ref_cursor;
-- declare
stu anran%rowtype;
Begin
--open
Open students for Select * from anran where rownum<6 order by 1;
--fetch
Fetch students into stu;
Loop
If students%found then
Dbms_output.put_line( 'it is ' || students%rowcount);
Fetch students into stu;
Elsif students%notfound then
Exit;
End if;
End loop;
If students%isopen then
--close
Close students;
End if;
End;
type ref_cursor is ref cursor;
students ref_cursor;
-- declare
stu anran%rowtype;
Begin
--open
Open students for Select * from anran where rownum<6 order by 1;
--fetch
Fetch students into stu;
Loop
If students%found then
Dbms_output.put_line( 'it is ' || students%rowcount);
Fetch students into stu;
Elsif students%notfound then
Exit;
End if;
End loop;
If students%isopen then
--close
Close students;
End if;
End;
谢谢。
本文转自 anranran 51CTO博客，原文链接:http://blog.51cto.com/guojuanjun/317112
