PL-SQL: Cursor

Das Buch PL-SQL wird zurzeit überarbeitet. Änderungen an dieser Seite bitte nur in Absprache mit mir.

Cursor sind Zeiger

Cursor ist englisch und bedeutet so viel wie "Zeiger". Im allgemeinen Gebrauch mit dem Computer hat das Wort vor allem zwei Bedeutungen: Einerseits bezeichnet man den Mauszeiger als Cursor und zum anderen den Strich, welcher die Eingabestelle bei Eingebeprogrammen (überall, wo man etwas schreiben kann) markiert.

Im Zusammenhang mit PL/SQL hat er noch eine dritte Bedeutung. Laut Wikipedia steht der Begriff hier eventuell als Abkürzung für Current Set of Records. Das bedeutet, er ist ein temporärer Bereich im Arbeitsspeicher, welcher Informationen über eine spezielle PL/SQL-Anweisung und ihre Ausgabe-Daten speichert und diese auch manipulieren kann. (Als Anweisung bezeichnet man einen Bereich zwischen zwei Semikola.) Wird ein Cursor in der Anweisung wieder geschlossen, gehen auch die in ihm enthaltenen Daten verloren.

In PL/SQL unterscheidet man, je nach Unterscheidungkriterium, insgesamt vier verschiedene Cursor: Das sind einerseits die statischen bzw. dynamischen und andererseits die expliziten bzw. impliziten Cursor. Die Anweisungen eines dynamischen Cursors werden erst zur Laufzeit festgelegt und können für jeden gültigen SQL-Anweisungstyp genutzt werden. Implementiert werden sie über EXECUTE IMMEDIATE-Anweisungen. Ein statischer Cursor hingegen wird bereits zur Kompilierzeit festgelegt und nur für DML-Anweisungen (also SELECT, INSERT, UPDATE, DELETE, MERGE oder SELECT FOR UPDATE) verwendet. Auftauchen können sie in zweierlei Formen: als explizit deklarierte oder implizit eingebettete Cursor.

Implizierter Cursor

Implizite Cursor werden weder deklariert noch per Befehl geöffnet, bearbeitet oder geschlossen. Sie tauchen erst an der Stelle im Ausführungsteil auf, an der sie benötigt werden, und werden danach wieder geschlossen, wodurch die in ihnen enthaltenen Daten wieder gelöscht werden.

Hier ein Beispiel eines impliziten Cursors, mit dem ein einziger Datensatz gelesen werden kann:

Beispiel in einem anonymen Block:

declare
    v_user_id users.id%TYPE;
    v_username users.username%type;
begin
    -- implicit cursor
    SELECT id, username
    INTO v_user_id, v_username
    FROM users
    WHERE username='test';
    
    dbms_output.PUT_LINE('----------------------------');
    dbms_output.PUT_LINE('User-ID: '||v_user_id);
    dbms_output.PUT_LINE('Username: '||v_username);
    dbms_output.PUT_LINE('----------------------------');
end;

Wenn man dabei ein SELECT-Statement angibt, das mehr als einen Satz als Ergebnis hat, wird eine Fehlermeldung ausgegeben, da nicht klar ist, welcher der gefundenen Datensätze in die Variable geschrieben werden soll.

Für SELECT-Statements, die mehr als einen Ergebnis-Satz liefern, kann die folgende Konstruktion verwendet werden:

begin
    -- implicit cursor mit FOR
    FOR rec_users in ( SELECT id, username, firstname, lastname FROM users ) LOOP
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Expliziter Cursor

Explizite Cursor sind, wie der Name bereits erahnen lässt, im Deklarationsabschnitt deklariert. Dies kann ohne Parameter, mit Argumenten als Parameterliste oder auch mit einer RETURN-Klausel geschehen. Im Ausführungs- bzw. Exceptionabschnitt operiert man mit ihnen mithilfe der Befehle OPEN, FETCH und CLOSE zum Öffnen, Auslesen und Schließen. Dabei sind explizite Cursor die einzigen, die auch mehrzeilige Ausgaben machen können.

Hier ein Beispiel dazu in einem anonymen Block:

declare  
    CURSOR cur_users IS
        SELECT id, username, firstname, lastname
        FROM users;
    rec_users cur_users%ROWTYPE;
begin
    -- explicit cursor
    OPEN cur_users;
    LOOP
        FETCH cur_users INTO rec_users;
        EXIT WHEN cur_users%NOTFOUND;
    
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Statusvariablen für Cursor

In einer Cursor-FOR-Schleife kann man die Anzahl der bereits gelesenen Sätze mit der Statusvariablen ROWCOUNT abfragen. Diese Variable ist jedoch nur im Inneren der Schleife gültig. Wenn man sie außerhalb der Schleife verwendet, dann wird ein Syntaxfehler ausgegeben.

Beispiel:

SET SERVEROUTPUT ON
DECLARE
CURSOR c IS
SELECT table_name 
FROM all_tables
WHERE ROWNUM <= 10
;
BEGIN
dbms_output.put_line('start');
-- gibt Fehler:
-- dbms_output.put_line('vor for i cursor rowcount=' || to_char(c%rowcount));
FOR i IN c LOOP
   dbms_output.put_line('cursor rowcount=' || to_char(c%rowcount));
END LOOP;
-- gibt Fehler:
-- dbms_output.put_line('nach end loop cursor rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
END;
/
SHOW ERRORS

Das Programm hat folgende Ausgabe:

start
cursor rowcount=1
cursor rowcount=2
cursor rowcount=3
cursor rowcount=4
cursor rowcount=5
cursor rowcount=6
cursor rowcount=7
cursor rowcount=8
cursor rowcount=9
cursor rowcount=10
fertig
PL/SQL procedure successfully completed.
No errors.

Rowcount bei einem Cursor ausgeben

Die auskommentierten Zeilen ergeben eine Fehlermeldung, wenn sie ausgeführt werden sollen.

set serveroutput on
declare
cursor c is
select table_name 
from all_tables
where rownum <= 1
;
name varchar2(100);
begin
dbms_output.put_line('start');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
open c;
dbms_output.put_line('open c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
close c;
dbms_output.put_line('close c');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
end;
/
show errors

Das Programm hat folgende Ausgabe:

start
c%isopen=FALSE
open c
c%found=FALSE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=0
fetch c
c%found=TRUE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
close c
c%isopen=FALSE
fertig
PL/SQL procedure successfully completed.
No errors.