oracle - 即使 PL/SQL 过程引发错误，仍可以写入 OUT 参数值吗？

Question

我有一个带有 OUT 的 PL/SQL 过程范围。我怎样才能强制OUT要写入的参数值，即使出现错误？出现错误时是否有另一种返回值的机制？

Answer 1

要扩展@APC 的答案，documentation说:

如果成功退出子程序，PL/SQL 会为 OUT 参数赋值。但是，如果您以未处理的异常退出，PL/SQL 不会为 OUT 参数赋值(除非它们是 NOCOPY 参数)。

以下是一些可能有助于说明的示例:

SQL> set serveroutput on
SQL> REM Example 1 -- OUT parameter value is unchanged if an exception was raised after value was assigned
SQL> declare
  2      x number := 1;
  3      procedure p(x out number)
  4      as
  5      begin
  6          x := 2;
  7          raise program_error;
  8      end p;
  9  begin
 10      p(x);
 11  exception
 12      when program_error then
 13          dbms_output.put_line
 14          (
 15              case
 16                  when x is null then 'null'
 17                  else to_char(x)
 18              end
 19          );
 20  end;
 21  /
1                                                                               

PL/SQL procedure successfully completed.

SQL> REM Example 2 -- OUT parameter value is unchanged if value was set in exception handler and exception was then re-raised
SQL> declare
  2      x number := 1;
  3      procedure p(x out number)
  4      as
  5      begin
  6          x := 2;
  7          raise program_error;
  8      exception
  9          when program_error then
 10              x := 3;
 11              raise;
 12      end p;
 13  begin
 14      p(x);
 15  exception
 16      when program_error then
 17          dbms_output.put_line
 18          (
 19              case
 20                  when x is null then 'null'
 21                  else to_char(x)
 22              end
 23          );
 24  end;
 25  /
1                                                                               

PL/SQL procedure successfully completed.

SQL> REM Example 3 -- OUT parameter value is changed if value was set in exception handler and exception was not re-raised
SQL> declare
  2      x number := 1;
  3      procedure p(x out number)
  4      as
  5      begin
  6          x := 2;
  7          raise program_error;
  8      exception
  9          when program_error then
 10              x := 3;
 11      end p;
 12  begin
 13      p(x);
 14      dbms_output.put_line
 15      (
 16          case
 17              when x is null then 'null'
 18              else to_char(x)
 19          end
 20      );
 21  end;
 22  /
3                                                                               

PL/SQL procedure successfully completed.

SQL> REM Example 4 -- OUT NOCOPY parameter value is changed if an exception was raised
SQL> declare
  2      x number := 1;
  3      procedure p(x out nocopy number)
  4      as
  5      begin
  6          x := 2;
  7          raise program_error;
  8      end p;
  9  begin
 10      p(x);
 11  exception
 12      when program_error then
 13          dbms_output.put_line
 14          (
 15              case
 16                  when x is null then 'null'
 17                  else to_char(x)
 18              end
 19          );
 20  end;
 21  /
2                                                                               

PL/SQL procedure successfully completed.