Skip to content

PL/SQL Packages (The Code Library) 📚

Prerequisites: Stored Procedures, Functions

Mentor's Note: Imagine a library with 10,000 books scattered on the floor. You'd never find anything! A Package is the bookshelf. It groups related Procedures, Functions, and Variables into one neat container. 💡

🌟 The Scenario: The Office Toolkit 🛠️

Imagine a toolbox labeled "HR_TOOLKIT". - Inside, you have a hammer (add_employee), a screwdriver (update_salary), and a measuring tape (get_avg_pay). - You don't just carry loose tools; you carry the Whole Box.

💻 1. The Package Specification (The API) 📝

This is the "Header". It lists what is in the box but not how it works. This is visible to other developers.

CREATE OR REPLACE PACKAGE hr_pkg AS
   -- Global Variable
   g_min_wage CONSTANT NUMBER := 15000;

   -- Procedure Declaration
   PROCEDURE hire_emp(p_name VARCHAR2);

   -- Function Declaration
   FUNCTION check_salary(p_id NUMBER) RETURN BOOLEAN;
END hr_pkg;
/

💻 2. The Package Body (The Engine Room) ⚙️

This is where the actual code lives. It is hidden from other users.

CREATE OR REPLACE PACKAGE BODY hr_pkg AS
   -- Implementation of hire_emp
   PROCEDURE hire_emp(p_name VARCHAR2) IS
   BEGIN
      INSERT INTO employees (name) VALUES (p_name);
   END hire_emp;

   -- Implementation of check_salary
   FUNCTION check_salary(p_id NUMBER) RETURN BOOLEAN IS
   BEGIN
      -- Complex logic here...
      RETURN TRUE;
   END check_salary;
END hr_pkg;
/

🛡️ 3. Why use Packages? (Architect's Note)

  1. Encapsulation: You can have "Private" procedures inside the Body that nobody else can call. This protects your internal logic. 🔒
  2. Performance (Pre-loading): When you call one function in a package, Oracle loads the Entire Package into memory. Next time you call a different function from the same package, it's already in RAM! ⚡
  3. Overloading: You can have two procedures with the same name, as long as they take different types of parameters (e.g., find_emp(id NUMBER) and find_emp(name VARCHAR2)).

🗑️ 4. Dropping Packages

DROP PACKAGE hr_pkg; -- Deletes both Spec and Body
DROP PACKAGE BODY hr_pkg; -- Deletes only the implementation

📈 Learning Path