- ORACLE游標(biāo)概念講解
- 2012-03-08 13:50:04 我來說兩句
-
收藏
 我要投稿
-
1，什么是游標(biāo)？
①從表中檢索出結(jié)果集，從中每次指向一條記錄進(jìn)行交互的機(jī)制。
②關(guān)系數(shù)據(jù)庫中的操作是在完整的行集合上執(zhí)行的。
由SELECT 語句返回的行集合包括滿足該語句的WHERE 子句所列條件的所有行。由該語句返回完整的行集合叫做結(jié)果集。
應(yīng)用程序，尤其是互動(dòng)和在線應(yīng)用程序，把完整的結(jié)果集作為一個(gè)單元處理并不總是有效的。
這些應(yīng)用程序需要一種機(jī)制來一次處理一行或連續(xù)的幾行。而游標(biāo)是對(duì)提供這一機(jī)制的結(jié)果集的擴(kuò)展。
游標(biāo)是通過游標(biāo)庫來實(shí)現(xiàn)的。游標(biāo)庫是常常作為數(shù)據(jù)庫系統(tǒng)或數(shù)據(jù)訪問API 的一部分而得以實(shí)現(xiàn)的軟件，
用來管理從數(shù)據(jù)源返回的數(shù)據(jù)的屬性（結(jié)果集）。這些屬性包括并發(fā)管理、在結(jié)果集中的位置、返回的行數(shù)，
以及是否能夠在結(jié)果集中向前和/或向后移動(dòng)（可滾動(dòng)性）。
游標(biāo)跟蹤結(jié)果集中的位置，并允許對(duì)結(jié)果集逐行執(zhí)行多個(gè)操作，在這個(gè)過程中可能返回至原始表，也可能不返回至原始表。
換句話說，游標(biāo)從概念上講基于數(shù)據(jù)庫的表返回結(jié)果集。
由于它指示結(jié)果集中的當(dāng)前位置 ，就像計(jì)算機(jī)屏幕上的光標(biāo)指示當(dāng)前位置一樣，“游標(biāo)”由此得名。
2，游標(biāo)有什么作用？
①指定結(jié)果集中特定行的位置。
②基于當(dāng)前的結(jié)果集位置檢索一行或連續(xù)的幾行。
③在結(jié)果集的當(dāng)前位置修改行中的數(shù)據(jù)。
④對(duì)其他用戶所做的數(shù)據(jù)更改定義不同的敏感性級(jí)別。
⑤可以以編程的方式訪問數(shù)據(jù)庫。
3，為什么避免使用游標(biāo)？
①在創(chuàng)建游標(biāo)時(shí)，最需要考慮的事情是，“是否有辦法避免使用游標(biāo)？”
因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該改寫；
如果使用了游標(biāo)，就要盡量避免在游標(biāo)循環(huán)中再進(jìn)行表連接的操作。
4，Oracle游標(biāo)的類型？
①靜態(tài)游標(biāo)：結(jié)果集已經(jīng)確實(shí)（靜態(tài)定義）的游標(biāo)。分為隱式和顯示游標(biāo)。
⑴隱式游標(biāo)：所有DML語句為隱式游標(biāo)，通過隱式游標(biāo)屬性可以獲取SQL語句信息。
⑵顯示游標(biāo)：用戶顯示聲明的游標(biāo)，即指定結(jié)果集。當(dāng)查詢返回結(jié)果超過一行時(shí)，就需要一個(gè)顯式游標(biāo)。
②REF游標(biāo)：動(dòng)態(tài)關(guān)聯(lián)結(jié)果集的臨時(shí)對(duì)象。
5，Oracle游標(biāo)的狀態(tài)有哪些，怎么使用游標(biāo)屬性？
①游標(biāo)的狀態(tài)是通過屬性來表示。
%Found ：Fetch語句（獲取記錄）執(zhí)行情況True or False。
%NotFound : 最后一條記錄是否提取出True or False。
%ISOpen ： 游標(biāo)是否打開True or False。
%RowCount ：游標(biāo)當(dāng)前提取的行數(shù) 。
②使用游標(biāo)的屬性。
例子：/* conn scott/tiger */
Begin
Update emp Set SAL = SAL + 0.1 Where JOB = 'CLERK';
If SQL%Found Then
DBMS_OUTPUT.PUT_LINE('已經(jīng)更新！');
Else
DBMS_OUTPUT.PUT_LINE('更新失敗！');
End If；
End；
6，如何使用顯示游標(biāo),？如何遍歷循環(huán)游標(biāo)？
①使用顯示游標(biāo)
⑴聲明游標(biāo)：劃分存儲(chǔ)區(qū)域，注意此時(shí)并沒有執(zhí)行Select 語句。
CURSOR 游標(biāo)名( 參數(shù) 列表) [返回值類型] IS Select 語句；
⑵打開游標(biāo)：執(zhí)行Select 語句，獲得結(jié)果集存儲(chǔ)到游標(biāo)中，此時(shí)游標(biāo)指向結(jié)果集頭， 而不是第一條記錄。
Open 游標(biāo)名( 參數(shù) 列表);
⑶獲取記錄：移動(dòng)游標(biāo)取一條記錄
Fetch 游標(biāo)名InTo 臨時(shí)記錄或?qū)傩灶愋妥兞浚?br>
⑷關(guān)閉游標(biāo)：將游標(biāo)放入緩沖池中，沒有完全釋放資源。可重新打開。
Close 游標(biāo)名；
②遍歷循環(huán)游標(biāo)
⑴For 循環(huán)游標(biāo)
循環(huán)游標(biāo)隱式打開游標(biāo)，自動(dòng)滾動(dòng)獲取一條記錄，并自動(dòng)創(chuàng)建臨時(shí)記錄類型變量存儲(chǔ)記錄。處理完后自動(dòng)關(guān)閉游標(biāo)。
For 變量名 In 游標(biāo)名
Loop
數(shù)據(jù)處理語句；
End Loop；
⑵Loop循環(huán)游標(biāo)
。。。
Loop
Fatch 游標(biāo)名InTo 臨時(shí)記錄或?qū)傩灶愋妥兞浚?br>
Exit When 游標(biāo)名%NotFound；
End Loop；
。。。
例子1：
/* conn scott/tiger */
Declare
Cursor myCur is select empno,ename,sal from emp;
vna varchar2(10);
vno number(4);
vsal number(7,2);
Begin
open myCur;
fetch myCur into vno,vna,vsal;
dbms_output.put_line(vno||' '||vna||' '||vsal);
close myCur;
End;
/
例子2：使用loop遍歷游標(biāo)。
/* conn scott/tiger */
Declare
Cursor myCur is select ename,job,sal,empno from emp;
varE myCur%rowType;
Begin
if myCur%isopen = false then
open myCur;
dbms_output.put_line('Opening...');
end if;
loop
fetch myCur into varE;
exit when myCur%notfound;
dbms_output.put_line(myCur%rowCount||' '||vare.empno||' '||vare.ename||' '||vare.sal);
end loop;
if myCur%isopen then
Close myCur;
dbms_output.put_line('Closing...');
end if;
End;
/
例子3：使用For循環(huán)遍歷游標(biāo),
/* conn scott/tiger */
Declare
Cursor myCur is select * from emp;
Begin
for varA in myCur
loop
dbms_output.put_line(myCur%rowCount||' '||varA.empno||' '||varA.ename||' '||varA.sal);
end loop;
End;
/
7，怎樣更新和刪除顯示游標(biāo)中的記錄？
①UPDATE或DELETE語句中的WHERE CURRENT OF子串專門處理要執(zhí)行UPDATE或DELETE操作的表中取出的最近的數(shù)據(jù)。
要使用這個(gè)方法，在聲明游標(biāo)時(shí)必須使用FOR UPDATE子串，當(dāng)對(duì)話使用FOR UPDATE子串打開一個(gè)游標(biāo)時(shí)，
所有返回集中的數(shù)據(jù)行都將處于行級(jí)（ROW-LEVEL)獨(dú)占式鎖定，其他對(duì)象只能查詢這些數(shù)據(jù)行，
不能進(jìn)行UPDATE、DELETE或SELECT...FOR UPDATE操作。
在多表查詢中，使用OF子句來鎖定特定的表,如果忽略了OF子句，那么所有表中選擇的數(shù)據(jù)行都將被鎖定。
如果這些數(shù)據(jù)行已經(jīng)被其他會(huì)話鎖定，那么正常情況下ORACLE將等待，直到數(shù)據(jù)行解鎖。
②使用更新或刪除：
⑴聲明更新或刪除顯示游標(biāo)：
Cursor 游標(biāo)名IS SELECT 語句 For Update [ Of 更新列列名]；
Cursor 游標(biāo)名IS SELECT 語句 For Delete [ Of 更新列列名]；
⑵使用顯示游標(biāo)當(dāng)前記錄來更新或刪除：
Update 表名 SET 更新語句 Where Current Of 游標(biāo)名；
Delete From 表名 Where Current Of 游標(biāo)名；
例子1:更新顯示游標(biāo)記錄
/*conn scott/tiger*/
Declare
Cursor myCur is select job from emp for update;
vjob empa.job%type;
rsal empa.sal%type;
Begin
open myCur;
loop
fetch myCur into vjob;
exit when myCur%notFound;
case (vjob)
when 'ANALYST' then rsal := 0.1;
when 'CLERK' then rsal := 0.2;
when 'MANAGER' then rsal := 0.3;
else
rsal := 0.5;
end case;
update emp set sal = sal + rsal where current of myCur;
end loop;
End;
/
例子2：刪除顯示游標(biāo)記錄
/*conn scott/tiger
Crate table empa Select * from scott.emp;
*/
Declare
Cursor MyCursor Select JOB From empa For Update;
vSal emp.Sal%TYPE;
Begin
Loop
Fetch MyCursor InTo vSal;
Exit When MyCursor%NotFound;
If vSal < 800 Then
Delete From empa Where Cursor Of MyCursor;
End If;
End Loop;
End;/
8，什么是帶參數(shù)的顯示游標(biāo)？
①與過程和函數(shù)相似，可以將參數(shù)傳遞給游標(biāo)并在查詢中使用。
參數(shù)只定義數(shù)據(jù)類型，沒有大小(所有Oracle中的形參只定義數(shù)據(jù)類型，不指定大小)。
與過程不同的是，游標(biāo)只能接受傳遞的值，而不能返回值。
可以給參數(shù)設(shè)定一個(gè)缺省值，當(dāng)沒有參數(shù)值傳遞給游標(biāo)時(shí)，就使用缺省值。
游標(biāo)中定義的參數(shù)只是一個(gè)占位符，在別處引用該參數(shù)不一定可靠。
②使用帶參數(shù)的顯示游標(biāo)
⑴聲明帶參數(shù)的顯示游標(biāo)：
CURSOR 游標(biāo)名 [(parameter[,parameter],...)] IS Select語句；;
參數(shù)形式：1，參數(shù)名 數(shù)據(jù)類型
2，參數(shù)名 數(shù)據(jù)類型 DEFAULT 默認(rèn)值
例子：
/*conn scott/tiger
Crate table empa Select * from scott.emp;
*/
Declare
Cursor MyCursor(pSal Number Default 800) Select JOB From empa Where SAL > pSal ;
varA MyCursor%ROWTYPE;
Begin
Loop
Fetch MyCursor InTo varA;
Exit When MyCursor%NotFound;
DBMS_OUTPUT.PUT_LINE(MyCursor%RowCount||' '||varA.empno||' '||varA.ename||' '||varA.sal);
End Loop;
End;/
REF CURSOR
1，什么是REF游標(biāo) ？
動(dòng)態(tài)關(guān)聯(lián)結(jié)果集的臨時(shí)對(duì)象。即在運(yùn)行的時(shí)候動(dòng)態(tài)決定執(zhí)行查詢。
2，REF 游標(biāo) 有什么作用？
實(shí)現(xiàn)在程序間傳遞結(jié)果集的功能，利用REF CURSOR也可以實(shí)現(xiàn)BULK SQL，從而提高SQL性能。
3，靜態(tài)游標(biāo)和REF 游標(biāo)的區(qū)別是什么？
①靜態(tài)游標(biāo)是靜態(tài)定義，REF 游標(biāo)是動(dòng)態(tài)關(guān)聯(lián)；
②使用REF 游標(biāo)需REF 游標(biāo)變量。
③REF 游標(biāo)能做為參數(shù)進(jìn)行傳遞，而靜態(tài)游標(biāo)是不可能的。
4，什么是REF 游標(biāo)變量？
REF游標(biāo)變量是一種 引用REF游標(biāo)類型 的變量，指向動(dòng)態(tài)關(guān)聯(lián)的結(jié)果集。
5，怎么使用 REF游標(biāo) ？
①聲明REF 游標(biāo)類型，確定REF 游標(biāo)類型；
⑴強(qiáng)類型REF游標(biāo)：指定retrun type，REF 游標(biāo)變量的類型必須和return type一致。
語法：Type REF游標(biāo)名 IS Ref Cursor Return 結(jié)果集返回記錄類型；
⑵弱類型REF游標(biāo)：不指定return type，能和任何類型的CURSOR變量匹配，用于獲取任何結(jié)果集。
語法：Type REF游標(biāo)名 IS Ref Cursor；
②聲明Ref 游標(biāo)類型變量；
語法：變量名 已聲明Ref 游標(biāo)類型；
③打開REF游標(biāo)，關(guān)聯(lián)結(jié)果集 ；
語法：Open Ref 游標(biāo)類型變量 For 查詢語句返回結(jié)果集；
④獲取記錄，操作記錄；
語法：Fatch REF游標(biāo)名InTo 臨時(shí)記錄類型變量或?qū)傩灶愋妥兞苛斜恚?br>
⑤關(guān)閉游標(biāo)，完全釋放資源；
語法：Close REF游標(biāo)名；
例子：強(qiáng)類型REF游標(biāo)
/*conn scott/tiger*/
Declare
Type MyRefCurA IS REF CURSOR RETURN emp%RowType;
Type MyRefCurB IS REF CURSOR RETURN emp.ename%Type;
vRefCurA MyRefCurA;
vRefCurB MyRefCurB;
vTempA vRefCurA%RowType;
vTempB vRefCurB.ename%Type;
Begin
Open vRefCurA For Select * from emp Where SAL > 2000;
Loop
Fatch vRefCurA InTo vTempA;
Exit When vRefCurA%NotFound;
DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||' '|| vTempA.eno||' '||vTempA.ename ||' '||vTempA.sal)
End Loop;
Close vRefCurA;
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
Open vRefCurB For Select ename from emp Where SAL > 2000;
Loop
Fatch vRefCurB InTo vTempB;
Exit When vRefCurB%NotFound;
DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount||' '||vTempB)
End Loop;
Close vRefCurB;
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
Open vRefCurA For Select * from emp Where JOB = 'CLERK';
Loop
Fatch vRefCurA InTo vTempA;
Exit When vRefCurA%NotFound;
DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||' '|| vTempA.eno||' '||vTempA.ename ||' '||vTempA.sal)
End Loop;
Close vRefCurA;
End;
例子：弱類型REF游標(biāo)
/*conn scott/tiger*/
Declare
Type MyRefCur IS Ref Cursor;
vRefCur MyRefCur;
vtemp vRefCur%RowType;
Begin
Case(&n)
When 1 Then Open vRefCur For Select * from emp;
When 2 Then Open vRefCur For Select * from dept;
Else
Open vRefCur For Select eno, ename from emp Where JOB = 'CLERK';
End Case;
Close vRefCur;
End;
|
