Oracle Package


Basically packages is set of procedures, functions, cursors, constraints and exceptions in one unit. To execute the package we write the code as follows:

EXECUTE SALARY_PACKAGE.NEW_WORKER('MICE');

In above example SALARY_PACKAGE is package name and NEW_WORKER is procedure name.

Creating Package

To create package user should have the CREATE PROCEDURE privilege. Two create package two techniques have to be done separetly:

(i) Creating package specification
A package specification includes list of functions, procedures, variables, constraints, 
cursors nd exceptions and will be available for package body.

CREATE OR REPLACE PACKAGE <NAME>
-------
-------

Above code should be saved in saperate file with saperate name.

(ii) Creating package body
The name of package body should be equal to name of package specification but file name should be
different than above file name. 

CREATE OR REPLACE PACKAGE BODY <NAME>
-------
-------

Above code should be saved in saperate file with saperate name. 

Exercise

Exercise to create package specification Type EDIT MPACK and type below code and save it.

CREATE OR REPLACE PACKAGE MICEPACK
AS
FUNCTION NETSALARY
(ID IN SALARY.EMP_NO%TYPE)
RETURN NUMBER;
PROCEDURE TAX
(ID IN SALARY.EMP_NO%TYPE, TAX OUT NUMBER);
PROCEDURE TOTSALES
(CID IN CSTMAST.CSTID%TYPE, SAL OUT NUMBER);
END;

Now create another file type EDIT MPACKB and type the below code and save it.

CREATE OR REPLACE PACKAGE BODY MICEPACK
AS
FUNCTION NETSALARY(ID IN SALARY.EMP_NO%TYPE)
RETURN NUMBER
IS
NETSAL SALARY.BASIC%TYPE;
BEGIN
SELECT SUM(BASIC)+SUM(COMMISSION)-SUM(DEDUCTION)
INTO NETSAL FROM SALARY
WHERE EMP_NO=ID;
RETURN(NETSAL);
END;

Deleting package

To delete the package that is both files specification and body file, use the following code-

DROP PACKAGE <package name>

Viwing all packages, procedures, functions, packages and package body

SELECT * FROM USER_SOURCE;

Comments

Popular posts from this blog

Migrating database from ASP.NET Identity to ASP.NET Core Identity

Customize User's Profile in ASP.NET Identity System