Oracle9i Die umfassende Referenz - ReadingSample - Beck-Shop

Oracle9i Die umfassende Referenz
von
Hans Hajer, Kevin Loney, George Koch
1. Auflage
Hanser München 2003
Verlag C.H. Beck im Internet:
www.beck.de
ISBN 978 3 446 22170 3
Zu Inhaltsverzeichnis
schnell und portofrei erhältlich bei beck-shop.de DIE FACHBUCHHANDLUNG
CARL HANSER VERLAG
Kevin Loney, George Koch
Oracle9i Die umfassende Referenz
3-446-22170-0
www.hanser.de
3
3
Grundlegende Sprachelemente
von SQL
Mit Hilfe der Structured Query Language bzw. SQL teilen Sie Oracle mit, welche Informationen Sie auswählen (select), einfügen (insert), aktualisieren (update) oder löschen (delete) möchten. Tatsächlich handelt es sich bei diesen Verben um die vier
wichtigsten Wörter, mit denen Sie Oracle Anweisungen erteilen können. Seit Oracle9i
können Sie inserts und updates mit einem einzigen Befehl ausführen: merge.
In Kapitel 1 wurde Ihnen die Bedeutung des Begriffs „relational“ erklärt, wie Tabellen
in Spalten und Zeilen organisiert sind und wie man Oracle instruiert, bestimmte Spalten aus einer Tabelle auszuwählen und die Informationen zeilenweise anzuzeigen. In
diesem und den folgenden Kapiteln erfahren Sie, wie man diese Aktionen für die anderen Datentypen ausführt, die von Oracle unterstützt werden. Darüber hinaus erlernen
Sie auch die Interaktion mit SQL*PLUS, einem leistungsfähigen Oracle-Produkt: Das
Programm überprüft die Richtigkeit der Eingabe, übergibt diese an Oracle und verändert die von Oracle erhaltenen Antworten nach den von Ihnen erhaltenen Vorgaben.
Die Anwendung interagiert mit Ihnen, was bedeutet, dass Sie mit der Applikation „sprechen“ können und sie „antwortet“ Ihnen. Sie können Anweisungen geben, die präzise
befolgt werden. Sollte es Verständigungsprobleme geben, wird Ihnen das mitgeteilt.
Auf den ersten Blick mag es etwas verwirrend sein, dass man zuerst die Funktionsweise
von SQL*PLUS und Oracle kennen lernen muss. Hinzu kommt noch, dass die OracleFehlermeldungen über SQL*PLUS übergeben werden. Aber im Verlauf des Buchs
werden Sie merken, wo die eigentlichen Unterschiede liegen. Für den Anfang können
Sie SQL*PLUS als eine Art Assistenten betrachten, der Ihre Anweisungen befolgt und
Sie bei der schnelleren Erledigung Ihrer Arbeiten unterstützt. Sie interagieren mit diesem Assistenten über die Tastatur.
Wenn Sie die in diesem und den folgenden Kapiteln aufgeführten Beispiele ausführen,
sollten die Antworten der Oracle- und SQL*PLUS-Programme wie die dargestellten
48
3
Grundlegende Sprachelemente von SQL
Antworten aussehen. Sie müssen auf jeden Fall sicherstellen, dass die in diesem Buch
verwendeten Tabellen auch in Ihre Oracle-Datenbank geladen wurden. Sämtliche Definitionen und die dazugehörigen Beispieldaten finden Sie auf der beiliegenden CD.
Wir gehen davon aus, dass dieser Vorgang abgeschlossen ist. Rufen Sie SQL*PLUS auf
und beginnen Sie mit der Arbeit:
sqlplus
(Falls Sie SQL*PLUS auf einem Client-Rechner starten möchten, wählen Sie die
Menüoption für die Oracle-Software, gehen ins Menü Application Development und
selektieren das Programm SQL*Plus). Damit starten Sie SQLPLUS. (Beachten Sie,
dass Sie den * in der Mitte des offiziellen Produktnamens nicht eingeben, ab sofort
schreiben wir SQLPLUS ohne Sternchen). Da Oracle genauestens darauf achtet, wer
auf die gespeicherten Daten zugreifen kann, benötigt man für die Verbindung mit
Oracle immer eine Benutzerkennung und ein Passwort. Oracle zeigt beim Start eine
Copyright-Nachricht an und fragt die Benutzerkennung plus Passwort ab. Um auf die
in diesem Buch beschriebenen Tabellen zugreifen zu können, geben Sie sowohl bei der
Benutzerkennung als auch beim Passwort practice ein. Von SQLPLUS erhalten Sie daraufhin die Meldung, dass Sie mit Oracle verbunden sind. Sie sehen folgende Eingabeaufforderung:
SQL>
Jetzt befinden Sie sich in SQLPLUS, und das Programm wartet auf Ihre Anweisungen.
Scheitert der Befehl, kann dafür einer der folgenden Gründe verantwortlich sein: Oracle ist nicht in Ihren Pfad eingebunden, Sie besitzen nicht die Berechtigung zur Ausführung von SQLPLUS, oder Oracle wurde auf Ihrem Rechner nicht richtig installiert.
Wenn Sie folgende Meldung erhalten:
ERROR: ORA-1017: Benutzername/Kennwort ungültig; Anmeldung abgewiesen
bedeutet dies, dass Sie entweder den Benutzernamen oder das Passwort falsch eingegeben haben, oder dass Ihre Kennung noch nicht eingerichtet wurde. Nach drei erfolglosen Versuchen bricht SQLPLUS die Anmeldungen mit folgender Meldung ab:
Kann nach drei Versuchen keine Verbindung zu Oracle aufbauen, SQL*Plus
wird beendet
Erhalten Sie diese Meldung, informieren Sie den Systemadministrator. Nehmen wir
einmal an, dass alles in Ordnung ist und die Eingabeaufforderung korrekt angezeigt
wird. Dann steht der Arbeit mit SQLPLUS nichts mehr im Wege.
Wenn Sie die Arbeit mit SQLPLUS beenden und das Programm verlassen möchten,
geben Sie diesen Befehl ein:
quit
49
3.1 Die Formate
3.1
Die Formate
Zuerst einige Anmerkungen zu den Formaten. SQLPLUS kümmert sich nicht darum,
ob Sie die Anweisungen in Groß- oder Kleinbuchstaben eingeben. Der Befehl
SeLeCt feaTURE, section, PAGE FROM newsPaPeR;
führt zum gleichen Ergebnis wie
select Feature, Section, Page from NEWSPAPER;
Die Groß- und Kleinschreibung spielt nur dann eine Rolle, wenn Oracle Werte innerhalb der Datenbank auf Gleichheit überprüft. Wenn Sie Oracle anweisen, nach einer
Zeile zu suchen, in der Section = ’f’ ist, aber Section ist gleich ’F’, findet Oracle das Gesuchte unter Umständen nicht (da f und F nicht identisch sind). Andernfalls ist die
Groß- und Kleinschreibung völlig irrelevant. (Nebenbei bemerkt, der hier verwendete
Buchstabe ’F’ wird als Literal bezeichnet. Das bedeutet, Sie möchten Section nach dem
Buchstaben ’F’ und nicht nach einer Spalte namens F durchsuchen lassen. Die einfachen Anführungszeichen auf beiden Seiten des Buchstabens teilen Oracle mit, dass es
sich um ein Literal und nicht um einen Spaltennamen handelt.)
Damit der Text leichter lesbar ist, werden in diesem Buch einige Konventionen eingehalten:
■ select, from, where, order by, having und group by werden immer in Kleinbuchstaben und fett gedruckt dargestellt.
■ Auch die SQLPLUS-Befehle werden immer klein und fett gedruckt dargestellt: column, set, save, ttitle usw.
■ IN, BETWEEN, UPPER und andere SQL-Operatoren und -Funktionen werden in
Großbuchstaben und fett gedruckt dargestellt.
■ Spaltennamen werden am Anfang großgeschrieben: Feature, EastWest, Longitude
usw.
■ Tabellennamen werden durchgängig großgeschrieben: NEWSPAPER,
WEATHER, LOCATION usw.
3.2
Die NEWSPAPER-Tabelle anlegen
Die Beispiele in diesem Buch basieren auf den Tabellen, die von den Skripten aus dem
Web (http://www.hanser.de/buch/2003/3-446-22170-0.htm) angelegt werden. Jede
Tabelle wird mit dem Befehl create table eingerichtet, der die Namen der Spalten in
der Tabelle und die dazugehörigen Charakteristiken definiert. Nachfolgend finden Sie
den create table-Befehl für die NEWSPAPER-Tabelle, die in zahlreichen Beispielen
verwendet wird:
50
3
Create table
Feature
Section
Page
);
Grundlegende Sprachelemente von SQL
NEWSPAPER (
VARCHAR2(15) not null,
CHAR(1),
NUMBER
In späteren Kapiteln erfahren Sie, wie die Klausel dieses Befehls zu interpretieren ist.
Für den Moment soll Folgendes genügen: „Lege eine Tabelle namens NEWSPAPER
ein. Sie besteht aus den drei Spalten namens Feature (einer Spalte mit variabler Länge
und alphanumerischem Inhalt), Section (einer Spalte fixer Länge und alphanumerischem Inhalt) und Page (einer numerischen Spalte). Die Werte in der Feature-Spalte
können bis zu 15 Zeichen lang sein, und jede Zeile muss für Feature mindestens einen
Wert enthalten. Die Werte für Section sind alle 1 Zeichen lang.
In den folgenden Kapiteln werden Sie erfahren, wie man diesen einfachen Befehl erweitert und Integritätsregeln, Indizes und storage-Klauseln einfügt. Jetzt halten wir die
NEWSPAPER-Tabelle betont einfach, damit die Beispiele SQL fokussieren können
3.3
Mit SQL Daten aus Tabellen selektieren
Tabelle 3-1 zeigt die Rubriken einer Zeitung.
Tabelle 3-1: Die Rubriken einer Zeitung.
Beitrag (Feature)
Rubrik (Section)
Seite (Page)
Births
F
7
Bridge
B
2
Business
E
1
Classified
F
8
Comics
C
4
Doctor Is In
F
6
Editorials
A
12
Modern Life
B
1
Movies
B
4
National News
A
1
Obituaries
F
6
51
3.3 Mit SQL Daten aus Tabellen selektieren
Tabelle 3-1: Die Rubriken einer Zeitung. (Fortsetzung)
Beitrag (Feature)
Rubrik (Section)
Seite (Page)
Sports
D
1
Television
B
7
Weather
C
2
Falls es sich um eine Oracle-Tabelle handelt, würde SQL nach der Eingabe des folgenden Befehls dieses Listing ausgeben:
select Feature, Section, Page from NEWSPAPER;
FEATURE
--------------National News
Sports
Editorials
Business
Weather
Television
Births
Classified
Doctor Is In
Modern Life
Comics
Movies
Bridge
Obituaries
S
PAGE
- ---------A
1
D
1
A
12
E
1
C
2
B
7
F
7
F
8
F
6
B
1
C
4
B
4
B
2
F
6
14 rows selected.
Wo liegen nun die Unterschiede zwischen der Tabelle, die Sie anlegten, und der Darstellung in Tabelle 3-1? Beide Tabellen enthalten dieselben Informationen. Der Unterschied ist nur das Format. So sind z.B. die Überschriften über den Spalten unterschiedlich.
In der Spalte namens Section wird als Überschrift nur der Buchstabe 'S' angezeigt, und
obwohl Sie bei der Abfrage Groß- und Kleinbuchstaben verwendet haben,
select Feature, Section, Page from NEWSPAPER;
werden sämtliche Spaltenüberschriften in Großbuchstaben dargestellt.
Diese Änderungen sind das Ergebnis der Annahmen, die SQLPLUS zur Darstellung
der Information trifft. Sie können diese Einstellungen zwar ändern, doch solange Sie
keine anderen Vorgaben machen, ändert SQLPLUS die von Ihnen gemachten Eingaben wie folgt:
52
3
Grundlegende Sprachelemente von SQL
■ Die Spaltenüberschriften werden in Großbuchstaben angezeigt.
■ Die Spalten sind nur so breit, wie sie in Oracle definiert wurden.
■ Wenn die Spaltenüberschrift eine Funktion ist, werden sämtliche Leerstellen unterdrückt (dieses Verhalten wird in Kapitel 7 besprochen).
Der erste Punkt ist offensichtlich. Die Spaltennamen werden in Großbuchstaben umgewandelt. Beim zweiten Punkt können durchaus die ersten Fragen auftauchen. Wie
sind die Spalten in Oracle definiert? Um das herauszufinden, stellen Sie Oracle mit
SQLPLUS eine einfache Abfrage:
describe NEWSPAPER
Name
Null?
------------------------------- -------FEATURE
NOT NULL
SECTION
PAGE
Type
------VARCHAR2(15
CHAR(1)
NUMBER
Dieses Listing ist eine beschreibende Tabelle, in der sämtliche Spalten und deren Definitionen für die NEWSPAPER-Tabelle angezeigt werden. Der describe-Befehl funktioniert bei allen Tabellen.
Die erste Spalte führt die Namen der Spalten auf, die innerhalb der Tabelle beschrieben sind.
Die zweite Spalte, Null?, stellt eine Regel für die Spalte dar, deren Name auf der linken
Seite steht. Wurde diese Spalte mit der Eingabe NOT NULL angelegt, weiß Oracle, dass
niemand irgendwelche Informationen eingeben darf, wenn die Feature-Spalte leer geblieben ist (NULL bedeutet leer). Natürlich hätte es sich in einer Tabelle wie NEWSPAPER vielleicht gelohnt, die gleiche Regel auf alle drei Spalten anzuwenden. Welchen
Sinn hat es, den Titel eines Beitrags zu kennen, wenn man nicht weiß, unter welcher Rubrik und auf welcher Seite er zu finden ist? Aber in diesem Beispiel wurde diese spezielle
Regel nur bei Feature eingesetzt.
Da bei Section und Page die NOT NULL-Anweisung fehlt, dürfen diese Spalten in der
NEWSPAPER-Tabelle leer bleiben.
Die dritte Spalte (Type) sagt etwas über die Natur jeder einzelnen Spalte aus. Feature
ist eine VARCHAR2 (variable length character)-Spalte, die bis zu 15 Zeichen (Buchstaben, Zahlen, Symbole oder Leerzeichen) aufnehmen kann.
Auch Section ist eine Spalte, in der Zeichen abgestellt werden können, wobei sie allerdings nur ein Zeichen lang ist! Beim Anlegen der Tabelle wusste der Anwender bereits,
dass die Rubriken in der lokalen Zeitung lediglich einen Buchstaben lang sind. Deshalb
wurde die Spalte nur so groß wie nötig definiert. Wenn SQLPLUS die Ergebnisse der
folgenden Abfrage darstellt
3.3 Mit SQL Daten aus Tabellen selektieren
53
select Feature, Section, Page from NEWSPAPER;
hat das Programm über Oracle erfahren, dass Section maximal ein Zeichen lang ist.
SQLPLUS geht davon aus, dass Sie zur Anzeige dieser Spalte nicht mehr Platz verschwenden möchten, und zeigt infolgedessen nur ein 'S' an.
Die dritte Spalte in der NEWSPAPER-Tabelle ist Page, die aus einer Zahl besteht. Wie
Sie sicher bemerkt haben, ist die Page-Spalte in der NEWSPAPER-Tabelle 10 Stellen
breit, obwohl es keine Seitenzahl gibt, die mehr als zwei Zahlen aufweist. Der Grund
dafür ist, dass Zahlen üblicherweise ohne maximale Länge definiert werden, deshalb
zeigt SQLPLUS anfangs das Maximum an.
Vielleicht haben Sie auch bemerkt, dass die Überschrift der einzigen Spalte, die nur aus
Zahlen besteht, rechtsbündig formatiert ist. In diesem Fall steht die Überschrift auf der
rechten Seite der Spalte, während die restlichen Überschriften jeweils linksbündig angeordnet sind. Diese Ausrichtung der Spaltenüberschriften ist bei SQLPLUS der Standard. Diese Einstellungen können Sie später (siehe Kapitel 6) nach Belieben verändern.
Schließlich teilt Ihnen Oracle noch mit, wie viele Spalten in der NEWSPAPER-Tabelle
gefunden wurden. (Beachten Sie am Ende der Anzeige die Meldung „14 rows selected“. Diese Meldung bezeichnet man als Feedback. Soll SQLPLUS diese feedback-Option ausschalten, geben Sie folgenden Befehl ein:
set feedback off
Alternativ können Sie eine minimale Anzahl von Zeilen angeben, die vor der Ausgabe
eines Feedbacks vorhanden sein sollen:
set feedback 25
Mit diesem Befehl teilen Sie Oracle mit, dass Sie keine Meldung erhalten möchten, solange nicht mindestens 25 Zeilen vorhanden sind. Geben Sie SQLPLUS keine diesbezüglichen Anweisungen, ist feedback standardmäßig auf 6 eingestellt.
Der set-Befehl ist ein SQLPLUS-Befehl, über den man das Verhalten von SQLPLUS
steuert. In SQLPLUS gibt es eine Reihe von Optionen, die Sie vorgeben können. Einige
dieser Möglichkeiten werden Ihnen in diesem und den folgenden Kapiteln vorgestellt.
Eine vollständige Auflistung aller Optionen finden Sie in der Alphabetischen Referenz.
Für den set-Befehl gibt es ein Gegenstück namens show, mit dem Sie die an SQLPLUS
gegebenen Anweisungen nachvollziehen können. Die Einstellungen für feedback prüfen Sie beispielsweise wie folgt:
show feedback
SQLPLUS antwortet mit:
FEEDBACK ON for 25 or more rows
54
3
Grundlegende Sprachelemente von SQL
Auch die Breite der Darstellung lässt sich mit dem set-Befehl ändern. Die entsprechende Anweisung sieht wie folgt aus:
show numwidth
SQLPLUS antwortet mit:
numwidth 9
Da 9 Stellen für die Anzeige einer Seitenzahl, die niemals länger als zwei Ziffern ist, relativ viel sind, schränken Sie die Anzeige mit folgender Anweisung ein:
set numwidth 5
Diese Anweisung bedeutet jedoch auch, dass alle Zahlenspalten nur fünf Ziffern lang
sind. Wenn Sie wissen, dass manche Zahlen länger sind, muss dieser Wert natürlich
höher liegen. In Kapitel 5 erfahren Sie, wie man die Anzeige der einzelnen Spalten individuell gestaltet. In der Darstellung lassen sich auch individuelle Spalten setzen.
Mehr dazu in Kapitel 6.
3.4
select, from, where und order by
Zur Auswahl von Informationen aus einer Oracle-Tabelle verwenden Sie vier wichtige
Schlüsselwörter: select, from, where und order by. select und from setzen Sie grundsätzlich in jeder Oracle-Abfrage ein.
Das Schlüsselwort select teilt Oracle mit, welche Spalten Sie auswählen möchten, während Sie über from den/die Namen der Tabellen definieren, in denen sich diese Spalten
befinden. Das Beispiel mit der NEWSPAPER-Tabelle zeigte, wie diese Schlüsselwörter
eingesetzt werden. In der ersten Zeile gaben Sie nach jedem Spaltennamen, mit Ausnahme des letzten, ein Komma ein. Sie haben sicher bemerkt, dass sich eine korrekt
eingegebene SQL-Abfrage fast wie ein englischer Satz liest. Eine Abfrage in SQLPLUS
wird stets mit einem Semikolon (auch SQL-Terminator oder SQL-Abschlusszeichen genannt) abgeschlossen. Das Schlüsselwort where teilt Oracle mit, welche Qualifier in
die von Ihnen ausgewählten Informationen einzufügen sind. Ein Beispiel:
select Feature, Section, Page from NEWSPAPER
where Section = ’F’;
FEATURE
--------------Births
Classified
Obituaries
Doctor Is In
S PAGE
- ----F
7
F
8
F
6
F
6
3.4 select, from, where und order by
55
Oracle überprüft vor der Rückgabe jede Zeile in der NEWSPAPER-Tabelle. Die Zeilen,
bei denen in der Section-Spalte kein 'F' steht, werden übergangen, während die anderen zurückgeliefert und von SQLPLUS angezeigt werden.
Über order by teilen Sie Oracle mit, wie die zurückgegebenen Informationen zu sortieren sind. Die Reihenfolge, in der die einzelnen Elemente innerhalb der Anfrage aufgeführt werden, bleibt Ihnen überlassen. Sehen Sie sich dazu die folgenden Beispiele
an:
select Feature, Section, Page from NEWSPAPER
where Section = ’F’
order by Feature;
FEATURE
--------------Births
Classified
Doctor Is In
Obituaries
S PAGE
- ----F
7
F
8
F
6
F
6
Bei der Sortierung über Page wird die Reihenfolge fast umgekehrt:
select Feature, Section, Page from NEWSPAPER
where Section = ’F’
order by Page;
FEATURE
--------------Obituaries
Doctor Is In
Births
Classified
S PAGE
- ----F
6
F
6
F
7
F
8
Im nächsten Beispiel werden die Features über Page sortiert (im letzten Listing wurden
nur die Inhalte über Page sortiert). Durch die Anordnung über Feature steht der Eintrag „Doctor Is In“ jetzt vor „Obituaries“.
select Feature, Section, Page from NEWSPAPER
where Section = ’F’
order by Page, Feature;
FEATURE
--------------Doctor Is In
Obituaries
Births
Classified
S PAGE
- ----F
6
F
6
F
7
F
8
56
3
Grundlegende Sprachelemente von SQL
Mit order by lässt sich die normale Sortierfolge auch umkehren:
select Feature, Section, Page from NEWSPAPER
where Section = ’F’
order by Page desc, Feature;
FEATURE
--------------Classified
Births
Doctor Is In
Obituaries
S PAGE
- ----F
8
F
7
F
6
F
6
Das Schlüsselwort desc steht für absteigend. Da es dem Wort „Page“ in der order byZeile folgt, werden die Seitenzahlen in absteigender Reihenfolge angezeigt. Das gleiche
Ergebnis könnte man auch in der Feature-Spalte erreichen, wenn die order by-Zeile
dem Wort „Feature“ folgen würde.
Beachten Sie, dass die Wörter, die select, from, where und order by folgen, jeweils anders strukturiert werden. Diese Gruppen mit Wörtern und Schlüsselwörtern werden
Klauseln genannt. Beispiele dazu finden Sie in Abbildung 3-1.
Select Feature, Section, Page
<--select clause
from NEWSPAPER
<--from clause
where Section = ‘F'
<--where clause
Abbildung 3-1: Klauseln.
3.5
Logik und Wert
Genau wie die order by-Klausel besteht auch die where-Klausel aus verschiedenen
Elementen, wobei das Ganze bei where sehr viel ausgefeilter ist. Durch die Verwendung von logischen Instruktionen können Sie genau steuern, was Sie von Oracle zurückerhalten. Die Instruktionen werden über mathematische Symbole, so genannte
logische Operatoren, dargestellt. Nachfolgend werden diese Operatoren kurz erklärt.
Eine vollständige Übersicht finden Sie in der Alphabetischen Referenz.
Nachfolgend ein einfaches Beispiel für Logik und Wert, wobei die Werte in der PageSpalte daraufhin überprüft werden, ob sie gleich 6 sind. Jede Zeile, in der diese Bedingung zutrifft, wird an Sie zurückgegeben. Alle anderen Zeilen werden einfach übersprungen (die Zeilen, bei denen Page = 6 nicht zutrifft).
57
3.5 Logik und Wert
select Feature, Section, Page
from NEWSPAPER
where Page = 6;
FEATURE
--------------Obituaries
Doctor Is In
S PAGE
- ----F
6
F
6
Das Gleichheitszeichen ist deshalb ein logischer Operator, weil es einen logischen Vergleich der Werte durchführt, die sich auf beiden Seiten des Operators befinden – in
unserem Fall von Page und dem Wert 6 – und überprüft, ob die Werte gleich sind.
In diesem Beispiel wurden die zu prüfenden Werte nicht in Anführungszeichen gesetzt, da die Spalte, die den zu vergleichenden Wert enthält (die Page-Spalte) als Datentyp NUMBER definiert ist. Numerische Werte benötigen für den Vergleich keine
Anführungszeichen.
3.5.1
Test von Einzelwerten
Zum Test einzelner Werte stehen Ihnen verschiedene logische Operatoren zur Verfügung (siehe Überschrift „Logische Tests auf einzelne Werte“). Sehen Sie sich die Beispiele in dieser Liste an. Sie funktionieren alle ähnlich und können nach Belieben
kombiniert werden. Dennoch sind bestimmte Regeln zu beachten.
Equal, Greater Than, Less Than, Not Equal
Logische Tests können Werte miteinander vergleichen, sowohl auf Gleichheit als auch
auf einen relativen Wert. Hier ein einfaches Beispiel, bei dem alle Sections daraufhin
geprüft werden, ob sie gleich B sind:
select Feature, Section, Page
from NEWSPAPER
where Section = ’B’;
FEATURE
--------------Television
Modern Life
Movies
Bridge
S PAGE
- ----B
7
B
1
B
4
B
2
58
3
Grundlegende Sprachelemente von SQL
Logische Tests auf einzelne Werte
Die folgenden Operatoren funktionieren alle mit Buchstaben, Zahlen, Spalten oder Literalen.
Gleich, größer als, kleiner als, ungleich
Page=
6
Page ist gleich 6
Page>
6
Page ist größer als 6
Page>= 6
Page ist größer oder gleich 6
Page<
Page ist kleiner als 6
6
Page<= 6
Page ist kleiner oder gleich 6
Page!=
6
Page ist ungleich 6
Page^= 6
Page ist ungleich 6
Page<> 6
Page ist ungleich 6
Da bei einigen Tastaturen entweder das Ausrufezeichen (!) oder das Caret-Zeichen
(^) fehlt, stellt Oracle für die Darstellung des Ungleich-Operators drei Möglichkeiten
zur Verfügung. Die letzte Alternative (<>) kann deshalb als Ungleich-Operator verwendet werden, weil nur Zahlen zugelassen werden, die größer oder kleiner (wie in
unserem Beispiel) als 6 sind. Die 6 selbst ist nicht zugelassen.
LIKE
Feature LIKE ’Mo%’
Feature beginnt mit den Buchstaben Mo
Feature LIKE ’_ _ I%’
Feature hat in der dritten Position ein I
Feature LIKE ’%o%o%’
Feature enthält zwei O’s
LIKE führt einen Mustervergleich durch. Ein Unterstrich (_) repräsentiert eine Stelle.
Ein Prozentzeichen (%) repräsentiert beliebig viele Stellen oder Zeichen.
IS NULL, IS NOT NULL
Precipitation IS NULL
„Präzipitation ist unbekannt“
Precipitation IS NOT NULL „Präzipitation ist bekannt“
NULL überprüft, ob in einer Zeile oder Spalte Daten vorhanden sind. Ist eine Spalte
vollkommen leer, wird sie als NULL bezeichnet. Im Zusammenhang mit NULL und
NOT NULL ist das Wort IS einzusetzen: Gleichheitszeichen, „größer als“ oder „kleiner als“ funktionieren in diesem Falle nicht.
Nachfolgend ein Test für alle Seiten, die größer als 4 sind:
select Feature, Section, Page
from NEWSPAPER
where Page > 4;
59
3.5 Logik und Wert
FEATURE
--------------Editorials
Television
Births
Classified
Obituaries
Doctor Is In
S PAGE
- ----A
12
B
7
F
7
F
8
F
6
F
6
Hier werden alle Sections daraufhin geprüft, ob sie größer als B sind (im Alphabet hinter B kommen):
select Feature, Section, Page
from NEWSPAPER
where Section > ’B’;
FEATURE
--------------Sports
Business
Weather
Births
Classified
Comics
Obituaries
Doctor Is In
S PAGE
- ----D
1
E
1
C
2
F
7
F
8
C
4
F
6
F
6
Natürlich können Sie auch einen Test auf „kleiner als“ (in diesem Fall 8) durchführen:
select Feature, Section, Page
from NEWSPAPER
where Page < 8;
FEATURE
--------------National News
Sports
Business
Weather
Television
Births
Modern Life
Comics
Movies
Bridge
Obituaries
Doctor Is In
S
PAGE
- ---------A
1
D
1
E
1
C
2
B
7
F
7
B
1
C
4
B
4
B
2
F
6
F
6
Neben der Gleichheit können Sie auch auf Ungleichheit prüfen:
60
3
Grundlegende Sprachelemente von SQL
select Feature, Section, Page
from NEWSPAPER
where Page <> 1;
FEATURE
--------------Editorials
Weather
Television
Births
Classified
Comics
Movies
Bridge
Obituaries
Doctor Is In
S
PAGE
- ---------A
12
C
2
B
7
F
7
F
8
C
4
B
4
B
2
F
6
F
6
Hinweis:
Beim Einsatz der Operatoren „größer als“ oder“ kleiner als“ für den
Vergleich von Zahlen in Spalten, deren Inhalte als Zeichen definiert
sind, ist Vorsicht geboten. Alle Werte in den VARCHAR2- und
CHAR-Spalten werden beim Vergleich als Zeichen behandelt.
Deshalb werden die in solchen Spalten abgestellten Zahlen wie
alphanumerische Strings behandelt. Wenn der Datentyp der Spalte
NUMBER ist, dann ist 12 größer als 9. Falls Inhalte der gleichen
Spalte als Zeichen definiert sind, ist 9 größer als 12, weil das Zeichen
'9' größer als das Zeichen '1' ist.
3.5.2
LIKE
Eine der leistungsfähigsten logischen SQL-Funktionen ist der Operator LIKE, der für
den Vergleich von Mustern eingesetzt wird. LIKE kann sämtliche Zeilen innerhalb einer Datenbank auf Werte durchsuchen, die wie das beschriebene Muster aussehen.
Zur Definition der gewünschten Übereinstimmung stehen Ihnen zwei Sonderzeichen
zur Verfügung: ein Prozentzeichen wird als Wildcard und ein Unterstrich als Positionsmarker bezeichnet. Um innerhalb der Feature-Spalte nach den Einträgen zu suchen,
die mit den Buchstaben 'Mo' beginnen, verwenden Sie folgende Abfrage:
select Feature, Section, Page from NEWSPAPER
where Feature LIKE ’Mo%’;
FEATURE
--------------Modern Life
Movies
S
PAGE
- ---------B
1
B
4
61
3.5 Logik und Wert
Das Prozentzeichen (%) bedeutet, dass alles akzeptiert wird: ein Zeichen, hundert Zeichen oder keine Zeichen. Wenn die ersten Buchstaben ’Mo’ sind, findet LIKE den entsprechenden Eintrag. Falls man als Suchbedingung stattdessen ’MO%’ verwendet,
liefert Oracle keinen Wert zurück, da beim Vergleich auch die Groß- und Kleinschreibung beachtet wird. Wenn Sie alle Einträge in der Feature-Spalte finden möchten, bei
denen der dritte Buchstabe ein 'i' ist, und es unwichtig ist, wie die beiden vorausgehenden bzw. nachfolgenden Zeichen aussehen, können Sie über zwei Unterstriche (_ _)
festlegen, dass in diesen Positionen jedes Zeichen akzeptiert wird. Wichtig ist, dass auf
der dritten Position ein kleines 'i' steht: das nachgestellte Prozentzeichen besagt, dass
alles OK ist.
select Feature, Section, Page from NEWSPAPER
where Feature LIKE ’__i%’;
FEATURE
--------------Editorials
Bridge
Obituaries
S
PAGE
- ---------A
12
B
2
F
6
Es können auch mehrere Prozentzeichen verwendet werden. Um im Feature-Titel alle
Wörter zu finden, in denen irgendwo zwei kleine 'o's stehen, werden drei Prozentzeichen verwendet:
select Feature, Section, Page from NEWSPAPER
where Feature LIKE ’%o%o%’;
FEATURE
S
PAGE
--------------- - ---------Doctor Is In
F
6
Die folgende Abfrage ähnelt der letzten, mit dem Unterschied, dass in diesem Fall nach
zwei kleinen 'i's gesucht wird:
select Feature, Section, Page from NEWSPAPER
where Feature LIKE ’%i%i%’;
FEATURE
--------------Editorials
Television
Classified
Obituaries
S
PAGE
- ---------A
12
B
7
F
8
F
6
Dieses Leistungsmerkmal für den Mustervergleich ist ein wichtiges Element zur benutzerfreundlichen Gestaltung einer Anwendung. Man vereinfacht damit die Suche
nach Namen, Adressen, Produkten und anderen Dingen, an die man sich zumindest
bruchstückhaft erinnern kann.
62
3
Grundlegende Sprachelemente von SQL
NULL und NOT NULL
Die NEWSPAPER-Tabelle besitzt keine Spalten, die NULL sind, obwohl die Anzeige
über describe zeigt, dass dies sehr wohl möglich wäre. Die nachfolgende COMFORTTabelle enthält neben anderen Daten die Niederschlagsmengen für San Francisco, California, und Keene, New Hampshire, an einigen Tagen im Jahr 2001.
select City, SampleDate, Precipitation
from COMFORT;
CITY
------------SAN FRANCISCO
SAN FRANCISCO
SAN FRANCISCO
SAN FRANCISCO
KEENE
KEENE
KEENE
KEENE
SAMPLEDAT PRECIPITATION
--------- ------------21-MAR-01
.5
22-JUN-01
.1
23-SEP-01
.1
22-DEC-01
2.3
21-MAR-01
4.4
22-JUN-01
1.3
23-SEP-01
22-DEC-01
3.9
Mit der folgenden Abfrage finden Sie die Städte und Daten, bei denen keine Messungen vorgenommen wurden:
select City, SampleDate, Precipitation
from COMFORT
where Precipitation IS NULL;
CITY
SAMPLEDAT PRECIPITATION
------------- --------- ------------KEENE
23-SEP-01
IS NULL bedeutet im Grunde genommen, dass Oracle nach Elementen suchen soll,
bei denen keine Daten vorhanden sind. Sie wissen nicht, ob der Wert für diesen Tag 0,
1 oder 5 Inches sein soll. Deshalb wird der Wert in dieser Spalte nicht auf 0 gesetzt,
sondern sie bleibt einfach leer. Über NOT finden Sie auch die Städte und Datumsangaben, für die irgendwelche Daten vorhanden sind:
select City, SampleDate, Precipitation
from COMFORT
where Precipitation IS NOT NULL;
CITY
------------SAN FRANCISCO
SAN FRANCISCO
SAN FRANCISCO
SAN FRANCISCO
KEENE
KEENE
KEENE
SAMPLEDAT PRECIPITATION
--------- ------------21-MAR-01
.5
22-JUN-01
.1
23-SEP-01
.1
22-DEC-01
2.3
21-MAR-01
4.4
22-JUN-01
1.3
22-DEC-01
3.9
63
3.5 Logik und Wert
Oracle lässt die Verwendung der relationalen Operatoren (=, !=, usw.) mit NULL zu,
aber solche Vergleiche ergeben keine besonders aussagekräftigen Ergebnisse. Um
Werte auf NULL abzuprüfen, sollten Sie mit IS oder IS NOT arbeiten.
3.5.3
Einfache Tests auf Wertelisten
Neben der Möglichkeit, einzelne Werte über logische Operatoren zu testen, können
Sie solche Tests auch mit mehreren Werten durchführen. In der Übersicht „Logische
Tests auf Wertelisten“ finden Sie eine Auswahl solcher Operatoren.
Logische Tests auf Wertelisten
Mit Zahlen:
Page IN (1,2,3)
Page ist in der Liste (1,2,3)
Page NOT IN (1,2,3)
Page ist nicht in der Liste (1,2,3)
Page BETWEEN 6 AND 10
Page ist gleich 6, 10 oder einem Wert, der dazwischen liegt
Page NOT BETWEEN 6 AND 10
Page ist kleiner als 6 oder größer als 10
Mit Buchstaben (oder Zeichen):
Section IN ('A','C','F')
Section ist in der Liste ('A', 'C', 'F')
Section NOT IN ('A', 'C', 'F')
Section ist nicht in der Liste ('A', 'C', 'F')
Section BETWEEN 'B' AND 'D'
liegt (alphabetisch)
Section ist gleich 'B', 'D' oder einem Wert, der dazwischen
Section NOT BETWEEN 'B' AND 'D' Section ist kleiner als 'B' oder größer als 'D' (im Alphabet)
Hier einige Beispiele zum Einsatz dieser Operatoren:
select Feature, Section, Page
from NEWSPAPER
where Section IN (’A’,’B’,’F’);
FEATURE
--------------National News
Editorials
Television
Births
Classified
Modern Life
Movies
Bridge
Obituaries
Doctor Is In
S
PAGE
- ---------A
1
A
12
B
7
F
7
F
8
B
1
B
4
B
2
F
6
F
6
64
3
Grundlegende Sprachelemente von SQL
select Feature, Section, Page
from NEWSPAPER
where Section NOT IN (’A’,’B’,’F’);
FEATURE
--------------Sports
Business
Weather
Comics
S
PAGE
- ---------D
1
E
1
C
2
C
4
select Feature, Section, Page
from NEWSPAPER
where Page BETWEEN 7 and 10;
FEATURE
--------------Television
Births
Classified
S
PAGE
- ---------B
7
F
7
F
8
Diese logischen Tests lassen sich auch kombinieren:
select Feature, Section, Page
from NEWSPAPER
where Section = ’F’
AND Page > 7;
FEATURE
S
PAGE
--------------- - ---------Classified
F
8
Mit dem AND-Befehl wurden zwei logische Ausdrücke kombiniert. Oracle wird gezwungen, in jeder Zeile beide Tests durchzuführen: Damit Sie eine Zeile zurückerhalten, müssen die Bedingungen „Section = 'F'“ und „Page > 7“ erfüllt sein. Alternativ
kann man auch OR einsetzen. In diesem Fall werden alle Zeilen zurückgeliefert, bei denen einer der beiden logischen Ausdrücke zutrifft:
select
from
where
OR
Feature, Section, Page
NEWSPAPER
Section = ’F’
Page > 7;
FEATURE
--------------Editorials
Births
Classified
Obituaries
Doctor Is In
S PAGE
- ----A
12
F
7
F
8
F
6
F
6
65
3.5 Logik und Wert
Es wurden einige Sections ausgegeben, deren Inhalte ungleich ’F’ sind, weil deren Page
größer als 7 ist. Bei anderen Seiten, die kleiner oder gleich 7 sind, wurde eine Übereinstimmung mit 'F' gefunden.
Mit der letzten Abfrage werden die Features in Section 'F' selektiert, deren Seitenzahl
zwischen 7 und 10 liegt:
select
from
where
and
Feature, Section, Page
NEWSPAPER
Section = ’F’
Page BETWEEN 7 AND 10;
FEATURE
--------------Births
Classified
S PAGE
- ----F
7
F
8
Es gibt einige mehrwertige Operatoren, deren Verwendung etwas komplexer ist. Sie
werden in Kapitel 8 besprochen. Weitere Hinweise auf diese Operatoren finden Sie in
der Alphabetischen Referenz.
3.5.4
Logiken kombinieren
Sowohl AND als auch OR folgen der landläufigen Bedeutung des Worts. Sie lassen sich
im Prinzip beliebig kombinieren, wobei etwas Vorsicht geboten ist, da die ANDs und
ORs ineinander verschachtelt sein können.
Angenommen, Sie möchten in der Zeitung die Features finden, die von den Redakteuren eher vernachlässigt werden. Diese Beiträge befinden sich normalerweise auf den
letzten Seiten von Section A oder B. Vielleicht probieren Sie es so:
select Feature, Section, Page
from NEWSPAPER
where Section = ’A’
or Section = ’B’
and Page > 2;
FEATURE
--------------National News
Editorials
Television
Movies
S PAGE
- ----A
1
A
12
B
7
B
4
Das von Oracle zurückgelieferte Ergebnis entspricht nicht Ihren Vorstellungen. Irgendwie ist auch die Seite 1 der Section A hineingerutscht. Was ist passiert? Gibt es
eine Möglichkeit, dass Oracle die korrekte Antwort findet? Obwohl AND und OR beide logische Konnektoren sind, ist AND stärker. Es bindet die logischen Ausdrücke auf
66
3
Grundlegende Sprachelemente von SQL
beiden Seite stärker als Oracle (technisch ausgedrückt, hat AND eine höhere Priorität).
Damit wird die where-Klausel
where Section = ’A’
or Section = ’B’
and Page > 2;
wie folgt interpretiert: „wo Section = 'A', oder wo Section = 'B' und Page > 2“. Wenn
Sie sich das gescheiterte Beispiele etwas genauer ansehen, werden Sie feststellen, wie
diese Interpretation das Ergebnis beeinflusst: AND wird immer als Erstes ausgeführt.
Sie können diese Disposition durch die Verwendung von Klammern durchbrechen.
Sie schließen die Ausdrücke ein, die gemeinsam zu interpretieren sind. Die Klammern
überschreiben die normalen Prioritätsvorgaben:
select Feature, Section, Page
from NEWSPAPER
where Page > 2
and ( Section = ’A’
or Section = ’B’ );
FEATURE
-----------------Editorials
Television
Movies
S PAGE
- ---A
12
B
7
B
42
Das Ergebnis entspricht genau dem, was Sie anfänglich eigentlich wollten. Bei der Eingabe dieser Anweisungen werden Sie feststellen, dass das Ergebnis exakt das Gleiche
ist: die Klammern teilen Oracle mit, was zusammen interpretiert werden muss. Vergleichen Sie das Ergebnis mit den ersten drei Beispielen, bei denen die Klammern nicht
eingesetzt wurden:
select Feature, Section, Page
from NEWSPAPER
where ( Section = ’A’
or Section = ’B’ )
and Page > 2;
FEATURE
--------------Editorials
Television
Movies
S PAGE
- ----A
12
B
7
B
4
3.6 Ein weiteres Einsatzgebiet für where: Unterabfragen
3.6
67
Ein weiteres Einsatzgebiet für where:
Unterabfragen
Was geschieht, wenn die logischen Operatoren unter den Überschriften „Logische
Tests auf einzelne Werte“ und „Logische Tests auf Wertelisten“ nicht nur für die Überprüfung eines einzelnen Literal-Werts (z.B. 'F') oder einer Liste von Werten (z.B. 4,2,7
oder 'A','C','F'), sondern auch für Werte verwendet werden können, die von einer
Oracle-Abfrage zurückgeliefert werden? Tatsächlich handelt es sich hierbei um ein
mächtiges Leistungsmerkmal von SQL.
Angenommen, Sie sind der Autor des Beitrags „Doctor Is In“, und jede Zeitung, die
Ihre Spalte publiziert, gibt gleichzeitig ein Inhaltsverzeichnis heraus, in dem Ihr Beitrag enthalten ist. Natürlich beurteilt jeder Lektor Ihre Wichtigkeit ein wenig anders
und stellt Ihr Werk dementsprechend in einer anderen Rubrik ab. Wie sieht die Abfrage aus, mit der man herausfinden kann, mit welchen anderen Beiträgen Ihr Kommentar platziert wurde? Sie können es wie folgt probieren:
select Section from NEWSPAPER
where Feature = ’Doctor Is In’;
S
F
Das Ergebnis ist 'F'. Damit können Sie die folgende Abfrage stellen:
select FEATURE from NEWSPAPER
where Section = ’F’;
FEATURE
--------------Births
Classified
Obituaries
Doctor Is In
Ihr Beitrag befindet sich unter Geburten, Todesfällen und Kleinanzeigen. Gibt es auch
eine Möglichkeit, um die beiden Abfragen in einer einzigen zusammenzufassen? Natürlich:
select FEATURE from NEWSPAPER
where Section = (select Section from NEWSPAPER
where Feature = ’Doctor Is In’);
68
3
Grundlegende Sprachelemente von SQL
FEATURE
--------------Births
Classified
Obituaries
Doctor Is In
3.6.1
Einzelne Werte aus einer Unterabfrage
Die in Klammern gesetzte select-Anweisung (Unterabfrage genannt) gab einen einzelnen Wert zurück: F. Die Hauptabfrage behandelt das F, als ob es sich um das Literal 'F'
aus der vorherigen Abfrage handelt. Beachten Sie, dass das Gleichheitszeichen ein Test
auf Einzelwerte ist. Hätte Ihre Unterabfrage also mehr als eine Zeile zurückgeliefert,
erhielten Sie eine Fehlermeldung:
select * from NEWSPAPER
where Section = (select Section from NEWSPAPER
where Page = 1);
ERROR: ORA-1427: Unterabfrage für eine Zeile liefert mehr als eine Zeile
Alle logischen Operatoren, die einen einzelnen Wert testen, lassen sich in Unterabfragen verwenden, solange die Unterabfrage nur eine einzige Zeile zurückliefert. So können Sie z.B. alle Beiträge (Features) in der Zeitung abfragen, deren Section kleiner
(innerhalb des Alphabets) als diejenige ist, in der Ihre Spalte enthalten ist. Der Stern in
der select-Anweisung zeigt eine Möglichkeit zur Abfrage aller Spalten innerhalb einer
Tabelle, ohne sie einzeln aufführen zu müssen. Die Spalten werden in der Reihenfolge
angezeigt, in der sie in der Tabelle angelegt wurden.
select * from NEWSPAPER
where Section < (select Section from NEWSPAPER
where Feature = ’Doctor Is In’);
FEATURE
--------------National News
Sports
Editorials
Business
Weather
Television
Modern Life
Comics
Movies
Bridge
S PAGE
- ----A
1
D
1
A
12
E
1
C
2
B
7
B
1
C
4
B
4
B
2
10 rows selected.
Zehn andere Beiträge stehen in dieser Lokalzeitung vor Ihrem medizinischen Rat.
3.6 Ein weiteres Einsatzgebiet für where: Unterabfragen
3.6.2
69
Werteliste aus einer Unterabfrage
In einer Unterabfrage können Sie auch die mehrwertigen Operatoren verwenden.
Wenn eine Unterabfrage mehr als eine Zeile zurückliefert, werden die Spaltenwerte für
jede einzelne Zeile in einer Liste aufgeführt. Angenommen, Sie möchten alle Städte
und Länder wissen, wo es bewölkt ist. Sie besitzen eine Tabelle mit den vollständigen
Wetterinformationen für alle Städte und eine LOCATION-Tabelle, in der alle Städte
und die entsprechenden Länder aufgeführt sind:
select City, Country from LOCATION;
CITY
-------------------------ATHENS
CHICAGO
CONAKRY
LIMA
MADRAS
MANCHESTER
MOSCOW
PARIS
SHENYANG
ROME
TOKYO
SYDNEY
SPARTA
MADRID
COUNTRY
--------------------------GREECE
UNITED STATES
GUINEA
PERU
INDIA
ENGLAND
RUSSIA
FRANCE
CHINA
ITALY
JAPAN
AUSTRALIA
GREECE
SPAIN
select City, Condition from WEATHER;
CITY
----------LIMA
PARIS
MANCHESTER
ATHENS
CHICAGO
SYDNEY
SPARTA
CONDITION
----------RAIN
CLOUDY
FOG
SUNNY
RAIN
SNOW
CLOUDY
Zuerst ermitteln Sie alle Städte, in denen es bewölkt ist:
select City from WEATHER
where Condition = ’CLOUDY’;
CITY
----------PARIS
SPARTA
70
3
Grundlegende Sprachelemente von SQL
Danach bauen Sie aus diesen Städten eine Liste auf und fragen damit die LOCATIONTabelle ab:
select City, Country from LOCATION
where City IN (’PARIS’, ’SPARTA’);
CITY
-------------------------PARIS
SPARTA
COUNTRY
--------------------------FRANCE
GREECE
Dieselbe Aufgabe lässt sich auch mit einer Unterabfrage erledigen, wobei die in Klammern gesetzte select-Anweisung eine Liste mit Städten aufbaut, die vom IN-Operator
getestet wird:
select City, Country from LOCATION
where City IN (select City from WEATHER
where Condition = ’CLOUDY’);
CITY
-------------------------PARIS
SPARTA
COUNTRY
--------------------------FRANCE
GREECE
Die anderen mehrwertigen Operatoren arbeiten ganz ähnlich. Zu den grundlegenden
Aufgaben gehört das Erstellen einer Unterabfrage, mit der eine Liste aufgebaut wird,
die sich logisch testen lässt. Nachfolgend einige relevante Punkte:
■ Die Unterabfrage darf entweder nur eine Spalte haben oder muss die ausgewählten
Spalten mit mehreren, in Klammern gestellten Spalten in der Hauptabfrage vergleichen (siehe Kapitel 12).
■ Die Unterabfrage muss in Klammern stehen.
■ Unterabfragen, die nur eine Zeile produzieren, lassen sich sowohl mit ein- als auch
mit mehrwertigen Operatoren einsetzen.
■ Unterabfragen, die mehr als eine Zeile produzieren, können nur mit mehrwertigen Operatoren einsetzen.
3.7
Tabellen kombinieren
Sobald Sie Ihre Daten normalisiert haben, müssen Sie unter Umständen zwei oder
mehr Tabellen kombinieren, um alle gewünschten Informationen zu erhalten.
Nehmen wir an, Sie wären das Orakel von Delphi. Die Athener möchten sich bei Ihnen
nach den Naturgewalten erkundigen, die Einfluss auf den erwarteten Angriff der Spar-
3.7 Tabellen kombinieren
71
taner haben könnten. Zudem möchten sie wissen, aus welcher Richtung das Unwetter
gegebenenfalls zu erwarten ist:
select City, Condition, Temperature from WEATHER;
CITY
----------LIMA
PARIS
MANCHESTER
ATHENS
CHICAGO
SYDNEY
SPARTA
CONDITION
TEMPERATURE
----------- ----------RAIN
45
CLOUDY
81
FOG
66
SUNNY
97
RAIN
66
SNOW
29
CLOUDY
74
Da Ihre Geografiekenntnisse in den letzten Jahren etwas gelitten haben, fragen Sie die
LOCATION-Tabelle ab:
select City, Longitude, EastWest, Latitude, NorthSouth
from LOCATION;
CITY
LONGITUDE E LATITUDE N
------------------------- --------- - -------- ATHENS
23.43 E
37.58 N
CHICAGO
87.38 W
41.53 N
CONAKRY
13.43 W
9.31 N
LIMA
77.03 W
12.03 S
MADRAS
80.17 E
13.05 N
MANCHESTER
2.15 W
53.3 N
MOSCOW
37.35 E
55.45 N
PARIS
2.2 E
48.52 N
SHENYANG
123.3 E
41.48 N
ROME
12.29 E
41.54 N
TOKYO
139.5 E
35.42 N
SYDNEY
151.1 E
33.52 S
SPARTA
22.27 E
37.05 N
MADRID
3.14 W
40.24 N
Sie haben jetzt zwar mehr Daten erhalten als benötigt, aber leider noch überhaupt keine Wetterinformationen. Allerdings haben die beiden Tabellen WEATHER und LOCATION eine gemeinsame Spalte: City. Deshalb können Sie die Informationen aus
beiden Tabellen zusammenführen. Lediglich über die where-Klausel teilen Sie Oracle
die Gemeinsamkeiten der beiden Tabellen mit (das Beispiel ähnelt dem in Kapitel 1):
select WEATHER.City, Condition, Temperature, Latitude,
NorthSouth, Longitude, EastWest
from WEATHER, LOCATION
where WEATHER.City = LOCATION.City;
72
3
CITY
----------ATHENS
CHICAGO
LIMA
MANCHESTER
PARIS
SPARTA
SYDNEY
Grundlegende Sprachelemente von SQL
CONDITION
TEMPERATURE LATITUDE N LONGITUDE E
----------- ----------- -------- - --------- SUNNY
97
37.58 N
23.43 E
RAIN
66
41.53 N
87.38 W
RAIN
45
12.03 S
77.03 W
FOG
66
53.3 N
2.15 W
CLOUDY
81
48.52 N
2.2 E
CLOUDY
74
37.05 N
22.27 E
SNOW
29
33.52 S
151.1 E
Beachten Sie, dass in dieser kombinierten Tabelle nur Zeilen mit Städten enthalten, die
in beiden Tabellen vorkommen. Die where-Klausel führt Ihre Logik genau wie im Falle
der NEWSPAPER-Tabelle aus. Diese Logik sagt Folgendes: „Wähle in der WEATHERund der LOCATION-Tabelle die Zeilen aus, in denen die Städte gleich sind“. Ist eine
Stadt nur in einer der Tabellen vorhanden, gibt es in der anderen Tabelle keine Übereinstimmung. Die in der select-Anweisung eingesetzte Notation ist „TABLE.SpaltenName“ – in unserem Fall WEATHER.City.
Die select-Klausel hat aus den beiden Tabellen alle Zeilen ausgewählt, die Sie gerne sehen möchten. Andere Zeilen wurden einfach ignoriert. Hätte man in der ersten Zeile
folgende Aussage getroffen
select City, Condition, Temperature, Latitude
wüsste Oracle nicht, auf welche Stadt (City) Sie sich beziehen, in diesem Fall erhalten
Sie von Oracle eine entsprechende Fehlermeldung. Der korrekte Wortlaut in der select-Klausel ist „WEATHER.City“ oder „LOCATION.City“. In unserem Beispiel wäre
es egal, welche dieser beiden Varianten verwendet wird. Sie möchten jedoch die Fälle
herausgreifen, in denen die Auswahl über identisch benannte Spalten erfolgt, die in
zwei oder mehr Tabellen vorkommen und völlig verschiedene Daten enthalten.
Die where-Klausel benötigt außer den Tabellennamen den identischen Spaltennamen,
über den die Tabellen kombiniert werden: „wo weather Punkt city ist gleich location
Punkt city“. Gesucht wird also nach Elementen in der City-Spalte, die in der WEATHER- und der LOCATION-Tabelle identisch sind.
Beachten Sie, dass die Kombination aus beiden Tabellen wie eine einzige Tabelle mit
sieben Spalten und sieben Zeilen aussieht. Ausgeschlossene Elemente werden nicht angezeigt. So fehlt z.B. die Humidity-Spalte, obwohl sie in der WEATHER-Tabelle vorhanden ist. Das Gleiche gilt für die Country-Spalte in der LOCATION-Tabelle. Und
von den 14 Städten in der LOCATION-Tabelle wurden nur solche übernommen, die
auch in der WEATHER-Tabelle vorhanden sind. Ihre where-Klausel sorgte dafür, dass
die anderen Elemente nicht berücksichtigt wurden.
Eine Tabelle, die aus den Spalten einer oder mehrerer Tabellen aufgebaut wurde, bezeichnet man als eine Projektion oder Ergebnistabelle.
73
3.8 Eine View anlegen
3.8
Eine View anlegen
Eine View sieht nicht nur wie eine neue Tabelle aus, Sie können ihr auch einen Namen
geben und sie tatsächlich wie eine Tabelle behandeln. Diesen Vorgang bezeichnet man
als Anlegen einer View. Mit einer View lässt sich die Logik verbergen, mit deren Hilfe
man die angezeigte Tabelle zusammengefügt hat. Das funktioniert so:
create view INVASION AS
select WEATHER.City, Condition, Temperature, Latitude,
NorthSouth, Longitude, EastWest
from WEATHER, LOCATION
where WEATHER.City = LOCATION.City;
View created.
Jetzt können Sie INVASION wie eine normale Tabelle behandeln und sich von Oracle
sogar eine Beschreibung ausgeben lassen:
describe INVASION
Name
Null?
------------------------------- -------CITY
CONDITION
TEMPERATURE
LATITUDE
NORTHSOUTH
LONGITUDE
EASTWEST
Type
---VARCHAR2(11)
VARCHAR2(9)
NUMBER
NUMBER
CHAR(1)
NUMBER
CHAR(1)
Sie können auch eine Abfrage stellen. (Sie müssen nicht angeben, aus welcher Tabelle
die City-Spalten stammen, da die Logik innerhalb der View verborgen bleibt.)
select City, Condition, Temperature, Latitude, NorthSouth,
Longitude, EastWest
from INVASION;
CITY
----------ATHENS
CHICAGO
LIMA
MANCHESTER
PARIS
SPARTA
SYDNEY
CONDITION
TEMPERATURE LATITUDE N LONGITUDE E
----------- ----------- -------- - --------- SUNNY
97
37.58 N
23.43 E
RAIN
66
41.53 N
87.38 W
RAIN
45
12.03 S
77.03 W
FOG
66
53.3 N
2.15 W
CLOUDY
81
48.52 N
2.2 E
CLOUDY
74
37.05 N
22.27 E
SNOW
29
33.52 S
151.1 E
Im Zusammenhang mit einer View können einige, wenn auch wenige Oracle-Funktionen nicht verwendet werden, z.B. solche zum Ändern von Zeilen und zum Indizieren
74
3
Grundlegende Sprachelemente von SQL
von Tabellen. Ansonsten verhält sich eine View wie eine normale Tabelle und kann
entsprechend manipuliert werden.
Hinweis
Views enthalten keine Daten, Tabellen hingegen schon.
Seit Oracle8i können Sie „materialisierte Views“ anlegen,
die Daten enthalten. Sie sind allerdings echte Tabellen,
keine Views.
Angenommen, Sie stellen jetzt fest, dass Sie keine Informationen über Chicago oder
andere Städte außerhalb von Griechenland benötigen. In diesem Fall ändern Sie einfach die Abfrage. Funktioniert die Folgende?
select City, Condition, Temperature, Latitude, NorthSouth,
Longitude, EastWest
from INVASION
where Country = ’GREECE’;
SQLPLUS liefert diese Oracle-Fehlermeldung zurück:
where Country = ’GREECE’
*
ERROR in Zeile 4: ORA-0704:
ungültiger Spaltenname
Warum? Obwohl es sich bei Country um eine Spalte in einer der Tabellen hinter der
INVASION-View handelt, wurde sie beim Aufbau der View nicht in die select-Klausel
aufgenommen. Infolgedessen wird die Spalte behandelt, als sei sie überhaupt nicht
vorhanden. Deshalb müssen Sie die create view-Anweisung ändern und beziehen nur
Griechenland mit ein.
create or replace view INVASION as
select WEATHER.City, Condition, Temperature, Latitude,
NorthSouth, Longitude, EastWest
from WEATHER, LOCATION
where WEATHER.City = LOCATION.City
and Country = ’GREECE’;
View created.
Mit dem Befehl create or replace view erstellen Sie eine neue Version der View, ohne
die alte View zu löschen. Der Befehl erleichtert die Verwaltung der Benutzerberechtigungen für den Zugriff auf die View (siehe Kapitel 19).
Die Logik der where-Klausel wurde so erweitert, dass nun zwei Tabellen zusammengeführt werden und in einer Spalte dieser Tabellen ein Test auf einen Wert durchgeführt wird. Übergeben Sie diese Abfrage an Oracle, erhalten Sie folgende Antwort:
75
3.8 Eine View anlegen
select City, Condition, Temperature, Latitude, NorthSouth,
Longitude, EastWest
from INVASION;
CITY
----------ATHENS
SPARTA
CONDITION
TEMPERATURE LATITUDE N LONGITUDE E
----------- ----------- -------- - --------- SUNNY
97
37.58 N
23.43 E
CLOUDY
74
37.05 N
22.27 E
Damit können Sie die Athener warnen und ihnen mitteilen, dass die Spartaner wahrscheinlich aus Südwesten kommen und vom Marsch überhitzt und müde sein werden.
Mit ein wenig Trigonometrie können Sie Oracle sogar ausrechnen lassen, wie weit die
Truppen marschiert sind. Das alte Orakel von Delphi war hinsichtlich der Vorhersagen immer etwas mehrdeutig, und hätte ungefähr Folgendes gesagt: „Einer wird gewinnen“. Im Gegensatz dazu können Sie wenigstens einige Fakten anbieten.
3.8.1
Die View erweitern
Die Möglichkeit, die Daten innerhalb der Views zu schützen oder zu ändern, kann oft
nützlich sein. Auf der Grundlage von einfachen Views lassen sich sehr komplexe Reports aufbauen, und die Berechtigungen bestimmter Personen oder Gruppen lassen
sich so einschränken, dass sie nur bestimmte Elemente der Tabelle zu sehen bekommen.
Tatsächlich können jegliche Einschränkungen, die im Rahmen einer Abfrage definiert
werden, auch Bestandteil einer View werden. So können Sie beispielsweise definieren,
dass Abteilungsleiter nur Einsicht in ihre eigene Gehaltsabrechnung und die ihrer Mitarbeiter erhalten. Wichtig ist allerdings, Views sind keine Momentaufnahmen zu irgendeinem Zeitpunkt der Vergangenheit. Sie sind dynamisch und geben immer die
aktuellen Daten aus den zu Grunde liegenden Tabellen wieder. Sobald sich die Daten
innerhalb einer Tabelle ändern, werden die mit dieser Tabelle generierten Views sofort
aktualisiert.
Angenommen, Sie haben eine View angelegt, die die Werte auf der Basis von Spaltenwerten einschränkt. Wie nachfolgend gezeigt wird, lässt sich eine Abfrage, die die LOCATION-Tabelle in der Country-Spalte einschränkt, dazu nutzen, die Anzahl der
Zeilen einzuschränken, die über die View sichtbar sind:
create or replace view PERU_LOCATIONS as
select * from LOCATION
where Country = ’PERU’;
Ein Anwender, der eine Abfrage über die PERU_LOCATIONS stellt, kann lediglich die
Zeilen sehen, die sich auf Peru beziehen.
76
3
Grundlegende Sprachelemente von SQL
Die Abfragen zur Definition von Views können auch Pseudospalten referenzieren. Bei
einer Pseudospalte handelt es sich um eine „Spalte“, die einen ausgewählten Wert zurückgibt, aber keine aktuelle Spalte in einer Tabelle ist. So würde die Pseudospalte User
immer den Benutzernamen des Oracle-Anwenders zurückliefern, der die Abfrage ausführt. Wenn also eine Spalte in der Tabelle Benutzernamen enthält, können diese
Werte mit der Pseudospalte User verglichen werden. Damit lässt sich die Anzahl der
Spalten reduzieren (siehe folgendes Listing). In unserem Fall wird die Tabelle NAME
abgefragt. Ist der Wert in der Name-Spalte identisch mit dem Namen des Benutzers,
der die Abfrage stellt, werden diese Zeilen zurückgeliefert:
create or replace view RESTRICTED_NAMES
select * from NAME
where Name = User;
Solche Views sind besonders hilfreich, wenn Anwender auf ausgewählte Zeilen innerhalb einer Tabelle zugreifen möchten. In diesem Zusammenhang lässt sich verhindern, dass Benutzer auf Zeilen zugreifen, die nicht mit ihren Oracle-Benutzernamen
übereinstimmen.
Views sind äußerst leistungsfähige Werkzeuge. Mehr dazu in Kapitel 18.
Mit der where-Klausel lassen sich zwei Tabellen auf der Basis einer gemeinsamen Spalte verknüpfen. Das Ergebnis kann in eine View (mit eigenem Namen) umgewandelt
werden, die wiederum als eigenständige Tabelle behandelt wird. Die eigentliche Stärke
einer View liegt in ihrer Fähigkeit, die Ansicht der Daten für Benutzer zu begrenzen
oder zu ändern, wobei die Daten in den zu Grunde liegenden Tabellen davon nicht berührt werden.