What are the steps for handling Oracle exceptions?

2 years ago
Isabella Edwards
3 minutes

The steps for Oracle exception handling are as follows: 1. Detecting exceptions: Statements executed in a program may trigger exceptions. In Oracle, exceptions are known as “exception conditions”. When an exception condition occurs, Oracle automatically raises the corresponding exception. 2. Catching exceptions: To handle exceptions, the program needs to catch these exceptions. In PL/SQL, you can use an EXCEPTION block to catch exceptions. The EXCEPTION block will execute the corresponding code block when an exception occurs. 3. Handling exceptions: After catching an exception, you can handle the exception. Handling exceptions can include logging exception information, rolling back transactions, re-raising exceptions, etc. Depending on the specific requirements, you can execute the appropriate handling logic in the EXCEPTION block. 4. Ending exception handling: Once exception handling is complete, the program will continue to execute the next set of code. It is important to note that in PL/SQL, you can use multiple EXCEPTION blocks to handle different types of exceptions. Each exception block can specify different handling logic. Below is a simple example demonstrating the steps of Oracle exception handling.

DECLARE
   -- 声明自定义异常
   emp_not_found EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_not_found, -1403);
   
   -- 定义变量
   emp_id NUMBER := 1000;
   emp_name VARCHAR2(100);
BEGIN
   -- 查询员工姓名
   SELECT last_name INTO emp_name
   FROM employees
   WHERE employee_id = emp_id;
   
   -- 打印员工姓名
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
   
EXCEPTION
   -- 处理自定义异常
   WHEN emp_not_found THEN
      DBMS_OUTPUT.PUT_LINE('Employee Not Found: ' || emp_id);
  
   -- 处理其他异常
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;

In the example above, the program tries to query the name of an employee that does not exist. If the employee is not found, a custom exception emp_not_found is raised. This exception is caught and handled appropriately in the EXCEPTION block. If any other exception occurs, it will be handled in the OTHERS block.

bannerAds