KD09714

 Profile Answers by KD09714 Questions by KD09714

• Jun 28th, 2008

 

A function can return one value by return statement itself.
Both are used to return more than one value in the function. As long as we are executing function same as procedure (i.e. not from any select statement or DML), out parameter is sufficient to return more than one value from function.

Example :

Create or replace function func1
(id1      IN     <datatype1>,
id2      OUT     <datatype1>,
id3      OUT     <datatype1>,
id4      OUT     <datatype1>,
id5      OUT     <datatype1>,
id6      OUT     <datatype1>,
.....
)
return <datatype>
AS

BEGIN
--body of procedure

END func1;



Calling function from any block

BEGIN
.....
var1= func1(var2,var3,var4,var5,var6,var7,......);
....
END;


----------------

But when we need to call the function from any select statement or DML, we cant write OUT or IN OUT parameter in function, thus we can take the help of ref cursor to return more than one values without using out parameters.
Or without using OUT parameter, we can return more than one value from function normally (function not called from select , DML).

Also, definition of ref cursor can be decided at run time.

Example of ref cursor:

DECLARE
A NUMBER := 10;
B NUMBER ;
C NUMBER ;
D NUMBER ;
FUNCTION TEST_FUN (A IN NUMBER, B  OUT NUMBER, C IN OUT NUMBER) RETURN NUMBER IS
D NUMBER;
BEGIN
B := A;
C := B + A;
D := A + B + C;
RETURN (D);
END ;
BEGIN
D := TEST_FUN(A,B,C);
DBMS_OUTPUT.PUT_LINE(' A =' || A);
DBMS_OUTPUT.PUT_LINE(' B =' || B);
DBMS_OUTPUT.PUT_LINE(' C =' || C);
DBMS_OUTPUT.PUT_LINE(' D =' || D);
END;



Using REF CURSOR we can return objects as return type. Here is example


DECLARE
TYPE EMP_REFCUR IS REF CURSOR;
EMP_CURVAR  EMP_REFCUR;
TYPE EMP_REC IS RECORD
(
V_EMPNO EMP.EMPNO%TYPE,
V_ENAME EMP.ENAME%TYPE,
V_JOB   EMP.JOB%TYPE,
V_SAL   EMP.SAL%TYPE
);
EMP_RECVAR EMP_REC;
FUNCTION TEST_FUN (V_EMPNO IN EMP.EMPNO%TYPE) RETURN EMP_REFCUR IS
BEGIN
OPEN EMP_CURVAR FOR SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE EMPNO = V_EMPNO;
RETURN(EMP_CURVAR);
END ;
BEGIN
EMP_CURVAR := TEST_FUN(7369);
FETCH EMP_CURVAR INTO EMP_RECVAR;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS ' || EMP_RECVAR.V_ENAME);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE DESIGNATION IS ' || EMP_RECVAR.V_JOB);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE SALARY IS ' || EMP_RECVAR.V_SAL);
END;

rprakashj

 Profile Answers by rprakashj 

• Aug 29th, 2008

 

Ref cursor is used when we need to assign different select statment for a single cursor in executable section.
where as out parameter is used in procedures and functions to bring out the output from a PL/SQL block.

pratap557

 Profile Answers by pratap557 

• Oct 13th, 2009

 

By Ref cursor we can send record set to the calling program

Like .NET VB page can get recordset as datatable from ref cursor returned by a procedure.
OUT parameter sends only one value from procedure

muraligrandhi

 Profile Answers by muraligrandhi 

• Jun 9th, 2010

 

Main advantage of Ref cursor is you can get the output as record set.
For example if you are using Java or .Net or cristal reports for reporting purpose then you can use Ref cursors and write a subprogram, then frot end guys can call it.

But OUT parameter always get only one value. Functions also get the output only one value and these are always accept IN parameters only. If you want to display more than one value by using OUT parameters you can get it. But oracle not recommanded this.