Oracle PLSQL Tutorials: PLSQL Functions with OUT Parameter & DML Operati...

/******************************************************************************
                  PLSQL FUNCTION -  SUBPROGRAM....
*******************************************************************************/
-- PLSQL FUNCTION SYNTAX

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
   < function_body >
END [function_name];

/******************************************************************************
                  Sample Example For PLSQL Function...
*******************************************************************************/

CREATE OR REPLACE FUNCTION Get_Max_Sal(FI_Deptno IN NUMBER)
   RETURN NUMBER
   IS
   max_sal NUMBER(11,2);
   BEGIN
  
      SELECT max(sal)
      INTO max_sal
      FROM EMP
      WHERE deptno = FI_Deptno;
     
      RETURN(max_sal);
     
    END Get_Max_Sal;
/

select * from emp where deptno=20;

select Get_Max_Sal(20) from dual;

select * from emp where deptno=10;


/*****************************************************************************
                PLSQL Function without Parameter Example....
*****************************************************************************/

CREATE OR REPLACE FUNCTION Total_employees
RETURN number IS

V_Total number(3) := 0;
BEGIN

SELECT count(*) into V_Total
FROM emp;

RETURN V_Total;
END;
/

select Total_employees from dual;

/*****************************************************************************
                 Function with OUT Parameter Example..
*****************************************************************************/

CREATE OR REPLACE FUNCTION get_outparam (A IN NUMBER, B IN NUMBER ,C OUT NUMBER)
RETURN NUMBER IS
BEGIN
  C := A+B;
  RETURN A-B;
END get_outparam;
/

select get_outparam(12,15,A) from dual;

SET serveroutput ON;


DECLARE
  A NUMBER:=25;
  B NUMBER:=15;
  C NUMBER;
  D NUMBER;
BEGIN
  D:=get_outparam(A,B,C);
  DBMS_OUTPUT.put_line('OUT PARAMETER VALUE ::: '||C);
    DBMS_OUTPUT.put_line(' FUNCTION RETURN VALUE ::: '||D);

END;
/

/*****************************************************************************
                 Recursive Function Example..
*****************************************************************************/

DECLARE
   num number;
   factorial number; 
  
FUNCTION fact(x number)
RETURN number 
IS
   f number;
BEGIN
   IF x=0 THEN
      f := 1;
   ELSE
      f := x * fact(x-1);
   END IF;
RETURN f;
END; 

BEGIN
   num:= 6;
   factorial := fact(num);
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;

/

CREATE OR REPLACE FUNCTION EMP_Update(depnt IN NUMBER)
RETURN VARCHAR2 IS
v_max_sal_before number := 0;
v_max_sal_after number := 0;
BEGIN
SELECT max(sal) into v_max_sal_before FROM emp
where deptno =depnt;

update emp
set sal = sal+500
where deptno =depnt;
commit;

SELECT max(sal) into v_max_sal_after FROM emp
where deptno =depnt;

RETURN 'Before Updating :::'||v_max_sal_before||' ::After Update :::'||v_max_sal_after;
END;
/

select * from emp where deptno=10;

select EMP_UPDATE(10) from dual;

DECLARE
V_MAX_SAL VARCHAR2(100);
BEGIN
 V_MAX_SAL:= EMP_UPDATE(10);

dbms_output.put_line(V_MAX_SAL);
END;
/

select EMP_Update(10) from dual;