PL/SQL - fbi.h

Prof. Dr. Stephan Karczewski
Datenbanken
Praktikum 3 (PL/SQL)
Vorbereitungen zum 3. Praktikum (PL/SQL):
SQL ist eine nicht-prozedurale Sprache der 4. Generation, die es nicht erlaubt, Elemente von
3.-Generation-Sprachen (Schleifen, Verzweigungen, etc.) zu benutzen.
Daher gibt es in professionellen DB-Systemen Spracherweiterungen, die bei Oracle PL/SQL
(Programming Language) genannt wird. In PL/SQL können Schleifen, Verzweigungen,
Prozeduren, Funktionen, globale und lokale Variablen benutzt werden. SQL-Befehle sind in
diesen prozeduralen Programmen eingebettet.
Im Praktikum werden wir PL/SQL mit dem Cursor-Konzept anwenden.
1. Anleitung zu PL/SQL
1.1 Aufbau eines PL/SQL-Blocks
PL/SQL ist blockorientiert. Der PL/SQL-Block beginnt (1)mit der Deklaration von
Variablen, eingeleitet durch das Schlüsselwort DECLARE. Der ausführbare Teil (2)
des Codes startet mit BEGIN. Mit der EXCEPTION können Ausnahmesituationen (3)
behandelt werden. Der erste und dritte Teil können fehlen (Wir werden in diesem
Praktikum Teil 3 weglassen.).
Ein Block wird als Skript in einer Datei abgelegt und mit START in SQLPLUS
aufgerufen werden (letzte Zeile: "/"). Jede Anweisung endet mit ";".Groß- und
Kleinschreibung ist irrelevant.
PL/SQL-Block:
DECLARE
Datendeklaration
BEGIN
SQL- und PL/SQL-Anweisungen
EXCEPTION
Behandlung von Ausnahmen
END;
/
1.2 Datendeklaration
Jede Variable, die in einem PL/SQL-Block benutzt wird, muss vorher deklariert
werden. Es gibt die Typen CHAR, VARCHAR, NUMBER, DATE, ROWID und BOOLEAN.
Beispiel:
DECLARE
v1 char(1);
n number;
s boolean;
Seite 1
Prof. Dr. Stephan Karczewski
Datenbanken
Praktikum 3 (PL/SQL)
Diese Variablen haben den Anfangswert NULL, wenn sie nicht initialisiert werden.
Beispiele:
v2 char(5) := 'ABCDE'; -- Textvariable
v3 number(6,2) not null := 0; -- darf niemals NULL sein
v4 boolean := TRUE;
v6 number(100,2) := v3 + 100;
Die Namen von vorhandenen Spalten von Tabellen, die in select- oder updateBefehlen innerhalb der Programme verwendet werden, müssen nicht deklariert
werden. Mit Hilfe des Zusatzes "%TYPE" kann man Variablen deklarieren, die den
Datentyp der Spalte einer Tabelle haben, z.B.:
V_Stdg
Vorlesungen.Studiengang%type
deklariert die Variable V_Stdg, die den Typ des Attributes Studiengang der Tabelle
Vorlesungen erthält.
1.3 Geschachtelte PL/SQL-Blöcke
Schachtelungen sind nur im Ausführungs- und Ausnahmebehandlungsteil möglich.
Eine Variable ist lokal bzgl. eines Blocks, wenn sie in ihm deklariert ist. Eine Variable
ist global bezüglich eines Blocks (und somit in ihm auch gültig), wenn sie nicht in ihn,
sondern in einem übergeordneten Block deklariert ist.
1.4 Kontrollstrukturen und Operatoren
1.1. Anweisungen und Zuweisungen
Leere Anweisung
null;
Zuweisungsoperator :=
(Beispiel: a := 70; )
Zuweisung mittels select: Werte über Spaltennamen aus der Datenbank werden
in Variablen eines PL/SQL-Blocks mit Hilfe des Schlüsselwortes INTO
übertragen.
Beispiel: SELECT COUNT(*) INTO anzahl FROM ARTIKEL;
1.2. Arithmetische Operatoren
+ Addition, - Subtraktion, * Multiplikation, / Division
Beispiel:
Endpreis := Preis * Steuersatz;
Bonus := AktuellerWert + 0.10;
1.3. Vergleichsoperatoren und logische Operatoren
= Gleichheit, != oder <> Ungleichheit, <=, >=, <, > größer/kleiner (oder gleich)
AND Konjunktion, OR Disjunktion, NOT Negation
Beispiel:
IF Konto <= kontoMinimum THEN UPDATE ...
Seite 2
Prof. Dr. Stephan Karczewski
Datenbanken
Praktikum 3 (PL/SQL)
IF Konto > 4000 AND Wert <= 7000 THEN UPDATE ...
1.4. Verzweigung
if Bedingung
then Anweisungsfolge A
else Anweisungsfolge B
end if;
Der else-Teil kann entfallen; "end if" ist Pflicht.
Beispiel:
IF someCursor%notfound -- Cursor liefert keine weiteren Elemente?
-- %notfound: Siehe 1.4 unter 1.5 Cursor-Konzept
THEN INSERT INTO someTable VALUES(value1, value2);
END IF;
1.5. Schleifen
Es gibt drei Formen von Schleifen:
1. bedingungslos:
2. Zählschleife:
LOOP
[Anweisungsblock]
END LOOP;
FOR Zaehlbereich LOOP
[Anweisungsblock]
END LOOP;
Beispiel: FOR i IN 1..3 LOOP
[Anweisungsblock]
END LOOP;
-- i läuft von 1 bis 3
-- Block dreimal ausführen
3. bedingte Schleife: WHILE Bedingung LOOP
[Anweisungsblock]
END LOOP;
Beispiel: WHILE someVariable <= someLimit
[Anweisungsblock]
END LOOP;
Die bedingungslose Schleife ist eine unendliche Schleife, wenn in ihr keine
Anweisung (und kein exit) vorkommt. Die Schleife wird mit der exit-Anweisung
Seite 3
Prof. Dr. Stephan Karczewski
Datenbanken
Praktikum 3 (PL/SQL)
verlassen. Darüber hinaus ist es möglich mit GOTO und Marken (<<marke>>) zu
arbeiten, um gezielte Sprünge zu machen. Mit der Anweisung "exit when
Bedingung" kann man direkt an das exit eine Bedingung knüpfen.
1.5 Cursor-Konzept
1.1. Im Allgemeinen liefert der select-Befehl eine Menge von Zeilen. Um solche
Mengen der Reihe nach abarbeiten zu können, gibt es in PL/SQL das CursorKonzept. Ein Cursor wird deklariert, eröffnet, gelesen und geschlossen. Bei der
Deklaration eines Cursors wird eine Teilmenge einer Tabelle oder eine
Verknüpfung mehrerer Tabellen definiert. Der Cursor zeigt auf genau einen Wert
(eine Zeile, ein Tupel) der Ergebnismenge und wird nach bestimmten Regeln auf
einen weiteren Wert geschaltet.
1.2. Deklaration eines expliziten Cursors
Im Deklarationsteil eines PL/SQL-Blocks wird ein Cursor definiert. Der Cursor
erhält dabei einen Namen und die Definition über einen select-Befehl, der
einfach sein kann oder über joins mit where-Bedingungen und order byKlauseln definiert werden kann.
Beispiele:
DECLARE
cursor c1 is select * from artikel;
cursor c2 is select preis, name from artikel where art_id = &1;
Cursor c1 liefert alle Zeilen mit allen Spalten der Tabelle artikel,
c2 liefert nur explizit angegebene Spalten der Artikel unter einer
benutzereingegebenen Bedingung.
1.3. Sequentielles Lesen eines Cursors
Die Anweisungen open, fetch und close erlauben das sequentielle Lesen eines
wie oben definierten Cursors.
Der FETCH-Befehl liest Daten in Variablen und setzt den Cursor auf den
nächsten (nach der Definition) relevanten Datensatz.
Beispiel: FETCH c1 INTO var1, var2, ...;
c1 ist der Name eines Cursors und var1, var2, ... sind Namen von Variablen, die
im DECLARE-Teil des PL/SQL-Blocks definiert sein müssen und in Anzahl,
Datentyp und Reihenfolge den Spalten des Cursor-Deklaration entsprechen
müssen.
Beispiel:
create table bericht
(
s1 char(15);
Seite 4
Prof. Dr. Stephan Karczewski
Datenbanken
Praktikum 3 (PL/SQL)
s2 varchar(20);
s3 number(9,2)
);
/
Diese Tabelle muss vor dem folgenden PL/SQL-Block definiert sein.
DECLARE
cursor c1 is select ISBN, TITEL, PREIS from BUCH;
v_isbn
BUCH.ISBN%type;
v_titel
BUCH.TITEL%type;
v_preis
BUCH.PREIS%type;
anzahl number := 0;
-- Anzahl der gefunden Buecher
schranke
number := &Schranke; -- Eingabe des Benutzers
begin
open c1;
fetch c1 into v__isbn, v_titel, v_preis;
while c1%found
loop
if v_preis > schranke then
anzahl := anzahl + 1;
insert into bericht values (v_isbn, v_titel, v_preis);
end if;
fetch c1 into v__isbn, v_titel, v_preis;
end loop;
close c1;
end;
/
1.4. Attribute zur Kontrolle des Cursors (am Beispiel von c1)
c1%isopen
true, wenn Cursor geöffnet ist, sonst false
c1%found
true, wenn fetch-Befehl auf einen Datensatz zeigt, sonst false
c1%notfound true, wenn fetch-Befehl auf keinen Datensatz zeigt (am Ende)
c1%rowcount gibt an, wie viel Zeilen mit dem open-Befehl geladen wurden
1.5. Strukturvariable
Eine Strukturvariable hat denselben Aufbau wie eine Zeile einer Tabelle oder
eines Cursors. Man verwendet für eine Strukturvariable den Namenszusatz
%rowtype.
1.6. Beispiel:
declare
cursor aktbuch is ...;
Seite 5
Prof. Dr. Stephan Karczewski
BuchRec
Datenbanken
Praktikum 3 (PL/SQL)
aktbuch%rowtype;
begin
loop
fetch aktbuch into BuchRec;
exit when AktBuch%notfound;
...
end loop;
end;
1.7. Update mit einem Cursor
Bei der Vereinbarung eines Cursors kann im SELECT-Befehl die Klausel FOR
UPDATE angegeben werden. Sie erlaubt, die ausgewählten Datensätze zu
verändern. In diesem Fall wird die Ergebnismenge des Cursors gesperrt, andere
Benutzer können nicht darauf zugreifen, solange die Sätze aktualisiert werden.
Bei update-Befehl muss die Klausel "where current of c1" (bei Cursor c1)
verwendet werden. Beispiel:
DECLARE
cursor c1 is select gebiet from fachbuch
for update of gebiet;
satz c1%rowtype;
begin
for satz in c1
loop
update fachbuch set gebiet = 'EDV'
where current of c1; -- aktuellen Datensatz des Cursors
end loop;
-- ändern
end;
/
1.6 Bildschirmausgabe mittels Package dbms_output
dbms_output ist ein Prozeduren-Standardpaket von Oracle, mit denen u.a. Ausgaben
am Bildschirm ausgeführt werden können. Wichtig ist, dass die Umgebungsvariable
serveroutput eingeschaltet ist (s. nächster Abschnitt).
Beispiel:
set serveroutput on;
declare
wert NUMBER(9);
begin
select count(*) into wert from lieferant;
dbms_output.put_line('Wert ist: ' || wert);
Seite 6
Prof. Dr. Stephan Karczewski
Datenbanken
Praktikum 3 (PL/SQL)
end;
/
Mit dem Verknüpfungszeichen || (pipelining) kann ein Variablenwert z.B. an einen
Textstring angehangen werden.
2. SQL/PLUS Script-Definitionen
&-Parameter: Bei Aufruf ohne Parameter wird bei jedem &-Parameter-Vorkommen der
Benutzer nach dem aktuellen Wert gefragt.
Kommentare: /* ... */
für länger Kommentare (über mehrere Zeilen)
-Kommentar bis zum Ende der Zeile
Umgebungsvariablen anzeigen lassen: show all
Umgebungsvariablen setzen (am Beispiel): set serveroutput on.
Seite 7