Umgang mit DB2 - Fakultät für Mathematik und Informatik

3 Benutzung von D B 2
25
3 Benutzung von D B 2
3.1 Grundlegende Konzepte
System
Instance(s)
Database(s)
Database partition group(s)
Table space
tables
index(es)
long data
Figure 3. Relationships Among Some Database Objects
AbbildungSchemas:
3.1: Übersicht der Struktur einer D B 2–Installation
A schema is an identifier, such as a user ID, that helps group tables and other
database
objects.
A schema can be (System)
owned by anläßt
individual,
ownerbeschreiben:
can
Die Struktur einer D B 2–Installation auf
einem
Rechnersystem
sich and
wiethefolgt
Es sind eine
control access to the data and the objects within it.
oder mehrere Instanzen (Exemplare, Instances)
vorhanden. Diese sind völlig getrennt voneinander und es gibt keinen
schema is entspricht
also an object das
in theeiner
database.
It may be created
automatically Installation von D B 2 auf
instanzübergreifenden Datenzugriff. ImA Prinzip
mehrfachen
vollständigen
when the first object in a schema is created. Such an object can be anything
diesem Rechnersystem. Die einzelnen Instanzen
können
individuell
verwaltet
und
konfiguriert
werden.
that can be qualified by a schema name, such as a table, index, view, package,
Jede Instanz kann mehrere Datenbanken (Databases) enthalten. Eine Datenbankpartition ist ein Teil einer Datenbank
bestehend aus Daten, Indexstrukturen,
und Log–Dateien.
6 AdministrationKonfigurations–
Guide: Planning
Eine Datenbank kann auf nur eine Datenbankpartition oder auf mehrere Datenbankpartitionen aufgeteilt (partitioniert)
sein. Diese werden dann zu Datenbankpartitionsgrupppen (Database partition groups) zusammengefaßt. Partitionierte
Datenbanken können auf mehrere Prozessoren oder Rechnersysteme verteilt werden. Dann können (transparent für den
Benutzer) Anfragen und Datenmanipulationen automatisch parallelisiert werden.
Ein Tabellenbereich (Table Space) ist ein Objekt, das der physischen Datenhaltung dient und auf verschiedene Speichermedien verteilt sein kann. Jedes Datenbankobjekt (Tabelle, Index, LOB–Daten) wird genau einem Tabellenbereich
zugeordnet.
3 Benutzung von D B 2
26
3.2 Grundsätzliches
Im Praktikum wird momentan IBM D B 2 Universal Database“ in der Version 8.1 auf dem Rechner
”
sunpower.informatik.uni-tuebingen.de verwendet. Um mit dem DBMS zu arbeiten, muß man sich stets
auf sunpower einloggen (mittels ssh/slogin).
Die Dokumentation zu D B 2, das D B 2 Information Center“, steht unter der URL
”
http://sunpower.informatik.uni-tuebingen.de/internes/db2/htmlcd/en US/index.htm
zur Verfügung. Um das D B 2 Information Center“ sinnvoll verwenden zu können, benötigt man einen aktuellen Browser
”
(z.B. Netscape 7.x oder Mozilla 1.x).
Jede(r) Praktikumsteilnehmer(in) erhält in der Instanz db2inst1 eine Datenbank, die wie sein/ihr (UNIX–)Benutzername heißt und auf der er/sie das Datenbankadministrator–Recht erhält.
3.3 Der Command Line Processor (CLP)
Innerhalb des Praktikums wird oft der zeilenorientierte Command Line Processor (CLP) von D B 2 benutzt, um S QL–
Anweisungen und Anfragen ausführen zu lassen.
Um die D B 2–Software und insbesondere den CLP nutzen zu können, muß man folgende Zeilen in ˜/.login oder
˜/.cshrc aufnehmen (Syntax für csh/tcsh):
setenv DB2DIR /opt/IBM/db2/V8.1
setenv DB2INSTANCE db2inst1
setenv INSTHOME /db2/home/db2inst1
set path = ($path ${INSTHOME}/sqllib/bin ${INSTHOME}/sqllib/adm ${INSTHOME}/sqllib/misc)
Diese Einstellungen sollten jedoch bereits automatisch vorgenommen werden (siehe auch Hinweise zu den Rechnern
”
im Datenbankpraktikum“).
Der CLP wird durch die Eingabe von db2 gestartet. Er erlaubt sowohl den Batchbetrieb als auch die interaktive Eingabe.
In der Umgebungsvariable DB2OPTIONS kann man Default–Befehlszeilenoptionen für den CLP angeben (siehe CLP–
Dokumentation).
Setzt man die Umgebungsvariable LANG auf den Wert de, so erhält man deutsche Meldungen im CLP.
3.3.1 Wichtige Befehle des CLP
• Der Befehl CONNECT TO Name veranlaßt den CLP, eine Verbindung zur Datenbank Name aufzubauen. DISCONNECT CURRENT beendet hingegen die aktuelle Verbindung zur Datenbank.
• Mit TERMINATE beendet man eine CLP–Sitzung. Nicht QUIT verwenden, denn dies beendet zwar den CLP,
nicht aber die Verbindung zur Datenbank!
• Zeilen, die mit -- beginnen, werden als Kommentare betrachtet und vom CLP ignoriert.
• Durch die Eingabe von ? Befehl erhält man Hilfe zum jeweiligen Befehl. Ebenso kann man zu den Warnungen und
Fehlern ausführliche Beschreibungen erhalten, wenn man ein Fragezeichen gefolgt vom jeweiligen Code eingibt
(Beispiel: ? SQL0104N).
3 Benutzung von D B 2
27
Ruft man den CLP mit der Option -t auf, so wird nicht das Zeilenende, sondern der Strichpunkt (;) verwendet, um
das Ende einer Anweisung zu erkennen (nützlich zur Eingabe von Befehlen oder S QL–Anweisungen, die über mehrere
Zeilen gehen).
Der CLP unterscheidet in Befehlen und S QL–Anweisungen nicht zwischen Groß– und Kleinschreibung.
3.4 Benutzerkonzept und Berechtigungsstufen (Instanz)
Das (für das Praktikum verwendete) Authentisierungskonzept von D B 2 sieht keine speziellen Datenbankbenutzer(namen) vor, sondern verwendet das Benutzerkonzept und die Authentisierungsmechanismen des Betriebssystems.
Auf einer D B 2–Instanz gibt es drei Berechtigungsstufen für Benutzer:
• SYSADM — Zugriff auf alle Daten und Ressourcen der Instanz
• SYSCTRL — berechtigt zum Verwalten der Instanz; kein direkter Zugriff auf Daten möglich
• SYSMAINT — weniger Berechtigungen als SYSCTRL; berechtigt z.B. dazu, Backups zu erstellen oder den Betrieb einer Datenbank zu beobachten (monitoring)
3.5 Verwalten von Datenbankberechtigungen
Berechtigungen auf einer (der aktuellen) Datenbank werden in S QL mit dem GRANT– und dem REVOKE–Statement
verwaltet. Mittels GRANT werden Berechtigungen (Rechte) erteilt, mittels REVOKE wieder entzogen.
So gibt es verschiedene Rechte, die festlegen, was ein Benutzer oder eine Benutzergruppe mit der Datenbank machen
darf. Diese Rechte und ihre Bedeutung kann man der folgenden Tabelle entnehmen:
Recht
BINDADD
CONNECT
CREATETAB
CREATE EXTERNAL ROUTINE
CREATE NOT FENCED ROUTINE
IMPLICIT SCHEMA
DBADM
LOAD
QUIESCE CONNECT
Bedeutung
Erzeugung neuer Packages
Verbinden zur Datenbank (Benutzen von CONNECT)
Anlegen von Tabellen
Registrieren externer Routinen
Registrieren von Routinen, die in der Prozeßumgebung des DBMS laufen
Implizite Schemaerzeugung
Datenbankadministrator–Recht
Benutzung der LOAD–Utility
Zugriff auf die Datenbank, solange sie sich im Zustand quiesce“ befindet
”
Aus Kompatibilitätsgründen kann man statt CREATE NOT FENCED ROUTINE auch CREATE NOT FENCED (wie in
älteren Versionen) verwenden.
Erteilen von Datenbankberechtigungen mit dem GRANT–Statement:
.-,-----------------------------.
V
|
>>-GRANT----+-BINDADD-------------------+-+--ON DATABASE-------->
+-CONNECT-------------------+
+-CREATETAB-----------------+
+-CREATE_EXTERNAL_ROUTINE---+
3 Benutzung von D B 2
28
+-CREATE_NOT_FENCED_ROUTINE-+
+-IMPLICIT_SCHEMA-----------+
+-DBADM---------------------+
+-LOAD----------------------+
’-QUIESCE_CONNECT-----------’
.-,---------------------------------.
V
|
>--TO----+-+-------+--authorization-name-+-+-------------------><
| +-USER--+
|
| ’-GROUP-’
|
’-PUBLIC------------------------’
authorization-name ist hierbei der Name eines Benutzers (UNIX–Benutzername) oder einer Benutzergruppe
(UNIX–Gruppenname); PUBLIC steht für alle Benutzer.
Bemerkungen:
• Ein Benutzer (eine Gruppe), dem (der) das DBADM–Recht verliehen wird, erhält automatisch auch alle anderen
Berechtigungen auf der Datenbank.
• Das Recht DBADM kann nicht an PUBLIC vergeben werden.
• Während es zur Erteilung der meisten Rechte ausreicht, die Berechtigung DBADM oder die Berechtigungsstufe
SYSADM zu besitzen, muß man für die Erteilung des DBADM–Rechts die Berechtigungsstufe SYSADM besitzen.
• Die Rechte gelten immer nur für die Datenbank, zu der gerade eine Verbindung besteht; insbesondere muß beim
Absetzen dieses Statements eine Verbindung zu einer Datenbank existieren.
Jede(r) Praktikumsteilnehmer(in) erhält für seine/ihre Datenbank (d. h. für die Datenbank, die wie sein/ihr (UNIX–)Benutzername heißt) die Berechtigung DBADM.
Entziehen von Datenbankberechtigungen mit dem REVOKE–Statement:
.-,-----------------------------.
V
|
>>-REVOKE----+-BINDADD-------------------+-+--ON DATABASE------->
+-CONNECT-------------------+
+-CREATETAB-----------------+
+-CREATE_EXTERNAL_ROUTINE---+
+-CREATE_NOT_FENCED_ROUTINE-+
+-IMPLICIT_SCHEMA-----------+
+-DBADM---------------------+
+-LOAD----------------------+
’-QUIESCE_CONNECT-----------’
.-,---------------------------------.
V
| .-BY ALL-.
>--FROM----+-+-------+--authorization-name-+-+--+--------+-----><
| +-USER--+
|
| ’-GROUP-’
|
’-PUBLIC------------------------’
• Wird einem Benutzer (einer Gruppe) das DBADM–Recht entzogen, so behält dieser Benutzer (diese Gruppe) evtl.
andere vorhandene Rechte auf der Datenbank; sie werden nicht automatisch entzogen.
3 Benutzung von D B 2
29
• Um die Berechtigung DBADM zu entziehen, ist die Berechtigungsstufe SYSADM notwendig; beim Entziehen der
anderen Berechtigungen genügt es, die Berechtigung DBADM oder die Berechtigungsstufe SYSADM zu besitzen.
• Wird einem Benutzer (einer Gruppe) ein Recht entzogen, so muß dies nicht unbedingt bedeuten, daß er (sie) dieses
Recht danach nicht mehr besitzt. Er (sie) könnte dieses Recht auch noch durch Zugehörigkeit zu einer Gruppe,
die dieses Recht besitzt, besitzen oder von einem anderen Benutzer erhalten haben.
Beispiele:
• Folgendes Statement gibt allen Benutzern das Recht, sich mit der (aktuellen) Datenbank zu verbinden:
GRANT CONNECT ON DATABASE TO PUBLIC
• Folgendes Statement gibt dem Benutzer Karl die Rechte zum Verbindungsaufbau mit der Datenbank und zum
Anlegen von Tabellen:
GRANT CONNECT, CREATETAB ON DATABASE TO USER Karl
• Mit einem Statement kann man auch mehreren Benutzern oder Benutzergruppen Rechte erteilen:
GRANT CONNECT, IMPLICIT SCHEMA TO USER Karl, USER Gerd, GROUP Students
• Entziehen des Rechtes zum Verbindungsaufbau für die Gruppe PUBLIC:
REVOKE CONNECT ON DATABASE FROM PUBLIC
Die aktuellen Datenbankberechtigungen kann man sich mit dem CLP–Kommando GET AUTHORIZATIONS anzeigen
lassen.
4 Ausdrücke, Prädikate, Suchbedingungen
30
4 Ausdrücke, Prädikate, Suchbedingungen
In diesem Kapitel werden grundlegende Sprachelemente, wie Ausdrücke, Prädikate und Suchbedingungen, kurz vorgestellt, die in S QL an vielen Stellen verwendet werden. Deutlich detaillierter sind diese Sprachelemente im Manual
beschrieben.
4.1 Ausdrücke
Ausdrücke (expressions) werden verwendet, um Werte zu spezifizieren. Sie besitzen die folgende Syntax:
expression:
.-operator--------------------------------.
V
|
|----+-----+--+-function-------------------+-+------------------|
+- + -+ +-(expression)---------------+
’- - -’ +-constant-------------------+
+-column-name----------------+
+-host-variable--------------+
+-special-register-----------+
+-(scalar-fullselect)--------+
+-labeled-duration-----------+
+-case-expression------------+
+-cast-specification---------+
+-dereference-operation------+
+-OLAP-function--------------+
+-XML-function---------------+
+-method-invocation----------+
+-subtype-treatment----------+
’-sequence-reference---------’
operator:
|--+-CONCAT-------+---------------------------------------------|
+- / ----------+
+- * ----------+
+- + ----------+
’- - ----------’
Diese Syntaxdefinition ist rekursiv, da ein Ausdruck, der in Klammern eingeschlossen ist, selbst wieder als Operand in
einem Ausdruck auftreten darf (expression).
Statt CONCAT kann auch || zur Stringkonkatenation verwendet werden.
4 Ausdrücke, Prädikate, Suchbedingungen
31
Die einfachen Ausdrücke sind die folgenden:
Attributname (column-name): Dieser kann durch einen Tabellennamen oder eine Tupelvariable qualifiziert werden.
Beispiele: GEHALT, ANGEST.NAME
D B 2 unterscheidet dabei nicht zwischen Groß– und Kleinschreibung. Durch die Verwendung von doppelten
Anführungszeichen (") kann dies erzwungen werden (z.B. "Gehalt").
Konstante (constant): Ganze Zahlen (z.B. 5, -37), Dezimalzahlen (z.B. 2.7, -3.14), Fließkommazahlen (z.B. 12E3, 3.2E-12), Zeichenketten (z.B. ’Hello’), Datumswerte (z.B. ’12/25/1998’, ’25.12.1998’, ’1998-12-25’), Zeitwerte (z.B. ’13.50.00’, ’13:50’, ’1:50 PM’), Zeitstempelwerte (z.B. ’2001-01-05-12.00.00.000000’) usw.
Skalarwertige Subquery (scalar-fullselect): Ist sichergestellt, daß eine Anfrage (Query, siehe später) einen
skalaren Wert zurückliefert, so kann diese in Klammern eingeschlossen auch als Operand verwendet werden.
Typumwandlung (cast-specification): Explizite Umwandlung in einen bestimmten Typ durch Verwendung
des CAST–Operators.
cast-specification:
|--CAST--(--+-expression-------+--AS--data-type----------------->
+-NULL-------------+
’-parameter-marker-’
>--+----------------------------------+--)----------------------|
|
|
’-SCOPE-------+-typed-table-name-+-’
’-typed-view-name--’
Der angegebene Ausdruck (expression oder NULL) wird in einen Wert vom Typ data-type umgewandelt.
Parametermarken (parameter-marker) und die SCOPE–Klausel werden später erläutert.
Beispiele: CAST (NULL AS VARCHAR(20)), CAST (Gehalt*1.234 AS DECIMAL(9,2))
Auf die folgenden Operandenarten wird hier zunächst nicht näher eingegangen:
• Hostvariablen (host-variable) dienen der Kommunikation zwischen einer Hostsprache und dem DBMS.
Siehe Embedded S QL.
• Dereferenzierungsoperationen (dereference-operation), Aufrufe von Methoden
(method-invocation) und die Behandlung von Untertypen (subtype-treatment) sind nur im Zusammenhang mit strukturierten Typen sinnvoll und werden dort behandelt.
• Die OLAP–Funktionen (OLAP-function) werden für das sog. On–Line Analytical Processing verwendet. Evtl.
wird später noch auf diese Funktionen eingegangen.
• X ML–Funktionen (XML-function) dienen dem Umgang mit X ML–basierten Daten.
• In einer sequence-reference wird ein Wert von einer Sequenz ausgelesen. Sie werden bei den Sequenzen behandelt.
4.1.1 Zeitdauerwerte
Bei arithmetischen Operationen mit Datums– oder Zeitwerten können Zeitdauerwerte (labeled-duration) benutzt
werden.
4 Ausdrücke, Prädikate, Suchbedingungen
32
labeled-duration:
|--+-function------+--+-YEAR---------+--------------------------|
+-(expression)--+ +-YEARS--------+
+-constant------+ +-MONTH--------+
+-column-name---+ +-MONTHS-------+
’-host-variable-’ +-DAY----------+
+-DAYS---------+
+-HOUR---------+
+-HOURS--------+
+-MINUTE-------+
+-MINUTES------+
+-SECOND-------+
+-SECONDS------+
+-MICROSECOND--+
’-MICROSECONDS-’
In komplexen Ausdrücken dürfen Zeitdauerwerte nur verwendet werden, wenn der andere Operand im Ausdruck vom
Typ TIME, TIMESTAMP oder DATE ist. Der Wert vor dem jeweiligen Schlüsselwort im Zeitdauerwert muß von einem
(für dieses Schlüsselwort) sinnvollen Typ sein (Details siehe Handbuch).
Beispiele: 5 DAYS, 1 HOUR, 1 YEAR.
4.1.2 Fallunterscheidungen
Fallunterscheidungen in Ausdrücken sind in D B 2-SQL durch CASE–Ausdrücke (case-expression) möglich:
case-expression:
|--CASE--+-searched-when-clause-+------------------------------->
’-simple-when-clause---’
.-ELSE NULL---------------.
>--+-------------------------+--END-----------------------------|
’-ELSE--result-expression-’
searched-when-clause:
.-----------------------------------------------------.
V
|
|----WHEN--search-condition--THEN--+-result-expression-+-+------|
’-NULL--------------’
simple-when-clause:
.-----------------------------------------------.
V
|
|--expression----WHEN--expression--THEN--+-result-expression-+-+--|
’-NULL--------------’
Der Wert eines CASE–Ausdrucks (result-expression oder NULL) ergibt sich wie folgt:
4 Ausdrücke, Prädikate, Suchbedingungen
33
• Die Bedingungen (searched-when-clause) werden der Reihe nach ausgewertet. Ergebnis ist diejenige
result-expression, die zum ersten Fall gehört, der zu true evaluiert. Ergibt keiner der Fälle true, so ist das
Ergebnis über die ELSE–Klausel festgelegt. Wurde keine ELSE–Klausel angegeben, so ist das Ergebnis NULL.
• Bei einer simple-when-clause wird der Ausdruck, der vor dem ersten WHEN steht, mit den Ausdrücken
verglichen, die auf WHEN folgen.
• Für result-expression darf nicht überall NULL stehen, damit ein Typ für diesen Ausdruck bestimmt werden
kann. Die Typen von verschiedenen result-expression in einem CASE–Ausdruck müssen kompatibel sein.
Wird ein CASE–Ausdruck in einer VALUES–, GROUP BY–, ORDER BY–Klausel oder in einem IN–Prädikat verwendet,
so darf die search-condition einer searched-when-clause kein quantifiziertes Vergleichsprädikat, kein IN–
Prädikat, welches ein fullselect verwendet, und kein EXISTS–Prädikat sein (siehe später).
Beispiele:
CASE WHEN wert > 1000 THEN ’Gross’
WHEN wert < 100 THEN ’Klein’
ELSE ’Mittel’ END
CASE SUBSTR(workdept,1,1)
WHEN ’A’ THEN ’Administration’
WHEN ’B’ THEN ’Human Resources’
WHEN ’C’ THEN ’Accounting’
END
Manche Spezialfälle von CASE–Ausdrücken können auch über die beiden Funktionen NULLIF bzw. COALESCE formuliert werden (siehe später).
4.1.3 Registerwerte
Registerwerte (special-register) sind spezielle Werte, die vom DBMS verwaltet und z.T. vom Benutzer verändert
werden können und dann die Funktion des DBMS verändern. Mehrfache Verwendungen eines Registerwertes innerhalb
einer Anfrage liefern alle den gleichen Wert.
Folgende Tabelle zeigt die wichtigsten Registerwerte und ihre Bedeutung:
Name
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
USER
DATE
TIME
TIMESTAMP
TIMEZONE
SCHEMA
PATH
Bedeutung
Aktuelles Datum
Aktuelle Uhrzeit
Aktueller Zeitstempelwert
Aktuelle Zeitzone
Aktuelles Schema
Aktueller S QL–Pfad
Name des angemeldeten Benutzers
Veränderbar
Nein
Nein
Nein
Nein
Ja
Ja
Nein
Der Wert von CURRENT SCHEMA kann via SET SCHEMA, der Wert von CURRENT PATH via SET PATH geändert
werden (Details siehe später).
4 Ausdrücke, Prädikate, Suchbedingungen
34
Beispiele:
SELECT lastname FROM employee WHERE DAYOFYEAR(birthdate) = DAYOFYEAR(CURRENT DATE)
SELECT * FROM syscat.tables WHERE definer = USER
Statt des Leerzeichens kann in den Namen der Registerwerte auch ein Underscore verwendet werden.
4.1.4 Funktionen
In D B 2–SQL stehen drei Arten von Funktionen zur Verfügung:
Skalare Funktionen: Sie werten eine Liste von skalaren Parametern aus und geben einen skalaren Wert zurück.
Aggregatfunktionen: Sie werden auf Spalten (oder auf Ausdrücke, die solche Spalten enthalten) einer Gruppe bzw.
einer Relation angewendet und geben einen skalaren Wert zurück. Sie werden nur in Anfragen (z.B. in der
SELECT–Klausel) verwendet.
Tabellenfunktionen: Sie geben eine Relation zurück und können nur in der FROM–Klausel verwendet werden.
Für die ersten beiden Arten stellt das DBMS Funktionen bereit, die sofort verwendet werden können.
Man kann für alle drei Arten benutzerdefinierte Funktionen erstellen, die dann an denselben Stellen wie die vom System
bereitgestellten Funktionen verwendet werden können.
Bei allen diesen Funktionen ist zu beachten, daß die von ihnen zurückgegebenen Datentypen von den Datentypen der
Parameter abhängen (können), d.h. es kann mehrere gleichnamige Funktionen geben, die jedoch eine unterschiedliche
Signatur besitzen (Overloading).
Da in Ausdrücken nur Aufrufe (Auswertungen) von skalaren Funktionen verwendet werden können, werden diese im
folgenden erläutert. Auf die anderen Arten von Funktionen wird später eingegangen.
Unter anderem stehen folgende skalare Funktionen (function) zur Verfügung:
Funktionen zur Typkonvertierung: BIGINT, BLOB, CHAR, CLOB, DATE, DBCLOB, DECIMAL, DREF,
DOUBLE, FLOAT, GRAPHIC, INTEGER, LONG VARCHAR, LONG VARGRAPHIC, REAL, SMALLINT, TIME,
TIMESTAMP, VARCHAR, VARGRAPHIC
Beispiele: CHAR(salary, ’,’), TIME(’10:00’)
Mathematische Funktionen: ABS, ACOS, ASIN, ATAN, CEIL, COS, COT, DEGREES, EXP, FLOOR, LN, LOG,
LOG10, MOD, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, TAN, TRUNC
Beispiele: ROUND(salary,0), POWER(2,10)
Funktionen zur Stringmanipulation: ASCII, CHR, CONCAT, DIFFERENCE, DIGITS, HEX, INSERT, LCASE,
LEFT, LENGTH, LOCATE, LTRIM, POSSTR, REPEAT, REPLACE, RIGHT, RTRIM, SOUNDEX, SPACE,
SUBSTR, UCASE, TRANSLATE
Funktionen zur Datumsmanipulation: DAY, DAYNAME, DAYOFWEEK, DAYOFYEAR, DAYS, HOUR,
JULIAN DAY, MICROSECOND, MIDNIGHT SECONDS, MINUTE, MONTH, MONTHNAME, QUARTER, SECOND,
TIMESTAMP ISO, TIMESTAMPDIFF, WEEK, WEEK ISO, YEAR, VARCHAR FORMAT, TO DATE, TO CHAR
Beispiele: MONTH(birthdate), DAYOFWEEK(CURRENT DATE)
Systemnahe Funktionen: EVENT MON STATE, DBPARTITIONNUM, RAISE ERROR,
TABLE NAME, TABLE SCHEMA, TYPE ID, TYPE NAME, TYPE SCHEMA
Beispiel: RAISE ERROR(’70007’,’This sucks’)
4 Ausdrücke, Prädikate, Suchbedingungen
35
Sonstige Funktionen: COALESCE bzw. VALUE: Liefert das erste Argument, das nicht der NULL–Wert ist.
VALUE(salary,0) liefert z. B. den Wert von salary, falls dieser nicht NULL ist, und sonst den Wert 0.
GENERATE UNIQUE: Liefert einen 13 Byte langen Bit-String, der eindeutig in Bezug auf mehrfache Aufrufe
dieser Funktion ist.
NULLIF: Liefert einen NULL–Wert, wenn beide Argumente gleich sind, und sonst den Wert des ersten Arguments.
Alle Funktionen sind detailliert im Manual beschrieben. Meistens gibt der Name einer Funktion einen Hinweis auf ihre
Semantik.
4.2 Prädikate
Prädikate (predicates) sind logische Tests, die in Suchbedingungen verwendet werden. Sie liefern die Wahrheitswerte true, false und unknown.
Bemerkung: Wenn im folgenden von fullselect die Rede ist, so ist damit eine Anfrage gemeint, die eine Relation
zurückliefert (siehe eine der nächsten Sitzungen).
Folgende Prädikate sind möglich:
Einfache Vergleichsprädikate für Ausdrücke:
>>-expression--+++++’-
= -------+----expression---------------------><
<> ------+
< -------+
> -------+
<= ------+
>= ------’
Beispiele: price > 100, name <> ’Fritz’
Vergleiche mit NULL–Werten ergeben unknown
Vorsicht: Zeichen werden in D B 2 unter Verwendung von sog. collating sequences sortiert. Insbesondere wird bei
der Installation, die für das Praktikum verwendet wird, nicht nach den ASCII–Werten sortiert. Die Zeichen sind
stattdessen wie folgt geordnet: 0,1,2,. . . ,9,a,A,b,B,c,C,. . . ,x,X,y,Y,z,Z
Zeichenketten werden dann entsprechend dieser Ordnung lexikographisch sortiert.
BETWEEN–Prädikat:
>>-expression--+-----+--BETWEEN--expression--AND--expression---><
’-NOT-’
Dieses Prädikat dient der Bereichsüberprüfung.
Beispiel: age BETWEEN 20 AND 29
NULL–Prädikat:
>>-expression--IS--+-----+--NULL-------------------------------><
’-NOT-’
Dieses Prädikat dient zum Test auf den NULL–Wert (ergibt immer true oder false — niemals unknown).
Beispiele: Telnr IS NULL, salary IS NOT NULL
4 Ausdrücke, Prädikate, Suchbedingungen
36
LIKE-Prädikat:
>>-match-expression--+-----+--LIKE--pattern-expression---------->
’-NOT-’
>--+---------------------------+-------------------------------><
’-ESCAPE--escape-expression-’
Dieses Prädikat bewirkt einen Zeichenkettenvergleich mit Platzhaltern für ein ( ) bzw. beliebig viele (%) Zeichen.
Dabei muß der Ausdruck match-expression zu einem String evaluieren. Als escape-expression kann
ein Zeichen angegeben werden, mit dem die Platzhalterzeichen geschützt werden können.
Beipiele: plz LIKE ’7207 ’, ort LIKE ’%ing%’
EXISTS–Prädikat:
>>-EXISTS--(fullselect)----------------------------------------><
Es wird geprüft, ob die angegebene Anfrage mindestens ein Tupel zurückliefert. Ist dies der Fall, so ergibt das
EXISTS–Prädikat true; wird kein Tupel zurückgeliefert, so ist das Ergebnis false.
IN–Prädikat:
>>-+-expression1--+-----+--IN--+-(fullselect1)-------------+-+-><
|
’-NOT-’
|
.-,---------------.
| |
|
|
V
|
| |
|
+-(------expression2---+--)-+ |
|
’-expression2---------------’ |
|
.-,---------------.
|
|
V
|
|
’-(------expression3---+--)--+-----+--IN--(fullselect2)---’
’-NOT-’
Es wird geprüft, ob ein Wert (nicht) in einer Kollektion von Werten vorkommt. Dabei ist zu beachten, daß die
Typen von expression1 und expression2 bzw. vom Ergebnis von fullselect1, sowie die Typen von
expression3 mit den jeweils korrespondierenden Spalten im Ergebnis von fullselect2 kompatibel sein
müssen.
Bei NULL–Werten in der Kollektion ist es möglich, daß das IN–Prädikat den Wert unknown annimmt.
TYPE–Prädikat: Da dieses Prädikat nur im Zusammenhang mit strukturierten Typen benutzt wird, wird es erst später
erläutert.
4.2.1 Quantifizierte Vergleichsprädikate
Quantifizierte Vergleichsprädikate besitzen die folgende Syntax:
>>-+-expression1--+- = -------+--+-SOME-+--(fullselect1)-----+-><
|
|
| +-ANY--+
|
|
+- <> ------+ ’-ALL--’
|
|
+- < -------+
|
|
+- > -------+
|
|
+- <= ------+
|
|
’- >= ------’
|
|
.-,---------------.
|
|
V
|
|
4 Ausdrücke, Prädikate, Suchbedingungen
37
’-(------expression2---+--)-- = --+-SOME-+--(fullselect2)-’
’-ANY--’
Dabei ist zu beachten, daß die Typen von expression1 und vom Ergebnis von fullselect1, bzw. die Typen
von expression2 mit den jeweils korrespondierenden Spalten im Ergebnis von fullselect2 kompatibel sein
müssen.
Diese zweite Form der quantifizierten Vergleichsprädikate, bei der ein Tupel mit einer Menge von Tupeln verglichen
wird, wird auch als Tupelprädikat bezeichnet.
Bei Verwendung von ALL ist das Ergebnis des Prädikats
• true, wenn das Ergebnis von fullselect1 keine Werte enthält oder für alle Werte im Ergebnis von
fullselect1 der Vergleich true ergibt,
• false, wenn der Vergleich für mindestens einen Wert im Ergebnis von fullselect1 false ergibt und
• unknown, wenn der Vergleich für keinen Wert im Ergebnis von fullselect1 false aber mindestens einmal
unknown ergibt.
Bei Verwendung von SOME oder ANY ist das Ergebnis des Prädikats
• true, wenn der Vergleich für mindestens einen Wert aus dem Ergebnis von fullselect1 true ergibt bzw. wenn
der Vergleich für mindestens ein Tupel aus dem Ergebnis von fullselect2 true ergibt,
• false, wenn die Ergebnisse von fullselect1 bzw. fullselect2 leer sind oder für alle Werte aus dem
Ergebnis von fullselect1 der Vergleich false ergibt bzw. wenn der Vergleich für jedes Tupel aus dem Ergebnis
von fullselect2 false ergibt und
• unknown, wenn der Vergleich für keinen Wert aus dem Ergebnis von fullselect1 true aber mindestens einmal
unknown ergibt bzw. wenn der Vergleich für jedes Tupel aus dem Ergebnis von fullselect2 nicht true ergibt
und mindestens ein Vergleich unknown ergibt.
Beispiele:
department IN (’A00’,’D01’,’E21’)
EXISTS (SELECT *
FROM employee
WHERE salary > 100000)
2000 < ALL (SELECT salary
FROM employee)
(’000010’,’CHRISTINE’) IN (SELECT empno, firstname
FROM employee
WHERE edlevel = 18)
(’000010’,’CHRISTINE’) = ANY (SELECT empno, firstname
FROM employee
WHERE edlevel = 18)
4 Ausdrücke, Prädikate, Suchbedingungen
38
4.3 Suchbedingungen
Eine Suchbedingung (search-condition) ist eine Bedingung, die für jedes Tupel einer Relation, für die sie ausgewertet wird, den Wert true, false oder unknown annimmt.
search-condition:
|--+-----+--+-predicate--+-------------------------------+-+---->
’-NOT-’ |
’-SELECTIVITY--numeric-constant-’ |
’-(search-condition)---------------------------’
.-------------------------------------------------------------------------.
V
|
>---+--------------------------------------------------------------------+-+--|
’-+-AND-+--+-----+--+-predicate--+-------------------------------+-+-’
’-OR--’ ’-NOT-’ |
’-SELECTIVITY--numeric-constant-’ |
’-(search-condition)---------------------------’
Hierbei steht predicate jeweils für ein Prädikat und search-condition für eine Suchbedingung.
Geklammerte Suchbedingungen werden zuerst ausgewertet. Bei ungeklammerten Bedingungen gilt die folgende Regel:
NOT bindet am stärksten, dann werden AND-Verknüpfungen ausgewertet, und am schwächsten bindet OR. Innerhalb
boolscher Operatoren mit gleicher Bindungsstärke ist die Auswertungsreihenfolge nicht festgelegt.
Der Wahrheitswert einer Suchbedingung errechnet sich gemäß folgender Tabelle (P und Q seien beliebige Prädikate):
P
true
true
true
false
false
false
unknown
unknown
unknown
Q
true
false
unknown
true
false
unknown
true
false
unknown
P AND Q
true
false
unknown
false
false
false
unknown
false
unknown
P OR Q
true
true
true
true
false
unknown
true
unknown
unknown
Außerdem gilt: NOT (true) ergibt false, NOT (false) ergibt true und NOT (unknown) ergibt unknown.
Über die SELECTIVITY–Klausel kann man die Selektivität des Prädikats angeben. Diese wird vom DBMS bei Optimierungen berücksichtigt. Sie darf nur bei benutzerdefinierten Prädikaten, d.h. bei Prädikaten, die den Aufruf einer
benutzerdefinierten Funktion enthalten, angegeben werden. Der Wert der Selektivität numeric-constant muß dann
zwischen 0 und 1 liegen.
5 Schemata
39
5 Schemata
Ein Schema ist eine Sammlung von benannten Objekten (Tabellen, Sichten, Trigger, Funktionen,. . . ). Jedes Objekt in
der Datenbank liegt in einem Schema. Objektnamen müssen innerhalb eines Schemas eindeutig sein. Schemata können
jedoch nicht rekursiv angelegt werden, d.h. ein Schema kann kein anderes Schema enthalten.
Ein Objekt in der Datenbank wird durch einen qualifizierten Namen identifiziert. Dieser besteht aus dem Namen des
Schemas, in dem das Objekt liegt, gefolgt von einem Punkt und dem Objektnamen.
Z. B. bezeichnet KUNDEN.ADRESSEN das Objekt ADRESSEN im Schema KUNDEN. Wird nur ein Objektname angegeben, so qualifiziert D B 2 diesen durch den Schemanamen, der im Register CURRENT SCHEMA abgelegt ist. Nach dem
Verbinden mit der Datenbank enthält dieses Register zunächst den Benutzernamen. Den Wert dieses Registers kann man
mit dem SET SCHEMA–Statement verändern:
.-CURRENT-.
.-=-.
>>-SET--+---------+--SCHEMA--+---+--+-schema-name-----+--------><
+-USER------------+
+-host-variable---+
’-string-constant-’
Beispiel: Folgende Statements setzen jeweils das Register auf den Wert prak:
SET SCHEMA = prak
SET CURRENT SCHEMA ’prak’
5.1 Anlegen und Löschen von Schemata
Es gibt zwei Arten, ein Schema zu erzeugen: explizit und implizit.
Explizites Anlegen eines Schemas mit dem CREATE SCHEMA–Statement:
>>-CREATE SCHEMA------------------------------------------------>
>--+-schema-name------------------------------------+----------->
+-AUTHORIZATION--authorization-name--------------+
’-schema-name--AUTHORIZATION--authorization-name-’
>--+--------------------------+--------------------------------><
| .----------------------. |
| V
| |
’---schema-SQL-statement-+-’
• Nur ein Benutzer mit der Berechtigung DBADM oder der Berechtigungsstufe SYSADM darf Schemata mit (fast)
beliebigen Namen anlegen.
• Ein anderer Benutzer darf nur ein Schema mit seinem Benutzernamen als Namen anlegen.
• Der angegebene schema-name darf nicht mit SYS beginnen und höchstens 30 Zeichen lang sein. Ein Schema
mit diesem Namen darf noch nicht existieren.
5 Schemata
40
• Wird nur der schema-name angegeben, so wird ein Schema mit diesem Namen angelegt, das dem aktuellen
Benutzer gehört.
• Wird nur AUTHORIZATION authorization-name angegeben, so wird ein Schema mit dem Namen
authorization-name angelegt, das dem Benutzer authorization-name gehört.
• Werden sowohl schema-name als auch authorization-name angegeben, so wird ein Schema mit dem
Namen schema-name angelegt, das dem Benutzer authorization-name gehört.
• Im schema-SQL-statement sind Statements zum Anlegen von Tabellen, Sichten und Indexen, sowie das
COMMENT ON– und bestimmte GRANT–Statements möglich. Alle nicht vollständig qualifizierten Objektnamen
werden durch das gerade angelegte Schema qualifiziert.
Ein Benutzer, der über die Datenbankberechtigung IMPLICIT SCHEMA verfügt, kann ein Schema auch implizit erzeugen:
• Dies geschieht dadurch, daß dieser Benutzer in einem (CREATE–)Statement ein Schema verwendet, das noch
nicht existiert.
• Dieses wird dann vom DBMS angelegt. Als Besitzer dieses impliziten Schemas wird immer SYSIBM eingetragen.
• Insbesondere hat standardmäßig jeder Benutzer das Recht, in einem implizit angelegten Schema Objekte anzulegen!
• Nur ein Benutzer mit der Berechtigung DBADM oder der Berechtigungsstufe SYSADM kann die Zugriffsrechte an
solch einem Schema ändern.
Löschen eines Schemas mit dem DROP–Statement:
>>---DROP----SCHEMA----schema-name----RESTRICT------------><
Hierbei ist zu beachten, daß in dem zu löschenden Schema keine Objekte mehr existieren dürfen.
Ein Benutzer muß Eigentümer des Schemas sein, oder mindestens die Datenbankberechtigung DBADM besitzen, um ein
Schema zu löschen.
5.2 Verwalten von Schemaberechtigungen
Erteilen von Schemaberechtigungen mit dem GRANT–Statement:
.-,------------.
V
|
>>-GRANT----+-ALTERIN--+-+--ON SCHEMA--schema-name-------------->
+-CREATEIN-+
’-DROPIN---’
.-,---------------------------------.
V
|
>--TO----+-+-------+--authorization-name-+-+-------------------->
| +-USER--+
|
| ’-GROUP-’
|
’-PUBLIC------------------------’
>--+-------------------+---------------------------------------><
’-WITH GRANT OPTION-’
5 Schemata
41
• Um einem Benutzer (oder einer Benutzergruppe) Rechte an einem Schema zu erteilen, muß man diese Rechte
mit der WITH GRANT OPTION erhalten haben, die Berechtigung DBADM oder die Berechtigungsstufe SYSADM
besitzen.
• Der Zusatz WITH GRANT OPTION bewirkt, daß der/die mit authorization-name bezeichnete(n) Benutzer
die erhaltenen Rechte an dritte weitergeben darf/dürfen.
• Folgende Schemarechte sind vorgesehen:
Recht
ALTERIN
CREATEIN
DROPIN
Bedeutung
Objekte im Schema ändern
Objekte im Schema anlegen (evtl. sind noch weitere Rechte, wie z. B. CREATETAB,
nötig)
Objekte im Schema löschen
Bei einem explizit angelegten Schema erhält der Besitzer des Schemas diese Rechte automatisch.
Entziehen von Schemaberechtigungen mit dem REVOKE–Statement:
.-,------------.
V
|
>>-REVOKE----+-ALTERIN--+-+--ON SCHEMA--schema-name------------->
+-CREATEIN-+
’-DROPIN---’
.-,---------------------------------.
V
| .-BY ALL-.
>--FROM----+-+-------+--authorization-name-+-+--+--------+-----><
| +-USER--+
|
| ’-GROUP-’
|
’-PUBLIC------------------------’
Zum Entziehen von Schemaberechtigungen ist die Berechtigung DBADM oder die Berechtigungsstufe SYSADM notwendig.
6 Tabellen
42
6 Tabellen
6.1 Datentypen
Data types
built-in
data
types
external
data
datetime
signed
numeric
string
DATALINK
time
TIME
fixed
length
CHAR
timestamp
exact
date
approximate
TIMESTAMP DATE
character
graphic
varying
length
fixed
length
GRAPHIC
floating
point
varying
length
binary
BLOB
varying
length
single
precision
double
precision
REAL
DOUBLE
VARCHAR CLOB VARGRAPHIC DBCLOB
binary
integer
16 bit
32 bit
SMALLINT INTEGER
decimal
64 bit
packed
BIGINT
DECIMAL
Figure 10. The DB2 Built-in Data Types
Abbildung 6.1: Datentypen
All data types include the null value. The null value is a special value that is
distinct from all non-null values and thereby denotes the absence of a
Abbildung 6.1 zeigt eine
Übersicht
der Although
Datentypenall
von
D Btypes
2–S QLinclude
und deren
Diese (eingebauten)
(non-null)
value.
data
the Zusammenhänge.
null value, columns
Datentypen stellt das DBMS
Nähere
Angaben,
Größe dieser Datentypen, sind im Manual beim
definedzur
as Verfügung.
NOT NULL
cannot
containu.a.
nullzur
values.
CREATE TABLE–Statement zu finden. Benutzerdefinierte und strukturierte Datentypen werden später behandelt.
Related reference:
Folgende Tabelle führt die Namen (Schlüsselwörter) der wichtigsten (eingebauten) Typen auf und erläutert deren Verv “User-defined types” on page 108
wendung.
Chapter 2. Language elements
93
6 Tabellen
Schlüsselwort
SMALLINT
INTEGER
BIGINT
REAL
DOUBLE
DECIMAL(p,s)
CHAR(n)
VARCHAR(n)
BLOB(s F)
DATE
TIME
TIMESTAMP
DATALINK
43
Verwendung
kleine ganze Zahl (Werte zwischen -32 768 und +32 767)
ganze Zahl (Werte zwischen -2 147 483 648 und +2 147 483 647)
große ganze Zahl (Werte zwischen -9 223 372 036 854 775 808 und +9 223 372 036 854 775 807)
Fließkommazahl (einfache Genauigkeit, Wert 0 oder Werte zwischen -3.402E+38 und -1.175E-37 bzw.
zwischen 1.175E-37 und 3.402E+38)
Fließkommazahl (doppelte Genauigkeit, Wert 0 oder Werte zwischen -1.79769E+308 und -2.225E-307
bzw. zwischen 2.225E-307 und 1.79769E+308)
Dezimalbruch der Länge p (max. 31), davon s Nachkommastellen
String der Länge n (max. 254 Zeichen)
String variabler Länge bis zu n Zeichen (max. 32672)
Binary Large OBject: s steht für die Länge; F gibt die Einheit an: K (Kilobyte), M (Megabyte), G
(Gigabyte); fehlt die Einheit, so wird Byte angenommen; max. 2 147 483 647 Byte (2 GB - 1)
Datum
Uhrzeit
Zeitstempel
Verweis auf Datei, die außerhalb der Datenbank liegt
Für einige dieser Typen können auch synonyme Schlüsselwörter verwendet werden, z.B. DEC oder NUM oder NUMERIC
statt DECIMAL.
6.1.1 NULL– und Defaultwerte
Zu jedem Datentyp gehört ein NULL–Wert. Dieser ist von allen anderen Werten des Datentyps zu unterscheiden, da
er nicht einen Wert an sich darstellt, sondern das Fehlen eines Wertes anzeigt (im Sinne von noch nicht (nicht mehr)
bekannt, nicht anwendbar).
Der Wert 0 im Attribut Gehalt eines Angestellten könnte z. B. bedeuten, daß der Angestellte ehrenamtlich tätig ist,
während der NULL–Wert bedeuten könnte, daß das Gehalt (noch) nicht bekannt ist. Das DBMS bietet Prädikate an, die
es z. B. in Anfragen erlauben, zu prüfen, ob ein NULL–Wert vorliegt oder eine andere Ausprägung des Datentyps.
NULL–Werte führen dazu, daß Prädikate nicht nur zu den Wahrheitswerten wahr und falsch, sondern auch zu unknown
evaluieren können (dreiwertige Logik).
Defaultwerte sind Werte, die vom DBMS eingetragen werden, wenn für das betreffende Attribut kein Wert angegeben
wird. Sie bieten die Möglichkeit, Attribute automatisch mit Daten zu versehen, z. B. eine bestimmte Konstante, der
Name des Benutzers (USER), die aktuelle Uhrzeit (CURRENT TIME), das aktuelle Datum (CURRENT DATE) usw.
Wird kein spezieller Defaultwert angegeben, so wird (sofern möglich) der NULL–Wert als Defaultwert verwendet.
6.2 Anlegen von Tabellen
Zum Anlegen einer Tabelle ist eine der folgenden Berechtigungen nötig:
• Berechtigungsstufe SYSADM oder DBADM–Recht
• Rechte CREATETAB und IMPLICIT SCHEMA, falls kein Schema für die zu erstellende Tabelle angegeben wurde
und das implizit verwendete Schema noch nicht existiert
• Rechte CREATETAB und CREATEIN, falls das Schema der zu erstellenden Tabelle bereits existiert
6 Tabellen
44
6.2.1 CREATE TABLE–Statement
Anlegen von Tabellen mit dem CREATE TABLE–Statement:
>>-CREATE--TABLE--table-name------------------------------------>
>--+-| element-list |----------------------------+--*----------->
|
|
’-LIKE--+-table-name1-+--+------------------+-’
’-view-name---’ ’-| copy-options |-’
>--+----------------------------------------------+--*---------->
’-IN--tablespace-name1--| tablespace-options |-’
>--*--+-------------------+--*--+-----------------------+------><
’-VALUE COMPRESSION-’
’-WITH RESTRICT ON DROP-’
• LIKE spezifiziert, daß die Attribute der Tabelle exakt dieselben Namen und dieselben Definitionen haben sollen
wie in der identifizierten Tabelle table-name1 oder Sicht view-name.
• Mit der IN–Klausel kann man angeben, in welchem Tabellenbereich die Tabelle angelegt werden soll, sowie noch
weitere Optionen für dessen Verwendung angeben.
• Wird VALUE COMPRESSION angegeben, so werden NULL–Werte und Werte der Länge 0 (z.B. leere Strings
oder BLOBs) platzsparender abgelegt. Allerdings kann dadurch die Tabelle leichter fragmentieren.
• Wird WITH RESTRICT ON DROP angegeben, so dürfen weder die Tabelle noch der Tablespace, in dem sie
angelegt wird, später gelöscht werden (es muß dann die ganze Datenbank gelöscht werden).
Das CREATE TABLE–Statement erlaubt noch viele weitere Optionen, die hier nicht behandelt werden können.
Derjenige Benutzer, der eine Tabelle anlegt, erhält das CONTROL–Recht auf dieser Tabelle. Sonst werden zunächst
keine Rechte vergeben.
Die copy-options (welche nur im Zusammenhang mit LIKE verwendet werden können) spezifizieren, welche
zusätzlichen Eigenschaften der zugrundeliegenden Tabellen– oder Sicht–Definition kopiert werden sollen.
copy-options:
|--*--+-------------------------------------+--*---------------->
|
.-COLUMN-.
|
’-+-INCLUDING-+--+--------+--DEFAULTS-’
’-EXCLUDING-’
.-COLUMN ATTRIBUTES-.
.-EXCLUDING IDENTITY--+-------------------+-.
>--+-------------------------------------------+--*-------------|
|
.-COLUMN ATTRIBUTES-. |
’-INCLUDING IDENTITY--+-------------------+-’
• Wird INCLUDING COLUMN DEFAULTS angegeben, so erhalten (nach Möglichkeit) die Attribute der neu angelegten Tabelle dieselben Defaultwerteinstellungen wie in der zugrundeliegenden Tabelle oder Sicht. Bei EXCLUDING COLUMN DEFAULTS ist dies nicht der Fall. Letzteres ist die Defaulteinstellung für diese Option, wenn
die zugrundeliegende Tabelle eine Basistabelle oder eine temporäre Tabelle ist.
6 Tabellen
45
• Wird INCLUDING IDENTITY COLUMN ATTRIBUTES angegeben, werden (falls möglich) die Definitionen
von IDENTITY–Attributen übernommen. Bei Verwendung von EXCLUDING IDENTITY COLUMN ATTRIBUTES ist dies nicht der Fall. Nähere Details, wann das Übernehmen der Definitionen möglich ist, sind im Manual
beschrieben.
Wird eine neue Tabelle nicht durch Übernehmen einer Tabellen– oder Sichtdefinition mittels LIKE angelegt, so muß
eine element-list angegeben werden:
element-list:
.-,------------------------------.
V
|
|--(----+-| column-definition |------+-+--)---------------------|
+-| unique-constraint |------+
+-| referential-constraint |-+
’-| check-constraint |-------’
Es werden Attribute (Spalten) definiert (column-definition) und Schlüssel– (unique-constraint), Fremdschlüssel– (referential-constraint) oder allgemeine Integritätsbedingungen (Check Constraints) angegeben
(check-constraint).
6.2.2 Attribute
Eine Attributdefinition wird wie folgt vorgenommen:
column-definition:
|--column-name--+--------------------+-------------------------->
|
|
’-| data-type |------’
>--+--------------------+---------------------------------------|
’-| column-options |-’
column-options:
.-----------------------------------------------------------------------.
V
|
|----+-------------------------------------------------------------------+-+--|
+-NOT NULL----------------------------------------------------------+
+-| lob-options |---------------------------------------------------+
+-+-----------------------------+---| column-constraints |----------+
| ’-CONSTRAINT--constraint-name-’
|
+-| generated-column-spec |-----------------------------------------+
’-COMPRESS SYSTEM DEFAULT-------------------------------------------’
lob-options:
.-LOGGED-----.
.-NOT COMPACT-.
|--*--+------------+--*--+-------------+--*---------------------|
’-NOT LOGGED-’
’-COMPACT-----’
6 Tabellen
46
column-constraints:
|---+-+-PRIMARY KEY-+-----------------------------------------+---|
| ’-UNIQUE------’
|
+-| references-clause |-----------------------------------+
’-CHECK--(--check-condition--)--| constraint-attributes |-’
Der Spalte mit dem Namen column-name wird der Datentyp data-type zugeordnet. Es sind folgende Optionen
möglich:
• Der Zusatz NOT NULL legt fest, daß für dieses Attribut keine NULL–Werte erlaubt sind.
• Die Angabe der lob-options ist nur bei LOB–Datentypen zulässig:
– Über [NOT] LOGGED kann angegeben werden, ob Änderungen an dieser (LOB–)Spalte mitgeloggt werden
sollen [oder nicht].
– Über [NOT] COMPACT kann angegeben werden, ob die (LOB–)Spalte platzsparender gespeichert werden
soll [oder nicht]. Die platzsparende Speicherung führt allerdings zu aufwendigeren Operationen, wenn der
Inhalt dieser Spalte verändert wird.
• Über CONSTRAINT kann einer Schlüssel–, Fremdschlüssel– oder Integritätsbedingung der Name
constraint-name gegeben werden:
– PRIMARY KEY definiert diese Spalte zu einem Primär–, UNIQUE zu einem Sekundärschlüssel.
– Über references-clause kann diese Spalte als Fremdschlüssel definiert werden.
– Mittels der CHECK–Klausel kann eine allgemeine Integritätsbedingung formuliert werden.
– Für Details zu Schlüssel–, Fremdschlüssel– oder Integritätsbedingungen siehe auch Abschnitte 6.2.3 und
6.2.5, die sich auch auf solche Bedingungen beziehen, die aber nicht bei der Definition eines Attributs
aufgeführt sind.
• COMPRESS SYSTEM DEFAULT bewirkt, daß Defaultwerte etwas platzsparender abgelegt werden. Dies führt
allerdings zu mehr Aufwand beim Einfügen und Löschen.
Über die generated-column-spec kann man Defaultwerte angeben oder daß und wie Attributwerte automatisch
vom System generiert werden sollen:
generated-column-spec:
|--+-| default-clause |-----------------------------------------------------+-|
’-GENERATED--+-ALWAYS-----+-AS-+-IDENTITY--+-------------------------+-+-’
’-BY DEFAULT-’
|
’-| identity-attributes |-’ |
’-(--generation-expression--)-----------’
default-clause:
.-WITH-.
|--+------+--DEFAULT--+--------------------+--------------------|
’-| default-values |-’
identity-attributes:
|--+---------------------------------------------------------+--|
6 Tabellen
47
|
.-----------------------------------------------.
|
|
V
.-1----------------.
|
|
’-(-----------+-START WITH--+-numeric-constant-+---+-+--)-’
|
.-1----------------. |
+-INCREMENT BY--+-numeric-constant-+-+
| .-NO MINVALUE----------------.
|
+-+-MINVALUE--numeric-constant-+-----+
| .-NO MAXVALUE----------------.
|
+-+-MAXVALUE--numeric-constant-+-----+
| .-NO CYCLE-.
|
+-+-CYCLE----+-----------------------+
| .-CACHE--20---------------.
|
+-+-NO CACHE----------------+--------+
| ’-CACHE--integer-constant-’
|
| .-NO ORDER-.
|
’-+-ORDER----+-----------------------’
default-values:
|--+-constant-------------------------------------------+-------|
+-datetime-special-register--------------------------+
+-CURRENT SCHEMA-------------------------------------+
+-USER-----------------------------------------------+
+-NULL-----------------------------------------------+
’-cast-function--(--+-constant------------------+--)-’
+-datetime-special-register-+
+-CURRENT SCHEMA------------+
’-USER----------------------’
• Über die DEFAULT–Klausel kann man einen Defaultwert für ein Attribut angeben (vgl. Abschnitt 6.1.1).
• Mittels GENERATED werden Attributwerte automatisch von D B 2 erzeugt. ALWAYS besagt, daß immer ein Attributwert generiert wird, wenn ein Tupel eingefügt wird, oder sich der Wert der generation-expression
ändert. BY DEFAULT erzeugt nur einen Attributwert, wenn kein Wert angegeben wird.
• Bei Verwendung von AS IDENTITY wird eine Sequenz für dieses Attribut definiert und vom DBMS angelegt.
Es wird dann automatisch als NOT NULL definiert und darf keinen Defaultwert besitzen. Jede Tabelle kann
nur ein solches Attribut haben. Dieses Attribut muß entweder einen der Ganzzahltypen oder DECIMAL mit 0
Nachkommastellen als Datentyp haben.
• Jedes der identity-attributes darf maximal einmal angegeben werden.
– START WITH gibt an, mit welchem Wert die Sequenz beginnen soll. Default ist hier MINVALUE für auf–
und MAXVALUE für absteigende Sequenzen.
– Der Betrag von INCREMENT BY gibt an, um wieviel sich die Werte der Sequenz unterscheiden. Ist die
angegebene Konstante positiv, so handelt es sich um eine aufsteigende Sequenz, sonst eine absteigende.
– MINVALUE und MAXVALUE geben Minimal– und Maximalwert der Sequenz an. Werden diese Optionen
mit vorangestelltem NO angegeben, so wird der Minimal– oder Maximalwert des Datentyps des Attributs
verwendet.
– Wird NO CYCLE angegeben, so stoppt D B 2 mit der Generierung von Werten, wenn eine aufsteigende Sequenz am Maximum, bzw. eine absteigende am Minimum angekommen ist.
– Bei CYCLE wird hingegen dann wieder von vorne (oder dem entsprechenden Maximal– oder Minimalwert)
mit der Generierung begonnen. In diesem Fall können Werte mehrfach auftreten.
6 Tabellen
48
– Über die CACHE–Option kann man fordern, daß (bzw. wieviele) Werte für diese Spalte im Speicher vorgehalten werden sollen, um das Laufzeitverhalten zu verbessern.
– Mittels [NO] ORDER gibt man an, ob die Werte in der Reihenfolge generiert werden sollen, in der sie
angefordert werden [oder nicht].
• Mittels generation-expression kann man angeben, wie die Werte für dieses Attribut ermittelt werden
sollen. Unter anderem kann man in diesem Ausdruck auf die Werte bereits definierter Attribute dieser Tabelle
zugreifen.
Die meisten dieser Optionen sind etwas komplexer als hier dargestellt. Genauere Angaben finden sich im Manual.
6.2.3 Schlüssel und Fremdschlüssel
Schlüssel können, wenn sie nur aus einem Attribut bestehen, als Teil der Attributdefinition angegeben werden, oder
(allgemeiner) über folgende Klausel definiert werden:
unique-constraint:
|--+-----------------------------+--+-UNIQUE------+------------->
’-CONSTRAINT--constraint-name-’ ’-PRIMARY KEY-’
.-,-----------.
V
|
>--(----column-name-+--)----------------------------------------|
Mittels PRIMARY KEY wird ein Primärschlüssel für die Tabelle festgelegt:
• Dieser besteht aus den angegebenen Attributen (besteht der Schlüssel nur aus einem Attribut, so kann man (wie
oben gesehen), PRIMARY KEY direkt hinter dem jeweiligen Attribut (ohne weitere Zusätze) angeben).
• Die Attribute, die in einem Schlüssel auftreten, dürfen keine NULL-Werte zulassen und können u. a. keine LONGDatentypen haben.
• Das DBMS legt einen Index auf dem Primärschlüssel an. Dieser Index trägt entweder den Namen constraintname (falls dieser angegeben wurde) oder einen vom System generierten eindeutigen Namen. Mehr zu Indexen
später.
• Jede Tabelle kann nur einen Primärschlüssel besitzen.
Mittels UNIQUE wird ein Sekundärschlüssel für die Tabelle festgelegt:
• Dieser besteht aus den angegebenen Attributen (besteht der Schlüssel nur aus einem Attribut, so kann man (wie
oben gesehen), UNIQUE direkt hinter dem jeweiligen Attribut (ohne weitere Zusätze) angeben).
• Es gelten die gleichen Einschränkungen an die Attribute wie bei Primärschlüsseln.
• Das DBMS legt wie bei einem Primärschlüssel einen entsprechenden Index an.
• Es ist nicht möglich, einen Sekundärschlüssel auf den gleichen Attributen anzulegen, auf denen schon ein anderer
Schlüssel angelegt wurde.
6 Tabellen
49
Diese Ausführungen gelten im Prinzip auch für die Schlüsselbedingungen, die bei der Attributdefinition angegeben
werden.
Fremdschlüssel werden verwendet, um zu erzwingen, daß ein Attribut bzw. eine Attributmenge einer abhängigen
Tabelle nur Werte annehmen kann, die auch als Werte (bzw. Wertkombinationen) eines Schlüssels einer anderen Tabelle,
auf die verwiesen wird, auftreten (Referentielle Integrität).
Fremdschlüsselbeziehungen (Bedingungen zur Erhaltung der referentiellen Integrität):
referential-constraint:
|--+-----------------------------+--FOREIGN KEY----------------->
’-CONSTRAINT--constraint-name-’
.-,-----------.
V
|
>--(----column-name-+--)--| references-clause |-----------------|
references-clause:
|--REFERENCES--table-name--+-----------------------+------------>
|
.-,-----------.
|
|
V
|
|
’-(----column-name-+--)-’
>--| rule-clause |--| constraint-attributes |-------------------|
rule-clause:
.-ON DELETE NO ACTION-----.
.-ON UPDATE NO ACTION-.
|--*--+-------------------------+--*--+---------------------+--*--|
’-ON DELETE--+-RESTRICT-+-’
’-ON UPDATE RESTRICT--’
+-CASCADE--+
’-SET NULL-’
• Besteht ein Fremdschlüssel nur aus einem Attribut, so kann dies direkt bei der Attributdefinition angegeben werden
(siehe column-constraints).
• Der Benutzer muß das REFERENCES–Recht auf der referenzierten Tabelle (mindestens auf den referenzierten
Attributen) oder ein übergeordnetes Recht (wie CONTROL) auf dieser Tabelle, das DBADM–Recht oder die Berechtigungsstufe SYSADM besitzen.
• Die Attribute (der zu definierenden Tabelle), deren Namen hinter dem Schlüsselwort FOREIGN KEY angegeben
werden, verweisen als Fremdschlüssel auf die angegebenen Attribute der Tabelle table-name.
• Die Tabelle table-name muß eine Basistabelle (mit CREATE TABLE erzeugt) sein.
• Die angegebenen Attribute der Tabelle table-name müssen dort als (Primär– oder Sekundär–) Schlüssel definiert sein; nicht notwendigerweise in der angegebenen Reihenfolge.
• Der Fremdschlüssel muß genau dieselbe Anzahl von Attributen wie der referenzierte Schlüssel besitzen. Attribute,
die jeweils an derselben Stelle stehen, müssen kompatibel sein. Kein Attribut darf mehrfach auftreten.
• Wird die Attributliste der referenzierten Tabelle table-name in der references-clause weggelassen, so
muß diese einen Primärschlüssel besitzen. Anstelle der Attributliste wird dann die Attributliste dieses Schlüssels
in der dort definierten Reihenfolge verwendet.
6 Tabellen
50
• In der rule-clause kann angegeben werden, ob und wie das DBMS auf das Löschen bzw. Ändern von Zeilen,
auf die die Tabelle verweist, reagieren soll (Lösch– und Änderungsregeln).
6.2.4 Lösch– und Änderungsregeln
Man kann hinter einer references-clause (die eine Fremdschlüsselbeziehung beschreibt) in der rule-clause
angeben, wie verfahren werden soll, wenn in der in der references-clause als table-name angegebenen
(Vater–)Tabelle T1 Tupel gelöscht (ON DELETE) oder geändert (ON UPDATE) werden sollen, die von Tupeln in dieser
Tabelle T2 referenziert werden.
Beim Löschen eines Tupels in T1, das in T2 noch referenziert wird, sind folgende Verfahren möglich:
Option ON DELETE . . .
NO ACTION
RESTRICT
CASCADE
SET NULL
Auswirkungen/Reaktion
kein Tupel in T1 oder T2 wird gelöscht, Fehlermeldung wird ausgegeben
bis auf sehr spezielle Ausnahmen dasselbe
Tupel in T1 wird gelöscht, verweisende Tupel in T2 ebenfalls
Tupel in T1 wird gelöscht, in den verweisenden Tupeln in T2 werden
die Attribute des Fremdschlüssels auf NULL gesetzt, sofern möglich
Wird zu einer Fremdschlüsselbeziehung keine Löschregel angegeben, so wird NO ACTION angenommen.
Beim Ändern von Schlüsselattributen eines Tupels in T1 sind folgende Verfahren möglich:
Option ON UPDATE . . .
RESTRICT
NO ACTION
Auswirkungen/Reaktion
Stimmt ein Tupel in T2 auf den Fremdschlüsselattributen mit den zu
ändernden Attributwerten des Schlüssels in T1 überein, so wird die
Operation zurückgewiesen, und alle Änderungen werden rückgängig
gemacht
Gibt es zu irgendeinem Tupel in T2 nach der Änderung in T1 kein
auf den Fremdschlüsselattributen übereinstimmendes Tupel in T1, so
wird die Operation zurückgewiesen, und alle Änderungen werden
rückgängig gemacht
Wird zu einer Fremdschlüsselbeziehung keine Änderungsregel angegeben, so wird NO ACTION angenommen.
6.2.5 Allgemeine Integritätsbedingungen
Mittels sog. Check Constraints können allgemeine Integritätsbedingungen für Attributwerte formuliert werden:
check-constraint:
|--+-----------------------------+------------------------------>
’-CONSTRAINT--constraint-name-’
>--CHECK--(--check-condition--)--| constraint-attributes |------|
6 Tabellen
51
constraint-attributes:
.-ENFORCED-----.
.-ENABLE QUERY OPTIMIZATION--.
|--*--+--------------+--*--+----------------------------+--*----|
’-NOT ENFORCED-’
’-DISABLE QUERY OPTIMIZATION-’
• Die check-condition ist eine spezielle Suchbedingung:
– Sie darf sich nur auf die Attribute der anzulegenden Tabelle beziehen.
– Sie darf keine Subqueries, Aggregatfunktionen, benutzerdefinierte Funktionen oder Registerwerte enthalten.
• Eine Check Constraint kann Teil einer Attributdefinition (siehe column-constraint) sein. Dann darf sich
die check-condition nur auf dieses Attribut beziehen.
• Check Constraints werden nicht auf Konsistenz geprüft. Es ist also möglich, eine Bedingung mehrfach bzw.
widersprüchliche Bedingungen zu definieren.
• Die Check Constraints werden überprüft, wenn Zeilen eingefügt oder verändert werden.
• Eine Check Constraint gilt für eine bestimmte Zeile als erfüllt, wenn die check-condition für diese Zeile
wahr oder unknown ist.
• Die constraint-attributes geben an, ob das DBMS die Gültigkeit der Constraint überwachen soll [oder
nicht] und ob die Constraint zur Anfrageoptimierung genutzt werden darf.
6.3 Verwalten von Tabellenberechtigungen
6.3.1 Erteilen von Tabellenberechtigungen
Tabellenberechtigungen werden mit dem GRANT–Statement erteilt:
.-PRIVILEGES-.
>>-GRANT--+-ALL--+------------+---------------------------+----->
| .-,-----------------------------------------. |
| V
| |
’---+-ALTER---------------------------------+-+-’
+-CONTROL-------------------------------+
+-DELETE--------------------------------+
+-INDEX---------------------------------+
+-INSERT--------------------------------+
+-REFERENCES--+-----------------------+-+
|
|
.-,-----------.
| |
|
|
V
|
| |
|
’-(----column-name-+--)-’ |
+-SELECT--------------------------------+
’-UPDATE--+-----------------------+-----’
|
.-,-----------.
|
|
V
|
|
’-(----column-name-+--)-’
.-TABLE-.
>--ON--+-------+--+-table-name-----+---------------------------->
6 Tabellen
52
’-view-name------’
.-,---------------------------------.
V
|
>--TO----+-+-------+--authorization-name-+-+-------------------->
| +-USER--+
|
| ’-GROUP-’
|
’-PUBLIC------------------------’
>--+-------------------+---------------------------------------><
’-WITH GRANT OPTION-’
• Das Statement ist sowohl auf Sichten (s. u.) als auch auf Basistabellen anwendbar.
• Um ein solches GRANT–Statement abzusetzen, muß man die entsprechenden Berechtigungen besitzen und mit
WITH GRANT OPTION erhalten haben (weitergeben dürfen), oder das CONTROL–Recht auf der Tabelle besitzen
oder über das DBADM–Recht oder die Berechtigungsstufe SYSADM verfügen.
• Um das CONTROL–Recht einer Tabelle/Sicht weiterzugeben, ist das DBADM–Recht oder die Berechtigungsstufe
SYSADM nötig.
• Der Zusatz WITH GRANT OPTION bewirkt, daß der/die mit authorization-name bezeichnete(n) Benutzer
die erhaltenen Rechte an dritte weitergeben darf/dürfen.
Erklärung der einzelnen Tabellenberechtigungen:
Berechtigung
ALL
ALTER
CONTROL
DELETE
INDEX
INSERT
REFERENCES
SELECT
UPDATE
REFERENCES (L)
UPDATE (L)
Erklärung
Alle Rechte außer CONTROL
Ändern der Tabellendefinition (Neue Attribute, Trigger, Anlegen/Löschen von Primär– und
Fremdschlüsseln, Check Constraints, Anlegen/Ändern des Kommentars)
Alle Rechte; Weitergabe von Rechten (außer CONTROL); Löschen der Tabelle
Löschen von Tupeln
Anlegen von Indexen
Einfügen von Tupeln
Anlegen von Fremdschlüsselbeziehungen auf alle Attribute
Zugriff auf Tupel; Anlegen von Sichten; Benutzung von EXPORT
Ändern aller Attributwerte
Anlegen von Fremdschlüsselbeziehungen auf die aufgeführten Attribute
Ändern der Werte der aufgeführten Attribute
Die Berechtigungen ALTER, INDEX und REFERENCES sind auf Sichten nicht anwendbar.
6.3.2 Entziehen von Tabellenberechtigungen
Tabellenberechtigungen werden mit dem REVOKE–Statement entzogen:
.-PRIVILEGES-.
.-TABLE-.
>>-REVOKE--+-ALL--+------------+-+--ON--+-------+--------------->
| .-,--------------. |
| V
| |
’---+-ALTER------+-+--’
+-CONTROL----+
6 Tabellen
53
+-DELETE-----+
+-INDEX------+
+-INSERT-----+
+-REFERENCES-+
+-SELECT-----+
’-UPDATE-----’
>--+-table-name-+----------------------------------------------->
’-view-name--’
.-,---------------------------------.
V
| .-BY ALL-.
>--FROM----+-+-------+--authorization-name-+-+--+--------+-----><
| +-USER--+
|
| ’-GROUP-’
|
’-PUBLIC------------------------’
• Zum Entziehen von Rechten ist das CONTROL–Recht auf der Tabelle, bzw. das DBADM–Recht oder die Berechtigungsstufe SYSADM nötig.
• Zum Entziehen des CONTROL–Rechtes und zum Entziehen von Rechten auf den Systemtabellen (s. u.) ist das
DBADM–Recht oder die Berechtigungsstufe SYSADM nötig.
6.4 Beispiele
CREATE TABLE EMPLOYEE
(ID
SMALLINT NOT NULL,
NAME
VARCHAR(9),
DEPT
SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
JOB
CHAR(5) CHECK (JOB IN (’Sales’,’Mgr’,’Clerk’)),
HIREDATE
DATE,
SALARY
DECIMAL(7,2),
PRIMARY KEY (ID),
CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) >1986 OR SALARY > 40500)
)
CREATE TABLE EMP_ACT
(EMPNO
CHAR(6) NOT NULL,
PROJNO
CHAR(6) NOT NULL,
ACTNO
SMALLINT NOT NULL,
EMPTIME
DECIMAL(5,2),
EMENDATE
DATE,
CONSTRAINT EMP_ACT_UNIQ UNIQUE (EMPNO,PROJNO,ACTNO),
CONSTRAINT FK_ACT_PROJ FOREIGN KEY (PROJNO) REFERENCES PROJECT (PROJNO)
)
CREATE TABLE DEPT
(DEPTNO SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 500 INCREMENT BY 1),
6 Tabellen
54
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT SMALLINT NOT NULL,
LOCATION CHAR(30)
)
CREATE TABLE DEPT2 LIKE DEPT
GRANT UPDATE (dept), REFERENCES ON TABLE employee TO USER karl
REVOKE ALL ON TABLE employee FROM PUBLIC
6.5 Löschen von Tabellen
Um eine Tabelle zu löschen, benötigt man eines der folgenden Rechte oder die Berechtigungsstufe SYSADM:
• das CONTROL–Recht für die Tabelle
• das DROPIN–Recht im Schema, das die Tabelle enthält
• das DBADM–Recht
Löschen einer Tabelle mit dem DROP–Statement:
>>---DROP----TABLE----table-name------------------------><
Wird eine Tabelle gelöscht, so werden in allen Tabellen, die diese Tabelle referenzieren, die zugehörigen Fremdschlüsselbeziehungen gelöscht (Löschregeln werden dabei nicht ausgeführt). Alle Indexe, die auf der Tabelle bestehen, werden
gelöscht.
6.6 Verändern von Tabellendefinitionen
Man kann zu bzw. in einer Basistabelle nachträglich
• Attribute hinzufügen,
• Integritätsbedingungen (Schlüssel, Check Constraints oder Fremdschlüsselbeziehungen) löschen oder hinzufügen,
• einige Einstellungen der Tabelle ändern,
• die Länge von Attributen vom Typ VARCHAR vergrößern oder
• die generation-expression von Attributen mit automatisch generierten Attributwerten verändern.
Insbesondere können keine Attribute entfernt werden.
Zum Ändern einer Tabellendefinition sind folgende Rechte nötig:
• Das ALTER– oder CONTROL–Recht an der zu ändernden Tabelle oder das ALTERIN–Recht für das Schema, in
dem die zu ändernde Tabelle liegt, das DBADM–Recht oder die Berechtigungsstufe SYSADM.
6 Tabellen
55
• Um eine Fremdschlüsselbeziehung anzulegen oder zu löschen, muß zusätzlich für die referenzierte Tabelle eines
der folgenden Rechte vorliegen: REFERENCES (mind. für die referenzierten Attribute), CONTROL, DBADM oder
die Berechtigungsstufe SYSADM.
• Wird eine Schlüsselbedingung gelöscht, so muß für alle Tabellen, die die zugehörigen Attribute referenzieren,
das ALTER– oder CONTROL–Recht oder das ALTERIN–Recht im Schema, das DBADM–Recht oder die Berechtigungsstufe SYSADM vorliegen, weil dort die entsprechenden Fremdschlüsselbedingungen gelöscht werden.
Verändern von Tabellendefinitionen mit dem ALTER TABLE–Statement:
>>-ALTER TABLE--table-name-------------------------------------->
.--------------------------------------------------------------------.
V
.-COLUMN-.
|
>---+-ADD--+-+--------+--| column-definition |-+----------------------+-+-><
|
+-| unique-constraint |-------------+
|
|
+-| referential-constraint |--------+
|
|
+-| check-constraint |--------------+
|
|
’-RESTRICT ON DROP------------------’
|
+-ALTER-+-FOREIGN KEY-+-constraint-name-| constraint-alteration |-+
|
’-CHECK-------’
|
|
.-COLUMN-.
|
+-ALTER--+--------+--| column-alteration |------------------------+
+-DROP--+-PRIMARY KEY----------------------+----------------------+
|
+-+-FOREIGN KEY-+--constraint-name-+
|
|
| +-UNIQUE------+
|
|
|
| +-CHECK-------+
|
|
|
| ’-CONSTRAINT--’
|
|
|
’-RESTRICT ON DROP-----------------’
|
+-LOCKSIZE--+-ROW---+---------------------------------------------+
|
’-TABLE-’
|
+-APPEND--+-ON--+-------------------------------------------------+
|
’-OFF-’
|
|
.-CARDINALITY-.
|
+-+-VOLATILE-----+--+-------------+-------------------------------+
| ’-NOT VOLATILE-’
|
’-+-ACTIVATE---+--VALUE COMPRESSION-------------------------------’
’-DEACTIVATE-’
constraint-alteration:
.-------------------------------------.
V
|
|-----------+-+-ENABLE--+--QUERY OPTIMIZATION-+-+---------------|
| ’-DISABLE-’
|
’-+-----+--ENFORCED---------------’
’-NOT-’
column-alteration:
|--column-name--------------------------------------------------->
>--+-SET--+-DATA TYPE--+-VARCHAR-----------+--(--integer--)-+-+--|
|
|
+-CHARACTER VARYING-+
| |
|
|
’-CHAR VARYING------’
| |
6 Tabellen
56
|
’-EXPRESSION AS--(--generation-expression--)------’ |
+-COMPRESS--+-SYSTEM DEFAULT-+-----------------------------+
|
’-OFF------------’
|
’-| identity-alteration |----------------------------------’
identity-alteration:
.-----------------------------------------.
V
|
|-----------+-SET INCREMENT BY--numeric-constant--+-+-----------|
+-SET--+-NO MINVALUE----------------+-+
|
’-MINVALUE--numeric-constant-’ |
+-SET--+-NO MAXVALUE----------------+-+
|
’-MAXVALUE--numeric-constant-’ |
+-SET--+-NO CYCLE-+-------------------+
|
’-CYCLE----’
|
+-SET--+-NO CACHE----------------+----+
|
’-CACHE--integer-constant-’
|
+-SET--+-NO ORDER-+-------------------+
|
’-ORDER----’
|
’-RESTART--+------------------------+-’
’-WITH--numeric-constant-’
• Mit der DROP–Klausel können nur benannte Constraints gelöscht werden. Insbesondere können Constraints, die
direkt bei einer Attributdefinition angegeben wurden, nicht gelöscht werden. Daraus folgt, daß man am besten
alle Arten von Constraints unabhängig von den Attributen definiert und ihnen einen Namen gibt, um sie später
(leichter) ändern zu können.
• Bei der Verwendung der ADD–Klausel gelten dieselben Regeln wie bei den entsprechenden Klauseln des CREATE
TABLE–Statements.
• Wird eine Schlüsselbedingung hinzugefügt, so prüft das DBMS, ob bereits ein Index existiert, der dazu verwendet
oder entsprechend geändert werden kann. Ist dies nicht der Fall, so wird ein neuer Index angelegt.
• Die ADD COLUMN–Klauseln werden immer zuerst ausgeführt.
• Via ADD RESTRICT ON DROP kann verhindert werden, daß die Tabelle oder der Tabellenbereich, in dem sie
sich befindet, gelöscht werden kann.
• Die ALTER–Klausel mit column-alteration kann auf Attribute vom Typ VARCHAR angewendet werden,
wobei die angegebene Zahl die neue Größe festlegt, die größer sein muß als der aktuelle Wert. Außerdem kann
damit auch die generation-expression für Attribute mit automatisch generierten Attributwerten geändert
werden.
• Werden Schlüsselbedingungen gelöscht, so hat dies Auswirkungen auf die zugehörigen Indexe (diese werden evtl.
gelöscht) und Referenzen (Fremdschlüsselbeziehungen in anderen Tabellen, die solche Schlüssel referenzieren,
werden gelöscht).
• Wird eine Integritätsbedingung hinzugefügt, so werden die Daten der Tabelle sofort auf Einhaltung dieser Bedingung geprüft (falls nicht gerade die Prüfung aller Constraints abgeschaltet ist – siehe SET INTEGRITY). Erfüllen
die Daten dabei die angegebene Bedingung nicht, so meldet das DBMS einen Fehler.
• Die Bedeutung der Optionen bei constraint-alteration un identity-alteration ist dieselbe wie
bei den ensprechenden Klauseln beim CREATE TABLE–Statement (siehe constraint-attributes und
identity-attributes). Mittels RESTART WITH kann eine Sequenz neu gestartet werden. Als neuer
6 Tabellen
57
Startwert wird der angegebene Wert numeric-constant oder, falls keiner angegeben wurde, der ursprüngliche
Startwert verwendet.
• Mittels DROP RESTRICT ON DROP wird bei einer Tabelle, die nicht gelöscht werden darf, dieses Verbot
zurückgenommen.
• Über APPEND kann man bestimmen, ob neue Tupel stets am Ende der Tabelle eingefügt werden (ON) oder ob
sie auch den Platz gelöschter Tupel einnehmen können (OFF). Letzteres ist die Standardeinstellung nach dem
Anlegen der Tabelle.
• Mithilfe der VOLATILE–Klausel kann man (zu Optimierungszwecken) angeben, ob die Anzahl der Tupel in der
Tabelle stark schwankt oder nicht.
• Mittels LOCKSIZE kann die Granularität von Sperren (siehe später) auf dieser Tabelle bestimmt werden:
– ROW bedeutet tupelweises Sperren und ist die Defaulteinstellung dieser Option.
– TABLE bedeutet, daß immer die ganze Tabelle gesperrt wird. Dies ist der Performanz zu–, der Nebenläufigkeit jedoch abträglich.
• Mithilfe der VALUE COMPRESSION–Klausel kann die platzsparende Speicherung von NULL–Werten oder Werten der Länge 0 (vgl. CREATE TABLE–Statement) an– (ACTIVATE) oder abgeschaltet werden (DEACTIVATE).
• Mit der COMPRESS–Klausel kann die effizientere Speicherung (vgl. CREATE TABLE–Statement) von Defaultwerten an– (SYSTEM DEFAULT) oder abgeschaltet werden (OFF).
Beispiel:
ALTER TABLE employee
ALTER name SET DATA TYPE VARCHAR (20)
ADD married CHAR(1)
DROP CONSTRAINT yearsal
6.7 Sequenzen
Sequenzen bieten die Möglichkeit, vom DBMS fortlaufende ganzahlige Werte generieren zu lassen. Sie arbeiten analog
zu der Werterzeugung in IDENTITY–Spalten, können jedoch nicht für die automatische Attributwerterzeugung, sondern
allgemein in Ausdrücken verwendet werden.
6.7.1 Anlegen von Sequenzen
Anlegen einer Sequenz mit dem CREATE SEQUENCE–Statement:
.-AS INTEGER----.
>>-CREATE SEQUENCE--sequence-name--*--+---------------+--*------>
’-AS--data-type-’
>--+------------------------------+--*-------------------------->
’-START WITH--numeric-constant-’
.-INCREMENT BY 1-----------------.
>--+--------------------------------+--*------------------------>
’-INCREMENT BY--numeric-constant-’
6 Tabellen
58
.-NO MINVALUE----------------.
>--+----------------------------+--*---------------------------->
’-MINVALUE--numeric-constant-’
.-NO MAXVALUE----------------.
.-NO CYCLE-.
>--+----------------------------+--*--+----------+--*----------->
’-MAXVALUE--numeric-constant-’
’-CYCLE----’
.-CACHE 20----------------.
.-NO ORDER-.
>--+-------------------------+--*--+----------+--*-------------><
+-CACHE--integer-constant-+
’-ORDER----’
’-NO CACHE----------------’
• Es wird eine Sequenz mit dem Namen sequence-name angelegt.
• Zum Anlegen einer Sequenz ist mindestens das CREATEIN–Recht im jeweiligen Schema nötig.
• Der Erzeuger (DEFINER) einer Sequenz erhält das ALTER– und USAGE–Recht auf dieser Sequenz.
• data-type gibt den Datentyp an, den die Werte dieser Sequenz besitzen. Folgende Datentypen sind möglich:
SMALLINT, INTEGER, BIGINT, DECIMAL (ohne Nachkommastellen), sowie benutzerdefinierte spezialisierte
Typen oder Referenztypen, deren Basistyp ein ganzahliger Typ ist. Der Default ist INTEGER.
Die weiteren Optionen bewirken dasselbe wie bei IDENTITY–Attributen (siehe Abschnitt 6.2.2).
6.7.2 Löschen und Ändern von Sequenzen
Löschen einer Sequenz mit dem DROP SEQUENCE–Statement:
.-RESTRICT-.
>>-DROP---SEQUENCE--sequence-name--+----------+----------------><
• Man muß entweder das DROPIN–Recht im jeweiligen Schema besitzen oder Erzeuger der Sequenz sein.
• Die Sequenz sequence-name wird gelöscht.
• Wird die Sequenz noch innerhalb eines Triggers, einer S QL–Funktion oder einer S QL–Methode in einem NEXT
VALUE– oder PREVIOUS VALUE–Ausdruck verwendet, so kann die Sequenz nicht gelöscht werden.
• Sequenzen, die vom System für ein IDENTITY–Attribut angelegt wurden, können nicht explizit gelöscht werden.
Ändern einer Sequenz mit dem ALTER SEQUENCE–Statement:
>>-ALTER SEQUENCE--sequence-name-------------------------------->
.------------------------------------------------.
V
|
>-----------+-RESTART--+------------------------+-+-+----------><
|
’-WITH--numeric-constant-’ |
+-INCREMENT BY--numeric-constant------+
+-+-MINVALUE--numeric-constant-+------+
| ’-NO MINVALUE----------------’
|
+-+-MAXVALUE--numeric-constant-+------+
6 Tabellen
59
| ’-NO MAXVALUE----------------’
|
+-+-CYCLE----+------------------------+
| ’-NO CYCLE-’
|
+-+-CACHE--integer-constant-+---------+
| ’-NO CACHE----------------’
|
’-+-ORDER----+------------------------’
’-NO ORDER-’
• Dabei darf jede Klausel höchstens einmal verwendet werden.
• Man muß Erzeuger der Sequenz sein oder mindestens das ALTERIN–Recht im jeweiligen Schema besitzen.
• Die einzelnen Klauseln besitzen dieselbe Bedeutung wie beim CREATE TABLE–Statement und sind in Abschnitt
6.2.2 beschrieben.
• Das Ändern einer Sequenz hat keine Wirkung auf Werte, die bereits verwendet wurden.
6.7.3 Rechtevergabe bei Sequenzen
Erteilen der Rechte auf einer Sequenz mit dem GRANT–Statement:
>>-GRANT--USAGE--ON SEQUENCE--sequence-name--TO--PUBLIC--------><
• Das Recht zur Benutzung der Sequenz in Ausdücken (USAGE) kann nur an PUBLIC weitergegeben werden.
• Man muß DEFINER der Sequenz sein, oder höherwertige Rechte besizten.
Die Rechte auf einer Sequenz können nicht widerufen werden.
6.7.4 Verwendung von Sequenzen
Auslesen von Werten aus einer Sequenz mit einem Sequenz–Referenz–Ausdruck (sequence-reference):
sequence-reference:
|--+-| nextval-expression |-+-----------------------------------|
’-| prevval-expression |-’
nextval-expression:
|--NEXT VALUE FOR--sequence-name--------------------------------|
prevval-expression:
|--PREVIOUS VALUE FOR--sequence-name----------------------------|
• Die Sequenz sequence-name wird referenziert.
• Bei NEXT VALUE FOR wird ein neuer Wert der Sequenz berechnet, und dieser wird als Ergebnis des Ausdrucks
zurückgegeben.
• Bei PREVIOUS VALUE FOR wird der letzte in derselben Applikation generierte Wert der Sequenz zurückgegeben. Insbesondere bedeutet das, daß in der Applikation vorher NEXT VALUE FOR benutzt worden sein muß.
6 Tabellen
60
• Bei mehrmaliger Verwendung des PREVIOUS VALUE FOR–Ausdrucks in einem Statement ergeben alle diese
Ausdrücke denselben Wert.
• NEXT VALUE FOR– oder PREVIOUS VALUE FOR–Ausdrücke dürfen nur an den folgenden Stellen verwendet
werden:
– In der SELECT–Klausel eines Select–Statements oder eines SELECT INTO–Statements, wenn dieses kein
DISTINCT, GROUP BY, ORDER BY, UNION, INTERSECT oder EXCEPT enthält,
– in der VALUES– oder der SELECT–Klausel eines INSERT–Statements,
– In der SET–Klausel eines UPDATE–Statements mit Ausnahme einer SELECT–Klausel eines Fullselect in
einem Ausdruck dieser SET–Klausel,
– in einem SET–Statement für Variablen (mit Ausnahme der SELECT–Klausel eines Fullselect in einem Ausdruck; in einem Trigger darf nur NEXT VALUE, nicht aber PREVIOUS VALUE benutzt werden),
– in einem VALUES INTO–Statement (mit Ausnahme der SELECT–Klausel eines Fullselect in einem Ausdruck),
– im Rumpf eines CREATE PROCEDURE–Statements oder
– in der triggered-action innerhalb eines CREATE TRIGGER–Statements (hier darf nur NEXT VALUE verwendet werden).
• NEXT VALUE FOR– oder PREVIOUS VALUE FOR–Ausdrücke dürfen an den folgenden Stellen nicht verwendet werden:
– In der Joinbedingung eines FULL OUTER Joins,
– als Defaultwert eines Attributs in einem CREATE TABLE– oder einem ALTER TABLE–Statements,
– in einer automatisch generierten Spalte in einem CREATE TABLE– oder einem ALTER TABLE–Statement,
– in der Bedingung einer CHECK–Constraint,
– in einem CREATE TRIGGER–Statement (NEXT VALUE darf hier verwendet werden) oder
– in einem CREATE VIEW–, CREATE METHOD– oder CREATE FUNCTION–Statement.
• Ein NEXT VALUE–Ausdruck darf an den folgenden Stellen nicht auftreten:
– In einem CASE–Ausdruck,
– in der Parameterliste einer Aggregatfunktion,
– in einer Subquery in einem anderen Kontext als oben explizit erlaubt,
– in einem Select–Statement, das im äußersten SELECT ein DISTINCT, GROUP BY, UNION, INTERSECT
oder EXCEPT enthält,
– in einer (verschachtelten) Tabellendefinition innerhalb einer FROM–Klausel,
– in der Parameterliste einer Funktion,
– in der WHERE–Klausel des äußersten Select–Statements, eines DELETE– oder UPDATE–Statements,
– in der ORDER BY–Klausel des äußersten Select–Statements,
– in der SELECT–Klausel des Fullselects eines Ausdrucks in der SET–Klausel eines UPDATE–Statements
oder
6 Tabellen
– in einem IF–, WHILE–, DO ...
thode oder Funktion).
61
UNTIL– oder CASE–Statement in einer S QL–Routine (Prozedur, Me-
• Falls ein Statement fehlschlägt, wird ein generierter Wert trotzdem als verbraucht betrachtet.
Es gelten noch weitere Einschränkungen, die im Manual dokumentiert sind.
Beispiele:
-- Anlegen einer Sequenz
CREATE SEQUENCE order_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE
CACHE 24
-- Verwundung von Werten einer Sequenz beim Einfuegen
INSERT INTO order(orderno, custno)
VALUES (NEXT VALUE FOR order_seq, 123456);
-- Verwundung von Werten einer Sequenz beim Aendern
UPDATE order
SET orderno = NEXT VALUE FOR order_seq
WHERE custno = 123456;
-- Auslesen von Werten einer Sequenz
VALUES (NEXT VALUE FOR order_seq)