Cursor的基本使用方法
?
??? 今天在用到Cursor的時(shí)候發(fā)現(xiàn)，有很多游標(biāo)相關(guān)的知識(shí)還是有欠缺，在網(wǎng)上搜了篇基礎(chǔ)講解的文，覺(jué)得還不錯(cuò)，自己整理了一下發(fā)上來(lái)。雖然很基礎(chǔ)，但是有一些內(nèi)容之前確實(shí)沒(méi)有很扎實(shí)得掌握，所以記下來(lái)也可以加深一下印象。
?
一、Cursor的分類(lèi)
?
??? 
?
?
二、各類(lèi)Cursor舉例
?
----
靜態(tài)游標(biāo)
-
顯式游標(biāo)
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
;
/
?
----
靜態(tài)游標(biāo)
-
隱式游標(biāo)
-1.DML
begin
?
update
emp
set
ename=ename ;
--where 1=2;
?dbms_output.put_line(
'update '
||
sql
%
rowcount
||
' records'
);
end
;
/
?
----
靜態(tài)游標(biāo)
-
隱式游標(biāo)
-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
;
/
?
----
靜態(tài)游標(biāo)
-
隱式游標(biāo)
-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
;
/
?
----
動(dòng)態(tài)游標(biāo)
-
弱類(lèi)型
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
;
/
?
----
動(dòng)態(tài)游標(biāo)
-
強(qiáng)類(lèi)型
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的區(qū)別：
?
1）靜態(tài)cursor不能返回到客戶(hù)端，只有PL/SQL才能利用它。ref cursor能夠被返回到客戶(hù)端，這就是從Oracle的存儲(chǔ)過(guò)程返回結(jié)果集的方式。
2）靜態(tài)cursor可以是全局的，而ref cursor則不是。
3）ref cursor可以從子例程傳遞到子例程，而cursor則不能。為了共享靜態(tài)cursor，必須在包說(shuō)明或包體中把它定義為全局cursor。
?? 因?yàn)槭褂萌肿兞客ǔ２皇且环N很好的編碼習(xí)慣，因此可以用ref cursor來(lái)共享PL/SQL中的cursor，無(wú)需混合使用全局變量。
4）使用靜態(tài)cursor，通過(guò)靜態(tài)SQL（但不用ref cursor），比使用ref cursor效率高，而ref cursor的使用僅限于以下幾種情況：
??? 1.把結(jié)果集返回給客戶(hù)端；
??? 2.在多個(gè)子例程之間共享cursor（實(shí)際上與上面提到的一點(diǎn)非常類(lèi)似）；
??? 3.沒(méi)有其他有效的方法來(lái)達(dá)到你的目標(biāo)時(shí)，則使用ref cursor，正如必須用動(dòng)態(tài)SQL時(shí)那樣
?
----
動(dòng)態(tài)游標(biāo)
-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
;
?
非強(qiáng)類(lèi)型的Ref cursor 和sys_refcursor的區(qū)別：
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.
?
?
三、游標(biāo)屬性
?
/*************************************************************
游標(biāo)屬性：
%FOUND
：變量最后從游標(biāo)中獲取記錄的時(shí)候，在結(jié)果集中找到了記錄。
%NOTFOUND
：變量最后從游標(biāo)中獲取記錄的時(shí)候，在結(jié)果集中沒(méi)有找到記錄。
%ROWCOUNT
：當(dāng)前時(shí)刻已經(jīng)從游標(biāo)中獲取的記錄數(shù)量。
%ISOPEN
：是否打開(kāi)。
**************************************************************/
?
----
靜態(tài)游標(biāo)
-
游標(biāo)屬性
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
;
/
?