我应该在 Oracle Update 语句中使用 Too Many Rows Error 作为异常子句吗？

Should I use the Too Many Rows Error as my exception clause in my Oracle Update statement?

提问by dee

I have a series of update statements that I need to use in my Oracle package. It's rare but there may be an occasional and unavoidable user error that would result in one of the update statements throwing a "Single row sub-query returns one or more rows" Error.

我有一系列更新语句需要在我的 Oracle 包中使用。这种情况很少见，但可能偶尔会出现不可避免的用户错误，这会导致更新语句之一抛出“单行子查询返回一行或多行”错误。

I've been looking into exception handling for oracle PL/SQl and I'm a bit stuck on how and what to use to catch this exception so the package doesn't crash.

我一直在研究 oracle PL/SQl 的异常处理，但我对如何以及使用什么来捕获此异常以便程序包不会崩溃感到有些困惑。

I know of the pre-built "Too Many Rows" exception clause that exists but everything I read seems to say it is used for improper insert statements.

我知道存在预先构建的“Too Many Rows”异常子句，但我读到的所有内容似乎都说它用于不正确的插入语句。

Can I use this as my exception? Or do I need to build my own exception clause. I've never built one myself before and have only a rough idea on where to put everything needed for it.

我可以用它作为我的例外吗？还是我需要建立自己的例外条款。我以前从来没有自己建造过一个，并且对把它需要的所有东西放在哪里只有一个粗略的想法。

The following code is basically how the updates are set up in this particular procedure but for the sake of brevity I'm only using a bare bones example of how it looks.

下面的代码基本上是在这个特定过程中如何设置更新，但为了简洁起见，我只使用一个简单的例子来说明它的外观。

INSERT INTO TempTable... --(Initial insert statement)

UPDATE TempTable t SET t.Row_one = (SELECT (Statement_One))
WHERE T.Row_One is NULL

UPDATE TempTable t SET t.Row_one = (SELECT (Statement_Two))
WHERE T.Row_One is NULL

UPDATE TempTable t SET t.Row_one = (SELECT (Statement_Three))
WHERE T.Row_One is NULL

-- Does the exception clause start here?
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
(What do I tell the Procedure to do here, what am I able to tell it to do?)

--end of updates that need the exception handling

-- more insert statements into other tables based on data from the preceding Temp Table

END;

Will this work or do I need to build a custom exception?

这会起作用还是我需要构建自定义异常？

Thanks in advance.

提前致谢。

回答by DCookie

First, the TOO_MANY_ROWS exception will not catch the case where your select statements return multiple rows. The TOO_MANY_ROWS exception is for ORA-01422 when you issue a SELECT .. INTO statement that returns more than one row. The exception you'll encounter in your case is ORA-01427, Single row subquery returns more than one row.

首先， TOO_MANY_ROWS 异常不会捕获您的选择语句返回多行的情况。TOO_MANY_ROWS 异常适用于 ORA-01422，当您发出返回多行的 SELECT .. INTO 语句时。您在这种情况下会遇到的例外是 ORA-01427，单行子查询返回多于一行。

If you want to handle this specific error in your procedure, use the EXCEPTION_INIT pragma to associate an exception name with the error:

如果要在过程中处理此特定错误，请使用 EXCEPTION_INIT 编译指示将异常名称与错误相关联：

too_many_values EXCEPTION;
PRAGMA EXCEPTION_INIT(too_many_values, -1427);

Then you can reference this name in your exception handler:

然后你可以在你的异常处理程序中引用这个名字：

EXCEPTION
    WHEN TOO_MANY_VALUES THEN
       {perform your handler here}

What you put in the handler depends on what your procedure does. Many times you'll want to return some sort of error code/message to your caller:

您在处理程序中放入的内容取决于您的程序执行的操作。很多时候你会想要向调用者返回某种错误代码/消息：

PROCEDURE my_proc(p_one VARCHAR2, p_err OUT VARCHAR2) IS
    too_many_values EXCEPTION;
    PRAGMA EXCEPTION_INIT(too_many_values, -1427);
BEGIN
...
EXCEPTION
   WHEN TOO_MANY_VALUES THEN
      p_err := 'More than one value available to assign in the update';
      RAISE;  -- re-raise the exception for the caller

   WHEN OTHERS THEN
      p_err := SQLERRM;  -- return the oracle message for the unexpected error
      RAISE;
END;

Another approach is to skip the specific exception handlers and return generic oracle messages in the WHEN OTHERS handler:

另一种方法是跳过特定的异常处理程序并在 WHEN OTHERS 处理程序中返回通用的 oracle 消息：

EXCEPTION
  WHEN OTHERS THEN
    p_err := SQLERRM;
END;

The advantage with the first approach is you can customize your messages to be more end-user friendly when the output from the process is fed directly back to the user. The advantage with the latter approach is there is less coding involved. Error handling is an important and often skimped on aspect of any application.

第一种方法的优点是，当流程的输出直接反馈给用户时，您可以自定义消息以对最终用户更加友好。后一种方法的优点是涉及的编码较少。错误处理是任何应用程序的一个重要方面，并且经常被忽略。

The documentation from Oracle is here.

Oracle 的文档在此处。

EDIT:

编辑：

If this is a package, and you want to avoid passing a long chain of error variables through a series of procedure calls, you could declare an error variable with package scope, set it when the error is encountered, and RAISE the error again.

如果这是一个包，并且您想避免通过一系列过程调用传递一长串错误变量，您可以声明一个带有包范围的错误变量，在遇到错误时设置它，然后再次引发错误。

PACKAGE BODY my_pkg is
  g_err  VARCHAR2(256);

PROCEDURE procx(... , p_err OUT VARCHAR2) IS...
  ...
  proc_y(p1);
EXCEPTION
  WHEN OTHERS THEN
    p_err := NVL(g_err, SQLERRM);
END;

PROCEDURE proc_y(p1 VARCHAR2) IS
...
proc_z(p2);

END;

PROCEDURE proc_z(p2 VARCHAR2) IS
  too_many_values EXCEPTION;
  PRAGMA EXCEPTION_INIT(too_many_values, -1427);
BEGIN
  ....
EXCEPTION
   WHEN TOO_MANY_VALUES THEN
      g_err := 'More than one value available to assign in the update';
      RAISE;  -- re-raise the exception for the caller
END;

When the exception is raised in proc_z, it is handled and then raised again. It propagates back through proc_y (no handler there) and then gets returned to the user in proc_x. Errors not set in the global g_err get the generic Oracle error message. This avoids having to pass the initial error parameter throughout the package.

当 proc_z 中引发异常时，它会被处理然后再次引发。它通过 proc_y（那里没有处理程序）传播回来，然后在 proc_x 中返回给用户。未在全局 g_err 中设置的错误获取通用 Oracle 错误消息。这避免了必须在整个包中传递初始错误参数。