Understanding Oracle cursors

Every SQL statement in Oracle uses a cursor internally. The difference is whether you manage it yourself (explicit) or let Oracle handle it (implicit). Knowing when to use each type is key to writing clean, efficient PL/SQL.

Recipe #1 - Implicit cursors

Oracle automatically creates an implicit cursor for every DML statement. You can check its attributes right after execution:

BEGIN
  UPDATE emp SET sal = sal * 1.05 WHERE deptno = 10;

  IF SQL%FOUND THEN
    DBMS_OUTPUT.put_line(SQL%ROWCOUNT || ' rows updated.');
  ELSE
    DBMS_OUTPUT.put_line('No rows found in department 10.');
  END IF;
END;

The available attributes are:

• SQL%FOUND — TRUE if the last statement affected at least one row
• SQL%NOTFOUND — TRUE if no rows were affected
• SQL%ROWCOUNT — Number of rows affected
• SQL%ISOPEN — Always FALSE for implicit cursors

Recipe #2 - Explicit cursors

When you need fine-grained control over query processing, use explicit cursors:

DECLARE
  CURSOR c_emp(p_deptno NUMBER) IS
    SELECT empno, ename, sal
    FROM emp
    WHERE deptno = p_deptno
    ORDER BY sal DESC;

  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
  v_sal   emp.sal%TYPE;
BEGIN
  OPEN c_emp(20);
  LOOP
    FETCH c_emp INTO v_empno, v_ename, v_sal;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.put_line(v_ename || ': ' || v_sal);
  END LOOP;
  CLOSE c_emp;
END;

Recipe #3 - Cursor FOR loop (the preferred way)

The cursor FOR loop handles opening, fetching, and closing automatically. It's the cleanest and most common pattern:

DECLARE
  CURSOR c_emp IS
    SELECT ename, sal, deptno FROM emp ORDER BY deptno, sal DESC;
BEGIN
  FOR rec IN c_emp LOOP
    DBMS_OUTPUT.put_line(rec.deptno || ': '
      || rec.ename || ' — ' || rec.sal);
  END LOOP;
END;

You can even skip the cursor declaration entirely by putting the query inline:

BEGIN
  FOR rec IN (SELECT ename, sal FROM emp WHERE deptno = 30) LOOP
    DBMS_OUTPUT.put_line(rec.ename || ': ' || rec.sal);
  END LOOP;
END;

Recipe #4 - Cursors with FOR UPDATE

When you need to lock rows while processing them, use FOR UPDATE. CURRENT OF lets you reference the current row:

DECLARE
  CURSOR c_emp IS
    SELECT empno, sal FROM emp
    WHERE deptno = 10
    FOR UPDATE OF sal;
BEGIN
  FOR rec IN c_emp LOOP
    IF rec.sal < 2000 THEN
      UPDATE emp SET sal = 2000
      WHERE CURRENT OF c_emp;
    END IF;
  END LOOP;
  COMMIT;
END;

Rule of thumb: Use cursor FOR loops for simple iteration, explicit cursors when you need BULK COLLECT or complex flow control, and FOR UPDATE when you need row-level locks.