PACKAGES in PLSQL in detail

Detailed Explanation: Creating, Managing, and Executing PL/SQL Packages with Sample Data

This explanation covers everything you need to know about creating a PL/SQL package, defining procedures and functions, creating a table, inserting sample data, and executing the package.

1. Creating a PL/SQL Package

A PL/SQL package is a collection of procedures, functions, variables, and other PL/SQL constructs grouped logically. Packages have two main parts:

Package Specification: Declares public procedures, functions, and variables.

Package Body: Contains the actual implementation of the declared procedures and functions.

In this case, we'll create a package to manage employee data in the employees table. The package will have:

1. A procedure to insert new employees (hire_employee).

2. A function to retrieve employee names by their ID (get_employee_name).

Package Specification

CREATE OR REPLACE PACKAGE employee_pkg AS
-- Public variable to store the number of employees
emp_count NUMBER;

-- Public procedure to hire a new employee
PROCEDURE hire_employee(emp_id NUMBER, emp_name VARCHAR2, dept_id NUMBER);

-- Public function to get the name of an employee
FUNCTION get_employee_name(emp_id NUMBER) RETURN VARCHAR2;
END employee_pkg;
/

emp_count is a public variable that will store the number of employees.

hire_employee is a procedure that takes three arguments: emp_id, emp_name, and dept_id, which are used to insert a new employee into the employees table.

get_employee_name is a function that takes an employee's emp_id as input and returns their name.

Package Body

CREATE OR REPLACE PACKAGE BODY employee_pkg AS
-- Initialize employee count to 0
emp_count NUMBER := 0;

-- Procedure to hire an employee
PROCEDURE hire_employee(emp_id NUMBER, emp_name VARCHAR2, dept_id NUMBER) IS
BEGIN
-- Insert employee details into the employees table
INSERT INTO employees (id, name, department_id) VALUES (emp_id, emp_name, dept_id);
-- Increment employee count
emp_count := emp_count + 1;
END hire_employee;

-- Function to get employee name by ID
FUNCTION get_employee_name(emp_id NUMBER) RETURN VARCHAR2 IS
v_name VARCHAR2(50);
BEGIN
-- Retrieve the employee's name from the table
SELECT name INTO v_name FROM employees WHERE id = emp_id;
RETURN v_name;
END get_employee_name;
END employee_pkg;
/

The employee_pkg package body implements:

hire_employee Procedure: Inserts a new employee into the employees table and increments the emp_count.

get_employee_name Function: Returns the name of an employee based on their emp_id.

---

1. Creating the employees Table

Now, let's create the employees table where employee information will be stored.

CREATE TABLE employees (
id NUMBER PRIMARY KEY, -- Employee ID
name VARCHAR2(50), -- Employee Name
department_id NUMBER -- Department ID
);

id: The unique identifier (primary key) for each employee.

name: The name of the employee.

department_id: The department to which the employee belongs.

---

1. Inserting Sample Data into the employees Table

Before we start using the package, let’s insert some initial data into the table.

INSERT INTO employees (id, name, department_id) VALUES (1, 'Alice Johnson', 10);
INSERT INTO employees (id, name, department_id) VALUES (2, 'Bob Smith', 20);
INSERT INTO employees (id, name, department_id) VALUES (3, 'Charlie Brown', 10);
INSERT INTO employees (id, name, department_id) VALUES (4, 'David White', 30);
INSERT INTO employees (id, name, department_id) VALUES (5, 'Eva Green', 20);

-- Commit the transaction to save the changes
COMMIT;

Now we have a few rows of employee data in the employees table.

---

1. Executing the Package's Procedures and Functions

4.1 Executing the hire_employee Procedure

We can now use the hire_employee procedure to add a new employee to the table. Here's an example of how to do this:

BEGIN
-- Hire a new employee using the hire_employee procedure
employee_pkg.hire_employee(6, 'Frank Miller', 40);
END;
/

In this example, we are adding an employee with:

emp_id = 6

emp_name = 'Frank Miller'

department_id = 40

The procedure will insert this data into the employees table and increment the emp_count variable.

4.2 Executing the get_employee_name Function

Next, let's retrieve the name of an employee using their emp_id by calling the get_employee_name function.

DECLARE
v_name VARCHAR2(50);
BEGIN
-- Call the function to get the employee name
v_name := employee_pkg.get_employee_name(6);

-- Output the employee's name
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
/

In this example, we pass the emp_id = 6 to the function, which returns Frank Miller.

---

1. Verifying the Data

You can verify that the new employee was added by executing a SELECT query:

SELECT * FROM employees;

This should display:

This confirms that the new employee has been added to the employees table.

---

Complete Workflow Recap

1. Create the PL/SQL Package:

Define public procedures and functions in the package specification.

Implement the logic for the procedures and functions in the package body.

1. Create the employees Table:

Set up the table structure to hold employee data.

1. Insert Initial Sample Data:

Insert data directly into the table before using the package to manage the data.

1. Use the Package's Procedures and Functions:

Call the hire_employee procedure to insert new employees.

Call the get_employee_name function to retrieve employee names.

1. Verify Data:

Use SQL queries to confirm the data is stored correctly in the table.

This process demonstrates how to create a package, interact with the database, and manage employees using PL/SQL.