Oracle数据库pl/sql显式抛出异常

在Oracle PL/SQL中，显式地抛出异常（Raising Exceptions Explicitly）是一种控制程序流程和处理错误的重要机制。当你希望在某些特定条件下中断程序的执行，并通知调用者发生了错误或异常情况时，可以使用这种机制。下面是如何在PL/SQL中显式地抛出异常的几个步骤：

Raising Exceptions Explicitly
To raise an exception explicitly, use either the RAISE statement or RAISE_APPLICATION_ERROR procedure.

Topics

• RAISE Statement
• RAISE_APPLICATION_ERROR Procedure

1. 定义异常

首先可以定义一个自定义异常。在PL/SQL中，自定义异常是通过EXCEPTION关键字声明的，但它本身并不包含任何错误信息或代码，它只是一个占位符，用于在后续的代码中被触发（抛出）。

DECLARE
  -- 定义一个自定义异常
  my_custom_exception EXCEPTION;
  -- 其他变量声明...
BEGIN
  -- 逻辑处理...
  
  -- 假设这里有一个条件需要抛出异常
  IF some_condition THEN
    RAISE my_custom_exception;
  END IF;

EXCEPTION
  WHEN my_custom_exception THEN
    -- 处理异常
    DBMS_OUTPUT.PUT_LINE('自定义异常被触发');
    -- 可以添加更多的错误处理逻辑
END;

In Example, the procedure declares an exception named past_due, raises it explicitly with the RAISE statement, and handles it with an exception handler.

CREATE PROCEDURE sp_account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
  past_due  EXCEPTION;  -- declare exception
BEGIN
  IF due_date < today THEN
    RAISE past_due;  -- explicitly raise exception
  END IF;
EXCEPTION
  WHEN past_due THEN  -- handle exception
    DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/
 
BEGIN
  sp_account_status (TO_DATE('2024-09-01', 'yyyy-mm-dd'),TO_DATE('2024-09-20', 'yyyy-mm-dd'));
END;
/
-- Run Result:
Account past due.

PL/SQL procedure successfully completed.

2. 使用预定义的异常

Oracle PL/SQL也提供了一系列预定义的异常( Explicitly Raising Predefined Exception)，这些异常在Oracle的数据库操作中自动被抛出，例如NO_DATA_FOUND（当SELECT INTO语句未返回任何行时）或VALUE_ERROR（当尝试将值赋给变量时，该值超出了变量的允许范围）。

你可以直接在EXCEPTION部分捕获这些预定义的异常，但你不能直接RAISE它们，因为它们是由Oracle在内部自动触发的。

BEGIN
  -- 尝试执行一个可能不返回数据的SELECT INTO
  SELECT column_name INTO variable_name FROM table_name WHERE condition;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('未找到数据');
END;

In Example, the procedure raises the predefined exception INVALID_NUMBER either explicitly or implicitly, and the INVALID_NUMBER exception handler always handles it.

DROP TABLE t_predefine_exception;
CREATE TABLE t_predefine_exception (col_01 NUMBER);
 
CREATE OR REPLACE PROCEDURE sp_predefine_exception (n  NUMBER) AUTHID DEFINER IS
  default_number NUMBER := 0;
BEGIN
  IF n < 0 THEN
    RAISE INVALID_NUMBER;  -- raise explicitly
  ELSE
    INSERT INTO t_predefine_exception VALUES(TO_NUMBER('100.00', '9G999'));  -- raise implicitly
  END IF;
EXCEPTION
  WHEN INVALID_NUMBER THEN
    DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.');
    INSERT INTO t_predefine_exception VALUES(default_number);
END;
/
 
BEGIN
  sp_predefine_exception(-1);
END;
/
-- Run Result
Substituting default value for invalid number.

PL/SQL procedure successfully completed.

-- query table 
TESTUSER@FREEPDB1> select * from t_predefine_exception;

    COL_01
----------
         0
 

调用过程，参数传入1,还是一样的结果，因为TO_NUMBER默认不支持逗号作为千位分隔符，所有TO_NUMBER(‘100.00’, ‘9G999’)转换错误，ORA-01722: unable to convert string value containing ‘1’ to a number:因此还是使用预定义的异常。

BEGIN
  sp_predefine_exception(1);
END;
/
-- Run Result
Substituting default value for invalid number.

PL/SQL procedure successfully completed.

-- query table
TESTUSER@FREEPDB1> select * from t_predefine_exception;

    COL_01
----------
         0
         0

用RAISE语句重新引发当前异常
Reraising Current Exception with RAISE Statement
In Example, the handling of the exception starts in the inner block and finishes in the outer block. The outer block declares the exception, so the exception name exists in both blocks, and each block has an exception handler specifically for that exception. The inner block raises the exception, and its exception handler does the initial handling and then reraises the exception, passing it to the outer block for further handling.

DECLARE
  salary_too_high   EXCEPTION;
  current_salary    NUMBER := 20000;
  max_salary        NUMBER := 10000;
  erroneous_salary  NUMBER;
BEGIN

  BEGIN
    IF current_salary > max_salary THEN
      RAISE salary_too_high;   -- raise exception
    END IF;
  EXCEPTION
    WHEN salary_too_high THEN  -- start handling exception
      erroneous_salary := current_salary;
      DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary ||' is out of range.');
      DBMS_OUTPUT.PUT_LINE ('Maximum salary is ' || max_salary || '.');
      RAISE;  -- reraise current exception (exception name is optional)
  END;

EXCEPTION
  WHEN salary_too_high THEN    -- finish handling exception
    current_salary := max_salary;

    DBMS_OUTPUT.PUT_LINE (
      'Revising salary from ' || erroneous_salary ||
      ' to ' || current_salary || '.'
    );
END;
/
-- Run Result
Salary 20000 is out of range.
Maximum salary is 10000.
Revising salary from 20000 to 10000.

PL/SQL procedure successfully completed.

3. 使用RAISE_APPLICATION_ERROR抛出带有错误信息的异常

RAISE_APPLICATION_ERROR过程允许你抛出一个带有自定义错误信息的异常。这对于向客户端或上层调用者提供更多关于错误的信息非常有用。

DECLARE
  -- 自定义错误号，范围在-20000到-20999之间
  error_number CONSTANT INTEGER := -20001;
BEGIN
  -- 假设这里有一个条件需要抛出带有错误信息的异常
  IF some_condition THEN
    RAISE_APPLICATION_ERROR(error_number, '这里是一个自定义的错误信息');
  END IF;
END;

使用RAISE_APPLICATION_ERROR时，你可以指定一个错误号（一个负整数，Oracle PL/SQL预定义的范围是从-20000到-20999），以及一个描述错误的字符串。

在例，一个匿名块声明了一个名为past_due的异常，为其分配了错误码-20000，并调用了一个存储过程。存储过程用错误码-20000和一条消息调用RAISE_APPLICATION_ERROR过程，然后控制返回到匿名块，由匿名块处理异常。为了检索与异常相关的消息，匿名块中的异常处理程序调用SQLERRM函数，该函数在“检索错误代码和错误消息”中描述。
In Example, an anonymous block declares an exception named past_due, assigns the error code -20000 to it, and invokes a stored procedure. The stored procedure invokes the RAISE_APPLICATION_ERROR procedure with the error code -20000 and a message, whereupon control returns to the anonymous block, which handles the exception. To retrieve the message associated with the exception, the exception handler in the anonymous block invokes the SQLERRM function, described in “Retrieving Error Code and Error Message”.

CREATE OR REPLACE PROCEDURE sp_account_status_errorcode (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
BEGIN
  IF due_date < today THEN                   -- explicitly raise exception
    RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
  END IF;
END;
/
 
DECLARE
  past_due  EXCEPTION;                       -- declare exception
  PRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception
BEGIN
  sp_account_status_errorcode (TO_DATE('2024-09-01', 'yyyy-mm-dd'),TO_DATE('2024-09-20', 'yyyy-mm-dd'));   -- invoke procedure

EXCEPTION
  WHEN past_due THEN                         -- handle exception
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/
-- Run Result
ORA-20000: Account past due.

PL/SQL procedure successfully completed.

4、 结论

在Oracle PL/SQL中，显式地抛出异常是处理错误和异常情况的重要技术。通过定义自定义异常、使用预定义的异常，以及使用RAISE_APPLICATION_ERROR过程，你可以更灵活地控制程序的执行流程，并向调用者提供有用的错误信息。