Return 2 values from a PL-SQL function

Question

How can i return 2 values from a PL-SQL function?

Answer 1

Try using OUT parameters:

create or replace function f(a IN NUMBER, b OUT NUMBER) RETURN NUMBER IS
BEGIN
  b := a;
  RETURN a;
END f;

Answer 2

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.

Answer 3

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 one output. It shouldn't change any of its arguments or have any other "side effects".

So if you need two outputs, write a procedure instead:

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;

Answer 4

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.

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;