Oracle PL/SQL – Cursor

Fach: Informationstechnologie – Datenbanksysteme
Schulstufe: 12. Schulstufe – HTL Informatik
Voraussetzungen: PL/SQL Einführung, Kontrollstrukturen
Autor: HTL Pinkafeld – IF/IT

1. Was ist ein Cursor?

1.1 Definition

Ein Cursor ist ein benannter Arbeitsbereich im Speicher des Oracle-Servers, der das Ergebnis einer SQL-Abfrage enthält. Er ermöglicht es, mehrzeilige Abfrageergebnisse zeilenweise in PL/SQL zu verarbeiten.

SQL-Abfrage → Oracle erzeugt Ergebnismenge (Result Set)
                    ↓
             Cursor zeigt auf aktuelle Zeile
                    ↓
         FETCH holt nächste Zeile → PL/SQL verarbeitet sie

1.2 Typen von Cursorn

2. Implizite Cursor

2.1 Automatische Cursor

Oracle erzeugt für jedes SQL-Statement automatisch einen impliziten Cursor. Der zuletzt ausgeführte implizite Cursor ist als SQL ansprechbar:

DECLARE
    v_anzahl NUMBER;
BEGIN
    UPDATE emp SET sal = sal * 1.05 WHERE deptno = 20;
    v_anzahl := SQL%ROWCOUNT;  -- Wie viele Zeilen wurden aktualisiert?
    DBMS_OUTPUT.PUT_LINE(v_anzahl || ' Mitarbeiter erhalten Gehaltserhöhung.');
    COMMIT;
END;
/

2.2 Implizite Cursor-Attribute

BEGIN
    DELETE FROM emp WHERE empno = 9999;

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('Kein Datensatz gelöscht.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' Datensatz/Datensätze gelöscht.');
    END IF;
END;
/

3. Explizite Cursor – Grundlagen

3.1 Lebenszyklus eines expliziten Cursors

DECLARE cursor    →  Cursor definieren (im DECLARE-Abschnitt)
OPEN cursor       →  SQL ausführen, Ergebnismenge bereitstellen
FETCH cursor INTO →  Nächste Zeile lesen
CLOSE cursor      →  Cursor schließen, Speicher freigeben

3.2 Vollständiges Beispiel

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

    -- Variablen für eine Zeile
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_sal   emp.sal%TYPE;
BEGIN
    OPEN c_emp;            -- Abfrage ausführen

    LOOP
        FETCH c_emp INTO v_empno, v_ename, v_sal;   -- Nächste Zeile holen
        EXIT WHEN c_emp%NOTFOUND;                   -- Ende der Ergebnismenge?

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

    CLOSE c_emp;           -- Cursor schließen
END;
/

3.3 FETCH mit %ROWTYPE

Statt einzelner Variablen kann direkt in eine Zeilenvariable gefetcht werden:

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

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

4. Cursor-Attribute

4.1 Übersicht

4.2 Attribute in der Praxis

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    -- Vor OPEN ist ISOPEN = FALSE
        OPEN c_sal;
    END IF;

    FETCH c_sal INTO v_rec;
    IF c_sal%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Erster Datensatz: ' || v_rec.ename);
    END IF;

    -- Alle weiteren Zeilen verarbeiten
    LOOP
        FETCH c_sal INTO v_rec;
        EXIT WHEN c_sal%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
            'Zeile ' || c_sal%ROWCOUNT || ': ' || v_rec.ename
        );
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Gesamt: ' || c_sal%ROWCOUNT || ' Zeilen');
    CLOSE c_sal;
END;
/

5. Cursor-FOR-Schleife

5.1 Automatisiertes OPEN / FETCH / CLOSE

Die Cursor-FOR-Schleife ist der eleganteste Weg für einfache Cursor-Verarbeitung. PL/SQL übernimmt OPEN, FETCH und CLOSE automatisch:

DECLARE
    CURSOR c_emp IS
        SELECT ename, job, sal FROM emp WHERE deptno = 10;
BEGIN
    FOR r IN c_emp LOOP
        -- r ist vom Typ c_emp%ROWTYPE – automatisch deklariert
        DBMS_OUTPUT.PUT_LINE(
            RPAD(r.ename, 12) || RPAD(r.job, 12) || r.sal
        );
    END LOOP;
    -- Cursor ist hier bereits geschlossen
END;
/

5.2 Inline-Cursor in der FOR-Schleife

Der Cursor kann direkt in der FOR-Schleife als Subquery angegeben werden:

BEGIN
    FOR r IN (
        SELECT d.dname, COUNT(e.empno) AS anzahl, 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) ||
            'Mitarbeiter: ' || NVL(TO_CHAR(r.anzahl), '0') ||
            ', Ø Gehalt: '  || NVL(TO_CHAR(ROUND(r.avg_sal,2)), '-')
        );
    END LOOP;
END;
/

5.3 Empfehlung

6. Cursor mit Parametern

6.1 Parameterisierte Cursor

Cursor können Parameter entgegennehmen, was sie wiederverwendbar macht:

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('=== Abteilung 20, Gehalt >= 1500 ===');
    FOR r IN c_emp(20, 1500) LOOP
        DBMS_OUTPUT.PUT_LINE(r.ename || ': ' || r.sal);
    END LOOP;

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

6.2 Cursor mit FOR UPDATE und WHERE CURRENT OF

Um die aktuell geholte Zeile zu aktualisieren, ohne erneut zu suchen:

DECLARE
    CURSOR c_update IS
        SELECT empno, sal FROM emp WHERE deptno = 20
        FOR UPDATE OF sal NOWAIT;  -- Sperrt die Zeilen sofort
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;   -- Aktuell gefetchte Zeile
        END IF;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Gehälter angepasst.');
END;
/

7. Cursor-Variablen (REF CURSOR)

7.1 Definition

Eine Cursor-Variable (REF CURSOR) ist ein Zeiger auf einen Cursor. Im Gegensatz zu statischen Cursorn kann die SQL-Abfrage zur Laufzeit festgelegt werden:

DECLARE
    -- Schwach typisierter REF CURSOR (akzeptiert jede Abfrage)
    TYPE t_cursor IS REF CURSOR;
    cv t_cursor;

    -- Für Ergebniszeilen:
    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 Stark typisierter REF CURSOR

DECLARE
    -- Stark typisierter REF CURSOR (fixer Ergebnistyp)
    TYPE t_emp_cursor IS REF CURSOR RETURN emp%ROWTYPE;
    cv t_emp_cursor;
    v_row emp%ROWTYPE;
BEGIN
    OPEN cv FOR SELECT * FROM emp WHERE job = 'MANAGER';
    LOOP
        FETCH cv INTO v_row;
        EXIT WHEN cv%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_row.ename || ' leitet Abteilung ' || v_row.deptno);
    END LOOP;
    CLOSE cv;
END;
/

7.3 SYS_REFCURSOR

Oracle stellt SYS_REFCURSOR als vordefinierten schwach typisierten REF CURSOR bereit:

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 – Massenverarbeitung

8.1 Motivation

Jedes FETCH erzeugt einen Wechsel zwischen PL/SQL-Engine und SQL-Engine. Bei vielen Zeilen ist das ineffizient. BULK COLLECT liest viele Zeilen auf einmal in eine Collection:

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

    v_namen  t_ename_tab;
    v_gehalter t_sal_tab;
BEGIN
    -- Alle Zeilen auf einmal einlesen:
    SELECT ename, sal
    BULK COLLECT INTO v_namen, v_gehalter
    FROM emp
    WHERE deptno = 20;

    DBMS_OUTPUT.PUT_LINE('Gelesene Zeilen: ' || v_namen.COUNT);
    FOR i IN 1..v_namen.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_namen(i) || ': ' || v_gehalter(i));
    END LOOP;
END;
/

8.2 BULK COLLECT mit LIMIT (Chunking)

Bei sehr großen Tabellen kann mit LIMIT häppchenweise gelesen werden:

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('Gesamtgehalt: ' || v_total);
END;
/

9. FORALL – Massenverarbeitung für DML

9.1 Motivation

FORALL sendet eine DML-Anweisung für alle Elemente einer Collection in einem einzigen Kontextwechsel zur SQL-Engine:

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

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' Zeilen aktualisiert.');
    COMMIT;
END;
/

9.2 FORALL mit SAVE EXCEPTIONS

Bei einem Fehler in einer Zeile kann die Verarbeitung fortgesetzt werden:

DECLARE
    TYPE t_num IS TABLE OF NUMBER;
    v_ids t_num := t_num(7369, 9999, 7521);  -- 9999 existiert nicht
    v_fehler NUMBER;
BEGIN
    FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
        DELETE FROM emp WHERE empno = v_ids(i);

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        v_fehler := SQL%BULK_EXCEPTIONS.COUNT;
        DBMS_OUTPUT.PUT_LINE('Fehler in ' || v_fehler || ' Zeilen:');
        FOR i IN 1..v_fehler LOOP
            DBMS_OUTPUT.PUT_LINE(
                '  Index ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                ': ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)
            );
        END LOOP;
        COMMIT;
END;
/

10. Zusammenfassung und Ausblick

10.1 Cursor-Vergleich

10.2 Häufige Fehler

10.3 Ausblick

Das nächste Kapitel behandelt Exceptions – die strukturierte Fehlerbehandlung in PL/SQL:

• Vordefinierte Exceptions (NO_DATA_FOUND, ZERO_DIVIDE, …)
• Benutzerdefinierte Exceptions
• RAISE_APPLICATION_ERROR
• PRAGMA EXCEPTION_INIT