Implicit Cursors

Whenever Oracle executes a SQL statement inside PL/SQL, it automatically creates and manages a cursor — a private work area in memory. For DML statements and SELECT INTO, Oracle manages this cursor invisibly: this is the implicit cursor.

What Is an Implicit Cursor?

You do not OPEN, FETCH, or CLOSE an implicit cursor. Oracle does all that for you. After any SQL statement executes, Oracle populates a set of cursor attributes on the special SQL cursor that reflect the outcome of the most recent statement.

Implicit Cursor Attributes

After SELECT INTO

DECLARE
    v_name  employees.first_name%TYPE;
BEGIN
    SELECT first_name
    INTO   v_name
    FROM   employees
    WHERE  employee_id = 100;

    -- SQL%FOUND is TRUE because exactly one row was returned
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Found: ' || v_name);
    END IF;

    -- SQL%ROWCOUNT is 1
    DBMS_OUTPUT.PUT_LINE('Rows fetched: ' || SQL%ROWCOUNT);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- SQL%FOUND would be FALSE here if we checked before the exception
        DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
/

After UPDATE

DECLARE
    v_dept_id  employees.department_id%TYPE := 60;
BEGIN
    UPDATE employees
    SET    salary = salary * 1.05
    WHERE  department_id = v_dept_id;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE(
            SQL%ROWCOUNT || ' employees in dept ' || v_dept_id || ' got a 5% raise.'
        );
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employees found in dept ' || v_dept_id || '.');
    END IF;

    COMMIT;
END;
/

Output (if dept 60 has 5 employees):

5 employees in dept 60 got a 5% raise.

After DELETE

DECLARE
    v_cutoff_date  DATE := ADD_MONTHS(SYSDATE, -60);  -- 5 years ago
BEGIN
    DELETE FROM job_history
    WHERE  end_date < v_cutoff_date;

    DBMS_OUTPUT.PUT_LINE('Deleted: ' || SQL%ROWCOUNT || ' old job history rows.');

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('Nothing to delete.');
    END IF;

    COMMIT;
END;
/

After INSERT

BEGIN
    INSERT INTO departments (department_id, department_name, location_id)
    VALUES (departments_seq.NEXTVAL, 'Data Engineering', 1700);

    -- SQL%ROWCOUNT is 1 after a single-row INSERT
    DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' department.');
    COMMIT;
END;
/

Combining SQL% Checks in a Procedure

CREATE OR REPLACE PROCEDURE transfer_employee(
    p_emp_id       IN employees.employee_id%TYPE,
    p_new_dept_id  IN employees.department_id%TYPE
) IS
BEGIN
    UPDATE employees
    SET    department_id = p_new_dept_id,
           manager_id    = (SELECT manager_id
                            FROM   departments
                            WHERE  department_id = p_new_dept_id)
    WHERE  employee_id = p_emp_id;

    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Employee ' || p_emp_id || ' does not exist.');
    END IF;

    DBMS_OUTPUT.PUT_LINE(
        'Employee ' || p_emp_id ||
        ' transferred to department ' || p_new_dept_id ||
        '. Rows updated: ' || SQL%ROWCOUNT
    );

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

-- Test
BEGIN
    transfer_employee(107, 90);
END;
/

SQL%ROWCOUNT with Bulk Operations

DECLARE
    v_dept_id   NUMBER := 80;
BEGIN
    -- Give all Sales employees a holiday bonus
    UPDATE employees
    SET    salary = salary + 500
    WHERE  department_id = v_dept_id;

    DBMS_OUTPUT.PUT_LINE('Bonus applied to ' || SQL%ROWCOUNT || ' Sales reps.');
    COMMIT;
END;
/

Key Difference: Implicit vs Explicit Cursors

Summary

• Implicit cursors are created automatically for every SQL statement inside PL/SQL.
• SQL%FOUND / SQL%NOTFOUND indicate whether rows were affected.
• SQL%ROWCOUNT tells you how many rows the last DML or SELECT INTO touched.
• SQL%ISOPEN is always FALSE for implicit cursors.
• Check cursor attributes immediately after the SQL — a subsequent statement resets them.
• For multi-row results, use explicit cursors or cursor FOR loops (next topic).