1、1,Oracle 數(shù)據(jù)庫系統(tǒng)第10章 PL/SQL編程基礎(chǔ)-PL/SQL的游標和異常,2,區(qū)分隱式游標和顯式游標。 掌握如何創(chuàng)建游標 會編寫游標式的FOR循環(huán) 會編寫帶有參數(shù)的游標 討論PL/SQL異常及其類型 異常聲明、觸發(fā)和處理過程,本章要點,3,游標,Oracle 服務器所執(zhí)行的每一個 SQL 語句都有唯一的游標與之相對應： 隱式游標：為每一個DML語句和PL/SQL語句都聲明一個隱式的游標。 顯式游標: 由程序員聲明并命名。,4,顯式游標的功能,活動集,當前行,7369SMITHCLERK 7566JONESMANAGER 7788SCOTTANALYST 7876ADAMSCLERK

2、 7902FORDANALYST,5,顯式游標的控制,創(chuàng)建一個命名的SQL區(qū),DECLARE,標識活動集,把當前行載入變量中,測試現(xiàn)有的行,如果找到行則返回到FETCH語句,No,釋放活動集,6,顯式游標的控制,打開游標,從游標中提取一行,為空時退出提取,關(guān)閉游標,7,游標的聲明,語法: 在游標聲明中不能使用INTO子句。 如果需要按指定的次序處理行，可在查詢中使用ORDER子句。,CURSOR cursor_name IS select_statement;,8,游標的聲明,舉例：,DECLARE CURSOR emp_cursor IS SELECT empno, ename FROM e

3、mp; CURSOR dept_cursor IS SELECT * FROM dept WHERE deptno = 10; BEGIN .,9,打開游標,語法： 打開游標來執(zhí)行查詢并標識活動集。 如果沒有要返回的行，不會出現(xiàn)異常。 提取后，使用游標的屬性檢測輸出。,OPENcursor_name;,10,從游標中提取數(shù)據(jù),語法: 檢索當前行,把值賦給變量。 包含相同數(shù)量的變量。 將每一個變量和相應的列進行位置匹配。 檢測游標中是否包含行。,FETCH cursor_name INTOvariable1, variable2, . | record_name;,11,從游標中提取數(shù)據(jù),舉例,

4、FETCH emp_cursor INTO v_empno, v_ename;,. OPEN defined_cursor; LOOP FETCH defined_cursor INTO defined_variables EXIT WHEN .; . - Process the retrieved data . END;,12,從游標中提取數(shù)據(jù),依次檢索10個員工：,DECLARE v_empno emp.empno %TYPE; v_ename emp.ename %TYPE; i NUMBER : = 1; CURSOR c1 IS SELECT empno , ename FROM e

5、mp; BEGIN OPEN c1; FOR i IN 1 . . 10 LOOP FETCH c1 INTO v_empno , v_ename; . . . END LOOP ; END;,13,關(guān)閉游標,語法： 處理完活動集中的數(shù)據(jù)后，應該關(guān)閉游標。 如果需要，可以再次打開該游標。 游標一旦關(guān)閉，不可再從游標中提取數(shù)據(jù)。,CLOSEcursor_name;,14,顯式游標的屬性,屬性類型 描述 %ISOPEN布爾 如果游標是打開的，其值為TRUE %NOTFOUND布爾 如果FETCH 語句沒有返回記錄，其 值為TRUE %FOUND布爾 如果FETCH 語句返回一行記錄，其值 為TRU

6、E；與%NOTFOUND相反 %ROWCOUNT 數(shù)量返回迄今為止已經(jīng)從游標中取出的記 錄數(shù)目,注: 不能直接在 SQL 語句中引用游標的屬性。,15,多重提取的控制,使用循環(huán)從顯式游標中處理多行數(shù)據(jù)。 每次僅提取一行數(shù)據(jù)。 使用 %NOTFOUND 屬性來檢測一個不成功的提取操作。 使用顯式游標的屬性檢測每一次提取是否成功。,16,%ISOPEN 屬性,僅當游標處于打開狀態(tài)時才可以從中提取數(shù)據(jù)。 在執(zhí)行提取操作之前，使用 %ISOPEN 游標屬性，檢測游標是否已被打開 舉例：,IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor; END IF; LOO

7、P FETCH emp_cursor.,17,%NOTFOUND 和 %ROWCOUNT 屬性,使用 %ROWCOUNT 游標屬性檢測行的確切數(shù)量。 使用 %NOTFOUND 游標屬性決定何時退出循環(huán)。,18,游標屬性舉例,依次檢索前10個員工,DECLARE v_empno emp.empno %TYPE; v_ename emp.ename %TYPE; CURSOR c1 IS SELECT empno , ename FROM emp; BEGIN OPEN c1; LOOP FETCH c1 INTO v_empno , v_ename ; EXIT WHEN c1%ROWCOUNT

8、 10 OR c1%NOTFOUND; . . . END LOOP ; CLOSE c1; END;,19,游標和記錄,將提取的行值存入一個PL/SQL RECORD 中能方便地處理活動集中的行。 舉例：,DECLARE CURSOR emp_cursor IS SELECTempno, ename FROMemp; emp_recordemp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_record; .,20,語法： 游標式的 FOR 循環(huán)可以更方便地處理顯式游標。 隱式地打開，提取和關(guān)閉游標

9、。 隱式聲明記錄。,游標式的FOR循環(huán),FOR record_name IN cursor_name LOOP statement1; statement2; . . . END LOOP;,21,含有游標的FOR循環(huán),舉例： 依次檢索所有員工,DECLARE CURSOR emp_cursor IS SELECT ename, deptno FROM emp; BEGIN FOR emp_record IN emp_cursor LOOP - implicit open and implicit fetch occur IF emp_record.deptno = 30 THEN . END

10、 LOOP; - implicit close occurs END;,22,使用子查詢的游標式FOR循環(huán),不需聲明游標。 舉例：,BEGIN FOR emp_record IN (SELECT ename, deptno FROM emp) LOOP - implicit open and implicit fetch occur IF emp_record.deptno = 30 THEN . END LOOP; - implicit close occurs END;,23,帶有參數(shù)的游標,語法： 在游標處于打開狀態(tài)時，執(zhí)行查詢語句，將參數(shù)的值傳遞給游標。 多次打開顯式變量，每次活動集都

11、不同。,CURSOR cursor_name (parameter_name datatype, .) IS select_statement;,24,帶有參數(shù)的游標,舉例： 將部門號和工作名傳遞給WHERE子句。,DECLARE CURSOR emp_cursor (p_deptno NUMBER, p_job VARCHAR2) IS SELECTempno, ename FROMemp WHEREdeptno = v_deptno AND job = v_job; BEGIN OPEN emp_cursor(10, CLERK); .,25,帶有參數(shù)的游標,DECLARE job_emp

12、 emp . job %TYPE : = CLERK ; v_ename emp . ename %TYPE; CURSOR c1 (v_deptno NUMBER , v_job VARCHAR2) IS SELECT . . . 打開游標：OPEN c1 (10, job_emp) ;OPEN c1 (20, ANALYST) ; 可以在游標式 FOR 循環(huán)中將參數(shù)傳遞給游標： DECLARE CURSOR c1 (v_deptno NUMBER , v_job VARCHAR2) IS SELECT . . . BEGIN FOR emp record IN c1 (10, ANALYS

13、T) LOOP . . .,26,FOR UPDATE 子句,語法： 在事務執(zhí)行期間可以顯式鎖定以拒絕訪問。 在更新或刪除行時要鎖定該行。,SELECT. FROM. FOR UPDATE OF column_referenceNOWAIT;,27,FOR UPDATE 子句,舉例：,DECLARE CURSOR emp_cursor IS SELECT empno, ename, sal FROM emp WHERE deptno = 30 FOR UPDATE OF sal NOWAIT;,28,WHERE CURRENT OF 子句,語法： 使用游標更新或刪除當前行。 首先要在游標中使用

14、 FOR UPDATE 子句鎖定行。 使用 WHERE CURRENT OF 子句從顯式游標中引用當前行,WHERE CURRENT OF cursor ;,29,WHERE CURRENT OF 子句,DECLARE CURSOR sal_cursor IS SELECT sal FROMemp WHEREdeptno = 30 FOR UPDATE OF sal NOWAIT; BEGIN FOR emp_record IN sal_cursor LOOP UPDATEemp SET sal = emp_record.sal * 1.10 WHERE CURRENT OF sal_curs

15、or; END LOOP; COMMIT; END;,舉例：,30,使用子查詢的游標,DECLARE CURSOR my_cursor IS SELECT t1.deptno, t1.dname, t2.STAFF FROM dept t1, (SELECT deptno, count(*) STAFF FROM emp GROUP BY deptno) t2 WHERE t1.deptno = t2.deptno AND t2.STAFF = 5;,舉例：,31,小結(jié),游標類型： 隱式游標：為每一個DML語句和單行查詢都聲明一個隱式的游標。 顯式游標：用來查詢空行、一行或多行。 可以操作顯式

16、游標。 使用游標屬性可以檢測游標的狀態(tài)。 可以使用游標式的FOR循環(huán)。,32,小結(jié),使用帶有參數(shù)的游標可以返回不同的活動集。 可以定義帶有子查詢或有相互聯(lián)系的子查詢的游標。 可以使用下面的命令操作顯式游標： FOR UPDATE 子句 WHERE CURRENT OF 子句,33,處理 PL/SQL異常,什么是異常?在PL/SQL中的一個警告或錯誤都是異常。 異常是如何觸發(fā)的? 發(fā)生了一個 Oracle 錯誤時。 使用RAISE語句顯式觸發(fā)。 如何處理異常? 用處理機截獲。 在調(diào)用環(huán)境中傳播異常。,34,異常處理,截獲異常,觸發(fā)異常,截獲異常,異常的傳播,觸發(fā)異常,異常沒有被截獲,異常傳播給了

17、調(diào)用環(huán)境,35,異常的類型,Oracle 服務器的預定義異常 Oracle 服務器沒有預定義的異常 自定義異常,顯式觸發(fā),36,截獲異常,EXCEPTION WHEN exception1 OR exception2 . . . THEN statement1; statement2; . . . WHEN exception3 OR exception4 . . . THEN statement1; statement2; . . . WHEN OTHERS THEN statement1; statement2; . . .,語法：,37,截獲異常的原則,WHEN OTHERS 是最后一個

18、子句。 異常處理部分從關(guān)鍵字EXCEPTION開始。 允許有多個異常處理機。 在離開塊之前只能執(zhí)行一個處理機。,38,截獲Oracle服務器預定義的錯誤,在相應的異常處理例程中引用錯誤的標準名。 一些預定義異常： NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR ZERO_DIVIDE DUP_VAL_ON_INDEX,39,預定義異常,BEGIN EXCEPTION WHEN NO_DATA_FOUND THEN statement1; statement2; WHEN TOO_MANY_ROWS THEN statement1; WHEN OTHERS

19、THEN statement1; statement2; statement3; END;,語法：,40,截獲Oracle 服務器沒有預定義的錯誤,命名異常,編譯指令 PRAGMA EXCEPTION_INIT,聲明部分,處理被觸發(fā)的異常,異常處理部分,41,DECLARE e_emps_remainingEXCEPTION; PRAGMA EXCEPTION_INIT ( e_emps_remaining, -2292); v_deptno dept.deptno%TYPE := ,沒有預定義的錯誤,截獲 Oracle 代碼為2292的違反完整性約束的錯誤。,1,2,3,42,截獲異常的函數(shù)

20、,SQLCODE 返回Oracle的錯誤代碼 SQLERRM 返回Oracle的錯誤消息,43,截獲異常的函數(shù),DECLARE v_error_code NUMBER; v_error_message VARCHAR2(255); BEGIN . EXCEPTION . WHEN OTHERS THEN ROLLBACK; v_error_code := SQLCODE ; v_error_message := SQLERRM ; INSERT INTO errors VALUES(v_error_code, v_error_message); END;,舉例：,44,截獲自定義的異常,聲明部

21、分,執(zhí)行部分,異常處理部分,45,自定義異常,DECLARE e_invalid_product EXCEPTION; BEGIN UPDATEproduct SETdescrip = ,舉例：,1,2,3,46,調(diào)用環(huán)境,SQL*Plus Procedure Builder OracleDeveloperForms Precompilerapplication An enclosing PL/SQL block,在屏幕上顯示錯誤代碼和錯誤消息 在屏幕上顯示錯誤代碼和錯誤消息 使用ERROR_CODE和 ERROR_TEXT 包函數(shù)訪問錯誤代碼和錯誤消息 通過SQLCA 數(shù)據(jù)結(jié)構(gòu)訪問異常代碼 在外部塊的異常處理例程中截獲異常,47,異常的傳播,BEGIN SELECT . UPDATE . IF SQL%NOTFOUND THEN RAISE e_no_rows; END IF; EXCEPTION WHEN e_integrity THEN . WHEN e_no_rows THEN . END;,DECLARE . . . e_no_rowsexception; e_integrityexception; PRAGMA EXCEPTION_INIT (e_integrity, -2292); BEGIN FO