Oracle PL/SQL – Cursors

Subject: Information Technology – Database Systems
Grade: 12th Grade – HTL Computer Science
Prerequisites: PL/SQL Introduction, Control Structures
Author: HTL Pinkafeld – IF/IT

1. What is a Cursor?

1.1 Definition

A cursor is a named working area in Oracle server memory that holds the result of a SQL query. It allows processing multi-row query results row by row in PL/SQL.

SQL query → Oracle creates result set
                 ↓
        Cursor points to current row
                 ↓
    FETCH retrieves next row → PL/SQL processes it

1.2 Types of Cursors

2. Implicit Cursors

2.1 Automatic Cursors

Oracle automatically creates an implicit cursor for every SQL statement. The most recently executed implicit cursor is accessible as SQL:

DECLARE
    v_count NUMBER;
BEGIN
    UPDATE emp SET sal = sal * 1.05 WHERE deptno = 20;
    v_count := SQL%ROWCOUNT;  -- How many rows were updated?
    DBMS_OUTPUT.PUT_LINE(v_count || ' employees received a pay raise.');
    COMMIT;
END;
/

2.2 Implicit Cursor Attributes

BEGIN
    DELETE FROM emp WHERE empno = 9999;

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No record deleted.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' record(s) deleted.');
    END IF;
END;
/

3. Explicit Cursors – Basics

3.1 Lifecycle of an Explicit Cursor

DECLARE cursor    →  Define cursor (in DECLARE section)
OPEN cursor       →  Execute SQL, prepare result set
FETCH cursor INTO →  Read next row
CLOSE cursor      →  Close cursor, free memory

3.2 Complete Example

DECLARE
    -- Declare cursor
    CURSOR c_emp IS
        SELECT empno, ename, sal
        FROM   emp
        WHERE  deptno = 20
        ORDER  BY sal DESC;

    -- Variables for one row
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_sal   emp.sal%TYPE;
BEGIN
    OPEN c_emp;            -- Execute query

    LOOP
        FETCH c_emp INTO v_empno, v_ename, v_sal;  -- Get next row
        EXIT WHEN c_emp%NOTFOUND;                  -- End of result set?

        DBMS_OUTPUT.PUT_LINE(
            RPAD(v_ename, 12) || TO_CHAR(v_sal, 'FM9,990.00') || ' USD'
        );
    END LOOP;

    CLOSE c_emp;           -- Close cursor
END;
/

3.3 FETCH with %ROWTYPE

Instead of individual variables, FETCH can go directly into a row variable:

DECLARE
    CURSOR c_dept IS
        SELECT deptno, dname, loc FROM dept ORDER BY deptno;

    v_row dept%ROWTYPE;
BEGIN
    OPEN c_dept;
    LOOP
        FETCH c_dept INTO v_row;
        EXIT WHEN c_dept%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_row.deptno || ' | ' || v_row.dname || ' | ' || v_row.loc);
    END LOOP;
    CLOSE c_dept;
END;
/

4. Cursor Attributes

4.1 Overview

4.2 Attributes in Practice

DECLARE
    CURSOR c_sal IS SELECT ename, sal FROM emp ORDER BY sal;
    v_rec c_sal%ROWTYPE;
BEGIN
    IF NOT c_sal%ISOPEN THEN
        OPEN c_sal;
    END IF;

    LOOP
        FETCH c_sal INTO v_rec;
        EXIT WHEN c_sal%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Row ' || c_sal%ROWCOUNT || ': ' || v_rec.ename);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Total: ' || c_sal%ROWCOUNT || ' rows');
    CLOSE c_sal;
END;
/

5. Cursor FOR Loop

5.1 Automated OPEN / FETCH / CLOSE

The cursor FOR loop is the most elegant way for simple cursor processing. PL/SQL handles OPEN, FETCH, and CLOSE automatically:

DECLARE
    CURSOR c_emp IS
        SELECT ename, job, sal FROM emp WHERE deptno = 10;
BEGIN
    FOR r IN c_emp LOOP
        -- r is of type c_emp%ROWTYPE – automatically declared
        DBMS_OUTPUT.PUT_LINE(
            RPAD(r.ename, 12) || RPAD(r.job, 12) || r.sal
        );
    END LOOP;
    -- Cursor is already closed here
END;
/

5.2 Inline Cursor in FOR Loop

The cursor can be given directly as a subquery in the FOR loop:

BEGIN
    FOR r IN (
        SELECT d.dname, COUNT(e.empno) AS cnt, AVG(e.sal) AS avg_sal
        FROM   dept d
        LEFT   JOIN emp e ON d.deptno = e.deptno
        GROUP  BY d.dname
        ORDER  BY d.dname
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(r.dname, 15) ||
            'Employees: ' || NVL(TO_CHAR(r.cnt), '0') ||
            ', Avg Sal: ' || NVL(TO_CHAR(ROUND(r.avg_sal, 2)), '-')
        );
    END LOOP;
END;
/

6. Cursors with Parameters

6.1 Parameterized Cursors

Cursors can accept parameters, making them reusable:

DECLARE
    CURSOR c_emp (p_deptno emp.deptno%TYPE, p_min_sal emp.sal%TYPE) IS
        SELECT ename, sal
        FROM   emp
        WHERE  deptno  = p_deptno
        AND    sal     >= p_min_sal
        ORDER  BY sal DESC;
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== Dept 20, Salary >= 1500 ===');
    FOR r IN c_emp(20, 1500) LOOP
        DBMS_OUTPUT.PUT_LINE(r.ename || ': ' || r.sal);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('=== Dept 30, Salary >= 1000 ===');
    FOR r IN c_emp(30, 1000) LOOP
        DBMS_OUTPUT.PUT_LINE(r.ename || ': ' || r.sal);
    END LOOP;
END;
/

6.2 FOR UPDATE and WHERE CURRENT OF

To update the currently fetched row without re-querying:

DECLARE
    CURSOR c_update IS
        SELECT empno, sal FROM emp WHERE deptno = 20
        FOR UPDATE OF sal NOWAIT;
BEGIN
    FOR r IN c_update LOOP
        IF r.sal < 2000 THEN
            UPDATE emp
            SET    sal = r.sal * 1.15
            WHERE  CURRENT OF c_update;
        END IF;
    END LOOP;
    COMMIT;
END;
/

7. Cursor Variables (REF CURSOR)

7.1 Definition

A cursor variable (REF CURSOR) is a pointer to a cursor. Unlike static cursors, the SQL query can be specified at runtime:

DECLARE
    TYPE t_cursor IS REF CURSOR;
    cv t_cursor;
    v_ename VARCHAR2(10);
    v_sal   NUMBER;
BEGIN
    OPEN cv FOR SELECT ename, sal FROM emp WHERE deptno = 10;
    LOOP
        FETCH cv INTO v_ename, v_sal;
        EXIT WHEN cv%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename || ': ' || v_sal);
    END LOOP;
    CLOSE cv;
END;
/

7.2 SYS_REFCURSOR

Oracle provides SYS_REFCURSOR as a predefined weakly typed REF CURSOR:

DECLARE
    cv  SYS_REFCURSOR;
    v_row emp%ROWTYPE;
BEGIN
    OPEN cv FOR SELECT * FROM emp WHERE deptno = 30;
    FETCH cv INTO v_row;
    CLOSE cv;
END;
/

8. BULK COLLECT – Bulk Processing

8.1 Motivation

Each FETCH causes a context switch between PL/SQL engine and SQL engine. For many rows this is inefficient. BULK COLLECT reads many rows at once into a collection:

DECLARE
    TYPE t_ename_tab IS TABLE OF emp.ename%TYPE;
    TYPE t_sal_tab   IS TABLE OF emp.sal%TYPE;

    v_names    t_ename_tab;
    v_salaries t_sal_tab;
BEGIN
    SELECT ename, sal
    BULK COLLECT INTO v_names, v_salaries
    FROM emp
    WHERE deptno = 20;

    DBMS_OUTPUT.PUT_LINE('Rows read: ' || v_names.COUNT);
    FOR i IN 1..v_names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_names(i) || ': ' || v_salaries(i));
    END LOOP;
END;
/

8.2 BULK COLLECT with LIMIT (Chunking)

For very large tables, LIMIT can be used to read in chunks:

DECLARE
    CURSOR c_all IS SELECT empno, sal FROM emp;
    TYPE t_tab IS TABLE OF c_all%ROWTYPE;
    v_chunk t_tab;
    v_total NUMBER := 0;
BEGIN
    OPEN c_all;
    LOOP
        FETCH c_all BULK COLLECT INTO v_chunk LIMIT 100;
        EXIT WHEN v_chunk.COUNT = 0;
        FOR i IN 1..v_chunk.COUNT LOOP
            v_total := v_total + v_chunk(i).sal;
        END LOOP;
    END LOOP;
    CLOSE c_all;
    DBMS_OUTPUT.PUT_LINE('Total salary: ' || v_total);
END;
/

9. FORALL – Bulk DML

9.1 Motivation

FORALL sends a DML statement for all elements of a collection in a single context switch to the SQL engine:

DECLARE
    TYPE t_empno_tab IS TABLE OF emp.empno%TYPE;
    v_ids   t_empno_tab := t_empno_tab(7369, 7499, 7521, 7566);
    v_raise NUMBER := 200;
BEGIN
    FORALL i IN 1..v_ids.COUNT
        UPDATE emp
        SET    sal = sal + v_raise
        WHERE  empno = v_ids(i);

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
    COMMIT;
END;
/

10. Summary and Outlook

10.1 Cursor Comparison

10.2 Common Mistakes

10.3 Outlook

The next chapter covers Exceptions – structured error handling in PL/SQL:

• Predefined exceptions (NO_DATA_FOUND, ZERO_DIVIDE, …)
• User-defined exceptions
• RAISE_APPLICATION_ERROR
• PRAGMA EXCEPTION_INIT