EXCEPTION
WHEN first_exception THEN <code to handle first exception >
WHEN second_exception THEN <code to handle second exception >
WHEN OTHERS THEN <code to handle others exception >
END;

--例1：更新指定員工工資，如工資小于1500，則加100；
DECLARE
   v_empno employees.employee_id%TYPE := &empno;
   v_sal   employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno;
   IF v_sal<=1500 THEN 
        UPDATE employees SET salary = salary + 100 WHERE employee_id=v_empno; 
        DBMS_OUTPUT.PUT_LINE(‘編碼為‘||v_empno||‘員工工資已更新!‘);     
   ELSE
        DBMS_OUTPUT.PUT_LINE(‘編碼為‘||v_empno||‘員工工資已經(jīng)超過規(guī)定值!‘);
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN  
      DBMS_OUTPUT.PUT_LINE(‘?dāng)?shù)據(jù)庫中沒有編碼為‘||v_empno||‘的員工‘);
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE(‘程序運(yùn)行錯(cuò)誤!請使用游標(biāo)‘);
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||‘—‘||SQLERRM);
END;

<異常情況> EXCEPTION;

PRAGMA EXCEPTION_INIT(<異常情況>, <錯(cuò)誤代碼>)；

--例2：刪除指定部門的記錄信息，以確保該部門沒有員工。
 
INSERT INTO departments VALUES(50, ‘FINANCE‘, ‘CHICAGO‘);
DECLARE
   v_deptno departments.department_id%TYPE := &deptno;
   deptno_remaining EXCEPTION;--定義一個(gè)異常變量
   PRAGMA EXCEPTION_INIT(deptno_remaining, –2292); /* -2292 是違反一致性約束的錯(cuò)誤代碼 */
BEGIN
   DELETE FROM departments WHERE department_id = v_deptno;
EXCEPTION
   WHEN deptno_remaining THEN 
      DBMS_OUTPUT.PUT_LINE(‘違反數(shù)據(jù)完整性約束!‘);
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||‘—‘||SQLERRM);
END;

<異常情況> EXCEPTION;
RAISE <異常情況>；

--例3：更新指定員工工資，增加100；
 
DECLARE
   v_empno employees.employee_id%TYPE :=&empno;
   no_result  EXCEPTION;-定義異常
BEGIN
   UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;//觸發(fā)異常
   END IF;
EXCEPTION
   WHEN no_result THEN //捕捉異常
      DBMS_OUTPUT.PUT_LINE(‘你的數(shù)據(jù)更新語句失敗了!‘);
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||‘—‘||SQLERRM);

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );

CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER) #函數(shù)檢索指定部門的工資總和
RETURN NUMBER 
AS
  v_sal NUMBER; #函數(shù)get_salary返回v_sal值
BEGIN
  IF p_deptno IS NULL THEN
    RAISE_APPLICATION_ERROR(–20991, '部門代碼為空'); #用戶定義的異常處理
  ELSIF p_deptno<0 THEN
    RAISE_APPLICATION_ERROR(–20992, '無效的部門代碼');
  ELSE
    SELECT SUM(employees.salary) INTO v_sal FROM employees 
    WHERE employees.department_id=p_deptno;
    RETURN v_sal;
  END IF;

DECLARE 
  V_salary NUMBER(7,2);
  V_sqlcode NUMBER;
  V_sqlerr VARCHAR2(512);
  Null_deptno EXCEPTION;  #定義異常情況
  Invalid_deptno EXCEPTION; #定義異常情況
  PRAGMA EXCEPTION_INIT(null_deptno,–20991);  #非預(yù)定義的異常，將它與oracle錯(cuò)誤聯(lián)系起來
  PRAGMA EXCEPTION_INIT(invalid_deptno, –20992); #非預(yù)定義的異常，將它與oracle錯(cuò)誤聯(lián)系起來
BEGIN
  BEGIN
    V_salary :=get_salary(–10);
  EXCEPTION
    WHEN invalid_deptno THEN
      V_sqlcode :=SQLCODE;
      V_sqlerr  :=SQLERRM;
      INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr); #將錯(cuò)誤SQLCODE及SQLERRM存入到表errlog中  
      COMMIT;
  END ;

CREATE OR REPLACE TRIGGER tr_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF :new.first_name IS NULL OR :new.last_name is null THEN
    RAISE_APPLICATION_ERROR(–20000,‘Employee must have a name.‘);
  END IF;