PL/SQL Packages

PL/SQL is a programming language that extends SQL by incorporating features of procedural programming languages. It is a highly structured language. A key feature of PL/SQL is the use of packages, which allow developers to group related procedures, functions, variables, and other PL/SQL constructs into a single, organized unit within Oracle databases.

What is a PL/SQL Package?

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.

Key Benefits of Using PL/SQL Packages

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.

Structure of a PL/SQL Package

A PL/SQL package consists of two parts:

1. A package Specification
2. A package Body

1. 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;

2. 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.

Using Oracle PL/SQL Packages in Code

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;

Here, my_procedure is called with the value 42, and the result of calculate_sum(10, 20) is displayed.

PL/SQL Packages Examples

Example 1: Creating a Basic Arithmetic Package in PL/SQL

Let’s look at an example where we define a package to perform basic arithmetic operations.

-- 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: Implementing Employee Calculations with a PL/SQL Package

In this example, we create a package to calculate employee-related details.

-- 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.

Important Points About PL/SQL Packages

Here are 4 important points for working with PL/SQL packages:

1. Encapsulation: Packages group related procedures, functions, and variables together, making code easier to manage and maintain.
2. Reusability: Code in packages can be reused across multiple applications, reducing duplication and simplifying future maintenance.
3. Performance: Packages are loaded into memory once per session, improving execution speed for repeated use during the session.
4. Overloading: Packages allow overloading, meaning multiple procedures or functions with the same name can exist, as long as they have different parameters