Implicit cursors
Oracle automatically creates an implicit cursor for every DML statement and single-row SELECT INTO. Access attributes via SQL%:
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 20;
DBMS_OUTPUT.PUT_LINE('Updated: ' || SQL%ROWCOUNT || ' rows');
IF SQL%FOUND THEN -- at least one row affected
DBMS_OUTPUT.PUT_LINE('Success');
END IF;
Attributes: SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN (always FALSE for implicit).
Explicit cursors
Use when you need to process multiple rows one by one:
DECLARE
CURSOR c_emp (p_dept NUMBER) IS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = p_dept
ORDER BY salary DESC;
v_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp(20);
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.last_name || ': ' || v_emp.salary);
END LOOP;
CLOSE c_emp;
END;
Always close cursors in an exception handler too, or use cursor FOR loops (which close automatically).
Cursor FOR loop — the cleaner syntax
The cursor FOR loop opens, fetches, and closes automatically:
FOR r IN (SELECT * FROM employees WHERE dept = 20) LOOP
process_employee(r.employee_id, r.salary);
END LOOP;
This is the preferred style for row-by-row processing. However, for performance-critical code, replace with BULK COLLECT + FORALL.
REF CURSORs: returning result sets
REF CURSORs pass query results from PL/SQL to calling programs (Java, VBCS service connections, OIC DB adapter):
CREATE OR REPLACE PROCEDURE get_dept_employees (
p_dept_id IN NUMBER,
p_result OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_result FOR
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = p_dept_id;
-- Do NOT close p_result — caller closes it
END;
The caller (Java, Python, etc.) iterates the REF CURSOR and closes it.
Cursor variables in packages
TYPE ref_cur IS REF CURSOR; -- weak (any query)
TYPE emp_cur IS REF CURSOR RETURN employees%ROWTYPE; -- strong (typed)
Strong REF CURSORs provide compile-time type checking — prefer them when the return structure is known.
When not to use cursors
Cursor loops that execute DML inside the loop are prime candidates for BULK COLLECT + FORALL refactoring. If your code looks like “for each row, do an insert/update”, that’s almost always better handled as a set operation.