Updated April 4, 2023
Introduction to PL/SQL Package
The following article provides an outline for PL/SQL Package. PL/SQL package is an object of the schema of oracle database which can contain the definitions and descriptions of a group of functionalities that are related to one another. A package in Pl/SQL usually consists of functions, procedures, subprograms, cursors, variables, cursors, and even exceptions. The PL/ SQL compiles the package and stores it inside the oracle database for further usage.
Syntax of PL/SQL Package
The syntax of the PL/ SQL package is as shown below:
CREATE [OR REPLACE] PACKAGE <name of the package >
IS
<declaration of the elements which will be public or the subprogram>
.
.
END <name of package>In the above package the terminologies used are as described here:
- Name of package: The package will be further referenced and used and even accessed by using this name used while creating it.
- Declaration part: The below part is used for declaring all the variables, cursors, exceptions and other objects that we might need inside the package. These things can either be public or private. When they are public, they are being accessed and used by even other places outside the package. While in case when these objects are declared as private, they can only be used inside the body of the PL/ SQL package. Note that the variables and either object can be initialized or we can perform any task that we need to do for that particular object one time in the package at the very beginning of the package such as setup things in the declaration part.
The syntax of the package body is as shown below:
CREATE [OR REPLACE] PACKAGE BODY <name of package>
IS
<Part where we will declare global objects>
<Defining the elements which are private in nature>
<Defining the elements which are public in nature and the other subprograms>
.
<Initializing the package>
END <name of package>The terminologies used above are as described here:
- We can define all the global elements as well as private elements. The elements which are not declared in the definition part are referred to as private things and can only be accessed within the package body.
Rules for Package Body
- For all the cursors or subprograms the body should contain a definition for each one of them.
- The subprograms and the cursors that have been defined in the body but have not been declared in the declaration part are referred to be private.
- The body of the package is dependent on the specification of the package and it may or may not be compulsory to write the package body as per the package specification.
- If we compile the specification part of the package then the body part becomes invalid. Hence, it is required to recompile the package each time after the specification part is compiled.
- The last portion of the body of the package is the initialization part where we write the things that we have to do one time at the very beginning when in a particular session the package is referred for the first time.
Advantages of PL/SQL Package
Following are the key advantages of having packages inside your PL/SQL application:
- Modularity: The code written inside the package makes the application more modular as the entities that are logically related are encapsulated within a single PL/SQL module. These entities may include constants, variables, subprograms, cursors, and exceptions. It becomes easier, reliable, manageable, reusable, and readable for the user to use them.
- Abstraction: We only show the functionality and features of the package in the specification part and hide the details about the implementation of the body of the package. This provides us with the advantage to improve the body code of the package without any of the effects on the applications and packages that are dependent on that particular package.
- Increase in performance: The loading of the package’s subprogram inside the memory is performed b oracle only the first time when we are invoking the subprogram of the package. For all the subsequent and required calls, there are none of the I/O disk operations involved which increases the performance of the application.
- Avoid the recompilation of code when not necessary: The recompilation of the code is avoided by using the packages at unnecessary moments such as if the function inside the package is changed then all the subprograms that in turn use this function are not recompiled again as they are only dependent on the specification and not the definition of the function.
- Easy authority management: We can grant the privileges to the packages as all the related objects are encapsulated inside the one package which becomes easier as compared to granting privileges to each and individual object.
Example of PL/SQL Package
Given below is the example mentioned. Consider the following example of the package.
Code:
CREATE OR REPLACE PACKAGE educba_retrieve_insert
IS
PROCEDURE insert_value (cust_record IN customers_details%ROWTYPE);
FUNCTION retrieve_value (cust_id IN NUMBER) RETURN customers_details%ROWTYPE;
END educba_retrieve_insert:
/Output:
Code:
CREATE OR REPLACE PACKAGE BODY educba_retrieve_insert
IS
PROCEDURE insert_value(cust_record IN customers_details%ROWTYPE)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO customers_details
VALUES(cust_record.customers_details_name,cust_record.customers_details_no; cust_record.billAmount,cust_record.attainee);
COMMIT;
END insert_value;
FUNCTION get_record(p_customers_details_no IN NUMBER)
RETURN customers_details%ROWTYPE
IS
l_customers_details_rec customers_details%ROWTYPE;
BEGIN
SELECT * INTO l_customers_details_rec FROM customers_details where customers_details_no=p_customers_details_no
RETURN l_customers_details_rec;
END get_record;
BEGUN
dbms_output.put_line(‘We are now in the initialization part');
END educba_retrieve_insert:
/Output:
Code:
DECLARE
l_customers_details_rec customers_details%ROWTYPE;
l_get_rec customers_details%ROWTYPE;
BEGIN
dbms output.put line(‘Add a new record into customer details table with is 1004');
l_customers_details_rec.customers_details_no:=1004;
l_customers_details_rec.customers_details_name:='Payal Udhani';
l_customers_details_rec.billAmount~20000;
l_customers_details_rec.attainee:='Mayur Sachwani’;
educba_retrieve_insert.insert_value(1_customers_details_rec);
dbms_output.put_line('Record inserted');
dbms output.put line('Now, we will call the function which will dispaly the customer record'):
l_get_rec:=educba_retrieve_insert.get_record(1004);
dbms_output.put_line('Customer name: '||l_get_rec.customers_details_name);
dbms_output.put_line('Customer number:'||l_get_rec.customers_details_no);
dbms_output.put_line('Customer billAmount:'||l_get_rec.billAmount');
dbms output.put line('Customer attainee:'||1_get_rec.attainee);
END:
/Output:
Conclusion
We can make the use of packages to encapsulate all the logically related objects such as cursors, subprograms, variables, constants, etc. inside one program unit which is completely reusable and modifiable.
Recommended Articles
We hope that this EDUCBA information on “PL/SQL Package” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
