Understanding Oracle Cursors in PL/SQL

In Oracle PL/SQL, cursors are pointers that allow you to work with the result set of a SQL query. They act as handles for fetching rows returned by a SELECT statement. Cursors are essential when you need to process query results row by row, perform complex logic, or reuse queries dynamically.

Implicit Cursors

Oracle automatically creates an implicit cursor every time you run a DML statement (INSERT, UPDATE, DELETE) or a SELECT … INTO query. You don’t need to declare or open it manually—Oracle does all the work behind the scenes.

Example: Implicit Cursor with SELECT INTO

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

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_first_name);

    -- Access implicit cursor attributes
    DBMS_OUTPUT.PUT_LINE('Rows Processed: ' || SQL%ROWCOUNT);
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Record found.');
    END IF;
END;

Key Points:

• SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT automatically refer to the last executed SQL statement.
• Best for single-row queries or simple DML operations.

Explicit Cursors

An explicit cursor is one you declare, open, fetch from, and close manually. Use these when:

• You expect multiple rows.
• You need more control over fetching and processing data.

Steps for Using Explicit Cursors

1. Declare the cursor with a SELECT statement.
2. Open the cursor.
3. Fetch rows into variables.
4. Close the cursor.

Example: Explicit Cursor

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 60;
    v_id    employees.employee_id%TYPE;
    v_name  employees.first_name%TYPE;
    v_sal   employees.salary%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_id, v_name, v_sal;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name || ' - ' || v_sal);
    END LOOP;
    CLOSE emp_cursor;
END;

Cursor FOR Loop

The Cursor FOR loop simplifies explicit cursor handling by automatically opening, fetching, and closing the cursor. It reduces boilerplate code.

Example: Cursor FOR Loop

BEGIN
    FOR rec IN (SELECT employee_id, first_name, salary
                FROM employees
                WHERE department_id = 90) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' ||
                             rec.first_name  || ' - ' ||
                             rec.salary);
    END LOOP;
END;

Advantages:

• No need for explicit OPEN, FETCH, or CLOSE.
• Automatically uses an implicit cursor for the loop.

Cursor Attributes

Oracle provides special attributes to check the cursor’s status or the result of the last fetch:

Example Using Attributes

DECLARE
    CURSOR c IS SELECT employee_id FROM employees WHERE ROWNUM <= 5;
    v_id employees.employee_id%TYPE;
BEGIN
    OPEN c;
    LOOP
        FETCH c INTO v_id;
        EXIT WHEN c%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Fetched ID: ' || v_id);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Rows Fetched: ' || c%ROWCOUNT);

    IF c%ISOPEN THEN
        CLOSE c;
    END IF;
END;

Parameterized Cursors

Parameterized cursors accept values at runtime, making them reusable for different queries. They’re useful when the WHERE clause changes depending on input.

Example: Parameterized Cursor

DECLARE
    CURSOR emp_by_dept(p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR rec IN emp_by_dept(50) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.first_name);
    END LOOP;

    FOR rec IN emp_by_dept(90) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.first_name);
    END LOOP;
END;

Key Point:
The cursor behaves like a function that takes parameters.

REF CURSOR (Dynamic Cursors)

A REF CURSOR (or cursor variable) is a pointer to a result set that can be opened dynamically at runtime. It’s widely used for dynamic SQL, procedures returning datasets, or passing results between programs.

Example: REF CURSOR

DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    v_cursor ref_cursor_type;
    v_id     employees.employee_id%TYPE;
    v_name   employees.first_name%TYPE;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name
        FROM employees
        WHERE salary > 10000;

    LOOP
        FETCH v_cursor INTO v_id, v_name;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
    END LOOP;

    CLOSE v_cursor;
END;

Real-World Use:

• Returning a result set from a stored procedure to a calling application: CREATE OR REPLACE PROCEDURE get_high_salary(p_salary IN NUMBER, p_out OUT SYS_REFCURSOR) AS BEGIN OPEN p_out FOR SELECT employee_id, first_name, salary FROM employees WHERE salary > p_salary; END;

Best Practices for Using Cursors

1. Use implicit cursors for single-row queries or simple DML.
2. Prefer Cursor FOR loops when possible—they are cleaner and less error-prone.
3. Always close explicit and REF cursors to free memory.
4. Use parameterized cursors to avoid repetitive code for similar queries.
5. Use REF CURSOR for flexible, dynamic operations and client-facing procedures.
6. Avoid unnecessary cursors—bulk operations or set-based SQL are usually faster.

Summary Table

Final Thoughts

Cursors are fundamental to PL/SQL programming. They give you row-by-row control when set-based operations aren’t enough. Start with implicit cursors for simplicity, move to explicit or FOR loop cursors for multi-row queries, and adopt REF CURSORs for advanced, dynamic requirements. Using cursor attributes and parameters properly ensures flexible, efficient, and maintainable PL/SQL code.