Skip to content

REF CURSOR (Cursor Variables) 🎞️🔀

Mentor's Note: A standard cursor is like a "Hardcoded Channel" on a TV. A REF CURSOR is like a "Variable Channel"—it can point to different queries at different times. It allows you to pass a whole result set from a Procedure to an Application (like Java or Python). 💡

🌟 The Scenario: The Multi-Menu Restaurant 🍽️

Imagine a waiter with a tablet. - At 8 AM, the tablet shows the Breakfast Menu. - At 1 PM, the same tablet shows the Lunch Menu. - The tablet (The REF CURSOR) stays the same, but the List of Items (The Query) changes based on the time. ✅

💻 1. The Basic Syntax

We use SYS_REFCURSOR (a built-in type) for the most flexibility.

DECLARE
   -- 1. Create the Cursor Variable
   v_cursor SYS_REFCURSOR;
   v_name   employees.first_name%TYPE;
   v_choice NUMBER := 1; -- 1 for Emp, 2 for Dept
BEGIN
   -- 2. Open it for DIFFERENT queries based on logic
   IF v_choice = 1 THEN
      OPEN v_cursor FOR SELECT first_name FROM employees;
   ELSE
      OPEN v_cursor FOR SELECT department_name FROM departments;
   END IF;

   -- 3. Fetch as usual
   LOOP
      FETCH v_cursor INTO v_name;
      EXIT WHEN v_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Result: ' || v_name);
   END LOOP;

   CLOSE v_cursor;
END;

💻 2. Passing Cursors between Subprograms 📤

This is the #1 use case for REF CURSORS in professional Oracle apps.

-- Procedure that "prepares" the data
CREATE OR REPLACE PROCEDURE get_staff_data(p_out_cursor OUT SYS_REFCURSOR) AS
BEGIN
   OPEN p_out_cursor FOR SELECT first_name FROM employees;
END;
/

🛡️ 3. Why use REF CURSOR? (Architect's Note)

  1. Abstraction: Your Web Application doesn't need to know the complex SQL logic. It just calls a procedure and receives a "Bag of Data" (the REF CURSOR).
  2. Flexibility: One variable can handle many different query structures.
  3. Decoupling: You can change the SQL inside the database without touching your Java/Python application code! ⚡

📊 Summary Table

Feature Static Cursor REF CURSOR
Logic Hardcoded Query Dynamic Query
Passing Cannot be passed ❌ Can be passed OUT ✅
Type Constant Variable

📈 Learning Path