如何从 Oracle 存储过程中获取两个返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9484135/
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 to get two return value from Oracle Stored Procedure
提问by PPShein
I know how to get one returnvalue from Oracle SP in Oracle as follow
我知道如何在 Oracle 中从 Oracle SP获取一个返回值，如下所示
MyReturn := MY_ORACLE_SP ();
If MY_ORACLE_SP2's return value is more than one. How can I do?
如果 MY_ORACLE_SP2 的返回值大于 1。我能怎么做？
回答by Benoit
-- IN arguments : you get them. You can modify them locally but caller won't see it
-- IN OUT arguments: initialized by caller, already have a value, you can modify them and the caller will see it
-- OUT arguments: they're reinitialized by the procedure, the caller will see the final value.
CREATE PROCEDURE f (p IN NUMBER, x IN OUT NUMBER, y OUT NUMBER)
IS
BEGIN
x:=x * p;
y:=4 * p;
END;
/
SET SERVEROUTPUT ON
declare
foo number := 30;
bar number := 0;
begin
f(5,foo,bar);
dbms_output.put_line(foo || ' ' || bar);
end;
/
outputs: 150 20
输出：150 20
回答by Dave Costa
What you have there is technically not a procedure, but a function-- the difference being that a procedure does not have a return value and cannot be used as the right-hand-side of an assignment statement.
从技术上讲，您拥有的不是过程，而是函数——区别在于过程没有返回值，不能用作赋值语句的右侧。
You basically have two options:
你基本上有两个选择：
(1) Use OUT parameters. In this case I would make it a procedure with two OUT parameters. Generally people don't like functions that also have OUT parameters, as it violates the usual expectations. @Benoit's answer shows this method.
(1) 使用 OUT 参数。在这种情况下，我将使其成为具有两个 OUT 参数的过程。通常人们不喜欢也有 OUT 参数的函数，因为它违反了通常的期望。@Benoit 的回答显示了这种方法。
(2) Define a type that contains multiple values and use this as the return type of the function. Example:
(2) 定义一个包含多个值的类型，并将其作为函数的返回类型。例子：
CREATE TYPE two_values AS object (
A NUMBER,
b number
);
/
CREATE FUNCTION get_two_values RETURN two_values AS
BEGIN
RETURN two_values(2,4);
END;
/
回答by vulkanino
Use OUTPUT parameters instead of the return value.
使用 OUTPUT 参数而不是返回值。
回答by dbkiller
Try the below code I just modified the response from user Benoit
试试下面的代码，我刚刚修改了用户 Benoit 的响应
ab=`sqlplus -s system/password << eof
SET SERVEROUTPUT ON
set pagesize 0;
set heading off;
set feedback off;
set linesize 5000;
set trimspool on;
declare
foo number := 30;
bar number := 0;
begin
f(5,foo,bar);
dbms_output.put_line(foo || ' ' || bar);
end;
/
eof`
echo $ab
