Conditional Statements: IF-THEN, IF-ELSE, multiple conditions, nested IF, CASE

📖 Theory

The Fine-Slab Dilemma

Imagine a student returns a book late to the CampusLib counter. As the database program, you cannot charge everyone a flat 20 rupees penalty. If a book is 1 to 5 days late, the fine is Rs. 2 per day. If it is 6 to 10 days late, it jumps to Rs. 5 per day. If it crosses 10 days, it spikes to Rs. 10 per day, and their library card must be instantly suspended. How do we teach our database engine to look at a variable number of overdue days and intelligently pick the correct operational penalty?

📖 Theory

The Railway Track Diverter

Think of standard SQL queries like a bullet train moving along a single straight track, it hits every block of data sequentially without making choices. PL/SQL conditional statements act like a track switcher at a busy railway junction. Based on a specific signaling rule (e.g., Is the train an Express or a local?), the track dynamically shifts, sending the train down one specific route while completely ignoring the alternative pathways.

📖 Theory

The Syntax of Decision Making

Formally, PL/SQL handles decisions using logical branching structures. Unlike C, C++, or Java, which use curly braces {} and keywords like else if, Oracle PL/SQL relies on clear, explicit keywords like THEN, ELSIF, and terminates the entire condition architecture using a definitive END IF; statement. Every conditional check evaluates an expression to either TRUE, FALSE, or NULL, routing code execution accordingly.

📊 At a glance

The core conditional architectures available in Oracle PL/SQL

⌨️ Practical

Building a Multi-Tier Library Fine Calculator

-- An anonymous block to determine per-day fine rates and account lockdowns
DECLARE
  v_overdue_days NUMBER := 12;
  v_fine_per_day NUMBER;
  v_total_fine   NUMBER;
  v_card_status  VARCHAR2(20);
BEGIN
  -- Multi-tier structural evaluation
  IF v_overdue_days <= 5 THEN
    v_fine_per_day := 2;
    v_card_status  := 'Active';
  ELSIF v_overdue_days <= 10 THEN
    v_fine_per_day := 5;
    v_card_status  := 'Active';
  ELSE
    v_fine_per_day := 10;
    v_card_status  := 'Suspended';
  END IF;
  
  -- Final mathematical calculation
  v_total_fine := v_overdue_days * v_fine_per_day;
  
  DBMS_OUTPUT.PUT_LINE('Account Status: ' || v_card_status);
  DBMS_OUTPUT.PUT_LINE('Total Fine Payable: Rs. ' || v_total_fine);
END;
/

🪜 Follow along

Behind the Scenes of a Conditional Branch

1. 1. Condition Trigger The engine assesses the top-most condition expression (v_overdue_days <= 5). Since 12 is greater than 5, this evaluates to FALSE.
2. 2. Sequential Drop The engine bypasses the first block and checks the next operational gate: ELSIF (v_overdue_days <= 10). This also evaluates to FALSE.
3. 3. The Default Catchall Having exhausted all listed conditions, the compiler drops straight into the ELSE chamber, executing the suspension and Rs. 10 assignment variables.
4. 4. Route Exit The compiler hits the END IF; marker, immediately closing the branching ecosystem and proceeding to execute the remaining standard equations down the line.

🧩 Quiz

An exam question asks you to write a multi-conditional check block. Which keyword spelling will compile successfully in Oracle PL/SQL for alternative conditional branches?

1. ELSEIF
2. ELS_IF
3. ELSIF
4. ELSE IF

⚠️ Watch out

The Silent Danger of NULL Conditions

In PL/SQL, if a condition encounters an uninitialized variable (which defaults to NULL), it does not crash. Instead, the logical comparison resolves to a neutral state that is neither TRUE nor FALSE. Oracle treats this as a failure and slides past the block. If you don't explicitly filter for NULL states using IS NULL, your records will drop directly into the default ELSE block, which can cause massive logical calculation errors!

💭 Think first

The Architectural Decision Test

If you need to check if a student belongs to the 'BCA' department AND check if their membership year is '3rd Year' to offer a graduation fee discount, should you use a single CASE statement or a Nested IF structure? Think about multi-variable testing before tapping.

📖 Theory

Lab Exam Execution Strategy

When writing PL/SQL code during terminal laboratory examinations, never forget the matching semi-colons. While IF, THEN, and ELSE are standalone clauses and do not take punctuation, the termination block strictly requires a terminating semi-colon written precisely as END IF;. Skipping this tiny character will throw off the entire Oracle compiler parser and ruin an otherwise perfect script.

✅ Summary

Key takeaways

• Conditional blocks isolate and execute specific program logic based on boolean validation rules.
• The mandatory closing marker for any decision loop structure is an explicit 'END IF;'.
• Watch out for the unique keyword spelling 'ELSIF' to prevent direct compilation failures.
• CASE statements offer a clean, elegant alternative to long ELSIF chains for single-variable matches.
• Uninitialized variables resolve to NULL, causing comparisons to fail silently and drop straight down the logic path.
• Memory hook: If it matches, hit THEN; if you need another check, spell it ELSIF; end the route with END IF!