PL/SQL Control Structures: Conditional & Repetitive Logic

1. Introduction: Why Control Structures Matter in PL/SQL

PL/SQL programs must make decisions (e.g., "if balance < 0, reject transaction") and repeat actions (e.g., "process all pending orders"). These constructs let you:

• Validate data before database updates (like eSewa checking account limits).
• Automate repetitive tasks (e.g., Daraz’s daily stock reordering).
• Handle errors gracefully (e.g., NTC’s call routing if a line is busy).

2. Conditional Constructs: Making Decisions

PL/SQL provides two primary ways to implement logic branches.

A. IF-THEN-ELSIF-ELSE Construct

Syntax:

flowchart TD
    A["IF condition"] -->|"True"| B["THEN block"]
    A -->|"False"| C["ELSIF condition"]
    C -->|"True"| D["ELSIF block"]
    C -->|"False"| E["ELSE block"]
    B --> F["END IF"]
    D --> F
    E --> F

Key Rules:

• Nested IFs are allowed (but avoid deep nesting; use CASE for complex logic).
• ELSIF checks conditions sequentially (like else if in Python).
• ELSE is optional.

Worked Example: eSewa Transaction Validation

DECLARE
    balance NUMBER := 5000;
    amount NUMBER := 6000;
BEGIN
    IF amount > balance THEN
        DBMS_OUTPUT.PUT_LINE('Insufficient funds. Current balance: ' || balance);
    ELSIF amount <= 0 THEN
        DBMS_OUTPUT.PUT_LINE('Invalid amount.');
    ELSE
        -- Deduct amount from balance (simplified)
        balance := balance - amount;
        DBMS_OUTPUT.PUT_LINE('Transaction successful. New balance: ' || balance);
    END IF;
END;

Output:

Insufficient funds. Current balance: 5000

Advantages:

• Readable for simple conditions.
• Supports early termination (e.g., IF condition THEN RETURN;).

Disadvantages:

• Hard to maintain for many conditions (use CASE instead).

B. CASE Statement

Syntax:

Types:

1. Simple CASE: Compares a single expression to values.

   CASE department_id
       WHEN 10 THEN 'HR'
       WHEN 20 THEN 'Finance'
       ELSE 'Unknown'
   END
   

2. Searched CASE: Evaluates boolean conditions.

   CASE
       WHEN salary > 10000 THEN 'High'
       WHEN salary > 5000 THEN 'Medium'
       ELSE 'Low'
   END
   

Worked Example: Daraz Order Discount

DECLARE
    order_total NUMBER := 1500;
    discount NUMBER;
BEGIN
    discount :=
        CASE
            WHEN order_total > 2000 THEN 0.2 * order_total  -- 20% off
            WHEN order_total > 1000 THEN 0.1 * order_total  -- 10% off
            ELSE 0
        END;
    DBMS_OUTPUT.PUT_LINE('Discount: ' || discount);
END;

Output:

Discount: 300

Advantages:

• Cleaner for multiple conditions.
• Supports complex logic in SQL queries (e.g., SELECT CASE ... END AS status).

Disadvantages:

• Slightly slower than IF for simple checks.

3. Repetitive Constructs: Loops and Iteration

PL/SQL offers three loop types, each for different scenarios.

A. LOOP (Unconditional)

Syntax:

flowchart TD
    A["LOOP"] --> B["Statements"]
    B --> C["EXIT WHEN condition"]
    C -->|"False"| A
    C -->|"True"| D["END LOOP"]

Worked Example: Pathao Driver Route Check

DECLARE
    trip_count NUMBER := 0;
BEGIN
    LOOP
        trip_count := trip_count + 1;
        DBMS_OUTPUT.PUT_LINE('Trip ' || trip_count || ' completed.');
        EXIT WHEN trip_count >= 5;  -- Stop after 5 trips
    END LOOP;
END;

Output:

Trip 1 completed.
Trip 2 completed.
...
Trip 5 completed.

Use Case: When you don’t know the exit condition in advance (e.g., polling a sensor until a value changes).

B. WHILE Loop (Conditional)

Syntax:

flowchart TD
    A["WHILE condition"] -->|"True"| B["Statements"]
    B --> A
    A -->|"False"| C["END LOOP"]

Worked Example: NTC Call Queue

DECLARE
    calls_remaining NUMBER := 10;
BEGIN
    WHILE calls_remaining > 0 LOOP
        DBMS_OUTPUT.PUT_LINE('Processing call ' || (10 - calls_remaining + 1));
        calls_remaining := calls_remaining - 1;
    END LOOP;
END;

Output:

Processing call 10
Processing call 9
...
Processing call 1

Advantages:

• Efficient for known termination (e.g., processing records until a counter hits zero).

Disadvantages:

• Risk of infinite loops if condition never becomes false.

C. FOR Loop (Counter-Controlled)

Syntax:

Variants:

1. Range loop: FOR i IN 1..5 LOOP.
2. Reverse loop: FOR i IN REVERSE 5..1 LOOP.
3. Record loop: FOR emp_rec IN employees%ROWTYPE LOOP.

Worked Example: NEPSE Stock Price Update

DECLARE
    stock_prices employees%ROWTYPE;
    CURSOR price_cursor IS SELECT * FROM stock_prices;
BEGIN
    FOR stock_rec IN price_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('Stock: ' || stock_rec.name || ', Price: ' || stock_rec.price);
    END LOOP;
END;

Output:

Stock: ABC, Price: 120.50
Stock: XYZ, Price: 85.25

Advantages:

• Most readable for fixed iterations.
• Automatically handles loop variable management.

Disadvantages:

• Cannot exit early (use EXIT WHEN in WHILE instead).

4. Loop Control Statements

Worked Example: Skipping Invalid Orders (Daraz)

DECLARE
    order_id NUMBER := 1;
BEGIN
    WHILE order_id <= 10 LOOP
        IF order_id = 7 THEN  -- Skip order #7 (invalid)
            DBMS_OUTPUT.PUT_LINE('Skipping order ' || order_id);
            order_id := order_id + 1;
            CONTINUE;
        END IF;
        DBMS_OUTPUT.PUT_LINE('Processing order ' || order_id);
        order_id := order_id + 1;
    END LOOP;
END;

Output:

Processing order 1
Skipping order 7
Processing order 8
...

5. Real-World Applications of Control Structures

## In the real world

1. eSewa Transaction Logic

   • Idea: Uses IF-ELSE to validate transactions (e.g., check balance, transaction limits, and fraud patterns).
   • Example: When you pay ₹500 via eSewa, the backend runs:

     IF (account_balance >= amount) AND (amount <= daily_limit) THEN
         deduct_amount(amount);
         update_transaction_log;
     ELSE
         send_rejection_notification;
     END IF;
     

2. Daraz Inventory Replenishment

   • Idea: Employs WHILE loops to check stock levels and FOR loops to update supplier orders.
   • Example: Daily script:

     WHILE stock_level < reorder_threshold LOOP
         FOR product IN products_to_reorder LOOP
             place_order(product_id, quantity);
         END LOOP;
     END LOOP;
     

3. NTC Call Routing

   • Idea: Uses CASE to route calls based on subscriber type (prepaid/postpaid) and LOOP to handle queue overflow.
   • Example:

     CASE subscriber_type
         WHEN 'prepaid' THEN route_to_prepaid_queue;
         WHEN 'postpaid' THEN route_to_postpaid_queue;
         ELSE route_to_default_queue;
     END CASE;
     

6. Comparison Table: Conditional vs. Repetitive Constructs

7. Common Pitfalls and Best Practices

• Avoid Deep Nesting: Replace nested IFs with CASE or refactor logic.
• Initialize Counters: Always set loop variables before use (e.g., trip_count := 0).
• Use EXIT WHEN: Prefer EXIT WHEN over IF ... THEN EXIT; for clarity.
• Test Edge Cases: Ensure loops handle empty datasets (e.g., FOR emp IN employees%ROWTYPE LOOP fails if employees is empty).

Bad Example (Infinite Loop Risk):

WHILE TRUE LOOP  -- Avoid!
    -- ...
END LOOP;

Good Example:

WHILE user_input <> 'quit' LOOP
    -- ...
END LOOP;

8. Exam Tip

• Focus on Syntax: Memorize the exact structure of IF, CASE, LOOP, WHILE, and FOR (e.g., WHILE condition LOOP vs. FOR i IN range LOOP).
• Show Execution Flow: For questions like "Explain how this loop processes employee records", draw a flowchart or trace the steps:

  1. Open cursor for employees.
  2. FOR emp IN cursor LOOP
     3. Process emp.salary.
     4. EXIT WHEN emp.salary > 10000.
  

• Link to Real Scenarios: Always tie examples to databases you know (e.g., "How would you use a WHILE loop to validate bank transactions?").
• Avoid Redundancy: If asked to "explain both IF and CASE", compare them in a table (as above) rather than repeating syntax.
• Practice Coding: Write a 5-line PL/SQL block using a loop and conditional to solve a problem like: "Display all employees earning >₹50,000, but skip those in department 10."