存储过程异常处理

子程序很像一段小型的程序，由可选的声明部分，
执行控制部分和可选的异常控制部分组成。

 

PROCEDURE award_bonus(emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT comm * 0.15
INTO bonus
FROM emp
WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE payroll
SET pay = pay + bonus
WHERE empno = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
...

    EXCEPTION
    --log
    WHEN OTHERS THEN
      v_ErrorCode := SQLCODE;
      v_ErrorText := SUBSTR(SQLERRM, 1, 2000);
      insert into t_l4_log
        (sid,
         TABLENAME,
         START_TIME,
         END_TIME,
         LOG_INFO)
      values
        (t_l4_log_seq.nextval,
         't_l4_pdt_specific',
         start_date,
         sysdate,
         v_ErrorCode || '::' || v_ErrorText||org_code_error||atnam_error||ATWRT_error);
      --log 
END award_bonus;