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 THENRAISE my_custom_exception;END IF;EXCEPTIONWHEN 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
ISpast_due  EXCEPTION;  -- declare exception
BEGINIF due_date < today THENRAISE past_due;  -- explicitly raise exceptionEND IF;
EXCEPTIONWHEN past_due THEN  -- handle exceptionDBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/BEGINsp_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 INTOSELECT column_name INTO variable_name FROM table_name WHERE condition;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_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 ISdefault_number NUMBER := 0;
BEGINIF n < 0 THENRAISE INVALID_NUMBER;  -- raise explicitlyELSEINSERT INTO t_predefine_exception VALUES(TO_NUMBER('100.00', '9G999'));  -- raise implicitlyEND IF;
EXCEPTIONWHEN INVALID_NUMBER THENDBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.');INSERT INTO t_predefine_exception VALUES(default_number);
END;
/BEGINsp_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:因此还是使用预定义的异常。

BEGINsp_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
----------00

用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.

DECLAREsalary_too_high   EXCEPTION;current_salary    NUMBER := 20000;max_salary        NUMBER := 10000;erroneous_salary  NUMBER;
BEGINBEGINIF current_salary > max_salary THENRAISE salary_too_high;   -- raise exceptionEND IF;EXCEPTIONWHEN salary_too_high THEN  -- start handling exceptionerroneous_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;EXCEPTIONWHEN salary_too_high THEN    -- finish handling exceptioncurrent_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 THENRAISE_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
BEGINIF due_date < today THEN                   -- explicitly raise exceptionRAISE_APPLICATION_ERROR(-20000, 'Account past due.');END IF;
END;
/DECLAREpast_due  EXCEPTION;                       -- declare exceptionPRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception
BEGINsp_account_status_errorcode (TO_DATE('2024-09-01', 'yyyy-mm-dd'),TO_DATE('2024-09-20', 'yyyy-mm-dd'));   -- invoke procedureEXCEPTIONWHEN past_due THEN                         -- handle exceptionDBMS_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过程，你可以更灵活地控制程序的执行流程，并向调用者提供有用的错误信息。