Views in SQL

Views in SQL
Holger Jakobs – [email protected], [email protected]
2010-07-15
Inhaltsverzeichnis
1 Wozu dienen Views?
1
2 Anlegen und Verwenden von Views
2
3 Schreibfähigkeit von Views
3.1 Views schreibfähig machen . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2 nicht-schreibfähige Views . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3
3
4
4 Löschen von Views
6
5 Übungen zu Views für die Krankenhaus-Datenbank
6
Views bedeutet Blick“ oder Sicht“, Ansicht“. Es sind virtuelle Tabellen in einer Daten”
”
”
bank. Sie enthalten nur eine bestimmte Sicht auf die Daten. Das heißt, dass sie keine Daten
enthalten, aber trotzdem prinzipiell genauso wie reale Tabellen benutzt werden können. Die
Daten, die man bei einem SELECT auf einen View erhält, werden genauso wie die einer
realen Tabelle angezeigt bzw. dem anfragenden Programm übermittelt.
1 Wozu dienen Views?
Views werden vom Datenbankverwalter für bestimmte Benutzer oder Benutzergruppen
angelegt, die eine Sicht auf die Daten benötigen, die nicht mit den tatsächlich vorhandenen
Tabellen übereinstimmt. Ein View kann Daten aus einer oder auch aus mehreren Tabellen
enthalten. Die Rechte an einem View können sich von denen an den Tabellen, auf denen
er basiert, unterscheiden.
Mit Hilfe eines Views kann man einem Benutzer nur Zugriff auf bestimmte Attribute einer
Tabelle geben oder auch nur auf bestimmte Tupel. Ein View kann auch Daten enthalten“,
”
die nicht direkt in der Datenbank enthalten sind, sondern erst (über Aggregatfunktionen
oder arithmetische Operationen) ermittelt werden müssen.
Die Menge der Tabellen und Views, die ein Benutzer sehen und verwenden kann, bilden
das externe Schema für diesen Benutzer. Views sind also ein wesentliches Gestaltungsmittel für die externen Schemata.
1
2 ANLEGEN UND VERWENDEN VON VIEWS
2 Anlegen und Verwenden von Views
Ein View wird wie eine reale Tabelle erzeugt. Allerdings definiert man keine Attribute,
sondern gibt stattdessen ein SELECT-Statement an, mit dessen Hilfe die gewünschte Sicht
erzeugt wird. Das SELECT-Statement kann alle Kommandos enthalten, einschließlich Subqueries. Die Attributnamen im View können sich von den zugeordneten Attributen der
realen Tabellen unterscheiden. Syntax:
CREATE VIEW viewname
AS SELECT ...;
[(columnname1, columnname2, ...)]
Die columnnames müssen angegeben werden, wenn im SELECT Berechnungen verwendet
werden, weil diese sonst keinen Namen hätten; ansonsten sind sie wahlfrei. Werden columnnames verwendet, so muss ihre Anzahl mit der Anzahl der Spalten im SELECT übereinstimmen. Beispiele:
CREATE VIEW DUSSPIELER AS SELECT * FROM SPIELER
WHERE ORT = 'Duesseldorf';
CREATE VIEW BESTRAFTE (NR, NAME, VORNAME, STRAFEN)
AS SELECT SP.SPIELNR, SPIELNAME, VORNAME, SUM (BETRAG)
FROM SPIELER SP, STRAFEN ST
WHERE SP.SPIELNR = ST.SPIELNR
GROUP BY SP.SPIELNR, SPIELNAME, VORNAME;
Das zweite Beispiel zeigt, dass man eine völlig neue Sicht auf die Daten generieren kann.
Das Abfragen der Spieler mit dem Gesamtbetrag ihrer Strafen erfordert ohne View immer
wieder das Nachvollziehen der doch recht komplexen Abfrage. Mit dem View ist es nun
ganz leicht: SELECT * FROM BESTRAFTE;
Beim Abfragen muss es dem Anwender nicht bewusst sein, dass er nur einen View und
nicht etwa eine reale Tabelle anspricht. Aus Anwendersicht sind die beiden gleichwertig.
Die Rechte an Views können allerdings eingeschränkt sein – entweder durch den Datenbankverwalter oder durch technische Gegebenheiten.
Ein View kann nur dann zum Schreiben benutzt werden, wenn er sich nur auf eine einzige
Tabelle bezieht und alle Spalten enthält, die die NOT NULL-Klausel, aber keinen DefaultWert haben. Beim Eintragen eines neuen Tupels in einen View werden alle Spalten, die im
View nicht enthalten sind, mit Default-Werten oder NULL-Werten belegt. Eine eventuelle
WHERE-Klausel in einem View wird beim Einfügen von Daten nicht berücksichtigt. Wer
Schreibrecht auf obigen View DUSSPIELER hat, kann jeden beliebigen Spieler eintragen, auch
wenn dieser nicht in Düsseldorf wohnt. Abfragen kann er die von ihm selbst eingegebenen
Daten allerdings nur, wenn sie der WHERE-Klausel genügen. Das Einfügen von Tupeln über
einen View, die der View-Bedingung nicht genügen (oder auch das Ändern von Tupeln,
so dass sie gegen die Bedingung verstoßen), kann man ausschließen, wenn man bei der
Erzeugung des Views die Klausel WITH CHECK OPTION angibt.
2
3 SCHREIBFÄHIGKEIT VON VIEWS
3 Schreibfähigkeit von Views
Bei Oracle sind Views vollständig und mit realen Tabellen gleichberechtigt integriert. Bei
PostgreSQL dienen Views lediglich zum Abfragen von Daten, d. h. dort sind View niemals direkt schreibfähig. Das liegt daran, dass Views über Regeln realisiert werden, die
eine Abfrage entsprechend umschreiben, was beim Einfügen und Aktualisieren leider nicht
unmittelbar funktioniert.
3.1 Views schreibfähig machen bei PostgreSQL
Allerdings kann man über eine entsprechende Regel Schreibvorgänge auf die Ursprungstabelle umlenken. Regeln, die Einfügeoperationen auf die reale Tabelle umlenken, sehen
prinziell so aus:
CREATE RULE Beispiel_View_INS AS
ON INSERT TO Beispiel_View
DO INSTEAD
INSERT INTO echte_Tabelle
VALUES (NEW.spalte1, NEW.spalte2);
CREATE RULE Beispiel_View_UPD AS
ON UPDATE TO Beispiel_View
DO INSTEAD
UPDATE echte_Tabelle
SET spalte1 = NEW.spalte1, spalte2 = NEW.spalte2
WHERE spalte1 = OLD.spalte1; -- Prüfung auf Primärschlüssel
CREATE RULE Beispiel_View_DEL AS
ON DELETE TO Beispiel_View
DO INSTEAD
DELETE FROM echte_Tabelle
WHERE spalte1 = OLD.spalte1; -- Prüfung auf Primärschlüssel
Hier wird vorausgesetzt, dass die Tabelle nur 2 Spalten hat und dass die Spalte 1 der
Primärschlüssel ist.
Falls die Tabelle ein auto-increment-Feld1 hat (über eine Sequenz), und man anderen
Benutzern das Recht zum Einfügen nicht direkt an einer Tabelle, sondern nur über eine
Sicht (View) erlaubt hat, ist es notwendig, den Benutzern auch das UPDATE-Recht an der
Sequenz ausdrücklich zu geben:
grant update on sequenzname to benutzer/gruppe
1) siehe Dokument Auto-Increment-Spalten in Datenbanken“ http://www.bg.bib.de/portale/dab/
”
DB-anlegen/auto_increment.pdf
3
3.2 nicht-schreibfähige Views
3 SCHREIBFÄHIGKEIT VON VIEWS
3.2 Views mit Join und/oder Aggregatfunktionen
Sobald Views Join-Operationen oder Aggregatfunktionen enthalten, sind sie in keinem
Datenbanksystem schreibfähig, weil das System nicht wissen kann, wie die Daten in die
Tabellen zu übertragen sind. Bei Join-Operationen müssten die Daten auf mehrere Tabellen aufgeteilt werden; bei Aggregatfunktionen müssten die Daten, die in einem Tupel
eingegeben werden, auf mehrere verteilt werden.
Gelegentlich scheint eine Schreiboperation aber auch auf solche Views wünschenswert
und sinnvoll. Ein schönes Beispiel dafür sind Joins, die über Tabellen gehen, die eine Teilmengen-Beziehung (is-a) haben. Das können Mitarbeiter und Verkaufsrepräsentanten sein.
Die Verkaufsrepräsentanten sind eine Teilmenge der Mitarbeiter, haben aber zwei zusätzliche Attribute, nämlich einen Prozentwert für die Provision und eine Mobiltelefonnummer.
So könnten die Tabellen und die Sicht definiert sein:
CREATE TABLE Mitarbeiter (
mnr
integer
primary key,
name
varchar(30)
not null,
gehalt
integer
not null
);
CREATE TABLE Verkaeufer (
mnr
integer
primary key
REFERENCES Mitarbeiter,
provision integer
not null
CHECK (provision > 1 AND provision < 10),
mobilfon
varchar(15) not null
);
CREATE VIEW Mitarb2 AS
SELECT mnr, name, gehalt, provision, mobilfon
FROM Mitarbeiter NATURAL LEFT JOIN Verkaeufer;
Um auf die Sicht Mitarb2 schreiben zu können, müssen bei allen Datenbanksystemen besondere Vorkehrungen getroffen werden. Diese unterscheiden sich leider syntaktisch etwas.
Bei PostgreSQL heißen sie Rules, bei Oracle dagegen instead-of-Trigger. Da PostgreSQLRules bereits im Abschnitt 3.1 auf der vorherigen Seite erläutert wurden, hier die Oracleinstead-of-Trigger:
CREATE TRIGGER Mitarb2_ins INSTEAD OF INSERT ON Mitarb2
FOR EACH ROW
BEGIN
insert into Mitarbeiter values (:new.mnr, :new.name, :new.gehalt);
insert into Verkaeufer values (:new.mnr, :new.provision,
:new.mobilfon);
4
3 SCHREIBFÄHIGKEIT VON VIEWS
3.2 nicht-schreibfähige Views
END;
CREATE TRIGGER Mitarb2_upd INSTEAD OF UPDATE ON Mitarb2
FOR EACH ROW
BEGIN
UPDATE Verkaeufer SET
mnr
= :new.mnr,
provision = :new.provision,
mobilfon = :new.mobilfon
WHERE mnr=:old.mnr;
UPDATE Mitarbeiter SET
mnr
= :new.mnr,
name
= :new.name,
gehalt
= :new.gehalt
WHERE mnr=:old.mnr;
END;
CREATE TRIGGER Mitarb2_del INSTEAD OF DELETE ON Mitarb2
FOR EACH ROW
BEGIN
DELETE FROM Verkaeufer
WHERE mnr = :old.mnr;
DELETE FROM Mitarbeiter WHERE mnr = :old.mnr;
END;
Besonderheit bei Oracle ist, dass vor dem new und dem old jeweils ein Doppelpunkt geschrieben werden muss, dass der Zusatz FOR EACH ROW notwendig ist, und dass die Anweisungen in BEGIN und END; eingeschlossen werden müssen. Letzteres bringt aber den Vorteil
mit sich, dass ein Trigger mehrere Kommandos ausführen kann. Bei einer Regel in PostgreSQL kann dagegen immer nur ein einziges Kommando enthalten sein. Man bräuchte
da also immer je zwei Regeln für INSERT, UPDATE und DELETE. So könnte es z. B. für das
Löschen aussehen:
CREATE RULE Mitarb2_del_v ON DELETE TO Mitarb2
DO INSTEAD
DELETE FROM Verkaeufer
WHERE mnr = old.mnr;
CREATE RULE Mitarb2_del_m ON DELETE TO Mitarb2
DO INSTEAD
DELETE FROM Mitarbeiter WHERE mnr = old.mnr;
5
5 ÜBUNGEN ZU VIEWS FÜR DIE KRANKENHAUS-DATENBANK
4 Löschen von Views
Views kann man mit dem Kommando drop view viewname wieder löschen – genauso wie
Tabellen, Indexe und andere Objekte.
Die zugehörigen Regeln und Trigger werden analog mit drop rule rulename und drop
trigger triggername gelöscht.
5 Übungen zu Views für die Krankenhaus-Datenbank
1. Vollziehen Sie die im Text gegebenen Beispiele mit eigenen Tabellen komplett nach –
in PostgreSQL und/oder Oracle.
2. Legen Sie einen View namens ANGEST2 an, der nur einen Teil der Tabelle der Angestellten enthält, nämlich alles bis auf die Adresse.
3. Schränken Sie beim View ARZT2 nicht die Anzahl der Spalten ein, sondern geben Sie
nur die Ärzte aus, aber mit vollständigen Angaben, d. h. Angaben aus der Arzt- und
der Angestellten-Tabelle.
4. Ganz knifflig ist eine Zusatzspalte BEHANDELT für den Arzt-View, der als ARZT3 gespeichert werden soll, die angibt, ob der jeweilige Arzt zur Zeit Patienten behandelt
oder nicht (eine Ja/Nein-Spalte). Sortiert werden soll alphabetisch aufsteigend nach
Name.
5. Legen Sie einen View namens ZIMMER2 an, der dieselben Daten enthält wie ZIMMER,
aber zusätzlich über die Attribute ANZAHLFREI und ANZAHLBELEGT verfügt, die die
Anzahl freier und belegter Betten angibt. Die Ausgabe soll absteigend nach Anzahl
freier Betten sortiert sein, d. h. die Zimmer mit den meisten freien Betten zuerst
ausgeben.
6. Machen Sie alle Views nach Möglichkeit schreibfähig, indem Sie die notwendigen
Regeln (PostgreSQL) bzw. Trigger (Oracle) für alle Schreiboperationen (Einfügen,
Ändern, Löschen) einbauen.
7. Vergeben Sie an Views andere Rechte als an den Grundtabellen, z. B. an andere Studierende Ihrer Klasse. Wie fein können Sie nun die Rechte vergeben? Auf bestimmte
Zeilen einer Tabelle? Auf bestimmte Spalten einer Tabelle?
$RCSfile: views.tex,v $ $Date: 2009/03/23 14:15:07 $ $Revision: efcb5b401798 $
6