oracle 当其他人然后异常处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27040996/
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
when others then exception handling
提问by Andrew Martin
Background:
背景：
I've used a few Oracle articles to develop an error package, which consists of five procedures.
我用几篇Oracle文章开发了一个错误包，它由五个程序组成。
Two of these are Log_And_Returnand Log_And_Continue. They are called throughout the program. Each takes input and passes it to the Handleprocedure. For example:
其中两个是Log_And_Return和Log_And_Continue。它们在整个程序中都会被调用。每个都接受输入并将其传递给Handle过程。例如：
PROCEDURE Log_And_Return (error_name)
IS
BEGIN
Handle (error_name, TRUE, TRUE);
END Log_And_Return;
The Handleprocedure then calls the Logprocedure and the Raise_To_Applicationprocedure depending on the variables passed to it, like so:
该手柄则过程调用日志程序和Raise_To_Application根据传递给它，像这样的变量的过程：
PROCEDURE Handle (error_name, log_error, reraise_error)
IS
BEGIN
// Code to fetch error code and message using error_name input parameter.
IF log_error THEN
LOG (error_code, error_message);
END IF;
IF in_reraise_error THEN
Raise_To_Application (error_code, error_message);
END IF;
END Handle;
The log procedure stores the date, stacktrace, error code, error message and id and finally the Raise_To_Applicationprocedure does what is says:
日志过程存储日期、堆栈跟踪、错误代码、错误消息和 ID，最后Raise_To_Application过程执行如下操作：
raise_application_error (error_code, error_message);
Problem:
问题：
My problem is this. Let's say I have a procedure, which performs a query, e.g. fetching a customer record. If this query fails, it's a big problem. So I could do this:
我的问题是这个。假设我有一个过程，它执行查询，例如获取客户记录。如果这个查询失败，那就是一个大问题。所以我可以这样做：
BEGIN
SELECT *something*
FROM *some table*
WHERE *some field* = *some user input*
// more logic
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR.Log_And_Return('unknown_id');
WHEN OTHERS THEN
ERR.Log_And_Return('unknown_error');
END;
Here, my Log_And_Returnprocedure takes the input, goes off to a table and returns a string to display to the user. I've a specific error for if the query doesn't find the user's record and a generic error for an unknown error. In both cases, logging is performed which takes the full stacktrace of the error.
在这里，我的Log_And_Return过程接受输入，转到表格并返回一个字符串以显示给用户。如果查询没有找到用户的记录，我有一个特定的错误，一个未知错误的一般错误。在这两种情况下，都会执行日志记录，获取错误的完整堆栈跟踪。
However, in my example I've got a "// more logic" section. Let's say, I amend the code to this:
但是，在我的示例中，我有一个“// 更多逻辑”部分。假设，我将代码修改为：
BEGIN
SELECT *something* INTO *some variable*
FROM *some table*
WHERE *some field* = *user id*
Call_Another_Procedure(*user id*, *some variable*)
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR.Log_And_Return('unknown_id');
WHEN OTHERS THEN
ERR.Log_And_Return('unknown_error');
END;
Now, after the select query, I'm calling another procedure with the result of the select query. Inside this newquery, I'm doing a few things, including an updatestatement, like so:
现在，在选择查询之后，我使用选择查询的结果调用另一个过程。在这个新查询中，我正在做一些事情，包括一个更新语句，如下所示：
// bunch of logic
BEGIN
UPDATE *another table*
SET *some field* = *some value*
WHERE *some field* = *variable passed into method*
EXCEPTION
WHEN NO_DATA_FOUND THEN
Err.Log_And_Return('some_error')
END;
Question:
题：
My problem here is that I throw the NO_DATA_FOUNDerror if the query returns no results, I log the problem and I then raise an application error in my "Raise_To_Application" procedure... which will then be caught by the "when others" clause in the parent procedure, which will return the wrong message to the user.
我的问题是，如果查询没有返回结果，我会抛出NO_DATA_FOUND错误，我记录问题，然后在我的“Raise_To_Application”过程中引发应用程序错误......然后将被“when others”子句捕获父过程，它将错误消息返回给用户。
What is the workaround to this?Note: If more code needs to be posted, just let me know.
解决方法是什么？注意：如果需要发布更多代码，请告诉我。
Edit:
编辑：
One workaround I had considered, and I've no idea if this is recommended or not, would be to wrap every stored procedure with a BEGIN END EXCEPTION block, where every procedure had a "When Others" block that just logged and reraised the most recent error (i.e. using SQLCODE). Then, in my application layer I could specify that if the error is between -20000 and -20999, show it along with its message, otherwise show a generic message (and the DBA can find out what happened in the database by looking at the log table, along with a full stacktrace). Any thoughts on this?
我考虑过的一种解决方法，我不知道是否推荐这样做，将每个存储过程包装在一个 BEGIN END EXCEPTION 块中，其中每个过程都有一个“When Others”块，该块刚刚记录并重新筹集最多最近的错误（即使用 SQLCODE）。然后，在我的应用程序层中，我可以指定如果错误在 -20000 和 -20999 之间，则将其与其消息一起显示，否则显示一条通用消息（DBA 可以通过查看日志找出数据库中发生的事情表，以及完整的堆栈跟踪）。对此有何想法？
Edit 2:
编辑2：
If anything doesn't make sense, I can clarify. I've heavily changed and simplifier the code to remove things like id parameters and a few other things.
如果有什么不明白的，我可以澄清。我对代码进行了大量更改和简化，以删除诸如 id 参数之类的内容和其他一些内容。
采纳答案by Jeffrey Kemp
This is pretty much the approach I've been using, since I want to log every entry and exit point in my code:
这几乎是我一直在使用的方法，因为我想在我的代码中记录每个入口和出口点：
application_error EXCEPTION;
PRAGMA EXCEPTION_INIT (application_error, -20000);
BEGIN
SELECT *something* INTO *some variable*
FROM *some table*
WHERE *some field* = *user id*
Call_Another_Procedure(*user id*, *some variable*)
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR.Log_And_Return('unknown_id');
WHEN application_error THEN -- ordinary exception raised by a subprocedure
ERR.Log_And_Return('application_error');
RAISE;
WHEN OTHERS THEN
ERR.Log_And_Return('unknown_error');
RAISE;
END;
And for subprocedures:
对于子过程：
BEGIN
UPDATE *another table*
SET *some field* = *some value*
WHERE *some field* = *variable passed into method*
EXCEPTION
WHEN NO_DATA_FOUND THEN
Err.Log_And_Return('some_error'); -- this raises ORA-20000
END;
回答by mikron
For the when_others exceptions consider of using AFTER SERVERERROR triggers. Something like bellow
对于 when_others 异常，请考虑使用AFTER SERVERERROR 触发器。像下面这样的东西
create or replace trigger TRG_SERVERERROR
after servererror on database
declare
<some_variable_for_logging_the_call_stack>
begin
ERR.Log;
end;
I will quote from Tom Kytes when he was permitted to submit three requests for new features in PL/SQL and this is what he say
我会引用 Tom Kytes 的话，他被允许提交三个对 PL/SQL 中的新功能的请求，这就是他所说的
I jumped at the chance. My first suggestion was simply, “Remove the WHEN OTHERS clause from the language.”
我抓住了这个机会。我的第一个建议很简单，“从语言中删除 WHEN OTHERS 子句。”
You can also read the following article from Tom Kyte - Why You Really Want to Let Exceptions Propagate
您还可以阅读Tom Kyte的以下文章- 为什么您真的想让异常传播
UPD: The whole workflow for the solution in your case is the following(in my subjective opinion)
UPD：您的情况下解决方案的整个工作流程如下（我的主观意见）
I'll sugges to Include no WHEN OTHERS. I prefer to receive unfriendly error message, instead of the seamless message - something like "Ooops, something goes wrong.". In the end of the day you can also wrap all the unexpected exceptions to the some message for the user on your application layer and wrap the details about the database, to not be used by 3rd parties, etc.
我会建议不包括其他人。我更喜欢收到不友好的错误消息，而不是无缝消息——比如“哎呀，出了点问题。”。归根结底，您还可以将所有意外异常包装在您的应用程序层上为用户提供的某些消息中，并包装有关数据库的详细信息，以免被 3rd 方使用等。
My suggestion is have some ERR.
我的建议是有一些 ERR。
create or replace package ERR
ci_NoDataFound constant int := -20100;
NoDataFound exception;
pragma exception_init(NoDataFound, -20100);
procedure Raise;
procedure Log;
end P_PRSRELIAB;
In your parent procedure, you will handle the excpetion of the current particular procedure, and no other ones.
在您的父过程中，您将处理当前特定过程的例外，而不是其他过程。
BEGIN
SELECT *something* INTO *some variable*
FROM *some table*
WHERE *some field* = *user id*
Call_Another_Procedure(*user id*, *some variable*)
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR.Raise(-20100, 'unknown user id');
END;
The procedure which is calling from the parent one, will handle only the excpetion of this particular procedure.
从父进程调用的过程将仅处理此特定过程的例外情况。
BEGIN
SELECT *something*
FROM *some table*
WHERE *some field* = *some user input*
EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR.Raise(-20100, 'unknown some user input');
END;
In the application layer, we will have proper messages - "uknown some user input" or "unknown user id". On the other side the trigger will log all the information about the particular exception.
在应用层，我们会有正确的消息——“未知某些用户输入”或“未知用户 ID”。另一方面，触发器将记录有关特定异常的所有信息。
回答by Lalit Kumar B
You need to re-raise the error in the underlying procedures using RAISE.
您需要使用RAISE.
When an erroroccurs, and if you have an exception block, the handle moves to the exception block. The callerwill remain unaware until you re-raiseit using RAISE.
当error发生时，如果您有exception block，则手柄移动到exception block。在caller您re-raise使用RAISE.
keep all the underlying procedures inside BEGIN-ENDblock.
将所有底层过程保存在BEGIN-END块内。
Also, use dbms_utility.format_error_stackand dbms_utility.format_error_backtraceto get the call stack.
此外，使用dbms_utility.format_error_stack和dbms_utility.format_error_backtrace获取调用堆栈。
