PLSQL Packages

        package is a group of related PL/SQL types (such as procedures, functions, variables, and cursors) that are stored together as a single entity in the database. Packages help organize code and provide modularity, making it easier to manage and maintain.

Features:

1. Encapsulation: Packages encapsulate related procedures and functions, allowing for better organization.
2. Modularity: You can separate the specification (public interface) from the body (implementation), which helps in hiding the implementation details.
3. Reusability: Procedures and functions within a package can be reused across different applications.
4. Performance: Packages can improve performance since all the components are loaded into memory when the package is first accessed.
5. State Maintenance: Packages can hold persistent variables, maintaining their state across multiple calls.

Structure of a Package

A package has two  components.

• Package Specification: Declares the public procedures, functions, and variables.
• Package Body: Contains the implementation of the procedures and functions declared in the specification.

Example:

Package Specification:

CREATE OR REPLACE PACKAGE employee_pkg AS
PROCEDURE add_employee(emp_id NUMBER, emp_name VARCHAR2);    -- Declare public procedure
FUNCTION get_employee(emp_id NUMBER) RETURN VARCHAR2;    -- Declare public function
END employee_pkg;

Package Body:

CREATE OR REPLACE PACKAGE BODY employee_pkg AS
-- Define private variable
TYPE emp_rec IS RECORD ( id NUMBER, name VARCHAR2(100) );
emp_table emp_rec;

PROCEDURE add_employee(emp_id NUMBER, emp_name VARCHAR2) IS
BEGIN
emp_table.id := emp_id;
emp_table.name := emp_name;
DBMS_OUTPUT.PUT_LINE('Employee added: ' || emp_table.name);
END add_employee;

FUNCTION get_employee(emp_id NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN 'Employee ID: ' || emp_table.id || ', Name: ' || emp_table.name;
END get_employee;
END employee_pkg;

Run:

BEGIN
employee_pkg.add_employee(1, 'John Doe');    -- Call the procedure to add an employee
DBMS_OUTPUT.PUT_LINE(employee_pkg.get_employee(1));    -- Call the function to get employee details
END;

List out the package:

SELECT object_name FROM user_objects WHERE object_type = 'PACKAGE';
SELECT owner, object_name FROM all_objects WHERE object_type = 'PACKAGE';
SELECT owner, object_name FROM dba_objects WHERE object_type = 'PACKAGE';

List out package specification and body:

SELECT object_name FROM user_objects WHERE object_type = 'PACKAGE';
SELECT object_name FROM user_objects WHERE object_type = 'PACKAGE BODY';

View the package content:

SELECT DBMS_METADATA.GET_DDL('PACKAGE', 'PACKAGE_NAME', 'OWNER_NAME') FROM dual;
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY', 'PACKAGE_NAME', 'OWNER_NAME') FROM dual;

Drop an entire package, including both its specification and body:

DROP PACKAGE package_name;

Drop Only the Package Body:

DROP PACKAGE BODY package_name;