PL/SQL編程_游標

游標是一種私有的工作區，用於保存SQL語句的執行結果。
在執行一條SQL語句時，資料庫伺服器會打開一個工作區，將SQL語句的執行結果保存在這裡。
在Oracle資料庫中有兩種形式的游標：隱式游標和顯式游標。
隱式游標是由資料庫伺服器定義的，顯式游標是用戶根據需要自己定義的。

隱式游標
隱式游標是資料庫伺服器定義的一種游標。
在執行一條DML語句或SELECT語句時，資料庫伺服器將自動打開一個隱式游標，存放該語句的執行結果。
在一個PL/SQL塊中可能有多條DML或SELECT語句，隱式游標始終存放最近一條語句的執行結果。
隱式游標有幾個很有用的屬性，可以幫助我們瞭解游標的信息。
下麵列出了隱式游標的幾個常用屬性。
SQL%ISOPEN 判斷當前游標是否打開。如果打開，該屬性值為TRUE，否則為FALSE
SQL%ROWCOUNT 對於DML語句，該屬性值為受影響的數據行數。對於SELECT語句，如果不發生異常，其值為1
SQL%FOUND 對於DML語句，該屬性表明表中是否有數據受到影響。如果DML語句沒有影響任何數據，該屬性值為FALSE，否則為TRUE。對於SELECT語句，如果不發生異常，其值為TRUE
SQL%NOTFOUND 與屬性SQL%FOUND正好相反
其中SQL%FOUND屬性值為布爾值，表示是否找到了滿足條件的數據，如果找到了相應的數據，其值為TRUE ，否則為FALSE 。
SQL%ROWCOUNT屬性表示某個操作影響的數據行數，對於UPDATE語句，表示修改的行數；對於INSERT語句，表示插入的行數；對於DELETE語句，表示被刪除的行數。
例如，可以在每一條DML語句之後輸出該操作影響的行數。
BEGIN
UPDATE emp set sal=sal+100;
if SQL%FOUND then
dbms_output.put_line('被修改的行數：' || SQL%ROWCOUNT);
END if;
DELETE FROM emp;
if SQL%FOUND then
dbms_output.put_line ('被刪除的行數：' || SQL%ROWCOUNT);
END if;
INSERT INTO dept VALUES(70 ,' aaa ',' aaaa ');
INSERT INTO dept VALUES(80 ,'bbb ',' bbbb' ) ;
if SQL%FOUND then
dbms_output.put_line ('最近插入的行數：' || SQL%ROWCOUNT);
END if;
ROLLBACK;
END;
從這個例子可以看出，當有多條DML語句時，隱式游標只記錄最近一條DML語句的執行情況。
如果是SELECT語句，情況則比較特殊。
因為當SELECT語句沒有檢索到滿足條件的數據時，將引發NO_DATA_FOUND 異常，而當檢索到多行滿足條件的數據時，將引發TOO_MANY ROWS異常。
所以只有當SELECT語句正好檢索到一行數據時，才可以使用隱式游標的這些屬性。
如果要處理這兩種特殊情況，就需要藉助於顯式游標。

顯式游標
對於PL/SQL塊中的SELECT語句，可以用顯式講標來處理。
顯式游標是一個打開的工作區，
在這個工作區里保存SELECT語句的執行結果。
用顯式游標可以處理返回0行、一行、多行等各種情況，並且在返回0行或多行數據這兩種特殊情況下，不會引發NO_DATA_FOUND 和TOO_MANY ROWS異常。
使用游標處理SELECT語句的步驟如下：
1 ）聲明游標
2 ）打開游標
3 ）逐行取出游標中的行，並分別進行處理
4 ）關閉游標
游標的工作過程如下所示。
DECLARE OPEN FETCH CLOSE
聲明游標 打開游標 從游標中取數據 關閉游標
註：在從游標中取數據時，要做個判斷，判斷是和否取完了，如果是，則CLOSE游標；如果否，則繼續FETCH游標。
游標的聲明在PL/SQL塊的聲明部分進行。
聲明的語法格式為：
DECLARE
CURSOR 游標名 IS
SELECT 語句;
例如，下麵的語句聲明瞭一個名為cur_1 的游標：
DECLARE
CURSOR cur_1 IS
SELECT * FROM emp;
由於SELECT語句的執行結果將存放在工作區中，因此不需要使用INTO子句將返回的數據賦給變數。
為了處理游標中的數據，首先要打開游標。
打開游標意味著將指定的SELECT語句交給資料庫伺服器執行，並將返回結果存放在工作區中。
打開游標的命令是OPEN ，其語法格式為：
OPEN 游標名
例如，打開游標cur_1的語句為：
OPEN cur_1;
游標打開後，就可以取出游標中的數據，並對其進行處理了。
從游標中取出數據的命令是FETCH 。
FETCH命令一次取出一行數據，並將其賦給指定的變數。
FETCH命令的格式為：
FETCH 游標 INTO 變數1，變數2, ...
游標中的數據只有在取出後才能進行處理。
為此，需要在FETCH語句中用INTO子句指定多個變數，分別存放一行數據中各個列的值。
FETCH命令將一行數據中各列的值依次賦給指定的變數。
需要註意的是，變數的類型、數目要與游標中一行的各列相對應。
在用FETCH命令取出游標中的數據時，可以設想有一個指針，指向游標中的一行數據。
當游標剛剛打開時，指針指向第一行，以後每取出一行，指針自動指向下一行，直到將所有的數據都取出為止。
游標在使用完後，應該及時關閉，以釋放它所占用的記憶體空間。
關閉游標的命令是CLOSE,其語法格式為：
CLOSE 游標名;
當游標關閉後，不能再從游標中獲取數據。
如果需要，可以再次打開游標。
考慮下麵的游標。
從emp表中檢索員工7902 的姓名、工資、工作時間。
由於SELECT命令僅返回一行數據，所以處理的過程很簡單。

DECLARE
name emp.ename%type;
salary emp.sal%type;
hire_date emp.hiredate%type;
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE empno=7902;
BEGIN
open cur_1;
fetch cur_1 INTO name,salary,hire_date;
dbms_output.put_line ('姓名：' || name || '工資：' || salary || '工作時間：' || hire_date);
close cur_1;
END;

為了使程式更加簡潔，在PL/SQL塊中可以使用記錄變數。

首先聲明一個記錄變數，它的結構與游標的結構相同。
然後可以使用FETCH語句將游標中的一行數據取出後存放在記錄變數中，接下來就可以對這個記錄變數進行處理了。
例如，對上面的PL/SQL塊進行修改，在PL/SQL塊中使用記錄變數。
修改後的代碼如下：

DECLARE
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE empno=7902;
e cur_1%rowtype;
BEGIN
open cur_1;
fetch cur_1 INTO e.ename, e.sal, e.hiredate;
dbms_output.put_line ('姓名：' || e.ename || '工資：' || e.sal|| '工作時間：' || e.hiredate);
close cur_1;
END;

在上面的塊中通過一個簡單的游標，處理一行數據。

在使用游標時，必須考慮各種特殊情況。
如果SELECT語句沒有返回結果，游標是空的，這時FETCH語句將取不到數據。

如果SELECT語句返回多行數據，這時用一條FETCH語句僅能取到游標中的一行數據。

利用游標的屬性可以瞭解游標當前的狀態，防止各種意外情況的發生。
下麵出了顯式游標的若幹屬性。

CURSOR%ISOPEN　　判斷當前游標是否打開。如果打開，該屬性值為TRUE,否則為FALSE

CURSOR%ROWCOUNT　　表示到目前為止，用FETCH語句取到的行數

CURSOR%FOUND　　表示最近一次FETCH操作是否從游標中取到一行數據，如果已經取到，其值為TRUE，否則為FALSE

CURSOR%NOTFOUND　　與屬性CURSOR%FOUND相反

備註：CURSOR為PL/SQL塊中游標的名稱。

在下麵的PL/SQL塊中使用了顯式游標的屬性，使得PL/SQL塊能夠處理各種例外情況，比如沒有取到合適的數據，或者取到多行數據。

DECLARE
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=20;
e cur_1%rowtype;
BEGIN
IF NOT cur_1%ISOPEN then
OPEN cur_1; --如果游標沒有打開，則打開它
END IF;
FETCH cur_1 INTO e; --取第一行數據
WHILE cur_1%FOUND LOOP
dbms_output.put_line ('姓名：' || e.ename || '工資：' || e.sal|| '工作時間：' || e.hiredate);
FETCH cur_1 INTO e;
END LOOP;
dbms_output.put_line('員工總數：' || cur_1%ROWCOUNT); --獲取總行數
CLOSE cur_1; --關閉游標
END;

這個PL/SQL塊的功能是查詢部門20的所有員工的姓名、工資和工作時間。

如果這個部門不存在，則不顯示任何員工的信息，僅顯示“員工總數：。”的信息。
如果該部門有一個或多個員工，則顯示他們的信息，井列印該部門員工總數。
在塊中首先用游標的%ISOPEN屬性判斷游標是否打開，如果沒有打開，則打開它。

然後用FETCH語句取出第一行，並用游標的%FOUND屬性判斷是否取到數據。
如果游標是空的，則這個屬性的值為FALSE ，這樣就不用繼續取數據了。
如果取到了數據，則處理這行數據，並試圖取下一行。

這樣通過迴圈的方式，每取到一行數據，就試圖再取下一行，然後判斷是否取到數據，直到將所有數據取出。
在游標的四個屬性中， %lSOPEN 屬性用於測試游標的狀態。

其他三個屬性用來測試FETCH命令的執行結果%FOUND和%NOTFOUND屬性用來測試最近的一次FETCH是否取到數據， %ROWCOUNT屬性表示自游標打開以來，到目前為止，用FETCH命令獲取的行數。
下麵的例子用另外一種形式的迴圈處理游標。

DECLARE
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=20;
e cur_1%rowtype;
BEGIN
IF NOT cur_1%ISOPEN then
OPEN cur_1; --如果游標沒有打開，則打開它
END IF;
LOOP
FETCH cur_1 INTO e; --取第一行數據
EXIT WHEN cur_1%NOTFOUND;  --如果最近一次FETCH沒有取得數據，則退出迴圈，否則，對取得數據進行處理
dbms_output.put_line ('姓名：' || e.ename || '工資：' || e.sal|| '工作時間：' || e.hiredate);
END LOOP;
dbms_output.put_line('員工總數：' || cur_1%ROWCOUNT); --獲取總行數
CLOSE cur_1; --關閉游標
END;

游標中的數據一般是通過迴圈方式來處理的。

在上面兩個例子中定義了兩個游標，並用常規的迴圈方法進行處理。

PL/SQL提供了一種更簡便的方怯處理游標，這種方法利用FOR迴圈，遠行處理游標中的行。
FOR語句的格式為：

FOR 變數 IN 游標名 LOOP

　　處理變數

END LOOP;

這裡把游標中的數據當做一個集合，一次從中取出一行，賦給一個記錄類型變數，然後就可以處理這個變數了。
這個變數在使用之前不需要定義，在迴圈開始時自動產生，在FOR語句中可以直接使用，這個變數的結構與游標的結構完全相同。
利用FOR迴圈從游標中取數據時，不需要用OPEN命令打開游標。

當迴圈開始執行時，游標被自動打開。
游標在使用完後，也不需要執行CLOSE命令關閉。
FOR迴圈的迴圈體每執行一次，就會自動取出游標中的一行數據，賦給記錄類型變數，然後指針自動往下移動，所以不需要通過FETCH命令獲取游標中的數據。
例如，下麵的PL/SQL塊利用FOR迴圈處理游標中的數據。

DECLARE
e_count number := 0;
CURSOR cur_1 IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=20;
BEGIN
FOR e IN cur_1 LOOP
dbms_output.put_line ('姓名：' || e.ename || '工資：' || e.sal|| '工作時間：' || e.hiredate);
e_count := e_count + 1;
END LOOP;
dbms_output.put_line('員工總數：' || e_count); --獲取總行數
END;

由於在迴圈執行結束時，游標已經關閉，因此無法再用游標的%rowcount屬性統計獲取的數據行數。
在塊中聲明瞭一個變數e_count ，每進行一次迴圈，變數e_count加1 ，這樣就可以統計出獲取的總行數。
從上面的例子可以看出，利用FOR迴圈可以大大簡化游標的處理過程。
需要註意的是，為了重點說明游標的用法，在與游標有關的例子中，對於從游標中取出的數據，僅僅顯示在屏幕上。
可以根據需要，對這些數據進行其他的處理，比如寫入其他表中。

帶參數的游標

在前面介紹游標的例子中， SELECT語句都沒有WHERE子句，或者用WHERE子句指定了一個固定的條件，這樣每次都查詢同樣的數據。
在更多的情況下，可能要根據實際情況查詢不同的數據。
為了通過游標對數據進行更加靈活的處理，可以為游標定義參數，這些參數可以用在WHERE子句中。
在打開游標時，指定實際的參數值，這樣游標在每次打開時，可以根據不同的實際參數值，返回所需的不同數據。
定義帶參數的游標的語法格式為：

DECLARE

　　CURSOR 游標名（參數1， 參數2 ...）

　　IS 

　　SELECT 語句;

其中參數的定義方法與子程式中的參數定義完全相同，可以指定預設值，指定參數傳遞模式。
預設的參數傳遞模式為IN ，如果要使用OUT或者“IN OUT”模式，就需要明確指定。

由於游標一般不需要通過參數向調用者傳遞數據，所以OUT模式在游標中沒有什麼實際用處。
在用OPEN命令打開游標時，要向游標提供實際參數，游標根據提供的參數值，查詢符合條件的數據。
打開游標的語法格式為：

OPEN 游標名（實際參數1，實際參數2 ...）

例如，考慮在下麵定義的游標。

DECLARE
CURSOR cur_1(d_no IN emp.deptno%type, min_sal IN emp.sal%type := 1000)
IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=d_no AND sal >= min_sal;
e cur_1%rowtype;
BEGIN
IF NOT cur_1%ISOPEN THEN --如果游標沒有打開，則打開它
OPEN cur_1(20, 2000);
END IF;
FETCH cur_1 INTO e;
WHILE cur_1%FOUND LOOP
dbms_output.put_line ('姓名：' || e.ename || '工資：' || e.sal|| '工作時間：' || e.hiredate);
FETCH cur_1 INTO e;
END LOOP;
dbms_output.put_line('員工總數：' || cur_1%ROWCOUNT); --獲取總行數
CLOSE cur_1;
END;

在這個例子中，用傳統的迴圈方法處理游標。

首先定義了一個帶參數的游標，參數d_no表示部門編號， min_sal表示最低工資，兩個參數的傳遞模式都是IN。
游標的功能是查詢屬於指定部門並且工資不低於指定值的所有員工。
在打開游標時，指定了兩個實際參數20和2000 ，這樣，檢索出來的數據就是屬於部門20,並且工資不低於2000的所有員工。
如果再次以“open cur_6 ( 10,3000 ）”的形式打開游標，那麼檢索到的數據就是屬於部門10 ，並且工資不低於3000的員工。
由此可見，帶參數的游標在查詢數據時更加靈活。
如果要用FOR 迴圈處理游標中的數據，可以按照同樣的方法定義游標。

由於沒有使用OPEN命令打開游標，所以實際參數在FOR語句中指定。
這時FOR語句的格式為：

FOR 變數 IN 游標名（實際參數1， 實際參數2 ...） LOOP
...

END LOOP;

這樣，在迴圈開始執行時，游標自動打開，並根據指定的實際參數查詢數據。

例如，用FOR迴圈處理帶參數的游標，對上面的PL/SQL塊進行修改，代碼如下：

DECLARE
e_count number := 0;
CURSOR cur_1(d_no IN emp.deptno%type, min_sal IN emp.sal%type := 1000)
IS
SELECT ename,sal,hiredate FROM emp WHERE deptno=d_no AND sal >= min_sal;
BEGIN
FOR e IN cur_1(20 ,2000) LOOP
dbms_output.put_line ('姓名：' || e.ename || '工資：' || e.sal|| '工作時間：' || e.hiredate);
e_count := e_count + 1;
END LOOP;
dbms_output.put_line('員工總數：' || e_count); --獲取總行數
END;

如何通過游標修改表中的數據

游標的主要作用是查詢數據，並對數據逐行進行處理。

對於游標中的數據還可以根據需要進行修改，例如修改某個列的值，或者刪除某一行。
在定義游標時，需要把游標定義為可以修改的形式，定義格式如下：

DECLARE

　　CURSOR 游標名（參數1，參數2 ...）

　　IS

　　SELECT語句

　　FOR UPDATE;

游標、可以帶參數，或者不帶參數。

SELECT語句中的FOR UPDATE子句的作用是加鎖，它的功能是把游標中的數據鎖定，這樣可以防止其他用戶同時修改這些數據。
由於在併發環境中許多用戶可能同時訪問資料庫，如果把游標打開後不希望其他用戶同時修改這些數據，就需要對游標加鎖，否則就會導致數據的不一致。
SELECT語句中的FOR UPDATE子句就是為了在游標打開後對它進行加鎖，待游標關閉時再釋放鎖，用這種方式可以保證用戶對數據的正確訪問。
對游標中的數據是逐行處理的，每次處理指針當前指向的行。

在修改游標中的數據時，也是對當前行進行修改，然後將修改後的結果寫人資料庫。
PL/SQL提供了一種修改游標當前行的機制，如果在UPDATE 、DELETE語句中使用WHERE CURRENT OF子句，可以保證只對游標當前行進行修改。
WHERE CURRENT OF子句將修改操作限定在游標的當前行。
例如，某部門要為員工增加工資，增加的幅度為10% ，但只限於工資最低的5名員工。

如果本部門員工總數不足5人，則為所有員工都增加工資。
考慮用下麵的PL/SQL塊實現這個操作。

DECLARE
CURSOR cur_1(d_no emp.deptno%type)
IS
SELECT ename,sal,hiredate FROM emp
WHERE deptno=d_no
ORDER BY sal --保證員工按照工資從低到高的順序排列
FOR UPDATE;
e_count integer:=0;
e cur_1%rowtype;
BEGIN
open cur_1 ( 30); --打開游標，將部門編號30 作為參數
fetch cur_1 INTO e;
while cur_1%FOUND loop
exit when e_count>=5;
UPDATE emp set sal=sal*1.1 WHERE CURRENT OF cur_1;
e_count:=e_count+1;
fetch cur_1 INTO e;
END loop;
dbms_output.put_line ('增加工資的員工人數：' || e_count);
close cur_1;
END;

再來考慮下麵的PL/SQL塊，它的功能同樣是為某部門工資最低的5名員工增加工資。

不過現在要用FOR迴圈來處理游標中的行。

DECLARE
CURSOR cur_1(d_no emp.deptno%type)
IS
SELECT ename,sal,hiredate FROM emp
WHERE deptno=d_no
ORDER BY sal --保證員工按照工資從低到高的順序排列
FOR UPDATE;
e_count integer:=0;
BEGIN
FOR e IN cur_1(30) loop
exit when e_count>=5;
UPDATE emp set sal=sal*1.1 WHERE CURRENT OF cur_1;
e_count:=e_count+1;
END loop;
dbms_output.put_line ('增加工資的員工人數：' || e_count);
END;

通過WHERE CURRENT OF子句，不僅可以修改游標的當前行，還可以刪除游標的當前行，實際的結果是從資料庫中刪除了當前行。
例如，要刪除游標cur_1的當前行時，可以使用以下語句：
DELETE FROM emp  WHERE CURRENT OF cur_1;