PL/SQL: Cursor
Aus Wikibooks
[Bearbeiten] Implizierter cursor
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;
[Bearbeiten] Explizierter cursor
Beispiel 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;
[Bearbeiten] 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.
