Skip to content

PL/SQL Cursors (The Movie Reel) 🎞️

Prerequisites: SELECT Statement, PL/SQL Records

Mentor's Note: A SELECT INTO can only handle one row. What if you need to process 1,000 employees? You need a Cursor. Think of it as a movie reel: you load the whole movie, but you view it one frame at a time. 💡

🌟 The Scenario: The Bank Queue 🏦

Imagine a bank with 100 people waiting. - Goal: Give every person a token. - The Cursor: The teller (The program) looks at the whole line, but calls One Person at a time to the window, processes them, and moves to the next.

💻 1. Implicit Cursors (Automatic)

Oracle opens these automatically for every SQL statement (INSERT, UPDATE, DELETE). You can check their status using Attributes: - SQL%FOUND: Did the query find any rows? - SQL%NOTFOUND: Is it empty? - SQL%ROWCOUNT: How many rows were affected?

💻 2. Explicit Cursors (The 4-Step Process)

For queries returning multiple rows, you must manage the cursor yourself.

DECLARE
   -- 1. DECLARE: Write the query
   CURSOR c_emp IS SELECT first_name FROM employees;
   v_name employees.first_name%TYPE;
BEGIN
   -- 2. OPEN: Execute the query
   OPEN c_emp;

   LOOP
      -- 3. FETCH: Get one row
      FETCH c_emp INTO v_name;
      EXIT WHEN c_emp%NOTFOUND; -- 🛑 Stop at end

      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
   END LOOP;

   -- 4. CLOSE: Free memory
   CLOSE c_emp;
END;

🏗️ Architect's Note: Performance & Memory 🛡️

  1. Closing Cursors: Always CLOSE your cursors! Open cursors consume memory on the server. If you leave too many open, you will get the dreaded ORA-01000: maximum open cursors exceeded error.
  2. Implicit is Faster: For single-row updates, implicit cursors are actually faster because Oracle performs fewer internal steps.

🎨 Visual Logic: The 4-Step Cycle

graph LR
    A[DECLARE 📝] --> B[OPEN 🗝️]
    B --> C[FETCH 📥]
    C --> D{Found?}
    D -- Yes --> C
    D -- No --> E[CLOSE 🚪]

📈 Learning Path