How to return more than one value from a function?

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

Question Posted / swapna

11 Answers
28877 Views
Satyam, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How to return more than one value from a function?..

Answer / swapna

We can return one more than one value by using reference
cursors.

Is This Answer Correct ?

26 Yes

11 No

How to return more than one value from a function?..

Answer / re

hey kumaran ...

its not like out paramaters cannot used.........

but its not preferred ...

its just simple ... just move basics why/for what a function
, procedures is used..

also with conditional if stmts... result is that a single
value is returned
re

Is This Answer Correct ?

8 Yes

2 No

How to return more than one value from a function?..

Answer / alok sinha

Hello Boyz !!

Get excited to see this example thru

declare
--
varx1 number := 0;
varx2 number := 0;
varx3 number := 3;
--
begin
--
--
dbms_output.put_line('In: Varx #1 : initial value :: ' ||
varx1);
dbms_output.put_line('In: Varx #2 : initial value :: ' ||
varx2);
--
dbms_output.put_line('In: Varx #3 : Initial value :: ' ||
varx3);
dbms_output.put_line('

');
dbms_output.put_line('Hhhmmmnnn
Hhhmmmnnn Hhhmmmnnn ');
dbms_output.put_line('

');
--
--
varx3 := xx_func(varx3, varx1, varx2);
--
--
dbms_output.put_line('------------------------------------
--------------');
dbms_output.put_line('---------- Post Function -----------
------');
dbms_output.put_line('------------------------------------
--------------');
dbms_output.put_line('

');
dbms_output.put_line('------------------------------------
--------------');
dbms_output.put_line('Return Value :: Varx #3 :: returned
from function :: '|| varx3);
dbms_output.put_line('Out: Varx #1 : returned from
function :: '|| varx1);
dbms_output.put_line('Out: Varx #2 : returned from
function :: '|| varx2);
--
end;

create or replace function xx_func(north in number, east in
out number, west in out number ) return number is
south number;
begin
south := north + 4;
east := 4;
west := 5;
--
return south;
end xx_func;

Is This Answer Correct ?

8 Yes

2 No

How to return more than one value from a function?..

Answer / mglbayar

We can use oracle pipelined function

FUNCTION f_serie_arithmetica (p_nr IN NUMBER)
RETURN ARRAY PIPELINED
IS
v_sum NUMBER;
BEGIN
v_sum := 0;
FOR i IN 1 .. p_nr
LOOP
v_sum := v_sum + i;
PIPE ROW (v_sum);
END LOOP;
RETURN;
END;

Usage:
select * from table(f_serie_arithmetica(23));

Is This Answer Correct ?

5 Yes

0 No

How to return more than one value from a function?..

Answer / kumar

OUT PARAMETER AND REF CURSOR

Is This Answer Correct ?

6 Yes

5 No

How to return more than one value from a function?..

Answer / ravivarman_r

Public Type Income
Wages As Currency
Dividends As Currency
Other As Currency
Total As Currency
End TypeYou can now use this structure as the return
type for a function. In a real situation, the function
would look up your database tables to get the values, but
the return values would be assigned like this:

Function GetIncome() As Income
GetIncome.Wages = 950
GetIncome.Dividends = 570
GetIncome.Other = 52
GetIncome.Total = GetIncome.Wages +
GetIncome.Dividends + GetIncome.Other
End FunctionTo use the function, you could type into
the Immediate Window:

GetIncome().Wages

Is This Answer Correct ?

3 Yes

2 No

How to return more than one value from a function?..

Answer / prajwal maloo

USING OUT AND REF PARAMETERS

Is This Answer Correct ?

2 Yes

1 No

How to return more than one value from a function?..

Answer / siva

Create or replace procedure proc(n number,s out sys_refcursor)
is
Begin
open s for select ename from emp where deptno=n;
end:

Var c ref_cursor;
exec proc(10,:c);
print : c;
Clark
King
Miller

Is This Answer Correct ?

0 Yes

0 No

How to return more than one value from a function?..

Answer / vetrikumaran

WE CAN RETURN MORE THAN ONE VALUE FOR A FUNCTION BY USING
THE CONDITIONAL IF STATEMENT..BUT ONLY ONE RETURN STATEMENT
CAN BE EXECUTED AT A TIME..

IN FUNCTION,BY DEFAULT IN PARAMETER MODE ONLY ALLOWED...

OUT PARAMETERS ARE NOT ALLOWED.

Is This Answer Correct ?

7 Yes

13 No

How to return more than one value from a function?..

Answer / murali

We can return more than one row using out parameter. Try
this with the following.

create or replace function my_func(myno number, myname out
varchar2) return number as
mysal emp.sal%type;
begin
select ename, sal into myname, mysal from emp where
empno=myno;
return sal;
end;

in the above example we are returning first value mysal
using return statement and second value myname using out
parameter.

Is This Answer Correct ?

11 Yes

18 No

Post New Answer