[Oracle] PL/SQL 例外處理

前言

現在的程式語言中，幾乎都提供了例外處理的功能，當然 PL/SQL 也不能例外，除了提供例外處理的功能外，還可以讓使用者自訂例外喔!

 

Oracle 內建的例外種類

其實在 Oracle 當中就已經內建了許多例外的定義了，例如：NO_DATA_FOUND(當 SELECT INTO 的語法沒有回傳任何資料列時會觸發)、CASE_NOT_FOUND(當條件中沒有任何一項符合且沒有 ELSE 選項時會觸發)，詳細的內建例外說明，可以參考官方網站的文件。

而例外的使用方式如下：
<br />EXCEPTION<br /> WHEN exception_name_1 THEN --捕捉第一個例外<br /> --例外處理<br /> ...;<br /> DBMS_OUTPUT.PUT_LINE(message_1);<br /> ...<br /> WHEN OTHERS THEN --捕捉沒定義的例外<br /> --例外處理<br /> ...<br /> DBMS_OUTPUT.PUT_LINE(message_others);<br />

 

以下用個範例說明如何使用 Oracle 的內建範例：
<br />PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS<br /> --宣告 cursor variable (型態為 package specification 中定義的 emp_refcursor_type)<br /> emp_cursor emp_refcursor_type;<br /> <br /> --目前正在處理的部門ID<br /> department_curr departments.department_id%TYPE; <br />BEGIN<br /> department_curr := department_id;<br /> FOR loop_c IN 1..3 LOOP<br /> --開啟 cursor<br /> OPEN emp_cursor FOR<br /> SELECT *<br /> FROM employees e<br /> WHERE e.department_id = department_curr;<br /> <br /> <br /> eval_loop_control(emp_cursor);<br /> <br /> DBMS_OUTPUT.PUT_LINE('Determining necessary evaluations in department #' || department_curr);<br /> <br /> --關閉 cursor<br /> CLOSE emp_cursor;<br /> <br /> department_curr := department_curr + 10;<br /> END LOOP;<br /> <br /> --定義錯誤處理的實作<br /> EXCEPTION<br /> WHEN NO_DATA_FOUND THEN<br /> DBMS_OUTPUT.PUT_LINE('The query did noy return a result set');<br /> WHEN OTHERS THEN<br /> DBMS_OUTPUT.PUT_LINE('Other exception occurs');<br />END eval_department;<br />

 

自訂例外

自訂的例外在宣告上很有彈性，可以放在 subprogram 中，也可以放在 package specification 或是 package body 中，完全隨使用者方便。

而自訂例外的語法如下：
<br />--自訂例外<br />exception_name EXCEPTION;<br /><br />--設定例外發生條件以觸發例外<br />IF condition THEN<br /> RAISE exception_name;<br />

接著是自訂例外觸發條件與例外處理的範例：
<br />--FUNCTION 的實作內容<br />FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE, <br /> performance_id IN scores.performance_id%TYPE) RETURN NUMBER AS<br /> --自訂例外<br /> weight_wrong EXCEPTION;<br /> score_wrong EXCEPTION;<br /> <br /> n_score scores.score%TYPE; --VARIABLE<br /> n_weight performance_parts.weight%TYPE; --VARIABLE<br /> running_total NUMBER := 0; --計算中使用<br /> max_score CONSTANT scores.score%TYPE := 9; --CONSTANT<br /> max_weight CONSTANT performance_parts.weight%TYPE := 1; --CONSTANT<br />BEGIN<br /> --實作於此處<br /> SELECT s.SCORE INTO n_score --設定 n_score 的值 <br /> FROM SCORES s<br /> WHERE evaluation_id = s.evaluation_id;<br /><br /> SELECT p.weight INTO n_weight --設定 n_weight 的值<br /> FROM performance_parts p<br /> WHERE performance_id = p.performance_id;<br /><br /> --設定觸發例外的條件 ===== (start)<br /> BEGIN<br /> IF n_weight > max_weight OR n_weight < 0 THEN<br /> RAISE weight_wrong;<br /> END IF;<br /> END;<br /> BEGIN<br /> IF n_score > max_score OR n_score < 0 THEN<br /> RAISE score_wrong;<br /> END IF;<br /> END;<br /> --設定觸發例外的條件 ===== (end)<br /> <br /> --計算 running_total<br /> running_total := max_score * max_weight;<br /><br /> --回傳值<br /> RETURN running_total;<br /> <br /> --自訂例外處理<br /> EXCEPTION<br /> WHEN weight_wrong THEN<br /> DBMS_OUTPUT.PUT_LINE('重量必須介於 0 與 ' || max_weight || ' 之間');<br /> RETURN -1;<br /> WHEN score_wrong THEN<br /> DBMS_OUTPUT.PUT_LINE('分數必須介於 0 與 ' || max_score || ' 之間');<br /> RETURN -1;<br />END CALCULATE_SCORE;<br />