OUT parameters in oracle Stored functions along with return Value

While writing PL/SQL functions we can use get values through return statement as well as using out parameters. Generally people don’t use out parameters in stored functions, but it is always pretty much possible to use them in functions also. Based on your requirement you can make use of them. Just go through below example:

CREATE OR REPLACE FUNCTION test_fun (var1 NUMBER, var2 OUT VARCHAR2,var3 out number)

   RETURN NUMBER

IS

   v_sal   NUMBER;

BEGIN

   SELECT ename, sal,deptno

     INTO var2, v_sal,var3

     FROM EMP

    WHERE empno=var1 and ROWNUM = 1;

   RETURN v_sal;

END;

/

Call the function like below:

DECLARE

   retval   NUMBER;

   var1     NUMBER;

   var2     VARCHAR2 (200);

   var3     NUMBER;

BEGIN

   var1 := 7369;

   var2 := NULL;

   var3 := NULL;

   retval := TEST_FUN (var1, var2, var3);

   DBMS_OUTPUT.put_line ('VAR2:' || var2);

   DBMS_OUTPUT.put_line ('VAR3:' || var3);

   DBMS_OUTPUT.put_line ('RETVAL:' || retval);

   COMMIT;

END;

/

Your output will be like below:

VAR2:SMITH

VAR3:20

RETVAL:800