Wednesday, 10 April 2013

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