How to create Package in SQL with example
Posted By : Harish Kalra | 30-Dec-2018
Overview of Package: A package is a schema object that bunches intelligently related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where numerous applications can share its substance.
A package will have two required parts −
1. Package specification
2. Package body or definition
Package specification: The specification like as an interface to the package. It just DECLARES the sorts, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. At the end of the day, it contains all data about the substance of the package, however, prohibits the code for the subprograms.
All objects set in the specification are called public objects. Any subprogram not in the package specification but rather coded in the package body is known as a private article.
The accompanying code piece demonstrates a package specification having a solitary procedure. You can have numerous global variables characterized and various procedures or capacities inside a package.
Syntax :
CREATE PACKAGE BODY Package_name AS
PROCEDURE Implemention
END procedure_name;
END package_name;
/
How To execute the procedure from the package: The package elements (variables, procedures or functions) are gotten to with the accompanying grammar −
Syntax :
exec Package_name.procedure_name(Paremeter)
Example :
For Body,
CREATE OR REPLACE PACKAGE HR AS
PROCEDURE GET_DETAIL()
END HR;
After excute this code we will get Package created in Output
For Specification,
CREATE PACKAGE BODY HR AS
PROCEDURE GET_DETAIL() IS
BEGIN
SELECT EMP_ID,EMP_SAL
FROM HR_EMP
END GET_DETAIL;
END HR;
/
After executing above code we will get Package body created in the output
For getting result :
exec HR.GET_DETIAL();
If you have any Query regarding Packages in SQL then Please Feel to write in comment session.
Thanks
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Harish Kalra
He is active in his work, as well as a quick learner. He has been a good resource to our company.