PL/SQL Packages
PL/SQL is a programming language that is an extended version of SQL. The PL/SQL is SQL with features of procedural programming languages. PL/SQL is a highly structured language. PL/SQL can only be executed in an Oracle database. PL/SQL allows you to write blocks of code that can contain variables, constants, loops, conditions, exceptions, and other elements of programming. PL/SQL is a comprehensive programming language solution for building critical applications on Oracle Databases.
Packages and Their Needs
PL/SQL packages are a way to organize and encapsulate related procedures, functions, variables, triggers, and other PL/SQL items into a single item. Packages provide a modular approach to write and maintain the code. It makes it easy to manage large codes.
A package is compiled and then stored in the database, which then can be shared with many applications. The package also has specifications, which declare an item to be public or private. Public items can be referenced from outside of the package.
A PL/SQL package is a collection of related Procedures, Functions, Variables, and other elements that are grouped for Modularity and Reusability.
The needs of the Packages are described below:
- Modularity: Packages provide a modular structure, allowing developers to organize and manage code efficiently.
- Code Reusability: Procedures and functions within a package can be reused across multiple programs, reducing redundancy.
- Private Elements: Packages support private procedures and functions, limiting access to certain code components.
- Encapsulation: Packages encapsulate related logic, protecting internal details and promoting a clear interface to other parts of the code.
PL/SQL Package
A PL/SQL package consists of two parts:
- A package Specification.
- A package Body.
Package Specification
The package specification declares the public interface of the package. It includes declarations of procedures, functions, variables, cursors, and other constructs that are meant to be accessible from outside the package. The specification is like a header file that defines what a package can do.
Example of Package Specification
CREATE OR REPLACE PACKAGE my_package AS
PROCEDURE my_procedure(p_param1 NUMBER);
FUNCTION calculate_sum(x NUMBER, y NUMBER) RETURN NUMBER;
-- Other declarations...
END my_package;
Package Body
The package body contains the implementation of the details of the package. It includes the coding of the procedures or functions which are decalared in the package specification. The body can also contain private variables and procedures that are not exposed to outside the code.
Example of Package Body
CREATE OR REPLACE PACKAGE BODY my_package AS
PROCEDURE my_procedure(p_param1 NUMBER) IS
BEGIN
-- Implementation code...
END my_procedure;
FUNCTION calculate_sum(x NUMBER, y NUMBER) RETURN NUMBER IS
BEGIN
-- Implementation code...
END calculate_sum;
-- Other implementation details...
END my_package;
Once your create your package in above two steps, you can use it in PL/SQL codes. This allows for modular programming, code reuse, and better maintenance of the the code base.
To Use the Package in Our Code, Follow the Below Pattern
DECLARE
result NUMBER;
BEGIN
-- Call a procedure from the package
my_package.my_procedure(42);
-- Call a function from the package
result := my_package.calculate_sum(10, 20);
-- Other code...
END;
Example 1
-- Enable the display of server output
SET SERVEROUTPUT ON;
-- Create a PL/SQL package specification
CREATE OR REPLACE PACKAGE math_operations AS
-- Procedure to add two numbers with an output parameter
PROCEDURE add_numbers(x NUMBER, y NUMBER, result OUT NUMBER);
-- Function to multiply two numbers
FUNCTION multiply_numbers(x NUMBER, y NUMBER) RETURN NUMBER;
END math_operations;
/
-- Create the body of the math_operations package
CREATE OR REPLACE PACKAGE BODY math_operations AS
-- Implementation of the add_numbers procedure
PROCEDURE add_numbers(x NUMBER, y NUMBER, result OUT NUMBER) IS
BEGIN
result := x + y;
END add_numbers;
-- Implementation of the multiply_numbers function
FUNCTION multiply_numbers(x NUMBER, y NUMBER) RETURN NUMBER IS
BEGIN
RETURN x * y;
END multiply_numbers;
END math_operations;
/
-- PL/SQL block to test the math_operations package
DECLARE
-- Declare variables to store results
sum_result NUMBER;
product_result NUMBER;
BEGIN
-- Call the procedure and pass output parameter
math_operations.add_numbers(5, 7, sum_result);
-- Display the result of the add_numbers procedure
DBMS_OUTPUT.PUT_LINE('Sum Result: ' || sum_result);
-- Call the function and retrieve the result
product_result := math_operations.multiply_numbers(3, 4);
-- Display the result of the multiply_numbers function
DBMS_OUTPUT.PUT_LINE('Product Result: ' || product_result);
END;
/
Output:
Explanation
The PL/SQL code defines a package named math_operations with a procedure (add_numbers) and a function (multiply_numbers). The package is then implemented in a package body, with the procedure adding two numbers and the function multiplying them.
Example 2
-- Enable the display of server output
SET SERVEROUTPUT ON;
-- Create a PL/SQL package specification for employee_operations
CREATE OR REPLACE PACKAGE employee_operations AS
-- Procedure to calculate annual salary
PROCEDURE calculate_annual_salary(monthly_salary NUMBER, annual_salary OUT NUMBER);
-- Function to get the full name of an employee
FUNCTION get_full_name(first_name VARCHAR2, last_name VARCHAR2) RETURN VARCHAR2;
END employee_operations;
/
-- Create the body of the employee_operations package
CREATE OR REPLACE PACKAGE BODY employee_operations AS
-- Implementation of the calculate_annual_salary procedure
PROCEDURE calculate_annual_salary(monthly_salary NUMBER, annual_salary OUT NUMBER) IS
BEGIN
-- Assuming 12 months in a year for simplicity
annual_salary := monthly_salary * 12;
END calculate_annual_salary;
-- Implementation of the get_full_name function
FUNCTION get_full_name(first_name VARCHAR2, last_name VARCHAR2) RETURN VARCHAR2 IS
BEGIN
-- Concatenate the first and last names
RETURN first_name || ' ' || last_name;
END get_full_name;
END employee_operations;
/
-- PL/SQL block to test the employee_operations package
DECLARE
-- Declare variables
annual_salary_result NUMBER;
full_name_result VARCHAR2(100);
BEGIN
-- Call the procedure to calculate annual salary
employee_operations.calculate_annual_salary(5000, annual_salary_result);
-- Display the result of the calculate_annual_salary procedure
DBMS_OUTPUT.PUT_LINE('Annual Salary: ' || annual_salary_result);
-- Call the function to get the full name
full_name_result := employee_operations.get_full_name('Chetan', 'Singh');
-- Display the result of the get_full_name function
DBMS_OUTPUT.PUT_LINE('Full Name: ' || full_name_result);
END;
/
Ouput:
Explanation
The above PL/SQL code create a package named employee_operations with a procedure (calculate_annual_salary) and function (get_full_name). The created package calculate the annual salary based on monthly salary and concatnates employee names. A PL/SQL block then tests the package by calling these routines and finally displays the result.
Conclusion
In this article, we see how PL/SQL package is useful in maintaining the modularity in the codebases. PL/SQL packages help in grouping up the related objects such as variables, constants, cursor, exceptions, functions etc. Packages improve the modularity, security and reusability of the code and it improves the overall functionality of the database application.
Contact Us