Cursors

A cursor is a pointer to the result set of a SQL query. Oracle uses cursors internally for every SQL statement; PL/SQL lets you work with them explicitly when you need row-by-row processing.

Implicit Cursors

Every DML statement (INSERT, UPDATE, DELETE) and single-row SELECT automatically creates an implicit cursor. You access it through the SQL cursor attributes:

Attribute	Meaning
SQL%ROWCOUNT	Number of rows affected by the last DML
SQL%FOUND	TRUE if the last statement affected at least one row
SQL%NOTFOUND	TRUE if the last statement affected no rows
SQL%ISOPEN	Always FALSE for implicit cursors (auto-closed)

BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
  DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
END;
/

SELECT INTO

For queries that return exactly one row, use SELECT INTO to populate a variable:

DECLARE
  v_name employees.first_name%TYPE;
BEGIN
  SELECT first_name INTO v_name FROM employees WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

• Raises NO_DATA_FOUND if zero rows are returned.
• Raises TOO_MANY_ROWS if more than one row is returned.

Explicit Cursors

Use explicit cursors when a query returns multiple rows and you need to process them one at a time.

Declare → Open → Fetch → Close

DECLARE
  CURSOR c_employees IS
    SELECT first_name, salary FROM employees WHERE department_id = 10;

  v_name   employees.first_name%TYPE;
  v_salary employees.salary%TYPE;
BEGIN
  OPEN c_employees;
  LOOP
    FETCH c_employees INTO v_name, v_salary;
    EXIT WHEN c_employees%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);
  END LOOP;
  CLOSE c_employees;
END;
/

Always close cursors when done — open cursors consume memory and Oracle has a system limit.

Cursor FOR Loop (Recommended)

The cursor FOR loop is simpler — Oracle handles OPEN, FETCH, and CLOSE automatically:

DECLARE
  CURSOR c_emp IS SELECT first_name, salary FROM employees;
BEGIN
  FOR rec IN c_emp LOOP
    DBMS_OUTPUT.PUT_LINE(rec.first_name || ': ' || rec.salary);
  END LOOP;
END;
/

Or inline without a named cursor:

BEGIN
  FOR rec IN (SELECT first_name, salary FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.first_name || ': ' || rec.salary);
  END LOOP;
END;
/

Parameterised Cursors

Cursors can accept parameters, making them reusable:

DECLARE
  CURSOR c_dept(p_dept_id NUMBER) IS
    SELECT first_name FROM employees WHERE department_id = p_dept_id;
BEGIN
  FOR rec IN c_dept(10) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.first_name);
  END LOOP;
END;
/

Explicit Cursor Attributes