Kapitel 20

Im Mittelpunkt des heutigen Tages steht SQL*Plus, die SQL-Schnittstelle für das RDBMS von Oracle. Insbesondere erfahren Sie in der heutigen Lektion ...

wie man den Puffer von SQL*Plus einsetzt.
wie man Berichte attraktiv formatiert.
wie man Datumswerte manipuliert.
wie man interaktive Abfragen stellt.
wie man kompliziertere Berichte erzeugt.
wie man die leistungsfähige Funktion DECODE einsetzt.

Einführung

SQL*Plus dürfte für viele Leser von Interesse sein, da Oracle auf dem Markt der relationalen Datenbanken dominiert und mit SQL*Plus dem Datenbankbenutzer ein leistungsfähiges und flexibles Produkt bietet. SQL*Plus ist Transact-SQL (siehe Tag 19) in vielerlei Hinsicht ähnlich. Beide Implementierungen gehen größtenteils mit dem ANSI-SQL-Standard konform, der weiterhin das Grundgerüst jeder Implementierung bildet.

SQL*Plus-Befehle können die Möglichkeiten einer SQL-Sitzung erweitern und das Format von Abfragen aus der Datenbank verbessern. SQL*Plus läßt sich außerdem als Werkzeug zur Berichterstellung einsetzen. SQL*Plus ergänzt sowohl Standard-SQL als auch PL/SQL und unterstützt damit den Programmierer bei der Zusammenstellung der Daten im gewünschten Format.

Der SQL*Plus-Puffer

Der SQL*Plus-Puffer speichert Befehle, die spezifisch zur jeweiligen SQL-Sitzung sind. Zu diesen Befehlen gehören die zuletzt ausgeführten SQL-Anweisungen und Befehle, mit denen Sie Ihre SQL-Sitzung angepaßt haben, zum Beispiel Befehle zur Formatierung und Variablenzuweisungen. Dieser Puffer verhält sich wie ein Kurzzeitgedächtnis. Einige der gebräuchlichsten SQL-Pufferbefehle lauten:

LIST Zeilennummer - Listet eine Zeile der im Puffer befindlichen Anweisung auf und kennzeichnet sie als aktuelle Zeile.
CHANGE/alter_Wert/neuer_Wert - Ändert alter_Wert in neuer_Wert auf der aktuellen Zeile im Puffer.
APPEND Text - Fügt den Text an die aktuelle Zeile im Puffer an.
DEL - Löscht die aktuelle Zeile im Puffer.
SAVE neue_Datei - Speichert die SQL-Anweisung im Puffer als Datei.
GET Dateiname - Lädt eine SQL-Datei in den Puffer.
/ - Führt die SQL-Anweisung im Puffer aus.

Wir beginnen mit einer einfachen SQL-Anweisung:

SQL> select *
2 from artikel
3 where einzelpreis > 25;

ART ART_NAME EINZELPREIS
--- ------------------------------ -----------
P01 MICKY-MAUS-LAMPE 29,95
P06 SQL-BEFEHLSREFERENZ 29,99
P07 LEDERBRIEFTASCHE SCHWARZ 99,99

3 Zeilen ausgewählt.

Der Befehl LIST zeigt die zuletzt ausgeführte SQL-Anweisung im Puffer an. Als Ausgabe erscheint lediglich die Anweisung.

SQL> list
1 select *
2 from artikel
3* where einzelpreis > 25

Beachten Sie, daß alle Zeilen numeriert sind. Die Zeilennummern dienen als Zeiger, über den man bestimmte Zeilen der Anweisung im SQL*Plus-Puffer spezifizieren kann. Der SQL*Plus-Puffer verhält sich nicht wie ein Ganzseiteneditor. Nachdem man die (¢)-Taste gedrückt hat, läßt sich der Cursor nicht mehr auf eine der vorhergehenden Zeilen setzen, wie es das folgende Beispiel zeigt.

SQL> select *
2 from artikel
3 where einzelpreis > 25
4 /

Befehle von SQL*Plus können Sie ebenso wie normale SQL-Befehle sowohl in Groß- als auch Kleinschreibung angeben.

Die meisten SQL*Plus-Befehle lassen sich abkürzen. Zum Beispiel läßt sich LIST mit l abkürzen.

Wenn man nach dem l die Zeilennummer schreibt, gelangt man direkt zur spezifizierten Zeile des Puffers.

SQL> l3

3* where einzelpreis > 25

Das Sternchen nach der Zeilennummer 3 kennzeichnet die aktuelle Zeilennummer. Achten Sie genau auf die Position des Sternchens in den heutigen Beispielen. Immer wenn eine Zeile durch das Sternchen markiert ist, kann man diese Zeile verändern.

An der aktuellen Zeile - im Beispiel 3 - können Sie nun Änderungen vornehmen. Die Syntax für den Befehl CHANGE lautet:

CHANGE/alter_Wert/neuer_Wert
oder

C/alter_Wert/neuer_Wert

SQL> c/>/<

3* where einzelpreis < 25

SQL> l

1 select *
2 from artikel
3* where einzelpreis < 25

In Zeile 3 wurde das Größer-als-Zeichen (>) in das Zeichen Kleiner-als (<) geändert. Zur Kontrolle erscheint die modifizierte Zeile in der Anzeige. Mit dem Befehl LIST oder l kann man die vollständige Anweisung einsehen. Führen Sie nun die folgende Anweisung aus:

SQL> /

ART ART_NAME EINZELPREIS
--- ------------------------------ -----------
P02 20ER PACKUNG STIFTE NR 2 1,99
P03 KAFFEEKANNE 6,95
P04 LANDSCHAFTSKALENDER 10,5
P05 NATURKALENDER 12,99

4 Zeilen ausgewählt.

Der Schrägstrich (/) an der Aufforderung SQL> führt alle im Puffer befindlichen Anweisungen aus.

SQL> l

1 select *
2 from artikel
3* where einzelpreis < 25

Dieser Anweisung können Sie nun eine neue Zeile hinzufügen, indem Sie an der Aufforderung SQL> eine neue Zeilennummer und den entsprechenden Text schreiben. Anschließend holen Sie sich das vollständige Listing auf den Bildschirm. Dazu ein Beispiel:

SQL> 4 order by einzelpreis
SQL> l

1 select *
2 from artikel
3 where einzelpreis < 25
4* order by einzelpreis

Das Löschen einer Zeile geht leichter vonstatten als das Hinzufügen. Tippen Sie einfach DEL 4 an der Aufforderung SQL> ein, um Zeile 4 zu löschen. Listen Sie wieder alle Anweisungen auf, um sich vom Löschen der Zeile zu überzeugen.

SQL> del4
SQL> l

1 select *
2 from artikel
3* where einzelpreis < 25

Mit dem Befehl INPUT lassen sich ebenfalls eine oder mehrere Zeilen in die Anweisung einfügen. Um den Ausgangszustand wiederherzustellen, geben Sie zunächst Zeile 4 wie im obigen Beispiel ein. Legen Sie dann mit dem Befehl l3 die Zeile 3 als aktuelle Zeile fest. Tippen Sie input an der Eingabeaufforderung ein, und drücken Sie (¢). Nun können Sie mit dem Schreiben des Textes beginnen. Jedes Drücken von (¢) erzeugt eine weitere Zeile. Wenn man (¢) zweimal betätigt, gelangt man zum SQL>-Prompt zurück. Zeigen Sie wie im folgenden Beispiel nach abgeschlossener Eingabe die Anweisungsliste an, um sich von der eingefügten Zeile 4 zu überzeugen.

SQL> input
4i and art_id = 'P01'
5i
SQL> l

1 select *
2 from artikel
3 where einzelpreis < 25
4 and art_id = 'P01'
5* order by einzelpreis

Mit dem Befehl APPEND läßt sich Text an die aktuelle Zeile anfügen. Vergleichen Sie die Ausgabe des vorherigen Beispiels - die aktuelle Zeilennummer ist 5 - mit dem folgenden Beispiel.

SQL> append desc

5* order by einzelpreis desc

Zeigen Sie nun ein vollständiges Listing der Anweisung an:

SQL> l

1 select *
2 from artikel
3 where einzelpreis < 25
4 and art_id = 'P01'
5* order by einzelpreis desc

Nehmen wir nun an, daß Sie wieder einen leeren »Notizblock« (das heißt Puffer) haben möchten. Der Inhalt des SQL*Plus-Puffers läßt sich mit dem Befehl CLEAR BUFFER löschen. Mit dem Befehl CLEAR kann man auch bestimmte Einstellungen aus dem Puffer löschen, beispielsweise Formatinformationen und Berechnungen auf einem Bericht. Darauf gehen wir später ein.

SQL> clear buffer

buffer entfernt

SQL> l

Puffer SQL enthält keine Zeilen.

Offensichtlich kann man aus einem leeren Puffer nichts abrufen.

Auch wenn Sie noch kein Meister sind, sollten Sie nun die Befehle im Puffer problemlos manipulieren können.

Der Befehl DESCRIBE

Mit dem Befehl DESCRIBE läßt sich die Struktur einer Tabelle anzeigen, daß man erst eine Abfrage bezüglich des Systemkatalogs vornehmen muß.

DESC[RIBE] Tabellenname

Die beiden folgenden Tabellen kommen in den restlichen Beispielen dieser Lektion zum Einsatz.

SQL> describe auftraege

Name Null? Type
------------------------------- -------- ----
AUFTR_NR NOT NULL NUMBER(2)
KUNDE NOT NULL VARCHAR2(30)
ART_ID NOT NULL CHAR(3)
ART_MENGE NOT NULL NUMBER(5)
LIEF_DAT DATE

Die folgende Anweisung verwendet die Abkürzung DESC statt DESCRIBE:

SQL> desc artikel

Name Null? Type
------------------------------- -------- ----
ART_ID NOT NULL VARCHAR2(3)
ART_NAME NOT NULL VARCHAR2(30)
EINZELPREIS NOT NULL NUMBER(8,2)

DESC zeigt alle Spaltennamen an, welche Spalten Daten enthalten müssen (NULL/NOT NULL) und welcher Datentyp jeder Spalte zugewiesen ist. Auf diesen Befehl werden Sie häufig zurückgreifen, insbesondere wenn Sie viele Abfragen schreiben. Auf lange Sicht spart Ihnen dieser Befehl viele Stunden Programmierzeit, da Sie sonst die Projektdokumentation oder sogar die Datenbankhandbücher mit den Listen der Systemkatalogtabellen durchsuchen müßten, um die entsprechenden Informationen zusammenzutragen.

Der Befehl SHOW

Der Befehl SHOW zeigt die aktuellen Einstellungen einer Sitzung an - angefangen bei Formatbefehlen bis zu Ihrem Benutzernamen. SHOW ALL listet alle Einstellungen auf. Die nächsten Abschnitte behandeln die gebräuchlichsten Einstellungen.

SQL> show all

appinfo ist ON und auf "SQL*Plus" eingestellt
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
shiftinout INVISIBLE
blockterminator "." (hex 2e)
btitle OFF und sind die ersten Zeichen der nächsten SELECT-Anweisung
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
copytypecheck ist ON
define "&" (hex 26)
echo ON
editfile "afiedt.buf"
embedded OFF
escape OFF
feedback ON für 1 oder mehr Zeilen
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
linesize 100
lno 10
loboffset 1
long 80
longchunksize 80
newpage 1
null ""
numformat ""
numwidth 9
pagesize 24
Pause ist OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 800030000
repfooter OFF und ist NULL
repheader OFF und ist NULL
serveroutput OFF
showmode OFF
spool OFF
sqlcase MIXED
sqlcode 942
sqlcontinue "> "
sqlnumber ON
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "SQL"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF und sind die ersten Zeichen der nächsten SELECT-Anweisung
underline "-" (hex 2d)
Der Benutzer ist "SYS"
verify ON
wrap : Es werden umgebrochen Zeilen

Der Befehl SHOW zeigt eine spezielle, vom Benutzer eingegebene Einstellung an. Wenn Sie zum Beispiel Zugriff auf die IDs mehrerer Datenbankbenutzer haben und die angemeldeten Benutzer anzeigen möchten, führen Sie einfach den folgenden Befehl aus:

SQL> show user

Der Benutzer ist "SYS"

Die momentane Länge einer Zeile zeigen Sie mit dem folgenden Befehl an:

SQL> show linesize

linesize 100

Dateibefehle

In SQL*Plus stehen verschiedene Befehle bereit, mit denen Sie Dateien manipulieren können. Dazu gehören das Erzeugen einer Datei, die Bearbeitung der Datei mit einem Editor statt über den Puffer von SQL*Plus und die Umleitung der Ausgabe in eine Datei. Außerdem muß man wissen, wie eine SQL-Datei auszuführen ist.

Die Befehle SAVE, GET und EDIT

Der Befehl SAVE speichert den Inhalt der im Puffer befindlichen SQL-Anweisung in eine Datei mit dem angegebenen Namen. Beispielsweise:

SQL> select *
2 from artikel
3 where einzelpreis < 25

SQL> save query1.sql

file query1.sql wurde angelegt

Eine gespeicherte Datei können Sie mit dem Befehl GET auflisten. GET arbeitet ähnlich zum Befehl LIST. Während aber GET die Anweisungen aus einer Datei holt, bringt LIST die Anweisungen aus dem Puffer auf den Bildschirm.

SQL> get query1

1 select *
2 from artikel
3* where einzelpreis < 25

Mit dem Befehl EDIT kann man sowohl eine neue Datei erstellen als auch eine vorhandene Datei bearbeiten. Der Befehl startet normalerweise den Standardeditor des jeweiligen Betriebssystems - unter Windows demnach den Windows-Editor. Vor allem bei großen und komplexen Anweisungen ist es einfacher, mit dem Editor zu arbeiten, als die Änderungen über den Puffer vorzunehmen. Abbildung 20.1 zeigt ein Beispiel für den Befehl EDIT.

SQL> edit query1.sql

Abbildung 20.1:
Eine Datei in SQL*Plus bearbeiten

Eine Datei ausführen

Nachdem Sie nun wissen, wie man eine SQL-Datei erzeugt und bearbeitet, brauchen Sie nur noch zu wissen, wie man diese Datei ausführt. Der Start-Befehl kann folgende Formen annehmen:

START Dateiname
oder

STA Dateiname

oder

@Dateiname

Befehle sind nicht von der Groß-/Kleinschreibung abhängig.

SQL> start query1.sql

Wenn Sie im Dateinamen die Erweiterung nicht explizit angeben, nimmt SQL*Plus die Standarderweiterung .sql an. In Befehlen wie START, SAVE, GET oder EDIT können Sie also die Dateierweiterung weglassen, solange Sie mit der Standarderweiterung arbeiten.

SQL> @query1

SQL> run query1

1 select *
2 from artikel
3* where einzelpreis < 25

ART ART_NAME EINZELPREIS
--- ------------------------------ -----------
P02 20ER PACKUNG STIFTE NR 2 1,99
P03 KAFFEEKANNE 6,95
P04 LANDSCHAFTSKALENDER 10,5
P05 NATURKALENDER 12,99

4 Zeilen ausgewählt.

Wenn Sie eine Abfrage mit RUN starten, wird die Anweisung als Echo auf dem Bildschirm ausgegeben.

Ausgaben umleiten

In der Regel genügt es, die Ergebnisse einer Abfrage auf dem Bildschirm anzuzeigen. Mit dem Befehl SPOOL kann man die Ausgabe auch an eine Datei senden, so daß man im nachhinein auf die Ergebnisse zurückgreifen oder die Datei drucken kann. Wenn die Datei noch nicht existiert, wird sie angelegt, ansonsten überschrieben.

SQL> spool artikel.lst
SQL> select *
2 from artikel;

ART ART_NAME EINZELPREIS
--- ------------------------------ -----------
P01 MICKY-MAUS-LAMPE 29,95
P02 20ER PACKUNG STIFTE NR 2 1,99
P03 KAFFEEKANNE 6,95
P04 LANDSCHAFTSKALENDER 10,5
P05 NATURKALENDER 12,99
P06 SQL-BEFEHLSREFERENZ 29,99
P07 LEDERBRIEFTASCHE SCHWARZ 99,99

7 Zeilen ausgewählt.

SQL> spool off
SQL> edit artikel.lst

Abbildung 20.2 zeigt die mit EDIT aufgerufene SQL*Plus-Datei ARTIKEL.LST. Die Umleitung in eine Datei ist mit dem Befehl SPOOL OFF aufzuheben. Beim Verlassen von SQL*Plus wird der Befehl SPOOL OFF automatisch ausgeführt. Ansonsten leitet SQL*Plus alle Ausgaben in die Datei weiter, bis man explizit den Befehl SPOOL OFF ausführt.

Abbildung 20.2:
Umleitung der Ausgabe in eine Datei

SET-Befehle

Mit den SET-Befehlen ändert man in Oracle die Einstellungen von SQL*Plus. Man kann damit die Arbeitsumgebung von SQL anpassen und Optionen festlegen, um die Anzeige der Ergebnisse übersichtlicher zu gestalten. Bei vielen SET-Befehlen schaltet man eine Option ein oder aus (ON bzw. OFF).

Die nächsten Beispiele demonstrieren verschiedene SET-Befehle. Führen Sie zunächst die folgende SELECT-Anweisung aus.

SQL> select *
2 from artikel;

Die letzte Zeile der Ausgabe

7 Zeilen ausgewählt.

bezeichnet man als RückmeldungFEEDBACK gleich ON gesetzt, die Rückmeldung also aktiviert. Sollte die Rückmeldung nicht zu sehen sein, können Sie sie vor Ausführung der SELECT-Anweisung mit dem Befehl

SET FEEDBACK ON

einschalten. Bei zusammenfassenden Berichten mit Berechnungen ist es oftmals sinnvoll, die Rückmeldung zu unterdrücken.

SQL> set feedback off
SQL> select *
2 from artikel;

Der Befehl SET FEEDBACK OFF schaltet die Anzeige der Rückmeldungen ab.

In bestimmten Fällen möchte man die Spaltenüberschriften in einem Bericht ausblenden. Diese Einstellung heißt HEADING und läßt sich ebenfalls auf ON oder OFF setzen.

SQL> set heading off
SQL> /

P01 MICKY-MAUS-LAMPE 29,95
P02 20ER PACKUNG STIFTE NR 2 1,99
P03 KAFFEEKANNE 6,95
P04 LANDSCHAFTSKALENDER 10,5
P05 NATURKALENDER 12,99
P06 SQL-BEFEHLSREFERENZ 29,99
P07 LEDERBRIEFTASCHE SCHWARZ 99,99

Die Ausgabe zeigt nur noch die eigentlichen Daten ohne Spaltenüberschriften.

Die Anzeige läßt sich mit zahlreichen Einstellungen modifizieren. Beispielsweise legt die Option LINESIZE die Länge der Ausgabezeilen fest. Bei kürzeren Zeilen treten Zeilenumbrüche häufiger auf, so daß man die Standardlänge von 80 Zeichen gegebenenfalls erhöhen muß, um Zeilenumbrüche bei längeren Zeilen zu vermeiden. Ab einer Länge von 80 Zeichen pro Zeile sollte man beim Ausdruck das Querformat verwenden. Das folgende Beispiel zeigt den Einsatz von LINESIZE.

SQL> set linesize 40
SQL> /

P01 MICKY-MAUS-LAMPE
29,95

P02 20ER PACKUNG STIFTE NR 2
1,99

P03 KAFFEEKANNE
6,95

P04 LANDSCHAFTSKALENDER
10,5

P05 NATURKALENDER
12,99

P06 SQL-BEFEHLSREFERENZ
29,99

P07 LEDERBRIEFTASCHE SCHWARZ
99,99

Die Größe einer Seite läßt sich mit der Einstellung PAGESIZE anpassen. Bei Ausgabe auf dem Bildschirm empfiehlt sich ein PAGESIZE-Wert von 23. Damit vermeidet man mehrere Seitenwechsel pro Bildschirm. Das folgende Beispiel setzt PAGESIZE auf einen kleinen Wert, um die Auswirkung auf die Seitenwechsel zu demonstrieren.

SQL> set linesize 80
SQL> set heading on
SQL> set pagesize 7
SQL> /

ART ART_NAME EINZELPREIS
--- ------------------------------ -----------
P01 MICKY-MAUS-LAMPE 29,95
P02 20ER PACKUNG STIFTE NR 2 1,99
P03 KAFFEEKANNE 6,95
P04 LANDSCHAFTSKALENDER 10,5

ART ART_NAME EINZELPREIS
--- ------------------------------ -----------
P05 NATURKALENDER 12,99
P06 SQL-BEFEHLSREFERENZ 29,99
P07 LEDERBRIEFTASCHE SCHWARZ 99,99

Bei der Einstellung PAGESIZE 7 erscheinen auf einer Seite maximal 7 Zeilen. Am Beginn jeder neuen Seite werden automatisch Spaltenüberschriften ausgegeben.

Die Einstellung TIME zeigt die aktuelle Uhrzeit als Teil der Aufforderung SQL> an.

SQL> set time on

22:24:06 SQL>

An dieser Stelle haben wir nur einen kleinen Ausschnitt der verfügbaren SET-Optionen gezeigt. Prinzipiell arbeiten aber alle SET-Befehle in der gleichen Weise. Wie Sie der umfangreichen Liste beim Befehl SHOW ALL (weiter oben) entnehmen können, gibt es zahlreiche Möglichkeiten zur Anpassung einer SQL*Plus-Sitzung. Probieren Sie am besten die einzelnen Optionen aus, und übernehmen Sie die Ihrer Meinung nach besten Werte. Wahrscheinlich können Sie die Standardwerte der meisten Optionen beibehalten, bei verschiedenen Szenarios werden Sie aber die eine oder andere Option ändern wollen.

Die Datei LOGIN.SQL

Wenn man sich von SQL*Plus abmeldet, werden alle Einstellungen für die Sitzung gelöscht. Melden Sie sich wieder an, müssen Sie die von den Vorgaben abweichenden Werte wiederherstellen. Dieser Vorgang läßt sich mit Hilfe der Datei login.sql automatisieren. Diese Initialisierungsdatei wird automatisch bei der Anmeldung in SQL*Plus ausgeführt und entspricht in ihrer Art der Datei autoexec.bat auf dem PC oder .profile in einer Unix-Umgebung.

In Personal Oracle8 können Sie mit dem Befehl EDIT eine eigene login.sql-Datei erstellen, wie es Abbildung 20.3 zeigt.

Abbildung 20.3:
Eine login.sql-Datei erstellen

Bei der Anmeldung in SQL*Plus erscheint dann folgende Meldung:

SQL*Plus: Release 8.0.3.0.0 - Production on So Dez 14 22:41:1 1997

(c) Copyright 1997 Oracle Corporation. All rights reserved.

Kennwort eingeben: *****************

Verbunden mit:
Personal Oracle8 Release 8.0.3.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.3.0.0 - Production

'HELLO!
-------
HELLO !

22:41:19 SQL>

Der Befehl CLEAR

In SQL*Plus werden die Einstellungen bei der Abmeldung oder dem Verlassen von SQL*Plus gelöscht. Bestimmte Einstellungen lassen sich auch mit dem Befehl CLEAR löschen, wie es die folgenden Beispiele zeigen.

SQL> clear col

columns entfernt

SQL> clear break

breaks entfernt

SQL> clear compute

computes entfernt

Ausgaben formatieren

SQL*Plus kennt auch Befehle, mit denen man die Ausgabe in nahezu jedem gewünschten Format anordnen kann. Dieser Abschnitt behandelt die grundlegenden Befehle zur Formatierung für Berichtstitel, Spaltenüberschriften und Formate und zeigt, wie man eine Spalte mit einem »neuen Wert« versieht.

TTITLE und BTITLE

Mit TTITLE und BTITLE lassen sich Titel in Berichten erstellen. Die bisherigen Lektionen haben sich mit Abfragen und Ausgaben beschäftigt, mit SQL*Plus lassen sich aber auch einfache Ausgaben in ansprechende Berichte umwandeln. Der Befehl TTITLE plaziert einen Titel am Beginn jeder Seite der Ausgabe oder des Berichts. BTITLE setzt einen Titel am unteren Rand jeder Seite des Berichts. Bei diesen Befehlen sind viele Optionen verfügbar, die heutige Darstellung behandelt aber nur das wesentliche. Die grundlegende Syntax von TTITLE und BTITLE lautet:

TTITLE [center|left|right] 'text' [&variable] [skip n]
BTITLE [center|left|right] 'text' [&variable] [skip n]

SQL> ttitle 'ARTIKELLISTE'
SQL> btitle 'ENDE DER LISTE'
SQL> set pagesize 15
SQL> /

So Dez 14 ARTIKELLISTE

ART ART_NAME EINZELPREIS
--- ------------------------------ -----------
P01 MICKY-MAUS-LAMPE 29,95
P02 20ER PACKUNG STIFTE NR 2 1,99
P03 KAFFEEKANNE 6,95
P04 LANDSCHAFTSKALENDER 10,5
P05 NATURKALENDER 12,99
P06 SQL-BEFEHLSREFERENZ 29,99
P07 LEDERBRIEFTASCHE SCHWARZ 99,99

ENDE DER LISTE

7 Zeilen ausgewählt.

Titel erscheinen am oberen und unteren Rand jeder Seite. Den unteren Titel verwendet man oft als Kennzeichnungsblock für überprüfte oder geänderte Daten im Bericht. Der obere Titel enthält unter anderem das Datum und läßt sich durch Angabe verschiedener Variablen um zusätzliche Angaben (zum Beispiel die Seitennummer) erweitern.

Spalten formatieren (COLUMN, HEADING, FORMAT)

Eine SpaltenformatierungSELECT in einer SQL-Anweisung aufgeführt sind. Mit den Befehlen COLUMN, HEADING und FORMAT lassen sich Spaltenüberschriften umbenennen und die anzuzeigenden Daten formatieren.

Den Befehl COL[UMN] setzt man gewöhnlich mit dem Befehl HEADING oder mit dem Befehl FORMAT ein. COLUMN definiert die zu formatierende Spalte. Die Definition der Spalte muß exakt mit der Angabe in der SELECT-Anweisung übereinstimmen. In diesem Befehl kann auch ein Alias für die Spalte statt dem vollen Spaltennamen stehen.

Mit dem Befehl HEADING legt man eine Spaltenüberschrift fest, der Befehl FORMAT dient der Formatierung einer Spalte. Bei beiden Befehlen ist mit COLUMN die betreffende Spalte anzugeben.

Die grundlegende Syntax für alle drei Befehle folgt umgehend. Beachten Sie, daß die Befehle HEADING und FORMAT optional sind. In der FORMAT-Syntax ist bei Zeichenformaten ein a zu schreiben, numerische Datentypen sind mit den Ziffern 0 oder 9 darzustellen. Numerische Werte können auch Dezimalstellen enthalten. Die Zahl rechts neben dem a gibt die gesamte Breite an, die für die spezifizierte Spalte zulässig sein soll.

COL[UMN] Spaltenname HEA[DING] "Neue_Überschrift" FOR[MAT] [a1|99.99]

Die einfache SELECT-Anweisung im folgenden Beispiel zeigt die Formatierung einer Spalte. Die angegebene Spalte hat den Datentyp NUMBER, die Zahl soll in Dezimaldarstellung mit einem Dollarzeichen erscheinen.

SQL> column einzelpreis heading "PREIS" format $99.99
SQL> select art_name, einzelpreis
2 from artikel;

ART_NAME PREIS
------------------------------ -------
MICKY-MAUS-LAMPE $29.95
20ER PACKUNG STIFTE NR 2 $1.99
KAFFEEKANNE $6.95
LANDSCHAFTSKALENDER $10.50
NATURKALENDER $12.99
SQL-BEFEHLSREFERENZ $29.99
LEDERBRIEFTASCHE SCHWARZ $99.99

7 Zeilen ausgewählt.

Mit dem verwendeten Format 99.99 läßt sich als größte Zahl 99.99 anzeigen.

Die Befehle in den folgenden Beispielen sind abgekürzt. Das erste Beispiel zeigt den Befehl HEADING:

SQL> col einzelpreis hea "Preis|Stück" for $09.99
SQL> select art_name, einzelpreis
2 from artikel;

Preis
ART_NAME Stück
------------------------------ -------
MICKY-MAUS-LAMPE $29.95
20ER PACKUNG STIFTE NR 2 $01.99
KAFFEEKANNE $06.95
LANDSCHAFTSKALENDER $10.50
NATURKALENDER $12.99
SQL-BEFEHLSREFERENZ $29.99
LEDERBRIEFTASCHE SCHWARZ $99.99

7 Zeilen ausgewählt.

Das Pipe-Zeichen (|) im HEADING-Befehl bewirkt die Ausgabe des folgenden Textes der Spaltenüberschrift auf der nächsten Zeile. Es lassen sich mehrere Pipe-Zeichen angeben. Diese Methode bietet sich an, wenn die Breite des Berichts an die maximal verfügbare Zeilengröße stößt. Das Format der Spalten für den Einzelpreis lautet jetzt 09.99. Die maximal anzuzeigende Zahl beträgt weiterhin 99.99. Jetzt aber steht eine 0 vor allen Zahlen, die kleiner als 10 sind. Vielleicht bevorzugen Sie dieses Format, da die Dollarbeträge dann einheitlich aussehen.

Zusammenfassungen in Berichten und Gruppen

Was wäre ein Bericht ohne Zusammenfassungen und Berechnungen? Bestimmte Befehle in SQL*Plus erlauben die Aufteilung des Berichts in verschiedene Gruppentypen mit Zusammenfassungen oder Berechnungen für jede Gruppe. BREAK unterscheidet sich etwas von den Gruppenfunktionen in Standard-SQL wie COUNT und SUM. Diese Funktionen verwendet man für Berichts- und Gruppenzusammenfassungen, um den Bericht aussagekräftiger zu gestalten.

BREAK ON

Der Befehl BREAK ON teilt die aus einer SQL-Anweisung zurückgegebenen Datenzeilen in eine oder mehrere Gruppen auf. Nimmt man die Aufteilung bezüglich des Kundennamens vor, erscheint der Name des Kunden per Vorgabe nur in der ersten zurückgegebenen Zeile und wird in den anderen Datenzeilen mit diesem Namen freigelassen. Die grundlegende Syntax des Befehls BREAK ON sieht folgendermaßen aus:

BRE[AK][ON Spalte1 ON Spalte2...][SKIP n|PAGE][DUP|NODUP]

Die Aufteilung läßt sich auch mit den Optionen REPORT und ROW durchführen. BREAK ON REPORT führt Berechnungen auf dem Bericht als Ganzes aus, während BREAK ON ROW die Berechnungen für jede Zeilengruppe vornimmt.

Mit der Option SKIP werden n Zeilen bzw. eine Seite bei jeder Gruppe übersprungen. DUP und NODUP zeigen an, ob Duplikate in jeder Gruppe auszugeben sind. Der Vorgabewert ist NODUP.

Dazu ein Beispiel:

SQL> col einzelpreis head 'Preis|Stück' for $09.99
SQL> break on kunde
SQL> select a.kunde, p.art_name, p.einzelpreis
2 from auftraege a,
3 artikel p
4 where a.art_id = p.art_id
5 order by kunde;

Preis
KUNDE ART_NAME Stück
------------------------------ ------------------------------ -------
BOUTIQUE AM TURM MICKY-MAUS-LAMPE $29.95
20ER PACKUNG STIFTE NR 2 $01.99
KAFFEEKANNE $06.95
MEIER-WERBUNG MICKY-MAUS-LAMPE $29.95
20ER PACKUNG STIFTE NR 2 $01.99
SQL-BEFEHLSREFERENZ $29.99
LEDERBRIEFTASCHE SCHWARZ $99.99
LANDSCHAFTSKALENDER $10.50
TANKSTELLE NORD MICKY-MAUS-LAMPE $29.95
LEDERBRIEFTASCHE SCHWARZ $99.99
LEDERBRIEFTASCHE SCHWARZ $99.99
20ER PACKUNG STIFTE NR 2 $01.99
20ER PACKUNG STIFTE NR 2 $01.99

13 Zeilen ausgewählt.

Jeder eindeutige Kundenname erscheint nur einmal. Gegenüber einem Bericht, der den Kundennamen auf jeder Zeile zeigt, läßt sich der obige Bericht besser lesen. Damit der BREAK-Befehl funktioniert, sind die Ergebnisse in der gleichen Reihenfolge aufzuteilen wie die Spalten, auf denen man die Gruppierung vornimmt.

COMPUTE

Der Befehl COMPUTE wird zusammen mit BREAK ON eingesetzt. COMPUTE erlaubt die Ausführung verschiedenartiger Berechnungen auf jeder Datengruppe und/oder auf dem gesamten Bericht.

COMP[UTE] Funktion OF Spalte_oder_Alias ON Spalte_oder_Zeile_oder_Bericht

Zu den gebräuchlichsten Funktionen gehören:

AVG - berechnet den Mittelwert für jede Gruppe.
COUNT - berechnet die Anzahl der Werte in jeder Gruppe.
SUM - berechnet die Summe der Werte in jeder Gruppe.

Das nächste Beispiel erstellt einen Bericht, der die Informationen aus der Tabelle ARTIKEL auflistet und den durchschnittlichen Artikelpreis berechnet.

SQL> break on report
SQL> compute avg of einzelpreis on report
SQL> select *
2 from artikel;

Preis
ART ART_NAME Stück
--- ------------------------------ -------
P01 MICKY-MAUS-LAMPE $29.95
P02 20ER PACKUNG STIFTE NR 2 $01.99
P03 KAFFEEKANNE $06.95
P04 LANDSCHAFTSKALENDER $10.50
P05 NATURKALENDER $12.99
P06 SQL-BEFEHLSREFERENZ $29.99
P07 LEDERBRIEFTASCHE SCHWARZ $99.99
-------
avg $27.48

7 Zeilen ausgewählt.

Die gewünschten Informationen erhält man, indem man eine Gruppe auf REPORT bildet und dann den Mittelwert (Funktion avg) für die Spalte Einzelpreis auf REPORT berechnet.

Erinnern Sie sich noch an den Befehl CLEAR? Wir löschen jetzt die letzte Berechnung aus dem Puffer und beginnen von neuem - dieses Mal möchten wir aber den von jedem Kunden bezahlten Betrag berechnen. Da wir kein Interesse mehr am Mittelwert haben, löschen wir die Berechnungen.

SQL> clear compute

computes entfernt

Den letzten BREAK löschen wir ebenfalls. (Im vorliegenden Fall ist das eigentlich nicht erforderlich, da wir die Gruppierung weiterhin auf dem Bericht bilden wollen.)

SQL> clear break

breaks entfernt

Im nächsten Schritt legen wir die Gruppen und die gewünschten Berechnungen neu fest. Außerdem ist die Spalte Einzelpreis für größere Zahlen zu formatieren, da wir jetzt die Summe für die Spalte Einzelpreis des Berichts berechnen und das Gesamtergebnis unter dieser Spalte im gleichen Format stehen soll. Aus diesem Grund sehen wir eine weitere Stelle links vom Dezimaltrennzeichen vor.

SQL> col einzelpreis hea 'Preis|Stück' for $099.99
SQL> break on report on kunde skip 1
SQL> compute sum of einzelpreis on kunde
SQL> compute sum of einzelpreis on report

Jetzt listen wir die letzte SQL-Anweisung im Puffer auf.

SQL> l

1 select a.kunde, p.art_name, p.einzelpreis
2 from auftraege a,
3 artikel p
4 where a.art_id = p.art_id
5* order by kunde

Nachdem wir uns davon überzeugt haben, daß es sich um die vorgesehene Anweisung handelt, führen wir sie aus:

SQL> /

Preis
KUNDE ART_NAME Stück
------------------------------ ------------------------------ --------
BOUTIQUE AM TURM MICKY-MAUS-LAMPE $029.95
20ER PACKUNG STIFTE NR 2 $001.99
KAFFEEKANNE $006.95
****************************** --------
sum $038.89

MEIER-WERBUNG MICKY-MAUS-LAMPE $029.95
20ER PACKUNG STIFTE NR 2 $001.99
SQL-BEFEHLSREFERENZ $029.99
LEDERBRIEFTASCHE SCHWARZ $099.99
LANDSCHAFTSKALENDER $010.50
****************************** --------
sum $172.42

TANKSTELLE NORD MICKY-MAUS-LAMPE $029.95
LEDERBRIEFTASCHE SCHWARZ $099.99
LEDERBRIEFTASCHE SCHWARZ $099.99
20ER PACKUNG STIFTE NR 2 $001.99
20ER PACKUNG STIFTE NR 2 $001.99

Preis
KUNDE ART_NAME Stück
------------------------------ ------------------------------ --------
****************************** --------
sum $233.91
--------
sum $445.22

13 Zeilen ausgewählt.

Dieses Beispiel hat die Summe für jeden Kunden sowie die Gesamtsumme für alle Kunden berechnet.

Mittlerweile sollten Sie die Grundlagen der Spaltenformatierung, der Gruppierung von Daten auf dem Bericht und die Ausführung von Berechnungen auf jeder Gruppe beherrschen.

Variablen in SQL*Plus

In einer SQL-Anweisung lassen sich Variablen definieren, ohne daß man dazu eine prozedurale Programmiersprache bemühen muß. Mit speziellen - in diesem Abschnitt behandelten - Optionen von SQL*Plus kann man Eingaben vom Benutzer entgegennehmen und Parameter an das SQL-Programm übergeben.

Variablen substituieren (&)

Mit Hilfe des kaufmännischen Und-Zeichens (&) läßt sich der Wert für eine Variable innerhalb eines SQL-Skripts abrufen. Ist die Variable noch nicht definiert, wird der Benutzer aufgefordert, einen Wert einzugeben.

SQL> select *
2 from &TBL
3 /
Geben Sie einen Wert für tbl ein: artikel

alt 2: from &TBL
neu 2: from artikel

Preis
ART ART_NAME Stück
--- ------------------------------ -------
P01 MICKY-MAUS-LAMPE $29.95
P02 20ER PACKUNG STIFTE NR 2 $1.99
P03 KAFFEEKANNE $6.95
P04 LANDSCHAFTSKALENDER $10.50
P05 NATURKALENDER $12.99
P06 SQL-BEFEHLSREFERENZ $29.99
P07 LEDERBRIEFTASCHE SCHWARZ $99.99

7 Zeilen ausgewählt.

In dieser »interaktiven Abfrage« wurde der Wert artikel für &TBL substituiert.

DEFINE

Mit DEFINE weist man Variablen in einem SQL-Skript Werte zu. Definiert man die Variablen im Skript, werden die Benutzer zur Laufzeit des Programms nicht zur Eingabe eines Werts für die Variable aufgefordert, wie es bei Verwendung des kaufmännischen Und-Zeichens (&) geschieht. Das nächste Beispiel führt die gleiche SELECT-Anweisung wie im vorhergehenden Beispiel aus, dieses Mal wird aber der Wert von TBL innerhalb des Skripts definiert.

SQL> define TBL=artikel
SQL> select *
2 from &TBL;

Beide Abfragen liefern das gleiche Ergebnis. Der nächste Abschnitt beschreibt eine andere Möglichkeit, um vom Benutzer Skript-Parameter abzufordern.

ACCEPT

ACCEPT erlaubt dem Benutzer, den Wert einer Variablen zur Laufzeit des Skripts einzugeben und bewirkt damit das gleiche wie das & ohne DEFINE. Allerdings läßt sich ACCEPT etwas besser kontrollieren und erlaubt die Ausgabe einer benutzerfreundlichen Aufforderung.

Das nächste Beispiel löscht zunächst den Puffer:

SQL> clear buffer

buffer entfernt

Die neue SQL-Anweisung geben wir dann über einen INPUT-Befehl in den Puffer ein. Läßt man den INPUT-Befehl weg und beginnt sofort mit der Eingabe, erhält man als erstes eine Aufforderung zur Eingabe eines Werts für newtitle. Als Alternative kann man gleich eine neue Datei anlegen und die Anweisungen dort schreiben.

SQL> input
1 accept newtitle prompt 'Bitte Titel für Bericht eingeben: '
2 ttitle center newtitle
3 select *
4 from artikel
5
SQL> save artikel

"artikel.SQL" existiert bereits.
Namen ändern oder "SAVE Dateiname REPLACE" verw.

Was nun? Die Datei artikel.sql existiert bereits. Wir können den Namen ändern oder die Datei überschreiben. Für das Beispiel entscheiden wir uns für das Überschreiben der Datei und verwenden die Ersetzungsoption REPLACE, um die Anweisung im Puffer nach artikel.sql zu speichern. Die PROMPT-Klausel in der obigen Anweisung gibt einen Aufforderungstext für den Benutzer aus.

SQL> save artikel replace

file artikel wurde geschrieben

Nun können Sie mit dem Befehl START die Datei ausführen.

SQL> start artikel

Bitte Titel für Bericht eingeben: ARTIKELLISTE

ARTIKELLISTE
Preis
ART ART_NAME Stück
--- ------------------------------ -------
P01 MICKY-MAUS-LAMPE $29.95
P02 20ER PACKUNG STIFTE NR 2 $1.99
P03 KAFFEEKANNE $6.95
P04 LANDSCHAFTSKALENDER $10.50
P05 NATURKALENDER $12.99
P06 SQL-BEFEHLSREFERENZ $29.99
P07 LEDERBRIEFTASCHE SCHWARZ $99.99

7 Zeilen ausgewählt.

Der eingegebene Text wird zum aktuellen Titel des Berichts.

Das nächste Beispiel zeigt, wie man Substitutionsvariablen in einer Anweisung verwendet:

SQL> input
1 accept ART_ID prompt 'Zu suchende ART ID eingeben: '
2 select *
3 from artikel
4 where ART_ID = '&ART_ID'
5
SQL> save art1

file art1 wurde angelegt

SQL> start art1

Zu suchende ART ID eingeben: P01

alt 3: where ART_ID = '&ART_ID'
neu 3: where ART_ID = 'P01'

ARTIKELLISTE
Preis
ART ART_NAME Stück
--- ------------------------------ -------
P01 MICKY-MAUS-LAMPE $29.95

1 Zeile wurde ausgewählt.

Variablen kann man für viele Zwecke einsetzen - beispielsweise, um die Datei zu benennen, in die man die Ausgaben umleiten möchte, oder um einen Ausdruck in der ORDER BY-Klausel zu spezifizieren. Über Substitutionsvariablen lassen sich auch Berichtsdaten in der WHERE-Klausel bei Berichten zur Qualitätssicherung eingeben. Soll eine Abfrage die Angaben zu jeweils einer Person abrufen, kann man eine Substitutionsvariable vorsehen und diese mit der Spalte SVN (Sozialversicherungsnummer) einer Tabelle vergleichen.

NEW_VALUE

Mit der Klausel NEW_VALUE kann man den Wert einer ausgewählten Spalte in die angegebene - noch nicht definierte - Variable übernehmen. Die Syntax lautet wie folgt:

COL[UMN] Spaltenname NEW_VALUE neuer_Name

Die Werte von Variablen ruft man mit Hilfe des Zeichens & auf. Dazu ein Beispiel:

&neuer_Name

Die Klausel NEW_VALUE ist in Verbindung mit dem Befehl COLUMN zu verwenden.

Beachten Sie die Verwendung von & und des Befehls COLUMN in der nächsten SQL*Plus-Datei. Der GET-Befehl lädt die Datei.

SQL> get art2
Zeile 5 abgeschnitten.
1 ttitle left 'Artikelübersicht: &ART_TITEL' skip 2
2 col ART_NAME new_value ART_TITEL
3 select ART_NAME, EINZELPREIS
4 from ARTIKEL
5* where ART_NAME = 'KAFFEEKANNE

Artikelübersicht: Kaffeekanne

Preis
ART_NAME Stück
------------------------------ -------
KAFFEEKANNE $6.95

1 Zeile wurde ausgewählt.

Das Beispiel übergibt den Wert für die Spalte ART_NAME mittels NEW_VALUE an die Variable art_titel. Der Wert der Variablen art_title wurde dann in TTITLE aufgerufen.

Weitere Informationen zu Variablen in SQL finden Sie in den Lektionen 18 und 19.

Die Tabelle DUAL

Die Tabelle DUAL ist eine Dummy-Tabelle, die in jeder Oracle-Datenbank existiert. Die Tabelle besteht nur aus der Spalte DUMMY mit dem einzigen Wert X. Die Tabelle DUAL steht allen Datenbankbenutzern zur Verfügung und läßt sich für allgemeine Zwecke verwenden, beispielsweise um arithmetische Operationen auszuführen (hier dient die Tabelle als Rechner) oder um das Format von SYSDATE zu manipulieren.

SQL> desc dual;

Name Null? Type
------------------------------- -------- ----
DUMMY VARCHAR2(1)

SQL> select *
2 from dual;

D
-
X

Sehen Sie sich die folgenden Beispiele an, die mit der Tabelle DUAL arbeiten:

SQL> select sysdate
2 from dual;

SYSDATE
--------
15.12.97

SQL> select 2 * 2
2 from dual;

2*2
---------
4

Die erste Anweisung hat SYSDATE aus der Tabelle DUAL ausgewählt und das heutige Datum geholt. Das zweite Beispiel zeigt, wie man in der Tabelle DUAL multipliziert. Die Antwort für 2 * 2 lautet 4.

Die Funktion DECODE

Die Funktion DECODE gehört zu den leistungsfähigsten Befehlen in SQL*Plus. In der Standardsprache von SQL fehlen prozedurale Funktionen, wie man sie aus Sprachen wie Cobol oder C kennt.

Die Anweisung DECODE ähnelt einer IF...THEN-Anweisung in einer prozeduralen Programmiersprache. In komplexen Berichten kann DECODE oftmals die Lücke zwischen SQL und den Funktionen einer prozeduralen Sprache schließen.

DECODE(Spalte1, Wert1, Ausgabe1, Wert2, Ausgabe2, Ausgabe3)

Das Syntaxbeispiel führt die DECODE-Funktion auf Spalte1 aus. Wenn Spalte1 einen Wert von Wert1 aufweist, dann erscheint Ausgabe1 statt dem eigentlichen Wert der Spalte in der Anzeige. Wenn Spalte1 einen Wert von Wert2 hat, dann wird Ausgabe2 anstelle des tatsächlichen Werts der Spalte angezeigt. Wenn Spalte1 irgendeinen anderen Wert als Wert1 oder Wert2 enthält, erscheint Ausgabe3 anstelle des tatsächlichen Spaltenwerts.

Wie wäre es mit ein paar Beispielen? Zuerst führen wir ein einfaches SELECT auf einer neuen Tabelle aus:

SQL> select * from staaten;

ST
--
IN
FL
KY
IL
OH
CA
NY

7 Zeilen ausgewählt.

Nun kommt der Befehl DECODE zum Einsatz:

SQL> select decode(staat,'IN','INDIANA','ANDERER') staat
2 from staaten;

STAAT
-------
INDIANA
ANDERER
ANDERER
ANDERER
ANDERER
ANDERER
ANDERER

7 Zeilen ausgewählt.

Nur eine Zeile erfüllt die Bedingung STAAT gleich IN, so daß nur eine Zeile als INDIANA erscheint. Die anderen Staaten nehmen den Standardwert an und werden demnach als ANDERER angezeigt.

Das nächste Beispiel liefert Ausgabestrings für alle Werte der Tabelle. Trotzdem sollte man einen Standardwert ('ANDERER') vorsehen, falls in der Tabelle Staaten vorkommen, die nicht in der DECODE-Liste aufgeführt sind.

SQL> select decode(staat,'IN','INDIANA',
2 'FL','FLORIDA',
3 'KY','KENTUCKY',
4 'IL','ILLINOIS',
5 'OH','OHIO',
6 'CA','CALIFORNIA',
7 'NY','NEW YORK','ANDERER')
8 from staaten;

DECODE(STA
----------
INDIANA
FLORIDA
KENTUCKY
ILLINOIS
OHIO
CALIFORNIA
NEW YORK
7 Zeilen ausgewählt.

Das war fast zu einfach. Das nächste Beispiel führt die Tabelle LOHN2 ein. Anhand dieser Tabelle läßt sich die Leistung von DECODE besser demonstrieren.

SQL> col std_lohn hea 'LOHN|STUNDE' for 99.00
SQL> col datum_erh hea 'LETZTE|ERHÖHUNG'
SQL> select name, std_lohn, datum_erh
2 from lohn2;

LOHN LETZTE
NAME STUNDE ERHÖHUNG
-------------------- ------ --------
JOHN 12.60 01.01.96
JEFF 8.50 17.03.97
RON 9.35 01.10.96
RYAN 7.00 15.03.96
BRYAN 11.00 01.06.96
MARY 17.50 01.01.96
ELAINE 14.20 01.02.97

7 Zeilen ausgewählt.

Alle in der Tabelle LOHN2 aufgeführten Mitarbeiter sollen nun eine Gehaltserhöhung bekommen. War das Jahr der letzten Gehaltserhöhung 1996, berechnen wir eine Erhöhung um 10 Prozent. Liegt die letzte Gehaltserhöhung in 1997, gibt es 20 Prozent mehr. Außerdem zeigen wir den prozentualen Zuwachs für die einzelnen Mitarbeiter an.

SQL> col lohn_neu hea 'LOHN|NEU' for 99.00
SQL> col std_lohn hea 'LOHN|STUNDE' for 99.00
SQL> col datum_erh hea 'LETZTE|ERHÖHUNG'
SQL> select name, std_lohn, datum_erh,
2 decode(substr(datum_erh,7,2),'96',std_lohn * 1.2,
3 '97',std_lohn * 1.1) lohn_neu,
4 decode(substr(datum_erh,7,2),'96','20%',
5 '97','10%',null) ZUWACHS
6 from lohn2;

LOHN LETZTE LOHN
NAME STUNDE ERHÖHUNG NEU ZUW
-------------------- ------ -------- ------ ---
JOHN 12.60 01.01.96 15.12 20%
JEFF 8.50 17.03.97 9.35 10%
RON 9.35 01.10.96 11.22 20%
RYAN 7.00 15.03.96 8.40 20%
BRYAN 11.00 01.06.96 13.20 20%
MARY 17.50 01.01.96 21.00 20%
ELAINE 14.20 01.02.97 15.62 10%

7 Zeilen ausgewählt.

Gemäß der Ausgabe erhält jeder eine 20prozentige Gehaltserhöhung mit Ausnahme von Jeff und Elaine, die bereits 1997 bedacht wurden.

Datumsumwandlungen

Wenn Sie in Ihre Datumsanzeige einen Hauch von Klasse bringen wollen, können Sie mit der Funktion TO_CHAR das »Datumsbild« verändern. Das nächste Beispiel ermittelt zunächst das heutige Datum:

SQL> select sysdate
2 from dual;

SYSDATE
--------
15.12.97

Bei der Umwandlung eines Datums in einen Zeichenstring können Sie die Funktion TO_CHAR mit der folgenden Syntax verwenden:

TO_CHAR(sysdate,'Datumsbild')

Datumsbild gibt das gewünschte Aussehen des Datums an. Zu den gebräuchlichen Teilen des Datumsformats gehören:

Month Der aktuelle Monat in ausgeschriebener Form.
Mon Der aktuelle Monat in abgekürzter Form.
Day Der aktuelle Wochentag.
mm Die Zahl des aktuellen Monats.
yy Die letzen beiden Stellen des aktuellen Jahrs.
dd Der laufende Tag des Monats.
yyyy Das aktuelle Jahr.
ddd Der laufende Tag des Jahres seit dem 1. Januar.
hh Die aktuelle Stunde des Tages.
mi Die aktuelle Minute der Stunde.
ss Die aktuelle Sekunde der Minute.
a.m. Zeigt a.m. oder p.m. an.

Das Datumsbild kann auch Kommas und literale Strings enthalten, sofern man diese in Anführungszeichen (" ") einschließt. Probieren Sie auch den Einfluß der Groß-/Kleinschreibung in Month, Mon und Day aus.

SQL> col HEUTE for a40
SQL> select to_char(sysdate,'Day", der "dd"." Month yyyy') HEUTE
2 from dual;

HEUTE
----------------------------------------
Montag , der 15. Dezember 1997

Beachten Sie den Alias HEUTE im Befehl COLUMN.

SQL> col HEUTE hea 'Heute ist der' for a30
SQL> select to_char(sysdate,'ddd ". Tag des Jahres') HEUTE
2 from dual;

Heute ist der
------------------------------
349 . Tag des Jahres

Vor die fortlaufende Nummer des Tages können Sie auch das Jahr setzen und folgendes Datumsbild verwenden: 'yyddd'.

Nehmen wir an, daß Sie ein kleines Skript geschrieben und als tag gespeichert haben. Das nächste Beispiel lädt die Datei und zeigt die verschiedenen Teile der konvertierten Datumsinformationen an.

SQL> get tag

Zeile 10 abgeschnitten.
1 set echo on
2 col TAG for a10
3 col HEUTE for a25
4 col JAHR for a25
5 col UHRZEIT for a15
6 select to_char(sysdate,'Day') TAG,
7 to_char(sysdate,'dd"." Mon yyyy') HEUTE,
8 to_char(sysdate,'Year') Jahr,
9 to_char(sysdate,'hh:mi:ss a.m.') Zeit
10* from dual

Nun können Sie das Skript ausführen:

SQL> @day

SQL> set echo on
SQL> col TAG for a10
SQL> col HEUTE for a25
SQL> col JAHR for a25
SQL> col UHRZEIT for a15
SQL> select to_char(sysdate,'Day') TAG,
2 to_char(sysdate,'dd"." Mon yyyy') HEUTE,
3 to_char(sysdate,'Year') Jahr,
4 to_char(sysdate,'hh:mi:ss a.m.') Zeit
5 from dual;

TAG HEUTE JAHR ZEIT
---------- ------------------------- ------------------------- -----------
Montag 15. Dez 1997 Nineteen Ninety-Seven 05:30:14 PM

Durch den Befehl SET ECHO ON am Anfang der Datei wurde die gesamte Anweisung vor der Ausführung angezeigt. Das Beispiel teilt das System in vier Spalten auf und konvertiert das Datum in vier Formate.

Mit der Funktion TO_DATE kann man Text in ein Datumsformat umwandeln. Die Syntax ist grundsätzlich die gleiche wie bei TO_CHAR.

TO_DATE(Ausdruck, 'Datumsbild')

Probieren Sie die folgenden Beispiele aus:

SQL> select to_date('19970501','yyyymmdd') "Datum 1"
2 from dual;

Datum 1
--------
01.05.97

SQL> select to_date('05/01/97','mm"/"dd"/"yy') "Datum 2"
2 from dual;

Datum 2
--------
01.05.97

Beachten Sie die Anführungszeichen zur Kennzeichnung eines literalen Strings.

Eine Folge von SQL-Dateien ausführen

In einer SQL-Skript-Datei kann alles stehen, was sich in den SQL-Puffer an der Aufforderung SQL> eingeben läßt - sogar Befehle, die ein anderes SQL-Skript ausführen. Abbildung 20.4 zeigt eine Skript-Datei, die mit dem Befehl EDIT erstellt wurde. Die Datei enthält mehrere SQL-Anweisungen sowie Befehle zur Ausführung anderer SQL-Skripts.

SQL> edit main.sql

SQL> @main

Der Start von main.sql führt alle SQL-Befehle aus, die im Skript enthalten sind. Die Skripts abfrage1 bis abfrage5 werden entsprechend der aus Abbildung 20.4 ersichtlichen Reihenfolge ebenfalls ausgeführt.

Abbildung 20.4:
SQL-Skripts aus einem anderen SQL-Skript ausführen

Kommentare im SQL-Skript

In SQL*Plus kann man Kommentare in drei verschiedenen Formen in eine Datei aufnehmen:

-- plaziert einen Kommentar auf einer einzigen Zeile.
REMARK (Abkürzung REM) plaziert einen Kommentar auf einer einzigen Zeile.
/* */ plaziert einen Kommentar auf einer oder mehreren Zeilen.

Studieren Sie das folgende Beispiel:

SQL> input
1 REMARK Das ist ein Kommentar
2 -- Das ist ebenfalls ein Kommentar
3 REM
4 -- SET-Befehle
5 set echo on
6 set feedback on
7 -- SQL-Anweisung
8 select *
9 from artikel
10
SQL> save abfrage6

Mit dem folgenden Befehl können Sie sich die Kommentare in der SQL-Skript-Datei ansehen:

SQL> edit query6

Kompliziertere Berichte

Gönnen wir uns etwas Abwechslung. Mit den Konzepten, die Sie heute und in den vergangenen Tagen gelernt haben, können Sie nun eigene Berichte erstellen. Nehmen wir an, daß Sie bereits ein Skript namens bericht1.sql erstellt haben. Starten Sie das Skript, und beobachten Sie die Ergebnisse.

SQL> @bericht1

SQL> set echo on
SQL> set pagesize 50
SQL> set feedback off
SQL> set newpage 0
SQL> col art_name hea 'Artikel|Name' for a20 trunc
SQL> col einzelpreis hea 'Preis|Stück' for $99.99
SQL> col art_menge hea 'Menge' for 999
SQL> col gesamt for $99,999.99
SQL> spool bericht
SQL> compute sum of gesamt on kunde
SQL> compute sum of gesamt on report
SQL> break on report on kunde skip 1
SQL> select a.kunde, p.art_name, p.einzelpreis,
2 a.art_menge, (p.einzelpreis * a.art_menge) Gesamt
3 from auftraege a,
4 artikel p
5 where a.art_id = p.art_id
6 order by kunde
7 Eingabe wurde auf 1 Zeichen abgeschnitten
/
Artikel Preis
KUNDE Name Stück Menge GESAMT
------------------------------ -------------------- ------- ----- -----------
BOUTIQUE AM TURM MICKY-MAUS-LAMPE $29.95 50 $1,497.50
20ER PACKUNG STIFTE $1.99 10 $19.90
KAFFEEKANNE $6.95 10 $69.50
****************************** -----------
sum $1,586.90

MEIER-WERBUNG MICKY-MAUS-LAMPE $29.95 5 $149.75
20ER PACKUNG STIFTE $1.99 15 $29.85
SQL-BEFEHLSREFERENZ $29.99 10 $299.90
LEDERBRIEFTASCHE SCH $99.99 1 $99.99
LANDSCHAFTSKALENDER $10.50 22 $231.00
****************************** -----------
sum $810.49

TANKSTELLE NORD MICKY-MAUS-LAMPE $29.95 1 $29.95
LEDERBRIEFTASCHE SCH $99.99 5 $499.95
LEDERBRIEFTASCHE SCH $99.99 1 $99.99
20ER PACKUNG STIFTE $1.99 10 $19.90
20ER PACKUNG STIFTE $1.99 10 $19.90
****************************** -----------
sum $669.69

-----------
sum $3,067.08
SQL> spool off

Die eigentliche SQL-Anweisung wählt Daten aus zwei Tabellen aus und führt eine arithmetische Funktion aus. Die Anweisung verknüpft die beiden Tabellen in der WHERE-Klausel und sortiert nach dem Kundennamen. Das sind die grundlegenden Operationen. Darüber hinaus formatieren die SQL*Plus-Befehle das Datum in der gewünschten Weise. Die Befehle teilen den Bericht in Gruppen auf, führen Berechnungen auf jeder Gruppe durch und fassen den Bericht zusammen.

Zusammenfassung

Tag 20 erläutert die Oracle-Erweiterungen der Standard-SQL-Sprache. Die heute behandelten Befehle sind nur ein Teil dessen, was Ihnen in SQL*Plus zur Verfügung steht. Wenn Sie mit den Produkten von Oracle arbeiten, sollten Sie Ihre erworbenen Kenntnisse anhand der Datenbankdokumentation vertiefen und alle gebotenen Möglichkeiten ausprobieren. Mit SQL*Plus kann man nahezu alle Aufgaben realisieren, die in Berichten erforderlich sind, ohne daß man auf eine prozedurale Programmiersprache zurückgreifen muß. Auch wenn Sie nicht mit Oracle arbeiten, können Sie das heute Gelernte einsetzen, um mit den Möglichkeiten Ihrer Implementierung die Datenbankabfragen zu verbessern. Die meisten großen Datenbankprodukte bieten Erweiterungen zur SQL-Standardsprache.

Fragen & Antworten

Frage:

Warum sollte ich meine Zeit mit dem Erlernen von SQL*Plus verbringen, wenn ich die gleichen Ergebnisse mit einfachem SQL erreichen kann?

Antwort:

Wenn Ihre Anforderungen an Berichte einfacher Natur sind, ist SQL tatsächlich ausreichend. Man kann aber die Zeit, die man auf das Erstellen von Berichten verwendet, per SQL*Plus reduzieren. Außerdem können Sie davon ausgehen, daß die Ansprüche Ihrer Kunden an die Berichte zunehmen.

Frage:

Wieso kann ich SYSDATE aus der Tabelle DUAL auswählen, obwohl es sich nicht um eine Spalte handelt?

Antwort:

Man kann aus DUAL oder irgendeiner anderen gültigen Tabelle auswählen, weil SYSDATE eine Pseudospalte darstellt.

Frage:

Wenn ich mit dem Befehl DECODE arbeite, kann ich dann auch DECODE innerhalb eines anderen DECODE verwenden?

Antwort:

Ja. Man kann DECODE aus einem anderen DECODE heraus aufrufen. In SQL lassen sich Funktionen auf anderen Funktionen ausführen, um die gewünschten Ergebnisse zu erhalten.

Workshop

Kontrollfragen

1. Mit welchen Befehlen lassen sich die Vorzugseinstellungen für eine SQL-Sitzung modifizieren?

2. Kann man im SQL-Skript einen Benutzer zur Eingabe eines Parameters auffordern und die SQL-Anweisung mit dem eingegebenen Parameter ausführen?

3. Wie würde man die Daten für einen Bericht gruppieren, wenn man einen zusammenfassenden Bericht auf den Einträgen einer KUNDEN-Tabelle erstellt?

4. Gibt es inhaltliche Einschränkungen bezüglich der Datei LOGIN.SQL?

5. Richtig oder falsch: Die Funktion DECODE ist das Äquivalent zu einer Schleife in einer prozeduralen Programmiersprache.

6. Richtig oder falsch: Wenn man die Ausgabe einer Abfrage in eine vorhandene Datei umleitet, wird die Ausgabe an diese Datei angefügt.

Übungen

1. Verwenden Sie die am Beginn der heutigen Lektion eingeführte Tabelle ARTIKEL. Schreiben Sie eine Abfrage, die alle Daten auswählt und alle in den Bericht zurückgegebenen Datensätze zählt, ohne den Befehl SET FEEDBACK ON zu verwenden.

2. Nehmen wir an, es ist Montag, der 12. Mai 1998. Schreiben Sie eine Abfrage, die die folgende Ausgabe liefert:

Heute ist Montag, der 12. Mai 1998

Verwenden Sie für diese Übung die folgende SQL-Anweisung:

1 select *
2 from auftraege
3 where kunden_id = '001'
4* order by kunden_id;

3. Ändern Sie - ohne erneutes Eintippen in den SQL-Puffer - die in der FROM-Klausel angegebene Tabelle in die Tabelle KUNDE.

4. Fügen Sie dann DESC an die ORDER BY-Klausel an.