Oracle–plsql遊標建立和使用

為什麼要遊標：

先看這個：

DECLARE<br /> v_empno emp.empno%type;<br /> v_ename emp.ename%type;<br />BEGIN<br /> SELECT empno,ename<br /> into v_ename,v_empno<br /> FROM emp;<br /> dbms_output.put_line(v_empno||' '||v_ename);<br />END;
這個SELECT語句可能返回多條記錄，所以這個指派陳述式可能是錯誤的。所以遊標可以發揮作用了。

什麼是遊標？

 遊標（cursor）是Oracle系統在記憶體中開闢的一個工作區，在其中存放SELECT語句返回的查詢結果。 

遊標的分類：

隱式遊標：PL/SQL隱式建立並自動管理這一遊標。隱式遊標也叫做SQL遊標。 對於SQL遊標，不能對其顯式地執行                     OPEN、CLOSE和FETCH語句，但是可以使用遊標屬性從最近執行的SQL語句中擷取資訊。

顯式遊標:由程式員顯式說明及控制，用於從表中取出多行資料，並將多行資料一行一行單獨處理。

隱式遊標：

• 由Oracle在內部聲明 
• 由Oracle自行管理遊標 
• 可以使用遊標屬性從最近執行的SQL語句中擷取資訊 
• 用於處理DML語句以及返回單行的查詢

隱式遊標屬性：


隱式遊標舉例：VARIABLE rows_deleted VARCHAR2(30)<br />DECLARE<br /> v_deptno NUMBER := 20;<br />BEGIN<br /> DELETE FROM emp<br /> WHERE deptno = v_deptno;<br /> :rows_deleted := (SQL%ROWCOUNT ||' rows deleted.');<br />END;<br />/<br />

返回所刪除行的數量

顯式遊標:由使用者顯式聲明,查詢返回多行記錄

• 使用遊標時，select語句查詢的結果可以是單條記錄，多條記錄，也可以是零條記錄。
• 遊標工作區中，存在著一個指標（POINTER）,在初始狀態它指向查詢結果的首記錄。
• 要訪問查詢結果的所有記錄，可以通過FETCH語句，進行指標的移動來實現。
• 使用遊標進行操作，包括定義遊標、開啟遊標、提取資料以及關閉遊標幾步。

聲明:命名遊標,定義在遊標中執行查詢的結構

開啟:OPEN 語句執行查詢並綁定每一個引用到的變數。滿足查詢條件的行稱為活動集（activeset )。

擷取:通過遊標從活動集中獲得資料,在投影片的流程圖中，每一次提取之後都要測試遊標中是否還有當前行。如果沒有要處理的行，就可以關閉遊標了。

關閉:CLOSE 語句釋放活動集中的所有行。這之後，就可以重新開啟這個遊標來建立一個新的活動集。

遊標聲明：

DECLARE<br /> CURSOR emp_cursor IS<br /> SELECT empno, ename<br /> FROM emp;</p><p> CURSOR dept_cursor IS<br /> SELECT *<br /> FROM dept<br /> WHERE deptno = 10;<br />BEGIN<br /> ...
DECLARE<br />v_empnoemp.empno%type;<br />v_enameemp.ename%type;<br />CURSORemp_cursor IS<br />SELECT empno,ename FROM emp;<br />BEGIN<br />OPEN emp_cursor;<br />FOR i IN 1..5 LOOP<br />FETCH emp_cursor INTO v_ename,v_empno;<br />dbms_output.put_line(v_empno||' '||v_ename);<br />END LOOP;<br />CLOSE emp_cursor;<br />END;<br />
顯示遊標屬性：

%ISOPEN屬性

•僅當遊標處於開啟狀態時才可以從中提取資料。•在執行提取操作之前，使用%ISOPEN
遊標屬性，檢測遊標是否已被開啟declare<br /> v_empno emp.empno%type;<br /> v_ename emp.ename%type;<br /> cursor emp_cursor is<br /> select empno,ename from emp;</p><p>begin<br /> if not emp_cursor%isopen then<br /> open emp_cursor;<br /> end if;<br /> loop<br /> fetch emp_cursor into v_empno,v_ename;<br /> exit when emp_cursor%notfound;<br /> dbms_output.put_line(v_empno||' '||v_ename);<br /> end loop;<br /> dbms_output.put_line(emp_cursor%rowcount);<br /> close emp_cursor;<br />end;
將提取的行值存入一個PL/SQL RECORD
中能方便地處理活動集中的行。declare<br /> cursor emp_cursor is<br /> select empno,ename from emp;</p><p>begin<br /> for emp_record in emp_cursor loop<br /> if emp_record.ename = 'SCOTT' then<br /> dbms_output.put_line(emp_record.empno);<br /> end if;<br /> end loop;<br />end;
帶有參數的遊標：DECLARE<br />CURSOR emp_cursor<br />(p_deptno number,p_sal number)<br /> IS<br />SELECT ename FROM emp<br />WHERE deptno= p_deptno and sal>p_sal ;<br />BEGIN<br />OPEN emp_cursor(10,2000);<br />….<br />CLOSE emp_cursor;<br />OPEN emp_cursor(20,4000);<br />….<br />END;<br />
FORUPDATE 子句SELECT...<br />FROM...<br />FOR UPDATE [OF column_reference][NOWAIT];<br />•在事務執行期間可以顯式鎖定以拒絕訪問。•在更新或刪除行時要鎖定該行。

DECLARE<br /> CURSOR emp_cursor IS<br /> SELECT empno, ename, sal<br /> FROM emp<br /> WHERE deptno = 30<br /> FOR UPDATE OF sal NOWAIT;<br />
WHERECURRENT OF 子句

•更新或刪除遊標中的當前行資料

WHERE CURRENT OF cursor ;•首先要在遊標中使用FOR UPDATE
子句鎖定行•使用 WHERE CURRENT OF
子句從顯式遊標中引用當前行

DECLARE<br /> CURSOR sal_cursor IS<br /> SELECT sal<br /> FROMemp<br /> WHEREdeptno = 30<br /> FOR UPDATE OF sal NOWAIT;<br />BEGIN<br /> FOR emp_record IN sal_cursor LOOP<br /> UPDATEemp<br /> SET sal = emp_record.sal * 1.10<br /> WHERE CURRENT OF sal_cursor;<br /> END LOOP;<br /> --COMMIT;<br />END;<br />
使用子查詢的遊標

DECLARE<br /> CURSOR my_cursor IS<br /> SELECT t1.deptno, t1.dname, t2.STAFF<br /> FROM dept t1, (SELECT deptno,<br /> count(*) STAFF<br /> FROM emp<br /> GROUP BY deptno) t2<br /> WHERE t1.deptno = t2.deptno<br /> AND t2.STAFF >= 5;<br />

子查詢就是在別的SQL資料處理語句中的查詢語句，通常它們被用圓括弧括起來。當需要的時候，子查詢提供一個值或結果集給資料處理語句。

一般子查詢經常會被用在 WHERE 子句中。同樣，也可以在FROM子句中使用子查詢。