遊標： 遊標(cursor)可以被看作指向結果集(a set of rows)中一行的指標(pointer)。在oracle資料庫中可以使用顯示或隱式兩種遊標。
隱式遊標：
在執行一個sql語句時,oracle伺服器將自動建立一個隱式遊標,這個遊標是記憶體中處理該語句的工作區域，其中儲存了執行SQL語句的結果.通過遊標的屬性可獲得sql語句執行的結果以及遊標狀態資訊。
遊標的主要屬性如下：
%found 布林屬性 如果sql語句至少影響一行則為true 否則為false
%notfound 布林屬性與%found相反。
%isopen 布林屬性遊標是否開啟 開啟為true 否則為false
%rowcount 數字屬性返回受sql語句影響的行數
怎麼來使用這些屬性呢?
則可以通過 “sql屬性名 “來檢視結果
//%rowcount 用來檢查受影響的行
SQL> declare
2 emp_row emp%rowtype;
3 begin
4 select * into emp_row from emp where empno=7369;
5 dbms_output.put_line(sql%rowcount);
6 end;
7 /
1 //返回的結果為1
//%found用來檢查是否影響到了行
SQL> begin
2 update emp set sal=2000 where empno=7369;
3 if sql%found then
4 dbms_output.put_line('更新記錄成功影響了'||sql%rowcount||'行');
5 else
6 dbms_output.put_line('未更新記錄');
7 end if;
8 end;
9 /
更新記錄成功 影響了1行
// %notfound
//%isopen
一以上兩個的使用方式參考 %found進行理解。
另外一種隱式遊標cursor for loop可用於處理sql語句的結果集
SQL> begin
2
3 for rec in (select * from emp) loop
4 dbms_output.put_line(rec.empno||'/'||rec.ename||'/'||rec.job||'/'||rec.mgr||'/'||rec.hiredate||'/'||rec.sal||'/'||rec.comm||'/'||rec.deptno);
5 end loop;
6 end;
7 /
顯示遊標
是在PL/SQL程式中使用包含select語句來宣告的遊標。如果需要處理從資料庫中檢索的一組記錄,則可以使用顯示遊標.使用顯示遊標處理資料需要四個步驟：宣告遊標,開啟遊標，檢索資料，關閉遊標。
1、 宣告遊標
宣告遊標就是通過定義遊標的名稱,遊標的特性來宣告遊標,以及開啟遊標後就可呼叫查詢語句,宣告的語法如下：
Cursor cursor_name[parameter[,parameter]….]
[return return_type] is select_statement;
Parameter作為遊標的輸入引數,它可以讓使用者在開啟遊標式,向遊標傳遞值；語法如下：
Parameter_name [in] datatype[{:=|default} expression]\
舉例：
declare
cursor emp_cursor (pno in number(4) default 7369)
is select * from emp
where empno=pno;
2、 開啟遊標
就是指執行宣告遊標時指定的查詢語句。開啟的方式只需使用open開啟語法：
Open cursor_name(引數);
如果沒有指定引數就採用預設值執行select語句
3、 檢索資料
檢索資料就是從檢索到的結果集中獲取資料儲存到變數中,以便變數進行處理。
使用fetch語句找出結果集中的單行,並從中提取單個值傳遞給主變數。
語法如下：
Fetch cursor_name into [variable_list[record_variable]]
變數用於儲存檢索的資料
4、 關閉遊標
close 遊標名稱
綜上所述綜合案例如下：
SQL> declare
2 cursor emp_cursor (pno in number default 7369) //宣告遊標
3 is select * from emp where empno=pno;
4
5 emp_row emp%rowtype; //宣告變數
6 begin
7 open emp_cursor(7934); //開啟遊標
8 fetch emp_cursor into emp_row; //檢索資料 結果為一行
9 dbms_output.put_line(emp_row.ename); //輸出檢索結果
10
11 close emp_cursor; //關閉遊標
12 end;
13 /
遊標for迴圈
依次讀取結果集中的行,當for迴圈開始時,遊標會自動開啟(不需要使用open方法開啟),每迴圈讀取一次,系統自動讀取當前資料(不需要使用fetch),當退出for迴圈時,遊標也會自動關閉(不需要使用close方法)。
SQL> declare
2 cursor emp_cursor (pno in number default 7369) //宣告遊標
3 is select * from emp where empno=pno;
4
5 begin
6 for emp_row in emp_cursor(7934) loop //for迴圈開始時 自動開啟遊標 並且自動獲取資料 自動關閉
7 dbms_output.put_line(emp_row.ename);
8 end loop;
9 end;
10 /
遊標變數
遊標變數也可以處理多行查詢結果集。
遊標變數的定義包括兩個步驟：
1、 定義cursor型別的指標
語法：
Type ref_cursor_name is ref cursor[return return_type]
舉例：
Type var_cursor_name is ref cursor;
2、 定義ref cursor型別的變數
v_rc var_cursor_name;
綜合寫法如下：
Type var_cursor_name is ref cursor;
v_rc var_cursor_name;
上面的綜合宣告的遊標變數 稱為弱的ref cursor型別,因為它沒有指明遊標返回的結果,因此它可以指向任何一個具有多列的select查詢結果.
相對於上面還有一種稱為:強ref cursor型別.
宣告方式如下：
Type varcursorName is ref cursor return emp%rowtype; //指明瞭返回的結果
Vcn varcursorName; //宣告一個強的ref cursor型別的變數
使用遊標變數與遊標使用方式一樣,也需要宣告,開啟,檢索，關閉遊標變數。
綜合案例如下：
SQL> declare
2
3 type emp_cname is ref cursor return emp%rowtype; //宣告遊標變數第一步
4
5 ecname emp_cname; //宣告遊標變數第二步
6
7 emp_row emp%rowtype; //宣告用於儲存檢索資料的變數
8
9 begin
10 dbms_output.put_line('開始');
11 open ecname for select * from emp where empno=7934; //開啟遊標變數
12 loop
13 fetch ecname into emp_row; //查詢結果賦值給儲存的變數
14 exit when ecname%notfound; //退出條件
15 dbms_output.put_line(emp_row.ename); //輸出結果
16 end loop; //退出迴圈
17 close ecname; //關閉遊標變數
18 dbms_output.put_line('結束');
19 end;
20 /
開始
MILLER
結束
//複雜的案例
SQL> declare
2
3 type emp_cname is ref cursor return emp%rowtype;
4
5 ecname emp_cname;
6
7 emp_row emp%rowtype;
8
9 begin
10 dbms_output.put_line('開始');
11 open ecname for select * from emp;
12 loop
13 fetch ecname into emp_row;
14 exit when ecname%notfound;
15 dbms_output.put_line(emp_row.ename);
16 end loop;
17 close ecname;
18 dbms_output.put_line('結束');
19 end;
20 /
開始
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
結束
使用遊標更新資料庫
1、 定位遊標之後進行刪除|修改指定的資料行 更新的時候需要使用for update選項 語法如下：
Cursor cursor_name is select_statement;
For update[of column[,column],[nowait]]
Of用來指定要鎖定的列,如果忽略of那麼表中選擇的資料行都將鎖定。如果被鎖定行已經被鎖定了,那麼必須等待釋放才能鎖定對於這種情況我們可以使用nowait語句。
當使用for update語句宣告遊標後,可以再delete|update語句中使用where current of子句，修改|刪除遊標結果集中當前行對應的表中的資料。
語法如下：
Where { current of cursor_name|search_condition}
舉例說明：
//修改操作
SQL> declare
2
3 cursor ecname is select * from emp where empno=7934
4 for update of sal nowait;
5
6 esal number(7,2);
7
8
9 begin
10 dbms_output.put_line('開始');
11
12 for r in ecname loop
13 esal:=r.sal*10;
14 update emp set sal=esal where current of ecname;
15 end loop;
16
17 dbms_output.put_line('結束');
18 end;
19 /
開始
結束
以上內容歸redarmy_chen所有,如需轉載請附帶出處,如有疑問請傳送至redarmy_chen@qq.com