NALAM Oracle Apps Technical : Packages:

Packages:

*********

-->Package is a database object which is used encapsulate variables,constants,procedures,cursors,functions and types into single unit.

--> A package can be defined as an oracle object that is compiled and stored in database.

-->Once it is compiled and stored in database it can be used by all the users of database who have executable permissions on oracle database.

-->Packages does not accepts parameters,can not be nested.

-->Generally packages are used to improve the performance of the application.

-->It is useful to logically group related objects.

-->search time is reduced

-->Packages have two types

a)Package specification

b)Package body

-->In package specification we are defining global data and also declare objects,subprograms. Where as in package body we are implementing subprograms and also package body subprogram internally behaves like a private subprogram.

Package Specification Syntax:

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

CREATE OR REPLACE PACKAGE PACKAGENAME

IS/AS

GLOBAL VARIABLE DECLARATION;

CONSTANT DECLARATION;

CURSOR DECLARATION;

TYPES DECLARATION;

PROCEDURE DECLARATION;

FUNCTION DECLARATION;

END [PACKAGENAME];

Package Body Syntax:

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

CREATE OR REPLACE PACKAGE BODY PACKAGE NAME

IS/AS

PROCEDURE IMPLEMENTATION;

FUNCTION IMPLEMENTATION;

END [PACKAGENAME];

Invoking Packaged Subprogram:

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

1)EXEC PACKAGENAME.PROCEDURENAME(ACTUAL PARAMETERS);

2)SELECT PACKAGENAME.FUNCTIONNAME(ACTUAL PARAMETERS)FROM DUAL;

EX:

***

CREATE OR REPLACE PACKAGE PK1

PROCEDURE A;

PROCEDURE B;

END;

CREATE OR REPLACE PACKAGE BODY PK1

PROCEDURE A

BEGIN

DBMS_OUTPUT.PUT_LINE('FIRST PROCEDURE');

END;

PROCEDURE B

BEGIN

DBMS_OUTPUT.PUT_LINE('SECOND PROCEDURE');

END;

EXEC PK1.A;

EXEC PK1.B;

EXEC PROCEDURENAME;

EXEC PACKAGENAME.PROCEDURENAME;

EXEC PKG1.P1;

SELECT FUNCTIONNAME FROM DUAL;

SEELCT PACKAGENAME.FUNCTIONNAME FROM DUAL;

SELECT PKG1.F1 FROM DUAL;

BEGIN

P1;

END;

BEGIN

PKG1.P1;

END;

DECLARE

A VARCHAR2(30);

BEGIN

A:=PKG1.F1;

DBMS_OUTPUT.PUT_LINE(A);

END;

EX:

****

CREATE OR REPLACE PACKAGE APPS.PK2

PROCEDURE P1(A NUMBER);

PROCEDURE P2;

PROCEDURE P3;

FUNCTION F1(A NUMBER) RETURN NUMBER;

FUNCTION F2 RETURN VARCHAR2;

END;

CREATE OR REPLACE PACKAGE BODY APPS.PK2

PROCEDURE P1(A NUMBER)

B NUMBER;

BEGIN

B :=A*A;

DBMS_OUTPUT.PUT_LINE(B);

END P1;

PROCEDURE P2

LV_ENAME EMP.ENAME%TYPE;

LV_SAL EMP.SAL%TYPE;

BEGIN

SELECT ENAME,SAL

INTO LV_ENAME,LV_SAL

FROM EMP

WHERE EMPNO=7782;

DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOESNOT EXIST');

WHEN OTHERS THEN

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

END P2;

PROCEDURE P3

CURSOR C1 IS SELECT * FROM EMP;

I EMP%ROWTYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO I;

EXIT WHEN C1%NOTFOUND;

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

END LOOP;

DBMS_OUTPUT.PUT_LINE('TOTAL NO OF RECORDS :'||C1%ROWCOUNT);

CLOSE C1;

END P3;

FUNCTION F1(A NUMBER) RETURN NUMBER

BEGIN

RETURN A;

END F1;

FUNCTION F2 RETURN VARCHAR2

LV_CNT NUMBER;

BEGIN

SELECT COUNT(*)

INTO LV_CNT

FROM EMP;

RETURN LV_CNT;

END;

END PK2;

EXEC PK2.P1(10);

EXEC PK2.P2;

EXEC PK2.P3;

SELECT PK2.F1(100) FROM DUAL;

SELECT PK2.F2 FROM DUAL;

EX:

****

DROP TABLE XX_EMP;

CREATE TABLE XX_EMP AS SELECT * FROM EMP WHERE 1=2;

DROP TABLE XX_EMP1;

CREATE TABLE XX_EMP1 AS SELECT * FROM EMP WHERE 1=2;

CREATE OR REPLACE PACKAGE PK1

PROCEDURE P1(P_EMPNO IN NUMBER);

PROCEDURE P2;

PROCEDURE P3(P_DEPTNO IN NUMBER);

FUNCTION F1(P_EMPNO IN NUMBER) RETURN VARCHAR2;

FUNCTION F2 RETURN VARCHAR2;

FUNCTION F3(P_DEPTNO IN NUMBER) RETURN VARCHAR2;

FUNCTION F4(P_DEPTNO NUMBER,P_AMT NUMBER)RETURN VARCHAR2;

END PK1;

CREATE OR REPLACE PACKAGE BODY PK1

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('SALARY IS : '||LV_SAL);

EXCEPTION

WHEN NO_dATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('EMPNO DOES NOT EXIST');

END P1;

PROCEDURE P2

CURSOR C1 IS SELECT ENAME FROM EMP;

BEGIN

FOR I IN C1

LOOP

DBMS_OUTPUT.PUT_LINE(I.ENAME);

END LOOP;

END P2;

PROCEDURE P3(P_DEPTNO IN NUMBER)

LV_CNT NUMBER;

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

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_CNT :=C1%ROWCOUNT;

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('NO OF ROWS INSERTED : '||LV_CNT);

END P3;

FUNCTION F1(P_EMPNO IN NUMBER) RETURN VARCHAR2

LV_SAL NUMBER;

BEGIN

SELECT SAL

INTO LV_SAL

FROM EMP

WHERE EMPNO=P_EMPNO;

RETURN LV_SAL;

EXCEPTION

WHEN NO_dATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('EMPNO DOES NOT EXIST');

END F1;

FUNCTION F2 RETURN VARCHAR2

CURSOR C1 IS SELECT ENAME FROM EMP;

BEGIN

FOR I IN C1

LOOP

DBMS_OUTPUT.PUT_LINE(I.ENAME);

END LOOP;

RETURN 0;

END F2;

FUNCTION F3(P_DEPTNO IN NUMBER) RETURN VARCHAR2

LV_COUNT NUMBER :=0;

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

BEGIN

FOR I IN C1

LOOP

INSERT INTO XX_EMP1

VALUES

(

I.EMPNO,

I.ENAME,

I.JOB,

I.MGR,

I.HIREDATE,

I.SAL,

I.COMM,

I.DEPTNO

);

LV_COUNT :=LV_COUNT+1;

END LOOP;

COMMIT;

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

RETURN 0;

END F3;

FUNCTION F4(P_DEPTNO NUMBER,P_AMT NUMBER)

RETURN VARCHAR2

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

BEGIN

FOR I IN C1

LOOP

UPDATE XX_EMP1

SET SAL=SAL+P_AMT

WHERE EMPNO=I.EMPNO;

END LOOP;

RETURN 0;

END F4;

END PK1;

EXEC PK1.P1(7876);

SELECT PK1.F1(7876) FROM DUAL;

EXEC PK1.P2;

SELECT PK1.F2 FROM DUAL;

DECLARE

P_TEST VARCHAR2(20);

BEGIN

P_TEST:=PK1.F2;

END;

EXEC PK1.P3(10);

SELECT * FROM XX_EMP;

DECLARE

P_TEST VARCHAR2(20);

BRGIN

P_TEST :=PK1.F4(30,2000);

END;

SELECT * FROM XX_EMP1 WHERE DEPTNO=30;

DECLARE

P_TEST VARCHAR2(20);

BEGIN

P_TEST :=PK1.F3(10);

END;

SELECT * FROM XX_EMP1;

EX:

****

create or replace package pk1

procedure p1;

procedure p2;

procedure p3;

function f1 return number;

function f2 return varchar2;

end;

CREATE TABLE EMP200 AS SELECT * FROM EMP WHERE 1=2;

ALTER TABLE EMP200 ADD(CREATION_DATE DATE,CREATED_BY NUMBER,LAST_UPDATE_dATE DATE,LAST_UPDATED_BY NUMBER,LAST_UPDATE_LOGIN NUMBER);

create or replace package body pk1

procedure p1

cursor c1 is select * from emp;

i emp%rowtype;

begin

open c1;

loop

fetch c1 into i;

exit when c1%notfound;

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

end loop;

close c1;

end p1;

procedure p2

cursor c1 is select * from emp;

cursor c2 is select * from dept;

begin

dbms_output.put_line('*********emp details program start*********');

for i in c1

loop

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

end loop;

dbms_output.put_line('*********emp details program end*********');

dbms_output.put_line(' ');

dbms_output.put_line('*********dept details program start*********');

for j in c2

loop

dbms_output.put_line(j.deptno||','||j.dname||','||j.loc);

end loop;

dbms_output.put_line('*********dept details program start*********');

end p2;

PROCEDURE P3

cursor c1 is select * from emp;

LV_COUNT NUMBER :=0;

begin

for i in c1

loop

LV_COUNT :=LV_COUNT+1;

insert into emp200

(

empno,

ename,

job,

mgr,

hiredate,

sal,

comm,

deptno,

creation_Date,

created_by,

last_update_Date,

last_updated_by,

last_update_login

)

values

(

i.empno,

i.ename,

i.job,

i.mgr,

i.hiredate,

i.sal,

i.comm,

i.deptno,

sysdate,

fnd_global.user_id,

sysdate,

fnd_global.user_id,

fnd_global.user_id

);

end loop;

DBMS_OUTPUT.PUT_LINE('TOTAL COUNT IS '||LV_COUNT);

end P3;

FUNCTION F1 RETURN NUMBER

LV_COUNT NUMBER;

BEGIN

SELECT COUNT(*)

INTO LV_COUNT

FROM EMP

WHERE DEPTNO=30;

RETURN LV_COUNT;

END F1;

FUNCTION F2 RETURN VARCHAR2

LV_ENAME VARCHAR2(20);

BEGIN

SELECT ENAME

INTO LV_ENAME

FROM EMP

WHERE EMPNO=7839;

RETURN LV_ENAME;

END F2;

end pK1;

EXEC PK1.P1;

EXEC PK1.P2;

EXEC PK1.P3;

SELECT * FROM EMP200;

SELECT PK1.F1 FROM DUAL;

SELECT PK1.F2 FROM DUAL;

EX:

****

CREATE TABLE XX_EMP_TEST(EMPNO,ENAME,JOB,SAL,DEPTNO) AS SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP WHERE 1=2;

CREATE OR REPLACE PACKAGE PK3

PROCEDURE EMPJOIN (P_EMPNO NUMBER,P_ENAME VARCHAR2,P_JOB VARCHAR2,P_SAL NUMBER,P_DEPTNO NUMBER);

PROCEDURE EMPRESIGN(P_EMPNO NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY PK3

PROCEDURE EMPJOIN (P_EMPNO NUMBER,P_ENAME VARCHAR2,P_JOB VARCHAR2,P_SAL NUMBER,P_DEPTNO NUMBER)

BEGIN

INSERT INTO XX_EMP_TEST

(

EMPNO,

ENAME,

JOB,

SAL,

DEPTNO)

VALUES

(

P_EMPNO,

P_ENAME,

P_JOB,

P_SAL,

P_DEPTNO);

COMMIT;

END;

PROCEDURE EMPRESIGN(P_EMPNO NUMBER)

BEGIN

DELETE FROM XX_EMP_TEST WHERE EMPNO=P_EMPNO;

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('EMPLOYEE DELETED SUCCESFULLY');

ELSIF SQL%NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE');

END IF;

END;

END PK3;

EXEC PK3.EMPJOIN(100,'A','SOFTWARE',3000,10);

EXEC PK3.EMPRESIGN(100);

EX:

***

CREATE TABLE XX_DEPT_INSERT

SELECT *

FROM DEPT

WHERE 1=2;

CREATE TABLE XX_EMP_INSERT

SELECT *

FROM EMP

WHERE 1=2;

ALTER TABLE XX_EMP_INSERT ADD (CREATION_DATE DATE,CREATED_BY NUMBER,LAST_UPDATE_DATE DATE,LAST_UPDATED_BY NUMBER,LAST_UPDATE_LOGIN NUMBER);

ALTER TABLE XX_DEPT_INSERT ADD (CREATION_DATE DATE,CREATED_BY NUMBER,LAST_UPDATE_DATE DATE,LAST_UPDATED_BY NUMBER,LAST_UPDATE_LOGIN NUMBER);

CREATE OR REPLACE PACKAGE PK4

PROCEDURE DEPT_DATA;

PROCEDURE EMP_DATA;

END PK4;

CREATE OR REPLACE PACKAGE BODY PK4

PROCEDURE DEPT_DATA

CURSOR C1 IS SELECT * FROM DEPT;

LV_DEPT_CNT NUMBER;

LV_DEPT_INSERT_CNT NUMBER :=0;

LV_DEPT_ERR_CNT NUMBER :=0;

BEGIN

FOR I IN C1

LOOP

BEGIN

INSERT INTO XX_DEPT_INSERT

(

DEPTNO,

DNAME,

LOC,

CREATION_DATE,

CREATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_LOGIN

)

VALUES

(

I.DEPTNO,

I.DNAME,

I.LOC,

SYSDATE,

FND_GLOBAL.USER_ID,

SYSDATE,

FND_GLOBAL.USER_ID,

FND_GLOBAL.USER_ID

);

LV_DEPT_INSERT_CNT :=LV_DEPT_INSERT_CNT+1;

EXCEPTION

WHEN OTHERS THEN

LV_DEPT_ERR_CNT :=LV_DEPT_ERR_CNT+1;

END;

END LOOP;

COMMIT;

BEGIN

SELECT COUNT(*)

INTO LV_DEPT_CNT

FROM DEPT;

END;

DBMS_OUTPUT.PUT_LINE('*************DEPT TABLE RECORD COUNT************************');

DBMS_OUTPUT.PUT_LINE('NO OF RECORDS N EMP TABLE :'||LV_DEPT_CNT);

DBMS_OUTPUT.PUT_LINE('NO OF RECORDS ARE PROCESSED :'||LV_DEPT_INSERT_CNT);

DBMS_OUTPUT.PUT_LINE('NO OF RECORDS ARE ERRORED :'||LV_DEPT_ERR_CNT);

DBMS_OUTPUT.PUT_LINE('*************DEPT TABLE RECORD COUNT************************');

END DEPT_DATA;

PROCEDURE EMP_DATA

CURSOR C1 IS SELECT * FROM EMP;

LV_EMP_CNT NUMBER;

LV_EMP_INSERT_CNT NUMBER :=0;

LV_EMP_ERR_CNT NUMBER :=0;

BEGIN

FOR I IN C1

LOOP

BEGIN

INSERT INTO XX_EMP_INSERT

(

EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

COMM,

DEPTNO,

CREATION_DATE,

CREATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_LOGIN

)

VALUES

(

I.EMPNO,

I.ENAME,

I.JOB,

I.MGR,

I.HIREDATE,

I.SAL,

I.COMM,

I.DEPTNO,

SYSDATE,

FND_GLOBAL.USER_ID,

SYSDATE,

FND_GLOBAL.USER_ID,

FND_GLOBAL.USER_ID

);

LV_EMP_INSERT_CNT :=LV_EMP_INSERT_CNT+1;

EXCEPTION

WHEN OTHERS THEN

LV_EMP_ERR_CNT :=LV_EMP_ERR_CNT+1;

END;

END LOOP;

COMMIT;

BEGIN

SELECT COUNT(*)

INTO LV_EMP_CNT

FROM EMP;

END;

DBMS_OUTPUT.PUT_LINE('*************EMP TABLE RECORD COUNT************************');

DBMS_OUTPUT.PUT_LINE('NO OF RECORDS N DEPT TABLE :'||LV_EMP_CNT);

DBMS_OUTPUT.PUT_LINE('NO OF RECORDS ARE PROCESSED :'||LV_EMP_INSERT_CNT);

DBMS_OUTPUT.PUT_LINE('NO OF RECORDS ARE ERRORED :'||LV_EMP_ERR_CNT);

DBMS_OUTPUT.PUT_LINE('*************EMP TABLE RECORD COUNT************************');

END EMP_DATA;

END PK4;

EXEC PK4.DEPT_DATA;

EXEC PK4.EMP_DATA;

CREATE OR REPLACE PACKAGE PK5

PROCEDURE P13(P_DEPTNO IN NUMBER,P_TOTAL OUT NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY PK5

PROCEDURE P13(P_DEPTNO IN NUMBER,P_TOTAL OUT NUMBER)

BEGIN

SELECT COUNT(*)

INTO P_TOTAL

FROM EMP

WHERE DEPTNO=P_DEPTNO;

DBMS_OUTPUT.PUT_LINE(P_TOTAL);

END;

DECLARE

X NUMBER;

BEGIN

PK5.P13(10,X);

DBMS_OUTPUT.PUT_LINE(X);

END;

DROP TABLE XX_EMP;

CREATE TABLE XX_EMP AS SELECT * FROM EMP;

CREATE OR REPLACE PACKAGE PK4

PROCEDURE P1;

PROCEDURE P2(P_EMPNO IN NUMBER,P_INCR IN NUMBER);

PROCEDURE P3(P_DEPTNO IN NUMBER);

END;

DROP TABLE XX_EMP;

CREATE TABLE XX_EMP AS SELECT * FROM EMP WHERE 1=2;

CREATE OR REPLACE PACKAGE BODY PK4

PROCEDURE P1

CURSOR C1 IS SELECT * FROM EMP1;

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

);

END LOOP;

END P1;

PROCEDURE P2(P_EMPNO IN NUMBER,P_INCR IN NUMBER)

BEGIN

UPDATE XX_EMP

SET SAL=SAL+P_INCR

WHERE EMPNO=P_EMPNO;

END P2;

PROCEDURE P3(P_DEPTNO IN NUMBER)

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

BEGIN

FOR I IN C1

LOOP

DELETE FROM XX_EMP

WHERE EMPNO=I.EMPNO;

END LOOP;

END P3;

END PK4;

EXEC PK4.P1;

EXEC PK4.P2(7698,500);

CREATE OR REPLACE PACKAGE PKG1

PROCEDURE P1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER);

FUNCTION F1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER) RETURN VARCHAR2;

END PKG1

CREATE OR REPLACE PACKAGE BODY PKG1

PROCEDURE P1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER)

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.EMPNO||','||I.ENAME||','||I.SAL||','||P_DEPTNO);

END;

END LOOP;

END P1;

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.EMPNO||','||I.ENAME||','||I.SAL||','||P_DEPTNO);

END;

END LOOP;

RETURN 0;

END F1;

END PKG1;

DECLARE

P_SAL NUMBER;

BEGIN

PKG1.P1(10,P_SAL);

END;

DECLARE

P_SAL NUMBER;

P_TEST VARCHAR2(20);

BEGIN

P_TEST :=PKG1.F1(10,P_SAL);

END;

CREATE OR REPLACE PACKAGE PKG_OUT

PROCEDURE P1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER);

FUNCTION F1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER)RETURN NUMBER;

END;

CREATE OR REPLACE PACKAGE BODY PKG_OUT

PROCEDURE P1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER)

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(P_SAL);

END;

END LOOP;

END P1;

FUNCTION F1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER)

RETURN NUMBER

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(P_SAL);

END;

END LOOP;

RETURN 0;

END F1;

END PKG_OUT;

--TESTING

DECLARE

P_SAL NUMBER;

BEGIN

PKG_OUT.P1(10,P_SAL);

END;

DECLARE

P_SAL NUMBER;

P_TEST VARCHAR2(20);

BEGIN

P_TEST :=PKG_OUT.F1(30,P_SAL);

END;

Global Variable:

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

-->It is one of the varibale which is used to define in package specification and implement in package body that variables are call it as a global variable.

Local Variable:

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

-->It is one of the variable which is used to define in programs(Procedure,Function)and implement with in the program only.

EX:

***

--GLOBAL VARIABLE DECLARATION

CREATE OR REPLACE PACKAGE PK1

A NUMBER :=200 ; --GLOBAL VARIABLE DECLARATION

PROCEDURE P1;

PROCEDURE P2;

END PK1;

CREATE OR REPLACE PACKAGE BODY PK1

PROCEDURE P1

B NUMBER :=100;

BEGIN

DBMS_OUTPUT.PUT_LINE('LOCAL VARIABLE IS :'||B);

DBMS_OUTPUT.PUT_LINE('GLOBAL VARIABLE IS : '||A);

END P1;

PROCEDURE P2

BEGIN

DBMS_OUTPUT.PUT_LINE('GLOBAL VARIABLE IS : '||A);

END P2;

END PK1;

CREATE OR REPLACE PACKAGE PK1

PROCEDURE P1;

PROCEDURE P2;

END PK1;

CREATE OR REPLACE PACKAGE BODY PK1

A NUMBER :=500; --GLOBAL VARIABLE DECLARATION

PROCEDURE P1

B NUMBER :=400;

BEGIN

DBMS_OUTPUT.PUT_LINE('GLOBAL VARIABLE :'||A); --GLOBAL VARIABLE DECLARATION

DBMS_OUTPUT.PUT_LINE('LOCAL VARIABLE :'||B); --LOCAL VARIABLE DECLARATION

END;

PROCEDURE P2

BEGIN

DBMS_OUTPUT.PUT_LINE('GLOBAL VARIABLE: '||A); --PRINT GLOBAL VARIABLE

END;

EXEC PK1.P1;

EXEC PK1.P2;

--GLOBAL VARIABLE DECLARATION in package specification

CREATE OR REPLACE PACKAGE PK2

A NUMBER;

PROCEDURE P1(B NUMBER,C NUMBER);

FUNCTION F1(B NUMBER,C NUMBER) RETURN number;

END;

CREATE OR REPLACE PACKAGE BODY PK2

PROCEDURE P1(B NUMBER,C NUMBER)

BEGIN

A:=B+C;

DBMS_OUTPUT.PUT_LINE('Addition of two numbers is :'||A);

END;

FUNCTION F1(B NUMBER,C NUMBER) RETURN number

BEGIN

A:=B*C;

RETURN A;

END;

EXEC PK2.P1(100,200);

SELECT PK2.F1(100,200);

CREATE OR REPLACE PACKAGE PK1

PROCEDURE P1;

PROCEDURE P2;

END PK1;

CREATE OR REPLACE PACKAGE BODY PK1

CURSOR C1 IS SELECT * FROM EMP; --GLOBAL CURSOR DECLARATION

PROCEDURE P1

I EMP%ROWTYPE;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO I;

EXIT WHEN C1%NOTFOUND;

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

END LOOP;

CLOSE C1;

END;

PROCEDURE P2

CURSOR C2 IS SELECT * FROM DEPT;

BEGIN

FOR J IN C2

LOOP

DBMS_OUTPUT.PUT_LINE(J.DEPTNO||','||J.DNAME||','||J.LOC);

END LOOP;

FOR K IN C1

LOOP

DBMS_OUTPUT.PUT_LINE(K.ENAME);

END LOOP;

END;

EXEC PK1.P1;

EXEC PK1.P2;

CREATE OR REPLACE PACKAGE PK1

PROCEDURE P1;

PROCEDURE P2;

END;

CREATE OR REPLACE PACKAGE BODY PK1

CURSOR C1 IS SELECT * FROM EMP; --GLOBAL CURSOR

PROCEDURE P1

BEGIN

FOR I IN C1

LOOP

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

END LOOP;

END;

PROCEDURE P2

CURSOR C2 IS SELECT * FROM DEPT;

BEGIN

FOR J IN C2

LOOP

DBMS_OUTPUT.PUT_LINE(J.DEPTNO||','||J.LOC);

END LOOP;

FOR K IN C1

LOOP

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

END LOOP;

END P2;

END;

EXEC PK1.P1;

EXEC PK1.P2;

EX:

****

CREATE OR REPLACE PACKAGE PK2

PROCEDURE P1(B NUMBER,C NUMBER);

FUNCTION F1(B NUMBER,C NUMBER) RETURN NUMBER;

END;

CREATE OR REPLACE PACKAGE BODY PK2

PROCEDURE P1(B NUMBER,C NUMBER)

A NUMBER;

BEGIN

A:=B+C;

DBMS_OUTPUT.PUT_LINE('Addition of two numbers is :'||A);

END;

FUNCTION F1(B NUMBER,C NUMBER) RETURN number

A NUMBER;

BEGIN

A:=B*C;

RETURN A;

END;

exec pk2.p1(10,20);

select pk2.f1(10,20) from dual;

CREATE OR REPLACE PACKAGE PK1

PROCEDURE CAL_COMM(VDEPT NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY PK1

procedure cal_comm

(vdept number) is

cursor c1 is select empno,ename,comm from emp

where deptno = vdept;

i c1%rowtype;

begin

dbms_output.put_line

(' Employ commision Report ');

for i in c1 loop

if i.comm is null then

i.comm := 3000;

elsif i.comm = 0 then

i.comm := 2500;

else

i.comm := i.comm + i.comm * .25;

end if;

update emp set comm = i.comm

where empno = i.empno;

dbms_output.put_line(i.empno||' '||i.ename

||' '||i.comm);

end loop;

--commit;

end;

END;

exec pk1.cal_comm(10);

CREATE OR REPLACE PACKAGE PK1

PROCEDURE P1(P_EMPNO IN NUMBER,P_SAL OUT NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY PK1

PROCEDURE P1 (P_EMPNO IN NUMBER,P_SAL OUT NUMBER)

BEGIN

SELECT SAL

INTO P_SAL

FROM EMP

WHERE EMPNO=P_EMPNO;

DBMS_OUTPUT.PUT_LINE(P_SAL);

END P1;

END PK1;

EXEC PK1.P1(7839,P_SAL); --IT WILL THROW THE ERROR

-- SINGLE VALUE FOR OUT PARAMETER

DECLARE

P_SAL NUMBER;

BEGIN

PK1.P1(7839,P_SAL);

END;

--MULTIPLE VALUES FOR OUT PARAMETER USING FOR LOOP

DECLARE

CURSOR C1 IS SELECT EMPNO FROM EMP;

P_SAL NUMBER;

BEGIN

FOR I IN C1

LOOP

PK1.P1(I.EMPNO,P_SAL);

END LOOP;

END;

Procedure OR Function Overloading:

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

-->Overloading refers to same name can be used for different purposes i.e we are implementing overloading procedures through packages only,those procedures having same name and also different types of arguments.

Package Specification:

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

CREATE OR REPLACE PACKAGE PK3

PROCEDURE P1(A NUMBER,B NUMBER);

PROCEDURE P1(X NUMBER,Y NUMBER);

END;

Package Body:

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

CREATE OR REPLACE PACKAGE BODY PK3

PROCEDURE P1(A NUMBER,B NUMBER)

C NUMBER;

BEGIN

C:=A+B;

DBMS_OUTPUT.PUT_LINE(C);

END;

PROCEDURE P1(X NUMBER,Y NUMBER)

Z NUMBER;

BEGIN

Z:=X+Y;

DBMS_OUTPUT.PUT_LINE(Z);

END;

EXEC PK3.P1(A=>10,B=>20);

NALAM Oracle Apps Technical

Monday 4 March 2024

Packages:

No comments:

Post a Comment