一、Cursor的分类
二、各类Cursor举例
----
静态游标
-
显式游标
set
serveroutput
on
declare
cursor
emp_sor(emp_deptno
in
number
)
is
select
*
from
emp
where
deptno=emp_deptno ;
emp_i emp%
rowtype
;
begin
dbms_output.put_line(
'Getting emp from deptno 10'
);
open
emp_sor(
10
);
loop
fetch
emp_sor
into
emp_i;
exit
when
emp_sor%
notfound
;
dbms_output.put_line(
'Employee id '
||emp_i.empno||
' is:'
);
dbms_output.put_line(emp_i.ename);
end
loop
;
close
emp_sor;
end
;
/
----
静态游标
-
隐式游标
-1.DML
begin
update
emp
set
ename=ename ;
--where 1=2;
dbms_output.put_line(
'update '
||
sql
%
rowcount
||
' records'
);
end
;
/
----
静态游标
-
隐式游标
-2.loop for
begin
for
r_sor
in
(
select
empno,ename
from
emp)
loop
dbms_output.put_line(r_sor.empno ||
' : '
|| r_sor.ename);
end
loop
;
end
;
/
----
静态游标
-
隐式游标
-3.select into
declare
v
varchar2
(
20
);
begin
select
ename
into
v
from
emp
where
rownum =
1
;
dbms_output.put_line(v);
dbms_output.put_line(
sql
%
rowcount
);
end
;
/
----
动态游标
-
弱类型
Declare
type
rc
is
ref
cursor
;
cursor
c
is
select
*
from
dual;
l_cursor rc;
begin
if
(to_char(
sysdate
,
'dd'
) =
30
)
then
open
l_cursor
for
'select * from emp'
;-- ref cursor with dynamic sql
elsif
(to_char(
sysdate
,
'dd'
) =
29
)
then
open
l_cursor
for
select
*
from
dept;-- ref cursor with static sql
else
open
l_cursor
for
select
*
from
dual;-- with ref cursor with static sql
end
if
;
open
c;-- the "normal" static cursor
end
;
/
----
动态游标
-
强类型
declare
type
emp_job
is
record
(empno
number
,
ename
varchar2
(
20
),
job
varchar2
(
30
)
);
type
emp_refcur
is
ref
cursor
return
emp_job;
--
声明
REF CURSOR
emp_sor emp_refcur;
emp_i emp_job;
begin
open
emp_sor
for
select
empno,ename,job
from
emp
where
rownum <
10
order
by
1
;
loop
fetch
emp_sor
into
emp_i;
exit
when
emp_sor%
notfound
;
dbms_output.put_line(emp_i.ename ||
'''s job is :'
);
dbms_output.put_line(emp_i.job);
end
loop
;
close
emp_sor;
end
;
/
普通cursor与REF cursor的区别：
1）静态cursor不能返回到客户端，只有PL/SQL才能利用它。ref cursor能够被返回到客户端，这就是从Oracle的存储过程返回结果集的方式。
2）静态cursor可以是全局的，而ref cursor则不是。
3）ref cursor可以从子例程传递到子例程，而cursor则不能。为了共享静态cursor，必须在包说明或包体中把它定义为全局cursor。
因为使用全局变量通常不是一种很好的编码习惯，因此可以用ref cursor来共享PL/SQL中的cursor，无需混合使用全局变量。
4）使用静态cursor，通过静态SQL（但不用ref cursor），比使用ref cursor效率高，而ref cursor的使用仅限于以下几种情况：
1.把结果集返回给客户端；
2.在多个子例程之间共享cursor（实际上与上面提到的一点非常类似）；
3.没有其他有效的方法来达到你的目标时，则使用ref cursor，正如必须用动态SQL时那样
----
动态游标
-sys_refcursor
DECLARE
TYPE
mytable
IS
TABLE
OF
emp%
ROWTYPE
;
l_data mytable;
l_refc
sys_refcursor
;
BEGIN
OPEN
l_refc
FOR
SELECT
empno,ename,job,mgr,hiredate,sal,comm,deptno
FROM
emp;
FETCH
l_refc
BULK
COLLECT
INTO
l_data;
CLOSE
l_refc;
FOR
i
IN
1
.. l_data.COUNT
LOOP
DBMS_OUTPUT.put_line ( l_data(i).ename ||
' was hired since '
|| l_data (i).hiredate );
END
LOOP
;
END
;
非强类型的Ref cursor 和sys_refcursor的区别：
A REF CURSOR that does not specify the return type such as SYS_REFCURSOR. Meaning the SYS_REFCURSOR can be opened for a dynamic SQL query, where as simple REF CURSOR can not be opened for a query dynamically built at execution time.
三、游标属性
/*************************************************************
游标属性：
%FOUND
：变量最后从游标中获取记录的时候，在结果集中找到了记录。
%NOTFOUND
：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
%ROWCOUNT
：当前时刻已经从游标中获取的记录数量。
%ISOPEN
：是否打开。
**************************************************************/
----
静态游标
-
游标属性
Declare
Cursor
emp_sor
is
Select
*
from
emp
where
rownum<
6
order
by
1
;
emp_i emp%
rowtype
;
num
number
:=
1
;
Begin
Open
emp_sor;
Fetch
emp_sor
into
emp_i;
Loop
If
emp_sor%
found
then
Dbms_output.put_line(
'Looping over record '
||num||
' of '
|| emp_sor%
rowcount
);
Fetch
emp_sor
into
emp_i;
num := num +
1
;
Elsif
emp_sor%
notfound
then
Exit
;
---exit loop, not IF
End
if
;
End
loop
;
If
emp_sor%
isopen
then
Close
emp_sor;
End
if
;
End
;
/