Tutorium: PL/SQL

Datenbanken & Informationssysteme II
Oracle PL/SQL Tutorium
(Version 3.7 vom 20.3.2017)
• Einleitung
Im vorliegenden Tutorium soll eine Einführung in PL/SQL erfolgen. Als Grundlage muss man sich
von der Kurshomepage die Tabellendefinition sowie einige initiale Datensätze herunterladen.
Das Schema realisiert ein einfaches Bestellungssystem. Es gibt Kunden, Artikel (repräsentieren
gleichzeitig den Lagerbestand), Bestellungen und Nachbestellungen. Eine Bestellung ist einem
Kunden zugeordnet (via Fremdschlüssel kunde_fk). Ob eine Bestellung bereits verschickt wurde kann an der Spalte „verschickt“ abgelesen werden. Ist der Wert der Spalte 0, so ist die Bestellung eingegangen ist der Wert 1 so wurde sie bereits an den Kunden versendet. Der Gesamtwert
einer Bestellung ist in der Spalte gesamtpreis abgelegt. In ihm findet sich die Summe aller
Einzelposten (inkl. MwSt.). Jede Bestellung besteht aus einzelnen Posten (Tabelle Posten), die
fortlaufend numeriert sind (Spalte position) und für jede Bestellung mit Position 1 beginnen.
In einem Posten ist erfasst welcher Artikel (Fremdschlüssel artikel_fk) in welcher Menge (Attribut
anzahl) bestellt wird.
Für einen Artikel (Tabelle Artikel) ist neben der ID und einer textuellen Bezeichnung festgehalten
was der Artikel kostet und welcher Mehrwertsteuersatz gilt (19% oder 7%). Weiterhin ist der aktuelle-, minimale und maximale Lagerbestand für diesen Artikel angegeben.
Lade dir von der Homepage die Quellen zum Tutorium 1 herunter. Wirf einen Blick in die beiden
Dateien, so dass du verstehst was in ihnen passiert. Führe anschließend die Kommandos aus den
Dateien bestellung-ddl.sql und bestellung-stammdaten.sql aus. Dass kannst du beispielsweise von
einem DOS oder Cygwin Fenster aus machen (alternativ kannst du die Statements auch mittels
Copy & Paste im SQLDeveloper ausführen):
sqlplus <dein-account>@oracledbwi @ bestellung-ddl.sql
sqlplus <dein-account>@oracledbwi @ bestellung-stammdaten.sql
Diese Befehle sollten ohne Fehler1 durchlaufen. Schau dir anschließend die Datensätze in den Tabellen Kunde und Artikel an.
1. eventuell dürfen Fehler bei drop-Table Statements auftreten, wenn die Tabellen noch nicht existiert haben
Andreas Schmidt
Oracle PL/SQL-Tutorium 1/14
Datenbanken & Informationssysteme II
Als nächstes soll eine Bestellung von Peter Pipe über zwei „Head Defiance I 160
2006“ Snowboards und ebenfalls zwei Paar der nagelneuen „Red Mens Total Impact
Shorts 2009“ Hosen eingegeben werden. Die Boards haben die Bestellposition eins,
die Hosen Position zwei. Ganz zum Schluss nimmt Peter auch noch ein Exemplar des
„Easy Riding. Snowboard Freestyle Book“ mit.
Ok, legen wir die Bestellung an. Welche Informationen brauchen wir:
Peter Pipe hat die Kunden ID 4, das Snowboard hat die Artikel ID 1, die Hose hat die
Artikel ID 3 und das Buch die ID Nr. 5.
insert into bestellung (id, bestelldatum, kunde_fk)
values(bestellung_seq.nextval, sysdate, 4);
insert into posten
values(1, bestellung_seq.currval, 1,2);
insert into posten
values(2, bestellung_seq.currval, 3,2);
insert into posten
values(3, bestellung_seq.currval, 5,1);
Als letztes müssen wir jetzt noch für die Bestellung den Gesamtpreis errechnen und
in die Spalte gesamtpreis der Tabelle Bestellung eintragen. Das geht beispielsweise so:
1. Erst mal die ID der Bestellung in einer Variablen1 (b_id) speichern und ausgeben:
var b_id number;
begin
select bestellung_seq.currval
into :b_id
from dual;
end;
/
print b_id
1. Hinweis: Das folgende Codefragment benutzt eine SQLPlus Bindevariable (b_id). Die gibt es in älteren
SQL-Developer Versionen (2.x., 3.x) nicht. Wenn ihr mit dem SQLDeveloper Version arbeitet und habt
Probleme mit der Bindungsvariablen, dann lasst euch statt das obige Statement auszuführen die aktuelle
Bestellungs-ID ausgeben und benutzt in den nächsten Statements diesen Wert direkt anstatt :b_id
Andreas Schmidt
Oracle PL/SQL-Tutorium 2/14
Datenbanken & Informationssysteme II
2. Errechnen des Gesamtpreises jedes einzelnen Postens für eine Bestellung:
select (a.preis+a.preis*a.mwst/100)*p.anzahl
from posten p
join artikel a
on a.id=p.artikel_fk
where p.bestellung_fk = :b_id;
bzw. dann entsprechend die Aufsummierung über alle Posten der Bestellung:
select sum((a.preis+a.preis*a.mwst/100)*p.anzahl)
from posten p
join artikel a
on a.id=p.artikel_fk
where p.bestellung_fk = :b_id;
Das Update-Statement für die Berechnung des Gesamtpreises sieht dann wiefolgt aus:
update bestellung b
set
gesamtpreis=(select
sum((a.preis+a.preis *
a.mwst/100)*p.anzahl)
from posten p
join artikel a
on a.id=p.artikel_fk
where p.bestellung_fk = b.id)
where b.id = :b_id;
Ok, wie schauts dann jetzt in der Datenbank aus:
select *
from bestellung b
where b.id = :b_id;
sehr löblich, genau so haben wir uns da s vorgestellt ;-).
Ok, was gefällt uns daran alles nicht ?
1.
wir müssen mitzählen um welchen Posten es sich bei der Bestellung handelt (1,
2, 3, ...).
2.
Wir müssen ein zusätzliches Statement angeben, das die Gesamtsumme der
Bestellung berechnet und ins zugehörige Feld einträgt.
Andreas Schmidt
Oracle PL/SQL-Tutorium 3/14
Datenbanken & Informationssysteme II
In der nächsten Lösung die wir erarbeiten, sollen genau diese beiden Punkte vermieden
werden. Dies geschieht dadurch, dass wir eine Reihe von PL/SQL Prozeduren und Funktionen schreiben, welche das Eintragen der Bestellungen und Posten vornehmen.
Im einzelnen wollen wir folgende Funktionen /Prozeduren implementieren:
• function neue_bestellung(kunde_id number) return number :
Legt eine neue Bestellung für den angegebenen Kunden zum aktuellen Datum an und
gibt die Bestellungs ID zurück.
• procedure neuer_bestellposten(bestellung_id in number,
artikel_id in number,
anzahl in number):
Legt eine neue Position für die durch den Parameter bestellung_id spezifizierte
Bestellung an. Die aktuelle Positionsnummer im Rahmen der Bestellung soll dabei
automatisch ermittelt werden. Weiterhin soll der Gesamtpreis der Bestellung entsprechend Preis, Mehrwertsteuersatz. und Anzahl Artikel modifiziert werden.
• procedure bestellung_verschicken(bestellung_id number):
Einfaches Ding, setzt die Spalte Bestellung.verschickt auf den Wert 1
• procedure show_bestellung(b_id in number).
liefert mittels dbms_output.put_line(...)1 Statements eine übersichtliche
Aufstellung der angegebenen Bestellung. Die Ausgabe soll in etwa wiefolgt aussehen:
Bestellung Nr.20 vom 09.03.09
POS BEZ
E-PREIS ANZ
PREIS
-------------------------------------------------001 Head Defiance I 160 2006
299.95 3
899.85
002 Salomon Prospect LTD 159 2008
003 Easy Riding. Snowboard Freesty
399.95
1
399.95
12.80
5
63.99
-------------------------------------------------Summe:
1,363.79
Status: eingegengen
Als erstes machen wir uns an die Implementierung der Funktion
neue_bestellung(...). Aufgabe dieser Funktion ist die Ermittlung einer neuen
Bestell ID sowie das Eintragen des Datensatzes in die Datenbank. Die ermittelte ID muss
zurückgegeben werden, da wir diese für das nachfolgende Eintragen von Bestellposten
benötigen.
1. damit die Ausgabe sichtbar ist, muss zuvor das Statement set serveroutput on abgesetzt werden.
Andreas Schmidt
Oracle PL/SQL-Tutorium 4/14
Datenbanken & Informationssysteme II
create or replace
function neue_bestellung(kunde_id in number)
return number
as
b_id number;
begin
select bestellung_seq.nextval
into b_id
from dual;
insert into bestellung
values(b_id, kunde_id, sysdate, 0.0, 0);
return b_id;
end;
/
show errors
Nachdem die Funktion (ohne Fehlermeldung) in die Datenbank eingetragen worden
ist, kann sie durch das folgende Testprogramm ausgeführt werden:
declare
bestell_id number;
kunde_id number := 2;
begin
bestell_id := neue_bestellung(kunde_id);
dbms_output.put_line('Neue Bestellung mit ID '||
bestell_id ||' erzeugt');
end;
/
show errors
Die Ausgabe bei Ausführung sollte dann wiefolgt aussehen1:
Andreas Schmidt
Oracle PL/SQL-Tutorium 5/14
Datenbanken & Informationssysteme II
Neue Bestellung mit ID 2 erzeugt
Als nächstes machen wir uns an die Implementierung der Prozedur
neuer_bestellposten(...). Deren Auzfgabe ist die Ermittlung der nächsten Position, dem Eintragen des Postens in die Datenbank sowie der Update der
Spalte gesamtpreis des zughörigen Bestellungs-Datensatzes. Input der Prozedur sind die Bestell-ID, die Artikel ID sowie die Anzahl der zu bestellenden Artikel.
Die Ermittlung der nächsten Position erfolgt durch ein SQL-Statement, das die
maximale Positionsnummer + 1 für diese Bestellung zurückliefert:
select max(position) + 1
from posten
where bestellung_fk = ...
Eingebaut in die Definition einer PL/SQL Routine sieht das Statement dann wiefolgt aus:
create or replace
procedure neuer_bestellposten(bestellung_id number,
artikel_id number,
anzahl number)
as
a_position number;
begin
select max(position) + 1
into a_position
from posten
where bestellung_fk=bestellung_id;
end;
/
a_position ist dabei eine Variable, in die das Ergebnis der Anfrage eingetragen wird.
1. Die Nummer der ID darf selbstverständlich abweichen ;-) - Falls du nichts siehst, hast du wahrscheinlich vergessen die Ausgabe euinzuschalten (siehe Folie Bildschirmausgabe, Foliensatz zu PL/SQL).
Andreas Schmidt
Oracle PL/SQL-Tutorium 6/14
Datenbanken & Informationssysteme II
Da das SQL-Statement immer genau einen Datensatz zurückliefert kann hier mit
der select ... into ... Zuweisung gearbeitet werden.
Es müssen jedoch noch zwei mögliche Fälle unterschieden werden:
1.
Es gibt noch keinen einzigen Posten für diese Bestellung - in diesem
Fall liefert das SQL-Statement NULL zurück.
2.
Es gibt mindestens einen Posten - in diesem Fall wird die nächste
Position zurückgeliefert.
Diesem Umstand wird insofern Rechnung getragen, dass als nächstes überprüft
wird, ob NULL zurückgeliefert wurde. In diesem Fall setzen wir die Variable
a_position explizit auf 1:
if a_position is null then
a_position := 1;
end if;
So jetzt haben wir alle Informationen für den Posten-Datensatz zusammen und
können ihn eintragen:
insert into posten (position, bestellung_fk,
artikel_fk, anzahl)
values (a_position, bestellung_id,
artikel_id, anzahl);
Zum Abschluss muss noch der Betrag in der Spalte gesamtpreis (Tabelle Bestellung) angepasst werden. Dazu benötigen wir die Information zu Preis und
MwSt. aus dem betreffenden Artikeldatensatz und multipliziern ihn mit der Anzahl der zu bestellenden Artikel für diesen Posten. Das Ergebnis schreiben wir
dann ebenfalls mittels der select ... into... Zuweisung in eine temporäre Variable (postenpreis - muss wie die Variable a_position im
Header der Routine (vor dem begin-Statement) definiert werden.):
select
into
from
where
(preis + preis * mwst/100) * anzahl
postenpreis
artikel
id=artikel_id;
Ok, damit sind wir auch fast fertig. Wir haben in der Variable postenpreis
den entsprechenden Betrag für diesen Posten stehen und müssen diesen nur noch
zum bisherigen Gesamtpreis der entsprechenden Bestellung hinzuaddieren.
update bestellung
set gesamtpreis = gesamtpreis + postenpreis
where id = bestellung_id;
Andreas Schmidt
Oracle PL/SQL-Tutorium 7/14
Datenbanken & Informationssysteme II
Die komplette Routine sieht demnach so aus:
create or replace
procedure neuer_bestellposten(bestellung_id in number,
artikel_id in number,
anzahl in number)
as
a_position number;
postenpreis number;
begin
select max(position) + 1
into a_position
from posten
where bestellung_fk=bestellung_id;
if a_position is null then
a_position := 1;
end if;
insert into posten (position, bestellung_fk,
artikel_fk, anzahl)
values (a_position,bestellung_id,artikel_id,anzahl);
select (preis+preis *mwst/100) * anzahl
into postenpreis
from artikel
where id=artikel_id;
update bestellung
set gesamtpreis = gesamtpreis + postenpreis
where id = bestellung_id;
end;
/
Nachdem wir jetzt die beiden Routinen erstellt haben, können wir mal das folgende
kleine Testprogramm ausführen:
Andreas Schmidt
Oracle PL/SQL-Tutorium 8/14
Datenbanken & Informationssysteme II
declare
bestell_id number;
kunde_id number := 1;
begin
bestell_id := neue_bestellung(kunde_id);
neuer_bestellposten(bestell_id , 1, 3);
neuer_bestellposten(bestell_id , 2, 1);
neuer_bestellposten(bestell_id , 5, 5);
end;
/
show error
Zur Kontrolle schauen wir uns mittels den beiden folgenden Statements die Einträge in den beiden Tabellen Bestellung und Posten an:
select *
from Bestellung;
select *
from Posten;
Als nächstes werden wir die Hilfroutine show_bestellung(...) implementieren. Auf Seite 4 ist das gewünschte Ausgabeformat dargestellt. Eingabeparameter ist die ID der Bestellung die ausgegeben werden soll. Die erste Zeile der
Ausgabe enthält Informationen aus der Tabelle Bestellung. Da wir die ID der
Bestellung haben ist es für uns kein Problem mittels einer select ... into
... Zuweisung die Informationen einer Variablen (best) zuzuweisen. Die
Struktur der Variablen entspricht der eines Datensatzes in der Tabelle Bestellung
(Bestellung%rowtype). Der Zugriff auf die einzelnen Felder geschieht
dann mit der Punkt-Notation ( z.B. best.bestelldatum).
create or replace
procedure show_bestellung(b_id in number)
as
best bestellung%rowtype;
begin
select *
Andreas Schmidt
Oracle PL/SQL-Tutorium 9/14
Datenbanken & Informationssysteme II
into best
from bestellung
where id=b_id;
dbms_output.put_line('Bestellung Nr.'||b_id||
' vom '||best.bestelldatum);
dbms_output.put_line('--------- ... -------');
end;
/
Anschließend müssen wir auf alle Postendatensätze mit der entsprechenden Bestellungs-ID (Fremdschlüssel bestellung_fk) zugreifen. Dies wird durch einen Cursor implementiert. Ein Cursor ist eine Variable vom Typ Cursor, die mit
einem select-Statement verknüpft ist. Die Deklaration eines Cursors erfolgt analog zu den übrigen Variablen im Kopf der Routine:
cursor posten_cur is select *
from posten p
join artikel a
on p.artikel_fk=a.id
where bestellung_fk=b_id
order by position;
Anschließend kann im Rumpf der Routine mittels des Cursors auf die dem SQLStatement zugrundeliegenden Datensätze zugegriffen werden:
for p in posten_cur loop
dbms_output.put_line(to_char(p.position,'009')||
' ' ||rpad(p.bezeichnung,30)||' '
||to_char(p.preis+p.preis*p.mwst/100
,'9,9999.99')||'
'||lpad(p.anzahl,4)||'
'
||to_char((p.preis+p.preis*p.mwst/100)*p.anzahl,
'9,9999.99'));
end loop;
Die rpad(), lpad() und to_char() Funktionsaufrufe sind hierbei für die
Ausgabeformatierung zuständig (wirf einen Blick in die Oracle Online-Dokumentation um ihre Funktionsweise nachzuschlagen).
Zum Schluss soll jetzt noch der Gesamtpreis sowie der Satus der Bestellung ausgegeben werden. Dies geschieht durch folgende Codefragmente (die Variable
status muss zuvor ebenfalls noch im Kopf der Routine deklariert werden):
Andreas Schmidt
Oracle PL/SQL-Tutorium 10/14
Datenbanken & Informationssysteme II
if best.verschickt=1 then
status := 'versandt';
else
status := 'eingegengen';
end if;
dbms_output.put_line('------ ... -------');
dbms_output.put_line('Summe: '||
lpad(to_char(best.gesamtpreis,'9,999.99'), 60));
dbms_output.put_line('Status: '||status);
So das wars. Jetzt solltest du in PL/SQL so fit sein, dass du dich ans erste Übungsblatt machen kannst (vorausgesetzt du weisst schon was Trigger sind).
Zum Abschluss und zur Überleitung aufs erste Übungsblatt jetzt noch die alternative
Realisierung der Prozedur neuer_bestellposten(...) als Trigger: Zweck
der Prozedur war es, automatisch die nächste Positionsnummer für einen Posten zu
ermitteln. Dazu wurde innerhalb der Prozedur die für diese Bestellung größte vergebene Positionssnummer ermittelt und anschließend die um eins erhöhte Positionsnummer, zusammmen mit den anderen Werten (artikel_fk,
bestellung_fk und anzahl) in Tabelle posten eingetragen.
Ok, was müssen wir tun, wenn wir uns nicht um die Position eines Postens kümmern
wollen? - 1. Versuch, wir lassen sie einfach weg ;-)
insert into posten(bestellung_fk, artikel_fk, anzahl)
values(bestellung_seq.currval1, 3, 1);
Dieses Vorgehen scheitert, da das Feld position als NOT NULL gekennzeichnet
ist. Was wir aber machen können ist, innerhalb eines Triggers, den Wert zu berechnen und zu setzen. Das Setzen des Wertes muss vor dem eigentlichen Eintrag erfolgen (BEFORE INSERT), denn sonst rennen wir wieder gegen das NOT NULL
Constraint. Da wir auf einen Wert eines Datensatzes zugreifen wollen (wir setzen
einen Wert), muss es sich weiterhin um einen Row-Trigger handeln.
Hier mal eine triviale Implementierung, welche hardcodiert die Position auf den
Wert 1 setzt:
create or replace TRIGGER set_position
before insert on posten
for each row
1. bzw. ein gültiger Primärschlüsselwert für eine Bestellung
Andreas Schmidt
Oracle PL/SQL-Tutorium 11/14
Datenbanken & Informationssysteme II
begin
dbms_output.put_line('START TRIGGER set_position');
:new.position := 1;
dbms_output.put_line('END TRIGGER set_position');
end;
/
show errors
Wenn wir jetzt noch einmal das obige insert-Statement absetzen1, bekommen wir
keine Fehlermeldung mehr (zumindest keine was das NOT NULL Constraint betrifft) und der Datensatz wird eingetragen, allerdings hat jeder Posten die
Position 1. Das ist natürlich auch quatsch und kann einfach verhindert werden.
Formuliere dazu ein Constraint, so dass es nicht vorkommen kann, dass zwei Posten einer Bestellung den selben Wert haben. Überprüfe anschließend, indem du
weitere Posten eingibst, ob dein neu formuliertes Constraint funktioniert. Ok, und
jetzt ist es ein auch nur noch ein kleiner Schritt die Implementierung des Triggers
so abzuändern, dass innerhalb des Triggers die korrekte Positionsnummer gesetzt
wird (wirf einfach einen Blick in die Implementierung der Prozedur
neuer_bestellposten(...) ).
Hinweis: Beachte, dass auf der Tabelle posten bereits ein Constraint formuliert
ist, das überprüft, dass keine zwei Posten sich auf den selben Artikel beziehen (das
wird über die Anzahl gemacht).
• Anhang A:
create table kunde (
id number primary key,
vorname varchar2(20) not null,
nachname varchar2(20) not null
);
create table bestellung (
id number primary key,
kunde_fk number not null references kunde(id),
bestelldatum date,
gesamtpreis number(7,2) default 0,
verschickt number(1) default 0 not null
1. um zu sehen, dass der Trigger auch aufgerufen wird, bitte die Ausgabe einschalten
Andreas Schmidt
Oracle PL/SQL-Tutorium 12/14
Datenbanken & Informationssysteme II
);
create table artikel (
id number primary key,
bezeichnung varchar2(80) not null,
preis number(7,2) not null,
mwst number(3) not null,
lager_ist_bestand number(3) not null,
lager_min_bestand number(3) not null,
lager_max_bestand number(3) not null
);
create table posten (
position number check (position > 0) not null,
bestellung_fk number not null
references bestellung (id)
on delete cascade,
artikel_fk number not null
references artikel(id),
anzahl number check (anzahl > 0),
constraint CONS_KEINE_GLEICHEN_ARTIKEL
unique(bestellung_fk, artikel_fk)
);
create table nach_bestellung (
id number primary key,
datum date default sysdate not null,
artikel_fk number not null
references artikel(id) on delete cascade,
anzahl number
);
• Quellen:
Kurshompage: http://www.smiffy.de/DB-IS-II
• Hinweise:
Andreas Schmidt
Oracle PL/SQL-Tutorium 13/14
Datenbanken & Informationssysteme II
• Der Oracle Server befindet sich auf dem Rechner mit dem Namen
iwi-w-vm-dbo.hs-karlsruhe.de, Port 1521.
• Die Datenbankinstanz ist ORACLEDBWI
• Der SQLDeveloper beitet einen Debugger. Details dazu:
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/
devdays2012/mod2_sqldev/mod2_sqldev.html
Andreas Schmidt
Oracle PL/SQL-Tutorium 14/14