Oracle 數據庫管理與應用：第7章 PLSQL編程基礎3

1、7.7 游標的創(chuàng)建與應用 當SELECT語句在PL/SQL程序塊中使用時，要求查詢結果集中只能包含一條記錄，若查詢出來的數據多于一行，則執(zhí)行出錯SQL提供了游標機制，使用游標可以解決這個問題。游標是指向查詢結果集的一個指針（內存的數據結構），通過游標可以將查詢結果集中的記錄逐一取出，并在PL/SQL程序塊中進行處理。 在Oracle中，包含兩種類型的游標：顯式游標和隱式游標。顯式游標是用戶自己創(chuàng)建并操作的游標隱式游標是由系統(tǒng)自動創(chuàng)建并管理的游標，用戶可以訪問隱式游標的屬性。系統(tǒng)為所有的DML語句和SELECT語句自動創(chuàng)建“隱式游標”。 7.7.1 顯式游標 1. 顯式游標的使用過程 定義游標打

2、開游標提取數據處理數據關閉游標 （1）定義游標在使用顯式游標之前，必須先在程序塊的定義部分對其進行定義。 語法如下：CURSOR cursor_name IS select_statement;例如：CURSOR c1 IS SELECT * FROM scott.emp; （2）打開游標當游標被打開時，Oracle會執(zhí)行游標所對應的SELECT語句，并將游標作為指針，指向SELECT語句結果集的第一行。語法如下：OPEN cursor_name;例如：OPEN c1;（3）使用游標獲取數據 游標被打開后，可以使用FETCH語句獲取游標正在指向的查詢結果集中的記錄該語句執(zhí)行后游標的指針自動下移

3、，指向下一條記錄。因此，每執(zhí)行一次FETCH語句，游標只獲取到一行記錄，如果要處理查詢結果集中的所有數據，那么需要多次執(zhí)行FETCH語句，通常使用循環(huán)實現。FETCH語句的語法如下：FETCH cursor_name INTO variable1,variable2,.;其中，variable表示一組用于接收游標獲取到的當前記錄的變量。這些變量的個數、數據類型、寬度應和游標指向的查詢結果集的結構保持一致。例如：FETCH c1 INTO emp_rec; 注意：本例中使用的emp_rec變量是一個記錄變量(例如：使用%ROWTYPE定義類型)，如果使用簡單變量需要定義多個，實現起來較麻煩。 （

4、4）處理從游標中獲取到的數據可以進一步處理存入到變量中的數據了，具體執(zhí)行何種處理操作，與具體的業(yè)務有關。例如，將上面emp_rec記錄變量中接收到的數據進行輸出。dbms_output.put_line(姓名是：|emp_rec.ename|工資是|emp_rec.sal);（5）關閉游標 在提取并處理了結果集的所有數據之后，就可以關閉游標并釋放其結果集了。語法如下：CLOSE cursor_name;例如：CLOSE c1; 例7.32 從scott.emp表中查詢所有記錄，利用游標獲取前兩行記錄并輸出ename、job、sal中的值。DECLARE CURSOR c1 IS SELECT

5、* FROM scott.emp; emp_rec scott.emp%ROWTYPE; -定義一個和表結構完全一致的記錄變量BEGIN OPEN c1; FETCH c1 INTO emp_rec; dbms_output.put_line(姓名是:|emp_rec.ename| 工作是:|emp_rec.job| 工資是:|emp_rec.sal); FETCH c1 INTO emp_rec; dbms_output.put_line(工作是:|emp_rec.ename|工作是:|emp_rec.job| 工資是:|emp_rec.sal); CLOSE c1;END; 將上面的程序改

6、進：程序實際需要處理的數據只有ename、job和sal三個字段的值 所以，可以將游標的定義和記錄變量的定義改為如下形式：CURSOR c1 IS SELECT ename,job,sal FROM scott.emp;emp_rec c1%ROWTYPE; -將記錄變量定義為和游標的結構一致 2. 游標的常用屬性 在游標的使用過程中，經常需要用到游標的四個屬性，以確定游標當前和總體狀態(tài)。%ISOPEN屬性：游標是否打開%FOUND屬性： 是否取到數據%NOTFOUND屬性：與上面相反%ROWCOUNT屬性：已獲取的記錄總數游標屬性的引用格式：游標名%游標屬性名 如：c1%ISOPEN參見教材

7、p158159結果為邏輯型數據結果為整型數據3. 顯式游標的循環(huán)用戶在使用FETCH語句獲取游標指向的查詢結果時，每次只能獲取一條記錄。如果獲取結果集中所有的記錄，那么需要使用循環(huán)重復執(zhí)行FETCH語句。 （1）游標的LOOP循環(huán)例7.33 使用游標實現逐行輸出scott.emp表中部門編號為10的員工姓名和工資。DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM scott.emp WHERE deptno=10; emp_record scott.emp%ROWTYPE; -也可以使用游標的結構直接定義該記錄變量BEGIN OPEN emp

8、_cursor ; LOOP FETCH emp_cursor INTO emp_record.ename,emp_record.sal; EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line(ename: |emp_record.ename| sal:|emp_record.sal); END LOOP; dbms_output.put_line(row count:|emp_cursor%rowcount); CLOSE emp_cursor;END;（2）游標的FOR循環(huán)與其他的循環(huán)語句相比FOR循環(huán)更能方便的控制游標的循環(huán)過程，這是由于

9、：FOR循環(huán)中的循環(huán)控制變量不需要事先定義。在游標的FOR循環(huán)之前，系統(tǒng)能夠自動打開游標；在FOR循環(huán)結束后，系統(tǒng)能夠自動關閉游標，不需要人為操作。在游標的FOR循環(huán)過程中，系統(tǒng)能夠自動執(zhí)行FETCH語句；每一次循環(huán)，系統(tǒng)就自動執(zhí)行一次FETCH語句，將游標指向的當前行記錄存入循環(huán)控制變量中 例7.34 使用FOR循環(huán)語句完成例7.33中規(guī)定的操作DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM scott.emp WHERE deptno=10;BEGIN FOR emp_record IN emp_cursor LOOP dbms_out

10、put.put_line(ename: |emp_record.ename| sal:|emp_record.sal); END LOOP;/* 下面這個語句無效，因為FOR循環(huán)結束后游標自動關閉dbms_output.put_line(row count:|emp_cursor%rowcount);*/END;例7.35 使用游標查詢scott.emp表中的所有記錄，并在程序塊中輸出工資最高的前五行記錄。DECLARE CURSOR cur IS SELECT * FROM scott.emp ORDER BY sal DESC;BEGIN FOR rec IN cur LOOP IF cu

11、r%ROWCOUNT=5 THEN dbms_output.put_line(ename: |rec.ename| sal:|rec.sal); ELSE EXIT; -退出循環(huán) END IF; END LOOP;END;7.7.2 帶參數的游標 參數游標是指帶有參數的游標，在定義了參數游標之后，當使用不同參數值打開游標時，可以產生不同的結果集。定義參數游標的語法如下： CURSOR cursor_name(parameter_name datatype) IS select_statment; 使用open命令打開帶參數的游標時，需要給游標傳遞實參值。例7.36 定義參數游標，查詢指定部門的

12、員工姓名。DECLARE-定義游標參數no，參數類型為number類型CURSOR emp_cursor(no NUMBER) IS SELECT ename FROM scott.emp WHERE deptno=no;emp_rec emp_cursor%ROWTYPE;BEGIN -打開參數游標時，指明一個替代變量作為游標參數的值 OPEN emp_cursor(&no); LOOP FETCH emp_cursor INTO emp_rec; EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line(ename:|emp_rec.ename

13、); END LOOP; CLOSE emp_cursor;END;使用FOR循環(huán)完成上例的操作，代碼如下所示：DECLARECURSOR emp_cursor(no NUMBER) IS SELECT * FROM scott.emp WHERE deptno=no; BEGIN FOR emp_record in emp_cursor(&no) LOOP dbms_output.put_line(ename:|emp_record.ename); END LOOP;END;7.7.3 隱式游標 在執(zhí)行一個SQL語句時（select、Insert、update、delete），Oracle服

14、務器將自動創(chuàng)建一個隱式游標。通過游標可獲得SQL語句的執(zhí)行結果，以及游標的狀態(tài)信息。隱式游標屬性的訪問方式：sql%屬性名例7.37 以下是一個更新指定部門員工工資的示例，通過游標屬性查看被更新記錄的行數。BEGIN UPDATE scott.emp SET sal = 1500 WHERE deptno = &no; IF sql%NOTFOUND THEN dbms_output.put_line(no update); ELSE dbms_output.put_line(update row: | sql%ROWCOUNT); END IF;END;如果在循環(huán)中不需要引用游標的名稱，那么

15、可以直接在游標FOR循環(huán)中使用子查詢，由系統(tǒng)自動創(chuàng)建隱式游標。例7.38 使用隱式游標的FOR循環(huán)查詢emp表中的數據。BEGIN FOR emp_record IN (SELECT ename,sal FROM emp) LOOP dbms_output.put_line(emp_record.ename| |emp_record.sal); END LOOP;END; 7.7.4 使用游標更新表中的數據 可以使用游標在表中修改、刪除數據。但是需要注意，若想通過游標修改或刪除數據，那么在定義游標時必須要帶有FOR UPDATE子句。語法格式如下：CURSOR cursor_name(para

16、meter_name datatype)IS select_statementFOR UPDATE OF column_reference NOWAIT;FOR UPDATE子句用于在游標結果集數據上加行共享鎖，以防止其他用戶在相應行上執(zhí)行DML操作。OF子句用來指定要鎖定的列，忽略OF子句，那么表中選擇的數據行都將被鎖定。 NOWAIT子句，表示，如果游標要修改的行被另一個用戶的操作鎖定，則OPEN會立即返回錯誤提示，不再等待。 為了修改或刪除游標當前行數據，必須在UPDATE、 DELETE語句中使用WHERE CURRENT OF 子句，表示修改或刪除的記錄是游標指向的當前記錄。UPDA

17、TE table_name SET column= new_value WHERE CURRENT OF cursor_name;DELETE table_name WHERE CURRENT OF cursor_name;例7.39 使用顯式游標查詢scott.emp表中的記錄，并將工資低于3000的工資增加為原來的百分之二十。DECLARE CURSOR emp_cursor IS SELECT ename, sal FROM scott.emp FOR UPDATE;BEGIN FOR rec IN emp_cursor LOOP IF rec.sal10000 THENRAISE ex_insert; -用戶自定義異常的觸發(fā)ELSEINSER