oracle 在oracle中从存储过程分配输出参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12780864/
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
Assigning output parameter from Stored Procedure in oracle
提问by KeenUser
Please help me with the following scenario
请帮我解决以下场景
I need to call a Stored Procedure inside another one. the inner SP would return a number value which I need to store it in a local variable.
我需要在另一个内部调用存储过程。内部 SP 将返回一个数字值，我需要将其存储在局部变量中。
rough code
粗略的代码
AssignSpResult NUMBER;
AssignSpResult = SPtobecalled(Param1,Param2, OutParam);
AssignSpResult = SPtobeCalled(Param1,Param2, OutParam);
This syntax is most probably wrong, so please help correct it
这个语法很可能是错误的，所以请帮助纠正它
回答by Nick Krasnov
Here is an example of how it might be:
这是一个可能的例子：
HR\XE> create or replace procedure Proc1(p_out out number)
2 is
3 begin
4 p_out := 123;
5* end;
HR\XE> /
Procedure created.
HR\XE> create or replace procedure Proc2
2 is
3 l_val number;
4 begin
5 Proc1(l_val);
6 dbms_output.put_line('Here is a value returned by Proc1: ' || to_char(l_val));
7 end;
8 /
Procedure created.
HR\XE> set serveroutput on;
HR\XE> exec Proc2;
Here is a value returned by Proc1: 123
PL/SQL procedure successfully completed
Depending on your needs it might be more convenient to use functions to return a result of a procedural processing of data. Here is an example:
根据您的需要，使用函数返回数据过程处理的结果可能更方便。下面是一个例子：
HR\XE> create or replace function F1 return number
2 is
3 l_ret_value number;
4 begin
5 l_ret_value := 123;
6 return l_ret_value;
7 end;
HR\XE> /
Function created.
HR\XE> create or replace procedure Proc3
2 is
3 l_val number;
4 begin
5 l_val := F1;
6 dbms_output.put_line('Value returned by the F1 function: ' ||
To_Char(l_val));
7 -- Or
8 dbms_output.put_line('Value returned by the F1 function: ' || To_Char(F1));
9 end;
HR\XE> /
Procedure created.
HR\XE> set serveroutput on;
HR\XE> exec proc3;
Value returned by the F1 function: 123
Value returned by the F1 function: 123
PL/SQL procedure successfully completed.
HR\XE>
回答by Rob van Laarhoven
A stored procedure does not return a value it takes IN, OUT or IN OUT parameters. So probably your have to call:
存储过程不会返回它接受 IN、OUT 或 IN OUT 参数的值。所以可能你必须打电话：
SPtobecalled(Param1,Param2, AssignSpResult );
