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

Question

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

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

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

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

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

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;

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

提前致谢。

Answer 1

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

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

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

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

EXCEPTION
    WHEN TOO_MANY_VALUES THEN
       {perform your handler here}

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

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;

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

EXCEPTION
  WHEN OTHERS THEN
    p_err := SQLERRM;
END;

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

Oracle 的文档是 here .

编辑:

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

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;

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