oracle 在 PL/SQL 中出现错误时提供更有意义的消息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1239164/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Providing a more meaningful message when an error is raised in PL/SQL
提问by Adam Paynter
Suppose I have a PL/SQL function that selects one value from a table. If the query returns no records, I wish for the NO_DATA_FOUNDerror to propagate (so that the calling code can catch it), but with a more meaningful error message when SQLERRMis called.
假设我有一个从表中选择一个值的 PL/SQL 函数。如果查询不返回任何记录，我希望NO_DATA_FOUND错误传播（以便调用代码可以捕获它），但在调用时具有更有意义的错误消息SQLERRM。
Here is an example of what I am trying to accomplish:
这是我试图完成的一个例子：
FUNCTION fetch_customer_id(customer_name VARCHAR2) RETURN NUMBER;
customer_id NUMBER;
BEGIN
SELECT customer_id
INTO customer_id
FROM CUSTOMERS
WHERE customer_name = fetch_customer_id.customer_name;
RETURN customer_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
meaningful_error_message := 'Customer named ' || customer_name || ' does not exist';
RAISE;
END;
Is there a way to associate meaningful_error_messagewith the NO_DATA_FOUNDerror?
有没有办法meaningful_error_message与NO_DATA_FOUND错误相关联？
Update:It has been suggested that I use RAISE_APPLICATION_ERRORto raise a custom error code when NO_DATA_FOUNDis encountered. The purpose of this question was to determine if this technique could be avoided so that the calling code can catch NO_DATA_FOUNDerrors rather than a custom error code. Catching NO_DATA_FOUNDseems more semantically correct, but I could be wrong.
更新：有人建议我RAISE_APPLICATION_ERROR在NO_DATA_FOUND遇到时使用自定义错误代码。这个问题的目的是确定是否可以避免这种技术，以便调用代码可以捕获NO_DATA_FOUND错误而不是自定义错误代码。捕捉NO_DATA_FOUND在语义上似乎更正确，但我可能是错的。
回答by Thomas Jones-Low
Use RAISE_APPLICATION_ERROR (-20001, 'your message');
用 RAISE_APPLICATION_ERROR (-20001, 'your message');
This will return an error number -20001, and your message instead of the NO_DATA_FOUND message. Oracle has reserved the error numbers between -20001 and -210000 for user use in their applications, so you won't be hiding another Oracle error by using these numbers.
这将返回错误号 -20001，以及您的消息而不是 NO_DATA_FOUND 消息。Oracle 保留了 -20001 到 -210000 之间的错误编号供用户在其应用程序中使用，因此您不会通过使用这些编号隐藏另一个 Oracle 错误。
EDIT:RAISE_APPLICATION_ERRORis specifically designed to allow you to create your own error messages. So Oracle does not have another method of allowing dynamic error messages. To further refine this you can define your own exception in the package where you define your procedure. Add the following:
编辑：RAISE_APPLICATION_ERROR专门设计用于允许您创建自己的错误消息。因此 Oracle 没有其他允许动态错误消息的方法。为了进一步完善这一点，您可以在定义过程的包中定义自己的异常。添加以下内容：
CUSTOMER_NO_DATA_FOUND EXCEPTION;
EXCEPTION_INIT (CUSTOMER_NO_DATA_FOUND, -20001);
In your procedure code, you do the RAISE_APPLICATION_ERROR, and the client code can do a
WHEN CUSTOMER_NO_DATA_FOUND THENwhich looks better, and they still have the error message captured in SQLERRM.
在您的过程代码中，您执行RAISE_APPLICATION_ERROR，并且客户端代码可以执行
WHEN CUSTOMER_NO_DATA_FOUND THEN看起来更好的一个，并且它们仍然在SQLERRM.
回答by Vincent Malgrat
As suggested by Thomasyou can use RAISE_APPLICATION_ERROR. If you also want to keep the NO_DATA_FOUNDerror on the error stack you can add TRUE as a third parameter to the function:
正如Thomas所建议的，您可以使用RAISE_APPLICATION_ERROR. 如果您还想将NO_DATA_FOUND错误保留在错误堆栈中，您可以将 TRUE 作为第三个参数添加到函数中：
DECLARE
l NUMBER;
BEGIN
SELECT NULL INTO l FROM dual WHERE 1 = 2;
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20001, 'Meaningful Message', TRUE);
END;
ORA-20001: Meaningful Message
ORA-06512: at line 8
ORA-01403: no data found (*)
The line tagged (*) is the original error message.
标记 (*) 的行是原始错误消息。
