oracle 我们如何在存储过程中定义输出参数大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3184338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can we define output parameter size in stored procedure?
提问by user384271
How can we define output parameter size in stored procedure?
我们如何在存储过程中定义输出参数大小？
回答by Tony Andrews
You can't. Of course, you arein control of how much data you put into the OUT parameter in the stored procedure. If you want you can create a sized local variable to hold the data and then assign the value of that variable to the OUT parameter.
你不能。当然，您可以控制将多少数据放入存储过程的 OUT 参数中。如果需要，您可以创建一个大小合适的局部变量来保存数据，然后将该变量的值分配给 OUT 参数。
The calling program determines the size of the variable that receives the OUT parameter.
调用程序确定接收 OUT 参数的变量的大小。
回答by APC
Here is a simple package which declares and uses a subtype:
这是一个声明并使用子类型的简单包：
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>
However, if we call PAD_STRING() in such a way that the output string exceeds the subtype's precision it still completes successfully. Bother!
但是，如果我们以输出字符串超出子类型精度的方式调用 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>
This is annoying but it's the way PL/SQL works so we have to live with it.
这很烦人，但这是 PL/SQL 的工作方式，所以我们必须忍受它。
The way to resolve the situaton is basically to apply DBC principlesand validate our parameters. So, we can assert business rules against the inputs like this:
解决这种情况的方法基本上是应用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>
Or we can assert business rules against the output like this:
或者我们可以像这样针对输出断言业务规则：
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>
In most scenarios we should do both. This is the polite way to build interfaces, because it means other routines can call our procedures with the confidence that they will return the values they say they will.
在大多数情况下，我们应该两者都做。这是构建接口的礼貌方式，因为这意味着其他例程可以放心地调用我们的过程，因为它们会返回它们所说的值。
回答by PaulJ
You could use a subtype in a package header and type check that in the body...
您可以在包头中使用子类型并在正文中进行类型检查...
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;
/
Then when you run this
然后当你运行这个
DECLARE
lv_variable VARCHAR2(30);
BEGIN
my_test.do_something( lv_variable );
DBMS_OUTPUT.PUT_LINE( '['||lv_variable||']');
END;
/
You would get the error
你会得到错误
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Seems to go against the spirit of using an out parameter, but after Tony's comment this was the only thing I could think of to control data within the called code.
似乎违背了使用 out 参数的精神，但在 Tony 的评论之后，这是我唯一能想到的在被调用代码中控制数据的方法。
