Cursors

Attributes: %ISOPEN, %FOUND, %NOTFOUND, %ROWCOUNT.

21.1 Implicit Cursors

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 50;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');

21.2 Explicit Cursors

Attributes: %ISOPEN, %FOUND, %NOTFOUND, %ROWCOUNT.

DECLARE
    CURSOR c_emp (p_dept NUMBER) IS
        SELECT employee_id, last_name, salary FROM employees WHERE department_id = p_dept;
    r_emp c_emp%ROWTYPE;
BEGIN
    OPEN c_emp(50);
    LOOP
        FETCH c_emp INTO r_emp;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(r_emp.last_name);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Rows: ' || c_emp%ROWCOUNT);
    CLOSE c_emp;
END;
/

21.3 Cursor FOR Loop (Preferred for Simple Iteration)

Since 10g, cursor FOR loops automatically array-fetch 100 rows at a time (with PLSQL_OPTIMIZE_LEVEL >= 2).

BEGIN
    FOR r IN (SELECT last_name, salary FROM employees WHERE department_id = 60) LOOP
        DBMS_OUTPUT.PUT_LINE(r.last_name || ': ' || r.salary);
    END LOOP;
END;
/

21.4 FOR UPDATE and WHERE CURRENT OF

DECLARE
    CURSOR c IS SELECT employee_id, salary FROM employees WHERE department_id = 50 FOR UPDATE OF salary NOWAIT;
BEGIN
    FOR r IN c LOOP
        UPDATE employees SET salary = r.salary * 1.1 WHERE CURRENT OF c;
    END LOOP;
    COMMIT;
END;
/

21.5 REF Cursors (Cursor Variables)

Strong (TYPE t IS REF CURSOR RETURN employees%ROWTYPE) vs weak (SYS_REFCURSOR).

Implicit result sets (12c): DBMS_SQL.RETURN_RESULT(rc).

Cursor expressions: CURSOR(SELECT ...) in a SELECT list.

CREATE OR REPLACE PROCEDURE get_emps (p_dept IN NUMBER, p_rc OUT SYS_REFCURSOR) IS
BEGIN
    OPEN p_rc FOR SELECT employee_id, last_name FROM employees WHERE department_id = p_dept;
END;
/
VARIABLE rc REFCURSOR
EXEC get_emps(50, :rc)
PRINT rc