How to return more than one value from a function?
Answers were Sorted based on User's Feedback
Answer / swapna
We can return one more than one value by using reference
cursors.
Is This Answer Correct ? | 26 Yes | 11 No |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
In a table only one column how to update rows
suppose u hav 1 book with set...page 1-100 n 101 -200 now print page from 2-100 n 102 -200... how we will do..?
what is innodb? : Sql dba
I have 2 Databases. How can create a table in particular database? How can i know the list of tables presented each database?( in oracle 10g)
Why we use sql profiler?
what is difference between decode and case function?
What is spool?
Can we use views in stored procedure?
What is bind variable in pl sql?
what is a database transaction? : Sql dba
What is the difference between sql, mysql and sql server?
What is difference between cursor and ref cursor?