Einführung in SQL: Druckversion: Erweiterungen
Dieser Text ist sowohl unter der „Creative Commons Attribution/Share-Alike“-Lizenz 3.0 als auch GFDL lizenziert.
Eine deutschsprachige Beschreibung für Autoren und Weiternutzer findet man in den Nutzungsbedingungen der Wikimedia Foundation.
Wegen des Umfangs und der Komplexität der einzelnen Kapitel besteht die Druckversion aus mehreren Teilen.
Dieser Abschnitt bietet vertiefte Informationen über das Arbeiten mit SQL-Datenbanken.
- DDL – Einzelheiten bietet weitere Informationen zu den DDL-Befehlen.
- Mit Fremdschlüssel-Beziehungen werden Verknüpfungen zwischen Tabellen geregelt.
- Mit SQL-Programmierung werden Verfahren behandelt, mit denen die Funktionalität einer Datenbank erweitert werden kann:
- Eigene Funktionen erweitern die internen Funktionen.
- Prozeduren steuern spezielle Arbeitsabläufe.
- Trigger steuern automatische Arbeitsabläufe.
- Tipps und Tricks enthält ein paar Hinweise zu verschiedenen Möglichkeiten.
- Änderung der Datenbankstruktur macht Anpassungen möglich.
- Testdaten erzeugen speichert eine Vielzahl zusätzlicher Datensätze.
Über das Inhaltsverzeichnis des Buches sind die anderen Teile der Druckversion zu erreichen:
| DDL – Einzelheiten |
In diesem Kapitel werden einige Befehle der Data Definition Language (DDL) vertieft behandelt.
Wegen des Umfangs mancher Befehle und Optionen werden die Abschnitte sachlich gegliedert, nicht nach einem einzelnen Befehl.
Definition einer Tabelle
Um eine Tabelle zu erzeugen, sind sehr umfangreiche Angaben nötig.
CREATE TABLE <Tabellenname> ( <Spaltenliste> [ , <Einschränkungen> ] );
Zum Erstellen einer Tabelle gehören folgende Angaben:
- der Name der Tabelle, mit dem die Daten über die DML-Befehle gespeichert und abgerufen werden
Dazu kommen – in Klammern gesetzt – die weiteren Angaben:
- die Liste der Spalten (Felder), und zwar vor allem mit den jeweiligen Datentypen.
- Angaben wie der Primärschlüssel (PRIMARY KEY, PK) oder weitere Indizes
Jede Spalte und Einschränkung wird mit einem Komma abgeschlossen; dieses entfällt vor der schließenden Klammer. Die Einschränkungen – CONSTRAINTs – werden häufig nicht sofort festgelegt, sondern durch anschließende ALTER TABLE-Befehle; sie werden deshalb getrennt besprochen.
Notwendig sind: der Name des Befehls, der Name der Tabelle, die runden Klammern, mindestens eine Spalte mit Name und Typ. Eine solche „Minimalversion“ gibt es aber nur für Code-Beispiele; in der Praxis gehören immer mehrere Spalten und der PK dazu.
Beim Entwurf einer Datenbank und ihrer Tabellen sollten Sie immer beachten:
- Datentypen, Art und Umfang der Zusatzangaben hängen vom DBMS ab.
- Primärschlüssel: Manche DBMS verlangen ausdrücklich einen PK – meistens eine ID o. ä., nur selten aus mehreren Spalten zusammengesetzt. Auch wenn es nicht verlangt wird, ist ein PK dringend zu empfehlen; eine Tabelle ohne PK ist selten sinnvoll. Dessen Inhalte müssen eindeutig sein und dürfen sich nicht wiederholen.
- Dafür wird meistens eine automatische Fortzählung (AUTO_INCREMENT) angeboten, was ohne Entwicklungsaufwand die Bedingungen eines Primärschlüssels erfüllt.
|
|
Sie müssen bei Art und Umfang aller Angaben immer die Besonderheiten Ihres DBMS beachten! |
|---|
Die Code-Auszüge stammen überwiegend aus den Skripten zur Beispieldatenbank. Die wichtigsten Bestandteile ersehen Sie aus dem folgenden Beispiel; weitere Bestandteile werden in den späteren Abschnitten behandelt.
create table Abteilung
( ID INTEGER not null auto_increment primary key,
Kuerzel VARCHAR(10) not null,
Bezeichnung VARCHAR(30) not null,
Ort VARCHAR(30)
);
Die Tabelle Abteilung wird mit vier Spalten erzeugt:
- ID ist eine ganze Zahl, die nicht NULL sein darf, ihre Werte durch die automatische Zählung erhält und als PRIMARY KEY benutzt wird.
- Kuerzel ist eine Zeichenkette mit variabler Länge (höchstens 10 Zeichen), die nicht NULL sein darf.
- Bezeichnung und Ort sind Zeichenketten mit höchstens 30 Zeichen. Der Ort darf NULL sein, die Bezeichnung nicht.
Definition einer einzelnen Spalte
Jede einzelne Spalte wird wie folgt definiert.
<Spaltenname> <Datentyp> [ <Optionen> ]
Jede der Optionen wird mit einem Schlüsselwort und der erforderlichen Angabe hinzugefügt.
Die Optionen können in der Regel kombiniert werden; die Reihenfolge muss beachtet werden. Verschiedene der Einschränkungen (siehe unten) können auch bei einer einzelnen Spalte angegeben werden.
COLLATE – Sortierungsregel
Jede Spalte kann eine eigene Regel für die alphabetische Sortierung erhalten – abweichend von der Regel der Tabelle oder Datenbank bzw. von der Standardsortierung gemäß Zeichensatz.
In der obigen Definition der Tabelle Abteilung soll die Bezeichnung nicht nach den allgemein für die Datenbank gültigen Regeln sortiert werden, sondern nach denen für kanadisches Französisch:
create /* usw. bis */
Bezeichnung VARCHAR(30) not null COLLATION FR_CA,
/* usw. */
Achtung: So funktioniert der Befehl nicht. Der obige CREATE-Befehl stammt aus der MySQL-Version, diese COLLATION-Ergänzung aber aus Firebird.
NULL-Werte zulässig oder nicht
NULL bzw. NOT NULL legt ausdrücklich fest, ob NULL-Werte in der Spalte zulässig sind oder nicht. Der Standardwert ist „zulässig“, das NULL kann deshalb entfallen.
Im obigen CREATE-Befehl gilt:
- Die Spalten ID, Kuerzel, Bezeichnung dürfen keine NULL-Werte enthalten. Die Informationen in diesen Spalten sind wesentlich für einen Datensatz; eine Speicherung ohne einen dieser Werte wäre sinnlos.
- Die Spalte Ort darf dagegen NULL-Werte enthalten. Diese Angabe ist nur eine zusätzliche Information; die Abteilung steht auch dann eindeutig fest, wenn der Sitz nicht bekannt oder noch nicht festgelegt ist.
DEFAULT – Vorgabewert
Mit DEFAULT <Wert> wird ein Standardwert festgelegt (als konstanter Wert oder als Ergebnis einer Funktion); dieser wird immer dann verwendet, wenn bei einer Neuaufnahme für diese Spalte kein Wert angegeben ist.
In der Tabelle Mitarbeiter erhält die Spalte Ist_Leiter den Vorgabewert 'N'; denn ein Mitarbeiter ist normalerweise kein Abteilungsleiter:
create table Mitarbeiter
( ID INTEGER not null auto_increment primary key,
/* usw. bis */
Ist_Leiter CHAR(1) default 'N',
Abteilung_ID INTEGER not null
);
Weit verbreitet sind dabei Standardwerte, mit denen Datum/Zeit einer Änderung und die Bearbeiterin registriert werden:
- CURRENT_TIMESTAMP als aktuelles Datum und Uhrzeit
- CURRENT_USER
AUTO_INCREMENT – automatischer Zähler
AUTO_INCREMENT legt fest, dass die Werte in dieser Spalte automatisch vom DBMS hochgezählt werden – siehe das obige Beispiel unter „Definition einer Tabelle“.
Die folgenden SQL-Systeme bieten einen automatischen Zähler als Teil der Spaltendefinition an. Teilweise hängt es von der Version ab, was möglich ist und was nicht.
| DBMS | Schlüsselwort | Beispiel | Bemerkungen |
|---|---|---|---|
| MS-SQL | IDENTITY | ALTER TABLE dbo.doc_exe
ADD column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY,
|
|
| MySQL | AUTO_INCREMENT | CREATE TABLE Abteilung
( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARYKEY, /* usw. */
);
|
|
| Oracle | AUTO INCREMENT | CREATE TABLE Fahrzeug
(ID INTEGER NOT NULL AUTO INCREMENT, /* usw. */
CONSTRAINT Fahrzeug_PK PRIMARY KEY (ID)
);
|
nur möglich bei der Lite-Version für mobile Computer, "AUTO INCREMENT" wird hier mit Leerzeichen geschrieben |
Bei den folgenden SQL-Systemen werden Zähler mit anderen Verfahren angeboten.
| DBMS | Schlüsselwörter | Beispiel | Bemerkungen |
|---|---|---|---|
| Firebird | SEQUENCE TRIGGER |
/* Definition */
CREATE SEQUENCE Fahrzeug_ID;
/* Benutzung im Trigger */
CREATE OR ALTER TRIGGER Fahrzeug_BI0 FOR Fahrzeug
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF ((new.ID IS NULL) OR (new.ID = 0))
then new.ID = next value FOR Fahrzeug_ID;
end
|
Es wird ein Zähler definiert (wahlweise je Tabelle oder pauschal). In einem Before-Insert-Trigger wird der nächste Wert abgerufen und eingefügt. |
| Firebird veraltet |
GENERATOR TRIGGER |
/* Definition */
CREATE GENERATOR Fahrzeug_ID;
SET GENERATOR Fahrzeug_ID TO 0;
/* Benutzung im Trigger */
CREATE OR ALTER TRIGGER Fahrzeug_BI0 FOR Fahrzeug
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF ((new.ID IS NULL) OR (new.ID = 0))
then new.ID = GEN_ID(Fahrzeug_ID, 1);
end
|
Es wird ein Zähler definiert (wahlweise je Tabelle oder pauschal). In einem Before-Insert-Trigger wird der nächste Wert abgerufen und eingefügt.
Das Verfahren mit GENERATOR sollte für neue Datenbanken nicht mehr benutzt werden. |
| Oracle | SEQUENCE TRIGGER |
/* Definition */
CREATE SEQUENCE Fahrzeug_ID;
/* Benutzung im Trigger */
CREATE OR REPLACE TRIGGER Fahrzeug_BI
BEFORE INSERT ON Fahrzeug
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (:NEW.ID IS NULL)
begin
/* in früheren Oracle-Versionen: */
SELECT Fahrzeug_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
/* ab Version 11g direkte Zuweisung: */
:NEW.ID := Fahrzeug_ID.NEXTVAL;
end
|
Es wird ein Zähler definiert (wahlweise je Tabelle oder pauschal). In einem Before-Insert-Trigger wird der nächste Wert abgerufen und eingefügt. |
| Oracle | SEQUENCE INSERT |
/* Definition */
CREATE SEQUENCE Fahrzeug_ID;
/* Benutzung im INSERT-Befehl */
INSERT INTO Fahrzeug
( ID, Kennzeichen /* usw. */ )
VALUES
( Fahrzeug_ID.NEXTVAL, 'BO-CR 123' /* usw. */ )
;
|
Es wird ein Zähler definiert (wahlweise je Tabelle oder pauschal). Im INSERT-Befehl wird der nächste Wert abgerufen und übernommen.
Die Version mit Trigger ist vorzuziehen. |
Wie schon früher erwähnt, gehört der Zähler gemäß AUTO_INCREMENT zur Tabelle; eine „Sequenz“ bezieht sich dagegen auf die gesamte Datenbank. Man kann ohne Weiteres eine einzige Sequenz AllMyIDs definieren und die neue ID einer jeden Tabelle daraus ableiten. In der Beispieldatenbank benutzen wir getrennte Sequenzen, weil sie für die verschiedenen DBMS „ähnlich“ aussehen soll.
Für die meisten Varianten gibt es Parameter zur genaueren Steuerung, nachzulesen in der DBMS-Dokumentation.
COMMENT – Beschreibung verwenden
Dies beschreibt den Inhalt der Spalte – nützlich für alle Spalten, sofern ein Bezeichner nicht ganz klar ist oder der Inhalt besondere Bedingungen erfüllen soll. Damit erleichtern Sie sich und anderen die Arbeit.
create table Schadensfall
( ID INTEGER not null auto_increment primary key,
Datum DATE not null,
Ort VARCHAR(200) not null
COMMENT 'nicht nur mit dem Ortsnamen, sondern auch mit Straße und äußeren Umständen'
, /* usw. */
);
Tabelle ändern
Mit ALTER TABLE wird die Struktur einer Tabelle geändert:
ALTER TABLE <Tabellenname> <Aufgabenliste> ;
Es können also mehrere Aufgaben mit einem ALTER-Befehl ausgeführt werden. Die möglichen Aufgaben sind in den einzelnen Abschnitten beschrieben.
Der Begriff COLUMN ist nicht immer Teil des Befehls: Bei manchen DBMS kann er weggelassen werden, bei manchen darf er nicht benutzt werden.
Stop – Aufgabe kann nicht ausgeführt werden
Eine Spalte kann oft nicht geändert oder gelöscht werden, wenn sie an anderer Stelle benutzt wird. Das gilt vor allem dann, wenn diese Spalte beim PRIMARY KEY, einem INDEX, einem FOREIGN KEY oder in einer CHECK-Einschränkung für die Tabelle benutzt wird. In diesen Fällen muss zunächst die „abhängige“ Konstruktion gelöscht, deaktiviert oder geändert werden. Erst danach kann die Änderung in der Tabelle ausgeführt werden.
Der Datentyp kann durch ALTER COLUMN nur dann geändert werden, wenn die „alten“ Werte automatisch (implizit) in den neuen Datentyp konvertiert werden können.
ADD COLUMN – Spalte hinzufügen
Diese Aufgabe fügt der Tabelle eine weitere Spalte hinzu.
Die Tabelle Versicherungsvertrag wird um Spalten zur Berechnung und Anpassung der Versicherungsprämie erweitert.
alter table Versicherungsvertrag
add Basispraemie DECIMAL DEFAULT 500 not null,
add Praemiensatz INTEGER DEFAULT 100 not null,
add Praemienaenderung DATE;
Die bisherigen Inhalte der Tabelle bleiben unverändert. In den neuen Spalten wird der DEFAULT-Wert eingetragen, andernfalls NULL.
ALTER COLUMN – Spalte ändern
Diese Aufgabe ändert eine Spalte dieser Tabelle. Dies kann eine Änderung des Datentyps, ein anderer DEFAULT-Wert oder eine andere Einschränkung sein.
In der Tabelle Abteilung ist die Spalte Kuerzel mit VARCHAR(10) definiert, die einzelnen Werte sind aber immer genau 4 Zeichen lang. Die Spaltendefinition soll an die Realität angepasst werden.
Firebird-Version: Maßnahme kann nicht unbedingt ausgeführt werden.alter table Abteilung
ALTER COLUMN Kuerzel TYPE CHAR(4);
AusgabeFirebird-Fehlermeldung: This operation is not defined for system tables.
unsuccessful metadata update.
New size specified for column KUERZEL must be at least 40 characters.
Mit der Option TYPE wird der Datentyp geändert. Vorhandene Inhalte werden dabei möglichst implizit konvertiert. Beispielsweise MySQL hat keine Probleme damit, den Text entsprechend abzuschneiden. Firebird weigert sich aber, obwohl die tatsächlichen Inhalte passen, sondern bringt eine völlig verwirrende Fehlermeldung, die gar nicht zu dieser Situation passt.
Dafür gibt es einen Umweg – siehe das Kapitel Änderung der Datenbankstruktur.
- Erzeugen Sie eine neue, temporäre Spalte.
- Kopieren Sie alle Inhalte durch einen UPDATE-Befehl aus der „alten“ Spalte, die geändert werden soll, in die temporäre Spalte.
- Löschen Sie die „alte“ Spalte.
- Erzeugen Sie eine neue Spalte unter dem „alten“ Namen mit den „neuen“ Eigenschaften.
- Kopieren Sie alle Inhalte durch einen UPDATE-Befehl aus der temporären Spalte in die neue Spalte, wobei sie passend konvertiert werden müssen.
- Löschen Sie die temporäre Spalte.
DROP COLUMN – Spalte entfernen
Diese Aufgabe entfernt eine Spalte aus der Tabelle, z. B. die eben erwähnte temporäre Spalte.
alter table Abteilung
DROP COLUMN Temp;
Durch Löschen einer Spalte wird nicht der Speicherplatz der Spalte freigegeben. Dafür muss (sofern erforderlich) ein vollständiges Backup + Restore der Datenbank ausgeführt werden. Aber das machen Sie ja sowieso regelmäßig.
ADD CONSTRAINT – Einschränkung hinzufügen
Diese Aufgabe erweitert die Bedingungen für die Daten der Tabelle.
ALTER TABLE <Tabellenname> ADD [ CONSTRAINT <constraint name> ] <Inhalt> ;
Einzelheiten zum <Inhalt> folgen unter CONSTRAINTs – Einschränkungen.
DROP CONSTRAINT – Einschränkung entfernen
Diese Aufgabe löscht eine Bedingung, die für die Daten der Tabelle gültig war.
ALTER TABLE <Tabellenname> DROP CONSTRAINT <constraint name>;
CONSTRAINTs – Einschränkungen
Dabei handelt es sich um Bedingungen, denen ein Datensatz entsprechen muss. Wenn eine der aktuell gültigen Bedingungen verletzt wird, wird der betreffende Datensatz nicht gespeichert. Die Bedingungen können Folgendes betreffen:
- die Schlüssel innerhalb der Tabelle: PRIMARY KEY, INDEX
- die Beziehungen zu anderen Tabellen: FOREIGN KEY
- die Werte innerhalb der Spalte: UNIQUE, CHECK
Ein CONSTRAINT kann mit oder ohne eigenen Namen festgelegt werden. Wir empfehlen die Benutzung eines Namens, weil dies die Arbeit übersichtlicher macht: Bei Verletzung einer Regel wird dieser Name meistens angegeben; anhand des Namens ist das Löschen direkt möglich. Aber das ist Geschmackssache und hängt wohl auch vom DBMS ab. Das Schlüsselwort CONSTRAINT selbst ist nur erforderlich bei Verwendung des Namens; ansonsten würden die Schlüsselwörter der einzelnen Bedingungen ausreichen.
Ein CONSTRAINT wird auf eine der folgenden Arten festgelegt:
- im CREATE TABLE-Befehl bei einer einzelnen Spalte als Bedingung für diese Spalte
- im CREATE TABLE-Befehl als Bedingung für die Tabelle, also in der Liste der <Einschränkungen>
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
Ein CONSTRAINT wird wie folgt gelöscht:
ALTER TABLE <Tabellenname> DROP CONSTRAINT <constraint name>;
Ein CONSTRAINT kann nicht geändert werden. Es ist nur Löschen und erneute Festlegung möglich.
Welche Wörter der Schlüsselbegriffe optional sind (z. B. KEY), hängt von der konkreten Situation ab.
PRIMARY KEY – Primärschlüssel der Tabelle
Der Primärschlüssel – PRIMARY KEY mit PK als gängiger Abkürzung – ist das wichtigste Mittel, mit dem die Datenbank alle Einträge verwaltet. Ohne PK sind weder Änderungen noch Löschungen einzelner Datensätze möglich, ohne alle Spalten anzugeben. Im praktischen Einsatz haben Tabellen ohne Primärschlüssel keinen Sinn. Fremdschlüssel (FOREIGN KEYs, FK) wären ohne Primärschlüssel nicht möglich.
Als Primärschlüssel geeignet sind folgende Arten von Spalten:
- der Datentyp GUID
- eine Spalte mit einem INTEGER-Datentyp, der als AUTO_INCREMENT verwendet wird oder ersatzweise durch eine SEQUENCE bestimmt wird
- Die Beispieldatenbank benutzt ausnahmslos eine solche Spalte namens ID.
- eine Spalte mit einem INTEGER-Datentyp, sofern die Werte nach Lage der Dinge eindeutig sind und während der „Lebenszeit“ der Datenbank nicht mehr geändert werden.
- Die Beispieldatenbank enthält in der Tabelle Mitarbeiter die Spalte Personalnummer. Diese ist eigentlich eindeutig und dürfte deshalb als PK verwendet werden. Da die Firma aber ihre interne Struktur ändern und die Personalnummern anpassen könnte, scheidet diese Spalte als PK aus.
- eine Kombination aus zwei Spalten, von denen jede dem PK jeweils einer anderen Tabelle entspricht, wenn die „neue“ Tabelle nur die Zuordnungen zwischen den beiden anderen Tabellen darstellt.
- Die Tabelle Zuordnung_SF_FZ der Beispieldatenbank enthält die Zuordnungen Fahrzeuge/Schadensfälle; anstelle einer eigenen ID wäre auch ein Primärschlüssel aus Fahrzeug_ID plus Schadensfall_ID möglich und sinnvoll.
Als Primärschlüssel ungeeignet oder unmöglich sind folgende Arten von Spalten:
- Unmöglich sind sämtliche Spalten (wie eine PLZ), bei denen mehrere Datensätze mit dem gleichen Wert vorkommen können.
- Unmöglich ist eine Kombination von Name/Vorname bei allen Tabellen mit Namen, weil über kurz oder lang ein „Müller, Hans“ doppelt vorkommen dürfte.
- Auch eine Kombination von Name/Vorname/Geburtstag scheidet aus dem gleichen Grund aus.
- Eine Kombination von Name/Geburtstag/Region/lfd. Nr. (ähnlich wie bei der Versicherungsnummer der deutschen Rentenversicherung) ist zwar eindeutig, aber als Kombination von vier Spalten äußerst unpraktisch.
- Eine Spalte, deren Werte sich ändern können, ist zwar möglich, aber nicht geeignet. Das gilt z. B. für das Kfz-Kennzeichen, aber auch (wie schon gesagt) für etwas wie die Personalnummer.
Der Primärschlüssel kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl bei einer einzelnen Spalte als Zuordnung für diese Spalte
create table Abteilung
( ID INTEGER not null auto_increment primary key,
Kuerzel VARCHAR(10) not null,
Bezeichnung VARCHAR(30) not null,
Ort VARCHAR(30)
);
Die Spalte ID wird direkt als (einzige) Spalte des PK definiert. Dies ist implizit ein CONSTRAINT, bekommt aber keinen eigenen Namen.
- im CREATE TABLE-Befehl in der Liste der <Einschränkungen>
CREATE TABLE Abteilung
( ID INTEGER,
Kuerzel VARCHAR(10) not null,
Bezeichnung VARCHAR(30) not null,
Ort VARCHAR(30)
CONSTRAINT Abteilung_PK PRIMARY KEY (ID)
);
Der PK bekommt als CONSTRAINT einen eigenen Namen, der Vermerk in Klammern führt die Spalten auf, die als PK verwendet werden (hier wie meistens handelt es sich um eine einzelne Spalte).
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
create table Abteilung
( ID INTEGER not null auto_increment,
Kuerzel VARCHAR(10) not null,
Bezeichnung VARCHAR(30) not null,
Ort VARCHAR(30)
);
alter Table Abteilung
add CONSTRAINT Abteilung_PK Primary Key (ID);
Die Tabelle erhält zunächst noch keinen PK, auch wenn das durch AUTO_INCREMENT suggeriert und vorbereitet wird. Vielmehr wird der PK anschließend (mit eigenem Namen) definiert; der Vermerk in Klammern führt die Spalten auf, die als PK verwendet werden.
UNIQUE – Eindeutigkeit
Ein UNIQUE KEY sorgt dafür, dass innerhalb einer Spalte bzw. einer Kombination von Spalten kein Wert doppelt auftreten kann. Beispiele sind in der Tabelle Mitarbeiter die Spalte Personalnummer und in der Tabelle Fahrzeug die Spalte Kennzeichen.
Eine solche Eindeutigkeitsbedingung kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl bei einer Spalte als Zuordnung für diese Spalte
create table Fahrzeug
( ID INTEGER not null auto_increment primary key,
Kennzeichen VARCHAR(10) not null unique,
Farbe VARCHAR(30),
Fahrzeugtyp_ID INTEGER not null
);
- im CREATE TABLE-Befehl in der Liste der <Einschränkungen> mit Bezug auf eine oder mehrere Spalten
CREATE TABLE Fahrzeug
( ID INTEGER not null auto_increment,
Kennzeichen VARCHAR(10) not null,
Farbe VARCHAR(30),
Fahrzeugtyp_ID INTEGER not null,
CONSTRAINT Fahrzeug_PK PRIMARY KEY (ID),
CONSTRAINT Fahrzeug_Kz UNIQUE (Kennzeichen)
);
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
alter TABLE Fahrzeug
ADD [ CONSTRAINT Fahrzeug_Kz ] UNIQUE (Kennzeichen);
INDEX – Suche beschleunigen
Ein INDEX ist ein Verfahren innerhalb einer Datenbank, mit dem schnell auf Datensätze zugegriffen werden kann. Vor allem der PK benutzt selbst einen Index. (Intern arbeiten die DBMS unterschiedlich; für den Nutzer sieht es immer so aus, als wenn der PK ein Index ist.) Sämtliche Spalten bzw. Kombinationen von Spalten, nach denen in SELECT-Befehlen häufiger gesucht oder sortiert wird, sollten mit einem Index versehen werden. Beispiele:
- Name/Vorname sowie PLZ und separat PLZ/Name (vielleicht auch PLZ/Straße) in Tabellen mit Adressen
- solche Spalten, die für den Nutzer wie ein PK aussehen, es aber nicht sind, z. B. in der Tabelle Mitarbeiter die Spalte Personalnummer und in der Tabelle Fahrzeug die Spalte Kennzeichen
- Die Angabe UNIQUE für eine Spalte sorgt bereits für einen Index; eine doppelte Festlegung ist nicht nötig.
- das Datum in der Tabelle Schadensfall
Wenn vorzugsweise die größeren Werte benötigt werden, ist mit DESC ein absteigender Index (wie im folgenden Beispiel) sinnvoll. In manchen Fällen sind durchaus zwei getrennte Indizes auf dieselbe Spalte angebracht – der eine ASC, der andere DESC.
Ein Index ist nicht sinnvoll, wenn eine Spalte nur wenige verschiedene Werte enthalten kann wie in der Tabelle Versicherungsvertrag die Spalte Art.
Ein Index kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl in der Liste der <Einschränkungen> mit Bezug auf eine oder mehrere Spalten
CREATE TABLE Schadensfall
( ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
Datum DATE NOT NULL,
Ort VARCHAR(200) NOT NULL,
/* usw. */
INDEX Schadensfall_Datum (Datum DESC)
);
In der Tabelle wird sofort ein Index (mit eigenem Namen) für die Spalte Datum angelegt, und zwar sollen die Werte absteigend verwaltet werden. (Das bedeutet, dass die Daten mit dem „größten“ Wert, also die aktuellsten Werte zuerst gefunden werden.) Der Standardwert, der nicht angegeben werden muss, ist ASC (= aufsteigend).
- mit einem zusätzlichen CREATE INDEX-Befehl in folgender Syntax:
CREATE [ UNIQUE ] INDEX <Indexname>
ON <Tabellenname> ( <Spaltenliste> );
„Irgendwo“ (unterschiedlich nach DBMS) kann außerdem ASC bzw. DESC festgelegt werden.
Das vorige Beispiel sieht dann unter Firebird (DESC vorgezogen) so aus:
CREATE DESC INDEX Schadensfall_Datum
ON Schadensfall (Datum);
Die Eindeutigkeitsbedingung UNIQUE benutzt intern (vermutlich immer) ebenfalls einen INDEX; dieser kann auch ausdrücklich angegeben werden:
ALTER TABLE Fahrzeug
ADD CONSTRAINT Fahrzeug_Kennzeichen UNIQUE (Kennzeichen)
USING INDEX Fahrzeug_Kennzeichen_UK;
FOREIGN KEY – Fremdschlüssel
Ein FOREIGN KEY (FK) regelt die logischen Verbindungen zwischen zwei Tabellen: Ein Datensatz in einer Tabelle darf in einer bestimmten Spalte nur solche Werte benutzen, die in einer anderen Tabelle als PK registriert sind. Beispiele:
- In der Tabelle Mitarbeiter darf als Abteilung_ID nur eine gültige ID der Tabelle Abteilung stehen.
- In der Tabelle Fahrzeug darf als Fahrzeugtyp_ID nur eine gültige ID der Tabelle Fahrzeugtyp stehen.
- In der Tabelle Fahrzeugtyp darf als Hersteller_ID nur eine gültige ID der Tabelle Fahrzeughersteller stehen.
Ein Fremdschlüssel kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl bei einer einzelnen Spalte als Bedingung für diese Spalte
- im CREATE TABLE-Befehl als Bedingung für die Tabelle, also in der Liste der <Einschränkungen>
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
Einzelheiten werden im Kapitel Fremdschlüssel-Beziehungen behandelt.
CHECK – Werteprüfungen
Ein CHECK ist eine Prüfung, ob die Werte, die für einen Datensatz gespeichert werden sollen, bestimmten Regeln entsprechen. Diese Prüfung wird sowohl bei INSERT als auch bei UPDATE vorgenommen; sie kann für eine einzelne Spalte oder für die Tabelle festgelegt werden.
Als Bedingung in der CHECK-Klausel kann im Wesentlichen alles stehen, was für die WHERE-Klausel vorgesehen ist.
Eine solche Prüfung kann wie folgt festgelegt werden:
- im CREATE TABLE-Befehl bei einer Spalte als Bedingung für diese Spalte
In der Tabelle Schadensfall sind als Schadenshoehe natürlich keine negativen Zahlen zulässig. Die Spalte Verletzte ist als CHAR(1) definiert; sinnvollerweise sind nur die Werte 'J' und 'N' zulässig.
create table Schadensfall
( ID INTEGER not null auto_increment primary key,
Datum DATE not null,
Ort VARCHAR(200) not null,
Beschreibung VARCHAR(1000) not null,
Schadenshoehe DECIMAL(16,2) check(Schadenshoehe >= 0),
Verletzte CHAR(1) not null check(Verletzte = 'J' OR Verletzte = 'N'),
Mitarbeiter_ID INTEGER not null
);
- im CREATE TABLE-Befehl als Bedingung für die Tabelle, also in der Liste der <Einschränkungen>
Bei Personen als Versicherungsnehmer benötigt man Vorname, Geburtsdatum und Führerschein sowie ein Mindestalter von 16 Jahren.
CREATE TABLE Versicherungsnehmer
( ID INTEGER NOT NULL auto_increment Primary Key,
Name VARCHAR(30) NOT NULL ,
Vorname VARCHAR(30) NOT NULL ,
Geburtsdatum DATE ,
Fuehrerschein DATE ,
/* usw. für alle anderen Spalten, danach: */
CONSTRAINT Versicherungsnehmer_CheckDatum
CHECK( ( (Geburtsdatum is null)
and (Fuehrerschein is null)
and (Vorname is null or Vorname = '') )
or (Fuehrerschein >= Geburtsdatum + 365*16) ));
Die ersten Bedingungen prüfen, ob es sich um eine Person handelt; wenn nicht, sind Führerscheinprüfung und Geburtsdatum irrelevant, und der Datensatz kann gespeichert werden. Wenn es sich um eine Person handelt, wird auch die letzte Bedingung benötigt; diese wird „näherungsweise“ geprüft und berücksichtigt, dass das DBMS ein Datum intern als ganze Zahl speichert. Alternativ könnten auch mit EXTRACT() Tag, Monat, Jahr getrennt verglichen werden. Dieses Verfahren wäre aber deutlich umständlicher; deshalb sollte es hier nicht stehen.
- im ALTER TABLE-Befehl durch ADD CONSTRAINT
In der Tabelle Versicherungsvertrag sind als Art nur bestimmte Werte zulässig: 'VK' (= Vollkasko), 'TK' (= Teilkasko incl. Haftpflicht), 'HP' (= Haftpflicht).
alter table Versicherungsvertrag
add CONSTRAINT Vertrag_CheckArt
check (Art in ('VK', 'TK', 'HP') );
Zusammenfassung
In diesem Kapitel lernten wir mehrere Verfahren kennen, mit denen einzelne Spalten und ganze Tabellen genauer festgelegt werden:
- Zu einer Spalte gehören nicht nur der Datentyp, sondern auch die Vorgabe von Werten und Wertebereichen.
- Für eine Spalte können Einschränkungen wie „Eindeutigkeit“ oder „Teil des Primärschlüssels“ oder „Teil eines Index“ gelten.
- Für eine Tabelle können Wertebereiche über mehrere Spalten geprüft werden.
- Eigentlich immer gehört zu einer Tabelle ein Primärschlüssel.
- Außerdem können Indizes und Fremdschlüssel festgelegt werden.
Übungen
| Übung 1 | Definitionen | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche sind falsch?
- Zur Definition einer Tabelle gehört unbedingt die Definition der Spalten.
- Zur Definition einer Tabelle gehört unbedingt die Definition des Primärschlüssels.
- Zur Definition einer Tabelle gehören unbedingt die Klammern.
- Die Definition von Einschränkungen ist während des CREATE-Befehls oder durch einen ALTER-Befehl möglich.
- Als UNIQUE darf nur eine Spalte festgelegt werden.
- Jede Spalte kann als NOT NULL festgelegt werden.
- Für jede Spalte können Vorgabewerte festgelegt werden.
- Es gibt Situationen, in denen die Definition einer Spalte nicht geändert werden kann.
- Der Begriff CONSTRAINT gehört zur Definition einer Einschränkung.
- Ein Primärschlüssel kann über beliebig viele Spalten festgelegt wird.
- Es ist üblich, dass der Wert eines Primärschlüssels immer wieder einmal geändert wird.
| Übung 2 | Tabellendefinition | Zur Lösung |
Bitte geben Sie an, welche Bestandteile der folgenden Definition falsch sind bzw. welche Angaben fehlen.
create table Computer
constraint ComputerID primary key (Nummer)
unique Name,
Name not null varchar collation Win1252
Nummer integer primary Key
Hersteller varchar(30)
Herstellung date
Festplatte long default 320*1024*1024*1024
Ram_Groesse long,
;
Hinweis: Bei den folgenden Definitionen verwenden Sie bitte für alle Einschränkungen geeignete Namen.
| Übung 3 | Einschränkungen allgemein definieren | Zur Lösung |
Erstellen Sie die Definition für eine Tabelle mit internationalen Postleitzahlen: laufende Nummer, Land, Code, Ortsname. Legen Sie für jede Spalte möglichst viele Einzelheiten fest; bei der Reihenfolge der Einzelheiten müssen Sie wegen der Unterschiede der DBMS nur auf die CONSTRAINTS achten.
| Übung 4 | Spalten mit Einschränkungen hinzufügen | Zur Lösung |
Ergänzen Sie die Tabelle Versicherungsvertrag um folgende Spalten:
- Basisprämie für einen Betrag, Vorgabewert 500, keine negativen Beträge
- Prämiensatz für eine Zahl, Vorgabewert 100, Minimalwert 10 [%]
| Übung 5 | Einschränkung und Index hinzufügen | Zur Lösung |
Ändern Sie die Tabelle Versicherungsvertrag so, dass die Spalte Vertragsnummer eindeutig ist und einen (ausdrücklich angegebenen) Index benutzt.
| Übung 6 | Einschränkung hinzufügen | Zur Lösung |
Ändern Sie die Tabelle Versicherungsnehmer so, dass die Spalte Eigener_Kunde nur die Werte 'J' und 'N' annehmen darf.
| Lösung zu Übung 1 | Definitionen | Zur Übung |
Die Aussagen 1, 3, 4, 6, 7, 8, 10 sind wahr, die Aussagen 2, 5, 9, 11 sind falsch.
| Lösung zu Übung 2 | Tabellendefinition | Zur Übung |
- Es fehlen die Klammern um die gesamte Auflistung aller Einzelheiten, und es fehlen Kommata nach jedem einzelnen Bestandteil. Dagegen ist das letzte Komma falsch.
- Zeilen 2/3: Diese Zeilen gehören an das Ende: Zuerst müssen die Spalten festgelegt werden, dann kann darauf Bezug genommen werden.
- Zeile 4: Es fehlt die Größenangabe für die Zeichenkette. Ob die Reihenfolge der Teile passt, hängt vom DBMS ab.
- Zeile 2 und 5: Doppelte Festlegung des Primary Key. Es bezieht sich zwar in beiden Fällen auf dieselbe Spalte, es sind aber wegen des CONSTRAINT-Namens unterschiedliche Definitionen.
| Lösung zu Übung 3 | Einschränkungen allgemein definieren | Zur Übung |
create Table PLZ_Codes
( ID INTEGER not null auto_increment
CONSTRAINT PLZ_PK primary key,
Land CHAR ( 2) not null default 'DE',
Code VARCHAR(10) not null, -- auch CHAR(10) ist denkbar
Ort VARCHAR(30) not null,
CONSTRAINT PLZ_UK unique (Land, Code)
);
| Lösung zu Übung 4 | Spalten mit Einschränkungen hinzufügen | Zur Übung |
ALTER TABLE Versicherungsvertrag
ADD [COLUMN] Basispraemie DECIMAL
DEFAULT 500 NOT NULL
CONSTRAINT Vertrag_Basispraemie_Check CHECK(Basispraemie > 0),
ADD [COLUMN] Praemiensatz INTEGER
DEFAULT 100 NOT NULL
CONSTRAINT Vertrag_Praemiensatz_Check CHECK(Praemiensatz >= 10);
| Lösung zu Übung 5 | Einschränkung und Index hinzufügen | Zur Übung |
ALTER TABLE Versicherungsvertrag
ADD CONSTRAINT Versicherungsvertrag_Nummer UNIQUE (Vertragsnummer)
USING INDEX Versicherungsvertrag_Nummer_UK;
| Lösung zu Übung 6 | Einschränkung hinzufügen | Zur Übung |
ALTER TABLE Versicherungsnehmer
ADD CONSTRAINT Versicherungsnehmer_Eigener_Kunde
CHECK( Eigener_Kunde = 'J' OR Eigener_Kunde = 'N' );
Siehe auch
In den folgenden Kapiteln sind Einzelheiten zu finden:
Bei Wikipedia gibt es grundlegende Erläuterungen:
- Globally Unique Identifier (GUID) als eindeutige Kennung
- Versicherungsnummer der deutschen Rentenversicherung
| Fremdschlüssel-Beziehungen |
In diesem Kapitel werden wir die Verknüpfungen zwischen Tabellen über Fremdschlüssel behandeln. Bereits in den einleitenden Kapiteln wurde die „referentielle Integrität“ betont, dass nämlich die Datensätze in verschiedenen Tabellen dauerhaft zueinander passen müssen. Diesem Zweck dienen die Fremdschlüssel.
Problemstellung
In der Beispieldatenbank gibt es viele Verweise von einer Tabelle auf andere Tabellen:
- Zu jedem Eintrag der Tabelle Versicherungsvertrag gehört genau ein Eintrag der Tabelle Fahrzeug: Verträge und Fahrzeuge gehören unmittelbar zusammen.
- Zu jedem Eintrag der Tabelle Versicherungsvertrag gehört ein Eintrag der Tabelle Versicherungsnehmer: Ein Vertrag ohne einen Kunden ist sinnlos.
- Einem Eintrag der Tabelle Versicherungsnehmer sind Einträge der Tabelle Versicherungsvertrag zugeordnet: Ein Kunde kann einen oder mehrere Verträge halten; zu ihm können auch null Verträge gehören, wenn er nämlich derzeit keinen Vertrag hat, aber als potenzieller Kunde weiterhin registriert ist.
- Zu jedem Vertrag gehört ein Eintrag der Tabelle Mitarbeiter: Jeder Vertrag wird hauptsächlich von einem bestimmten Mitarbeiter bearbeitet. Das ist zwar in der Praxis nicht so eng zu sehen, wird aber in unserer theoretischen Firma so geregelt.
- Umgekehrt gehört nicht zu jedem Eintrag der Tabelle Mitarbeiter ein Vertrag – z. B. bei der Abteilung „Forschung und Entwicklung“.
Es wäre viel Aufwand, wenn ein Anwender alle diese Bedingungen immer selbständig beachten müsste. In einem Büro kann man immer einmal gestört werden; und schon fehlt eine der unbedingt erforderlichen Informationen: Ein neuer Vertrag wird gespeichert, aber der Kunde fehlt noch; dann kommt ein Telefonat dazwischen; dann macht die Mitarbeiterin mit dem nächsten Vertrag weiter. Und wohin soll die Rechnung zum vorigen Vertrag gehen?
Viel sinnvoller ist es, wenn das DBMS in die Lage versetzt wird, diese Bedingungen direkt zu berücksichtigen. Dies wird über die Fremdschlüssel – englisch: ForeignKeys (FK) – geregelt.
Grundsätze der Lösung
Beziehungen beschreiben
Bei all diesen Beziehungen geht es um die referentielle Integrität, dass nämlich die Verbindungen zwischen den Tabellen und Querverweise immer auf dem aktuellen Stand sind und die Werte in allen Tabellen korrekt zusammenpassen. Weiter unten wird es als „interne Datensicherheit“ bezeichnet: Die Daten sollen innerhalb der Datenbank insofern sicher sein, dass keine Widersprüche zwischen den Daten in verschiedenen Tabellen auftreten.
Durch die Fremdschlüssel werden Beziehungen zwischen Datensätzen verschiedener Tabellen definiert. Das DBMS sorgt dann für die richtigen Verknüpfungen:
- Kein neuer Vertrag kann ohne Fahrzeug-ID, Versicherungsnehmer-ID und Mitarbeiter-ID eingetragen werden.
- Die entsprechenden Datensätze in den Tabellen, auf die verwiesen wird, müssen zuerst gespeichert werden, bevor ein Vertrag neu aufgenommen werden kann.
- Kein Versicherungsnehmer kann gelöscht werden, solange noch Verträge vorliegen.
- Bei entsprechender Festlegung können zunächst alle Verträge eines Kunden und sofort der Kundensatz selbst gelöscht werden.
Ergänzend sind weitere Bedingungen denkbar:
- Wenn Kundennummern geändert werden, müssen die abhängigen Verträge ebenso geändert werden (Update-Weitergabe); oder Änderungen an Kundennummern werden erst gar nicht zugelassen (Update-Restriktion).
Die letzten Bedingungen können vernachlässigt werden, da die Beziehungen über die IDs geregelt werden. Nach den Regeln der relationalen Datenbanken hat die ID keine andere Bedeutung als die Identifizierung der Datensätze; es gibt niemals die Notwendigkeit, sie zu ändern.
Beziehungen definieren
In der Tabellenstruktur der Beispieldatenbank sind bei den einzelnen Tabellen in der Spalte Erläuterung die Verknüpfungen aufgeführt, beispielsweise:
- Die Tabelle Versicherungsvertrag enthält die folgenden Verweise:
- Ein Eintrag in der Spalte Versicherungsnehmer-ID verweist auf eine ID der Tabelle Versicherungsnehmer.
- Die Spalte Fahrzeug-ID verweist auf eine ID der Tabelle Fahrzeug.
- Die Spalte Mitarbeiter-ID verweist auf eine ID der Tabelle Mitarbeiter.
- Die Tabelle Zuordnung_SF_FZ enthält die folgenden Verweise:
- Die Spalte Schadensfall-ID verweist auf eine ID der Tabelle Schadensfall.
- Die Spalte Fahrzeug-ID verweist auf eine ID der Tabelle Fahrzeug.
- Die Tabelle Versicherungsnehmer enthält den folgenden Verweis:
- Die Spalte Versicherungsgesellschaft-ID verweist optional (nämlich nur bei Fremdkunden) auf eine ID der Tabelle Versicherungsgesellschaft.
In allen Fällen bedeuten die Verweise: In einem Datensatz der jeweils ersten Tabelle stehen keine weiteren Einzelheiten (z. B. zum Versicherungsnehmer). Stattdessen steht dort eine ID; die Angaben dazu sind im Datensatz der „weiteren“ Tabelle unter der genannten ID zu finden. Soweit es sich um einen optionalen Verweis handelt, kann in der „ersten“ Tabelle auch der NULL-Wert stehen; ob ein NULL-Verweis mit einem Fremdschlüssel automatisiert gesteuert werden kann, hängt vom DBMS ab.
Syntax und Optionen
Es sind also zwei Punkte sicherzustellen:
- Als Fremdschlüssel in der einen Tabelle (z. B. beim Versicherungsvertrag) dürfen nur solche Werte stehen, die als Primärschlüssel in der anderen Tabelle (z. B. beim Versicherungsnehmer) vorhanden sind.
- Ein Primärschlüssel darf nur dann geändert oder gelöscht werden, wenn er nicht als Fremdschlüssel in einer weiteren Tabelle verwendet wird.
Begriffe
Auf der Ebene von Tabellen werden die folgenden Begriffe verwendet; diese beziehen sich immer auf eine bestimmte Fremdschlüssel-Beziehung.
- Die Tabelle, auf deren Primärschlüssel verwiesen wird, heißt Primärtabelle; auch die Begriffe Master-Tabelle oder Parent-Tabelle sind gebräuchlich – manchmal auch die deutsche Bezeichnung Eltern-Tabelle.
Im ersten Beispiel der Auflistung sind dies die Tabellen Versicherungsnehmer, Fahrzeug und Mitarbeiter. - Die Tabelle, die den bzw. die Fremdschlüssel enthält, bezeichnet man als Detailtabelle oder Child-Tabelle oder auch abhängige Tabelle – manchmal auch die deutsche Bezeichnung Kind-Tabelle.
Im Beispiel ist das die Tabelle Versicherungsvertrag.
Der Begriff Detailtabelle hat in diesem Beispiel nichts damit zu tun, dass in der Tabelle Einzelheiten zum Fahrzeug oder zum Sachbearbeiter stünden (das ist gerade nicht der Fall). Mit dem Beispiel im Wikipedia-Artikel „Fremdschlüssel“ wird die Bedeutung von Master und Detail verständlich: Die Kunden sind die maßgebende Tabelle; zu jedem Kunden gehören als Details „seine“ Bestellungen.
Wir sprechen deshalb von Detailtabelle mit Fremdschlüsseln: Eine Spalte in der Detail-Tabelle wird über den Fremdschlüssel verknüpft mit dem Primärschlüssel in der Primärtabelle.
Die Syntax
Die Fremdschlüssel gehören zur Definition der Tabellen, ihre Definition über FOREIGN KEY ist also Bestandteil der Data Definition Language (DDL). Wie bei anderen Erweiterungen der Tabellendefinition gibt es mehrere Wege:
- Bei der Definition einer einzelnen Spalte, die den Fremdschlüssel benutzt:
<Definition der Spalte>
-- ergänzt durch:
REFERENCES <Primärtabelle> ( <Spaltenname> )
[ <Optionen> ]
- Bei der Definition der Tabelle in der Liste der Einschränkungen (CONSTRAINTS):
[ CONSTRAINT <Constraint-Name> ]
FOREIGN KEY ( <Spaltenname> )
REFERENCES <Primärtabelle> ( <Spaltenname> )
[ <Optionen> ]
- Als Änderung der Tabellendefinition mit ALTER TABLE:
ALTER TABLE <Detailtabelle>
ADD [ CONSTRAINT <Constraint-Name> ]
FOREIGN KEY ( <Spaltenname> )
REFERENCES <Primärtabelle> ( <Spaltenname> )
[ <Optionen> ]
Zu dem Zeitpunkt, an dem ein FOREIGN KEY festgelegt wird, muss die Tabelle, auf die verwiesen wird, bereits definiert sein. Der letzte Weg ist deshalb in der Regel am sichersten: zuerst werden alle Tabellen bestimmt, danach alle FOREIGN KEYs als Verknüpfung.
Zur eigentlichen Definition gehören die folgenden Bestandteile:
- CONSTRAINT <Name>
Dies legt den Namen dieser Einschränkung fest und kann auch entfallen. - FOREIGN KEY <Spaltenname>
Dies ist der Hinweis auf einen Fremdschlüssel und bestimmt, zu welcher Spalte dieser gehört.
Bei der Definition einer einzelnen Spalte (erste Variante) ist von vornherein klar, um welche Spalte es sich handelt; dies muss deshalb nicht wiederholt werden und entfällt bei dieser Variante. - REFERENCES <Tabellenname>
Dies bestimmt die Tabelle, auf die mit dem Fremdschlüssel verwiesen wird. - <Spaltenname> in Klammern gesetzt
Damit wird festgelegt, welche Spalten in den beiden Tabellen miteinander in Beziehung gesetzt werden.
Die Optionen werden im nächsten Abschnitt erläutert.
Der Vollständigkeit halber sei darauf hingewiesen: Eine solche Verknüpfung kann sich auch auf mehrere Spalten beziehen, nämlich sowohl als Primärschlüssel als auch als Fremdschlüssel. Da ein Primärschlüssel keine weitere Bedeutung haben soll, besteht er sowieso (fast) immer aus einer einzelnen Spalte; deshalb wird in der Übersicht nur ein <Spaltenname> erwähnt.
Optionen
Die Optionen eines FOREIGN KEY bestimmen das Verhalten der Tabelle, die die Verweise (Fremdschlüssel) benutzt – also der Detailtabelle –, sobald in der Primärtabelle die Primärschlüssel geändert werden. Allgemein steht folgendes Verhalten zur Auswahl:
- NO ACTION – alle Änderungen werden verweigert
- CASCADE – die Weitergabe der Änderung an die Detailtabelle
- RESTRICT – die Verweigerung der Änderung auch in der Primärtabelle
- SET NULL – die Änderung des Verweises in der Detailtabelle auf NULL
- SET DEFAULT – die Änderung des Verweises in der Detailtabelle auf den Vorgabewert der Spalte
Die verschiedenen Update- und Lösch-Optionen werden nicht von allen DBMS unterstützt. Die Option ON UPDATE CASCADE z. B. wird von den meisten DBMS nicht angeboten.
Im einzelnen wirken sich diese Optionen wie folgt aus.
Bei Neuaufnahmen in der Primärtabelle sind die Datensätze in der übergeordneten Tabelle noch nicht vorhanden. Also kann es keine Probleme geben; deshalb muss dies bei den Optionen nicht beachtet werden.
Die folgenden Optionen wirken sich bei Änderungen und Löschungen in der Primärtabelle in gleicher Weise aus:
- ON UPDATE NO ACTION und ON DELETE NO ACTION
Die „Inaktivität“ bedeutet: Wenn ein Primärschlüssel in der Primärtabelle geändert bzw. gelöscht werden soll und abhängige Sätze in der Detailtabelle existieren, dann wird die Änderung/Löschung mit einem Fehler abgebrochen; es erfolgt ein ROLLBACK. - ON UPDATE RESTRICT und ON DELETE RESTRICT
Die „Restriktion der Aktualisierung“ bedeutet: Wenn ein Primärschlüssel in der Primärtabelle geändert bzw. gelöscht werden soll und abhängige Sätze in der Detailtabelle existieren, dann wird die Änderung/Löschung verweigert. - ON UPDATE SET NULL und ON DELETE SET NULL
Das „NULL-Setzen“ bedeutet: Wenn ein Primärschlüssel in der Primärtabelle geändert bzw. gelöscht wird, dann werden die Verweise in der Detailtabelle auf NULL gesetzt. Das ist nur möglich, wenn die betreffende Spalte NULL-Werte zulässt (also nicht mit NOT NULL definiert wurde). - ON UPDATE SET DEFAULT und ON DELETE SET DEFAULT
Das „DEFAULT-Setzen“ bedeutet: Wenn ein Primärschlüssel in der Primärtabelle geändert bzw. gelöscht wird, dann werden die Verweise in der Detailtabelle auf den Vorgabewert der betreffenden Spalte gesetzt. Das ist nur möglich, wenn für die betreffende Spalte ein Vorgabewert festgelegt ist.
Die folgende Option wirkt sich bei Änderungen und Löschungen unterschiedlich aus:
- ON UPDATE CASCADE also bei Änderungen
Mit der „Weitergabe der Aktualisierung“ werden die Fremdschlüssel in der Detailtabelle in der gleichen Weise geändert, wie der Primärschlüssel in der Primärtabelle geändert wird. - ON DELETE CASCADE also bei Löschungen
Die „Löschweitergabe“ bedeutet: Zusammen mit dem Datensatz in der Primärtabelle werden auch alle Datensätze in der Detailtabelle gelöscht, die sich auf diesen Schlüssel beziehen.
Wenn der Primärschlüssel „richtig“ definiert ist, nämlich für alle Zeiten unveränderlich ist, dann wäre die UPDATE-Option eigentlich überflüssig. Aber man sollte vorbereitet sein, falls man doch auf die Idee kommt, einen Primary Key zu ändern.
Auswirkungen
Änderungen in der Primärtabelle haben mit den Optionen nichts zu tun. Sie werden durch einen direkten Befehl – INSERT, UPDATE, DELETE – ausgelöst. Ein Fremdschlüssel steuert mit den Optionen nur, inwieweit eine solche Änderung Auswirkungen auf die Detailtabelle hat oder nicht. Allerdings kann es passieren, dass durch die Restriktion nicht nur die Änderung in der Detailtabelle, sondern auch die Änderung in der Primärtabelle verhindert wird.
Änderungen in der Detailtabelle werden durch einen FK wie folgt eingeschränkt: Bei INSERT und UPDATE dürfen in den Spalten des Fremdschlüssels nur solche Werte eingefügt werden, die in der Primärtabelle als Primärschlüssel vorhanden sind. Einzige Ausnahme ist, wenn die Fremdschlüssel-Spalte als optional definiert ist. Dann kann hier auch NULL eingefügt werden, obwohl NULL niemals als Primärschlüssel in der Primärtabelle stehen wird.
Beispiele
Versicherungsvertrag und Kunden
Beginnen wir für die Tabelle Versicherungsvertrag mit dem Verweis von der Spalte Versicherungsnehmer_ID auf die Tabelle Versicherungsnehmer.
ALTER TABLE Versicherungsvertrag
ADD CONSTRAINT Versicherungsvertrag_VN
FOREIGN KEY (Versicherungsnehmer_ID)
REFERENCES Versicherungsnehmer (ID);
Wie fast immer benutzen wir eine Einschränkung mit Namen. Wie bei den anderen Constraints hängen wir an den Namen der Tabelle „etwas“ an (Suffix), das für die Art der Einschränkung steht. Wenn eine Tabelle nur einen Fremdschlüssel bekommt, wäre FK als Suffix geeignet. Da zur Tabelle Versicherungsvertrag drei Fremdschlüssel gehören, verwenden wir stattdessen den jeweiligen Tabellen-Alias.
Mit diesem CONSTRAINT ist festgelegt: Ein neuer Versicherungsvertrag kann nur dann registriert werden, wenn die dort vorgesehene Versicherungsnehmer_ID in der Tabelle Versicherungsnehmer als ID bereits registriert ist.
Wie gesagt: Eine „richtige“ ID wird niemals mehr geändert. Vorsichtshalber legen wir aber auch die Optionen fest:
ALTER TABLE Versicherungsvertrag
ADD CONSTRAINT Versicherungsvertrag_VN
FOREIGN KEY (Versicherungsnehmer_ID)
REFERENCES Versicherungsnehmer (ID)
ON UPDATE RESTRICT
ON DELETE RESTRICT;
Die Änderung der ID oder die Löschung eines Versicherungsnehmers ist also nicht zulässig, wenn zu diesem Kunden ein Versicherungsvertrag registriert ist.
Mitarbeiter und Abteilung
Die Beziehung zwischen diesen Tabellen kann so festgelegt werden:
ALTER TABLE Mitarbeiter
ADD CONSTRAINT Mitarbeiter_FK
FOREIGN KEY (Abteilung_ID)
REFERENCES Abteilung (ID)
ON UPDATE CASCADE
ON DELETE RESTRICT;
Das DBMS sorgt damit für die interne Datensicherheit:
- Ein neuer Mitarbeiter kann nicht eingetragen werden, ohne dass die Abteilung_ID in der Tabelle Abteilung als ID vorhanden ist.
- Wenn die Nummer einer Abteilung geändert wird, wird das automatisch bei allen ihren Mitarbeitern angepasst und ebenfalls geändert.
- Wenn eine Abteilung gelöscht (d. h. geschlossen) werden soll, tritt folgende Prüfung ein:
- Durch ON DELETE RESTRICT kann sie nicht gelöscht werden, solange ihr Mitarbeiter zugeordnet sind.
- Aber mit ON DELETE CASCADE würden beim Löschen einer Abteilung automatisch alle dort arbeitenden Mitarbeiter ebenfalls gestrichen.
Kombination von Fremdschlüsseln
Grundsätzlich kann eine Detail-Tabelle gleichzeitig als Master-Tabelle (Primärtabelle) für eine andere Tabelle definiert werden. In unserer Beispieldatenbank betrifft das die mehrfachen Verknüpfungen:
Versicherungsvertrag ⇔ Versicherungsnehmer ⇔ Versicherungsgesellschaft
⇔ Fahrzeug ⇔ Fahrzeugtyp
⇔ Fahrzeughersteller
⇔ Mitarbeiter ⇔ Abteilung
Fahrzeug ⇔ Zuordnung_SF_FZ ⇔ Schadensfall
Dann sind aber nicht alle Kombinationen der Optionen CASCADE (Weitergabe) und RESTRICT (Restriktion) zulässig – weder bei DELETE noch bei UPDATE. Das DBMS prüft beim Ausführen der DDL-Befehle, ob die gewünschte Regel zulässig ist oder nicht.
Nicht zulässig sind die folgende Verknüpfungen:
Tabelle C Tabelle B Tabelle A
(Details zu Tabelle B) (Details zu Tabelle A, (Master zu Tabelle B)
(Master zu Tabelle C)
Versicherungsvertrag ⇔ Versicherungsnehmer ⇔ Versicherungsgesellschaft
ON DELETE RESTRICT ON DELETE CASCADE
Erläuterung: Es ist nicht zulässig, einen Versicherungsnehmer zu löschen, wenn zu ihm noch (mindestens) ein Vertrag registriert ist. Andererseits sollen mit einer Versicherungsgesellschaft auch alle ihre Versicherungsnehmer automatisch gelöscht werden. Diese automatische Löschung stünde im Widerspruch zur Verhinderung der Löschung bei vorhandenen Verträgen.
Zulässig sind die folgende Verknüpfungen:
Tabelle C Tabelle B Tabelle A
(Details zu Tabelle B) (Details zu Tabelle A, (Master zu Tabelle B)
(Master zu Tabelle C)
Versicherungsvertrag ⇔ Versicherungsnehmer ⇔ Versicherungsgesellschaft
ON DELETE CASCADE ON DELETE RESTRICT
Erläuterung: Das Löschen der Versicherungsgesellschaft ist nicht zulässig, wenn noch Versicherungsnehmer registriert sind. Damit wird die Weitergabe oder Restriktion der Löschung vom Versicherungsnehmer zum Versicherungsvertrag überhaupt nicht beeinflusst.
Möglich sind auch Ring-Verkettungen:
Tabelle A ⇔ Tabelle B ⇔ Tabelle C ⇔ Tabelle A
Ob Sie diese Verknüpfungen von rechts nach links lesen oder umgekehrt, ist gleichgültig: Eine Tabelle hat Verweise auf eine andere, diese auf eine nächste und eine weitere wieder auf die erste.
Rekursive Fremdschlüssel
Fremdschlüsselbeziehungen können auch rekursiv definiert werden. Dabei verweist in einer Tabelle eine abhängige Spalte auf den eigenen Primärschlüssel.
Als Beispiel verwenden wir eine hierarchische Gliederung der Abteilungen:
CREATE TABLE Abteilung
( AbtNr INTEGER NOT NULL,
UebergeordneteAbt INTEGER,
AbtName VARCHAR(100),
PRIMARY KEY (AbtNr),
FOREIGN KEY (UebergeordneteAbt)
REFERENCES Abteilung (AbtNr)
ON DELETE CASCADE
);
Die gesamte Firma wird mit AbtNr = 1 gespeichert; zu ihr gehört keine UebergeordneteAbt. Bei jeder anderen Abteilung wird in dieser Spalte die AbtNr derjenigen Abteilung registriert, der sie direkt zugeordnet ist: Eine Hauptabteilung gehört direkt zur Firma, eine beliebige Abteilung zu ihrer Hauptabteilung, eine Arbeitsgruppe zu einer bestimmten Abteilung usw.
Praktische Probleme
Rekursive Fremdschlüsselbeziehungen sind etwas problematisch in der Handhabung.
Die Neuaufnahme von Datensätzen muss in einer bestimmten Reihenfolge geschehen: zuerst die oberste Ebene (Firma), dann die nächste Ebene (Hauptabteilungen) usw. Auf jeder Ebene ist eine Neuaufnahme nur dann möglich, wenn der übergeordnete Eintrag schon vorhanden ist.
Beim Löschen von Datensätzen kann es zu verschiedenen Problemen kommen.
Mit Lösch-Weitergabe – ON DELETE CASCADE – könnten wesentlich mehr Daten gelöscht werden, als in der WHERE-Bedingung angegeben wird:
DELETE FROM Abteilung
WHERE AbtNr = 33;
Bei diesem Beispiel werden auch alle Sätze gelöscht, die der Abteilung 33 untergeordnet sind.
Mit Lösch-Restriktion – ON DELETE RESTRICT – wird nach jeder einzelnen Löschung geprüft, ob es keine Fremdschlüsselverletzung gibt. Selbst wenn alle Sätze aus der Tabelle entfernt werden sollen, kann es passieren, dass die Ausführung fehlschlägt.
DELETE FROM Abteilung;
Das liegt daran, dass bei der Ausführung des DELETE-Befehls die Sätze in einer beliebigen Reihenfolge gelöscht werden, meistens in der Reihenfolge, in der sie „real“ in der Tabelle gespeichert sind. Nur wenn die Sätze exakt in der richtigen Reihenfolge (von der untersten Abteilung beginnend bis zur obersten Abteilung) gespeichert sind, dann kann die Ausführung des DELETE-Statements gelingen.
Dass der Erfolg eines SQL-Befehls von der physischen Speicherreihenfolge der Sätze abhängig ist, darf in einem DBMS nicht vorkommen. Daher bieten einige DBMS die Möglichkeit der verzögerten Prüfung bei der Löschweitergabe.
Durch ein einziges DELETE-Statement können mehrere Sätze und unter Umständen auch alle Sätze einer Tabelle gelöscht werden. Innerhalb einer Transaktion können mehrere DELETE-Statements ausgeführt werden. Standardmäßig erfolgt die Prüfung, ob eine Lösch-Operation ausgeführt werden darf, nach jedem einzelnen Satz, der gelöscht wurde. Das hat den Vorteil, dass bei einer unzulässigen Löschung gleich abgebrochen werden kann und der Rollback nicht unnötig viel zu tun hat.
Maßnahmen
Um die oben beschriebenen Lösch-Anomalien zu vermeiden, kann bei einigen DBMS die Prüfung, ob die Löschung zulässig ist, als Gesamtprüfung stattfinden und nicht nach jedem einzelnen Satz:
- Nach der Löschung aller Sätze, die durch ein DELETE-Statement angewiesen werden, wird der Zusammenhang der Daten überprüft.
Diese Variante wird z. B. von DB2 angeboten durch die Option ON DELETE NO ACTION. - Erst zum Abschluss der Transaktion wird die Prüfung erledigt.
Diese Variante gibt es z. B. bei Oracle durch die Option INITIALLY IMMEDIATE DEFERRABLE. - Einige DBMS können Fremdschlüssel-Beziehungen deaktivieren und später wieder aktivieren. Bei einer Aktivierung muss der gesamte Datenbestand der betroffenen Tabelle überprüft werden, und es müssen Anweisungen erteilt werden, wie mit fehlerhaften Sätzen umgegangen werden soll.
- Mit Tools (z. B. Import, Load) kann man bei den meisten DBMS Sätze in eine Tabelle laden, ohne dabei die Fremdschlüssel-Beziehungen zu prüfen.
Bei DB2 z. B. ist die Tabelle danach gesperrt und muss durch das CHECK-Tool geprüft werden. Erst dann steht die Tabelle wieder für reguläre Zugriffe zur Verfügung. - Wieder andere DBMS lassen rekursive Fremdschlüsselbeziehungen erst gar nicht zu.
Reihenfolge der Maßnahmen beachten
Wenn die Tabellen in einer Datenbank mit Fremdschlüsseln verbunden sind, dann muss beim Bearbeiten der Tabellen eine bestimmte Reihenfolge eingehalten werden, und zwar sowohl beim Einfügen als auch beim Löschen. Ob auch das Ändern mit Schwierigkeiten verbunden sein kann, hängt von der Situation ab.
Alle hier genannten Probleme beziehen sich ausschließlich auf diejenigen Spalten, die mit Fremdschlüsseln an andere Tabellen gebunden sind. Änderungen in anderen Spalten können immer ohne Probleme ausgeführt werden.
Bei INSERT
Man muss mit den Tabellen beginnen, die keine Fremdschlüssel haben. Danach können die Tabellen befüllt werden, die auf diese Tabellen verweisen, und so weiter. Bei unserer Beispieldatenbank bedeutet das für einen neuen Versicherungsvertrag:
- Registriere den Versicherungsnehmer.
- Dazu ist vorher ggf. die Versicherungsgesellschaft zu speichern.
- Registriere das Fahrzeug.
- Dazu ist vorher ggf. der Fahrzeugtyp zu speichern
- und noch einen Schritt früher der Fahrzeughersteller.
- Registriere, soweit notwendig, den Mitarbeiter.
- Dazu ist vorher ggf. die Abteilung zu speichern.
Erst jetzt sind alle Voraussetzungen vorhanden, sodass der Vertrag gespeichert werden kann.
Wenn Ring-Verkettungen vorkommen, dann müssen Tools zum initialen Befüllen verwendet werden; oder es muss mit Sätzen begonnen werden, die NULL als Fremdschlüssel enthalten.
Bei DELETE
Zum Löschen von Datensätzen muss – sofern nicht mit einer automatischen Lösch-Weitergabe gearbeitet wird – genau die umgekehrte Reihenfolge eingehalten werden.
Bei UPDATE
Die Fremdschlüssel-Beziehungen verlangen, dass die Datensätze in der Primärtabelle, auf die aus der Detailtabelle verwiesen wird, vorhanden sind. Ändert man einen Fremdschlüssel in der Detailtabelle, muss der neue Wert ebenfalls in der Primärtabelle vorhanden sein. Wenn man einen Schlüsselwert in der Primärtabelle ändern will, dann ist das nur möglich, wenn der alte Wert von keinem Satz in der Detailtabelle verwendet wird. Sollte der Wert doch verwendet (referenziert) werden, dann ist der UPDATE nicht möglich.
Theoretisch wäre auch ein UPDATE-CASCADE vorstellbar. Das würde bedeuten, dass die Änderung eines Wertes in der Primärtabelle auch gleichzeitig alle referenzierten Werte in der Detailtabelle mitändert. Eine solche Funktion wird jedoch von den meisten Datenbanken nicht angeboten.
Wie geht man also vor, wenn man einen Schlüsselwert in der Primärtabelle ändern will, der von mehreren Sätzen in der Detailtabelle verwendet wird? Diese Aufgabe wird in drei Schritten gelöst:
- Einen Satz mit dem neuen Wert in der Primärtabelle einfügen (INSERT)
- Alle Sätze in der Detailtabelle ändern auf den neuen Wert (UPDATE)
- Den Satz mit dem alten Wert in der Primärtabelle löschen (DELETE)
Bestimme die „Einfüge-Reihenfolge“
Theoretisch handelt es sich hier um ein Problem der „topologischen Sortierung“.
Bei großen Datenmodellen sollte man alle vorhandenen Tabellen in der Reihenfolge notieren, in der sie befüllt werden können. Dazu kann man die Struktur der Datenbank auslesen; Tabellen und Fremdschlüssel müssen also bereits definiert sein. Beispiele dazu sind im Kapitel Tipps und Tricks zu finden.
Zusammenfassung
In diesem Kapitel erfuhren wir einiges darüber, wie mit Fremdschlüsseln (= FOREIGN KEYs) die Verbindungen zwischen Tabellen sichergestellt werden:
- Die Definition gehört zur DDL und erfolgt mit einer entsprechenden Einschränkung (CONSTRAINT), meistens über ALTER TABLE.
- Sowohl beim Einfügen von Datensätzen als auch beim Ändern und Löschen sind diese Beziehungen zu beachten.
- Daraus ergibt sich eine bestimmte Reihenfolge, welche Daten zuerst gespeichert werden müssen.
- Beim Ändern und Löschen können die Anpassungen durch eine Klausel automatisiert werden.
Übungen
Bei allen SQL-Befehlen benutzen Sie bitte CONSTRAINTS mit Namen.
| Übung 1 | Definitionen | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche sind falsch?
- Ein Fremdschlüssel legt fest, dass in einer bestimmten Spalte der einen Tabelle nur solche Werte verwendet werden können, die als Primärschlüssel der anderen Tabelle vorhanden sind.
- Die Tabelle, die den Primärschlüssel enthält, wird als Master-Tabelle bezeichnet, die Tabelle mit dem Fremdschlüssel als Detail-Tabelle.
- In unserer Beispieldatenbank ist bei der Verknüpfung Fahrzeugtyp/Fahrzeughersteller die Tabelle Fahrzeugtyp der „Master“, die Tabelle Fahrzeughersteller die Detailtabelle.
- Bei der Verknüpfung Fahrzeug/Fahrzeugtyp gilt die Tabelle Fahrzeug als Detailtabelle, die Tabelle Fahrzeugtyp als Primärtabelle.
- Ein INSERT in der Detailtabelle ist immer möglich, ohne die Werte in der Primärtabelle zu beachten.
- Ein UPDATE in der Primärtabelle ist immer möglich, ohne die Werte in der Detailtabelle zu beachten.
- Ein DELETE in der Detailtabelle ist immer möglich, ohne die Werte in der Primärtabelle zu beachten.
- Ein DELETE in der Primärtabelle ist immer möglich, ohne die Werte in der Detailtabelle zu beachten.
- Ein FOREIGN KEY wird so definiert: Er wird der Tabelle mit dem Fremdschlüssel und der betreffenden Spalte zugeordnet und verweist auf die Tabelle mit dem Primärschlüssel und der betreffenden Spalte.
- Ein FOREIGN KEY kann nicht unmittelbar bei der Definition der Fremdschlüssel-Spalte angegeben werden.
- Eine Fremdschlüssel-Beziehung kann nur zu jeweils einer Spalte der beiden Tabellen gehören.
| Übung 2 | Zusammenhänge | Zur Lösung |
Welche Fremdschlüssel sind bei den Tabellen Fahrzeug und Mitarbeiter der Beispieldatenbank vorzusehen? Nennen Sie jeweils die betreffenden Spalten. Sind bei den Primärtabellen weitere Fremdschlüssel vorzusehen?
| Übung 3 | Fremdschlüssel festlegen | Zur Lösung |
Verknüpfen Sie die Tabelle Fahrzeugtyp mit der Tabelle Fahrzeughersteller als Fremdschlüssel auf die entsprechenden Spalten.
| Übung 4 | Fremdschlüssel festlegen | Zur Lösung |
Verknüpfen Sie die Tabelle Dienstwagen mit den Tabellen Mitarbeiter und Fahrzeugtyp als Fremdschlüssel auf die entsprechenden Spalten.
| Übung 5 | Optionen bei Neuaufnahmen | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche sind falsch?
- Für Neuaufnahmen gibt es keine Option ON INSERT zur Automatisierung.
- Bei einer Neuaufnahme in der Primärtabelle müssen Verknüpfungen in der Detailtabelle nicht beachtet werden.
- Bei einer Neuaufnahme in der Detailtabelle müssen Verknüpfungen in der Primärtabelle nicht beachtet werden.
- Bei einer Neuaufnahme in der Primärtabelle kann der erforderliche Datensatz in der Detailtabelle automatisch aufgenommen werden.
- Bei einer Neuaufnahme in der Detailtabelle kann der erforderliche Datensatz in der Primärtabelle automatisch aufgenommen werden.
- Bei Neuaufnahmen in beiden Tabellen ist die Reihenfolge zu beachten.
| Übung 6 | Optionen bei Änderungen | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche sind falsch?
- Bei einer Änderung in der Primärtabelle müssen Verknüpfungen in der Detailtabelle nicht beachtet werden.
- Bei einer Änderung in der Detailtabelle müssen Verknüpfungen in der Primärtabelle nicht beachtet werden.
- Bei einer Änderung in der Primärtabelle wird der zugehörige Datensatz in der Detailtabelle automatisch geändert, sofern ON UPDATE SET DEFAULT definiert ist.
- Bei einer Änderung in der Primärtabelle wird der zugehörige Datensatz in der Detailtabelle automatisch geändert, sofern ON UPDATE CASCADE festgelegt ist.
- Eine Änderung in der Detailtabelle ändert auch den zugehörigen Datensatz in der Primärtabelle, sofern ON UPDATE CASCADE gilt.
- Sofern ON UPDATE RESTRICT festgelegt ist, wird eine Änderung in der Primärtabelle immer ausgeführt.
| Übung 7 | Die Reihenfolge | Zur Lösung |
Nennen Sie die Reihenfolge der INSERT-Befehle, wenn ein Schadensfall mit drei beteiligten Fahrzeugen aufzunehmen ist. Dabei soll ein Fahrzeug zu einem „Eigenen Kunden“ und zwei Fahrzeuge zu „Fremdkunden“ gehören; die eine „fremde“ Versicherungsgesellschaft soll schon gespeichert sein, die andere nicht.
| Lösung zu Übung 1 | Definitionen | Zur Übung |
Die Aussagen 1, 2, 4, 7, 9 sind wahr. Die Aussagen 3, 5, 6, 8, 10, 11 sind falsch.
Hinweis zu Aussage 6: Die Ergänzung „immer“ macht die Aussage falsch. Wenn die ID ausgenommen würde, wäre die Aussage wahr.
| Lösung zu Übung 2 | Zusammenhänge | Zur Übung |
Fahrzeug: Fahrzeugtyp_ID verweist auf ID der Tabelle Fahrzeugtyp.
Fahrzeugtyp: Hersteller_ID verweist auf ID der Tabelle Fahrzeughersteller.
Mitarbeiter: Abteilung_ID verweist auf ID der Tabelle Abteilung.
| Lösung zu Übung 3 | Fremdschlüssel festlegen | Zur Übung |
ALTER TABLE Fahrzeugtyp
ADD CONSTRAINT Fahrzeugtyp_FK
FOREIGN KEY (Hersteller_ID)
REFERENCES Fahrzeughersteller (ID);
| Lösung zu Übung 4 | Fremdschlüssel festlegen | Zur Übung |
ALTER TABLE Dienstwagen
ADD CONSTRAINT Dienstwagen_FZ
FOREIGN KEY (Fahrzeugtyp_ID) REFERENCES Fahrzeugtyp (ID),
ADD CONSTRAINT Dienstwagen_MI
FOREIGN KEY (Mitarbeiter_ID) REFERENCES Mitarbeiter (ID);
| Lösung zu Übung 5 | Optionen bei Neuaufnahmen | Zur Übung |
Die Aussagen 1, 2, 6 sind wahr. Die Aussagen 3, 4, 5 sind falsch.
| Lösung zu Übung 6 | Optionen bei Änderungen | Zur Übung |
Die Aussagen 3, 4 sind wahr. Die Aussagen 1, 2, 5, 6 sind falsch.
| Lösung zu Übung 7 | Die Reihenfolge | Zur Übung |
- die „neue“ Versicherungsgesellschaft speichern
- deren Kunden speichern
- dessen Fahrzeug speichern
- dessen Versicherungsvertrag speichern
- den Fremdkunden der schon registrierten Versicherungsgesellschaft speichern
- dessen Fahrzeug speichern
- dessen Versicherungsvertrag speichern
- den Schadensfall speichern
- den Schadensfall mit den Fahrzeugen verknüpfen:
- mit dem Fahrzeug des eigenen Kunden
- mit dem Fahrzeug des einen Fremdkunden
- mit dem Fahrzeug des anderen Fremdkunden
Siehe auch
Grundlagen und weitere Einzelheiten sind in den folgenden Kapiteln zu finden:
- Relationale Datenbanken – Normalisierung
- Tabellenstruktur der Beispieldatenbank
- Data Definition Language (DDL)
- DDL - Einzelheiten mit Einzelheiten zur Tabellendefinition
Wikipedia bietet verschiedene grundlegenden Informationen:
| SQL-Programmierung |
Innerhalb einer Datenbank können Arbeitsabläufe selbst gesteuert werden. Dafür gibt es Funktionen, Prozeduren und Trigger.
Allgemeine Hinweise
Bei diesen Konstruktionen gibt es relativ wenig Gemeinsamkeiten zwischen den DBMS. Für Funktionen und Prozeduren lässt bereits der SQL-Standard den DB-Anbietern „alle“ Freiheiten, wie sie diese Möglichkeiten verwirklichen wollen. Deshalb können auch wir uns nur auf einige Grundlagen beschränken und müssen erneut auf die Dokumentation des jeweiligen DBMS verweisen.
Funktionen und Prozeduren werden oft gemeinsam als Routinen bezeichnet.
Diese Elemente benutzen integrierte Funktionen, DML-Befehle und teilweise Datenbank-Operationen, verbunden in einer speziellen Programmiersprache, die prozedurales SQL o. ä. bezeichnet wird. In diesem Kapitel gibt es allgemeine Erklärungen dazu, wie solche Abläufe erstellt und programmiert werden können; in den folgenden Kapiteln werden diese Mittel konkret benutzt.
Funktionen
Eine (benutzerdefinierte Skalar-) Funktion liefert genau einen Wert eines bestimmten Datentyps. Es handelt sich dabei um eine Ergänzung zu den internen Skalarfunktionen des DBMS. Es gibt sie mit und ohne Argumente; sie werden gezielt vom Anwender bzw. einem Anwendungsprogramm aufgerufen.
- Einzelheiten dazu werden in Eigene Funktionen behandelt.
Prozeduren
Eine Prozedur – gespeicherte Prozedur, engl. StoredProcedure (SP) – ist vorgesehen für „immer wiederkehrende“ Arbeitsabläufe. Es gibt sie mit und ohne Argumente und Rückgabewerte; sie werden gezielt vom Anwender bzw. einem Anwendungsprogramm aufgerufen.
- Einzelheiten dazu werden in Prozeduren behandelt.
Trigger
Ein Trigger ist ein Arbeitsablauf, der automatisch beim Speichern in einer Tabelle ausgeführt wird. Es gibt weder Argumente noch Rückgabewerte und keinerlei direkte Zusammenarbeit zwischen der Datenbank und dem Anwender bzw. einem Anwendungsprogramm.
- Einzelheiten dazu werden in Trigger behandelt.
Routinen ohne feste Speicherung
Das, was als Prozedur gespeichert werden kann, kann in einem DBMS in der Regel auch direkt ausgeführt werden (ohne Speicherung in der Datenbank). Dazu werden die Definition von Parametern und Variablen sowie die Anweisungen mit einer EXECUTE-Anweisung aufgerufen.
Im Kapitel zu Prozeduren gibt es ein Beispiel „Testdaten in einer Tabelle erzeugen“, das auch so verwirklicht werden kann:
EXECUTE BLOCK ( Anzahl INT = ?anzahl )
RETURNS ( Maxid INT )
AS
DECLARE VARIABLE Temp INT = 0; /* usw. identisch wie bei der Prozedur */
BEGIN
Maxid = 0;
WHILE (Temp < Anzahl) DO
BEGIN /* identischer Arbeitsablauf wie bei der Prozedur */
Temp = Temp + 1;
END
select MAX(ID) from Fahrzeug into :Maxid;
SUSPEND;
END
Der Aufbau entspricht dem einer Prozedur (siehe unten). Der Unterschied besteht in der direkten Ausführung durch EXECUTE BLOCK.
Programmieren innerhalb von Routinen
Bitte haben Sie Nachsicht: Wegen der vielen Varianten bei den DBMS wurde ein Teil der folgenden Hinweise und der Beispiele in den nächsten Kapiteln nur nach der Dokumentation verfasst und nicht in der Praxis umgesetzt.
Allgemeines
Routinen – also Funktionen und Prozeduren – werden grundsätzlich mit einer Syntax ähnlich der folgenden definiert:
CREATE OR ALTER { FUNCTION | PROCEDURE } <routine-name>
( [ <parameterliste> ] )
RETURNS <parameterliste>
AS
BEGIN
<variablenliste>
<anweisungen>
END
Die Definition von Triggern verläuft so ähnlich: Parameter entfallen, aber die Art der Auslösung kommt hinzu. Die Hinweise zu Variablen und Anweisungen in den folgenden Abschnitten gelten für Trigger in gleicher Weise wie für Routinen.
Der Teil zwischen BEGIN und END (jeweils einschließlich) wird als Rumpf – engl. body – bezeichnet, alles davor heißt Kopf – engl. header – der Routine.
Bitte beachten Sie, dass jedes DBMS seine eigenen Besonderheiten hat. Die wichtigsten Unterschiede sind:
- Bei MySQL müssen CREATE und ALTER getrennt werden, bei Oracle heißt es CREATE OR REPLACE.
- RETURNS gehört zu Funktionen (bei Oracle: RETURN). Nur Firebird benutzt es auch bei Prozeduren zur Trennung der Ausgabe-Parameter.
- Ob die Parameter in Klammern stehen müssen oder nicht, ist unterschiedlich geregelt.
- AS kann teilweise auch entfallen, bei Oracle wird auch IS verwendet.
- Die <variablenliste> ist Bestandteil der <anweisungen>; bei Firebird und Oracle steht sie zwischen AS und BEGIN.
Wenn es insgesamt (einschließlich Variablen) nur eine einzige Anweisung gibt, kann auf BEGIN und END verzichtet werden; der Übersichtlichkeit halber ist ihre Verwendung aber fast immer zu empfehlen.
Gleiches gilt innerhalb einzelner Abschnitte (wie Verzweigungen oder Schleifen): Eine einzelne Anweisung kann ohne BEGIN...END angegeben werden; wenn es die Übersichtlichkeit oder Verschachtelung erfordern, ist die Verwendung dieser Schlüsselwörter vorzuziehen.
Eine Funktion benötigt als (letzte) Anweisung RETURN, mit der ein bestimmter Wert zurückgegeben wird.
Bei Verzweigungen und Schleifen kann durch LABELs der Zusammenhang deutlich gemacht werden. Meistens gibt es keine Notwendigkeit dazu, sodass wir in der Regel darauf verzichten.
Hinweis: Dieser Abschnitt beschränkt sich auf die wichtigsten Erläuterungen. Die konkreten SQL-Anweisungen sind in den folgenden Kapiteln zu finden. Außerdem gibt es zu fast allen genannten Themen weitere Möglichkeiten.
Spalten, Variable und Parameter
In diesem Buch werden nur einfache lokale Variable benutzt; deren Gültigkeitsbereich beschränkt sich auf die aktuelle Routine. Je nach DBMS stehen auch globale Variable zur Verfügung. Außerdem kann man über das Schlüsselwort CURSOR eine ganze Zeile von Tabellen oder Ergebnismengen mit einer Variablen benutzen.
In allen Fällen, in denen die Namen von Variablen oder Parametern auf die Namen von Tabellenspalten treffen, muss dem DBMS klar sein, um welche Art von Namen es sich handelt:
- MS-SQL regelt das mit '@' am Anfang des Namens von Variablen oder Parametern.
- MySQL und Oracle unterscheiden nicht. Sie müssen selbst für unterschiedliche Bezeichner sorgen.
- Firebird verlangt in diesen Situationen einen Doppelpunkt vor dem Namen von Variablen und Parametern.
Wegen der vielfältigen Unterschiede werden die wichtigsten Möglichkeiten getrennt behandelt.
SQL-Programmierung mit Firebird
Jeder Parameter, der innerhalb der Anweisungen benutzt wird und dessen Wert an die Routine übergeben oder durch die Bearbeitung zurückgegeben wird, muss im Kopf der Routine festgelegt werden: Name, Datentyp, Vorgabe- oder Anfangswert. Mehrere Parameter werden mit Komma verbunden; nach dem letzten Parameter fehlt es.
Bei Funktionen kann es nur Eingabe-Parameter geben; der Ausgabe-Parameter wird durch RETURNS immer getrennt angegeben.
Eingabe-Parameter stehen nach dem Namen der Routine vor der RETURNS-Klausel, Ausgabe-Parameter sind Teil der RETURNS-Klausel. Als Datentypen sind ab Version 2.1 auch DOMAINS zulässig. Ein einzelner Parameter wird so deklariert:
<name> <typ> [ {= | DEFAULT} <wert> ]
Bei Eingabe-Parametern sind auch Vorgabewerte möglich, die durch '=' oder DEFAULT gekennzeichnet werden. Wichtig ist: Wenn ein Parameter einen Vorgabewert erhält und deshalb beim Aufruf in der Liste nicht benutzt wird, müssen alle nachfolgenden Parameter ebenfalls mit Vorgabewert arbeiten.
Jede Variable, die innerhalb der Routine benutzt wird, muss in ihrem Kopf festgelegt werden, nämlich zwischen AS und BEGIN: Name, Datentyp, Vorgabe- oder Anfangswert. Jede Deklaration gilt als eine einzelne Anweisung und ist mit Semikolon abzuschließen.
DECLARE [VARIABLE] <name> <typ> [ {=DEFAULT} <wert> ];
Als Vorgabewert ist auch ein SQL-Ausdruck möglich.
Der einfachste Weg ist die direkte Zuweisung eines Wertes oder eines Ausdrucks (einer internen oder einer eigenen Funktion) zu einer Variablen oder einem Parameter:
<name> = <ausdruck> ; /* Standard: nur das Gleichheitszeichen */
Sehr oft werden die Werte aus einem SELECT-Befehl mit Variablen weiterverarbeitet. Dazu gibt es die INTO-Klausel:
SELECT <spaltenliste> FROM <usw. alles andere> INTO <variablenliste> ;
Die Liste der Variablen muss von Anzahl und Typ her der Liste der Spalten entsprechen. Bitte beachten Sie, dass bei Firebird die INTO-Klausel erst am Ende des Befehls stehen darf.
In ähnlicher Weise kann auch das Ergebnis einer Prozedur übernommen und in der aktuellen Routine verarbeitet werden:
EXECUTE PROCEDURE <routine-name> [ <eingabe-parameter> ]
RETURNING_VALUES <variablenliste> ; /* Variablen mit Doppelpunkt */
Jede hier genannte Variable muss (in Reihenfolge und Typ) einem der <ausgabe-parameter> der Prozedur entsprechen.
Achtung: In allen Fällen, in denen die Namen von Variablen oder Parametern auf die Namen von Tabellenspalten treffen, muss dem DBMS klar sein, um welche Art von Namen es sich handelt. Firebird verlangt in diesen Situationen einen Doppelpunkt vor dem Namen von Variablen und Parametern.
Erst die SUSPEND-Anweisung sorgt dafür, dass ein Ausgabe-Parameter vom „rufenden“ Programm entgegengenommen werden kann. Bei einer Rückgabe einfacher Werte steht diese Anweisung am Ende einer Prozedur; bei einer Funktion übernimmt RETURN diese Aufgabe. Wenn aber (wie durch einen SELECT-Befehl) mehrere Zeilen zu übergeben sind, muss SUSPEND bei jeder dieser Zeilen stehen. Ein Beispiel steht im Kapitel zu Prozeduren unter Ersatz für eine View mit Parametern.
Die IF-Abfrage steuert den Ablauf nach Bedingungen:
IF ( <bedingung> ) THEN BEGIN <anweisungen> END [ ELSE BEGIN <anweisungen> END ]
Diese Abfrage sieht so aus:
- Die <bedingung> muss in Klammern stehen; sie kann auch mit AND und OR sowie weiteren Klammern verschachtelt werden.
- Der ELSE-Zweig ist optional; die IF-Abfrage kann auch auf den IF-THEN-Abschnitt beschränkt werden.
- Der ELSE-Zweig kann durch weitere IF-Abfragen verschachtelt werden.
- Vor allem bei verschachtelten Abfragen sollten BEGIN...END immer benutzt und durch Einrückungen der Zusammenhang deutlich werden.
Es gibt zwei Arten von Schleifen: eine Schleife mit einer Bedingung und eine Schleife mit einer Ergebnismenge für eine SELECT-Abfrage.
Die WHILE-Schleife prüft eine Bedingung und wird so lange durchlaufen, wie diese Bedingung wahr ist:
WHILE ( <bedingung> ) DO BEGIN <anweisungen> END
Diese Schleife sieht so aus:
- Die <bedingung> muss in Klammern stehen; sie kann auch mit AND und OR sowie weiteren Klammern verschachtelt werden.
- Die <bedingung> wird jeweils am Anfang eines Durchgangs geprüft. Wenn ihr Wert von Anfang an FALSE ist, wird die Schleife überhaupt nicht durchlaufen.
Die FOR SELECT-Schleife erstellt durch einen SELECT-Befehl eine Ergebnismenge und führt für jede Zeile des Ergebnisses etwas aus:
FOR SELECT <abfrage-einzelheiten>
INTO <variablenliste>
DO BEGIN
<anweisungen>
END
Diese Schleife sieht so aus:
- Beim SELECT-Befehl handelt es sich um eine beliebige Abfrage.
- Für jede Zeile des Ergebnisses wird der DO-Block einmal durchlaufen.
- Die Ergebnisspalten und ihre Werte sind nur innerhalb des SELECT-Befehls bekannt, nicht innerhalb der DO-Anweisungen. Die Werte müssen deshalb zunächst an Variablen übergeben (für jede Spalte eine Variable oder Ausgabe-Parameter, mit Doppelpunkt gekennzeichnet), bevor sie in den <anweisungen> benutzt werden können.
- Wenn diese Werte für jede Zeile einzeln zurückgegeben werden sollen, wird SUSPEND als eine Anweisung benötigt.
- Wenn SUSPEND die einzige Anweisung ist und kein Einzelwert später (außerhalb der Schleife) noch benötigt wird, kann auf die INTO-Klausel verzichtet werden.
Innerhalb von Routinen sind ausschließlich DML-Befehle zulässig. Keinerlei anderer Befehl wird akzeptiert, also vor allem kein DDL-Befehl, aber auch nicht GRANT/REVOKE (DCL) oder COMMIT/ROLLBACK (TCL).
Sämtliche DML-Befehle, die innerhalb einer Routine ausgeführt werden, gehören zurselben Transaktion wie die Befehle, durch die sie aufgerufen bzw. ausgelöst werden.
Jede einzelne Anweisung innerhalb einer Routine und jeder SQL-Befehl müssen mit einem Semikolon abgeschlossen werden. Das ist kein Problem, wenn nur ein einzelner CREATE PROCEDURE o. ä. ausgeführt werden soll; dann wird das abschließende Semikolon weggelassen, und es gibt keine Unklarheiten (siehe ein Firebird-Beispiel im Trigger-Kapitel).
Bei mehreren Routinen nacheinander – wie im Skript zur Beispieldatenbank – muss das DBMS zwischen den verschiedenen Abschlusszeichen unterscheiden. Dazu dient SET TERM (TERM steht für Terminator, also Begrenzer):
SET TERM ^ ;
CREATE OR ALTER TRIGGER Abteilung_BI0 FOR Abteilung
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF ((new.ID IS NULL) OR (new.ID = 0))
THEN new.ID = NEXT VALUE FOR Abteilung_ID;
END
^
SET TERM ; ^
Zuerst wird der Begrenzer für SQL-Befehle auf '^' geändert; das Abschlusszeichen für einzelne Anweisungen bleibt das Semikolon. Dann folgen alle Befehle zur Trigger-Definition; jeder einzelne Trigger wird mit dem neuen Begrenzer beendet. Abschließend wird der Begrenzer wieder auf das Semikolon zurückgesetzt.
SQL-Programmierung mit MS-SQL
Als erste Anweisung einer Routine sollte immer SET NOCOUNT ON; verwendet werden; dies beschleunigt die Ausführung.
Jeder Parameter, dessen Wert an die Routine übergeben oder durch die Bearbeitung zurückgegeben wird, muss im Kopf festgelegt werden: Name, Datentyp, Verwendung für Eingabe und/oder Ausgabe, Vorgabe- oder Anfangswert. Mehrere Parameter werden mit Komma verbunden; nach dem letzten Parameter fehlt es. Bei Funktionen kann es nur Eingabe-Parameter geben; der Ausgabe-Parameter wird durch RETURNS immer getrennt angeben.
Ein einzelner Parameter wird so deklariert:
<name> <typ> [ = <wert> ] [ OUT | OUTPUT ]
Parameternamen müssen immer mit '@' beginnen. Ausgabe-Parameter werden mit OUT bzw. OUTPUT markiert; alle anderen sind Eingabe-Parameter. Durch das Gleichheitszeichen kann ein Vorgabewert zugewiesen werden.
Jede Variable, die innerhalb der Routine benutzt wird, muss in ihrem Rumpf festgelegt werden: Name, Datentyp, Vorgabe- oder Anfangswert. Jede Deklaration gilt als eine einzelne Anweisung und ist mit Semikolon abzuschließen.
DECLARE <name> [AS] <typ> [ = <wert> ];
Bei MS-SQL muss der Name immer mit '@' beginnen. Als Vorgabewert ist auch ein SQL-Ausdruck möglich.
Der einfachste Weg ist die direkte Zuweisung eines Wertes oder eines Ausdrucks (einer internen oder einer eigenen Funktion) zu einer Variablen oder einem Parameter mit dem SET-Befehl:
SET <name> = <ausdruck> ;
Sehr oft werden die Werte aus einem SELECT-Befehl mit Variablen weiterverarbeitet. Ein einzelner Wert wie das Ergebnis von SELECT COUNT(*) wird ebenfalls durch SET der Variablen zugewiesen. Mehrere Werte können innerhalb des SELECT direkt zugewiesen werden:
SELECT @Variable1 = <spalte1>, @Variable2 = <spalte2> FROM <tabellenliste> /* usw. weitere Bedingungen */
Für eine Ergebnismenge wird ein CURSOR benötigt, der mit FETCH einen Datensatz holt und den Wert einer Spalte mit INTO an eine Variable übergibt.
Eine Routine wird meistens mit EXECUTE ausgeführt. Rückgabewerte, die über OUTPUT-Parameter bestimmt werden, werden vorher deklariert und mit dem <ausgabe-parameter> der Prozedur verbunden.
declare @inputvariable varchar(25);
set @inputvariable = 'Meier';
declare @outputvariable MONEY;
execute myprocedure @inputparameter = @inputvariable, @outputparameter = @outputvariable OUTPUT;
select @outputvariable;
Jede hier genannte Variable muss (in Reihenfolge und Typ) allen Parametern der Prozedur entsprechen.
Für EXECUTE (auch die Abkürzung EXEC ist möglich) gibt es viele Varianten für Aufruf und Zuweisung der Parameter.
Die IF-Abfrage steuert den Ablauf nach Bedingungen:
IF <bedingung>
BEGIN
<anweisungen>
END
[ ELSE IF <bedingung>
BEGIN
<anweisungen>
END ]
[ ELSE
BEGIN
<anweisungen>
END ]
END
Diese Abfrage sieht so aus:
- Bei <bedingung> handelt es sich um eine einfache Prüfung, die nicht mit AND oder OR erweitert werden kann.
- Wenn ein SELECT Teil der <bedingung> ist, muss es in Klammern stehen.
- Der ELSE-Zweig ist optional. Es ist auch möglich, dass nur der IF-THEN-Abschnitt ausgeführt werden muss.
- Durch ELSE IF-Zweige sind Verschachtelungen, also auch weitere Prüfungen möglich.
- Vor allem bei verschachtelten Abfragen sollten BEGIN...END immer benutzt und durch Einrückung den Zusammenhang deutlich machen.
Die WHILE-Schleife prüft eine Bedingung und wird so lange durchlaufen, wie diese Bedingung wahr ist:
WHILE <bedingung> BEGIN <anweisungen> END
Dabei sind folgende Punkte wichtig:
- Bei <bedingung> handelt es sich um eine einfache Prüfung, die nicht mit AND oder OR erweitert werden kann.
- Wenn ein SELECT Teil der <bedingung> ist, muss es in Klammern stehen.
- Die <bedingung> wird jeweils am Anfang eines Durchgangs geprüft. Wenn ihr Wert von Anfang an FALSE ist, wird die Schleife überhaupt nicht durchlaufen.
- Schleifen können verschachtelt werden. Vor allem dann sollten BEGIN...END immer benutzt und durch Einrückung den Zusammenhang deutlich machen.
Eine Schleife oder ein IF-Zweig kann mit BREAK vorzeitig abgebrochen; mit CONTINUE kann direkt der nächste Durchlauf begonnen werden. Bei Verschachtelung wird mit BREAK zur nächsthöheren Ebene gesprungen.
Um alle Datensätze einer Ergebnismenge, also eines SELECT-Befehls zu durchlaufen, wird ein CURSOR benötigt. Dies wird in diesem Buch nicht behandelt.
Einige Anweisungen können nicht in einer Routine verwendet werden. Dazu gehören vor allem der Wechsel der aktuellen Datenbank sowie CREATE/ALTER für Views, Routinen und Trigger. Im Gegensatz zu anderen DBMS ist aber z. B. CREATE TABLE und unter Umständen auch COMMIT bzw. ROLLBACK möglich.
Bei MS-SQL gibt es keine Notwendigkeit, zwischen Anweisungen innerhalb einer Routine und getrennten SQL-Befehlen zu unterscheiden: Jede Anweisung wird mit Semikolon abgeschlossen; ein „selbständiger“ SQL-Befehl wird durch GO abgeschlossen und ausgeführt.
SQL-Programmierung mit MySQL
MySQL hat gespeicherte Prozeduren und Funktionen erst mit Version 5 eingeführt, sodass noch nicht alle Wünsche erfüllt werden. Aber das wird natürlich von Version zu Version besser.
Bitte achten Sie unbedingt darauf, dass sich die Namen von Parametern und Variablen von Spaltennamen unterscheiden, die in derselben Anweisung vorkommen. Wenn Sie gegen diese Bedingung verstoßen, gibt es unvorhersehbare Ergebnisse, weil die Namen der Variablen Vorrang haben gegenüber gleichnamigen Spalten. Ein gängiges Verfahren ist es (wie bei MS-SQL), dass diese Namen mit '@' beginnen.
Jeder Parameter, der innerhalb der Anweisungen benutzt wird und dessen Wert an die Routine übergeben oder durch die Bearbeitung zurückgegeben wird, muss im Kopf der Routine festgelegt werden: Name, Datentyp, Verwendung für Eingabe und/oder Ausgabe, Vorgabe- oder Anfangswert. Mehrere Parameter werden mit Komma verbunden; nach dem letzten Parameter fehlt dies.
Bei Funktionen kann es nur Eingabe-Parameter geben; der Ausgabe-Parameter wird durch RETURNS immer getrennt angeben.
Ein einzelner Parameter wird so deklariert:
[ IN | OUT | INOUT ] <name> <typ>
Die Festlegung als Eingabe-Parameter kann entfallen; IN ist der Standardwert.
Jede Variable, die innerhalb der Routine benutzt wird, muss in ihrem Rumpf festgelegt werden: Name, Datentyp, Vorgabe- oder Anfangswert. Jede Deklaration gilt als eine einzelne Anweisung und ist mit Semikolon abzuschließen.
DECLARE <name> <typ> [ DEFAULT <wert> ];
Als Vorgabewert ist auch ein SQL-Ausdruck möglich.
Der einfachste Weg ist die direkte Zuweisung eines Wertes oder eines Ausdrucks (einer internen oder einer eigenen Funktion) zu einer Variablen oder einem Parameter mit dem SET-Befehl:
SET <name> = <ausdruck> ;
Sehr oft werden die Werte aus einem SELECT mit Variablen weiterverarbeitet. Dazu gibt es die INTO-Klausel, die direkt nach der <spaltenliste> kommt. Die Liste der Variablen muss von Anzahl und Typ her der Liste der Spalten entsprechen.
SELECT <spaltenliste> INTO <variablenliste> FROM <usw. alles andere> ;
Eine Prozedur wird mit CALL ausgeführt. Rückgabewerte, die über OUT-Parameter bestimmt werden, werden vorher deklariert.
declare @inputvariable varchar(25);
SET @inputvariable = 'Meier';
declare @outputvariable MONEY = 0;
CALL myprocedure (@inputvariable, @outputvariable);
SELECT @outputvariable;
Jede hier genannte Variable muss (in Reihenfolge und Typ) einem der <ausgabe-parameter> der Prozedur entsprechen.
MySQL kennt zwei Arten, um auf unterschiedliche Bedingungen zu reagieren:
Die IF-Abfrage steuert den Ablauf nach Bedingungen:
IF <bedingung> THEN
BEGIN
<anweisungen>
END
[ ELSEIF <bedingung> THEN
BEGIN
<anweisungen>
END ]
[ ELSE
BEGIN
<anweisungen>
END ]
END IF
Diese Abfrage sieht so aus:
- Bei <bedingung> handelt es sich um eine einfache Prüfung, die nicht mit AND oder OR erweitert werden kann.
- Der ELSE-Zweig ist optional. Es ist auch möglich, dass nur der IF-THEN-Abschnitt ausgeführt werden muss.
- Durch ELSEIF-Zweige sind Verschachtelungen, also auch weitere Prüfungen möglich.
- Vor allem bei verschachtelten Abfragen sollten BEGIN...END immer benutzt und durch Einrückungen der Zusammenhang deutlich gemacht werden.
Die CASE-Prüfung entspricht der Fallunterscheidung aus dem Kapitel Nützliche Erweiterungen:
-- Variante 1
CASE <ausdruck>
WHEN <ausdruck 1> THEN BEGIN <anweisungen 1> END
[ WHEN <ausdruck 1> THEN BEGIN <anweisungen 2> END ] /* usw. */
[ ELSE BEGIN <anweisungen n> END ]
END CASE
-- Variante 2
CASE
WHEN <bedingung 1> THEN BEGIN <anweisungen 1> END
[ WHEN <bedingung 2> THEN BEGIN <anweisungen 2> END ] /* usw. */
[ ELSE BEGIN <anweisungen n> END ]
END CASE
Bei dieser Prüfung gelten die gleichen Prüfungen wie bei der Fallunterscheidung mit folgenden Abweichungen:
- Im ELSE-Zweig darf es keine NULL-Anweisung geben.
- Die Verzweigung muss mit END CASE abgeschlossen werden.
MySQL kennt mehrere Arten von Schleifen, aber keine FOR-Schleife. Wenn ITERATE oder LEAVE verwendet werden, müssen LABELs gesetzt werden.
Die LOOP-Schleife arbeitet ohne Bedingung am Anfang oder Ende und wird solange durchlaufen, bis – vor allem aufgrund einer „inneren“ Bedingung – die LEAVE-Anweisung getroffen wird. Mit ITERATE wird direkt der nächste Durchgang gestartet.
LOOP BEGIN <anweisungen> END END LOOP ;
Die REPEAT-Schleife wird mindestens einmal durchlaufen, und zwar solange, bis die Ende-Bedingung FALSE ergibt oder bis – aufgrund einer „inneren“ Bedingung – die LEAVE-Anweisung getroffen wird.
REPEAT BEGIN <anweisungen> END UNTIL <bedingung> END REPEAT
Die WHILE-Schleife prüft eine Bedingung und wird so lange durchlaufen, wie diese Bedingung wahr ist:
WHILE <bedingung> DO BEGIN <anweisungen> END END WHILE
Die <bedingung> wird jeweils am Anfang eines Durchgangs geprüft. Wenn ihr Wert von Anfang an FALSE ist, wird die Schleife überhaupt nicht durchlaufen.
Um alle Datensätze einer Ergebnismenge, also eines SELECT-Befehls zu durchlaufen, wird ein CURSOR benötigt. Dies wird in diesem Buch nicht behandelt.
Innerhalb von eigenen Funktionen, Prozeduren und Triggern sind nicht alle SQL-Befehle zulässig. Bitte lesen Sie in Ihrer Dokumentation Einzelheiten nach.
Jede einzelne Anweisung innerhalb einer Routine und jeder SQL-Befehl müssen mit einem Semikolon abgeschlossen werden. Das ist kein Problem, wenn nur ein einzelner CREATE PROCEDURE o. ä. ausgeführt werden soll; dann wird das abschließende Semikolon weggelassen, und es gibt keine Unklarheiten (siehe ein MySQL-Beispiel zu Trigger).
Wenn mehrere Routinen nacheinander erzeugt werden, muss das DBMS zwischen den verschiedenen Arten von Abschlusszeichen unterscheiden. Dazu dient der delimiter-Befehl:
delimiter //
CREATE OR ALTER TRIGGER Mitarbeiter_BD
ACTIVE BEFORE DELETE ON Mitarbeiter
FOR EACH ROW
BEGIN
UPDATE Dienstwagen
SET Mitarbeiter_ID = NULL
WHERE Mitarbeiter_ID = old.ID;
END
//
delimiter ;
Zuerst wird der Begrenzer für SQL-Befehle auf '//' geändert; das Abschlusszeichen für einzelne Anweisungen bleibt das Semikolon. Dann folgen alle Befehle zur Trigger-Definition; jeder einzelne wird mit dem neuen Begrenzer beendet. Abschließend wird der Begrenzer wieder auf das Semikolon zurückgesetzt.
SQL-Programmierung mit Oracle
Bei Oracle gilt das prozedurale SQL nicht als Teil des DBMS, sondern als spezielle Programmiersprache PL/SQL. In der Praxis wird beides gemischt verwendet.
Bitte achten Sie unbedingt darauf, dass sich die Namen von Parametern und Variablen von Spaltennamen unterscheiden, die in derselben Routine vorkommen.
Jeder Parameter, dessen Wert an die Routine übergeben oder durch die Bearbeitung zurückgegeben wird, muss im Kopf der Routine festgelegt werden: Name, Datentyp, Verwendung für Eingabe und/oder Ausgabe, Vorgabe- oder Anfangswert. Mehrere Parameter werden mit Komma verbunden; nach dem letzten Parameter fehlt dies.
Bei Funktionen kann es nur Eingabe-Parameter geben; der Ausgabe-Parameter wird durch RETURN (nicht wie sonst oft durch RETURNS) getrennt angegeben.
Ein einzelner Parameter wird so deklariert:
<name> [ IN | OUT | IN OUT ] <typ>
Die Festlegung als Eingabe-Parameter kann entfallen; IN ist der Standardwert. Beim Datentyp darf die Größe nicht angegeben werden: VARCHAR2 ist zulässig, aber VARCHAR2(20) nicht.
Jede Variable, die innerhalb der Routine benutzt wird, muss in ihrem Kopf festgelegt werden, nämlich zwischen AS/IS und BEGIN: Name, Datentyp und ggf. Vorgabewert; das Schlüsselwort DECLARE entfällt. Jede Deklaration gilt als eine einzelne Anweisung und ist mit Semikolon abzuschließen.
<name> <typ> [ := <ausdruck> ] ;
Als Vorgabewert ist auch ein SQL-Ausdruck möglich.
Der einfachste Weg ist die direkte Zuweisung eines Wertes oder eines Ausdrucks (einer internen oder einer eigenen Funktion) zu einer Variablen oder einem Parameter:
<name> := <ausdruck> ;
Bitte beachten Sie, dass (wie bei Pascal) Doppelpunkt und Gleichheitszeichen zu verwenden sind.
Sehr oft werden die Werte aus einem SELECT-Befehl mit Variablen weiterverarbeitet. Dazu gibt es die INTO-Klausel, die direkt nach der <spaltenliste> kommt:
SELECT <spaltenliste> INTO <variablenliste> FROM <usw. alles andere> ;
Die Liste der Variablen muss von Anzahl und Typ her der Liste der Spalten entsprechen.
In ähnlicher Weise kann auch das Ergebnis einer Prozedur übernommen und in der aktuellen Routine verarbeitet werden:
- Deklaration der Übergabevariablen:
- direkt:
<variablenname> <typ>; - oder unter Bezug auf den Typ einer definierten Variablen:
<variablenname> <package_name.typ_name>;
- direkt:
- Aufruf der Prozedur:
<prozedur_name> ( <Eingabe_Parameter>, <Ausgabe_Parameter> ); - Weiterarbeiten mit den Ausgabeparametern aus der Prozedur
Jede hier genannte Variable muss (in Reihenfolge und Typ) einem der <parameter> der Prozedur entsprechen.
Oracle kennt zwei Arten, um auf unterschiedliche Bedingungen zu reagieren:
Die IF-Abfrage steuert den Ablauf nach Bedingungen:
IF <bedingung> THEN BEGIN <anweisungen> END [ ELSE BEGIN <anweisungen> END ] END IF
Diese Abfrage sieht so aus:
- Bei <bedingung> handelt es sich um eine einfache Prüfung, die nicht mit AND oder OR erweitert werden kann.
- Der ELSE-Zweig ist optional. Es ist auch möglich, dass nur der IF-THEN-Abschnitt ausgeführt werden muss.
- Durch ELSIF-Zweige sind auch Verschachtelungen möglich.
- Vor allem bei verschachtelten Abfragen sollten BEGIN...END immer benutzt und durch Einrückungen der Zusammenhang deutlich gemacht werden.
Die CASE-Prüfung entspricht der Fallunterscheidung aus dem Kapitel Nützlichen Erweiterungen:
-- Variante 1
CASE <ausdruck>
WHEN <ausdruck 1> THEN BEGIN <anweisungen 1> END
[ WHEN <ausdruck 1> THEN BEGIN <anweisungen 2> END ] /* usw. */
[ ELSE BEGIN <anweisungen n> END ]
END CASE
-- Variante 2
CASE
WHEN <bedingung 1> THEN BEGIN <anweisungen 1> END
[ WHEN <bedingung 2> THEN BEGIN <anweisungen 2> END ] /* usw. */
[ ELSE BEGIN <anweisungen n> END ]
END CASE
Bei dieser Prüfung gelten die gleichen Prüfungen wie bei der Fallunterscheidung mit folgenden Abweichungen:
- Im ELSE-Zweig darf es keine NULL-Anweisung geben.
- Die Verzweigung muss mit END CASE abgeschlossen werden.
Oracle kennt mehrere Arten von Schleifen.
Die LOOP-Schleife arbeitet ohne Bedingung am Anfang oder Ende und wird solange durchlaufen, bis – vor allem aufgrund einer „inneren“ Bedingung – die EXIT-Anweisung getroffen wird.
LOOP BEGIN <anweisungen> END END LOOP ;
DECLARE x NUMBER := 1;
BEGIN
LOOP
X := X + 1;
EXIT WHEN x > 10;
END LOOP;
END;
Die WHILE-Schleife prüft eine Bedingung und wird so lange durchlaufen, wie diese Bedingung wahr ist:
WHILE <bedingung> LOOP BEGIN <anweisungen> END END LOOP;
Die <bedingung> wird jeweils am Anfang eines Durchgangs geprüft. Wenn ihr Wert von Anfang an FALSE ist, wird die Schleife überhaupt nicht durchlaufen.
Die FOR-Schleife durchläuft eine vorgegebene Liste von Werten von Anfang bis Ende:
FOR <variable> IN <werteliste> LOOP BEGIN <anweisungen> END END LOOP;
Bei der FOR-Schleife wird der Wert der „Laufvariablen“ am Beginn jedes weiteren Durchlaufs automatisch erhöht; bei LOOP und WHILE müssen Sie sich selbst um die Änderung einer solchen Variablen kümmern.
Um alle Datensätze einer Ergebnismenge, also eines SELECT-Befehls zu durchlaufen, wird ein CURSOR benötigt. Dies wird in diesem Buch nicht behandelt.
Neben Anweisungen und den DML-Befehlen sind auch TCL-Befehle (Steuerung von Transaktionen) sowie die Sperre von Tabellen durch LOCK TABLE möglich.
Jede einzelne Anweisung innerhalb einer Routine und jeder SQL-Befehl müssen mit einem Semikolon abgeschlossen werden, auch das abschließende END. Das ist kein Problem, wenn nur ein einzelner CREATE PROCEDURE o. ä. ausgeführt werden soll. Andernfalls folgt in einer eigenen Zeile ein einzelner Schrägstrich:
BEGIN <anweisungen> END; /
Zusammenfassung
In diesem Kapitel wurden die wichtigsten Bestandteile besprochen, mit denen SQL-Befehle in eigenen Funktionen, in gespeicherten Prozeduren oder in Triggern verarbeitet werden:
- Deklaration von Parametern und Variablen
- Verwendung von Parametern und Variablen
- Verzweigungen mit IF u. a. sowie Schleifen
- Zulässigkeit von DML- und DDL-Befehlen
- Trennung zwischen einer Routine insgesamt und einer einzelnen Anweisung
Bei allen Einzelheiten müssen die Besonderheiten eines jeden DBMS beachtet werden.
Übungen
| Übung 1 | Erklärungen | Zur Lösung |
- Erläutern Sie den Zweck einer (benutzerdefinierten) Funktion.
- Erläutern Sie den Zweck einer (gespeicherten) Prozedur.
- Erläutern Sie den Zweck eines Triggers.
- Worin unterscheiden sich „Eigene Funktionen“ und Prozeduren?
- Worin unterscheiden sich Prozeduren und Trigger?
| Übung 2 | Der Kopf einer Routine | Zur Lösung |
Erläutern Sie die folgenden Bestandteile aus dem „Kopf“ (header) einer Routine, d. h. einer Funktion oder einer Prozedur. Erwähnen Sie auch, was zu diesen Bestandteilen gehört.
- CREATE OR ALTER bzw. CREATE OR REPLACE
- <Parameterliste>
- Eingabe-Parameter
- Ausgabe-Parameter
- RETURNS bzw. RETURN
| Übung 3 | Der Rumpf einer Routine | Zur Lösung |
Erläutern Sie die folgenden Bestandteile aus dem „Rumpf“ (body) einer Routine, d. h. einer Funktion oder einer Prozedur. Erwähnen Sie auch, was zu diesen Bestandteilen gehört; die Antworten können sehr knapp ausfallen, weil sie sich in Abhängigkeit vom DBMS sehr unterscheiden müssten.
- BEGIN und END am Anfang und Ende des Rumpfes
- BEGIN und END innerhalb des Rumpfes
- Variablen
- Verzweigungen
- Schleifen
- Zuweisung von Werten
| Lösung zu Übung 1 | Erklärungen | Zur Übung |
- Eine benutzerdefinierte Funktion ist eine Ergänzung zu den internen Funktionen des DBMS und liefert immer einen bestimmten Wert zurück.
- Eine gespeicherte Prozedur (StoredProcedure, SP) ist ein Arbeitsablauf, der fest innerhalb der Datenbank gespeichert ist und Aufgaben ausführt, die innerhalb der Datenbank erledigt werden sollen – im wesentlichen ohne „Kommunikation“ mit dem Benutzer bzw. dem auslösenden Programm.
- Ein Trigger ist ein Arbeitsablauf, der fest innerhalb der Datenbank gespeichert ist und Aufgaben ausführt, die automatisch beim Speichern innerhalb der Datenbank erledigt werden sollen.
- Eine Funktion liefert genau einen Wert zurück, der durch RETURNS festgelegt wird. Prozeduren gibt es mit und ohne Rückgabewert.
- Ein Trigger wird automatisch bei einem Speichern-Befehl ausgeführt, und es gibt keinen Rückgabewert. Eine Prozedur wird bewusst aufgerufen, und sie kann Rückgabewerte liefern.
| Lösung zu Übung 2 | Der Kopf einer Routine | Zur Übung |
- Dies definiert eine Routine. Dazu gehören einer der Begriffe FUNCTION und PROCEDURE sowie der Name der Routine.
- Die Parameterliste enthält diejenigen Parameter, die beim Aufruf an die Routine übergeben werden (Eingabe-Parameter), sowie diejenigen, die nach Erledigung an den Aufrufer zurückgegeben werden (Ausgabe-Parameter). Wie Eingabe- und Ausgabe-Parameter gekennzeichnet bzw. unterschieden werden, hängt vom DBMS ab.
- Alle Eingabe-Parameter sind (durch Komma getrennt) aufzuführen; die Reihenfolge muss beim Aufruf beachtet werden. Zu jedem Parameter gehören Name und Datentyp; unter Umständen ist ein Standardwert möglich.
- Alle Ausgabe-Parameter sind (durch Komma getrennt) aufzuführen; die Reihenfolge muss bei der Auswertung im aufrufenden Programm o. ä. beachtet werden. Zu jedem Parameter gehören Name und Datentyp; unter Umständen ist ein Standardwert möglich.
- RETURNS bzw. RETURN gibt bei Funktionen den Ausgabe-Parameter, d. h. das Ergebnis der Funktion an. Nur Firebird benutzt dies, um auch die Ausgabe-Parameter einer Prozedur anzugeben.
| Lösung zu Übung 3 | Der Rumpf einer Routine | Zur Übung |
- Dies beschreibt Anfang und Ende des gesamten Rumpfes. Wenn der Rumpf nur einen einzelnen Befehl enthält, kann es entfallen.
- Dies begrenzt einzelne Abschnitte innerhalb des Rumpfes, vor allem bei Schleifen und IF-Verzweigungen. Wenn der Abschnitt nur einen einzelnen Befehl enthält, kann BEGIN/END entfallen; die Verwendung wird aber stets empfohlen.
- Dies definiert Werte, die (nur) innerhalb der Routine benutzt werden. Sie müssen mit Namen und Datentyp ausdrücklich deklariert werden.
- Mit IF-ELSE o. ä. können – abhängig von Bedingungen – unterschiedliche Befehle ausgeführt werden.
- Mit WHILE, FOR o. a. können einzelne Befehle oder Abschnitte von Befehlen wiederholt ausgeführt werden. Wie oft bzw. wie lange die Schleife durchlaufen wird, hängt von der Art der Schleife (unterschiedlich nach DBMS) und Bedingungen ab.
- Den Variablen und Parametern können Werte zugewiesen werden, und zwar durch das Gleichheitszeichen mit konkreten Angaben oder als Ergebnis von Funktionen, Prozeduren oder SELECT-Befehlen.
Siehe auch
Verschiedene Einzelheiten stehen in den folgenden Kapiteln:
Zur SQL-Programmierung mit Oracle gibt es das (unvollständige) Wikibook PL/SQL.
| Eigene Funktionen |
Auch wenn ein DBMS viele Funktionen zur Verfügung stellt, kommt man in der Praxis immer wieder einmal zu weiteren Wünschen und Anforderungen. Dafür kann ein Benutzer eigene Funktionen definieren und dem DBMS bekannt geben oder in einer Datenbank speichern. Einmal definiert, erspart dies künftig, die gleiche Routine immer neu zu erstellen; stattdessen wird die Funktion aufgerufen und liefert den Rückgabewert.
Firebird hat solche Funktionen erst für Version 3 angekündigt. Zurzeit kann eine Funktion nur als UDF (user-defined function) aus einer externen DLL, die mit einer Programmiersprache erstellt wurde, eingebunden werden.
Bitte haben Sie Nachsicht: Wegen der vielen Varianten bei den DBMS wurde ein Teil der Hinweise und Beispiele auch in diesem Kapitel nur nach der Dokumentation verfasst und nicht in der Praxis umgesetzt.
Funktion definieren
Benutzerdefinierte Funktionen geben (ebenso wie die eigenen Funktionen des DBMS) genau einen Wert zurück, sind also Skalarfunktionen.
Funktion erstellen
Mit CREATE FUNCTION in der folgenden Syntax (vereinfachte Version des SQL-Standards) wird eine Funktion definiert:
CREATE FUNCTION <routine-name>
( [ <parameterliste> ] )
RETURNS <datentyp>
[ <characteristics> ]
<routine body>
Der <routine-name> der Funktion muss innerhalb eines gewissen Bereichs („Schema“ genannt) eindeutig sein und darf auch nicht als Name einer Prozedur verwendet werden. Teilweise wird verlangt, dass der Name der Datenbank ausdrücklich angegeben wird. Es empfiehlt sich, keinen Namen einer eingebauten Funktion zu verwenden.
Die Klammern sind erforderlich und kennzeichnen eine Routine. Eingabeparameter können vorhanden sein, müssen es aber nicht. Mehrere Parameter werden durch Kommata getrennt, der einzelne <parameter> wird wie folgt definiert:
[ IN ] <parameter-name> <datentyp>
Der <parameter-name> muss innerhalb der Funktion eindeutig sein. Der Zusatz IN kann entfallen, weil es bei Funktionen nur Eingabe-Parameter (keine Ausgabe-Parameter) gibt.
Der RETURNS-Parameter ist wesentlich und kennzeichnet eine Funktion.
Der <datentyp> sowohl für den RETURNS-Parameter als auch für die Eingabe-Parameter muss einer der SQL-Datentypen des DBMS sein.
Für <characteristics> gibt es diverse Festlegungen, wie die Funktion arbeiten soll, z. B. durch LANGUAGE mit der benutzten Programmiersprache.
<routine body> kennzeichnet den eigentlichen Arbeitsablauf, also die Schritte, die innerhalb der Routine ausgeführt werden sollen. Bei diesen Befehlen handelt es sich um „normale“ SQL-Befehle, die mit Bestandteilen der SQL-Programmiersprache verbunden werden. Zum Semikolon, das den Abschluss des CREATE-Befehls darstellen sollte, aber innerhalb des Inhalts für jede einzelne Anweisung benutzt wird, beachten Sie bitte auch die Hinweise zur SQL-Programmierung unter Anweisungen begrenzen.
Bei einer Funktion muss – beispielsweise durch eine RETURN-Anweisung – der gesuchte Rückgabewert ausdrücklich festgelegt werden. In der Regel werden die Befehle durch BEGIN...END zusammengefasst; bei einem einzelnen Befehl ist dies nicht erforderlich.
Funktion ausführen
Eine benutzerdefinierte Funktion kann wie jede interne Funktion des DBMS benutzt werden. Sie kann an jeder Stelle benutzt werden, an der ein einzelner Wert erwartet wird, wie in den Beispielen zu sehen ist.
Funktion ändern oder löschen
Mit ALTER FUNCTION wird die Definition einer Funktion geändert. Dafür gilt die gleiche Syntax:
ALTER FUNCTION <routine-name>
( [ <parameterliste> ] )
RETURNS <datentyp>
[ <characteristics> ]
<routine body>
Mit DROP FUNCTION wird die Definition einer Funktion gelöscht.
DROP FUNCTION <routine-name>;
Beispiele
Einfache Bestimmung eines Wertes
Das folgende Beispiel erstellt den Rückgabewert direkt aus der Eingabe.
Zur Begrüßung wird ein Name mit einem Standardtext, der von der Tageszeit abhängt, verbunden.
CREATE FUNCTION Hello (s CHAR(20))
RETURNS CHAR(50)
RETURN CASE
WHEN CURRENT_TIME < '12:00' THEN concat('Guten Morgen, ', s,'!')
WHEN CURRENT_TIME < '18:00' THEN concat('Guten Tag, ', s, '!')
ELSE concat('Guten Abend, ', s, '!')
END;
Bei diesem Beispiel kann auf BEGIN...END verzichtet werden, weil der „Rumpf“ der Funktion nur eine einzige Anweisung enthält, nämlich RETURN unter Benutzung des CASE-Ausdrucks. Zur Verkettung der Zeichenketten wird CONCAT verwendet.
Eine solche Funktion wird wie jede eingebaute Funktion benutzt.
SELECT Hello('Juetho') [from fiktiv];
Ausgabe'Guten Abend, Juetho!'
Text in Anführungszeichen einschließen
Im folgenden Beispiel werden mehr Schritte bis zum RETURN-{}Wert benötigt. Der Nutzen liegt im Export von Daten aus einem System zu einem anderen.
Ein gegebener Text soll in Anführungszeichen eingeschlossen werden; Gänsefüßchen innerhalb des Textes müssen verdoppelt werden.
CREATE FUNCTION Quoting
( instring VARCHAR(80) )
RETURNS VARCHAR(100)
BEGIN
IF (instr(instring, '"')!=0)
THEN BEGIN
SET instring = REPLACE( instring, '"', '""' );
END;
END IF;
RETURN CONCAT( '"', instring, '"');
END
Hinweis: Auf das BEGIN und END innerhalb des IF-Konstrukts kann verzichtet werden, da innerhalb des IFs nur ein Statement ausgeführt wird.
Diese Funktion kann direkt aufgerufen werden:
select Quoting('Schulze'), Quoting('Restaurant "Abendrot"') [from fiktiv];
Ausgabe"Schulze" "Restaurant ""Abendrot"""
Anzahl der Mitarbeiter einer Abteilung
Bei der folgenden Funktion werden zunächst weitere Informationen benötigt.
Suche zu einer Abteilung gemäß Kuerzel die Anzahl der Mitarbeiter.
CREATE OR REPLACE FUNCTION AnzahlMitarbeiter
( abt CHAR(4) )
RETURN ( INTEGER )
AS
anzahl INTEGER;
BEGIN
select COUNT(*) into anzahl
from Mitarbeiter mi
join Abteilung ab on ab.ID = mi.Abteilung_ID
where ab.Kuerzel = abt;
RETURN anzahl;
END
Damit erhalten wir die Anzahl der Mitarbeiter einer bestimmten Abteilung:
select AnzahlMitarbeiter('Vert') [from fiktiv] ;
AusgabeAnzahlMitarbeiter : 4
Zusammenfassung
In diesem Kapitel lernten wir die Grundregeln für die Erstellung eigener Funktionen kennen:
- Benutzerdefinierte Funktionen sind immer Skalarfunktionen, die genau einen Wert zurückgeben.
- Der Datentyp des Rückgabewerts ist in der RETURNS-Klausel anzugeben, der Wert selbst durch eine RETURN-Anweisung.
- Komplexe Maßnahmen müssen in BEGIN...END eingeschlossen werden; eine Funktion kann aber auch nur aus der RETURN-Anweisung bestehen.
Übungen
Wenn bei den folgenden Übungen der Begriff „Funktion“ ohne nähere Erklärung verwendet wird, ist immer eine „Eigene Funktion“, d. h. eine benutzerdefinierte Funktion gemeint.
Unter „Skizzieren“ (Übung 3, 5) ist gemeint: Eingabe- und Ausgabeparameter sowie Variablen mit sinnvollen Namen und Datentypen benennen, Arbeitsablauf möglichst genau mit Pseudo-Code oder normaler Sprache beschreiben.
Tipp: Die Parameter ergeben sich in der Regel aus der Aufgabenstellung. Aus der Überlegung zum Arbeitsablauf folgen die Variablen.
| Übung 1 | Definition einer Funktion | Zur Lösung |
Welche der folgenden Aussagen sind richtig, welche sind falsch?
- Eine Funktion dient zur Bestimmung genau eines Wertes.
- Eine Funktion kann höchstens einen Eingabe-Parameter enthalten.
- Eine Funktion kann mehrere Ausgabe-Parameter enthalten.
- Eine Funktion kann einen oder mehrere SQL-Befehle verwenden.
- Der Rückgabewert wird mit einer RETURN-Anweisung angegeben.
- Der Datentyp des Rückgabewerts ergibt sich automatisch aus der RETURN-Anweisung.
- Die Definition einer Funktion kann nicht geändert werden; sie kann nur gelöscht und mit anderem Inhalt neu aufgenommen werden.
| Übung 2 | Definition einer Funktion prüfen | Zur Lösung |
Nennen Sie in der folgenden Definition Punkte, die unabhängig vom SQL-Dialekt falsch sind. (Je nach DBMS können noch andere Punkte falsch sein, danach wird aber nicht gefragt.) Die Funktion soll folgende Aufgabe erledigen:
- Es wird eine Telefonnummer in beliebiger Formatierung als Eingabe-Parameter übergeben, z. B. als '(0049 / 030) 99 68-32 53'.
- Das Ergebnis soll diese Nummer ganz ohne Trennzeichen zurückgeben.
- Sofern die Ländervorwahl enthalten ist, soll das führende '00' durch '+' ersetzt werden.
CREATE Telefon_Standard AS Function
( IN Eingabe VARCHAR(20),
OUT Ausgabe VARCHAR(20) )
AS
DECLARE INTEGER x1, i1;
DECLARE CHAR c1;
BEGIN
-- Rückgabewert vorbelegen
Ausgabe = '';
-- Länge der Schleife bestimmen
i1 = CHAR_LENGTH(Eingabe);
-- die Schleife für jedes vorhandene Zeichen verarbeiten
WHILE (i1 < x1)
DO
-- das nächste Zeichen auslesen
x1 = x1 + 1
c1 = SUBSTRING(Eingabe from x1 for 1);
-- dieses Zeichen prüfen: ist es eine Ziffer
if (c1 >= '0' and (c1 <= '9')
THEN
-- ja: an den bisherigen Rückgabewert anhängen
Ausgabe = Ausgabe || c1;
END
END
-- Zusatzprüfung: '00' durch '+' ersetzen
IF (Ausgabe STARTS WITH '00')
THEN
Ausgabe = '+' || SUBSTRING(Ausgabe from 3 for 20)
END
END
| Übung 3 | Funktion DateToString erstellen | Zur Lösung |
Skizzieren Sie eine Funktion DateToString: Aus einem Date- oder DateTime-Wert als Eingabe-Parameter soll eine Zeichenkette mit genau 8 Zeichen der Form 'JJJJMMTT' gemacht werden. Benutzen Sie dafür nur die EXTRACT- und LPAD-Funktionen sowie die „Addition“ von Zeichenketten; Sie können davon ausgehen, dass eine Zahl bei LPAD korrekt als Zeichenkette verstanden wird.
| Übung 4 | Funktion DateToString erstellen | Zur Lösung |
Erstellen Sie die Funktion aus der vorigen Übung.
| Übung 5 | Funktion String_Insert erstellen | Zur Lösung |
Skizzieren Sie eine Funktion String_Insert: In eine Zeichenkette soll an einer bestimmten Position eine zweite Zeichenkette eingefügt werden; alle Zeichenketten sollen max. 80 Zeichen lang sein. Benutzen Sie dafür nur SUBSTRING und CAST sowie die „Addition“ von Zeichenketten; stellen Sie außerdem sicher, dass das Ergebnis die mögliche Maximallänge nicht überschreitet und schneiden Sie ggf. ab.
| Übung 6 | Funktion String_Insert erstellen | Zur Lösung |
Erstellen Sie die Funktion aus der vorigen Übung.
| Lösung zu Übung 1 | Definition einer Funktion | Zur Übung |
Die Aussagen 1, 4, 5 sind richtig. Die Aussagen 2, 3, 6, 7 sind falsch.
| Lösung zu Übung 2 | Definition einer Funktion prüfen | Zur Übung |
- Zeile 1: Die Definition erfolgt durch CREATE FUNCTION <Name>.
- Zeile 2: Bei einer Funktion gehören in die Klammern nur die Eingabe-Parameter.
- Zeile 3: Der Ausgabe-Parameter muss mit RETURNS festgelegt werden.
- Zeile 4: Bei jeder Variablen ist zuerst der Name, dann der Typ anzugeben.
- Zeile 12: Der Startwert für die Schleifenvariable x1 = 0 fehlt.
- Zeile 14: Die Schleife verarbeitet mehrere Befehle, muss also hinter DO auch ein BEGIN erhalten.
- Zeile 16 und 29: Jeder einzelne Befehl muss mit einem Semikolon abgeschlossen werden.
- Zeile 19: Die Klammern sind falsch gesetzt; am einfachsten wird die zweite öffnende Klammer entfernt.
- Zeile 20/23: Entweder es wird THEN BEGIN...END verwendet, oder das END wird gestrichen.
- Zeile 28/30 enthält den gleichen Fehler.
- Zeile 31: Es fehlt die RETURN-Anweisung, mit der der erzeugte Ausgabe-String zurückgegeben wird.
| Lösung zu Übung 3 | Funktion DateToString erstellen | Zur Übung |
- Eingabe-Parameter: ein DateTime-Wert Eingabe
- Ausgabe-Parameter: ein CHAR(8) Ausgabe
- drei INTEGER-Variable: jjjj, mm, tt
- mit 3x EXTRACT werden die Bestandteile jjjj, mm, tt herausgeholt
- diese werden mit LPAD auf CHAR(2) für Monat und Tag gesetzt
- schließlich werden diese Werte per „Addition“ verknüpft und
- als Ergebnis zurückgegeben
| Lösung zu Übung 4 | Funktion DateToString erstellen | Zur Übung |
CREATE FUNCTION DateToString
( Eingabe DATE )
RETURNS ( Ausgabe CHAR(8) )
AS
DECLARE VARIABLE jjjj int;
DECLARE VARIABLE mm int;
DECLARE VARIABLE tt int;
BEGIN
jjjj = EXTRACT(YEAR from Eingabe);
mm = EXTRACT(MONTH from Eingabe);
tt = EXTRACT(DAY from Eingabe);
Ausgabe = jjjj || LPAD(mm, 2, '0') || LPAD(tt, 2, '0');
RETURN Ausgabe;
END
| Lösung zu Übung 5 | Funktion String_Insert erstellen | Zur Übung |
- Eingabe-Parameter: Original-String Eingabe, pos als Position (der Name Position ist als interne Funktion verboten), der Zusatz-String part
- Ausgabe-Parameter: die neue Zeichenkette Ausgabe
- Variable: temp mit Platz für 160 Zeichen
- es wird einfach eine neue Zeichenkette temp zusammengesetzt aus drei Teilen:
- der Anfang von Eingabe mit pos Zeichen
- dann der Zusatz-String part
- dann der Rest von Eingabe ab Position pos + 1
- falls die Zeichenkette temp zu lang ist, werden mit SUBSTRING nur noch die ersten 80 Zeichen verwendet
- das Ergebnis muss wegen der seltsamen Definition von SUBSTRING mit CAST auf VARCHAR(80) gebracht werden und
- kann dann als Rückgabewert verwendet werden
| Lösung zu Übung 6 | Funktion String_Insert erstellen | Zur Übung |
CREATE FUNCTION String_Insert
( Eingabe VARCHAR(80), pos INTEGER, part VARCHAR(80) )
RETURNS ( Ausgabe VARCHAR(80) )
AS
-- genügend Zwischenspeicher bereitstellen
DECLARE VARIABLE temp VARCHAR(160);
BEGIN
-- Teil 1, dazu Zwischenteil, dann Teil 2
temp = SUBSTRING( Eingabe from 1 for pos )
|| part
|| SUBSTRING( Eingabe from pos + 1 );
-- auf jeden Fall auf die Maximallänge von 80 Zeichen bringen
if (CHAR_LENGTH(temp) > 80) THEN
Ausgabe = cast( substring(temp from 1 for 80) as varchar(80));
ELSE
Ausgabe = cast( temp as varchar(80));
RETURN Ausgabe;
END
Siehe auch
Verschiedene Einzelheiten werden in den folgenden Kapiteln behandelt:
| Prozeduren |
Eine Prozedur – gespeicherte Prozedur, engl. StoredProcedure (SP) – ist vorgesehen für Arbeitsabläufe, die „immer wiederkehrende“ Arbeiten direkt innerhalb der Datenbank ausführen sollen.
Bitte haben Sie Nachsicht: Wegen der vielen Varianten bei den DBMS beschränkt sich dieses Kapitel bei den Hinweisen und Beispielen auf Firebird. Zusammen mit den speziellen Hinweisen zur SQL-Programmierung sollten sie problemlos an andere DBMS-Regeln angepasst werden können.
Ein Überblick
Zur Verwendung von Prozeduren gibt es zwei gegensätzliche Standpunkte, die mit den Begriffen Fat Server und Fat Client zusammengefasst werden können:
- Beim Fat Server wird so viel Funktionalität wie möglich in die (zentral gespeicherte) Datenbank gelegt.
- Beim Fat Client ist die Datenbank nur für die Speicherung der Daten vorgesehen; sämtliche Verarbeitung erfolgt auf dem Arbeitsplatzrechner.
Der große Vorteil eines Fat Server liegt darin, dass Daten direkt in der Datenbank verarbeitet werden können, sofern keine Ansicht der Daten auf dem Arbeitsplatz benötigt wird. Es ist natürlich überflüssig, die Daten zuerst von der Datenbank zum Arbeitsplatz zu kopieren, dann automatisch zu verarbeiten und schließlich das Arbeitsergebnis (ohne manuelle Überprüfung) auf die Datenbank zurückzukopieren. Einfacher ist es, alles in der Datenbank ausführen zu lassen.
Der große Vorteil eines Fat Client liegt darin, dass das Client-Programm meistens schneller und einfacher geändert werden kann als die Arbeitsabläufe innerhalb der Datenbank.
In der Praxis ist eine Mischung beider Verfahren am sinnvollsten. Zur Steuerung der Arbeitsabläufe in der Datenbank werden die gespeicherten Prozeduren verwendet; Beispiele dafür werden in diesem Abschnitt behandelt.
Prozedur definieren
Prozedur erstellen
Die Syntax für die Definition einer Prozedur sieht so aus:
CREATE OR ALTER PROCEDURE <routine-name>
( [ <parameterliste> ] )
AS
BEGIN
[ <variablenliste> ]
<routine body>
END
Notwendig sind folgende Angaben:
- neben dem Befehlsnamen der Name der Prozedur
- das Schlüsselwort AS als Zeichen für den Inhalt
- die Schlüsselwörter BEGIN und END als Begrenzer für den Inhalt
Hinzu kommen die folgenden Angaben:
- eine Liste von Parametern, sofern Werte übergeben oder abgefragt werden
- Bei den meisten DBMS werden Eingabe- und Ausgabe-Parameter durch Schlüsselwörter wie IN und OUT unterschieden, sie stehen dabei innerhalb derselben Liste.
- Bei Firebird enthält die Parameterliste nur die Eingabe-Parameter; die Ausgabe-Parameter stehen hinter RETURNS in einer eigenen Liste mit Klammern.
- eine Liste von Variablen, die innerhalb der Prozedur verwendet werden; diese Liste steht je nach DBMS zwischen AS und BEGIN oder innerhalb des Rumpfes (also zwischen BEGIN und END)
- die Befehle, die innerhalb der Prozedur auszuführen sind
Bei den Befehlen innerhalb der Prozedur handelt es sich um „normale“ SQL-Befehle sowie um Bestandteile der SQL-Programmiersprache. Zum Semikolon, das den Abschluss des CREATE-Befehls darstellen sollte, aber innerhalb des Inhalts bereits für jede einzelne Anweisung benutzt wird, beachten Sie bitte auch die Hinweise zur SQL-Programmierung unter Anweisungen begrenzen.
Prozedur ausführen
Es gibt mehrere Verfahren, wie eine Prozedur vom Nutzer, aus einem Anwendungsprogramm oder aus einer anderen Prozedur oder einem Trigger heraus benutzt werden kann.
Der direkte Weg geht bei Firebird über Execute Procedure und führt eine Prozedur aus:
EXECUTE PROCEDURE <routine-name> [ <eingabe-parameter> ]
[ RETURNING_VALUES <ausgabe-parameter> ] ;
Dem Namen der Prozedur folgen (soweit erforderlich) die Eingabe-Parameter; mehrere werden mit Komma getrennt. Sofern die Prozedur Werte zurückliefert, werden sie in eine oder mehrere Variablen eingetragen, die dem Begriff RETURNING_VALUES folgen.
Bei den anderen DBMS gehören zu EXECUTE (MySQL: CALL) nicht nur die Eingabe-, sondern auch die Ausgabe-Parameter. Beispiele stehen im Kapitel zur SQL-Programmierung bei den Erläuterungen des jeweiligen DBMS.
Eine Prozedur kann bei manchen DBMS auch mit Select Procedure wie eine Abfrage verwendet werden. Dies ist vor allem dann erforderlich, wenn eine Reihe von Werten oder Datensätzen zurückgeliefert werden.
SELECT * FROM <routine-name> [ <eingabe-parameter> ] ;
Die Ausgabe-Parameter stehen dann in der Liste der Spalten, also in der Ergebnismenge des SELECT-Befehls.
Man könnte auch an Execute Block denken, aber dieser Hinweis passt hier überhaupt nicht. Ein Block ist eine Menge von Anweisungen, die einmalig benutzt werden, aber eben nicht in der Datenbank gespeichert werden. Ein solcher Block ersetzt eine gespeicherte Prozedur, wenn das Speichern überflüssig ist, und wird bei der SQL-Programmierung unter Routinen ohne feste Speicherung beschrieben.
Prozedur ändern
Eine Änderung einer Prozedur ist nur möglich durch eine vollständige Neudefinition mit allen Bestandteilen. Deshalb wird es heutzutage fast immer durch CREATE OR ALTER zusammengefasst. Wenn dies nicht möglich ist, dann muss ein einzelner ALTER-Befehl benutzt werden, der alle Bestandteile der obigen Erläuterungen enthält.
Prozedur löschen
Das geht wie üblich mit dem DROP-Befehl:
DROP PROCEDURE <routine-name>;
Beispiele
Um die Beispiele für ein anderes DBMS zu übertragen, sind vor allem folgende Punkte zu ändern:
- Die Ausgabe-Parameter folgen nicht nach RETURNS, sondern gehören in die Parameterliste.
- Die Deklaration der Variablen ist anzupassen, ebenso die Kennzeichnung mit Doppelpunkt.
- Schleifen und ähnliche Maßnahmen sind umzuschreiben.
- Dies gilt auch dafür, wie Werte – z. B. eines SELECT – übernommen werden.
Ersatz für eine View mit Parametern
Im Kapitel zu VIEWs wollten wir eine Ansicht mit variabler Selektion erstellen, aber variable Bedingungen waren nicht möglich. Mit einer Prozedur geht es.
Mache aus der View Mitarbeiter_in_Abteilung eine Prozedur Mitarbeiter_aus_Abteilung, die die Abteilungsnummer als Parameter erhält.
CREATE OR ALTER PROCEDURE Mitarbeiter_aus_Abteilung
( Abt INTEGER)
RETURNS ( Personalnummer VARCHAR(10),
Name VARCHAR(30),
Vorname VARCHAR(30),
Geburtsdatum DATE )
AS
BEGIN
FOR SELECT Personalnummer, Name, Vorname, Geburtsdatum
FROM Mitarbeiter
WHERE Abteilung_ID = :Abt
ORDER BY Ist_Leiter, Name, Vorname
INTO :Personalnummer, :Name, :Vorname, :Geburtsdatum
DO SUSPEND;
END
Als Prozedur enthält die Abfrage folgende Bestandteile:
- Als Eingabe-Parameter wird die gewünschte Abteilungsnummer erwartet und in der Variablen Abt gespeichert.
- Als Ausgabe-Parameter werden die gewünschten Spalten der Tabelle Mitarbeiter aufgeführt.
- Weitere Variable werden nicht benötigt.
Zwischen BEGIN und END steht der eigentliche Arbeitsablauf mit Anweisungen gemäß SQL-Programmierung.
- Es handelt sich dabei vor allem um einen SELECT-Befehl mit der gewünschten WHERE-Klausel, die bei der VIEW nicht möglich war.
- Diese Abfrage wird in eine FOR-DO-Schleife eingebunden.
- Dabei werden die Spalten eines jeden ausgewählten Datensatzes mit INTO in die Ausgabe-Variablen übertragen und per SUSPEND zurückgegeben.
Damit haben wir die gewünschte variable Abfrage. Bei der Verwendung ist es für den Anwender gleichgültig, ob er sie wie eine View oder wie eine Prozedur aufzurufen hat:
Hole alle Mitarbeiter einer bestimmten Abteilung.
SELECT * FROM Mitarbeiter_in_Abteilung
WHERE Abt = 5;
SELECT * FROM Mitarbeiter_aus_Abteilung (5);
INSERT in mehrere Tabellen
Wenn in der Beispieldatenbank ein neuer Versicherungsvertrag eingegeben werden soll, benötigt man in der Regel drei INSERT-Befehle:
- für einen neuen Eintrag in der Tabelle Fahrzeug
- für einen neuen Eintrag in der Tabelle Versicherungsnehmer
- für einen neuen Eintrag in der Tabelle Versicherungsvertrag mit Verweisen auf die beiden anderen Einträge
Bei den ersten beiden Einträgen ist durch SELECT die neue ID abzufragen und beim dritten INSERT zu verwenden. Warum sollte man sich die Arbeit in dieser Weise erschweren? Soll doch die Datenbank einen einzigen INSERT-Befehl entgegennehmen und die Parameter selbständig auf die beteiligten Tabellen verteilen.
Speichere einen neuen Versicherungsvertrag mit allen Einzelheiten.
create or alter PROCEDURE Insert_Versicherungsvertrag
( /* Bestandteile des Vertrags */
Vertragsnummer VARCHAR(20),
Abschlussdatum DATE,
Art CHAR(2),
Praemiensatz INTEGER,
Basispraemie DECIMAL(9,0),
Mitarbeiter_ID INTEGER,
/* Angaben zum Versicherungsnehmer */
Name VARCHAR(30),
Vorname VARCHAR(30),
Geschlecht CHAR(1),
Geburtsdatum DATE,
Fuehrerschein DATE,
Ort VARCHAR(30),
PLZ CHAR(5),
Strasse VARCHAR(30),
Hausnummer VARCHAR(10),
Eigener_Kunde CHAR(1),
Versicherungsgesellschaft_ID INTEGER,
/* Angaben zum Fahrzeug */
Kennzeichen VARCHAR(10),
Farbe VARCHAR(30),
Fahrzeugtyp_ID INTEGER
)
RETURNS( NewID INTEGER )
AS
DECLARE VARIABLE NewFahrzeugID integer;
DECLARE VARIABLE NewVersnehmerID integer;
BEGIN
NewFahrzeugID = NEXT VALUE FOR Fahrzeug_ID;
INSERT into Fahrzeug
values ( :NewFahrzeugID, :Kennzeichen, :Farbe, :Fahrzeugtyp_ID );
NewVersnehmerID = NEXT VALUE FOR Versicherungsnehmer_ID;
insert into Versicherungsnehmer
values ( :NewVersnehmerID, :Name, :Vorname, :Geburtsdatum, :Fuehrerschein,
:Ort, :PLZ, :Strasse, :Hausnummer,
:Eigener_Kunde, :Versicherungsgesellschaft_ID, :Geschlecht );
NewID = NEXT VALUE FOR Versicherungsvertrag_ID;
insert into Versicherungsvertrag
values ( :NewID, :Vertragsnummer, :Abschlussdatum, :Art, :Mitarbeiter_ID,
:NewFahrzeugID, :NewVersnehmerID, :Praemiensatz, :Abschlussdatum, :Basispraemie );
SUSPEND;
END
Als Eingabe-Parameter werden alle Werte benötigt, die der Sachbearbeiter für die einzelnen Tabellen eingeben muss (siehe die durch Kommentare getrennten Abschnitte).
Als Ausgabe-Parameter wollen wir die ID für den neuen Vertrag erhalten.
Als Variable werden die Verweise auf die zugeordneten Tabellen Fahrzeug und Versicherungsnehmer vorgesehen.
Der Arbeitsablauf ist ganz einfach strukturiert:
- Hole die nächste ID für die Tabelle Fahrzeug und speichere den nächsten Eintrag in dieser Tabelle.
- Hole die nächste ID für die Tabelle Versicherungsnehmer und speichere den nächsten Eintrag in dieser Tabelle.
- Hole die nächste ID für die Tabelle Versicherungsvertrag und speichere den nächsten Eintrag in dieser Tabelle. Benutze dafür die beiden anderen IDs.
Bei einem DBMS mit automatischem Zähler wird statt der „nächsten ID“ die gerade neu vergebene ID abgefragt. Möglichkeiten dafür enthält der Abschnitt Die letzte ID abfragen des Kapitels „Tipps und Tricks“.
Damit wird ein neuer Vertrag mit einem einzigen Befehl erstellt:
Speichere einen neuen Vertrag mit allen Angaben.
EXECUTE PROCEDURE Insert_Versicherungsvertrag
( 'HS-38', '03.11.2009', 'VK', 125, 870, 11, 'Graefing', 'Charlotte',
'W', '09.11.1989', '26.02.2008', 'Hattingen', '45529',
'Laakerweg', '17 b', 'J', NULL, 'BO-MC 413', 'gelb', 8 );
Ausgabe------ Procedure executing results: ------
NEWID = 29 /* die ID des neuen Vertrags */
Automatisches UPDATE gemäß Bedingungen
Bei einer Versicherungsgesellschaft muss regelmäßig der Prämiensatz eines Vertrags neu berechnet werden: Bei verschuldeten Schadensfällen wird er (abhängig von der Höhe des Schadens) erhöht, bei Schadensfreiheit verringert. Dies ist eine Aufgabe, die die Datenbank selbständig erledigen kann und soll. Das ist ein komplexer Arbeitsablauf mit mehreren Prüfungen; vor allem die unterschiedliche Zuordnung neuer Werte wird hier stark vereinfacht.
Berechne für alle (eigenen) Verträge, ob eine neue Prämienrechnung ansteht. Dabei ist zu prüfen, ob wegen neuer Schadensfälle der Prämiensatz zu erhöhen oder wegen Schadensfreiheit zu verringern ist.
Die einzelnen Schritte werden anhand des folgenden Codes erläutert.
create or alter PROCEDURE Update_Praemiensatz
( Aktualisierung DATE default CURRENT_DATE )
RETURNS ( Erledigt INTEGER )
AS
DECLARE VARIABLE current_id INTEGER;
DECLARE VARIABLE current_fz INTEGER;
DECLARE VARIABLE current_aenderung DATE;
DECLARE VARIABLE vergleich_aenderung DATE;
DECLARE VARIABLE current_satz INTEGER;
DECLARE VARIABLE current_value DECIMAL(16,2);
BEGIN
Erledigt = 0;
SUSPEND;
/* Vorarbeit: Anfänger werden auf Praemiensatz 200 gesetzt; das kann aber
nur für noch nicht behandelte Verträge gelten und muss deshalb noch
vor der nächsten IS NULL-Prüfung erledigt werden. */
UPDATE Versicherungsvertrag vv
SET Praemiensatz = 200
WHERE Praemienaenderung is null
and Versicherungsnehmer_ID
/* bestimme die Liste der Anfänger, aber nur für eigene Kunden */
in ( SELECT ID
from Versicherungsnehmer vn
/* wenn der Führerschein beim Vertragsabschluss weniger als 2 Jahre alt ist */
where vn.Eigener_kunde = 'J'
and ( ( DATEADD( YEAR, 2, vn.Fuehrerschein ) > vv.Abschlussdatum )
/* wenn der VersNehmer beim Führerschein-Erwerb noch nicht 21 Jahre alt ist */
or ( DATEADD( YEAR, 21, vn.Geburtsdatum ) > vn.Fuehrerschein ) ) );
/* Schritt 1: zu bearbeiten sind alle Verträge für eigene Kunden, deren letzte
Prämienänderung „zu lange“ zurückliegt */
for SELECT vv.ID, Fahrzeug_ID,
CASE WHEN Praemienaenderung is null THEN Abschlussdatum
ELSE Praemienaenderung
END,
Praemiensatz
from Versicherungsvertrag vv
join Versicherungsnehmer vn on vn.Id = vv.Versicherungsnehmer_id
where vn.Eigener_Kunde = 'J'
and ( Praemienaenderung is null or Praemienaenderung <= :Aktualisierung)
into :current_id, :current_fz, :current_aenderung, :current_satz
DO BEGIN
/* Schritt 2: wegen der Übersicht das mögliche Schlussdatum vorher bestimmen */
vergleich_aenderung = DATEADD( YEAR, 1, current_aenderung );
vergleich_aenderung = DATEADD( DAY, -1, vergleich_aenderung );
/* Schritt 3: weitere Bearbeitung, sofern die Aktualisierung über das
Vergleichsdatum hinausgeht */
if (Aktualisierung >= vergleich_aenderung) THEN
BEGIN
/* Schritt 4: suche zu diesem Vertrag, d.h. diesem Fahrzeug alle Schadensfälle in dieser Zeit
und summiere die Schadenssumme nach Schuldanteil */
select SUM( sf.Schadenshoehe * zu.Schuldanteil / 100 )
from Zuordnung_SF_FZ zu
join Schadensfall sf on zu.Schadensfall_id = sf.Id
where zu.Fahrzeug_ID = :current_fz
and sf.Datum between :current_aenderung and :vergleich_aenderung
into :current_value;
/* Schritt 5: abhängig von (anteiliger) Schadenssumme und bisherigem Prämiensatz
wird der neue Prämiensatz bestimmt und das Datum weitergesetzt */
update Versicherungsvertrag
set Praemiensatz =
CASE
WHEN :current_value is null THEN CASE
WHEN :current_satz > 100
THEN :current_satz - 20
WHEN :current_satz BETWEEN 40 AND 100
THEN :current_satz - 10
ELSE 30
END
WHEN :current_value = 0 THEN :current_satz - 10
WHEN :current_value < 500 THEN :current_satz
WHEN :current_value < 1000 THEN :current_satz + 10
WHEN :current_value >= 1000 THEN :current_satz + 30
END,
Praemienaenderung = DATEADD( YEAR, 1, :current_aenderung )
where ID = :current_id;
Erledigt = Erledigt + 1;
END
END
SUSPEND;
END
Als Eingabe-Parameter wird nur das Datum der derzeitigen Aktualisierung benötigt. Ein Vertrag wird dann geprüft, wenn der Zeitraum der nächsten Prämienrechnung – vom Datum der letzten Prämienänderung aus ein Jahr – das Datum der Aktualisierung enthält. Mit diesem Verfahren werden immer nur „neue“ Schadensfälle und die jeweils anstehenden Prämienrechnungen berücksichtigt.
Als Ausgabe-Parameter nehmen wir nur die Anzahl der berechneten Verträge, also der geänderten Erledigt-Vermerke.
Als Variable benötigen wir Zwischenwerte für alle Angaben, die im Arbeitsablauf benutzt werden.
Der Arbeitsablauf umfasst die folgenden Punkte:
- Der Rückgabewert wird mit dem Anfangswert belegt und angezeigt zum Zeichen dafür, dass die Prozedur arbeitet.
- Als Vorarbeit wird ein „Anfänger“, der als Eigener_Kunde gespeichert ist, auf einen Prämiensatz von 200 gesetzt. Als Anfänger gilt, wenn der Führerschein beim Abschluss des Vertrags noch nicht zwei Jahre alt ist oder wenn der Führerschein vor dem 21. Geburtstag erworben wurde.
- Bei der Prüfung auf „zwei Jahre“ handelt es sich nicht um einen sachlichen Fehler: Dieser Arbeitsablauf wird innerhalb des ersten Versicherungsjahres ausgeführt; später ist die Hauptbedingung „Praemienaenderung IS NULL“ niemals mehr gegeben. In der Praxis müssen solche Bedingungen wegen möglicher Missverständnisse vorher ganz genau formuliert werden.
- Schritt 1 wählt die Verträge aus, die „im Moment“ zu prüfen und ggf. zu bearbeiten sind: alle eigenen Kunden, deren Prämiensatz noch nie geprüft wurde (dann wird das Abschlussdatum, also das Datum der ersten Rechnung, zur Berechnung verwendet) oder deren letzte Prämienänderung vor dem Datum der Aktualisierung liegt. Durch die FOR-SELECT-Schleife wird jeder dieser Verträge innerhalb von DO BEGIN...END einzeln bearbeitet; die dazu benötigten Werte werden in den Variablen zwischengespeichert.
- Schritt 2 berechnet das Schlussdatum der letzten Prämienrechnung. Wir arbeiten hier nur mit Jahresrechnungen; da bei BETWEEN beide Grenzwerte einbezogen werden, müssen wir das Schlussdatum um einen Tag verringern.
- Schritt 3 erledigt die letzte Vorprüfung:
- Wenn das Datum der Aktualisierung vor dem Schlussdatum liegt, soll noch keine neue Rechnung erfolgen.
- Aber wenn das Datum der Aktualisierung das Schlussdatum der letzten Prämienrechnung überschreitet, ist eine neue Rechnung und damit eine Überprüfung des Prämiensatzes fällig.
- Schritt 4 berechnet die Summe aller Schadensfälle:
- Berücksichtigt wird die Summe der Schadenshöhe je Vorfall, also aus der Tabelle Schadensfall.
- Dieser Wert wird bei der Summierung anteilig nach dem Schuldanteil aus der Tabelle Zuordnung_SF_FZ berücksichtigt.
- Das Ergebnis bei current_value lautet NULL, wenn das Fahrzeug nicht in einen Schadensfall verwickelt wurde, es lautet 0 bei fehlender Teilschuld und größer als 0 bei Mit- oder Vollschuld.
- Schritt 5 berechnet den Prämiensatz neu:
- Ohne Schadensfall wird er um 10 Punkte verringert, wenn er bisher kleiner/gleich 100 beträgt, und um 20 Punkte, wenn er bisher größer ist.
- Das Minimum von 30 kann nicht unterschritten werden.
- Mit Schadensfall ohne Teilschuld wird der Prämiensatz um 10 Punkte verringert.
- Bei einem sehr geringen Schaden bleibt der Prämiensatz unverändert.
- Bei einem kleineren Schaden wird er um 10 Punkte, sonst um 30 Punkte erhöht.
- Gleichzeitig wird das Datum der Prämienänderung um 1 Jahr weitergesetzt.
Mit dieser Prozedur können die neuen Prämiensätze berechnet und für die nächste Prämienrechnung vorbereitet werden:
Berechne die Prämiensätze für alle (eigenen) Verträge, bei denen im 3. Quartal 2009 eine neue Prämienrechnung ansteht.
EXECUTE PROCEDURE Update_Praemiensatz('30.09.2009');
Ausgabe------ Procedure executing results: ------
Erledigt = 7
Testdaten in einer Tabelle erstellen
Das übliche Vorgehen, wenn in einer Tabelle viele Testdaten gespeichert werden sollen, werden wir im Kapitel Testdaten erzeugen verwenden:
- In Zusatztabellen werden geeignete Feldinhalte gesammelt: eine Tabelle mit möglichen Vornamen, eine mit Nachnamen usw.
- Wie im Kapitel Einfache Tabellenverknüpfung beschrieben, werden als „kartesisches Produkt“ alle Zeilen und Spalten aller Zusatztabellen miteinander kombiniert.
- Oder die Zeilen und Spalten der Zusatztabellen werden per Zufallsfunktion miteinander verknüpft.
Bei wenigen Spalten der Zieltabelle kann auf Zusatztabellen verzichtet werden; dieses Verfahren nutzen wir im folgenden Beispiel. (Das Verfahren ist theoretisch auch für komplexere Tabellen möglich, wird dann aber schnell unübersichtlich; schon die folgenden CASE-Konstruktionen deuten solche Probleme an.)
Erzeuge eine bestimmte Anzahl von Datensätzen in der Tabelle Fahrzeug; die Anzahl der neuen Einträge soll als Parameter vorgegeben werden.
CREATE OR ALTER PROCEDURE Insert_Into_Fahrzeug
( Anzahl INTEGER = 0)
RETURNS ( Maxid INTEGER)
AS
DECLARE VARIABLE Temp INTEGER = 0;
DECLARE VARIABLE Listekz CHAR( 66) = 'E E E DU DU BOTRE RE OB OB GE GE HERHAMBO BO DO DO UN UN EN EN ';
DECLARE VARIABLE Listekza CHAR( 26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE VARIABLE Listefrb CHAR(100)
= 'elfenbein schwarz gelb orange ocker blau silbern grau braun weiss ';
DECLARE VARIABLE Kz VARCHAR( 3);
DECLARE VARIABLE Name VARCHAR( 30);
DECLARE VARIABLE Rand1 INTEGER = 0;
DECLARE VARIABLE Rand2 INTEGER = 0;
DECLARE VARIABLE Rand3 INTEGER = 0;
DECLARE VARIABLE Rand4 INTEGER = 0;
DECLARE VARIABLE Rand5 INTEGER = 0;
BEGIN
Maxid = 0;
WHILE (Temp < Anzahl) DO
BEGIN
/* neue Zufallszahlen */
Rand1 = FLOOR(1 + (RAND() * 21)); /* für Kfz-Kz. eine Zufallszahl 1 bis 22 */
Rand2 = FLOOR(0 + (RAND() * 26)); /* ebenso eine Zufallszahl 0 bis 26 */
Rand3 = FLOOR(1 + (RAND() * 25)); /* ebenso eine Zufallszahl 1 bis 26 */
Rand4 = FLOOR(1 + (RAND() * 9)); /* für Farbe eine Zufallszahl 1 bis 10 */
Rand5 = FLOOR(1 + (RAND() * 22)); /* für Fz.Typ eine Zufallszahl 1 bis 23 */
/* hole per Zufall Rand1 eines der Kürzel aus Listekz */
Kz = TRIM(SUBSTRING(:Listekz from (:Rand1*3 - 2) for 3));
/* mache daraus ein vollständiges Kfz-Kennzeichen */
Name = Kz
Dies definiert die Prozedur mit folgenden Bestandteilen:
- Mit Anzahl als einzigem Eingabe-Parameter wird festgelegt, wie viele Datensätze erzeugt werden sollen. Dazu gehören der Name und Typ der Variablen sowie ein Vorgabewert.
- Mit Maxid als einzigem Ausgabe-Parameter wird angegeben, dass ein INTEGER-Wert mit diesem Namen erwartet wird. Der Anfangswert wird im ersten Befehl des „Rumpfes“ der Prozedur festgelegt.
- Zwischen AS und BEGIN stehen die verwendeten Variablen mit ihren Anfangswerten.
- Listekz ist eine Liste von 22 möglichen Kfz-Kennzeichen, Listekza enthält das Alphabet für die Zufallssuche nach dem zweiten Teil des Kennzeichens und Listefrb eine Liste von 10 Farben (zu je 10 Zeichen). Diese Listen ersetzen die temporären Tabellen.
- Temp wird als Zähler für die neuen Datensätze verwendet, Kz und Name als Zwischenspeicher bei der Bildung des neuen Kennzeichens.
- Rand1 usw. sind Variablen für Zufallszahlen. Darauf könnte auch verzichtet werden, weil die Formeln für die Zufallszahlen auch direkt in der String-Verknüpfung und im VALUES-Teil verwendet werden könnten; aber die Trennung macht es übersichtlicher.
Zwischen BEGIN und END steht der eigentliche Arbeitsablauf mit Anweisungen gemäß SQL-Programmierung. Innerhalb der WHILE-Schleife zwischen DO BEGIN und END wird jeweils ein neuer Datensatz erzeugt und gespeichert:
- Zuerst werden neue Zufallszahlen geholt.
- Durch Rand1 wird eine der Zahlen 1, 4, 7 usw. berechnet und gemäß diesem Wert eines der Kennzeichen aus Listekz geholt.
- Anschließend wird daraus ein vollständiges Kfz-Kennzeichen:
- Wenn Rand2 ungleich 0 ist, folgt ein Buchstabe nach der Zufallszahl Rand2 aus dem Alphabet, sonst nichts.
- Danach folgt ein weiterer Buchstabe nach der Zufallszahl Rand3 aus dem Alphabet.
- Danach folgt eine (Zufalls-) Zahl: bei einstelligem Kennzeichen eine vierstellige Zahl, sonst eine dreistellige.
- Schließlich wird ein neuer Datensatz eingetragen mit dem eben erzeugten Kennzeichen sowie:
- einer Farbe, die mit der Zufallszahl Rand4 aus Listefrb geholt wird
- einer Fahrzeugtyp-ID, die nach der Zufallszahl Rand5 einem der vorhandenen Werte in der Tabelle Fahrzeugtyp entspricht
Zusätzlich wird die Variable Temp weitergezählt; nach dem Ende der Arbeit wird der größte Wert von ID bestimmt, in die Variable Maxid eingetragen und per SUSPEND als Rückgabewert übernommen.
Ausgeführt wird diese Prozedur durch einen einfachen Befehl.
Erzeuge 10 neue Datensätze in der Tabelle Fahrzeug.
EXECUTE PROCEDURE Insert_into_Fahrzeug (10);
Ausgabe------ Procedure executing results: ------
MAXID = 145
Zusammenfassung
In diesem Kapitel lernten wir Prozeduren für Arbeitsabläufe kennen, die „nur“ innerhalb der Datenbank ausgeführt werden:
- Eine Prozedur kann mit oder ohne Argumenten und mit oder ohne Rückgabewerte ausgeführt werden.
- Sie dient zur automatischen Erledigung von Aufgaben, die „von außen“ angestoßen werden, aber keine zusätzlichen Maßnahmen des Anwenders oder der Anwendung benötigen.
- Bei einem solchen Arbeitsablauf werden viele Bestandteile einer Programmiersprache benutzt.
Übungen
Unter „Skizzieren“ (Übung 3, 5, 6) ist wie im vorigen Kapitel gemeint: Eingabe- und Ausgabeparameter sowie Variablen mit sinnvollen Namen und Datentypen benennen, Arbeitsablauf möglichst genau mit Pseudo-Code oder normaler Sprache beschreiben.
Tipp: Die Parameter ergeben sich in der Regel aus der Aufgabenstellung. Aus der Überlegung zum Arbeitsablauf folgen die Variablen.
| Übung 1 | Prozeduren verwenden | Zur Lösung |
In welchen der folgenden Situationen ist eine Prozedur sinnvoll, in welchen nicht? Gehen Sie davon aus, dass alle Informationen in der Datenbank gespeichert sind.
- Erstelle neue Rechnungen nach den aktuellen Prämiensätzen.
- Berechne die Weihnachtsgratifikationen der Mitarbeiter nach den erfolgten Abschlüssen.
- Ein Versicherungsvertrag wird gekündigt.
| Übung 2 | Definition einer Prozedur kontrollieren | Zur Lösung |
Nennen Sie in der folgenden Definition Punkte, die unabhängig vom SQL-Dialekt falsch sind. (Je nach DBMS sind noch andere Punkte falsch, danach wird aber nicht gefragt.) Die Prozedur soll folgende Aufgabe erledigen:
Mit einem Aufruf sollen bis zu 5 Abteilungen neu gespeichert werden. Die Angaben sollen wie folgt in jeweils einem String zusammengefasst werden:
- 'AbCd-Name der Abteilung-Ort der Abteilung'
zuerst 4 Zeichen für das Kürzel, Bindestrich, der Name der Abteilung, Bindestrich, der Ort der Abteilung
In einer Schleife werden die 5 Zeichenketten verarbeitet, nämlich aufgeteilt und als Neuaufnahme in der Tabelle Abteilung gespeichert. Als Rückgabewert soll die letzte neu vergebene ID dienen.
create Insert_Abteilungen as PROCEDURE
/* Eingabe: mehrere neue Abteilungen einzutragen in der Schreibweise */
'AbCd-Name der Abteilung-Ort der Abteilung' */
INPUTS Inhalt1, Inhalt2, Inhalt3, Inhalt4, Inhalt5 VARCHAR(70)
OUTPUTS lastid INTEGER
variables x1, pos INTEGER,
temp VARCHAR(70),
krz CHAR(4),
name, ort VARCHAR(30)
as BEGIN
x1 = 0;
WHILE (x1 < 5)
DO BEGIN
x1 = x1 + 1;
temp = CASE x1
WHEN 1 THEN Inhalt1
WHEN 2 THEN Inhalt2
WHEN 3 THEN Inhalt3
WHEN 4 THEN Inhalt4
WHEN 5 THEN Inhalt5
END
/* vorzeitiger Abbruch, falls NULL übergeben wird */
IF (temp IS NULL)
THEN break;
/* bestimme durch '-', wo der Name aufhört und der Ort anfängt */
pos = POSITION( '-', temp, 6 );
krz = SUBSTRING( temp from 1 for 4 );
name = SUBSTRING( temp from 5 for (pos-5) );
ort = SUBSTRING( temp from (pos+1) );
/* neuen Datensatz speichern */
insert into Abteilung
( Kuerzel, Bezeichnung, Ort )
values ( krz, name, ort );
lastid = SELECT ID from Abteilung WHERE Kuerzel = krz;
END
END
| Übung 3 | Prozedur Insert_Fahrzeugtyp erstellen | Zur Lösung |
Skizzieren Sie eine Prozedur Insert_Fahrzeugtyp zum Speichern von Fahrzeugtyp und Hersteller in einem Schritt: Es ist zu prüfen, ob der Hersteller schon gespeichert ist; wenn ja, ist diese ID zu verwenden, andernfalls ist ein neuer Eintrag zu erstellen und dessen ID zu übernehmen. Mit dieser Hersteller-ID ist der Fahrzeugtyp zu registrieren.
| Übung 4 | Prozedur Insert_Fahrzeugtyp erstellen | Zur Lösung |
Erstellen Sie die Prozedur Insert_Fahrzeugtyp aus der vorigen Übung.
| Übung 5 | Prozedur Insert_Schadensfall erstellen | Zur Lösung |
Skizzieren Sie eine Prozedur Insert_Schadensfall zum Speichern eines Schadensfalls; dabei soll nur das Fahrzeug des Versicherungsnehmers beteiligt sein.
| Übung 6 | Prozedur Update_Schadensfall erstellen | Zur Lösung |
Skizzieren Sie eine Prozedur Update_Schadensfall zum Ändern eines Schadensfalls; dabei soll jeweils ein weiteres beteiligtes Fahrzeug registriert werden. (Hinweise: Sie müssen auch berücksichtigen, wie sich die Schadenshöhe neu verteilt. Sie können davon ausgehen, dass der Versicherungsnehmer schon gespeichert ist – egal, ob es sich um einen eigenen Kunden handelt oder nicht.) Beschreiben Sie zusätzlich, welche Punkte beim Arbeitsablauf und damit bei den Eingabe-Parametern noch geklärt werden müssen.
| Lösung zu Übung 1 | Prozeduren verwenden | Zur Übung |
- sinnvoll, weil es nach den gespeicherten Informationen automatisch erledigt werden kann
- nicht sinnvoll, weil zwar die Angaben automatisch zusammengestellt werden können, aber die Gratifikation eine individuelle Entscheidung der Geschäftsleitung ist
- sinnvoll, weil mehrere zusammenhängende Maßnahmen auszuführen sind
| Lösung zu Übung 2 | Definition einer Prozedur kontrollieren | Zur Übung |
- Zeile 1: Der Befehl lautet: CREATE PROCEDURE <name>.
- Zeile 2/3: Der Kommentar ist falsch abgeschlossen.
- Zeile 4 ff.: Die gemeinsame Deklaration von Parametern oder Variablen ist nicht zulässig.
- Zeile 4: Die Eingabe-Parameter müssen in Klammern stehen.
- Zeile 5: Die Ausgabe-Parameter werden bei keinem DBMS durch eine OUTPUTS-Klausel angegeben.
- Zeile 6 ff.: Die Variablen folgen erst hinter AS und werden anders deklariert.
- Zeile 21: Es fehlt das Semikolon am Ende der Zuweisung.
- Zeile 28: Die Längenangabe im SUBSTRING ist nicht korrekt.
- Zeile 31 ff.: Die Eindeutigkeit der Namen von Variablen und Spalten wird teilweise nicht beachtet.
| Lösung zu Übung 3 | Prozedur Insert_Fahrzeugtyp erstellen | Zur Übung |
- Eingabe-Parameter: TypBezeichnung Varchar(30), HerstellerName Varchar(30), HerstellerLand Varchar(30)
- Ausgabe-Parameter: TypID Integer, HerstellerID Integer
- Variable: werden nicht benötigt
- Arbeitsablauf:
- Suche in der Tabelle Fahrzeughersteller den gegebenen HerstellerName und registriere die gefundene ID im Parameter HerstellerID.
- Wenn dieser Parameter jetzt NULL ist, fehlt der Eintrag noch. Also ist er neu aufzunehmen unter Verwendung der Angaben aus HerstellerName und HerstellerLand; das liefert den Wert von HerstellerID.
- Damit kann der neue Datensatz in der Tabelle Fahrzeugtyp registriert werden; die neue ID dieses Datensatzes wird als Wert TypID zurückgegeben.
| Lösung zu Übung 4 | Prozedur Insert_Fahrzeugtyp erstellen | Zur Übung |
Diese Variante der Lösung benutzt die Firebird-Syntax, vor allem hinsichtlich der Trennung von Eingabe- und Ausgabeparametern.
CREATE OR ALTER PROCEDURE Insert_Fahrzeugtyp
( TypBezeichnung VARCHAR(30),
HerstellerName VARCHAR(30),
HerstellerLand VARCHAR(30)
)
RETURNS( TypID INTEGER, HerstellerID INTEGER )
AS
BEGIN
/* ist der Hersteller schon registriert? */
select ID from Fahrzeughersteller
where Name = :HerstellerName
into :HerstellerID;
/* nein, dann als Hersteller neu aufnehmen */
IF (HerstellerID is null) THEN
BEGIN
HerstellerID = NEXT VALUE FOR Fahrzeughersteller_ID;
insert into Fahrzeughersteller
values ( :HerstellerID, :HerstellerName, :HerstellerLand );
END
/* anschließend den Typ registrieren */
TypID = NEXT VALUE FOR Fahrzeugtyp_ID;
INSERT INTO Fahrzeugtyp
VALUES ( :TypID, :TypBezeichnung, :HerstellerID );
END
| Lösung zu Übung 5 | Prozedur Insert_Schadensfall erstellen | Zur Übung |
- Eingabe-Parameter: die Angaben zum Schadensfall (Datum Date, Ort varchar(200), Beschreibung varchar(1000), Schadenshoehe number, Verletzte char(1), Mitarbeiter_ID Integer), Fahrzeugbeteiligung (ID Integer oder Kennzeichen Varchar(10), Schuldanteil Integer)
- Ausgabe-Parameter: neue ID des Schadensfalls
- Arbeitsablauf:
- Insert into Schadensfall: Registriere den Schadensfall, notiere die neue ID
- Select from Fahrzeug: Suche ggf. zum Kennzeichen die Fahrzeug-ID
- Insert into Zuordnung_SF_FZ: Registriere die Zuordnung zwischen Schadensfall und Fahrzeug
- Variable werden voraussichtlich nicht benötigt.
| Lösung zu Übung 6 | Prozedur Change_Schadensfall erstellen | Zur Übung |
- Eingabe-Parameter: Schadensfall-ID Integer, Fahrzeugbeteiligung (Kennzeichen Varchar(10), anteilige Schadenshöhe Number)
- Ausgabe-Parameter: eigentlich nicht erforderlich, aber als „Erfolgsmeldung“ die ID der neuen Zuordnung
- Arbeitsablauf:
- Select from Fahrzeug: Suche zum Kennzeichen die Fahrzeug-ID.
- Insert into Zuordnung_SF_FZ: Registriere die Zuordnung zwischen Schadensfall und dem neuen Fahrzeug.
- Übernimm die ID dieser neuen Zuordnung als Ausgabe-Parameter.
- Update Zuordnung_SF_FZ: Ändere im ersten Eintrag zu diesem Schadensfall die anteilige Schadenshöhe unter Berücksichtigung des neu registrierten beteiligten Fahrzeugs. (Der benötigte „erste Eintrag“ kann durch eine passende WHERE-Klausel direkt geändert werden; ersatzweise kann er auch durch einen eigenen SELECT-Befehl bestimmt werden – dann wird für die ID eine Variable benötigt.)
- Variable werden voraussichtlich nicht benötigt.
- Unklarheiten: Bei diesem Arbeitsablauf wird davon ausgegangen, dass zu jedem später registrierten Fahrzeug ein Teil des ursprünglichen Schadens gehört; es wird nicht berücksichtigt, dass sich die Schadensverteilung insgesamt ändern kann. Völlig offen ist, wie sich der Verschuldensanteil beim ersten Eintrag und bei jedem weiteren Eintrag ändern kann. In beiden Punkten ist lediglich klar, dass alle Einzelwerte zu summieren sind und den Maximalwert (100 beim Schuldanteil bzw. den Gesamtschaden) nicht überschreiten dürfen.
Siehe auch
Teile dieses Kapitels beziehen sich auf Erläuterungen in den folgenden Kapiteln:
- SQL-Programmierung
- Views: Eine View mit variabler Selektion
- Testdaten erzeugen
- Einfache Tabellenverknüpfung – alle Kombinationen aller Datensätze
- Funktionen (2) – u. a. RAND für Zufallszahlen
- Tipps und Tricks
Bei Wikipedia gibt es grundlegende Hinweise:
- Fat Client als ein Prinzip der Datenverarbeitung
| Trigger |
Ein Trigger ist so etwas wie eine Routine zur Ereignisbehandlung: Immer dann, wenn sich in der Datenbank eine bestimmte Situation ereignet, wird eine spezielle Prozedur automatisch ausgeführt.
Bitte haben Sie Nachsicht: Wegen der vielen Varianten bei den DBMS beschränkt sich dieses Kapitel bei den Hinweisen und Beispielen weitgehend auf Firebird. Zusammen mit den speziellen Hinweisen zur SQL-Programmierung sollten sie problemlos an andere DBMS-Regeln angepasst werden können.
Ein Überblick
Schon die kurze Einleitung weist darauf hin: Ein Trigger wird niemals vom Benutzer gezielt aufgerufen, sondern immer automatisch vom DBMS erledigt; darin unterscheidet er sich wesentlich von eigenen Funktionen oder Prozeduren.
- Eine deutsche Übersetzung wäre eigentlich Auslöser; besser passen würde „etwas, das ausgelöst wird“. Allenfalls spricht man noch von Ereignisbehandlungsroutinen. Solche Formulierungen sind aber äußerst ungebräuchlich; der Begriff Trigger hat sich eingebürgert.
Ein Trigger ist in folgenden Situationen nützlich:
- Werte in einer Zeile einer Tabelle sollen festgelegt werden.
- Dies wird vor allem benutzt, wenn es keine AutoInc-Spalte gibt, siehe das Beispiel mit der nächsten ID.
- Werte sollen vor dem Speichern auf ihre Plausibilität geprüft werden.
- Veränderungen in der Datenbank sollen automatisch protokolliert werden.
- Die Regeln der referenziellen Integrität sollen mit Hilfe der Fremdschlüssel-Beziehungen überwacht werden.
Trigger definieren
Trigger erstellen
Die Syntax für die Definition eines Triggers sieht grundsätzlich so aus:
CREATE OR ALTER TRIGGER <routine-name> FOR <Tabellenname>
[ ACTIVE | INACTIVE ]
{ BEFORE | AFTER } /* bei MS-SQL heißt es INSTEAD OF */
{ INSERT | UPDATE | DELETE }
[ POSITION <zahl> ]
AS
BEGIN
[ <variablenliste> ]
<routine body>
END
Auch dabei sind wieder die Besonderheiten des jeweiligen DBMS zu beachten. Aber die Ähnlichkeit zu den Routinen fällt auf.
Notwendig sind folgende Angaben:
- neben dem Befehlsnamen der Name des Triggers
- dazu die Tabelle, zu der er gehört
- mehrere Angaben, bei welchem Befehl und an welcher Stelle er wirksam sein soll; Oracle kennt neben den Varianten, die bei der Syntax genannt werden, auch eine WHEN-Bedingung.
- das Schlüsselwort AS als Zeichen für den Inhalt
- die Schlüsselwörter BEGIN und END als Begrenzer für den Inhalt
Hinzu kommen die folgenden Angaben:
- eine Liste von Variablen, die innerhalb der Routine verwendet werden; diese Liste steht je nach DBMS zwischen AS und BEGIN oder innerhalb des Rumpfes (also zwischen BEGIN und END)
- die Befehle, die innerhalb der Prozedur ausgeführt werden sollen
Eingabe- und Ausgabe-Parameter gibt es nicht, weil es sich um automatische Arbeitsabläufe handelt.
Die folgenden Variablen stehen immer zur Verfügung. MS-SQL benutzt andere Verfahren; bei Oracle ist die Dokumentation nicht eindeutig.[1]
- OLD ist der Datensatz vor einer Änderung.
- NEW ist der Datensatz nach einer Änderung.
Mit diesen Variablen zusammen mit den Spaltennamen können die Werte „vorher“ und „nachher“ geprüft und bearbeitet werden (siehe die Beispiele).
Bei den Befehlen innerhalb des Triggers handelt es sich (eher selten) um „normale“ SQL-Befehle und überwiegend um Bestandteile der SQL-Programmiersprache. Zum Semikolon, das den Abschluss des CREATE-Befehls darstellen sollte, aber innerhalb des Inhalts bereits für jede einzelne Anweisung benutzt wird, beachten Sie bitte auch die Hinweise zur SQL-Programmierung unter Anweisungen begrenzen.
Trigger deaktivieren
Natürlich kann ein Trigger gelöscht werden:
DROP TRIGGER <name>;
Wenn das DBMS es ermöglicht, kann er auch vorübergehend deaktiviert werden.
ALTER TRIGGER <name> INACTIVE;
Bei ALTER sind nur die Angaben erforderlich, die sich ändern.
Einen Trigger vorübergehend abzuschalten, ist vor allem während eines längeren Arbeitsablaufs in zwei Situationen hilfreich:
- Ein Trigger bremst sehr stark; dann kann z. B. auf eine Protokollierung jeder kleinen Änderung verzichtet werden.
- Wenn Widersprüche in den Daten auftreten könnten, die sich nach vollständiger Erledigung „von selbst auflösen“, können sie durch die Deaktivierung vermieden werden.
Beispiele
Lege die nächste ID fest
Wenn ein DBMS keinen automatischen Zähler, also keine AUTOINCREMENT-Spalte ermöglicht, kann eine ID vom Anwender vergeben werden. Viel besser ist aber, wenn sich das DBMS selbst darum kümmert. Dies wird in der Beispieldatenbank bei Firebird und Oracle benutzt. Zum einen wird eine SEQUENCE definiert; der Trigger holt den nächsten Wert:
CREATE OR REPLACE TRIGGER Abteilung_BI
BEFORE INSERT ON Abteilung
FOR each row
WHEN (new.ID IS NULL)
BEGIN
SELECT Abteilung_ID.NEXTVAL INTO :new.ID FROM DUAL;
END
Der Trigger wird vor einem INSERT-Befehl aufgerufen. Wenn für den neuen Datensatz – repräsentiert durch die Variable new – keine ID angegeben ist, dann soll der NEXTVAL gemäß der SEQUENCE Abteilung_ID geholt werden.
Der Name des Triggers Abteilung_BI soll andeuten, dass er zur Tabelle Abteilung gehört und wie folgt aktiv ist: B (= Before) I (= Insert). Bei Firebird fügen wir noch eine '0' an zur Festlegung, dass er an Position null auszuführen ist.
Protokolliere Zeit und Benutzer bei Änderungen
In vielen Situationen ist es wichtig, dass Änderungen in einer Datenbank kontrolliert werden können. (Das DBMS macht es für sich sowieso, aber oft soll es auch „nach außen hin“ sichtbar sein.) Dafür gibt es in Tabellen Spalten wie folgt (in der Beispieldatenbank verzichten wir darauf):
CREATE TABLE /* usw. bis */ Last_User VARCHAR(30), Last_Change TIMESTAMP, /* usw. */
Wenn solche Spalten bei der Tabelle Mitarbeiter vorhanden wären, gäbe es einen passenden Trigger. Ohne dass sich der Anwender darum kümmern müsste, werden Nutzer (laut Anmeldung an der Datenbank) und aktuelle Zeit immer gespeichert.
CREATE OR ALTER TRIGGER Mitarbeiter_BU1 FOR Mitarbeiter
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
BEGIN
new.Last_User = CURRENT_USER;
new.Last_Change = CURRENT_TIMESTAMP;
END
Aktualisiere abhängige Daten
In vielen Fällen gibt es Abhängigkeiten zwischen verschiedenen Tabellen. Durch Fremdschlüssel-Beziehungen können diese Verbindungen bei Änderungen automatisch berücksichtigt werden. Solche Anpassungen sind auch durch Trigger möglich.
Mit einer Prozedur (siehe dort) hatten wir ein INSERT in mehrere Tabellen ausgeführt. Ähnlich kann ein Trigger abhängige Datensätze löschen:
- Der Anwender ruft ein DELETE für einen Versicherungsvertrag auf.
- Der Trigger löscht den dazugehörigen Versicherungsnehmer und das Fahrzeug.
- Ein Trigger zur Tabelle Fahrzeug löscht alle registrierten Schadensfälle usw.
Aber das ist so komplex und hat Auswirkungen auf weitere Tabellen, dass wir darauf verzichten.
- In einer „echten“ Firma müssten solche Informationen sowieso dauerhaft gespeichert werden – zumindest so lange wie die Buchhaltungsdaten. Ein Vertrag wird also im normalen Betrieb niemals gelöscht, sondern als „nicht mehr aktiv“ markiert.
Aber auch in unserer Firma gibt es sinnvolle Maßnahmen:
Beim Ausscheiden eines Mitarbeiters wird (soweit vorhanden) ein persönlicher Dienstwagen gestrichen.
create or alter trigger Mitarbeiter_BD1 for Mitarbeiter
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
update Dienstwagen
set Mitarbeiter_ID = null
where Mitarbeiter_ID = old.ID;
END
create trigger Mitarbeiter_BD1
BEFORE DELETE on mitarbeiter
FOR EACH ROW
BEGIN
update Dienstwagen
set Mitarbeiter_ID = null
where Mitarbeiter_ID = old.ID;
END
Bevor ein Mitarbeiter gelöscht wird, wird geprüft, ob ihm ein persönlicher Dienstwagen zugewiesen ist. Dieser Vermerk wird (sofern vorhanden) auf NULL gesetzt; die WHERE-Klausel greift dabei auf die ID des bisherigen Datensatzes der Tabelle Mitarbeiter zu.
Zusammenfassung
Dieses Kapitel erläuterte Trigger als automatisch ausgeführte Prozeduren:
- Ein Trigger wird vor oder nach einem Speichern-Befehl (Insert, Update, Delete) ausgeführt.
- Er dient zur automatischen Prüfung oder Vervollständigung von Werten.
- Damit vereinfacht er die Arbeit für den Anwender und
- sorgt für die Einhaltung von Sicherheitsregeln in der Datenbank.
|
|
Hinweis: Bedenken Sie, welche konkreten Situationen in den letzten Kapiteln und den Übungen behandelt werden. Diese Zusammenhänge zwischen den Tabellen müssen bei der Planung einer Datenbank mit ihren Fremdschlüsseln, Prozeduren und Triggern für „gleichzeitiges“ Einfügen, Ändern oder Löschen in mehreren Tabellen und notwendigen Maßnahmen in anderen Tabellen berücksichtigt werden. Auch in dieser Hinsicht bietet unsere Beispieldatenbank nur einen kleinen Einblick in das, was mit SQL möglich ist. |
|---|
Übungen
| Übung 1 | Trigger definieren | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche sind falsch?
- Ein Trigger wird durch eine Prozedur aufgerufen.
- Ein Trigger hat keine Eingabe-Parameter.
- Ein Trigger hat keine (lokalen) Variablen.
- Ein Trigger kann nur die Tabelle bearbeiten, der er zugeordnet ist.
- Ein Trigger kann sowohl „normale“ SQL-Befehle als auch Elemente der SQL-Programmierung enthalten.
| Übung 2 | Trigger verwenden | Zur Lösung |
In welchen der folgenden Situationen ist ein Trigger sinnvoll, in welchen nicht bzw. nicht möglich? Gehen Sie davon aus, dass alle benötigten Informationen in der Firmen-Datenbank zur Speicherung vorgesehen sind.
- Wenn ein Versicherungsvertrag neu aufgenommen wird, sollen auch alle Datensätze in zugeordneten Tabellen neu aufgenommen werden.
- Vorgabewerte für einzelne Spalten können eingetragen werden (wie bei einem DEFAULT-Wert).
- Wenn eine Abteilung gelöscht (d. h. geschlossen) wird, sollen auch alle ihre Mitarbeiter gelöscht werden (weil die Abteilung ausgelagert wird).
- Wenn ein Versicherungsvertrag gelöscht wird, sollen auch alle Datensätze in zugeordneten Tabellen bearbeitet werden: Datensätze, die noch in anderem Zusammenhang benutzt werden (z. B. Mitarbeiter), bleiben unverändert; Datensätze, die sonst nicht mehr benutzt werden (z. B. Fahrzeug), werden gelöscht.
| Übung 3 | Trigger-Definition kontrollieren | Zur Lösung |
Nennen Sie in der folgenden Definition Punkte, die unabhängig vom SQL-Dialekt falsch sind. Je nach DBMS sind noch andere Punkte falsch, danach wird aber nicht gefragt. Der Trigger soll folgende Aufgabe erledigen:
- Wenn ein neuer Fahrzeugtyp aufgenommen werden soll, ist der Hersteller zu überprüfen.
- Ist der angegebene Hersteller (d. h. die Hersteller_ID) in der Tabelle Fahrzeughersteller gespeichert? Wenn ja, dann ist nichts weiter zu erledigen.
- Wenn nein, dann ist ein Fahrzeughersteller mit dem Namen „unbekannt“ zu suchen.
- Wenn dieser vorhanden ist, ist dessen ID als Hersteller_ID zu übernehmen.
- Andernfalls ist er mit der angegebenen Hersteller_ID neu als „unbekannt“ zu registrieren.
create Fahrzeugtyp_Check_Hersteller as trigger
to Fahrzeugtyp
for INSERT
as
DECLARE VARIABLE INTEGER fh_id
BEGIN
-- Initialisierung
fh_id = null;
-- prüfen, ob ID vorhanden ist
select ID
from Fahrzeughersteller
where ID = new.Hersteller_ID
-- wenn sie nicht gefunden wurde
if (fh_id is null) THEN
-- suche stattdessen den Hersteller 'unbekannt'
select ID
from Fahrzeughersteller
where Name = 'unbekannt'
-- wenn auch dazu die ID nicht gefunden wurde
if (fh_id is null) THEN
fh_id = new.Hersteller_ID;
insert into Fahrzeughersteller
values ( :fh_id, 'unbekannt', 'unbekannt' )
END
END
new.Hersteller_ID = :fh_id
END
| Übung 4 | Trigger-Definition kontrollieren | Zur Lösung |
Berichtigen Sie den Code der vorigen Aufgabe.
| Übung 5 | Trigger Mitarbeiter_On_Delete erstellen | Zur Lösung |
Skizzieren Sie den Inhalt eines Triggers Mitarbeiter_On_Delete für die Tabelle Mitarbeiter, der folgende Aufgaben ausführen soll:
- Zu behandeln ist die Situation, dass ein Mitarbeiter aus der Firma ausscheidet, also zu löschen ist.
- Suche die ID des zugehörigen Abteilungsleiters.
- In allen Tabellen und Datensätzen, in denen der Mitarbeiter als Sachbearbeiter registriert ist, ist stattdessen der Abteilungsleiter als „zuständig“ einzutragen.
Ignorieren Sie die Situation, dass der Abteilungsleiter selbst ausscheidet.
| Übung 6 | Trigger Mitarbeiter_On_Delete erstellen | Zur Lösung |
Erstellen Sie den Trigger Mitarbeiter_On_Delete aus der vorigen Übung.
Lösungen
| Lösung zu Übung 1 | Trigger definieren | Zur Übung |
Die Aussagen 2, 5 sind wahr. Die Aussagen 1, 3, 4 sind falsch.
| Lösung zu Übung 2 | Trigger verwenden | Zur Übung |
- nicht möglich, weil die Werte für die Datensätze in den zugeordneten Tabellen nicht bekannt sind
- sinnvoll, aber die Festlegung per DEFAULT ist vorzuziehen
- zur Not sinnvoll, wenn die Fremdschlüssel nicht durch ON DELETE geeignet gesteuert werden können; aber dies ist riskannt wegen der „Nebenwirkungen“, siehe Übung 5/6
- zur Not möglich, aber eher weniger sinnvoll wegen der vielen zugeordneten Tabellen und vieler „Nebenwirkungen“
| Lösung zu Übung 3 | Trigger-Definition kontrollieren | Zur Übung |
- Zeile 1: Der Befehl lautet: CREATE TRIGGER <name>.
- Zeile 2: Die Tabelle steht je nach DBMS bei ON oder FOR, nicht bei TO.
- Zeile 3: Die Aktivierung BEFORE oder AFTER (bei MS-SQL INSTEAD OF) fehlt.
- Zeile 5: Zuerst kommt der Name der Variablen, dann der Datentyp; das abschließende Semikolon fehlt.
- Zeile 10/12 und 16/18: Es fehlt die Übergabe der ID, die durch SELECT gefunden wurde, an die Variable fh_id. Je nach DBMS erfolgt dies unterschiedlich, aber es muss gemacht werden, damit das Ergebnis der Abfrage in der Variablen gespeichert und danach verwendet werden kann.
- Zeile 12, 18, 23, 26: Es fehlt jeweils das abschließende Semikolon.
- Zeile 14 und 20: Mehrere Anweisungen sind in BEGIN...END einzuschließen.
| Lösung zu Übung 4 | Trigger-Definition kontrollieren | Zur Übung |
-- Firebird-Version
create or alter trigger Fahrzeugtyp_Check_Hersteller
for Fahrzeugtyp
ACTIVE BEFORE INSERT POSITION 10
as
DECLARE VARIABLE fh_id INTEGER;
BEGIN
fh_id = null;
select ID
from Fahrzeughersteller
where id = new.Hersteller_ID
into :fh_id;
if (fh_id is null) THEN
BEGIN
select Id
from Fahrzeughersteller
where Name = 'unbekannt'
into :fh_id;
if (fh_id is null) THEN
BEGIN
fh_id = new.Hersteller_ID;
insert into Fahrzeughersteller
values ( :fh_id, 'unbekannt', 'unbekannt' );
END
END
new.Hersteller_ID = :fh_id;
END
| Lösung zu Übung 5 | Trigger Mitarbeiter_On_Delete erstellen | Zur Übung |
- Wir brauchen einen Trigger BEFORE DELETE zur Tabelle Mitarbeiter.
- Wir brauchen eine Variable für die ID des Abteilungsleiters.
- Durch einen SELECT auf die Abteilung_ID des ausscheidenden Mitarbeiters bekommen wir die ID des Abteilungsleiters; die wird in der Variablen gespeichert.
- Für alle betroffenen Tabellen ist ein UPDATE zu machen, durch das die Mitarbeiter_ID durch die ID des Abteilungsleiters ersetzt wird. Dies betrifft die Tabellen Schadensfall, Versicherungsvertrag.
- Die Tabelle Dienstwagen kann unberücksichtigt bleiben wegen des o. g. Beispiels.
| Lösung zu Übung 6 | Trigger Mitarbeiter_On_Delete erstellen | Zur Übung |
-- Firebird-Version
create or alter trigger Mitarbeiter_On_Delete
for Mitarbeiter
ACTIVE BEFORE DELETE POSITION 10
as
DECLARE VARIABLE ltr_id INTEGER;
BEGIN
ltr_id = null;
-- hole die ID des Abteilungsleiters in die Variable
select ID
from Mitarbeiter
where Abteilung_id = old.Abteilung_ID
and Ist_Leiter = 'J'
into :ltr_id;
-- ändere die Mitarbeiter_ID für die Schadensfälle
update Schadensfall
set Mitarbeiter_ID = :ltr_id
where Mitarbeiter_ID = old.ID;
-- ändere die Mitarbeiter_ID für die Verträge
update Versicherungsvertrag
set Mitarbeiter_ID = :ltr_id
where Mitarbeiter_ID = old.ID;
END
Siehe auch
Verschiedene Einzelheiten stehen in folgenden Kapiteln:
Über Wikipedia sind grundlegende Informationen zu erhalten:
- Datenbanktrigger
- Ereignisse in der Programmierung, Behandlung von Ereignissen
- Referentielle Integrität
- Hinweis
- ↑ Einerseits heißt es, dass diese Variablen immer bekannt sind; andererseits sind sie sowohl in der Definition als auch in den meisten Beispielen in einer REFERENCING-Klausel aufgeführt.
| Tipps und Tricks |
Dieses Kapitel stellt ein paar nützliche Verfahren vor, die in keines der anderen Kapitel passten und „zuwenig Stoff“ für ein eigenes Kapitel enthalten.
Die letzte ID abfragen
Wenn bei einer Tabelle für die Spalte ID die neuen Werte automatisch als AUTO_INCREMENT vergeben werden, benötigt man den neu vergegebenen Wert häufig für die korrekte Behandlung der Fremdschlüssel-Beziehungen.
Firebird: Rückgabewerte benutzen
Seit Firebird 2.x kann der INSERT-Befehl Maßnahmen, die durch einen Before-Insert-Trigger ausgeführt werden – also auch die Zuweisung einer Sequence – durch eine RETURNING-Klausel abfragen:
INSERT INTO <Tabelle> ( <Spaltenliste> )
VALUES ( <Werteliste> )
RETURNING <Spaltenliste> [ INTO <Variablenliste> ]
Die INTO-Klausel wird bei Aufgaben innerhalb der SQL-Programmierung benutzt. Beispiel für direkten Aufruf:
insert into Fahrzeug ( Kennzeichen, Fahrzeugtyp_ID )
values ( 'B-JT 1234', 7 )
RETURNING ID, Farbe;
Ausgabe------ Inserted values ------
ID = 652
FARBE = <null>
Die RETURNING-Klausel gibt es auch bei DB2 und Oracle.
MS-SQL: spezielle Abfragen
Je nach Situation wird nach einem INSERT in einem weiteren Befehl der neu zugeordnete Wert abgefragt.
Variante 1 mit einer lokalen Variablen:
SELECT @@IDENTITY
Dies liefert den letzten für eine IDENTITY-Spalte vergebenen Wert der aktuellen Verbindung zurück. Hierbei wird keine Tabelle angegeben; es kann aber auch der Wert einer anderen Tabelle geliefert werden (beispielsweise wenn indirekt über einen Trigger eine weitere Tabelle bearbeitet wird).
Variante 2 mit einer Funktion:
SELECT SCOPE_IDENTITY();
Dies liefert den letzten für eine IDENTITY-Spalte vergebenen Wert der aktuellen Verbindung zurück.
Variante 3 mit einer Funktion, die sich auf eine bestimmte Tabelle bezieht:
SELECT IDENT_CURRENT('Fahrzeug');
Zu den Unterschieden zwischen diesen Verfahren siehe MSDN: @@IDENTITY.
MySQL: spezielle Abfragen
Unmittelbar nach einem INSERT wird in einem weiteren Befehl der neu zugeordnete Wert mit einer speziellen Funktion abgefragt:
SELECT LAST_INSERT_ID();
Dies liefert immer den letzten für eine AUTO_INCREMENT-Spalte vergebenen Wert der aktuellen Verbindung zurück. Hierbei wird keine Tabelle angegeben. Je nach Arbeitsumgebung gibt es auch die interne Funktion mysql_insert_id(), die manchmal (siehe Dokumentation) abweichende Ergebnisse liefert.
Oracle: Wert abfragen
In Oracle dient die SEQUENCE zur Vergabe eines automatischen Zählers. Mit <sequence_name>.NEXTVAL wird der nächste Wert zugewiesen, mit <sequence_name>.CURRVAL der aktuelle Wert abgefragt.
Im Skript zur Beispieldatenbank wird für die Tabelle Mitarbeiter eine SEQUENCE Mitarbeiter_ID definiert und verwendet. Für einen neuen Mitarbeiter erhält man so die zugewiesene ID:
INSERT INTO MITARBEITER
( ID, PERSONALNUMMER, NAME, VORNAME /* und weitere Angaben */ )
VALUES ( Mitarbeiter_ID.NEXTVAL, '80017', 'Schicker', 'Madelaine' );
select CONCAT('Last ID = ' , to_char(Mitarbeiter_ID.CURRVAL)) from dual;
Ausgabe1 row inserted;
Last ID = 23
Weitere Informationen über Sequenzen siehe Oracle: Sequenzen.
Tabellenstruktur auslesen
Die Tabellen, Spalten, Views, Fremdschlüssel usw. werden in der Datenbank in systemeigenen Strukturen gespeichert. Sie können genauso wie die „eigenen“ Daten per SELECT abgefragt werden. Auch wenn der SQL-Standard dafür mit INFORMATION_SCHEMA ein detailliertes Muster vorgesehen hat, gibt es Unterschiede zwischen den DBMS.
DB2
Bei DB2 spricht man von Katalog-Tabellen. Sie befinden sich im Schema SYSIBM. Einige Beispiel-Zugriffe auf die Katalog-Tabellen.
-- Liste aller Tabellen in einer Datenbank, die von P123 erstellt wurden.
SELECT creator, name
FROM sysibm.systables
WHERE creator = 'P123'
AND type = 'T'
--
-- Liste aller Views in einer Datenbank, die von P123 erstellt wurden.
SELECT creator, name
FROM sysibm.systables
WHERE creator = 'P123'
AND type = 'V'
--
-- Liste aller Fremdschlüssel-Beziehungen, die von P123 erstellt wurden.
-- TBNAME ist die (Detail-)Tabelle mit dem Fremdschlüssel
-- REFTBNAME ist die (Master-)Tabelle, auf die der Fremdschlüssel verweist
SELECT creator, tbname, reftbname
FROM sysibm.sysrels
WHERE creator = 'P123'
Firebird
Bei Firebird und Interbase beginnen Bezeichner der „Systemtabellen“ mit RDB$.
-- listet die Tabellen einer Datenbank auf
SELECT rdb$relation_name from rdb$relations
where rdb$system_flag = 0
and rdb$relation_type = 0;
-- listet die Views einer Datenbank auf
SELECT rdb$relation_name from rdb$relations
where rdb$system_flag = 0
and rdb$relation_type = 1;
-- listet die Spaltennamen mit den dazugehörigen Datentypen einer Tabelle auf
SELECT rdb$relation_name, rdb$field_name, rdb$field_source
from rdb$relation_fields
where rdb$system_flag = 0
and rdb$view_context is null
order by rdb$relation_name, rdb$field_position
-- listet die Fremdschlüssel einer Tabelle auf (i.d.R. nur zwischen je einer Spalte)
SELECT rel.RDB$Constraint_Name AS ForeignKey,
co1.RDB$Relation_Name AS DetailTable,
CASE idx.RDB$Segment_Count
WHEN 1 THEN fl1.RDB$Field_Name
ELSE idx.RDB$Segment_Count
END AS Fields,
rel.RDB$Const_Name_UQ AS PrimaryKey,
co2.RDB$Relation_Name AS MasterTable,
fl2.RDB$Field_Name AS MasterField
FROM RDB$Ref_Constraints rel
/* RDB$Relation_Constraints wird 2x benötigt:
als co1 für den Tabellennamen des ForeignKey
als co2 für den Tabellennamen des PrimaryKey, auf den sich der ForeignKey bezieht */
/* ebenso RDB$Index_Segments
als fl1 für den Spaltennamen des FK
als fl2 für den Spaltennamen des PK */
JOIN RDB$Relation_Constraints co1
ON rel.RDB$Constraint_Name = co1.RDB$Constraint_Name
JOIN RDB$Indices idx
ON rel.RDB$Constraint_Name = idx.RDB$Index_Name
JOIN RDB$Relation_Constraints co2
ON rel.RDB$Const_Name_UQ = co2.RDB$Constraint_Name
JOIN RDB$Index_Segments fl1
ON rel.RDB$Constraint_Name = fl1.RDB$Index_Name
JOIN RDB$Index_Segments fl2
ON rel.RDB$Const_Name_UQ = fl2.RDB$Index_Name
WHERE (NOT rel.RDB$Constraint_Name LIKE 'RDB$')
ORDER BY rel.RDB$Constraint_Name
/* Liste der Indizes
die Zugehörigkeit zu den Tabellen und die Bedeutung
ergibt sich aus dem Namen des Index:
PK = Primary Key
FK = Foreign Key
MI usw. = Foreign Key auf die Tabelle mi = Mitarbeiter
Unter anderen Bedingungen braucht man geeignete JOINs. */
select * from RDB$Index_Segments
where rdb$index_name not starting with 'RDB$'
order by rdb$index_name, rdb$field_position
MS-SQL Server
-- listet die Tabellen einer Datenbank auf
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- listet die Views einer Datenbank auf
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
-- listet die Spaltennamen mit den dazugehörigen Datentypen einer Tabelle auf
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Tabellenname'
Oracle
In Oracle gibt es fast alle Dictionary-Views in dreifacher Ausführung:
- Views mit dem Präfix USER_ zeigen die eigenen Objekte an, also die Objekte, die im eigenen Schema erstellt sind.
- Views mit dem Präfix ALL_ zeigen alle Objekte an, für die man eine Zugriffsberechtigung hat. Das sind die Objekte im eigenen Schema und auch Objekte in anderen Schemata, für die man durch den GRANT-Befehl eine Zugriffsberechtigung erhalten hat.
- Auf Views mit dem Präfix DBA_ kann man nur zugreifen, wenn man das Administrations-Recht hat. In dieser View werden alle Objekte der gesamten Datenbank angezeigt, also auch die, auf die man keine Zugriffsrechte besitzt.
Alle Oracle Dictionary-Views sind im Manual Reference (nicht: SQL-Reference) beschrieben.
Beispiele:
-- listet alle eigenen Tabellen einer Datenbank auf.
SELECT TABLE_NAME FROM USER_TABLES
-- listet alle Tabellen auf, auf die man zugriffsberechtigt ist.
SELECT TABLE_NAME FROM ALL_TABLES
-- listet alle Tabellen auf, die es in der gesamten Datenbank gibt.
SELECT TABLE_NAME FROM DBA_TABLES
-- listet die Views auf
SELECT VIEW_NAME FROM USER_VIEWS
-- listet die Indizes auf. In der Spalte UNIQUENESS ist angegeben,
-- ob es sich um einen eindeutigen Index handelt (UNIQUE)
-- oder ob die Index-Werte in der Tabelle mehrmals vorkommen dürfen (NONUNIQUE)
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS FROM USER_INDEXES
-- listet die Spaltennamen mit den dazugehörigen Datentypen einer Tabelle auf
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,
DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'Tabellenname'
ORDER BY COLUMN_ID
-- Liste aller Fremdschlüssel-Beziehungen und anderen Constraints
-- Fremdschlüssel-Beziehungen haben den Typ 'R'
-- Bei DELETE_RULE = 'CASCADE' handelt es sich um eine Beziehung mit Löschweitergabe
-- bei 'NO ACTION' um eine Beziehung mit Lösch-Restriktion.
SELECT CONSTRAINT_NAME, TABLE_NAME, R_CONSTRAINT_NAME REFERENCED_TABLE, DELETE_RULE
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'
Es gibt noch eine weitere Gruppe von Dictionary-Views. Diese geben über den Zustand der Datenbank Auskunft. Diese Views haben den Präfix V$; sie sind hauptsächlich für die Administration wichtig.
Beispiele:
-- Anzeigen aller Sessions, die gerade aktiv sind oder zuletzt aktiv waren
SELECT * FROM V$SESSION
-- Anzeigen von Informationen über die aktuelle Datenbank-Instanz.
-- Datenbank-Name, auf welchem Server die Datenbank läuft, Oracle-Version,
-- seit wann die Datenbank aktiv ist, ob aktuell Logins möglich sind
-- uns in welchem Status sich die Datenbank befindet.
SELECT INSTANCE_NAME, HOST_NAME, VERSION, STARTUP_TIME, LOGINS, DATABASE_STATUS
FROM V$INSTANCE
Weitere Erläuterungen dazu stehen unter Oracle: Table; dort gibt es auch eine Anleitung, um die Einfügereihenfolge zu ermitteln.
Siehe auch
Weitere Erläuterungen sind in den folgenden Kapiteln zu finden:
| Änderung der Datenbankstruktur |
In diesem Kapitel werden wir die Erkenntnisse aus den vorhergehenden Kapiteln benutzen, um die Beispieldatenbank zu ergänzen und zu erweitern, wie es dort in den Anmerkungen angesprochen wurde.
Spalten hinzufügen und ändern
Dazu wird der Befehl ALTER TABLE ... ADD/ALTER verwendet.
Die hier besprochenen Änderungen sowie weitere Angaben zu Spalten sind im Skript-Spalten[1] zusammengefasst.
Neue Spalten einfügen
Die Tabelle Versicherungsvertrag benötigt mehrere zusätzliche Spalten wegen Prämienberechnung und Schadenfreiheitsrabatt.
alter table Versicherungsvertrag
add [column] Basispraemie DECIMAL
default 500 not null
CONSTRAINT Vertrag_Basispraemie_Check check(Basispraemie > 0),
add [column] Praemiensatz INTEGER
default 100 not NULL
CONSTRAINT Vertrag_Praemiensatz_Check check(Praemiensatz > 0),
add [column] Praemienaenderung DATE;
Die CHECK-Prüfungen werden als CONSTRAINT mit Namen festgelegt. Ob das Wort COLUMN benötigt wird oder weggelassen werden kann oder muss, hängt (wie mehrfach gesagt) vom DBMS ab.
Der Vorgabewert wird bei den einzelnen Verträgen noch an die Versicherungsart angepasst, siehe später unter Weitere Anpassungen, ebenso Prämiensatz und Prämienänderung auf einen einheitlichen Stand zum 1. Januar 2007. Danach werden sie durch eine Stored Procedure nach und nach auf den aktuellen Stand gebracht – siehe das Beispiel für eine Prozedur „automatisches UPDATE gemäß Bedingungen“.
Normalerweise sollte der Vorgabewert automatisch überall eingetragen werden. Falls das nicht geschieht, genügt ein einziger UPDATE-Befehl dafür.
Datentyp einer Spalte ändern
Bei dieser Aufgabe muss unbedingt darauf geachtet werden, dass die bisherigen Daten zum neuen Datentyp passen, d. h. dass sie automatisch (implizit) konvertiert werden können.
In der Tabelle Abteilung ist die Spalte Kuerzel als VARCHAR(10) festgelegt. Tatsächlich sind die Kürzel maximal vier Zeichen lang. Deshalb soll die Definition an die Realität angepasst werden.
alter table Abteilung
alter column Kuerzel TYPE CHAR(4);
Bei MySql wird dieser Befehl ausgeführt; Firebird erkennt die Möglichkeit der impliziten Konvertierung durch Abschneiden zu langer Werte nicht. (Zu lange Werte gibt es sowieso nur in der Theorie, aber nicht wirklich.) Dann sind, wie schon bei den DDL-Einzelheiten für ALTER COLUMN erwähnt, mehrere Einzelmaßnahmen mit einer temporären Spalte erforderlich:
/* Erzeugen Sie eine neue, temporäre Spalte. */
alter table Abteilung add [column] TEMP VARCHAR(10);
/* Kopieren Sie alle Inhalte aus der „alten“ Spalte in die temporäre Spalte. */
update Abteilung set temp = Kuerzel;
/* Löschen Sie die „alte“ Spalte. */
alter table Abteilung drop [column] Kuerzel;
/* Erzeugen Sie eine neue Spalte unter dem „alten“ Namen mit den „neuen“ Eigenschaften. */
alter table Abteilung add [column] Kuerzel CHAR(4) not null;
/* Kopieren Sie alle Inhalte aus der temporären Spalte in die neue Spalte,
wobei sie passend konvertiert werden müssen. */
update Abteilung set Kuerzel = SUBSTRING(temp from 1 for 4);
/* Löschen Sie die temporäre Spalte. */
alter table Abteilung drop [column] temp;
Vorgabewert hinzufügen
Für diese Aufgabe gibt es mehrere Varianten; wir beschränken uns auf eine.
In der Tabelle Mitarbeiter soll die Spalte Ist_Leiter in der Regel den Wert 'N' erhalten.
ALTER TABLE Abteilung MODIFY Ist_Leiter CHAR(1) NOT NULL DEFAULT 'N';
Bei Firebird beispielsweise kann ein Default-Wert nicht einfach hinzugefügt werden; dann ist der „Umweg“ mit einer temporären Spalte nötig.
Die gleiche Änderung wäre auch denkbar in der Tabelle Versicherungsnehmer für die Spalte Eigener_Kunde. Da aber nicht abgeschätzt werden kann, welcher Fall „wesentlich häufiger“ vorkommt, lohnt sich eine solche Einschränkung nicht.
Einschränkungen auf Spalten
Dazu wird der Befehl ALTER TABLE – ADD CONSTRAINT verwendet.
Die hier besprochenen Änderungen sowie weitere Einschränkungen sind im Skript-Constraints zusammengefasst.
Eindeutigkeit festlegen
Bei mehreren Tabellen gibt es Spalten, deren Werte eindeutig sein sollen. Dies wird durch eine UNIQUE-Einschränkung geregelt.
In der Tabelle Mitarbeiter ist die Personalnummer nicht als Primary Key vorgesehen, muss aber dennoch eindeutig sein.
ALTER TABLE Mitarbeiter
ADD CONSTRAINT Mitarbeiter_Nummer UNIQUE (Personalnummer)
USING INDEX Mitarbeiter_Nummer_UK;
Wir benutzen dafür einen CONSTRAINT mit Namen und legen außerdem fest, dass ein bestimmter Index (ebenfalls mit Namen) benutzt werden soll.
CHECK-Bedingungen für eine Spalte
Bei verschiedenen Spalten in mehreren Tabellen sind nur bestimmte Werte zulässig. Das wurde oben unter Neue Spalten einfügen bereits berücksichtigt, gilt aber auch für einige schon vorhandene Spalten.
In der Tabelle Versicherungsvertrag gibt es für die Spalte Art nur eine begrenzte Anzahl von Werten.
ALTER TABLE Versicherungsvertrag
ADD CONSTRAINT Versicherungsvertrag_Art
CHECK( Art = 'HP' OR Art = 'TK' OR Art = 'VK' );
In gleicher Weise ist es vor allem bei den Spalten zu regeln, die als Ersatz für ein boolesches Feld nur die Werte 'J' und 'N' akzeptieren.
CHECK-Bedingungen für mehrere Spalten
Die bisherigen Prüfungen beziehen sich immer auf eine einzige Spalte. Genauso kann auch geprüft werden, ob die Werte mehrerer Spalten zusammenpassen. Bitte beachten Sie, dass für das folgende Beispiel die Spalte Geschlecht benutzt wird, die oben durch das Skript-Spalten eingefügt wird.
In der Tabelle Versicherungsnehmer muss es sich entweder um eine Firma handeln, d. h. Geschlecht IS NULL, oder eine Reihe anderer Spalten benötigt für Personen geeignete Werte.
alter table Versicherungsnehmer
add CONSTRAINT Versicherungsnehmer_Person
CHECK( ( Geschlecht IS NULL )
or ( Vorname is not NULL
and Geburtsdatum is not null
and Fuehrerschein is not null
and Fuehrerschein >= Geburtsdatum + 365*16 )
);
Gleichzeitig wird geprüft, dass der Versicherungsnehmer den Führerschein frühestens im Alter von 16 Jahren (vereinfachte Berechnung, soweit möglich) erhalten haben kann.
Indizes
Dazu wird der Befehl CREATE INDEX verwendet.
Die hier besprochenen Indizes sowie weitere Suchschlüssel sind im Skript-Indizes zusammengefasst.
In der Tabelle Versicherungsvertrag ist häufig nach dem Fahrzeug, also nach Fahrzeug_ID zu suchen.
CREATE INDEX Versicherungsvertrag_FZ
ON Versicherungsvertrag (Fahrzeug_ID);
In der Tabelle Schadensfall ist häufig nach dem Datum zu suchen; jüngere Schadensfälle sollten zuerst kommen.
CREATE DESC INDEX Schadensfall_Datum
ON Schadensfall (Datum);
Für diesen Zweck benötigen wir also mit DESC einen absteigenden Index.
In der Tabelle Versicherungsnehmer ist häufig nach dem Namen – mit oder ohne Vorname – zu suchen.
CREATE INDEX Versicherungsnehmer_Name
ON Versicherungsnehmer (Name, Vorname);
Sie sehen, dass ein Schlüssel auch mehrere Spalten benutzen kann. Die DBMS arbeiten unterschiedlich, ob daraus für Name ein eigener Index wird oder ob es nur den „einheitlichen, gemeinsamen“ Index gibt.
In der Tabelle Versicherungsnehmer ist oft auch nach der Versicherungsgesellschaft zu suchen.
CREATE INDEX Versicherungsnehmer_Ges
ON Versicherungsnehmer (Versicherungsgesellschaft_ID);
Es ist nicht sicher, dass ein solcher Index eingerichtet werden kann; denn diese Spalte kann auch NULL-Werte enthalten (nämlich für die „Eigenen Kunden“), und dabei verhalten sich die DBMS unterschiedlich.
Fremdschlüssel
Dazu wird der Befehl ALTER TABLE – ADD CONSTRAINT – FOREIGN KEY verwendet. Alle Einzelheiten dazu wurden bei den Fremdschlüssel-Beziehungen behandelt.
Die Fremdschlüssel, die für die Beispieldatenbank vorzusehen sind, sind im Skript-ForeignKeys zusammengefasst.
Weitere Anpassungen
Durch die nachträglichen Änderungen in den vorstehenden Abschnitten müssen die bisherigen Daten angepasst werden. Das wollen wir jetzt erledigen.
Die hier besprochenen Anpassungen sind im Skript-Anpassen zusammengefasst. Dort ist eine der Versionen mit allen Werten enthalten.
Geschlecht ändern
In den Tabellen Mitarbeiter und Versicherungsnehmer wurde als Standardwert 'W' (= weiblich) eingetragen. Wir müssen also noch die Männer markieren; das geht durch manuelle Änderung jeder einzelnen Zeile, durch Bezug auf männliche Vornamen oder mit einer Liste der IDs von Männern. Im folgenden Code werden alle drei Varianten notiert; sinnvoll ist es aber, nur eine davon zu benutzen.
update Mitarbeiter
set Geschlecht = 'M'
where ID = 1
or Vorname in ('Kurt', 'Walter', 'Michael')
or ID in (10, 11, 12);
Die gleiche Änderung ist in der Tabelle Versicherungsnehmer nötig. Mangels Vorgabewert setzen wir vorher alle Kunden, die keine Firma sind, auf 'W'.
update Versicherungsnehmer
set Geschlecht = 'W'
where Vorname is not null;
AusgabeOperation violates CHECK constraint on view or table.
Operation violates CHECK constraint VERSICHERUNGSNEHMER_PERSON
on view or table VERSICHERUNGSNEHMER.
Ach, was ist da denn wieder passiert? Bei der Firma mit ID=1 steht der Vorname als leere Zeichenkette, aber der oben eingerichtete CHECK mit der Prüfung „Ist Person“ vergleicht den Vornamen mit dem NULL-Wert. Also muss entweder der konkrete Wert auf NULL gesetzt werden, oder der CHECK muss geändert werden. Bei einer „echten“ Datenbank wäre der zweite Weg unbedingt vorzuziehen; wir machen es uns hier einfacher:
update Versicherungsnehmer
set Vorname = null
where Vorname = '';
Danach funktioniert der obige Befehl; wir müssten also wie folgt vorgehen:
- CHECK für „Ist Person“ löschen.
- CHECK für „Ist Person“ neu registrieren, sodass auch eine leere Zeichenkette für den Vornamen bei einer Firma zulässig ist.
- Geschlecht auf den Vorgabewert 'W' setzen.
- alle männlichen Personen auf 'M' ändern wie im ersten Beispiel.
Vorgabewert der Basisprämie
In der Tabelle Versicherungsvertrag wurde dieser Wert allgemein auf 500 (Euro pro Jahr) gesetzt. Damit wir später richtig damit arbeiten können, muss er natürlich von der Vertragsart abhängen. (Wir unterscheiden dabei nicht nach Fahrzeugtypen und ignorieren Prämienänderungen im Laufe der Jahre, sondern tun so, als ob sich Versicherungsprämien niemals erhöhen würden.) Da der Vorgabewert sowieso auf 500 gesetzt wurde, brauchen nur die davon abweichenden Werte geändert zu werden.
Fehlerupdate Versicherungsvertrag
set Basispraemie = CASE Art
WHEN 'TK' THEN 550
WHEN 'VK' THEN 800
END ;
AusgabeThe update failed because a column definition includes validation constraints.
validation error for column BASISPRAEMIE, value "*** null ***".
Natürlich, man muss aufpassen. Bei den „Nützlichen Erweiterungen“ hieß es zur CASE-Anweisung: „wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen.“ Der bisherige Vorgabewert darf also nicht weggelassen werden:
update Versicherungsvertrag
set Basispraemie = CASE Art
WHEN 'TK' THEN 550
WHEN 'VK' THEN 800
ELSE 500
END ;
Prämiensatz und Prämienänderung vorbereiten
Da sich unser Datenbestand nicht in der Wirklichkeit entwickelt hat, müssen wir diese Entwicklung simulieren. Die ersten Schadensfälle sind 2007 registriert; also muss der Datenbestand auf das Jahresende 2006 gebracht werden. Dazu benutzen wir eine Routine ohne feste Speicherung (siehe SQL-Programmierung).
EXECUTE BLOCK
AS
DECLARE VARIABLE jj INTEGER;
DECLARE VARIABLE T1 INTEGER;
DECLARE VARIABLE T2 INTEGER;
BEGIN
for SELECT vv.ID, EXTRACT(YEAR from Abschlussdatum), Praemiensatz
from Versicherungsvertrag vv
join Versicherungsnehmer vn on vn.Id = vv.Versicherungsnehmer_Id
where Praemienaenderung <= '31.12.2006'
and vn.Eigener_kunde = 'J'
into :T1, :jj, :T2
DO BEGIN
update Versicherungsvertrag
set Praemienaenderung = DATEADD( YEAR, 2006 - :jj, Abschlussdatum ),
Praemiensatz = CASE :T2
WHEN 200 then CASE
WHEN :jj <= 2000 THEN 80
ELSE 200 - (2006 - :jj)*20
END
WHEN 100 then CASE
WHEN :jj <= 2000 THEN 30
ELSE 100 - (2006 - :jj)*10
END
ELSE :T2
END
where ID = :T1;
END
END
Diese Berechnung kann nur für eigene Kunden ausgeführt werden. Deshalb benötigen wir die Verknüpfung mit der Tabelle Versicherungsnehmer und können es nicht mit einem einzigen UPDATE-Befehl erledigen.
Der Prämiensatz 2006 errechnet sich aus der Dauer des Vertrags, also nach dem Abschlussdatum sowie natürlich dem bisher notierten pauschalen Prämiensatz. Im UPDATE-Befehl werden für jeden Vertrag – notiert in der Variablen T1 – die neuen Werte berechnet:
- Das Datum Praemienaenderung wird vom Abschlussdatum aus auf das Jahr 2006 weitergesetzt.
- Der Prämiensatz wird neu berechnet, und zwar:
- für neuere Verträge um je 20 Punkte pro Jahr bis zu einem Minimum von 80
- für andere Verträge um je 10 Punkte pro Jahr bis zu einem Minimum von 30
- Werte, die von den Anfangssätzen 100 oder 200 abweichen, bleiben stehen
Zusammenfassung
In diesem Kapitel wurden alle Änderungen, die sich für die Beispieldatenbank als sinnvoll erwiesen, besprochen. Für jede Situation wurden Beispiele angegeben; alle erforderlichen Befehle sind in Skripten zusammengefasst:
- Skript-Spalten zum Hinzufügen und Ändern einzelner Spalten
- Skript-Constraints zum Anlegen von UNIQUE KEYs und CHECK-Constraints
- Skript-Indizes zum Anlegen weiterer Schlüssel
- Skript-ForeignKeys zum Anlegen der Fremdschlüssel
- Skript-Anpassung zum Anpassen der betroffenen Datensätze
Weitere Hinweise dazu gibt es im Kapitel Downloads.
Siehe auch
Die hier verwendeten Verfahren werden in den folgenden Kapiteln behandelt:
- Anmerkungen zur Beispieldatenbank
- Beispiel einer StoredProcedure: automatisches UPDATE gemäß Bedingungen
- DDL - Einzelheiten: ALTER COLUMN
- Fremdschlüssel-Beziehungen
- Nützliche Erweiterungen
- SQL-Programmierung: Routine ohne feste Speicherung.
Was ist ein Skript?
Unter einem Skript versteht man in der EDV eine Liste von Befehlen, die durch einen einzelnen Befehl aufgerufen und automatisch nacheinander ausgeführt werden. Diese Befehle sind meist in einer Datei zusammengefasst und werden dadurch ausgeführt, dass die betreffende Datei aufgerufen wird.
Bei SQL sind solche Dateien unter Windows üblicherweise mit der Endung ".sql" gespeichert.
Hinweis
- ↑ Erläuterungen zu diesem und den anderen Skripts stehen am Ende dieses Kapitels.
| Testdaten erzeugen |
In diesem Kapitel werden wir die Erkenntnisse aus den vorhergehenden Kapiteln benutzen, um die Beispieldatenbank um eine Vielzahl zusätzlicher Datensätze zu ergänzen.
Auch hier gilt: Wegen der vielen Varianten bei den DBMS beschränkt sich dieses Kapitel bei den Hinweisen und Beispielen auf Firebird. Zusammen mit den speziellen Hinweisen zur SQL-Programmierung sollten sie problemlos an andere DBMS-Regeln angepasst werden können.
Allgemeine Hinweise
Damit man in einer Datenbank viele Tests ausführen kann, sind umfangreiche, möglichst unterschiedliche Testdaten nötig. Dies wollen wir jetzt ansatzweise besprechen und dabei die bisherigen Erkenntnisse aus den vorigen Kapiteln benutzen.
Folgende Grundgedanken bei solchen Testdaten sind immer zu beachten:
- Die einzelnen Datensätze sollen zufällig erzeugte Werte erhalten.
- Die Werte in den Spalten sollen halbwegs realistisch sein: Ein Vorname wie 'Blabla' oder eine Schadenshöhe wie 0,15 € verbieten sich.
- Verschiedene Spalten müssen zusammenpassen: Zur PLZ 50667 darf es nur den Ortsnamen 'Köln' geben.
- Am besten sollte auch die Verteilung der Datensätze halbwegs realistisch sein: Es sollte mindestens soviele Fahrzeuge mit dem Kfz-Kennzeichen 'D' (Stadt Düsseldorf) wie mit 'RE' (Stadt- und Landkreis Recklinghausen) geben; ähnlich sollten auch die Versicherungsnehmer auf Stadt- und Landkreis passend verteilt sein.
|
|
Hinweis: Die hier genannten Schritte sind nur ein denkbares Verfahren. Wichtig ist, dass Sie einen Einblick in mögliche Vorgehensweisen und Probleme bekommen. |
|---|
Neue Fahrzeuge registrieren
Für viele neue Datensätze in der Tabelle Fahrzeug lernten wir bereits zwei Varianten des gleichen Verfahrens kennen:
- Bei den Prozeduren werden mit einer WHILE-Schleife mehrere zufällige Werte für neue Zeilen zusammengesetzt.
- Dies geht auch als Routine ohne feste Speicherung.
Beide Varianten sind nur sinnvoll, wenn eine geringe Anzahl von Spalten mit einer geringen Anzahl möglicher Werte „vervielfältigt“ werden.
Neue Versicherungsverträge registrieren
Die Prozedur (siehe dort) Insert_Versicherungsvertrag ist für einen solchen automatischen Vorgang nicht geeignet, weil sie immer wieder mit neuen Werten aufgerufen werden müsste. In diesem Kapitel sollen deshalb neue Datensätze durch das „kartesische Produkt“ zusammengestellt werden. Damit die erforderlichen Beziehungen gesichert sind, müssen zuerst Fahrzeuge und Versicherungsnehmer erstellt werden; erst zuletzt dürfen die Verträge registriert werden.
Die folgenden Maßnahmen sind im Skript-Testdaten der Download-Seite zusammengefasst. Dort stehen auch diejenigen (einfacheren) SQL-Befehle, die im Folgenden nicht ausführlich angegeben und besprochen werden. Der Vermerk ➤ Skript verweist bei den folgenden Schritten auf die Befehle, die in diesem Skript stehen.
Die Tabelle Fahrzeug
Eine Menge von Fahrzeugen wurde bereits durch die Prozedur Insert_Into_Fahrzeug gespeichert. Wir prüfen zunächst, wie viele Fahrzeuge (noch) nicht zu einem Vertrag gehören:
select COUNT(ID) from Fahrzeug
where ID not in (Select Fahrzeug_ID from Versicherungsvertrag);
AusgabeCOUNT : 120
Wenn das noch nicht genügt, speichern Sie jetzt mit der o. g. Prozedur weitere Datensätze. Auf diese Daten greifen wir in den nächsten Schritten zurück, damit alle Einträge zusammenpassen.
Bei den späteren Maßnahmen gibt es Anmerkungen dazu, wie viele Datensätze vorkommen können. Die Zahl der Fahrzeuge sollte dem ungefähr entsprechen.
Hilfstabellen
Für die zufällige Verknüpfung erstellen wir uns zunächst einige Tabellen mit passenden Werten:
- TempName mit einer Liste von 20 Nachnamen, TempVorname mit einer Liste von 20 Vornamen und Geschlecht ➤ Skript
- TempPLZOrt mit einer Liste von etwa 30 Postleitzahlen und zugehörigen Ortsnamen ➤ Skript
- TempStrasse mit einer Liste von etwa 20 Straßennamen (eigentlich müssten diese zu den Orten passen; aber da es kein „freies“ Straßenverzeichnis für kleinere Städte und Orte gibt, soll uns das nicht interessieren) ➤ Skript
Die Tabelle Versicherungsnehmer
Für eine Menge potenzieller Versicherungsnehmer benutzen wir eine Mischung aus Zufallsfunktionen und der direkten Verknüpfung der Daten aus den Hilfstabellen.
Erzeuge viele Testdaten Versicherungsnehmer durch das kartesische Produkt aus den Hilfstabellen.
insert into Versicherungsnehmer
( Name, Vorname, Geschlecht,
PLZ, Ort,
Strasse,
Hausnummer,
Geburtsdatum, Fuehrerschein, Eigener_Kunde, Versicherungsgesellschaft_ID )
select n.Text, v.Text, v.Geschlecht,
p.Plz, p.Ort,
s.Name,
CAST( CAST( FLOOR( 1 + RAND()* 98) as INTEGER) as VARCHAR(10)),
'01.01.1950', '31.12.2009', 'J', null
from TempName n, TempVorname v, TempPLZOrt p, TempStrasse s
where n.Text <= 'M' and v.Text <= 'P' and s.Name >= 'M';
Diese Anweisung kombiniert jeden Eintrag der beteiligten Tabellen mit jedem anderen Eintrag und erzeugt damit eine „Unmenge“ potenzieller Kunden (es gibt keine Verknüpfung zwischen den vier Hilfstabellen). Dies ist also das „kartesische Produkt“, das als im Normalfall ungeeignet bezeichnet wurde, aber in dieser Spezialsituation nützlich ist.
Geburtsdatum und Fuehrerschein werden nachträgliche geändert. Der SELECT-Befehl wäre zu kompliziert, wenn zunächst das Geburtsdatum mit mehreren Zufallsfunktionen erstellt würde und davon abhängig mit weiteren Zufallszahlen das Datum des Führerscheinerwerbs berechnet würde.
|
|
Achtung: Diese Art der Verknüpfung mehrerer Tabellen kann sehr viele Datensätze erzeugen und belastet deshalb das DBMS für längere Zeit erheblich. |
|---|
Nur deshalb wird die WHERE-Bedingung benutzt. Mit allen Datensätzen der temporären Tabellen entstünden 20*20*47*22 (= 413 600) Zeilen, was bei einem Testlauf etwa 15 Minuten dauerte. Mit der Einschränkung und einer kleineren PLZ/Orte-Tabelle gibt es etwa 12*12*25*13 (= 46 800) Zeilen in 15 Sekunden.
Nun werden zufällige Daten für Geburtsdatum und Fuehrerschein erzeugt und bei den neu erstellten Versicherungsnehmern gespeichert.
UPDATE Versicherungsnehmer
SET Geburtsdatum = DATEADD( DAY, CAST( FLOOR(RAND()*27) AS INTEGER),
DATEADD( MONTH, CAST( FLOOR(RAND()*11) AS INTEGER),
DATEADD( YEAR, CAST( FLOOR(RAND()*40) AS INTEGER),
Geburtsdatum)))
WHERE Geburtsdatum = '01.01.1950';
COMMIT;
UPDATE Versicherungsnehmer
SET Fuehrerschein = DATEADD( DAY, CAST( FLOOR(RAND()*27) AS INTEGER),
DATEADD( MONTH, CAST( FLOOR(RAND()*11) AS INTEGER),
DATEADD( YEAR, CAST( FLOOR(18 + RAND()*(DATEDIFF
(YEAR, Geburtsdatum, CAST('31.12.2008' AS DATE))-18)
) AS INTEGER), Geburtsdatum)))
WHERE Fuehrerschein = '31.12.2009';
COMMIT;
Das Geburtsdatum kann dabei nicht später als der '31.12.1990' liegen; der Führerschein kann frühestens mit dem 18. Geburtstag erworben worden sein.
Die Tabelle Versicherungsvertrag
Um die bisherigen Teildaten zu Verträgen zusammenzufassen, müssen wir anders vorgehen. Folgende Bedingungen sind zu berücksichtigen:
- Jedes Fahrzeug darf nur einmal benutzt werden.
- Ein Versicherungsnehmer darf auch in mehreren Verträgen stehen.
- Aber der Wohnort des Kunden sollte zum Kfz-Kennzeichen passen; wir machen es zur Bedingung.
- Das Abschlussdatum des Vertrags muss nach dem Führerscheinerwerb liegen.
- Prämiensatz und Prämienänderung müssen ebenso vorbereitet werden wie im Kapitel Änderung der Datenbankstruktur.
Ein erster Versuch (zunächst beschränkt auf Fahrzeuge mit 'RE' als Kennzeichen) konnte nicht sinnvoll funktionieren – das DBMS „hängte sich auf“.:
Firebird-Versionselect fz.id, fz.Kennzeichen,
vn.Id, vn.Name, vn.Vorname, vn.PLZ
from Fahrzeug fz
join Versicherungsnehmer vn on vn.ID =
( select FIRST 1 ID
from Versicherungsnehmer
where ID not in ( select Versicherungsnehmer_ID
from Versicherungsvertrag )
and PLZ = ( select FIRST 1 PLZ
from TempPLZOrt
where Kreis = 'RE'
order by RAND()
)
order by RAND()
)
where fz.Kennzeichen STARTS WITH 'RE-';
Dieser Versuch sollte so vorgehen (lesen Sie den Befehl „von innen nach außen“):
- Hole nach Zufallsreihenfolge eine PLZ aus der Liste der Orte, die zum Kreis 'RE' gehören.
- Hole nach Zufallsreihenfolge einen Kunden mit dieser PLZ, sofern er noch nicht mit einem Vertrag registriert ist.
- Nur dessen Daten sollen mit einem einzelnen Fahrzeug verknüpft werden. Wenn man auf das JOIN verzichten würde, würde der erste Versicherungsnehmer mit jedem Fahrzeug verknüpft.
Dieses Verfahren verlangt bei jedem Fahrzeug zwei neue abhängige SELECT-Befehle, die per Zufallsfunktion aus fast 50 000 Datensätzen jeweils genau einen Datensatz liefern sollen – eine völlig unsachgemäße Belastung des DBMS.
Stattdessen wird mit einer weiteren Hilfstabelle TempVertrag schrittweise vorgegangen:
- Schreibe alle noch „freien“ Fahrzeuge aus den oben verwendeten Kreisen in die Hilfstabelle. ➤ Skript
- Sortiere sie nach Kreis und nummeriere sie in dieser Reihenfolge. ➤ Skript
- Hole per Zufallsreihenfolge – getrennt nach jedem Kreis – einen Eintrag aus der Tabelle Versicherungsnehmer.
- Übertrage diese Zusammenstellung in die Tabelle Versicherungsvertrag.
- Passe Prämiensatz und Prämienänderung an (wie im früheren Kapitel ausgeführt).
Der erste, entscheidende Schritt ist die Zuordnung eines potenziellen Kunden zu jedem „freien“ Fahrzeug.
Erzeuge eine zufällige Reihenfolge der Kunden und trage sie in die (temporäre) Liste der Fahrzeuge ein.
EXECUTE BLOCK
as
DECLARE VARIABLE nextid INTEGER = 0;
DECLARE VARIABLE tempid INTEGER;
DECLARE VARIABLE tkreis VARCHAR(3);
DECLARE VARIABLE Tname CHAR(2);
DECLARE VARIABLE minnr INTEGER;
DECLARE VARIABLE maxnr INTEGER;
DECLARE VARIABLE Tdatum DATE;
BEGIN
for select fz_Kreis, Min(Nr), Max(Nr)
from TempVertrag
group by fz_Kreis
order by fz_Kreis
into :tkreis, :Minnr, :Maxnr
DO BEGIN
/* hole alle möglichen potenziellen Kunden für diesen Kreis
in Zufallsreihenfolge */
nextid = :Minnr - 1;
for select ID,
/* diese komplizierte Konstruktion mit TRIM, CAST ist
wegen SUBSTRING nötig */
CAST( TRIM(SUBSTRING(Name from 1 for 1))
Dieser Arbeitsablauf berücksichtigt die o. g. Grundgedanken:
- In der Hauptschleife werden die Fahrzeuge nach dem Kreis gruppiert.
- Für jeden Kreis werden der kleinste und der größte Wert der laufenden Nummer aus der Hilfstabelle notiert.
- Der nächste SELECT bereitet eine Schleife mit potenziellen Kunden vor:
- Zum aktuellen Kreis werden alle Postleitzahlen aus der Hilfstabelle TempPLZOrte registriert.
- Die Einträge der Kunden, die zu diesen PLZ gehören, werden in eine zufällige Reihenfolge gebracht.
- In dieser Schleife wird jeder dieser Kunden bei einem Fahrzeug mit der nächsten laufenden Nummer eingetragen.
- Der Anfangsbuchstabe vom Namen und Ort wird registriert, damit dieser Wert für die Vertragsnummer zur Verfügung steht.
- Zusätzlich wird als Abschlussdatum des Vertrags ein zufälliges Datum zwischen dem Führerscheinerwerb und einem festen Schlussdatum erzeugt.
Jetzt wird für jede dieser Kombinationen von Fahrzeugen und Kunden aus der Hilfstabelle TempVertrag ein neuer Versicherungsvertrag erzeugt:
insert into Versicherungsvertrag
select null, /* ID nach Generator */
Vn_name
Dabei wird jede Spalte in der Tabelle Versicherungsvertrag mit einem Wert versehen, überwiegend direkt aus der Hilfstabelle TempVertrag. Einige Werte werden statt einer weiteren Zufallsfunktion aus der Fahrzeug-ID errechnet.
Probleme mit Testdaten
Bei den vorstehenden Einzelschritten sind wiederholt Probleme aufgetreten. Das gilt natürlich auch für viele andere Versuche. Bitte vergessen Sie deshalb niemals diesen Ratschlag, der fast immer für jeden einzelnen Teilschritt gilt:
|
|
Merke Vor jeder umfangreichen oder wichtigen Änderung ist eine Datensicherung vorzunehmen. |
|---|
Die hier behandelten Verfahren sind beileibe nicht die einzig möglichen. Sie sollten aber sehen, wie zum einen verschiedene konstante Daten kombiniert und zum anderen Zufallswerte erzeugt werden können, um viele unterschiedliche Daten mit sinnvollen Kombinationen in verschiedenen Tabellen einzufügen. In gleicher Weise könnten wir noch viele weitere Datensätze in den Tabellen der Beispieldatenbank erzeugen.
Vor allem weitere Schadensfälle wären äußerst nützlich. Dabei sind aber viele Probleme zu beachten:
- Jeder Schadensfall benötigt eine Schadenssumme, die nicht nur zufällig, sondern auch sinnvoll festgelegt werden sollte.
- Die Beschreibung des Schadensfalls sollte wenigstens annähernd realistisch zur Schadenssumme passen.
- Wir benötigen Schadensfälle mit 1, 2 oder „vielen“ beteiligten Fahrzeugen. Auch deren Anzahl sowie die anteiligen Schadenssummen sollten annähernd realistisch zur Schadenssumme und zur Beschreibung passen.
- Wenn ein beteiligtes Fahrzeug (genauer: der Versicherungsnehmer) nicht zu den eigenen Kunden gehört, muss gleichzeitig ein Versicherungsvertrag (mit Versicherungsnehmer und Fahrzeugdaten) gespeichert werden.
- Zumindest in der Praxis gibt es auch Unfallfahrer ohne Versicherungsschutz. Wie soll man das berücksichtigen?
Eher unproblematisch sind Ergänzungen für die folgenden Tabellen; aber weil es sich in der Regel um kleinere Tabellen handelt, muss man sich darüber auch keine Gedanken machen, sondern kann jederzeit einzelne Datensätze oder kleine Prozeduren erstellen:
- Weitere Abteilungen sind kaum nötig; bei Bedarf macht man einzelne INSERT-Befehle.
- Weitere Mitarbeiter sind nur nötig, wenn die Datenbank auch für andere Arbeitsbereiche benutzt werden soll.
- Ebenso sind weitere Dienstwagen nicht wichtig.
- Viele weitere Fahrzeugtypen und Fahrzeughersteller wären nützlich, und zwar schon vor der Maßnahme mit den vielen neuen Versicherungsverträgen. Dafür sind aber keine zufällig entstehenden Kombinationen sinnvoll; besser ist eine einfache Prozedur: Der neue Typ und sein Hersteller werden im Klartext angegeben; wenn der Hersteller noch nicht existiert, wird er im gleichen Schritt (automatisch) registriert (siehe die Übungen zu den Prozeduren).
Insgesamt gibt es so viele praktische Probleme, die nur sehr schwer in zufällig erzeugten Datensätzen berücksichtigt werden könnten. Wir verzichten deshalb darauf.
|
|
Hinweis: Für Ihre praktische Arbeit sollten Sie solche „Folgeprobleme“ immer beachten. |
|---|
Zusammenfassung
In diesem Kapitel wurden eine Reihe Verfahren besprochen, mit denen automatisch eine Menge zusätzlicher Datensätze erstellt werden können:
- Mit dem kartesischen Produkt können aus Hilfstabellen schnell sehr viele Datensätze erzeugt werden.
- Prozeduren (oder nicht gespeicherte Routinen) sind in anderen Fällen hilfreich.
- In aller Regel sind viele Nebenbedingungen zu berücksichtigen.
Siehe auch
Dieses Kapitel benutzt Erkenntnisse der folgenden Kapitel.
- Einfache Tabellenverknüpfung zum „kartesischen Produkt“
- SQL-Programmierung mit allgemeiner Beschreibung dazu sowie dem Abschnitt „Routinen ohne feste Speicherung“
- Prozeduren mit den Abschnitten „Testdaten in einer Tabelle erzeugen“, „INSERT in mehrere Tabellen“ und den Übungen
- Änderung der Datenbankstruktur mit dem Abschnitt „Prämiensatz und Prämienänderung vorbereiten“
Das Skript-Testdaten – siehe Downloads – enthält alle Befehle, die für die Erzeugung der Testdaten benutzt werden.
| Lizenz |
Dieser Text ist sowohl unter der „Creative Commons Attribution/Share-Alike“-Lizenz 3.0 als auch GFDL lizenziert.
Eine deutschsprachige Beschreibung für Autoren und Weiternutzer findet man in den Nutzungsbedingungen der Wikimedia Foundation.