Packages in PL/SQL


In PL/SQL (Procedural Language/Structured Query Language), a package is a schema object that groups logically related PL/SQL types, variables, and subprograms (procedures and functions). Packages have two parts: the specification (spec) and the body. The specification is the interface to the package; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body defines these elements and implements the subprograms.

Benefits of Using Packages:
  • Modularity: Packages help organize related PL/SQL types, items, and subprograms.
  • Easier Application Design: You can design an application as a set of related packages.
  • Information Hiding: You can specify which types, items, and subprograms are public or private.
  • Improved Performance: The entire package is loaded into memory when a package subprogram is called for the first time, thus reducing the disk I/O for subsequent calls.
Structure of a Package:
  • Package Specification: Declares the public items (visible to users).
  • Package Body: Implements the public items and declares private items (visible only within the package).
Example:

Let's create a simple package for managing employees.

Package Specification:
CREATE OR REPLACE PACKAGE emp_pkg IS
  -- Public type declaration
  TYPE emp_rec IS RECORD (
    emp_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER
  );

  -- Public procedure declaration
  PROCEDURE add_employee(p_emp IN emp_rec);

  -- Public function declaration
  FUNCTION get_employee(p_emp_id IN NUMBER) RETURN emp_rec;
END emp_pkg;
/

In the package specification emp_pkg, we declare a record type emp_rec to hold employee information, a procedure add_employee to add a new employee, and a function get_employee to retrieve employee details.

Package Body:
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  -- Declare a variable to simulate a database table
  employees_tbl SYS_REFCURSOR;

  -- Implement the add_employee procedure
  PROCEDURE add_employee(p_emp IN emp_rec) IS
  BEGIN
    -- Simulate inserting into a database table
    OPEN employees_tbl FOR
      SELECT p_emp.emp_id, p_emp.first_name, p_emp.last_name, p_emp.salary
      FROM dual;
    DBMS_OUTPUT.PUT_LINE('Employee added: ' || p_emp.first_name || ' ' || p_emp.last_name);
  END add_employee;

  -- Implement the get_employee function
  FUNCTION get_employee(p_emp_id IN NUMBER) RETURN emp_rec IS
    v_emp emp_rec;
  BEGIN
    -- Simulate retrieving from a database table
    FETCH employees_tbl INTO v_emp.emp_id, v_emp.first_name, v_emp.last_name, v_emp.salary;
    RETURN v_emp;
  END get_employee;
END emp_pkg;
/


In the package body, we implement the add_employee procedure and the get_employee function. We use a SYS_REFCURSOR to simulate a database table for simplicity.

Using the Package:

To use the package, you can call its procedures and functions in your PL/SQL code.
DECLARE
  new_emp emp_pkg.emp_rec;
  fetched_emp emp_pkg.emp_rec;
BEGIN
  -- Initialize a new employee record
  new_emp.emp_id := 1;
  new_emp.first_name := 'John';
  new_emp.last_name := 'Doe';
  new_emp.salary := 50000;

  -- Add the new employee
  emp_pkg.add_employee(new_emp);

  -- Retrieve the employee details
  fetched_emp := emp_pkg.get_employee(1);

  DBMS_OUTPUT.PUT_LINE('Fetched Employee: ' || fetched_emp.first_name || ' ' || fetched_emp.last_name);
END;
/


This example demonstrates how to declare a new employee record, add it using the add_employee procedure, and fetch the employee details using the get_employee function.

Packages in PL/SQL provide a powerful way to encapsulate and modularize your PL/SQL code, making it easier to maintain and understand.

Example:
CREATE OR REPLACE PACKAGE simple_pkg IS
  -- Public procedure declaration
  PROCEDURE greet_user(p_name IN VARCHAR2);

  -- Public function declaration
  FUNCTION square_number(p_number IN NUMBER) RETURN NUMBER;
END simple_pkg;
/
************************************
CREATE OR REPLACE PACKAGE BODY simple_pkg IS
  -- Implement the greet_user procedure
  PROCEDURE greet_user(p_name IN VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
  END greet_user;

  -- Implement the square_number function
  FUNCTION square_number(p_number IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_number * p_number;
  END square_number;
END simple_pkg;
/
***************************
BEGIN
  -- Call the greet_user procedure
  simple_pkg.greet_user('Alice');

  -- Call the square_number function
  DECLARE
    v_number NUMBER := 5;
    v_square NUMBER;
  BEGIN
    v_square := simple_pkg.square_number(v_number);
    DBMS_OUTPUT.PUT_LINE('The square of ' || v_number || ' is ' || v_square);
  END;
END;
/

Output:
Hello, Alice! 
The square of 5 is 25

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more