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;

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.