oracle 从 PL-SQL 函数返回 2 个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3500081/
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
Return 2 values from a PL-SQL function
提问by shirel
How can i return 2 values from a PL-SQL function?
如何从 PL-SQL 函数返回 2 个值？
采纳答案by Erich Kitzmueller
You can return one value directly and another one as an OUT parameter. Or you return a record that contains both values. The first option is, in most cases, simpler to do.
您可以直接返回一个值，另一个作为 OUT 参数返回。或者您返回包含这两个值的记录。在大多数情况下，第一种选择更简单。
回答by Tony Andrews
I would not advocate creating a function with an OUT parameter for the second value, because I like to think of functions as a pure concept: a function performs an operation on one or more inputs to produce oneoutput. It shouldn't change any of its arguments or have any other "side effects".
我不提倡为第二个值创建一个带有 OUT 参数的函数，因为我喜欢将函数视为一个纯粹的概念：函数对一个或多个输入执行操作以产生一个输出。它不应该改变它的任何参数或有任何其他“副作用”。
So if you need two outputs, write a procedureinstead:
因此，如果您需要两个输出，请编写一个过程：
procedure get_sqrt_and_half
( p_input number
, p_sqrt OUT number
, p_half OUT number
)
is
begin
p_sqrt := sqrt(p_input);
p_half := p_input/2;
end;
回答by Gary Myers
A function can only return a single SQL type, but that can be a user-defined type with multiple values. I'd need to know more about the actual end requirements before I'd recommend this as a solution, but it is a possibility.
一个函数只能返回一个 SQL 类型，但它可以是具有多个值的用户定义类型。在我推荐它作为解决方案之前，我需要更多地了解实际的最终要求，但这是一种可能性。
create or replace type a_b is object (a number, b number);
/
create or replace function ret_a_b return a_b is
begin
return a_b(1,2);
end;
/
select ret_a_b from dual;
select d.rab.a, d.rab.b from (select ret_a_b rab from dual) d;
回答by Subham Chanda
**If you are wanting to use it in SQL, then you would need a pipelined function e.g.**
CREATE OR REPLACE TYPE myemp AS OBJECT
( empno number,
ename varchar2(10),
job varchar2(10),
mgr number,
hiredate date,
sal number,
comm number,
deptno number
);
CREATE OR REPLACE TYPE myrectable AS TABLE OF myemp ;
enter code here
CREATE OR REPLACE FUNCTION pipedata(p_min_row number, p_max_row number) RETURN myrectable PIPELINED IS
v_obj myemp := myemp(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
FOR e IN (select *
from (
select e.*
,rownum rn
from (select * from emp order by empno) e
)
where rn between p_min_row and p_max_row)
LOOP
v_obj.empno := e.empno;
v_obj.ename := e.ename;
v_obj.job := e.job;
v_obj.mgr := e.mgr;
v_obj.hiredate := e.hiredate;
v_obj.sal := e.sal;
v_obj.comm := e.comm;
v_obj.deptno := e.deptno;
PIPE ROW (v_obj);
END LOOP;
RETURN;
END;
SQL> select * from table(pipedata(1,5));
回答by Constantin
Try using OUTparameters:
尝试使用OUT参数：
create or replace function f(a IN NUMBER, b OUT NUMBER) RETURN NUMBER IS
BEGIN
b := a;
RETURN a;
END f;
