我们如何定义存储过程中的输出参数大小？

Question

我们如何定义存储过程中的输出参数大小？

Answer 1

你不能。当然，您可以控制在存储过程的 OUT 参数中放入多少数据。如果需要，可以创建一个指定大小的局部变量来保存数据，然后将该变量的值分配给 OUT 参数。

调用程序确定接收 OUT 参数的变量的大小。

Answer 2

这是一个声明并使用子类型的简单包：

SQL> create or replace package my_pkg as
  2      subtype limited_string is varchar2(10);
  3      procedure pad_string (p_in_str varchar
  4                          , p_length number
  5                          , p_out_str out limited_string);
  6  end my_pkg;
  7  /

Package created.

SQL> create or replace package body my_pkg as
  2      procedure pad_string
  3          (p_in_str varchar
  4              , p_length number
  5              , p_out_str out limited_string)
  6      as
  7      begin
  8          p_out_str := rpad(p_in_str, p_length, 'A');
  9      end  pad_string;
 10  end my_pkg;
 11  /

Package body created.

SQL>

但是，如果我们以输出字符串超出子类型精度的方式调用 PAD_STRING()，它仍然会成功完成。打扰！

SQL> var out_str varchar2(128)
SQL>
SQL> exec my_pkg.pad_string('PAD THIS!', 12, :out_str)

PL/SQL procedure successfully completed.

SQL>
SQL> select length(:out_str) from dual
  2  /

LENGTH(:OUT_STR)
----------------
              12

SQL>

这很烦人，但这就是 PL/SQL 的工作方式，所以我们必须忍受它。

解决这种情况的方法基本上是应用 DBC 原则 并验证我们的参数。因此，我们可以像这样针对输入断言业务规则：

SQL> create or replace package body my_pkg as
  2      procedure pad_string
  3          (p_in_str varchar
  4              , p_length number
  5              , p_out_str out limited_string)
  6      as
  7      begin
  8          if length(p_in_str) + p_length > 10 then
  9              raise_application_error(
 10                      -20000
 11                      , 'Returned string cannot be longer than 10 characters!');
 12          end if;
 13          p_out_str := rpad(p_in_str, p_length, 'A');
 14      end  pad_string;
 15  end my_pkg;
 16  /

Package body created.

SQL>
SQL> exec my_pkg.pad_string('PAD THIS!', 12, :out_str)
BEGIN my_pkg.pad_string('PAD THIS!', 12, :out_str); END;

*
ERROR at line 1:
ORA-20000: Returned string cannot be longer than 10 characters!
ORA-06512: at "APC.MY_PKG", line 9
ORA-06512: at line 1


SQL>

或者我们可以像这样针对输出断言业务规则：

SQL> create or replace package body my_pkg as
  2      procedure pad_string
  3          (p_in_str varchar
  4              , p_length number
  5              , p_out_str out limited_string)
  6      as
  7          l_str limited_string;
  8      begin
  9          l_str := rpad(p_in_str, p_length, 'A');
 10          p_out_str := l_str;
 11      end  pad_string;
 12  end my_pkg;
 13  /

Package body created.

SQL>
SQL> exec my_pkg.pad_string('PAD THIS!', 12, :out_str)
BEGIN my_pkg.pad_string('PAD THIS!', 12, :out_str); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APC.MY_PKG", line 9
ORA-06512: at line 1

SQL>

在大多数情况下，我们应该同时执行这两种操作。这是构建接口的礼貌方式，因为这意味着其他例程可以调用我们的过程，并确信它们将返回它们所说的值。

Answer 3

您可以在包头中使用子类型并在主体中进行类型检查...

CREATE OR REPLACE PACKAGE my_test
AS
   SUBTYPE   my_out   IS   VARCHAR2( 10 ); 

   PROCEDURE do_something( pv_variable IN OUT my_out );
END;
/

CREATE OR REPLACE PACKAGE BODY my_test
AS 
   PROCEDURE do_something( pv_variable IN OUT my_out )
   IS
      lv_variable   my_out;
   BEGIN
      -- Work on a local copy of the variable in question
      lv_variable := 'abcdefghijklmnopqrstuvwxyz';

      pv_variable := lv_variable;
   END do_something;

END;
/

然后当您运行此命令时

DECLARE
   lv_variable VARCHAR2(30);
BEGIN
   my_test.do_something( lv_variable );
   DBMS_OUTPUT.PUT_LINE( '['||lv_variable||']');
END;
/

您会收到错误

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

似乎违背了使用输出参数的精神，但在托尼的评论之后这是唯一的事情我可以想到在调用的代码中控制数据。