Cursors & Row Processing in PL/SQL

TL;DR

A cursor is a pointer to a result set. Use cursors when you need to process multiple rows one by one (unlike SELECT ... INTO, which works for single rows only).

Cursor FOR loop is the preferred pattern — it opens, fetches, and closes automatically. Explicit cursor handling (OPEN, FETCH, CLOSE) is older and more verbose.

Cursor attributes (%FOUND, %NOTFOUND, %ROWCOUNT) tell you if rows were found and how many. Parameterised cursors accept input values, making them reusable.

What Is a Cursor?

A cursor is a pointer to a result set in the database. It's the mechanism for fetching multiple rows from a query and processing them one row at a time inside a PL/SQL block.

In lesson 1, we used SELECT ... INTO, which returns exactly one row. If your SELECT matches 100 rows, it throws a TOO_MANY_ROWS exception. Cursors solve this — they let you fetch and iterate over multiple rows.

Callidus Commissions PL/SQL procedures iterate over participants, results, and quotas constantly. The most common pattern is: open a cursor over CS_PARTICIPANT, loop through each active participant, and for each one, calculate commission, apply rules, and insert results. Cursors are central to this pattern.

Implicit Cursors (SQL Context)

Every SQL statement (SELECT, INSERT, UPDATE, DELETE) in PL/SQL opens an implicit cursor. You don't declare it — Oracle manages it automatically. You can check implicit cursor attributes after any DML:

DECLARE
  v_rows_updated PLS_INTEGER;

BEGIN
  -- UPDATE statement uses an implicit cursor
  UPDATE CS_RESULTS
  SET    PROCESSED = 1
  WHERE  PERIOD_ID = '2026-Q1'
    AND  STATUS = 'DRAFT';

  -- SQL%ROWCOUNT: number of rows affected by the last statement
  v_rows_updated := SQL%ROWCOUNT;

  DBMS_OUTPUT.PUT_LINE(
    'Updated ' || v_rows_updated || ' Q1 draft results'
  );

  -- SQL%NOTFOUND: true if no rows were affected
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No draft results found');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Updates successful');
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;
/

The implicit cursor attributes are:

SQL%ROWCOUNT: Number of rows affected (updated, deleted, or selected).
SQL%FOUND: TRUE if at least one row was affected.
SQL%NOTFOUND: TRUE if no rows were affected (opposite of %FOUND).
SQL%ISOPEN: Always FALSE for implicit cursors (they close immediately after the statement).

Explicit Cursors: The Cursor FOR Loop

An explicit cursor is one you declare in the DECLARE section and manage in the BEGIN section. The cursor FOR loop is the standard modern pattern — it handles opening, fetching, and closing for you.

DECLARE
  -- Cursor definition: SELECT statement
  CURSOR cur_active_participants IS
    SELECT ID, NAME, PLAN_ID, STATUS
    FROM   CS_PARTICIPANT
    WHERE  STATUS = 'ACTIVE'
    ORDER BY ID;

BEGIN
  -- Cursor FOR loop: automatically opens, fetches, and closes
  FOR rec IN cur_active_participants LOOP
    DBMS_OUTPUT.PUT_LINE(
      'ID: ' || rec.ID ||
      ', Name: ' || rec.NAME ||
      ', Plan: ' || rec.PLAN_ID
    );
    -- Your per-row logic here
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Done processing participants');

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;
/

Key points:

Declare the cursor with CURSOR name IS SELECT ....
In the FOR loop, rec is automatically declared as a %ROWTYPE variable matching the cursor's columns. You access columns with dot notation: rec.ID, rec.NAME.
The FOR loop opens the cursor before iteration, fetches each row, and closes the cursor after iteration completes (or if an exception occurs). You don't need to manage OPEN/FETCH/CLOSE.
If the cursor returns zero rows, the loop simply doesn't execute — no exception.

Parameterised Cursors

Cursors can accept parameters, making them reusable with different filter values.

DECLARE
  -- Cursor with parameter (p_period_id is the placeholder)
  CURSOR cur_period_results (p_period_id VARCHAR2) IS
    SELECT PARTICIPANT_ID, SUM(RESULT_AMOUNT) AS TOTAL_EARNED,
           COUNT(*) AS RESULT_COUNT
    FROM   CS_RESULTS
    WHERE  PERIOD_ID = p_period_id
    GROUP BY PARTICIPANT_ID
    ORDER BY TOTAL_EARNED DESC;

BEGIN
  -- Call cursor with parameter: pass '2026-Q1'
  FOR rec IN cur_period_results('2026-Q1') LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Participant ' || rec.PARTICIPANT_ID ||
      ': Earned $' || rec.TOTAL_EARNED ||
      ' from ' || rec.RESULT_COUNT || ' results'
    );
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('---');

  -- Same cursor, different parameter: pass '2026-Q2'
  FOR rec IN cur_period_results('2026-Q2') LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Q2: Participant ' || rec.PARTICIPANT_ID ||
      ' earned $' || rec.TOTAL_EARNED
    );
  END LOOP;

END;
/

Parameterised cursors are more efficient than building dynamic SQL strings. Use them whenever you need the same cursor logic with different filter values.

Explicit Cursor Attributes

When you use a cursor FOR loop, you rarely need cursor attributes — the loop handles everything. But if you use explicit OPEN/FETCH/CLOSE (an older pattern), you check attributes to know when to stop:

Attribute	Returns	Usage
%FOUND	TRUE if the last FETCH returned a row	Check if row was successfully fetched
%NOTFOUND	TRUE if the last FETCH returned no row (end of result set)	Exit loop when no more rows
%ROWCOUNT	Number of rows fetched so far	Track iteration count
%ISOPEN	TRUE if cursor is open	Check if cursor needs to be opened

DECLARE
  CURSOR cur_participants IS
    SELECT ID, NAME
    FROM   CS_PARTICIPANT
    WHERE  STATUS = 'ACTIVE';

  -- Record variable to hold fetched row
  rec_participant cur_participants%ROWTYPE;

BEGIN
  -- Manually open, fetch, and close (not recommended unless needed)
  OPEN cur_participants;

  LOOP
    FETCH cur_participants INTO rec_participant;
    EXIT WHEN cur_participants%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
      'Row ' || cur_participants%ROWCOUNT ||
      ': ' || rec_participant.NAME
    );
  END LOOP;

  CLOSE cur_participants;

EXCEPTION
  WHEN OTHERS THEN
    IF cur_participants%ISOPEN THEN
      CLOSE cur_participants;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;
/

This pattern is older and more verbose. Unless you have a specific reason to use explicit OPEN/FETCH/CLOSE (e.g., you need to pause iteration or fetch specific rows), use the cursor FOR loop instead. It's cleaner and safer.

💡Rule of thumb: Use cursor FOR loops 95% of the time. Use explicit OPEN/FETCH/CLOSE only if you need fine-grained control (e.g., pause iteration, manually advance, or handle rows conditionally). In Callidus Commissions procedures, you'll almost always see cursor FOR loops.

Set-Based vs Row-by-Row Processing

Cursors iterate row-by-row, which is intuitive but slow for large datasets. Whenever possible, use set-based SQL (UPDATE/DELETE that matches multiple rows at once). Reserve cursors for logic that must vary per row.

-- SLOW: Row-by-row update (Callidus scenario: apply 10% increase to all Q1 earnings)
DECLARE
  CURSOR cur_q1_results IS
    SELECT ID, RESULT_AMOUNT
    FROM   CS_RESULTS
    WHERE  PERIOD_ID = '2026-Q1';

BEGIN
  FOR rec IN cur_q1_results LOOP
    UPDATE CS_RESULTS
    SET    RESULT_AMOUNT = rec.RESULT_AMOUNT * 1.1
    WHERE  ID = rec.ID;
  END LOOP;
END;
/

-- FAST: Set-based update (does the same thing in one statement)
BEGIN
  UPDATE CS_RESULTS
  SET    RESULT_AMOUNT = RESULT_AMOUNT * 1.1
  WHERE  PERIOD_ID = '2026-Q1';

  DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows');
END;
/

The second approach is 10x–100x faster on large datasets. Use cursors only when per-row logic is unavoidable — complex conditionals, lookups, or calls to functions that vary by row.

Practical Callidus Example: Commission Adjustment by Plan

Here's a realistic procedure that uses a parameterised cursor to iterate over results and apply plan-specific adjustments.

DECLARE
  -- Cursor: fetch results for a given period, with plan info
  CURSOR cur_results_by_period (p_period_id VARCHAR2) IS
    SELECT r.ID, r.PARTICIPANT_ID, r.RESULT_AMOUNT, p.PLAN_ID
    FROM   CS_RESULTS r
    JOIN   CS_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
    WHERE  r.PERIOD_ID = p_period_id
      AND  r.STATUS = 'PROCESSED'
    ORDER BY r.PARTICIPANT_ID;

  v_adjustment_pct  NUMBER(5,2);
  v_adjusted_amount NUMBER(18,2);
  v_count           PLS_INTEGER := 0;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Processing Q1 2026 results...');

  FOR rec IN cur_results_by_period('2026-Q1') LOOP
    -- Determine adjustment based on plan
    v_adjustment_pct := CASE rec.PLAN_ID
      WHEN 'FIELD_SALES'   THEN 5
      WHEN 'INSIDE_SALES'  THEN 3
      WHEN 'CHANNEL'        THEN 2
      ELSE 0
    END;

    -- Calculate adjusted amount
    v_adjusted_amount := rec.RESULT_AMOUNT * ((100 + v_adjustment_pct) / 100);

    -- Insert adjustment record
    INSERT INTO CS_ADJUSTMENTS
      (RESULT_ID, PARTICIPANT_ID, PERIOD_ID, ORIGINAL_AMOUNT, ADJUSTED_AMOUNT,
       ADJUSTMENT_PCT, CREATED_DATE)
    VALUES
      (rec.ID, rec.PARTICIPANT_ID, '2026-Q1', rec.RESULT_AMOUNT, v_adjusted_amount,
       v_adjustment_pct, SYSDATE);

    v_count := v_count + 1;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE(
    'Processed ' || v_count || ' results, inserted ' ||
    ' adjustment records'
  );

  -- Commit changes
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;
/

This procedure demonstrates:

Parameterised cursor filtering by period.
JOIN in the cursor to fetch plan info.
Per-row logic: determine adjustment based on plan, calculate adjusted amount.
DML inside the loop: INSERT adjustment records.
COMMIT to persist changes (or ROLLBACK on error).

Inline Cursors (No Explicit Declaration)

You can use a cursor FOR loop without pre-declaring the cursor. Just put the SELECT directly in the FOR clause:

BEGIN
  -- No CURSOR declaration; SELECT is inline
  FOR rec IN (
    SELECT ID, NAME, PLAN_ID
    FROM   CS_PARTICIPANT
    WHERE  STATUS = 'ACTIVE'
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || rec.ID || ' Name: ' || rec.NAME);
  END LOOP;
END;
/

Inline cursors are convenient for one-off queries. For reusable cursors (used multiple times or passed as parameters), pre-declare the cursor in DECLARE.

Key Takeaways

Cursors let you iterate over multiple rows. Use them when SELECT ... INTO won't work (multiple rows).
Cursor FOR loops are the modern standard — they handle open/fetch/close automatically.
Parameterised cursors make cursors reusable with different filter values.
Use explicit OPEN/FETCH/CLOSE only if you need fine-grained control (rare).
When processing large datasets, prefer set-based SQL (one UPDATE/DELETE statement) over cursor loops. Cursors are 10x–100x slower.
In Callidus Commissions, you'll use cursors for per-participant or per-result logic that depends on individual row values.

Next: Procedures & Functions →

Learn how to create reusable stored procedures and functions that encapsulate cursor logic and data processing.

Lesson 4: Procedures & Functions →