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:

1. A package Specification.
2. 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.