PL/SQL Cursors

A cursor is a name for a query's result set, plus a pointer that lets you walk through it one row at a time. Every SELECT in PL/SQL uses a cursor internally — most of the time it is implicit and invisible; sometimes you need explicit control over it.

Implicit Cursors

A SELECT ... INTO that returns exactly one row uses an implicit cursor: Oracle opens it, fetches the one row, and closes it for you. If it returns zero rows or more than one, PL/SQL raises NO_DATA_FOUND or TOO_MANY_ROWS — see Exceptions.

Example:

declare
  n_count number;
begin
  select count(*) into n_count from dual;
  dbms_output.put_line('count = ' || n_count);
end;
/

Explicit Cursors

When a query can return many rows and you need to process them one by one, declare the cursor explicitly, then drive it through four steps: OPEN, repeated FETCH, and finally CLOSE. Always close what you open — an open cursor holds server resources until it is closed or the session ends.

Example:

declare
  cursor c_persons is
    select name, salary from persons where salary > 10000;
  v_name   persons.name%type;
  v_salary persons.salary%type;
begin
  open c_persons;
  loop
    fetch c_persons into v_name, v_salary;
    exit when c_persons%notfound; -- cursor attributes use %, not #
    dbms_output.put_line(v_name || ', ' || v_salary);
  end loop;
  close c_persons;
end;
/

Cursor Attributes

Attribute	Meaning
`%FOUND`	True if the last fetch returned a row.
`%NOTFOUND`	True if the last fetch found no row — the usual loop-exit condition.
`%ROWCOUNT`	How many rows have been fetched so far.
`%ISOPEN`	True if the cursor is currently open.

%ROWTYPE

Instead of one variable per column, %ROWTYPE declares a single record whose fields match the cursor's columns one for one. It keeps the receiving variable in sync automatically if the query's column list ever changes.

Example:

declare
  cursor c_persons is select name, salary from persons;
  v_person c_persons%rowtype; -- one record, not two scalars
begin
  open c_persons;
  fetch c_persons into v_person;
  dbms_output.put_line(v_person.name || ', ' || v_person.salary);
  close c_persons;
end;
/

Cursor FOR Loop

The cursor FOR loop is the idiomatic shortcut: it declares the record, opens the cursor, fetches every row, and closes the cursor automatically — no OPEN/FETCH/EXIT WHEN/CLOSE to write or forget.

Example:

begin
  for r in (select name, salary from persons where salary > 10000) loop
    dbms_output.put_line(r.name || ', ' || r.salary);
  end loop;
end;
/

REF CURSOR

A REF CURSOR is a cursor variable: instead of being tied to one fixed query at compile time, it can point to different queries at runtime, and it can be passed as a parameter or returned from a function — the usual way a PL/SQL procedure hands a whole result set back to a caller (a Java application, a report, another PL/SQL block).

Example:

declare
  type t_ref_cursor is ref cursor;
  c_any  t_ref_cursor;
  v_name persons.name%type;
begin
  open c_any for select name from persons where salary > 10000;
  loop
    fetch c_any into v_name;
    exit when c_any%notfound;
    dbms_output.put_line(v_name);
  end loop;
  close c_any;
end;
/

See it in a full demo: Demo 07 — Cursors.