Document

Prof. Dr. Stephan Karczewski
Datenbanken 2
Aufgabe 3
Vorbereitungen zum 3. Praktikum:
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, bei Oracle PL/SQL
(Programming Language) genannt wird. In PL/SQL können Schleifen, Verzweigungen,
Prozduren, Funktionen, globale und lokale Variablen benutzt werden. SQL-Befehle sind in
diesen prozeduralen Programmen eingebettet.
In dem Praktikum werden PL/SQL mit dem Cursor-Konzept angewendet.
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 bollean;
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
Seite 1
Prof. Dr. Stephan Karczewski
Datenbanken 2
Aufgabe 3
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.4.1. Anweisungen und Zuweisungen
Leere Anweisung
null;
Zuweisungsoperator :=
(z.B. 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.
Bsp: select count(*) into anzahl from ARTIKEL;
1.4.2. Arithmetische Operatoren
+ Addition, - Subtraktion, * Multiplikation, / Division
1.4.3. Vergleichsoperatoren und logische Operatoren
= Gleichheit, != oder <> Ungleichheit, <=, >=, <, > größer/kleiner (oder gleich)
AND Konjunktion, OR Disjunktion, NOT Negation
1.4.4. Verzweigung
if Bedingung
then Anweisungsfolge A
else Anweisungsfolge B
end if;
Der else-Teil kann entfallen; "end if" ist Pflicht.
1.4.5. Schleifen
Es gibt drei Formen von Schleifen:
bedingungslos:
loop ... end loop;
Zählschleife:
for Zählbereich loop ... end loop;
bedingte Schleife:
while Bedingung loop ... 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
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.5.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
Seite 2
Prof. Dr. Stephan Karczewski
Datenbanken 2
Aufgabe 3
Wert (eine Zeile, ein Tupel) der Ergebnismenge und wird nach bestimmten
Regeln auf einen weiteren Wert geschaltet.
1.5.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.5.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.
Bsp: 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);
s2 varchar(20);
s3 number(9,2)
);
/
Diese Tabelle muss vor dem folgenden PL/SQL-Block definiert sein.
PL/SQL-Block:
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;
Seite 3
Prof. Dr. Stephan Karczewski
Datenbanken 2
Aufgabe 3
end loop;
close c1;
end;
/
1.5.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.5. Strukturvariable
Eine Strukturvariable hart den selben Aufbau wie eine Zeile einer Tabelle oder
eines Cursors. Man verwendet für eine Strukturvariable den Namenszusatz
%rowtype.
Beispiel:
declare
cursor aktbuch is ...;
BuchRec
aktbuch%rowtype;
begin
loop
fetch aktbuch into BuchRec;
exit when AktBuch%notfound;
...
end loop;
end;
1.5.6. Update mit einem Cursor
Bei der Vereinbarung eines Cursors kann imSELECT-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;
end loop;
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. Beispiel:
declare
wert NUMBER(9);
begin
Seite 4
Prof. Dr. Stephan Karczewski
Datenbanken 2
Aufgabe 3
select count(*) into wert from lieferant;
dbms_output.put_line('Wert ist: ' || wert);
end;
/
Mit dem Verknüpfungszeichen || (pipelining) kann ein Variablenwert z.B. an einen
Textstring angehangen werden. Wichtig ist, dass die Umgebungsvariable
serveroutput eingeschaltet ist (s. nächster Abschnitt).
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 5