Explicit Cursors & Dynamic REF CURSORs

Master implicit vs explicit cursor lifecycles, cursor FOR loops, parameterized cursors, and dynamic REF CURSOR pointers for streaming datasets.

📽️ What is a Cursor?

A Cursor is an active memory pointer allocated in the private work area (PGA RAM) that holds the result set generated by executing a SQL query statement.

Analogy: A Movie Projection Lens & Film Roll.
- The database table is the full film roll stored in the vault.
- The SQL query selects specific frames (WHERE dept = 10).
- The Cursor is the projector lens pointing at the current single frame on screen (FETCH). Each time you click the remote control (LOOP), the lens moves forward by 1 frame.

                     Cursor Result Set & Memory Pointer
                                     │
    [Row 1: Alice]  ◄── Cursor Pointer Location (Current FETCH position)
    [Row 2: Bob]
    [Row 3: Charlie]

⚡ Implicit Cursors vs. Explicit Cursors

Oracle automatically manages Implicit Cursors for all DML operations (INSERT, UPDATE, DELETE, single-row SELECT INTO). Developers create Explicit Cursors for multi-row queries:

Implicit Cursor Attributes (`SQL%...`)

SQL%FOUND: Returns TRUE if DML modified $\ge 1$ row.
SQL%NOTFOUND: Returns TRUE if DML modified 0 rows.
SQL%ROWCOUNT: Returns total number of rows impacted by DML statement.

BEGIN
   UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;
   
   -- Check implicit cursor attribute
   IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' employees.');
   END IF;
END;
/

🔄 Explicit Cursor Lifecycle

An explicit cursor requires four distinct procedural management steps:

  1. DECLARE ──► 2. OPEN ──► 3. FETCH (Loop) ──► 4. CLOSE

DECLARE
   -- 1. DECLARE Cursor: Defines SQL query in PGA memory
   CURSOR c_high_earners IS
      SELECT employee_id, first_name, salary 
      FROM employees 
      WHERE salary > 100000;
      
   r_emp c_high_earners%ROWTYPE;
BEGIN
   -- 2. OPEN Cursor: Executes query and populates result set pointer
   OPEN c_high_earners;
   
   LOOP
      -- 3. FETCH Row: Reads current tuple and advances pointer 1 row
      FETCH c_high_earners INTO r_emp;
      EXIT WHEN c_high_earners%NOTFOUND;
      
      DBMS_OUTPUT.PUT_LINE(r_emp.first_name || ': $' || r_emp.salary);
   END LOOP;
   
   -- 4. CLOSE Cursor: Releases memory resources back to PGA
   CLOSE c_high_earners;
END;
/

The Clean Solution: Cursor `FOR` Loops

Oracle provides a Cursor FOR Loop construct that automatically handles OPEN, %ROWTYPE record declaration, FETCH, EXIT WHEN %NOTFOUND, and CLOSE:

DECLARE
   CURSOR c_dept_emps (p_dept_id INT) IS
      SELECT first_name, salary FROM employees WHERE department_id = p_dept_id;
BEGIN
   -- Automatically OPENS, FETCHES row-by-row into 'r', and CLOSES on completion!
   FOR r IN c_dept_emps(30) LOOP
      DBMS_OUTPUT.PUT_LINE('Employee: ' || r.first_name || ' ($' || r.salary || ')');
   END LOOP;
END;
/

🔀 Dynamic `REF CURSOR` & Parameterized Pointers

A REF CURSOR (Cursor Variable) is a dynamic pointer to a query result set. Unlike static explicit cursors, a REF CURSOR can be passed between procedures, opened for dynamic SQL strings, and returned directly to frontend client applications (Node.js, Java Spring Boot):

                        REF CURSOR Remote Pointers
                                    │
    ┌───────────────────────────────┴───────────────────────────────┐
    ▼                                                               ▼
Strong REF CURSOR (Strict Return Type)              Weak REF CURSOR (SYS_REFCURSOR)
Binds to 1 explicit row record type                Flexible pointer; openable for ANY query
TYPE t_strong IS REF CURSOR RETURN emp%ROWTYPE;     v_cursor SYS_REFCURSOR;

CREATE OR REPLACE PROCEDURE get_filtered_employees (
    p_min_salary IN  NUMBER,
    p_cursor     OUT SYS_REFCURSOR -- Returns dynamic cursor pointer to caller
) AS
BEGIN
   -- Opens weak REF CURSOR dynamically
   OPEN p_cursor FOR
      SELECT employee_id, first_name, salary 
      FROM employees 
      WHERE salary >= p_min_salary;
END;
/

❓ Conceptual Quizzes

Knowledge Check

What is the primary advantage of a Cursor FOR Loop over a standard OPEN-FETCH-CLOSE loop?

Knowledge Check

Why are SYS_REFCURSOR parameters used when building PL/SQL APIs for backend Java/Node.js microservices?

💻 Practice Problems

Problem: Streaming Filtered Data via SYS_REFCURSOR

Write a stored procedure named get_active_orders that accepts a p_status VARCHAR2 input parameter and returns an OUT SYS_REFCURSOR opened for SELECT order_id, customer_id, order_total FROM orders WHERE status = p_status.

Explicit Cursors & Dynamic REF CURSORs

What is the primary advantage of a Cursor FOR Loop over a standard OPEN-FETCH-CLOSE loop?

Why are SYS_REFCURSOR parameters used when building PL/SQL APIs for backend Java/Node.js microservices?

Problem: Streaming Filtered Data via SYS_REFCURSOR

On this page