Definition of a Package
• Group of precompiled SQL statements &
metadata
• RDBMS packages manage execution plans &
performance
• Oracle: PL/SQL packages = modules of
procedures & functions
Why Packages are Needed
• Improve performance with precompiled
execution plans
• Provide stability and reuse across sessions
• Enable optimizer to reuse strategies
• Support application portability
Architecture View
• Application → SQL → Optimizer → Package →
Database Objects
• Package = bridge between SQL optimizer and
execution engine
Types of Packages in RDBMS
• Static SQL Packages – precompiled, fixed
structure
• Dynamic SQL Packages – compiled at runtime
• Procedural Packages – stored modules
How Packages Work (Step by Step)
• 1. Application issues SQL
• 2. Optimizer creates or reuses plan
• 3. Stored as package in catalog
• 4. Execution engine executes using package
Example in MySQL (Stored
Procedure)
• MySQL Stored Procedure Example:
• DELIMITER //
• CREATE PROCEDURE GetEmployeeSalary(IN
emp_id INT)
• BEGIN
• SELECT salary FROM employees WHERE id =
emp_id;
• END //
Example in MySQL (Stored
Function)
• MySQL Stored Function Example:
• DELIMITER //
• CREATE FUNCTION AnnualSalary(monthly
DECIMAL(10,2))
• RETURNS DECIMAL(10,2)
• DETERMINISTIC
• BEGIN
• RETURN monthly * 12;
Advantages & Disadvantages of
Packages
• Advantages: • Disadvantages:
• - Encapsulation of logic • - Static packages need
• - Improved rebinding
performance via • - DB changes may
precompilation invalidate packages
• - Reusability across • - Vendor-specific
applications implementations
• - Better security
• - Portability across
environments
Real-Life Scenarios
• Banking System: package for transaction
management
• HR System: package for employee operations
• Provides stability, security, and consistency
Comparison: Static vs Dynamic vs
Procedural
3.5
• Static SQL Packages: 3
2.5
• - Precompiled
2
• - Fast, require rebinding1.5
1
• Dynamic SQL Packages:0.5
0
• - Runtime compilation Static SQL Dynamic SQL Procedural
• - Flexible, slower
• Procedural Packages:
• - Encapsulation
Summary + Q&A
• Packages improve performance, reusability,
and security
• Different types: Static, Dynamic, Procedural
• Examples: MySQL Stored Procedures &
Functions
• Advantages & Disadvantages considered
• Q&A
