Return 2 values from a PL-SQL function

How can i return 2 values from a PL-SQL开发者_如何学运维 function?

---

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;

---

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;

---

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.

---

**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));

---

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;