Iterative statements: Loop-End Loop, For-Loop, While Loop, Exit Loop, Continue

📖 Theory

The Automated Printing Press

Imagine you are running backend terminal operations for the CampusLib database. The head librarian asks you to print a personalized warning message for 50 different students who have overdue books. Writing the same DBMS_OUTPUT.PUT_LINE statement 50 times with manually incremented numbers isn't just exhausting, it makes your code look bloated and completely amateurish. To perform repetitive database actions flawlessly without duplicating lines, we use iterative loops. In Oracle PL/SQL, we have three distinct loop strategies to handle these cycles.

📖 Theory

The Ceiling Fan and the Push-Up Count

Think of a Basic Loop like a ceiling fan running at full speed; it will keep spinning forever in an infinite loop until you explicitly hit the wall switch (EXIT). A WHILE loop is like walking outside with an open umbrella; you keep walking while the rain is falling, but the moment the downpour stops, you shut the frame and step inside. A FOR loop is like doing exactly 20 push-ups during your morning gym workout; you know the exact starting count (1) and the exact ending target (20) before you even begin the first rep.

📖 Theory

The Three Forms of Iteration

PL/SQL splits iterative logic into three flavors: the Basic Loop (infinite by default), the WHILE Loop (conditional validation at the entry gate), and the FOR Loop (counter-driven boundaries). Managing these blocks efficiently requires complete mastery over transition keywords: EXIT WHEN breaks out of a sequence early, whereas CONTINUE tells Oracle to skip the remaining lines of the current loop and jump straight to the top of the next iteration.

📊 At a glance

Structural behaviors and properties of PL/SQL loop configurations

⌨️ Practical

Tracking Fines and Skipping Holidays across Iterations

-- An anonymous block comparing a Basic Loop and a counter-driven FOR Loop
DECLARE
  v_basic_counter NUMBER := 1;
  v_total_fine    NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- 1. Processing via Basic Loop ---');
  LOOP
    v_total_fine := v_total_fine + 5;
    DBMS_OUTPUT.PUT_LINE('Day ' || v_basic_counter || ' Fine: Rs. ' || v_total_fine);
    
    v_basic_counter := v_basic_counter + 1;
    -- Mandatory exit guard to prevent system freeze
    EXIT WHEN v_basic_counter > 5;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('--- 2. Processing via Counter-Driven FOR Loop ---');
  -- Note: loop_idx does NOT need to be declared in the DECLARE section!
  FOR loop_idx IN 1..5 LOOP
    -- Using CONTINUE to skip an entry point dynamically
    IF loop_idx = 3 THEN
      DBMS_OUTPUT.PUT_LINE('Day 3 is a College Holiday! Skipping generation.');
      CONTINUE;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('Day ' || loop_idx || ' automated ledger entry processed.');
  END LOOP;
END;
/

🪜 Follow along

The Internal Sequence of a FOR Loop

1. 1. Implicit Setup The Oracle engine registers the FOR loop statement and implicitly declares the counter variable as an integer automatically behind the scenes.
2. 2. Range Initialization The compiler checks the boundaries (e.g., 1..5). If the lower value is lower than or equal to the upper target, execution drops inside.
3. 3. Automated Stepping Upon hitting the matching END LOOP marker, Oracle handles the arithmetic step internally, updating the counter by 1 without manual code prompts.
4. 4. Automated Context Purge Once the index steps past the upper range boundary, the loop terminates and the implicit index variable is permanently erased from session memory.

🧩 Quiz

What happens if you attempt to manually modify or assign a value to the loop index variable inside the body of a PL/SQL FOR loop (e.g., writing 'loop_idx := loop_idx + 2;')?

1. The statement executes successfully and changes the stride step of the loop.
2. The block runs but converts the FOR loop into an infinite Basic Loop.
3. Oracle throws an immediate compilation error because a FOR loop counter is strictly a read-only variable inside the loop body.
4. The block compiles successfully but ignores the line completely at runtime.

⚠️ Watch out

The REVERSE Boundary Trap

When you want a FOR loop to count backwards from 10 down to 1, you might instinctively write FOR i IN 10..1 LOOP. This is an absolute trap! Oracle strictly enforces that the lower bound must always sit on the left and the upper bound on the right. To run reverse cycles, you must write FOR i IN REVERSE 1..10 LOOP. If you write 10..1, Oracle evaluates that 10 is greater than 1, fails the boundary validation, and skips the entire loop block without executing a single line!

💭 Think first

The Loop Index Variable Masking Test

Mental Challenge: If you declare a variable named 'v_counter NUMBER := 100;' in your DECLARE block, and then execute a loop structured as 'FOR v_counter IN 1..5 LOOP', what value will print when you reference v_counter IMMEDIATELY AFTER the END LOOP statement? Think about variable scope.

📖 Theory

University Lab Exam Scoring Habit

External examiners grading the Semester 4 database lab evaluation often check your Basic Loops specifically for the presence of an EXIT condition. If you write a basic LOOP-END LOOP structure on paper or a terminal screen without pairing it with an explicit EXIT WHEN statement, you will instantly lose performance marks for introducing a malicious infinite lockup risk.

✅ Summary

Key takeaways

• Iterative loops automate repetitive operations safely without creating redundant code blocks.
• Basic Loops run continuously and require an explicit EXIT marker to break out of execution.
• WHILE Loops assess conditions at the initial entry gate before processing any internal code lines.
• FOR Loops automatically manage index variables and range bounds, treating loop counters as read-only.
• The REVERSE loop syntax strictly requires the lower bound listed first, like this: REVERSE lower..upper.
• The CONTINUE modifier bypasses the remainder of the active loop cycle to jump straight to the next pass.
• Memory hook: Basic loops need a manual switch, WHILE filters the entry gate, and FOR drives its own gears!