PACKAGES IN PL-SQL

P A C K A G E S
Packages are the third type of sub programs after functions and procedures. Like procedures and functions packages are also stored in the database. But unlike functions and procedures packages should not be used as local sub programs.
A package consists of two parts, package specification and package body. Package specification consists of only the declaration of variables, functions and procedures that can be used globally.
Package body consists of definitions for the functions and procedures that are declared within the package specification.
The main purpose of a package is to group the related subprograms. As variables can also be declared within a package specification, another purpose of package is to declare the variables and cursors that can be used globally.

PACKAGE SPECIFICATION : Package specification of a package consists of the variables, cursors, declarations that can be used globally and the declarations of functions and procedures to be included within the package. A package specification was created by using the following syntax.
Syntax: SQL> CREATE OR REPLACE PACKAGE PACKAGENAME IS/OR
VAR DECLARATIONS
CURSOR DECLARATIONS
FUNCTION DECLARATIONS
PROCEDURE DECLARATIONS
END PACKAGENAME;

EX : CREATE OR REPLACE PACKAGE STUPACK AS
PROCEDURE ADDSTUDENT ( P_SNO STUDENT.SNO%TYPE,
P_SNAME STUDENT.SNAME%TYPE,
P_COURSE STUDENT.COURSE%TYPE);
PROCEDURE DELSTUDENT ( P_SNO STUDENT.SNO%TYPE);
END STUPACK;

PACKAGE BODY : Package body contains definitions for the functions and procedures declared in the package specification. Package specification and package body name must be same.

Package body creation has the following syntax.
Syntax : CREATE OR REPLACE PACKAGE BODY PBNAME IS/AS
FUNCTION FUNCTIONNAME( ARGU.LIST ) RETURN TYPE IS
LOCAL VARIABLE DECLARATION
BEGIN
EXECUTABLE CODE
EXCEPTION
ERROR HANDLING
END ENAME;
PROCEDURE PNAME ( ARGU.LIST ) IS/AS
LOCAL VARIABLE DECLARATION
BEGIN
EXECUTABLE CODE
EXCEPTION
ERROR HANDLING
END PNAME.
.
.
.
END PBNAME
EXAMPLE : CREATE OR REPLACE PACKAGE BODY STUPACK AS
PROCEDURE ADDSTUDENT ( P_SNO STUDENT.SNO%TYPE,
P_SNAME STUDENT.SNAME%TYPE,
P_COURSE STUDENT.COURSE%TYPE ) IS
BEGIN
INSERT INTO STUDENT VALUES ( P_SNO, P_SNAME, P_COURSE );
END ADDSTUDENT;
PROCEDURE DELSTUDENT ( P_SNO STUDENT.SNO%TYPE ) IS
BEGIN
DELETE FROM STUDENT WHERE SNO=P_SNO;
END DELSTUDENT;
END STUPACK;

Write a package that includes function for calculating total, average and grade and procedure to insert a record into marks table.
CREATE OR REPLACE PACKAGE.STURECORD AS
FUNCTION TOTAL ( S_M1 STUDENT.MM%TYPE,
S_M2 STUDENT.MP%TYPE) RETURN NUMBER IS;
FUNCTION AVERAGE( S_TOT STUDENT.TOTAL%TYPE ) RETURN NUMBER IS;
FUNCTION GRADE ( S_AVERAGE STUDENT.AVERAGE%TYPE) RETURN VARCHAR2 IS;
PROCEDURE INSERTRECORD ( S_SNO STUDENT.SNO%TYPE,
S_M1 STUDENT.MM%TYPE,
S_M2 STUDEMT.MP%TYPE);
END STURECORD;
PACKAGE BODY :
CREATE OR REPLACE PACKAGE BODY STURECORD AS
FUNCTION TOTAL ( S_M1 STUDENT.MM%TYPE,
S_M2 STUDENT.MP%TYPE ) RETURN NUMBER IS
BEGIN
RETURN ( S_M1 + S_M2 );
END TOTAL;
FUNCTION AVERAGE ( S_TOT STUDENT.TOTAL%TYPE ) RETURN NUMBER IS
BEGIN
RETURN ( S_TOT/2 );
END AVERAGE;
FUNCTION GRADE ( S_AVERAGE STUDENT.AVG%TYPE ) RETURN VARCHAR2 IS
GRADE VARCHAR2(10);
BEGIN
IF S_AVERAGE > 90 THEN GRADE = ‘FIRST’;
ELSIF S_AVERAGE > 60 THEN GRADE = ‘SEC’;
ELSE GRADE = ‘FAIL’;
END IF;
RETURN GRADE;
END GRADE;
PROCEDURE INSERTRECORD ( S_SNO STUDENT.SNO%TYPE,
S_M1 STUDENT.MM%TYPE,
S_M2 STUDENT.MP%TYPE ) IS
TOT NUMBER(10);
GRADE VARCHAR2(10);
AVG NUMBER(10);
BEGIN TOT := STURECORD.TOTAL( S_M1, S_M2 );
AVG := STURECORD.AVERAGE(TOT);
GRADE := STURECORD.GRADE( AVG );
INSERT INTO STUDENT VALUES ( S_SNO, S_M1, S_M2, TOT, AVG, GRADE );
END INSERTRECORD;
END STURECORD;

OVERLOADING SUBPROGRAMS
We can crate more than one functionor procedure within a package with same name. When more than one function or procedure within a package are created with same name then those functions or procedures are said to be overloaded. The overloaded functions or procedures may have the same name, but the number of arguments and their datatypes may differ.
EXAMPLE : CREATE OR REPLACE PACKAGE OVERLOADED AS
PROCEDURE ADDSTUDENT ( P_SNO STUDENT.SNO%TYPE,
P_SNAME STUDENT.SNAME%TYPE,
P_COURSE STUDENT.COURSE%TYPE );
PROCEDURE ADDSTUDENT (P_SNO STUDENT.SNO%TYPE,
P_MM STUDENT.MM%TYPE,
P_MP STUDENT.MP%TYPE );
END OVERLOAD;
EXAMPLE : CREATE OR REPLACE PACKAGE BODY OVERLOADED IS
PROCEDURE ADDSTUDENT ( P_SNO STUDENT.SNO%TYPE,
P_SNAME STUDENT.SNAME%TYPE,
P_COURSE STUDENT.COURSE%TYPE )
BEGIN
INSERT INTO STUDENT VALUES ( P_SNO, P_SNAME, P_COURSE );
END ADDSTUDENT;
PROCEDURE ADDSTUDENT (P_SNO STUDENT.SNO%TYPE,
P_M1 STUDENT.MM%TYPE,
P_M2 STUDENT.MP%TYPE ) IS
TOT NUMBER(5);
A NUMBER(5,2);
G VARCHAR2(10);
BEGIN
TOT := TOTAL( P_M1, P_M2 );
A := AVERAGE( TOT );
G := GRADE(A);
INSERT INTO STUDENT VALUES ( P_SNO, P_M1, P_M2, TOT, A, G );
END ADDSTUDENT;
END OVERLOAD;


OVERLOADING IS NOT POSSIBLE IN FOLLOWING SITUTATIONS

1.When the number of arguments and the datatypes are same, but the modes of parameters are different.
Ex : PROCEDURE TEST( A1 IN NUMBER );
PROCEDURE TEST( A1 OUT NUMBER );
2.Overloading is not possible when the functions are differed only in their return type.
Ex: FUNCTION TEST ( A1 IN NUMBER ) RETURN NUMBER;
FUNCTION TEST ( A2 IN NUMBER ) RETURN VARCHAR2;
3.Overloading is not possible when the parameters are different in their datatype and those datatypes are of same family.
Ex : FUNCTION TEST ( A VARCHAR2 ) RETURN NUM;
FUNCTION TEST ( A CHAR ) RETURN NUM;