Как мы можем определить размер выходного параметра в хранимой процедуре?

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

Кажется, идет вразрез с духом использования параметра out, но после комментария Тони это было единственное, что я мог придумать для управления данными в вызываемом коде.