NALAM Oracle Apps Technical : Function:

Function:

*********

-->Function is a database object.

-->Functions will be stored in the database.

-->FUNCTION IS SIMILAR TO PROCEDURE.It is named block in PL/SQL and it may or may not take parameter but it must return a value.

-->Function must have return clause in the executable section of the function.

-->Function is used to mainly to perform the calculations.

-->The datatype of return must be decalred in the header of the function.

-->Procedures and Functions are stored in database,these are declared with in a block.

-->Function will accepts all parameters like IN,OUT and INOUT.

-->It is a named pl/sql block which is used to solve some particular task and default functions return a single value.

-->RETURN keyword is mandatory for functions body.

-->Only RETURN is invalid,RETURN with a value or variable or expression is allowed.

-->Function will return multiple values through OUT and INOUT parameters.

-->All DML and TCL statements will be allowed through function.

-->A procedure call is a PL/SQL statement by itself. A function call is part of an expression,

Procedure Call Function Call

BEGIN DECLARE

procedurename(arguments) VARIABLE DATATYPE;

END; BEGIN

VARIABLENAME :=FUNCTIONNAME(ARGUMENTS);

END;

-->Function also having two parts.

a)Function Specification

b)Function Body:

-->In function specification we are specifying name of the function and type of the parameters.

-->In function body we are solving the actual task.

syntax:

*******

CREATE OR REPLACE FUNCTION <FUNCTIONNAME> (PARAMETERS)

RETURN DATATYPE

IS/AS

BEGIN

STMTS;

RETURN <VAL/VAR/EXP>;

EXCEPTION

END [FUCTION NAME];

-->Function can execute in different ways.

a)select statement

a)annonymous block

c)Exec

syntax:

*******

select functionname(actual parameters) from dual;

syntax:

*******

declare

variablename datatype(size);

begin

variablename:=functionname(actual parameters);

end;

EX:

***

create or replace function f1(a varchar2)

return varchar2

begin

return a;

end;

-->First compile the function

-->execute or run the function

step1:

******

select f1(10) from dual;

step2:

******

step3:

******

declare

a varchar2(10);

begin

a:=f1('hi');

dbms_output.put_line(a);

end;

Differences between procedure and function:

*******************************************

FUNCTION:

*********

--i don't want write return syntax in function check the output.

create or replace function f1(a varchar2)

begin

return a;

end;

--Compilation errors

-- I don't want write the return keyword in executable section

create or replace function f1(a varchar2)

return varchar2

begin

--return a;

dbms_output.put_line(a);

end;

--I didn't write return statemnt in place of return stmt i will put dbms stmt

select f1(10) from dual;

--Function is created when we run or call the function it will throw the error.

PROCEDURE:

**********

CREATE OR REPLACE PROCEDURE P1(A NUMBER)

B NUMBER;

BEGIN

B:=A*A;

DBMS_OUTPUT.PUT_LINE(B);

END;

--Procedure Created.

EXEC P1(5);

CREATE OR REPLACE PROCEDURE P1(A NUMBER)

RETURN NUMBER --ADDING RERURN KEYWORD IN PROCEDURE

B NUMBER;

BEGIN

B:=A*A;

DBMS_OUTPUT.PUT_LINE(B);

END;

--PLZ CHECK THE OUTPUT

PROCEDURE CREATED WITH COMPILATION ERRORS.

CREATE OR REPLACE PROCEDURE P1(A NUMBER)

B NUMBER;

BEGIN

B:=A*A;

RETURN B;

--DBMS_OUTPUT.PUT_LINE(B);

END;

--PLZ CHECK THE OUTPUT

PLS-00372: In a procedure, RETURN statement cannot contain an expression

CREATE OR REPLACE PROCEDURE P1(A NUMBER)

B NUMBER;

BEGIN

B:=A*A;

RETURN;

--DBMS_OUTPUT.PUT_LINE(B);

END;

EXEC P1(5);

--PROCEDURE IS EXECUTED BUT WE DIDN'T GET THE OUTPUT.

CREATE OR REPLACE PROCEDURE P1(A NUMBER)

B NUMBER;

BEGIN

B:=A*A;

DBMS_OUTPUT.PUT_LINE(B);

RETURN;

END;

EXEC P1(5);

--PLZ CHECK THE OUTPUT

--OUTPUT WILL BE DISPLAYED

CREATE OR REPLACE PROCEDURE P1(A NUMBER)

B NUMBER;

BEGIN

B:=A*A;

RETURN;

DBMS_OUTPUT.PUT_LINE(B);

END;

PRCEDURE COMPILED

EXEC P1(5);

PL/SQL procedure successfully completed BUT WE DIDN'T GET THE OUTPUT WHY BECAUSE AFTER RETURN STATEMENT THE PROGRAM WILL COME OUT THE BLOCK.

--MULTIPLE RETURN STATEMENTS IN PROCEDURE

CREATE OR REPLACE PROCEDURE P1(A NUMBER)

B NUMBER;

BEGIN

B:=A*A;

DBMS_OUTPUT.PUT_LINE(B);

RETURN;

END;

--COMPILE AND EXECUTE

--CHECK THE OUTPUT

FUNCTION:

*********

CREATE OR REPLACE FUNCTION F1(A NUMBER)

RETURN NUMBER

B NUMBER;

BEGIN

B:=A*A;

DBMS_OUTPUT.PUT_LINE(B);

END;

--Function F1 compiled

--CHECK THE OUTPUT

SELECT F1(5) FROM DUAL;

ORA-06503: PL/SQL: Function returned without value

CREATE OR REPLACE FUNCTION F1(A NUMBER)

RETURN NUMBER

B NUMBER;

BEGIN

B:=A*A;

RETURN B;

END;

--COMPILE AND EXECUTE

Function F1 compiled

--CHECK THE OUTPUT

SELECT F1(5) FROM DUAL;

OUTPUT :25

CREATE OR REPLACE FUNCTION F1(A NUMBER)

RETURN VARCHAR2

B NUMBER:=0;

C VARCHAR2(240);

BEGIN

C :=A/B;

RETURN C;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);

RETURN SQLERRM;

END;

SELECT F1(1000) FROM DUAL;

DECLARE

A VARCHAR2(240);

BEGIN

A :=F1(1000);

DBMS_OUTPUT.PUT_LINE(A);

END;

CREATE OR REPLACE FUNCTION F1(A NUMBER)

RETURN NUMBER

B NUMBER;

BEGIN

B:=A*A;

RETURN B;

DBMS_OUTPUT.PUT_LINE(B);

END;

--COMPILE AND EXECUTE

Function F1 compiled

--CHECK THE OUTPUT

SELECT F1(5) FROM DUAL;

OUTPUT : 25

CREATE OR REPLACE FUNCTION F1(A NUMBER)

RETURN NUMBER

B NUMBER;

BEGIN

B:=A*A;

RETURN B;

DBMS_OUTPUT.PUT_LINE(B);

END;

--COMPILE AND EXECUTE

Function F1 compiled

--CHECK THE OUTPUT

SELECT F1(5) FROM DUAL;

OUTPUT : 25

NOTE:IF WE WANT WRITE MULTIPLE RETURN STATEMENTS IN FUNCTION IT IS SUCCESSFULLY ACCEPTED WE DON'T GET ANY ERROR BUT THE FUNCTION ALWAYS RETURN ONLY FIRST RETURN STATEMENT,THE FUNCTION DOES NOT RETURN ANOTHER STATEMENT.

CREATE OR REPLACE FUNCTION F1(A NUMBER)

RETURN NUMBER

B NUMBER;

C NUMBER;

BEGIN

B:=A*A;

C:=A+B;

RETURN B;

RETURN C;

DBMS_OUTPUT.PUT_LINE(B);

END;

--COMPILE AND EXECUTE

Function F1 compiled

SELECT F1(5) FROM DUAL;

--CHECK THE OUTPUT

OUTPUT : 25

--PROCEDURE

CREATE OR REPLACE PROCEDURE P1(A NUMBER)

B NUMBER;

C NUMBER;

BEGIN

B:=A*A;

C:=A+B;

DBMS_OUTPUT.PUT_LINE(B||','||C);

END;

--COMPILE AND EXECUTE

--Procedure P1 compiled

--CHECK THE OUTPUT

EXEC P1(5);

OUTPUT :25 10

Examples of function:

*********************le

CREATE OR REPLACE FUNCTION F1(A NUMBER)

RETURN VARCHAR2

BEGIN

IF MOD(A,2)=0 THEN

RETURN 'EVEN NUMBER';

ELSE

RETURN 'ODD NUMBER';

END IF;

END;

SELECT F1(10) FROM DUAL;

CREATE OR REPLACE FUNCTION F1(A NUMBER,B NUMBER)

RETURN NUMBER

C NUMBER;

BEGIN

C :=A*B;

RETURN C;

END;

--Calling function from select query

SELECT F1(10,20) FROM DUAL;

--Calling function from program

DECLARE

A NUMBER :=&A;

B NUMBER :=&B;

C NUMBER;

BEGIN

C:=F1(A,B);

DBMS_OUTPUT.PUT_LINE(C);

END;

CREATE OR REPLACE PROCEDURE P1(P NUMBER,Q NUMBER,R OUT NUMBER)

BEGIN

R :=F1(P,Q);

DBMS_OUTPUT.PUT_LINE(R);

END;

--Calling function from procedure

DECLARE

A NUMBER :=&A;

B NUMBER :=&B;

C NUMBER;

BEGIN

P1(A,B,C);

END;

--we can also use user defined functions in insert statement.

create or replace function f1(a varchar2)

return varchar2

begin

return a;

end;

select f1('hi') from dual;

create table xyz(id number,message varchar2(100));

insert into xyz values(1,f1(10));

insert into xyz values(10,f1('welcome'));

create or replace function f1(p_ename varchar2)

return VARCHAR2

lv_empno number;

LV_ERROR_MESSAGE VARCHAR2(240);

begin

select empno

into lv_empno

from emp

where ename=p_ename;

return lv_empno;

exception

when others then

--RETURN SQLERRM;

LV_ERROR_MESSAGE :=SQLERRM;

return LV_ERROR_MESSAGE;

end;

select f1('ABC')FROM DUAL;

write a pl/sql program for passing empno as parameter return gross slary from emp table based on below condition.

gross:=basic+hra+da-pf

hra==>10%sal

da ==>20%sal

pf ==>10%sal

CREATE OR REPLACE FUNCTION F1(P_EMPNO NUMBER)

RETURN NUMBER

LV_SAL NUMBER;

GROSS NUMBER;

HRA NUMBER;

DA NUMBER;

PF NUMBER;

BEGIN

SELECT SAL INTO LV_SAL FROM EMP WHERE EMPNO=P_EMPNO;

HRA:=LV_SAL*0.1;

DA :=LV_SAL*0.2;

PF :=LV_SAL*0.1;

GROSS:=LV_SAL+HRA+DA-PF;

RETURN GROSS;

END;

select f1(7369) from dual;

DECLARE

CURSOR C1 IS SELECT EMPNO,ENAME,SAL FROM EMP;

LV_SAL NUMBER;

LV_TOTAL_SAL NUMBER;

BEGIN

FOR I IN C1

LOOP

LV_TOTAL_SAL :=F1(I.EMPNO);

DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.SAL||','||LV_TOTAL_SAL);

END LOOP;

END;

CREATE TABLE XX_GROSS

(

EMPNO NUMBER,

ENAME VARCHAR2(20),

SAL NUMBER,

DEPTNO NUMBER,

GROSS NUMBER

);

DECLARE

CURSOR C1 IS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP;

BEGIN

FOR I IN C1

LOOP

INSERT INTO XX_GROSS

VALUES

(

I.EMPNO,

I.ENAME,

I.SAL,

I.DEPTNO,

F1(I.EMPNO)

);

END LOOP;

COMMIT;

END;

CREATE OR REPLACE FUNCTION F1

RETURN VARCHAR2

CURSOR C1 IS SELECT ENAME FROM EMP;

LV_ENAME VARCHAR2(20);

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO LV_ENAME;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(LV_ENAME);

END LOOP;

CLOSE C1;

RETURN 0;

END;

DECLARE

A VARCHAR2(20);

BEGIN

A :=F1;

END;

CREATE OR REPLACE FUNCTION F1(P_DEPTNO NUMBER)

RETURN VARCHAR2

CURSOR C1 IS SELECT ENAME FROM EMP WHERE DEPTNO=P_DEPTNO;

LV_ENAME VARCHAR2(20);

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO LV_ENAME;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(LV_ENAME);

END LOOP;

CLOSE C1;

RETURN 0;

END;

DECLARE

A VARCHAR2(20);

BEGIN

A :=F1(20);

END;

create or replace function f1

return varchar2

cursor c1 is select * from emp;

begin

for i in c1

loop

dbms_output.put_line(i.ename||','||i.sal);

end loop;

return 0;

end;

declare

a varchar2(20);

begin

a :=f1;

end;

create or replace function f1(P_DEPTNO IN NUMBER)

return varchar2

cursor c1 is select * from emp WHERE DEPTNO=P_DEPTNO;

begin

for i in c1

loop

dbms_output.put_line(i.ename||','||i.sal);

end loop;

return 0;

end;

declare

a varchar2(20);

begin

a :=f1(20);

end;

CREATE OR REPLACE FUNCTION F1(P_VENDOR VARCHAR2)

RETURN VARCHAR2

CURSOR C1

SELECT VENDOR_NAME

FROM AP_SUPPLIERS

WHERE VENDOR_TYPE_LOOKUP_CODE=P_VENDOR;

BEGIN

FOR I IN C1

LOOP

DBMS_OUTPUT.PUT_LINE(I.VENDOR_NAME);

END LOOP;

RETURN 0;

END;

DECLARE

P_VENDOR_NAME VARCHAR2(50);

BEGIN

P_VENDOR_NAME :=F1('VENDOR');

END;

CREATE OR REPLACE FUNCTION F1

RETURN VARCHAR2

CURSOR C1 IS SELECT * FROM EMP;

LV_COUNT NUMBER :=0;

LV_CNT NUMBER;

BEGIN

FOR I IN C1

LOOP

INSERT INTO XX_EMP

VALUES

(

I.EMPNO,

I.ENAME,

I.JOB,

I.MGR,

I.HIREDATE,

I.SAL,

I.COMM,

I.DEPTNO

);

LV_COUNT :=LV_COUNT+1;

LV_CNT :=C1%ROWCOUNT;

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS INSERTED : '||LV_COUNT);

DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS INSERTED1 : '||LV_CNT);

RETURN 0;

END;

DECLARE

P_ABC VARCHAR2(20);

BEGIN

P_ABC:=F1;

END;

SELECT * FROM XX_EMP;

CREATE OR REPLACE FUNCTION F1(P_EMPNO NUMBER,P_BONUS NUMBER)

RETURN NUMBER

BEGIN

UPDATE EMP1

SET SAL=SAL+P_BONUS

WHERE EMPNO=P_EMPNO;

RETURN 0;

END;

DECLARE

P_ABC NUMBER;

BEGIN

P_ABC :=F1(7698,1500);

END;

SELECT *

FROM EMP1

WHERE EMPNO=7698;

CREATE OR REPLACE FUNCTION F1(P_EMPNO NUMBER,P_BONUS NUMBER)

RETURN NUMBER

CURSOR C1 IS SELECT * FROM EMP1 WHERE EMPNO=P_EMPNO;

BEGIN

FOR I IN C1

LOOP

UPDATE EMP1

SET SAL=SAL+P_BONUS

WHERE EMPNO=I.EMPNO;

END LOOP;

RETURN 0;

END;

DECLARE

P_ABC NUMBER;

BEGIN

P_ABC :=F1(7782,1000);

END;

SELECT *

FROM EMP1

WHERE EMPNO=7782;

CREATE OR REPLACE FUNCTION F1(P_DEPTNO NUMBER)

RETURN NUMBER

CURSOR C1 IS SELECT * FROM EMP1 WHERE DEPTNO=P_DEPTNO;

LV_COUNT NUMBER :=0;

BEGIN

DBMS_OUTPUT.PUT_LINE('DEPTNO'||' '||P_DEPTNO||' '||'DETAILS');

FOR I IN C1

LOOP

DELETE FROM EMP1 WHERE DEPTNO=I.DEPTNO;

LV_COUNT :=LV_COUNT+1;

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('NO OF ROWS DELETED : '||LV_COUNT);

RETURN 0;

END;

DECLARE

P_ABC NUMBER;

BEGIN

P_ABC :=F1(10);

END;

SELECT *

FROM EMP1

WHERE DEPTNO=10;

CREATE OR REPLACE FUNCTION F1

RETURN VARCHAR2

CURSOR C1 IS SELECT * FROM EMP;

LV_COUNT NUMBER :=0;

LV_CNT NUMBER;

BEGIN

FOR I IN C1

LOOP

INSERT INTO XX_EMP

(

EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

COMM,

DEPTNO

)

VALUES

(

I.EMPNO,

I.ENAME,

I.JOB,

I.MGR,

I.HIREDATE,

I.SAL,

I.COMM,

I.DEPTNO

);

LV_COUNT :=LV_COUNT+1;

LV_CNT :=C1%ROWCOUNT;

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS INSERTED : '||LV_COUNT);

DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS INSERTED1 : '||LV_CNT);

RETURN 0;

END;

DECLARE

P_ABC VARCHAR2(20);

BEGIN

P_ABC:=F1;

END;

SELECT * FROM XX_EMP;

CREATE OR REPLACE FUNCTION F1

RETURN VARCHAR2

BEGIN

INSERT INTO XX_EMP SELECT * FROM EMP;

RETURN 0;

END;

DECLARE

P_ABC VARCHAR2(20);

BEGIN

P_ABC :=F1;

END;

SELECT * FROM XX_EMP;

SELECT * FROM XX_GROSS;

TRUNCATE TABLE XX_GROSS;

SELECT * FROM XX_GROSS;

CREATE OR REPLACE PROCEDURE P1(P_DEPTNO NUMBER)

CURSOR C1 IS SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO=P_DEPTNO;

BEGIN

FOR I IN C1

LOOP

INSERT INTO XX_GROSS

VALUES

(

I.EMPNO,

I.ENAME,

I.SAL,

I.DEPTNO,

F1(I.EMPNO)

);

END LOOP;

END;

EXEC P1(10);

SELECT * FROM XX_GROSS;

CREATE OR REPLACE FUNCTION F1(P_SAL EMP.SAL%TYPE)

RETURN NUMBER

--LV_SAL NUMBER;

LV_BONUS NUMBER;

BEGIN

IF (P_SAL<1000) THEN

LV_BONUS :=0.05*P_SAL;

ELSIF (P_SAL>=1000 AND P_SAL<2000) THEN

LV_BONUS :=0.10*P_SAL;

ELSIF (P_SAL>=2000 AND P_SAL<3000) THEN

LV_BONUS :=0.15*P_SAL;

ELSIF (P_SAL>=3000 AND P_SAL<5000) THEN

LV_BONUS :=0.20*P_SAL;

ELSE

LV_BONUS :=0.25*P_SAL;

END IF;

RETURN LV_BONUS;

END;

SELECT F1(5000) FROM DUAL;

CREATE OR REPLACE PROCEDURE P1 (P_EMPNO NUMBER)

LV_SAL NUMBER;

LV_BONUS NUMBER;

LV_TOTAL_SAL NUMBER;

BEGIN

SELECT SAL

INTO LV_SAL

FROM EMP

WHERE EMPNO=P_EMPNO;

LV_BONUS :=F1(LV_SAL);

LV_TOTAL_SAL :=LV_SAL+LV_BONUS;

DBMS_OUTPUT.PUT_LINE('EMPNO IS :'||P_EMPNO);

DBMS_OUTPUT.PUT_LINE('SALARY IS :'||LV_SAL);

DBMS_OUTPUT.PUT_LINE('BONUS IS :'||LV_BONUS);

DBMS_OUTPUT.PUT_LINE('TOTAL SALARY IS :'||LV_TOTAL_SAL);

END;

EXEC P1(7839);

CREATE OR REPLACE PROCEDURE P2

CURSOR C1 IS SELECT EMPNO,SAL FROM EMP;

LV_EMPNO NUMBER;

LV_SAL NUMBER;

LV_BONUS NUMBER;

LV_TOTAL_SAL NUMBER;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO LV_EMPNO,LV_SAL;

LV_BONUS :=F1(LV_SAL);

LV_TOTAL_SAL :=LV_SAL+LV_BONUS;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMPNO IS :'||LV_EMPNO);

DBMS_OUTPUT.PUT_LINE('*********************');

DBMS_OUTPUT.PUT_LINE('SALARY IS :'||LV_SAL);

DBMS_OUTPUT.PUT_LINE('BONUS IS :'||LV_BONUS);

DBMS_OUTPUT.PUT_LINE('TOTAL SALARY IS :'||LV_TOTAL_SAL);

DBMS_OUTPUT.PUT_LINE('*********************');

END LOOP;

CLOSE C1;

END;

EXEC P2;

CREATE OR REPLACE PROCEDURE P3

CURSOR C1 IS SELECT EMPNO,SAL FROM EMP;

--I EMP%ROWTYPE;

LV_BONUS NUMBER;

LV_TOTAL_SAL NUMBER;

BEGIN

FOR I IN C1

LOOP

LV_BONUS :=F1(I.SAL);

LV_TOTAL_SAL :=I.SAL+LV_BONUS;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMPNO IS :'||I.EMPNO);

DBMS_OUTPUT.PUT_LINE('*********************');

DBMS_OUTPUT.PUT_LINE('SALARY IS :'||I.SAL);

DBMS_OUTPUT.PUT_LINE('BONUS IS :'||LV_BONUS);

DBMS_OUTPUT.PUT_LINE('TOTAL SALARY IS :'||LV_TOTAL_SAL);

DBMS_OUTPUT.PUT_LINE('*********************');

END LOOP;

END;

EXEC P3;

CREATE OR REPLACE PROCEDURE P4(P_DEPTNO IN NUMBER)

CURSOR C1 IS SELECT EMPNO,SAL FROM EMP WHERE DEPTNO=P_DEPTNO;

LV_EMPNO NUMBER;

LV_SAL NUMBER;

LV_BONUS NUMBER;

LV_TOTAL_SAL NUMBER;

BEGIN

OPEN C1;

DBMS_OUTPUT.PUT_LINE('DEPT '||P_DEPTNO||' DETAILS');

DBMS_OUTPUT.PUT_LINE(' ');

LOOP

FETCH C1 INTO LV_EMPNO,LV_SAL;

LV_BONUS :=F1(LV_SAL);

LV_TOTAL_SAL :=LV_SAL+LV_BONUS;

EXIT WHEN C1%NOTFOUND;

--DBMS_OUTPUT.PUT_LINE('DEPTNO IS : '||P_DEPTNO);

DBMS_OUTPUT.PUT_LINE('EMPNO IS :'||LV_EMPNO);

DBMS_OUTPUT.PUT_LINE('*********************');

DBMS_OUTPUT.PUT_LINE('SALARY IS :'||LV_SAL);

DBMS_OUTPUT.PUT_LINE('BONUS IS :'||LV_BONUS);

DBMS_OUTPUT.PUT_LINE('TOTAL SALARY IS :'||LV_TOTAL_SAL);

DBMS_OUTPUT.PUT_LINE('*********************');

END LOOP;

CLOSE C1;

END;

EXEC P4(20);

CREATE OR REPLACE PROCEDURE P5

CURSOR C1 IS SELECT EMPNO,SAL FROM EMP;

LV_EMPNO NUMBER;

LV_SAL NUMBER;

LV_BONUS NUMBER;

LV_TOTAL_SAL NUMBER;

BEGIN

FOR I IN C1

LOOP

LV_EMPNO :=I.EMPNO;

LV_BONUS :=F1(I.SAL);

LV_SAL :=I.SAL;

LV_TOTAL_SAL :=LV_SAL+LV_BONUS;

DBMS_OUTPUT.PUT_LINE('EMPNO IS :'||LV_EMPNO);

DBMS_OUTPUT.PUT_LINE('*********************');

DBMS_OUTPUT.PUT_LINE('SALARY IS :'||LV_SAL);

DBMS_OUTPUT.PUT_LINE('BONUS IS :'||LV_BONUS);

DBMS_OUTPUT.PUT_LINE('TOTAL SALARY IS :'||LV_TOTAL_SAL);

DBMS_OUTPUT.PUT_LINE('*********************');

END LOOP;

END;

EXEC P5;

CREATE OR REPLACE PROCEDURE P6

CURSOR C1 IS SELECT EMPNO,SAL FROM EMP;

LV_BONUS NUMBER;

LV_TOTAL_SAL NUMBER;

BEGIN

FOR I IN C1

LOOP

LV_BONUS :=F1(I.SAL);

LV_TOTAL_SAL :=I.SAL+LV_BONUS;

DBMS_OUTPUT.PUT_LINE('EMPNO IS :'||I.EMPNO);

DBMS_OUTPUT.PUT_LINE('*********************');

DBMS_OUTPUT.PUT_LINE('SALARY IS :'||I.SAL);

DBMS_OUTPUT.PUT_LINE('BONUS IS :'||LV_BONUS);

DBMS_OUTPUT.PUT_LINE('TOTAL SALARY IS :'||LV_TOTAL_SAL);

DBMS_OUTPUT.PUT_LINE('*********************');

END LOOP;

END;

EXEC P6;

CREATE OR REPLACE FUNCTION F1(p_empno EMP.empno%TYPE)

RETURN NUMBER

LV_BONUS NUMBER;

lv_sal number;

BEGIN

select sal

into lv_sal

from emp

where empno=p_empno;

IF (lv_sal<1000) THEN

LV_BONUS :=lv_sal+0.05*lv_sal;

ELSIF (lv_sal>=1000 AND lv_sal<2000) THEN

LV_BONUS :=lv_sal+0.10*lv_sal;

ELSIF (lv_sal>=2000 AND lv_sal<3000) THEN

LV_BONUS :=lv_sal+0.15*lv_sal;

ELSIF (lv_sal>=3000 AND lv_sal<5000) THEN

LV_BONUS :=0.20*lv_sal;

ELSE

LV_BONUS :=lv_sal+0.25*lv_sal;

END IF;

RETURN LV_BONUS;

END;

select f1(7839) from dual;

--Write a PL/SQL stored function for passing empno,date as parameter return no.of.years that employee is working based on date from emp table.

create or replace function f1(p_empno in number,p_date date)

return number

a number;

begin

select months_between(p_date,hiredate)/12 into a from emp where empno=p_empno;

return (round(a));

end;

execution:

**********

select f1(7369,sysdate) from dual;

CREATE OR REPLACE PROCEDURE P1

CURSOR C1 IS SELECT EMPNO,ENAME,SAL,HIREDATE FROM EMP;

LV_DATE DATE :=SYSDATE;

LV_TOTAL_EXP NUMBER;

BEGIN

FOR I IN C1

LOOP

LV_TOTAL_EXP :=F1(I.EMPNO,LV_DATE);

DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.SAL||','||I.HIREDATE||','||LV_TOTAL_EXP);

END LOOP;

END;

EXEC P1;

CREATE OR REPLACE PROCEDURE P1(P_EMPNO IN NUMBER)

LV_SAL NUMBER;

BEGIN

SELECT SAL

INTO LV_SAL

FROM EMP

WHERE EMPNO=P_EMPNO;

DBMS_OUTPUT.PUT_LINE(LV_SAL);

END;

EXEC P1(7839);

--calling procedure in function

CREATE OR REPLACE FUNCTION F1 (LV_EMPNO NUMBER)

RETURN NUMBER

BEGIN

P1(LV_EMPNO);

RETURN 1;

END;

SELECT F1(7839) FROM DUAL;

DECLARE

LV_SAL NUMBER;

BEGIN

LV_SAL :=F1(7839);

DBMS_OUTPUT.PUT_LINE(LV_SAL);

END;

--oracle apps po_headers_all table data

CREATE OR REPLACE FUNCTION F1(P_PO_HEADER_ID IN NUMBER)

RETURN VARCHAR2

LV_SEGMENT1 NUMBER;

BEGIN

SELECT SEGMENT1

INTO LV_SEGMENT1

FROM PO_HEADERS_ALL

WHERE PO_HEADER_ID=P_PO_HEADER_ID;

RETURN LV_SEGMENT1;

END;

SELECT F1(1) FROM DUAL;

SELECT PO_HEADER_ID,F1(PO_HEADER_ID) FUN_VALUE

FROM PO_HEADERS_ALL;

CREATE TABLE XX_PO(PO_HEADER_ID NUMBER,SEGMENT1 VARCHAR2(20));

DECLARE

CURSOR C1 IS SELECT PO_HEADER_ID FROM PO_HEADERS_ALL;

LV_PO_CNT NUMBER :=0;

BEGIN

FOR I IN C1

LOOP

INSERT INTO XX_PO

(

PO_HEADER_ID,

SEGMENT1

)

VALUES

(

I.PO_HEADER_ID,

F1(I.PO_HEADER_ID)

);

LV_PO_CNT :=LV_PO_CNT+1;

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('NUMBER OF RECORDS ARE INSERTED : '||LV_PO_CNT);

END;

SELECT COUNT(*) FROM XX_PO;

--OUT Parameter

CREATE OR REPLACE FUNCTION F1(A IN NUMBER,

B IN NUMBER,

C OUT NUMBER)

RETURN NUMBER

BEGIN

C:=A+B;

RETURN A-B;

END;

DECLARE

A NUMBER:=45;

B NUMBER:=10;

C NUMBER;

D NUMBER;

BEGIN

D :=F1(A,B,C);

DBMS_OUTPUT.PUT_LINE('OUT PARAMETER VALUE IS :'||C);

DBMS_OUTPUT.PUT_LINE('FUNCTION RETURN VALUE IS :'||D);

END;

create or replace function f1(id in number,fname out varchar2)

return varchar2

begin

fname :='Mohan';

return 'Reddy';

end;

declare

fname varchar2(100);

sname varchar2(100);

begin

sname:=f1(3,fname);

dbms_output.put_line(fname);

dbms_output.put_line(sname);

end;

create or replace function f1(p_deptno number,p_dname out varchar2)

return varchar2

begin

select dname

into p_dname

from dept

where deptno=p_deptno;

return p_dname;

end;

declare

a varchar2(20);

b varchar2(20);

begin

a:=f1(10,b);

dbms_output.put_line(b);

end;

create or replace function f1(p_deptno number,p_dname out varchar2)

return varchar2

LV_LOC VARCHAR2(20);

begin

select dname,LOC

into p_dname,LV_LOC

from dept

where deptno=p_deptno;

return LV_LOC;

end;

declare

a varchar2(20);

b varchar2(20);

begin

a:=f1(10,b);

dbms_output.put_line(b);

DBMS_OUTPUT.PUT_LINE(A);

end;

create or replace function f1(p_deptno number,p_dname out varchar2,p_loc out varchar2)

return varchar2

begin

select dname,loc

into p_dname,p_loc

from dept

where deptno=p_deptno;

return p_dname;

end;

declare

a varchar2(20);

b varchar2(20);

c varchar2(20);

begin

a:=f1(10,b,c);

dbms_output.put_line(b||','||c);

end;

CREATE OR REPLACE FUNCTION F1(P_DEPTNO IN NUMBER,P_TOTAL OUT NUMBER,P_TOTAL_SAL OUT NUMBER,P_AVG_SAL OUT NUMBER)

RETURN VARCHAR2

BEGIN

SELECT COUNT(*),SUM(SAL),AVG(SAL)

INTO P_TOTAL,P_TOTAL_SAL,P_AVG_SAL

FROM EMP

WHERE DEPTNO=P_DEPTNO;

RETURN P_TOTAL;

END;

EXECUTION:

**********

DECLARE

A NUMBER;

B NUMBER;

C NUMBER;

D NUMBER;

BEGIN

A :=F1(10,B,C,D);

DBMS_OUTPUT.PUT_LINE('FUNCTION VALUE : '||A);

DBMS_OUTPUT.PUT_LINE('FUNCTION VALUE : '||C);

DBMS_OUTPUT.PUT_LINE('FUNCTION VALUE : '||D);

END;

CREATE OR REPLACE FUNCTION F1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER)

RETURN VARCHAR2

CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=P_DEPTNO;

BEGIN

FOR I IN C1

LOOP

BEGIN

SELECT SAL

INTO P_SAL

FROM EMP

WHERE EMPNO=I.EMPNO;

DBMS_OUTPUT.PUT_LINE(I.DEPTNO||','||P_SAL);

END;

END LOOP;

RETURN 0;

END;

DECLARE

P_TEST VARCHAR2(20);

P_SAL NUMBER;

BEGIN

P_TEST :=F1(10,P_SAL);

END;

CREATE OR REPLACE FUNCTION F1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER,P_HIREDATE OUT DATE)

RETURN VARCHAR2

CURSOR C1 IS SELECT ENAME,SAL,HIREDATE,DEPTNO FROM EMP WHERE DEPTNO=P_DEPTNO;

BEGIN

FOR I IN C1

LOOP

P_SAL :=I.SAL;

P_HIREDATE :=I.HIREDATE;

DBMS_OUTPUT.PUT_LINE(P_SAL||' '||P_HIREDATE||' '||P_DEPTNO);

END LOOP;

RETURN 0;

END;

DECLARE

P_SAL NUMBER;

P_HIREDATE DATE;

A VARCHAR2(20);

BEGIN

A :=F1(10,P_SAL,P_HIREDATE);

END;

CREATE OR REPLACE FUNCTION F1

RETURN VARCHAR2

CURSOR C1 IS SELECT * FROM EMP;

LV_EXP NUMBER;

BEGIN

FOR I IN C1

LOOP

BEGIN

SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)EXP

INTO LV_EXP

FROM EMP

WHERE EMPNO=I.EMPNO;

DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.HIREDATE||','||LV_EXP);

END;

END LOOP;

RETURN 0;

END;

DECLARE

P_TEST VARCHAR2(20);

BEGIN

P_TEST :=F1;

END;

CREATE OR REPLACE FUNCTION F1(P_DEPTNO IN NUMBER,P_EXP OUT NUMBER)

RETURN VARCHAR2

CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=P_DEPTNO;

BEGIN

FOR I IN C1

LOOP

BEGIN

SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)EXP

INTO P_EXP

FROM EMP

WHERE EMPNO=I.EMPNO;

DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.HIREDATE||','||I.DEPTNO||','||P_EXP);

END;

END LOOP;

RETURN 0;

END;

DECLARE

P_TEST VARCHAR2(20);

P_EXP NUMBER;

BEGIN

P_TEST :=F1(10,P_EXP);

END;

--IN OUT Parameter

create or replace function f1(p_no in out number)

return number

l_avg_sal number;

begin

select avg(sal) into l_avg_sal

from emp

where deptno=p_no;

select max(sal) into p_no

from emp

where deptno=p_no;

return l_avg_sal;

end;

declare

p_no number :=&a;

p_avg_sal number;

begin

p_avg_sal :=f1(p_no);

dbms_output.put_line(p_no);

dbms_output.put_line(p_avg_sal);

end;

CREATE OR REPLACE FUNCTION F1(P_ABC IN OUT VARCHAR2)

RETURN VARCHAR2

BEGIN

SELECT SAL

INTO P_ABC

FROM EMP

WHERE EMPNO=P_ABC;

RETURN(P_ABC);

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN('EMP DOES NOT EXIST');

END;

DECLARE

P_ABC VARCHAR2(20) :=7839;

P_TEST NUMBER;

BEGIN

P_TEST :=F1(P_ABC);

DBMS_OUTPUT.PUT_LINE(P_TEST);

END;

DECLARE

P_ABC VARCHAR2(20);

P_TEST VARCHAR2(20);

CURSOR C1 IS SELECT * FROM EMP;

P_SAL NUMBER;

BEGIN

FOR I IN C1

LOOP

P_ABC :=I.EMPNO;

P_TEST :=F1(P_ABC);

DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||P_TEST);

END LOOP;

END;

CREATE OR REPLACE FUNCTION F1(P_ABC IN OUT NUMBER)

RETURN VARCHAR2

CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=P_ABC;

BEGIN

FOR I IN C1

LOOP

P_ABC :=I.SAL;

DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||P_ABC);

END LOOP;

RETURN 0;

END;

DECLARE

P_TEST VARCHAR2(20);

P_ABC NUMBER :=10;

BEGIN

P_TEST :=F1(P_ABC);

END;

DROP TABLE DEPT1;

CREATE TABLE DEPT1 AS SELECT * FROM DEPT WHERE 1=2;

--can we use dml statements inside a function;

create or replace function f1

return varchar2

begin

insert into dept1 values(50,'HR','IND');

RETURN 0;

END;

SELECT F1 FROM DUAL; --IT WILL THROW THE ERROR.WE CAN NOT PERFORM DML OPERATIONS THROUGH SELECT STATEMENT.

TO OVERCOME THIS ISSUE BY USING ANONYMOUS BLOCK.

DECLARE

A VARCHAR2(20);

BEGIN

A:=F1;

DBMS_OUTPUT.PUT_LINE(A);

END;

--can we use dml statements inside a function THROUGH PRAGMA AUTONOMOUS TRANSACTION.

create or replace function f1

return number

pragma autonomous_transaction;

begin

--insert into dept1 values(50,'HR','IND');

--update DEPT1 set LOC='HYD' WHERE DEPTNO=50;

DELETE FROM DEPT1 WHERE DEPTNO=50;

COMMIT;

return 1;

end;

select f1 from dual;

NOTE:

*****

-->we can also use predefined functions in user defined functions like max,min,count and also this user defined functions in same table or different table.

CREATE OR REPLACE FUNCTION F1

RETURN NUMBER

LV_SAL NUMBER;

BEGIN

SELECT MAX(SAL) INTO LV_SAL FROM EMP;

RETURN LV_SAL;

END;

SELECT F1 FROM DUAL;

CREATE OR REPLACE FUNCTION F1

RETURN NUMBER

LV_CNT NUMBER;

BEGIN

SELECT COUNT(*) INTO LV_CNT FROM EMP;

RETURN LV_CNT;

END;

SELECT F1 FROM DUAL;

--REAL TIME EXAMPLES

create or replace function f1(p_inv_item_id number,

p_org_id number,

p_date date)

return number

l_last_update_date date;

begin

select last_update_date

into l_last_update_date

from mtl_system_items_b msib

where 1=1

and msib.inventory_item_id=p_inv_item_id

and msib.organization_id=p_org_id;

if (l_last_update_date < p_date) then

return 1;

else

return 0;

end if;

end;

SELECT F1(45,204,SYSDATE) FROM DUAL;

create or replace function f1(p_inv_item_id number,

p_org_id number,

p_date date)

return date

l_last_update_date date;

begin

select last_update_date

into l_last_update_date

from mtl_system_items_b msib

where 1=1

and msib.inventory_item_id=p_inv_item_id

and msib.organization_id=p_org_id;

if (l_last_update_date < p_date) then

return l_last_update_date;

else

return l_last_update_date;

end if;

end;

SELECT F1(45,204,SYSDATE) FROM DUAL;

create or replace function f1(p_org_code varchar2)

return number

lv_count number;

begin

select count(1)

into lv_count

from mtl_system_items_interface

where 1=1

and organization_code=p_org_code;

return lv_count;

end;

SELECT F1('V1') FROM DUAL;

create or replace function f1(p_org_code varchar2)

return number

lv_count number;

begin

select count(1)

into lv_count

from mtl_system_items_interface

where 1=1

and (organization_code=p_org_code or organization_code is null);

return lv_count;

end;

SELECT F1(null)FROM DUAL;

create or replace function f1 (p_invoice_id number)

return number

l_prepay_amount number:=0;

cursor c1

select sum(nvl(prepay_amount_remaining,amount))

from ap_invoice_distributions_all

where 1=1

and invoice_id=p_invoice_id

and line_type_lookup_code in('ITEM','TAX')

;

BEGIN

OPEN C1;

FETCH C1 INTO l_prepay_amount;

CLOSE C1;

RETURN l_prepay_amount;

END;

SELECT F1(22928) FROM DUAL;

SELECT SUM(NVL(prepay_amount_remaining,AMOUNT))

FROM AP_INVOICE_DISTRIBUTIONS_ALL

WHERE INVOICE_ID=22928

and line_type_lookup_code in('ITEM','TAX');

DECLARE

P_TEST VARCHAR2(20);

CURSOR C1 IS SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE ROWNUM<=10;

BEGIN

FOR I IN C1

LOOP

P_TEST:=F1(I.INVOICE_ID);

DBMS_OUTPUT.PUT_LINE(P_TEST);

END LOOP;

END;

CREATE OR REPLACE FUNCTION F1(P_INVOICE_ID NUMBER)

RETURN VARCHAR2

L_PREPAYMENT_TYPE VARCHAR2(20);

CURSOR C1

SELECT DECODE(EARLIEST_SETTLEMENT_DATE,NULL,'PREPAYMENT','TEMPORARY')

FROM AP_INVOICES_ALL

WHERE 1=1

AND INVOICE_ID=P_INVOICE_ID;

BEGIN

OPEN C1;

FETCH C1 INTO L_PREPAYMENT_TYPE;

CLOSE C1;

RETURN L_PREPAYMENT_TYPE;

END;

SELECT INVOICE_ID,EARLIEST_SETTLEMENT_DATE

FROM AP_INVOICES_ALL;

SELECT EARLIEST_SETTLEMENT_DATE

FROM AP_INVOICES_ALL

WHERE 1=1

AND INVOICE_ID=145054

SELECT F1(145054) FROM DUAL;

--REAL TIME EXAMPLES

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

-- Function to validate Business Unit --

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

CREATE OR REPLACE FUNCTION VALIDATE_BUSINEES_UNIT(P_BUSINESS_UNIT IN VARCHAR2)

RETURN VARCHAR2

L_NAME VARCHAR2(240) := NULL;

BEGIN

SELECT hou.name

INTO l_name

FROM hr_operating_units hou

WHERE 1 = 1

AND UPPER(hou.name) = UPPER(P_BUSINESS_UNIT);

IF l_name IS NOT NULL

THEN

RETURN 'Y';

ELSE

RETURN 'N';

END IF;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.put_line( 'Invalid Operating unit ' ||P_BUSINESS_UNIT);

RETURN 'N';

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line('UNEXP Exception while validating Operating unit -' ||P_BUSINESS_UNIT || ' : '|| SUBSTR(SQLERRM,1,150));

RETURN 'N';

END;

SELECT VALIDATE_BUSINEES_UNIT('Vision Operations') FROM DUAL;

CREATE OR REPLACE FUNCTION validate_vendor_name( p_vendor_name IN VARCHAR2)

RETURN VARCHAR2

l_vendor_id NUMBER := 0;

BEGIN

SELECT sup.vendor_id

INTO l_vendor_id

FROM ap_suppliers sup

WHERE 1 = 1

AND UPPER( sup.vendor_name) = UPPER( p_vendor_name)

AND sup.enabled_flag = 'Y'

AND SYSDATE BETWEEN NVL(sup.start_date_active, SYSDATE) AND NVL(sup.end_date_active, SYSDATE);

IF l_vendor_id IS NOT NULL

THEN

RETURN 'Y';

ELSE

RETURN 'N';

END IF;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.put_line( 'Invalid Vendor name '

|| p_vendor_name);

RETURN 'N';

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line( 'UNEXP Exception while validating vendor name '

|| p_vendor_name

|| ' : '

|| SUBSTR(SQLERRM

, 1

, 150));

RETURN 'N';

END;

SELECT validate_vendor_name('TP1 Supp') FROM DUAL;

CREATE SEQUENCE EMP_SEQ;

CREATE OR REPLACE FUNCTION F1(P_EMPNO IN NUMBER)

RETURN NUMBER

LV_SAL NUMBER;

BEGIN

SELECT SAL

INTO LV_SAL

FROM EMP

WHERE EMPNO=P_EMPNO;

RETURN LV_SAL;

END;

SELECT F1(7839) FROM DUAL;

SELECT F1(EMPNO),EMPNO,ENAME,JOB FROM EMP;

SELECT * FROM XX_EMP;

TRUNCATE TABLE XX_EMP;

CREATE OR REPLACE PROCEDURE P1

CURSOR C1 IS SELECT * FROM EMP;

BEGIN

FOR I IN C1

LOOP

INSERT INTO XX_EMP

(

EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

COMM,

DEPTNO

)

VALUES

(

EMP_SEQ.NEXTVAL,

I.ENAME,

I.JOB,

I.MGR,

I.HIREDATE,

F1(I.EMPNO),

I.COMM,

I.DEPTNO

);

END LOOP;

END;

EXEC P1;

NALAM Oracle Apps Technical

Monday, 4 March 2024

Function:

No comments:

Post a Comment