Packages in PLSQL

Package: A schema object in Oracle PL/SQL that groups related procedures, functions, variables, and other elements to promote modularity, reusability, and better performance.

Package Specification: The public interface of the package, declaring the procedures, functions, variables, and other elements that are accessible to external programs. It contains no implementation, only declarations.

Package Body: Contains the actual implementation of the elements declared in the package specification. It can also include private procedures and variables that are not exposed to the outside. The package body is optional if all procedures and functions are fully defined in the specification.

Here’s a complete example that includes both the package creation (specification and body) and a combined call to the procedure and function, based on the inputs and explanations provided earlier.

1. Create the Package Specification:

The package specification declares the public procedures and functions that will be accessible.

CREATE OR REPLACE PACKAGE emp_pkg AS
   -- Declare public procedure and function
   PROCEDURE add_employee (p_name VARCHAR2, p_salary NUMBER);
   FUNCTION get_employee_count RETURN NUMBER;
END emp_pkg;
/

2. Create the Package Body:

The package body implements the logic of the procedures and functions declared in the specification.

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
   -- Implementation of the add_employee procedure
   PROCEDURE add_employee (p_name VARCHAR2, p_salary NUMBER) IS
   BEGIN
      INSERT INTO employees (name, salary) VALUES (p_name, p_salary);
   END add_employee;

   -- Implementation of the get_employee_count function
   FUNCTION get_employee_count RETURN NUMBER IS
      v_count NUMBER;
   BEGIN
      SELECT COUNT(*) INTO v_count FROM employees;
      RETURN v_count;
   END get_employee_count;
END emp_pkg;
/

3. Combined Procedure and Function Call:

Now, let’s combine both the procedure call (to add a new employee) and the function call (to get the employee count) in a single PL/SQL block:

DECLARE
   v_count NUMBER;
BEGIN
   -- Call the procedure to add a new employee
   emp_pkg.add_employee('Jane Smith', 60000);

   -- Call the function to get the total number of employees
   v_count := emp_pkg.get_employee_count;

   -- Display the total count of employees
   DBMS_OUTPUT.PUT_LINE('Total Employees after insertion: ' || v_count);
END;
/

4. Expected Execution Flow:

1. Procedure Call: The add_employee procedure inserts a new employee (Jane Smith) into the employees table with a salary of 60,000.

2. Function Call: The get_employee_count function retrieves the total number of employees from the employees table.

3. DBMS_OUTPUT: The result (total employee count) is printed to the console.

5. Expected Output Example:

If the employees table had 5 employees before adding Jane Smith, the output would be:

Total Employees after insertion: 6

Key Features Utilized:

Encapsulation: The internal logic is hidden within the package body, while only the procedure and function are exposed via the package specification.

Modularity: Procedures and functions are bundled together logically in one package.

Reusability: The package can be reused across various applications or blocks without rewriting the logic.