Prinzip
Da PL/SQL die prozedurale Erweiterung von SQL darstellt, können in PL/SQL fast alle
Möglichkeiten von SQL benutzt werden. Alle SQL- DML - Befehle (select, insert, update, delete) sind direkt
in PL/SQL verfügbar. Dabei dienen PL/SQL - Variablen dem Datenaustausch zwischen SQL und PL/SQL. Weiterhin
ist die Verwendung der Befehle zur Transaktionssteuerung (commit, rollback ...) möglich.
DDL - Befehle müssen über dynamisches SQL durch Verwendung der Standard Package
DBMS_SQL realisiert werden.
Alle SELECT-Befehle müssen eine INTO-Klausel enthalten, die definiert, welche Variablen
das Ergebnis aufnehmen sollen.
Weitere Details sind auch in dem Buch Oracle
PL/SQL Programmierung zu finden.
Beispiel:
declare
v_bezeichnung einheit.BEZEICHNUNG%TYPE;
begin
select bezeichnung into v_bezeichnung
from einheit
where einheit_kurz = 'm';
...
insert into einheit (einheit_kurz, bezeichnung)
values ('l', 'Liter');
...
update auftrag set
hinweis = hinweis || 'Neuer Artikel ' || v_bezeichnung;
end;
Cursor
Definition
Cursor sind Datenstrukturen im Arbeitsspeicher, die für die Abarbeitung von SQL - Befehlen
benötigt werden. Für jeden SQL - Befehl wird automatisch ein entsprechender Speicherbereich allokiert
(impliziter Cursor). Für SELECT - Befehle, die mehrere Zeilen liefern, muß ein expliziter Cursor verwendet
werden.
Beispiel:
declare
v_summe number :=
0;
v_pos_preis number;
cursor c_auftrag_pos is
select anzahl*preis from auftrag_pos;
begin
OPEN c_auftrag_pos;
loop
FETCH c_auftrag_pos into v_pos_preis;
exit when c_auftrag_pos%NOTFOUND;
v_summe := v_summe + v_pos_preis;
end loop;
CLOSE c_auftrag_pos;
end;
Cursor - Parameter
Beispiel:
declare
v_summe number := 0;
v_pos_preis number;
cursor c_auftrag_pos
(p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
select anzahl*preis
from auftrag_pos
where auftrag_nr = p_auftrag_nr;
begin
OPEN c_auftrag_pos (4711);
loop
FETCH c_auftrag_pos into v_pos_preis;
exit when c_auftrag_pos%NOTFOUND;
v_summe := v_summe + v_pos_preis;
end loop;
CLOSE c_auftrag_pos;
end;
Cursor - Strukturvariablen
Beispiel:
declare
v_summe number
:= 0;
v_auftrag_pos auftrag_pos%ROWTYPE;
cursor c_auftrag_pos
(p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
select *
from auftrag_pos
where auftrag_nr = p_auftrag_nr;
begin
OPEN c_auftrag_pos (4711);
loop
FETCH c_auftrag_pos into v_auftrag_pos;
exit when c_auftrag_pos%NOTFOUND;
v_summe := v_summe + v_auftrag_pos.anzahl *
v_auftrag_pos.preis;
end loop;
CLOSE c_auftrag_pos;
end;
Cursor - Attribute
Jeder Cursor hat vier Statusattribute, auf die mit cursor%ATTRIBUT zugegriffen wird.
Bei impliziten Cursorn erfolgt der Zugriff mit SQL%ATTRIBUT. %OPEN steht bei impliziten Cursorn
nicht zur Verfügung.
Um den aktuellen Datensatz eines expliziten Cursors mit einem UPDATE-Befehl zu ändern,
kann man die CURRENT OF - Klausel in der WHERE-Bedingung verwenden. Beispiel siehe unten.
%FOUND
- Gibt an, ob der letzte FETCH - Befehl einen Satz gefunden hat => TRUE
- Vor dem ersten Fetch NULL
%NOTFOUND
- Gibt an, ob der letzte FETCH - Befehl einen Satz gefunden hat => FALSE
- Vor dem ersten Fetch NULL
%ROWCOUNT
- Liefert die Anzahl der mit FETCH gelesenen Zeilen
- Vor dem ersten FETCH auf 0
%ISOPEN
- Gibt an, ob ein Cursor geöffnet ist
CURRENT OF
declare
v_auftrag auftrag%ROWTYPE;
cursor c_auftrag
(p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
select *
from auftrag
where auftrag_nr = p_auftrag_nr;
for update of status;
begin
OPEN c_auftrag (4711);
loop
FETCH c_auftrag into v_auftrag;
exit when c_auftrag_pos%NOTFOUND;
if v_auftrag.status = 'A' then
bearbeite_auftrag (v_auftrag);
update auftrag set status = 'E'
where current of c_auftrag;
end if;
end loop;
CLOSE c_auftrag;
end;
Cursor - FOR - Schleifen
Durch eine Cursor - FOR - Schleife wird ein Cursor automatisch geöffnet, alle Sätze
der Ergebnismenge des zugehörigen SELECT - Befehls gelesen und der Cursor wieder geschlossen. Die Befehle
OPEN, FETCH und CLOSE sind dann nicht notwendig.
Die Schleifenvariable hat den Recordtyp des Cursors.
Beispiel 1:
declare
v_summe number := 0;
cursor c_auftrag_pos
(p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
select *
from auftrag_pos
where auftrag_nr = p_auftrag_nr;
begin
for v_auftrag_pos in c_auftrag_pos(4711) loop
v_summe := v_summe + v_auftrag_pos.anzahl *
v_auftrag_pos.preis;
end loop;
end;
Beispiel 2:
declare
v_summe number := 0;
begin
for v_auftrag_pos in (select * from auftrag_pos) loop
v_summe := v_summe + v_auftrag_pos.anzahl *
v_auftrag_pos.preis;
end loop;
end;
|
