Oracle 數(shù)據(jù)庫(kù)管理與應(yīng)用：第7章 PLSQL編程基礎(chǔ)3

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

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

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

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

5、* FROM scott.emp; emp_rec scott.emp%ROWTYPE; -定義一個(gè)和表結(jié)構(gòu)完全一致的記錄變量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、進(jìn)：程序?qū)嶋H需要處理的數(shù)據(jù)只有ename、job和sal三個(gè)字段的值 所以，可以將游標(biāo)的定義和記錄變量的定義改為如下形式：CURSOR c1 IS SELECT ename,job,sal FROM scott.emp;emp_rec c1%ROWTYPE; -將記錄變量定義為和游標(biāo)的結(jié)構(gòu)一致 2. 游標(biāo)的常用屬性 在游標(biāo)的使用過程中，經(jīng)常需要用到游標(biāo)的四個(gè)屬性，以確定游標(biāo)當(dāng)前和總體狀態(tài)。%ISOPEN屬性：游標(biāo)是否打開%FOUND屬性： 是否取到數(shù)據(jù)%NOTFOUND屬性：與上面相反%ROWCOUNT屬性：已獲取的記錄總數(shù)游標(biāo)屬性的引用格式：游標(biāo)名%游標(biāo)屬性名 如：c1%ISOPEN參見教材

7、p158159結(jié)果為邏輯型數(shù)據(jù)結(jié)果為整型數(shù)據(jù)3. 顯式游標(biāo)的循環(huán)用戶在使用FETCH語(yǔ)句獲取游標(biāo)指向的查詢結(jié)果時(shí)，每次只能獲取一條記錄。如果獲取結(jié)果集中所有的記錄，那么需要使用循環(huán)重復(fù)執(zhí)行FETCH語(yǔ)句。 （1）游標(biāo)的LOOP循環(huán)例7.33 使用游標(biāo)實(shí)現(xiàn)逐行輸出scott.emp表中部門編號(hào)為10的員工姓名和工資。DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM scott.emp WHERE deptno=10; emp_record scott.emp%ROWTYPE; -也可以使用游標(biāo)的結(jié)構(gòu)直接定義該記錄變量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）游標(biāo)的FOR循環(huán)與其他的循環(huán)語(yǔ)句相比FOR循環(huán)更能方便的控制游標(biāo)的循環(huán)過程，這是由于

9、：FOR循環(huán)中的循環(huán)控制變量不需要事先定義。在游標(biāo)的FOR循環(huán)之前，系統(tǒng)能夠自動(dòng)打開游標(biāo)；在FOR循環(huán)結(jié)束后，系統(tǒng)能夠自動(dòng)關(guān)閉游標(biāo)，不需要人為操作。在游標(biāo)的FOR循環(huán)過程中，系統(tǒng)能夠自動(dòng)執(zhí)行FETCH語(yǔ)句；每一次循環(huán)，系統(tǒng)就自動(dòng)執(zhí)行一次FETCH語(yǔ)句，將游標(biāo)指向的當(dāng)前行記錄存入循環(huán)控制變量中 例7.34 使用FOR循環(huán)語(yǔ)句完成例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;/* 下面這個(gè)語(yǔ)句無效，因?yàn)镕OR循環(huán)結(jié)束后游標(biāo)自動(dòng)關(guān)閉dbms_output.put_line(row count:|emp_cursor%rowcount);*/END;例7.35 使用游標(biāo)查詢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 帶參數(shù)的游標(biāo) 參數(shù)游標(biāo)是指帶有參數(shù)的游標(biāo)，在定義了參數(shù)游標(biāo)之后，當(dāng)使用不同參數(shù)值打開游標(biāo)時(shí)，可以產(chǎn)生不同的結(jié)果集。定義參數(shù)游標(biāo)的語(yǔ)法如下： CURSOR cursor_name(parameter_name datatype) IS select_statment; 使用open命令打開帶參數(shù)的游標(biāo)時(shí)，需要給游標(biāo)傳遞實(shí)參值。例7.36 定義參數(shù)游標(biāo)，查詢指定部門的

12、員工姓名。DECLARE-定義游標(biāo)參數(shù)no，參數(shù)類型為number類型CURSOR emp_cursor(no NUMBER) IS SELECT ename FROM scott.emp WHERE deptno=no;emp_rec emp_cursor%ROWTYPE;BEGIN -打開參數(shù)游標(biāo)時(shí)，指明一個(gè)替代變量作為游標(biāo)參數(shù)的值 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 隱式游標(biāo) 在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)（select、Insert、update、delete），Oracle服

14、務(wù)器將自動(dòng)創(chuàng)建一個(gè)隱式游標(biāo)。通過游標(biāo)可獲得SQL語(yǔ)句的執(zhí)行結(jié)果，以及游標(biāo)的狀態(tài)信息。隱式游標(biāo)屬性的訪問方式：sql%屬性名例7.37 以下是一個(gè)更新指定部門員工工資的示例，通過游標(biāo)屬性查看被更新記錄的行數(shù)。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)中不需要引用游標(biāo)的名稱，那么

15、可以直接在游標(biāo)FOR循環(huán)中使用子查詢，由系統(tǒng)自動(dòng)創(chuàng)建隱式游標(biāo)。例7.38 使用隱式游標(biāo)的FOR循環(huán)查詢emp表中的數(shù)據(jù)。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 使用游標(biāo)更新表中的數(shù)據(jù) 可以使用游標(biāo)在表中修改、刪除數(shù)據(jù)。但是需要注意，若想通過游標(biāo)修改或刪除數(shù)據(jù)，那么在定義游標(biāo)時(shí)必須要帶有FOR UPDATE子句。語(yǔ)法格式如下：CURSOR cursor_name(para

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

17、TE table_name SET column= new_value WHERE CURRENT OF cursor_name;DELETE table_name WHERE CURRENT OF cursor_name;例7.39 使用顯式游標(biāo)查詢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