Explicit Cursors & FOR UPDATE

An explicit cursor is a named pointer to a multi-row query result set that you control: you OPEN it, FETCH rows from it, and CLOSE it. Explicit cursors give you fine-grained control over row-by-row processing.

Declaring and Using an Explicit Cursor

The full lifecycle — DECLARE → OPEN → FETCH → CLOSE:

DECLARE
    -- 1. Declare the cursor
    CURSOR c_dept_employees IS
        SELECT employee_id, first_name, last_name, salary
        FROM   employees
        WHERE  department_id = 60
        ORDER BY salary DESC;

    -- Variables to hold fetched values (use %TYPE for safety)
    v_emp_id    employees.employee_id%TYPE;
    v_fname     employees.first_name%TYPE;
    v_lname     employees.last_name%TYPE;
    v_salary    employees.salary%TYPE;
BEGIN
    -- 2. Open — executes the query, creates result set
    OPEN c_dept_employees;

    -- 3. Fetch — retrieve rows one at a time
    LOOP
        FETCH c_dept_employees INTO v_emp_id, v_fname, v_lname, v_salary;
        EXIT WHEN c_dept_employees%NOTFOUND;  -- stop when no more rows

        DBMS_OUTPUT.PUT_LINE(
            v_fname || ' ' || v_lname || ': $' || v_salary
        );
    END LOOP;

    -- 4. Close — release resources
    CLOSE c_dept_employees;
END;
/

Cursor Attributes

DECLARE
    CURSOR c_emp IS SELECT employee_id, salary FROM employees WHERE rownum <= 3;
    v_id  employees.employee_id%TYPE;
    v_sal employees.salary%TYPE;
BEGIN
    OPEN c_emp;
    DBMS_OUTPUT.PUT_LINE('Is open: ' || CASE WHEN c_emp%ISOPEN THEN 'YES' ELSE 'NO' END);

    LOOP
        FETCH c_emp INTO v_id, v_sal;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Row ' || c_emp%ROWCOUNT || ': emp=' || v_id);
    END LOOP;

    CLOSE c_emp;
END;
/

Cursor FOR Loop (Preferred)

The cursor FOR loop is the cleanest syntax — Oracle opens, fetches, and closes the cursor automatically. A record variable is declared implicitly:

DECLARE
    CURSOR c_high_earners IS
        SELECT e.first_name, e.last_name, e.salary, d.department_name
        FROM   employees   e
        JOIN   departments d ON d.department_id = e.department_id
        WHERE  e.salary > 10000
        ORDER BY e.salary DESC;
BEGIN
    FOR r IN c_high_earners LOOP
        DBMS_OUTPUT.PUT_LINE(
            r.first_name || ' ' || r.last_name ||
            ' (' || r.department_name || '): $' || r.salary
        );
    END LOOP;
    -- Cursor is automatically closed after the loop
END;
/

You can also inline the query directly in the FOR loop:

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

Cursor Parameters

Parameterized cursors accept values when opened, making them reusable:

DECLARE
    CURSOR c_dept_emps(p_dept_id IN NUMBER, p_min_salary IN NUMBER DEFAULT 0) IS
        SELECT first_name, last_name, salary
        FROM   employees
        WHERE  department_id = p_dept_id
        AND    salary >= p_min_salary
        ORDER BY salary DESC;
BEGIN
    -- Open for department 80 with no salary filter
    DBMS_OUTPUT.PUT_LINE('=== Sales (dept 80) ===');
    FOR r IN c_dept_emps(80) LOOP
        DBMS_OUTPUT.PUT_LINE(r.first_name || ': $' || r.salary);
    END LOOP;

    -- Open for department 60 with minimum salary
    DBMS_OUTPUT.PUT_LINE('=== IT (dept 60), salary >= $6000 ===');
    FOR r IN c_dept_emps(60, 6000) LOOP
        DBMS_OUTPUT.PUT_LINE(r.first_name || ': $' || r.salary);
    END LOOP;
END;
/

FOR UPDATE and WHERE CURRENT OF

Use FOR UPDATE to lock rows as they are fetched, preventing other sessions from changing them. WHERE CURRENT OF cursor_name updates or deletes the row most recently fetched — no need to re-specify the WHERE clause:

DECLARE
    CURSOR c_low_salary IS
        SELECT employee_id, salary, first_name
        FROM   employees
        WHERE  salary < 5000
        FOR UPDATE OF salary NOWAIT;  -- NOWAIT: fail immediately if locked
BEGIN
    FOR r IN c_low_salary LOOP
        UPDATE employees
        SET    salary = salary * 1.10
        WHERE  CURRENT OF c_low_salary;  -- updates the row just fetched

        DBMS_OUTPUT.PUT_LINE(
            'Raised ' || r.first_name || ' from $' || r.salary ||
            ' to $' || ROUND(r.salary * 1.10, 2)
        );
    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

FOR UPDATE Options

DECLARE
    CURSOR c_skip IS
        SELECT employee_id, salary
        FROM   employees
        WHERE  department_id = 80
        FOR UPDATE SKIP LOCKED;
BEGIN
    FOR r IN c_skip LOOP
        UPDATE employees
        SET    salary = salary + 100
        WHERE  CURRENT OF c_skip;
    END LOOP;
    COMMIT;
END;
/

Summary

• Explicit cursors: DECLARE the query, OPEN, FETCH INTO, CLOSE.
• Use %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN to inspect cursor state.
• Cursor FOR loop is the preferred pattern for read-only iteration — handles OPEN/FETCH/CLOSE automatically.
• Parameterized cursors make the same cursor reusable with different inputs.
• FOR UPDATE locks fetched rows; WHERE CURRENT OF cursor updates/deletes the current row without repeating the WHERE clause.
• Use NOWAIT, WAIT n, or SKIP LOCKED to control lock-wait behavior.