Skip to content

PL/SQL Cursor FOR LOOP 🔄

Mentor's Note: The manual 4-step cursor process (Open, Fetch, Exit, Close) is a lot of work. The Cursor FOR LOOP is the modern "Auto-Pilot" version. It handles all four steps for you in a single line of code! 💡

🌟 The Scenario: The Automatic Scanner 📄

Imagine you have a stack of 500 documents. - Manual: Pick up page -> Scan -> Put down -> Check if more left. - Auto-Pilot: You put the whole stack into an Automatic Document Feeder. The machine handles everything until the stack is empty. ✅

💻 1. The Basic Syntax

You don't even need to declare a variable to hold the data—Oracle creates a Record for you automatically!

DECLARE
   CURSOR c_emp IS SELECT first_name, salary FROM employees;
BEGIN
   -- 🚀 Logic: Oracle OPENS, FETCHES, and CLOSES automatically
   FOR r_emp IN c_emp LOOP
      DBMS_OUTPUT.PUT_LINE(r_emp.first_name || ' earns ' || r_emp.salary);
   END LOOP;
END;

💻 2. Inline Cursors

You don't even have to declare the cursor! You can write the query directly in the loop.

BEGIN
   FOR r_dept IN (SELECT department_name FROM departments) LOOP
      DBMS_OUTPUT.PUT_LINE('Dept: ' || r_dept.department_name);
   END LOOP;
END;

🛡️ 3. Why use Cursor FOR LOOP? (Architect's Note)

  1. Safety: You can never "forget" to close the cursor. No more memory leaks! ⚡
  2. Speed: Oracle internally optimizes these loops using Bulk Fetching (fetching rows in groups of 10 or 100), making them faster than manual loops.
  3. Clean Code: Your programs will be 50% shorter and much easier for other developers to read.

📊 Comparison: Manual vs. Automatic

Task Manual (Loop) Cursor FOR LOOP
Open Required Automatic ✅
Fetch Required Automatic ✅
Exit Check Required Automatic ✅
Close Required Automatic ✅

📈 Learning Path