Stored Procedures, Functions, Parameterization & PL/SQL Reusability

TAKEAWAYS:

• Stored procedures and functions are PL/SQL blocks that encapsulate reusable SQL logic, but functions return a value while procedures do not.
• Parameterization allows dynamic input/output via IN, OUT, and IN OUT modes, enabling flexible data handling without hardcoding.
• Functions are called like expressions (e.g., SELECT calc_tax(amount)), while procedures require explicit execution (e.g., EXECUTE calc_discount).
• Parameterized procedures/functions reduce security risks (SQL injection) and improve maintainability by centralizing logic.
• Triggers and packages often rely on procedures/functions for event-driven or modular database operations.
• Real-world use: eSewa’s transaction validation logic (procedure) and Daraz’s inventory restock alerts (function) leverage these concepts.

1. Stored Procedures: Definition and Purpose

A stored procedure is a precompiled collection of SQL and PL/SQL statements stored in the database and executed as a single unit. It encapsulates business logic, reduces network traffic, and improves security by centralizing logic in the database layer.

Key Characteristics

stateDiagram-v2
    [*] --> Stored
    Stored --> Compiled: Precompiled at creation
    Compiled --> Reusable: Executed on demand
    Reusable --> Parameterized: Accepts dynamic inputs
    Parameterized --> Atomic: Executes as a single transaction

Why Use Stored Procedures?

2. Parameterization: Modes and Usage

Stored procedures and functions can accept parameters to interact dynamically with data. Parameter modes define how data flows:

Parameter Modes Explained

Worked Example: Parameterized Procedure for Loan Interest Calculation

CREATE OR REPLACE PROCEDURE calculate_loan_interest(
    principal IN NUMBER,
    rate IN NUMBER,
    years IN NUMBER,
    interest OUT NUMBER
) AS
BEGIN
    interest := (principal * rate * years) / 100;
    DBMS_OUTPUT.PUT_LINE('Interest: ' || interest);
END;

Execution:

DECLARE
    total_interest NUMBER;
BEGIN
    calculate_loan_interest(10000, 5, 3, total_interest);
    DBMS_OUTPUT.PUT_LINE('Total Interest: ' || total_interest); -- Output: 1500
END;

Real-World Tie: Nepal’s NEPSE uses parameterized procedures to calculate daily stock index adjustments dynamically, avoiding hardcoded values that could lead to errors.

3. Stored Functions vs. Stored Procedures

Worked Example: Function for Discount Calculation

CREATE OR REPLACE FUNCTION apply_discount(
    original_price IN NUMBER,
    discount_rate IN NUMBER DEFAULT 0.1
) RETURN NUMBER AS
BEGIN
    RETURN original_price * (1 - discount_rate);
END;

Usage in SQL:

SELECT product_name, apply_discount(price, 0.15) AS discounted_price
FROM products;

4. Comparison Table: Procedures vs. Functions

5. Parameterized Stored Procedures: Security and Flexibility

Parameterized procedures reduce SQL injection risks by separating SQL logic from data. For example:

CREATE OR REPLACE PROCEDURE search_customer(
    name IN VARCHAR2,
    age IN NUMBER
) AS
BEGIN
    FOR customer_rec IN (SELECT * FROM customers WHERE name LIKE '%' || name || '%' AND age >= age) LOOP
        DBMS_OUTPUT.PUT_LINE(customer_rec.customer_id || ': ' || customer_rec.name);
    END LOOP;
END;

Call:

EXECUTE search_customer('John', 25);

Security Note: Hardcoding values (e.g., WHERE name = 'John') is vulnerable. Parameterization ensures inputs are sanitized.

6. Real-World Applications

In the Real World

1. eSewa Transaction Validation

   • Idea: Parameterized procedures validate user credentials and transaction amounts before processing payments.
   • How: A stored procedure like validate_payment(uid IN VARCHAR2, amount IN NUMBER) checks if the user has sufficient balance and the amount is within limits.
   • Example Call:

     EXECUTE validate_payment('user123', 500);
     

2. Daraz Order Processing

   • Idea: Functions calculate shipping costs dynamically based on order weight and distance.
   • How: A function like calculate_shipping(weight IN NUMBER, distance IN NUMBER) RETURN NUMBER uses predefined rates to compute costs.
   • Example Call:

     SELECT order_id, calculate_shipping(2.5, 50) AS shipping_cost
     FROM orders;
     

3. Pathao Ride Dispatch

   • Idea: Procedures assign drivers to rides based on real-time availability and location.
   • How: A procedure like assign_driver(ride_id IN NUMBER, driver_id OUT NUMBER) queries the database for the nearest available driver.
   • Example Call:

     DECLARE driver_id NUMBER;
     BEGIN
         assign_driver(1001, driver_id);
         DBMS_OUTPUT.PUT_LINE('Assigned Driver: ' || driver_id);
     END;
     

7. Worked Example: Parameterized Procedure for Bank Loan Approval

Scenario: A bank uses a parameterized procedure to approve loans based on credit score and loan amount.

CREATE OR REPLACE PROCEDURE approve_loan(
    customer_id IN NUMBER,
    loan_amount IN NUMBER,
    credit_score IN NUMBER,
    approval_status OUT VARCHAR2
) AS
BEGIN
    IF credit_score >= 700 THEN
        IF loan_amount <= (SELECT balance FROM accounts WHERE account_id = customer_id) THEN
            approval_status := 'APPROVED';
            INSERT INTO loans VALUES (customer_id, loan_amount, SYSDATE, 'APPROVED');
        ELSE
            approval_status := 'REJECTED (Insufficient Balance)';
        END IF;
    ELSE
        approval_status := 'REJECTED (Low Credit Score)';
    END IF;
END;

Execution:

DECLARE
    status VARCHAR2(50);
BEGIN
    approve_loan(101, 50000, 750, status);
    DBMS_OUTPUT.PUT_LINE('Loan Status: ' || status); -- Output: APPROVED
END;

8. Advantages and Disadvantages

Exam Tip

1. Definitions:

   • Always define stored procedures/functions clearly, emphasizing reusability and encapsulation.
   • Differentiate between them using the table above (return value, call syntax, use cases).

2. Parameterization:

   • Explain IN, OUT, and IN OUT modes with real examples (e.g., loan interest calculation).
   • Highlight how parameterization prevents SQL injection.

3. Worked Examples:

   • Include complete code snippets (creation + execution) for procedures/functions.
   • Use business scenarios (e.g., bank loans, e-commerce discounts) to show practicality.

4. Comparison Tables:

   • Use a clear table to differentiate procedures and functions (as shown above).

5. Real-World Tie:

   • Link examples to Nepali companies (eSewa, Daraz) or global apps (WhatsApp’s message encryption logic could use procedures for validation).
   • For parameterization, mention how Ncell uses it to validate mobile top-up amounts.

6. Error Handling (Bonus Points):

   • If time permits, briefly mention exception handling (e.g., WHEN OTHERS THEN RAISE_APPLICATION_ERROR).