SQL

1
SQL

• Claus Andersen
• Heiko Weber

Datum 30.06.04 Seminar Programmiersprachenkonzep
te
2
Gliederung

• Einführung in das Relationenmodell
• Einführung in SQL DDL und IQL
• Transaktionen
• Synchronisation
• verteilte Datenbanken
• Aufbau eines Oracle DB Systems
• SQLPlus
• PL/SQL
• Trigger
• Constraints vs. Trigger

3
Relationenmodell (1)

• Das Relationenmodell wurde 1970 von Codd
  eingeführt
• Es ist das am weitesten verbreitete
  Datenbankmodell, das in der Praxis eingesetzt
  wird.
• Einfachheit und Exaktheit des Relationenmodells
  hat weitreichende Ergebnisse in der
  Datenbankforschung ermöglicht.

4
Relationenmodell (2)

• Datenbankschema besteht aus
• einer Menge von Relationenschemata
• Die zu modellierende Anwendungswelt wird durch
  Relationenschemata beschrieben.
• Sie bestehen aus einer Menge von Attributen
• Relationen
• Menge von Tupel mit Attributwerten der Attribute
  der Relationenschemata

5
Schlüssel

• Primärschlüssel
• Minimale Menge von Attributen, deren Werte ein
  Tupel einer Relation eindeutig identifizieren
• Fremdschlüssel
• Attributmenge, die in einer anderen Relation
  (Primär)Schlüssel ist

6
Aufbau von Tabellen
Die Datenbank besteht aus einer Menge von
Relationen, die nach den Relationenschemata
gebildet werden.
7
Beispiel Tabellen
8
Geschichte und Standards von SQL (1)

• 1970 wurde das relationale Datenbankmodell von
  Codd eingeführt
• 1974 wurde vom IBM-Forschungszentrum in San Jose
  eine erste
  Datenbanksprache Sequel (Structured
  English QUEry Language) entwickelt und
  1976 zur Sprache Sequel 2 weiter
  entwickelt
• In den ersten verfügbaren relationalen
  Datenbanksystemen wurde eine
  Untermenge von Sequel 2 implementiert, die
  SQL genannt wurde

9
Geschichte und Standards von SQL (2)

• 1982 bis 86 wurde SQL von der ANSI (American
  National Standards Institute) genormt und
  üblicherweise mit SQL-86 bezeichnet
• darauf folgte die von der ISO genormte Version
  SQL-89
• 1992 erschien die von der ANSI und ISO genormte
  Version SQL-92 (SQL 2)
• SQL 3 war das letzte Normungsprojekt der ANSI
  und ISO und wurde in gewissen Anteilen in
  den Standard SQL-99 überführt

10
Was ist SQL

• SQL (Structured Query Language) ist die
  Norm-Datenbanksprache für relationale
  Datenbanksysteme.
• Teilsprachen von SQL
• DDL (Data Definition Language)
• SSL (Storage Structure Language)
• IQL (Interactive Query Language)
• DML (Data Manipulation Language)

11
SQL-DDL

• Was ist SQL-DDL
• ist eine Datendefinitionssprache zur
  Umsetzung des Datenbankschemas
• ist Teil der Standardsprache für relationale
  Datenbanksysteme (SQL)

12
Anforderungen an SQL-DDL

• SQL-DDL sollte mindestens folgende Bestandteile
  definieren können
• Attribute
• Wertebereiche
• Relationenschemata
• Primärschlüssel
• Fremdschlüssel

13
Konzepte für SQL-DDL(1)

• create table, alter table und drop table
• Definition von Relationenschemata
• create table basisrelationenname
• (spaltenname_1 wertebereich_1,
• ...
• spaltenname_k werteberich_k)
• Mit alter table kann man die angelegten
  Relationenschemata
• ändern und mit drop table können
  Relationenschemata aus der
• Datenbank entfernt werden.
• create view, drop view ähnlich

14
Konzepte für SQL-DDL(2)

• create domain, alter domain und drop domain
• Definition von benutzerdefinierten Wertebereichen
• create domain domainname Datentyp default
  Datenwert

15
SQL als Anfragesprache (1)

• Der SFW-Block
• Die select-Klausel
• gibt die Projektionsliste an
• integriert auch arithmetische Operationen und
  Aggregatfunktionen

16
SQL als Anfragesprache (2)

• Die from-Klausel
• spezifiziert zu verwendende Relationen
• führt eventuelle Umbenennungen durch
• verwendetete Relationen werden mittels eines
  kartesischen Produkts verknüpft

17
SQL als Anfragesprache (3)

• Die where-Klausel
• spezifiziert Selektionsbedingungen
• Verbundbedingungen, um aus dem kartesischen
  Produkt einen Gleichverbund zu machen
• Geschachtelte Anfragen sind in der where-Klausel
  erlaubt

18
SQL als Anfragesprache (4)

• Beispiel
• SELECT S.Name
• FROM Schauspieler S, Darsteller D
• WHERE S.PNR D.PNR

19
Einige Erweiterungen von SQL-92 gegenüber SQL-89
(1)

• Neue Datentypen (z.B. Intervall)
• Domänenkonzept (create domain, alter domain)
• Änderung des Datenbankschemas (alter table, drop
  table)
• allgemeine Integritätsbedingungen über mehrere
  Tabellen
• Der Verbund join ist als eigener Operator
  vorhanden und wird in diversen Varianten
  angeboten cross join, join und using,
  natural join
• Die Beschreibungen von Embedded SQL und Dynamic
  SQL sind Teil der Norm

20
Einige Erweiterungen von SQL 3 gegenüber SQL-92

• abstrakte Datentypen (ADTs)
• Objekt- Identifikatoren
• ADT und Tabellen Hierarchien
• Definition von Funktionen von ADTs
• Komplexe Datentypen wie Mengen,
  Multimengen und Listen
• Rekursive Anfragen (with recursive und union)

21
Transaktionen

• Definition
• Eine Transaktion ist eine ununterbrechbare Folge
  von DML-Befehlen, die die Datenbank von einem
  logisch konsistenten in einen (neuen) logisch
  konsisten Zustand überführt

22
Transaktionen (2)

• Transaktionen sollten dabei die
  ACID-Eigenschaften besitzen
• Atomarität
• Die Transaktion ist die kleinste, nicht mehr
  zerlegbare Einheit
• alles-oder-nichts-Prinzip
• Consistency
• Hinterlässt nur konsistenten Datenbankzustand
• Zwischenzustände der Transaktionen dürfen jedoch
  inkonsistent sein
• Endzustand muss den Integritätsbedingungen
  erfüllen

23
Transaktionen (3)

• Isolation
• Nebenläufig ausgeführte Transaktionen dürfen sich
  nicht beeinflussen

24
Transaktionen (4)

• Durability
• Wirkung einer erfolgreich abgeschlossener
  Transaktion bleibt dauerhaft in der Datenbank
• Auch nach einem Systemfehler muss die Wirkung
  gewährleistet sein
• Die Wirkung einer erfolgreich abgechlossenen
  Transaktion kann nur durch eine kompensierende
  Transaktion aufgehoben werden

25
Synchronisation (Mehrbenutzerbetrieb)

• im Einbenutzerbetrieb werden Transaktionen immer
  
• hintereinander ausgeführt
• im Mehrbenutzerbetrieb kann es vorkommen, dass
  mehrere
• Transaktionen gleichzeitig (nebenläufig)
  ablaufen
• dies kann zu verschiedenen Problemen führen

26
Synchronisation (2)
Dirty Read
T1 T2
Read(A) A A 100 Write(A) Abort Read(A) Read(B) B B A Write(B) Commit
27
Sychronisation (3)
Non Repeatable Read
Lesetransaktion Änderungstransaktion DB-Inhalt (Pnr, Gehalt)
SELECT Gehalt INTO gehalt FROM Pers WHERE Pnr 2345 summe summe gehalt SELECT Gehalt INTO gehalt FROM Pers WHERE Pnr 3456 summe summe gehalt UPDATE Pers SET Gehalt Gehalt 1000 WHERE Pnr 2345 UPDATE Pers SET Gehalt Gehalt 2000 WHERE Pnr 3456 2345 39.000 3456 48.000 2345 40.000 3456 50.000
28
Synchronisation (4)
Phantom - Problem
Lesetransaktion Änderungstransaktion
SELECT SUM(Gehalt) INTO summe FROM Pers WHERE Anr 17 SELECT Gehaltssumme INTO gsumme FROM Abt WHERE Anr 17 IF gsumme ltgt summe THEN ltFehlerbehandlunggt INSERT INTO Pers (Pnr, Anr, Gehalt) VALUES (4567, 17, 55.000) UPDATE Abt SET Gehaltssumme Gehaltsumme 55.000 WHERE Anr 17
29
Synchronisation (5)
Konsistenzebenen in SQL
Konsistenzebenen Dirty Read NonRepeatable Read Phantome
Read Uncommitted Read Committed Repeatable Read Serializable - - - - - -
SET TRANSACTION READ ONLY, ISOLATION
LEVEL READ COMMITTED
30
Client-Server Prinzip

• Zentraler Datenbestand auf den mehrere Clients
  Zugriff haben

31
Verteilte Datenbanken

• Der Datenbestand wird in verteilten Datenbank
  Management Systemen (VDBMS) physisch auf mehrer
  Knote (Rechnern) verteilt

32
Zwei-Phasen-Commit-Protokoll

• Ausgehend von verteilten Transaktionen auf
  unterschiedlichen Knoten im Netz sollen nach dem
  alles oder nichts-Prinzip entweder alle
  Transaktionen oder keine Transaktion durchgeführt
  werden.
• Dies wird in verteilten Datenbanken durch das
  Zwei-Phasen-Commit-Protokoll unterstützt

33
(No Transcript)
34
Oracle DB - System

• Oracle Version 1 erschien 1979
• derzeit aktuell Version 10g
• das Oracle System besteht aus dem Oracle
  Server und
• verschiedenen Tools zur Steuerung und Erzeugung
  von
• Oracle Datenbanken
• Oracle Precompiler wie PROC oder PROCOBOL die
• den Zugriff auf Oracle DBs aus anderen
  Programmier
• sprachen unterstützen
• Oracle unterstützt direkt PL/SQL und Java

35
lauffähiges System

• ein Oracle DB System setzt sich aus den
  unabhängigen
• Teilsystemen Instanz und Datenbank zusammen
• auf einem Server können mehrere Instanzen und
  Datenbanken
• gleichzeitig aktiv sein
• ein lauffähiges System besteht aber immer aus
  einer Instanz und
• einer angeschlossenen Datenbank

36
Zusammensetzung einer Instanz

• System Global Area (SGA)
• Serverprozesse
• Hintergrundprozesse
• - Database Writer
• - Process Monitor

37
System Global Area (SGA)

• prozessübergreifender Speicherbereich
• 3 Komponenten
• Database Buffer Cache zum Zwischenspeichern von
• Datenbankblock-Kopien (zur Performance-Steigerun
  g)
• Redo Log Buffer zur Protokollierung von
  Änderungen
• auf dem Database Buffer Cache
• Shared Pool enthält geparste SQL-Anweisungen,
• kompilierte PL/SQL-Anweisungen und
  Datenbank-Trigger

38
Data Dictionary

• besondere Tabellen in der Datenbank
• bei Erzeugen einer neuen DB legt Oracle System
  und
• DB-Informationen als Tabellen in der Datenbank
  selbst ab
• enthält Informationen über Benutzer und alle zur
  Funktionsfähig-
• keit der Datenbank notwendigen Informationen

39
SQLPlus

• von Oracle entwickeltes interaktives Werkzeug
  zum Zugriff
• auf Oracle - Datenbanken
• in Oracle - System integriert (seit Version 3),
  Vorgänger war
• UFI (UserFriendlyInterface)
• äquivalent zu "isql" in Sybase and SQLServer,
• "db2" in IBM DB2, "mysql" in MySQL

40
Benutzung von SQLPlus

• aus dem Betriebssystem wird mit dem Kommando
• SQLPLUS Benutzer/Paßwort_at_Datenbankname
• Dateiname Parameter1...
• die interaktive Umgebung gestartet und an der
  angegebenen
• Datenbank angemeldet
• nach dem Anmelden erscheint eine
  Eingabeaufforderung
• SQLgt
• und das System ist zur Annahme von Anweisungen
  bereit

41
Benutzung von SQLPlus (2)

• es können alle SQL Anweisungen verwendet
  werden
• es können auch Dateien aufgerufen werden, die
  bereits SQL
• Anweisungen enthalten
• anonyme PL/SQL Blöcke können eingegeben werden
  und
• werden sofort ausgeführt
• es können auch PL/SQL Dateien und
  StoredProcedures
• aufgerufen und ausgeführt werden
• Transaktionen beginnen mit der ersten SQL
  Anweisung und
• enden mit dem Schlüsselwort COMMIT

42
SQLPlus Befehlspuffer

• SQLPlus legt jede aktuell eingebene SQL
  Anweisung
• bzw. jeden PL/SQL Block im Befehlspuffer ab
• der Inhalt des Befehlspuffers läßt sich anzeigen
  und editieren
• die zuletzt eingegebene Zeile wird als aktuelle
  Zeile betrachtet
• und mit einem markiert

43
Beispiel SQLPlus Befehlspuffer
SQLgt run 1 select name 2 from person 3
where nname like 'MOR' ERROR at line
3 ORA-00904 invalid column name SQLgt
c/nname/name 3 where name like
'MOR' SQLgtrun 1 select name 2 from
person 3 where name like 'MOR'
NAME ------------------------------------- MORLEY
MOROSCO .....
44
Procedural Language / SQL (PL/SQL)

• integraler Bestandteil von Oracle seit Version 6
• Erweiterung von SQL durch prozedurale Elemente
• Syntax ähnelt Ada
• ist in allen Oracle-Produkten verfügbar
• lässt sich auch in Client-Programmen in anderen
  Programmier-
• sprachen einsetzen (C, C, Ada, Cobol,
  Fortran, Pascal)
• erlaubt DML - Befehle aber keine DDL - Befehle

45
PL/SQL Code Beispiel
declare anzahl number(3) begin
SELECT count() INTO anzahl
FROM person end --------------------------------
--------------------------------- PL/SQL
procedure successfully completed
46
Verarbeitung von PL/SQL

• Entwicklung und Aufruf von PL/SQL-Blöcken
  erfolgt
• z.B. in interaktiven Umgebungen wie SQLPlus
• Verarbeitung erfolgt durch PL/SQL-Prozessor im
  Server
• oder im Client
• aus anderen Programmiersprachen heraus wird über
  RPC
• der Code an den PL/SQL-Prozessor im Server
  übergeben
• SQL-Anweisungen im PL/SQL-Code werden an den
  SQL-
• Prozessor weitergegeben, der das Ergebnis
  zurückgibt

47
(No Transcript)
48
Einschub Variablen - Typen

• PL/SQL unterstützt folgende elementare
  Datentypen
• - char, varchar, number, boolean, date,
  rowid, raw
• und folgende strukturierte Datentypen
• - PL/SQL Table eine Tabellenspalte mit
  einem
• bestimmten Datentyp
• - Record
• Datentyp von Variablen muss vor der Verwendung
  bekannt
• sein

49
Elemente von PL/SQL

• anonyme Blöcke
• Prozeduren
• Funktionen
• Packages
• Trigger

Stored Procedures
50
Anonymer Block

• kann nicht aus anderen PL/SQL-Programmen
  aufgerufen
• werden
• wird nicht in der Datenbank abgelegt
• wird direkt nach Eingabe ausgeführt
• declare /Deklarationsteil/
• begin /Anweisungsteil/
• exception /Exceptionteil/
• end

51
Prozeduren / Funktionen

• wie anonyme Blöcke, besitzen aber
  Definitionsteil
• function procedure trigger Name
  Parameterliste
• /Definitionsteil/
• is /Deklarationsteil/
• begin /Anweisungsteil/
• exception /Exceptionteil/
• end

52
Prozeduren / Funktionen (2)

• sind jederzeit aufrufbar und besitzen ein
  Parameterliste
• zur Übergabe von Aufrufparametern
• mit dem Schlüsselwort CREATE können Prozeduren
  und
• Funktionen in der Datenbank erzeugt und
  kompiliert ge-
• speichert werden (StoredProcedures)

53
Packages

• dienen zur Strukturierung von umfangreichem
  Programmtext
• Zusammenfassung von logisch zusammengehörigen
  Variablen,
• Typdefinitionen, Prozeduren und Funktionen
• Diese können von anderen PL/SQL-Objekten
  referenziert werden
• objektorientiert Definition von öffentlichen
  und privaten
• Objekten, dadurch Kapselung
  von Daten

54
StoredProcedures

• da Funktionen und Prozeduren kompiliert in der
  Datenbank
• vorliegen, entfällt die Übersetzungszeit beim
  Aufruf
• bei Zugriff auf eine StoredProcedure wird diese
  in den SGA
• geladen und steht allen DB-Benutzern zur
  Verfügung bis sie
• aus dem SGA verdrängt werden (LRU-Algorithmus)
• da das neue Laden in den SGA Zeit kostet, können
  wichtige SPs
• auch explizit im SGA gehalten werden

55
Vorteile von PL/SQL

• bessere Performance bei mehreren aufeinander
  folgenden
• DB-Zugriffen, wenn Anwendungsprogramm und
  Oracle-Server
• auf verschiedenen Rechnern laufen

Server
begin insert into test1 select from...
insert into test2. end
PL/SQL Blockübergabe
56
Trigger

• Trigger sind in PL/SQL oder SQL programmierte
  Objekte, die
• wie StoredProcedures in der Datenbank
  gespeichert sind
• ein Trigger gehört immer zu einer Tabelle und
  kann nur implizit
• aufgerufen werden, kann zur Integritätssicherun
  g eingesetzt werden
• der Aufruf erfolgt vor (BEFORE) oder nach
  (AFTER) einer
• Insert -, Update oder Delete - Operation auf
  einer Tabelle
• Trigger können StoredProcedures, weitere
  Trigger, DML und
• DDL Anweisungen benutzen
• zwei Typen Zeilen - und Anweisungstrigger

57
(Zeilen-) Trigger Beispiel
TRIGGER historie_trg after INSERT or UPDATE or
DELETE on film FOR EACH ROW begin if
INSERTING then prc_ins_syshist( FILM ,I,
new.film_id) end if if UPDATING
then prc_ins_syshist( FILM ,U,
new.film_id) end if if DELETING
then prc_ins_syshist( FILM ,D,
old.jahr,old.genre) end if
58
Triggertypen

• Zeilentrigger werden für jeden Datensatz, den
  die DML -
• Operation einfügt, verändert oder löscht einmal
  aktiviert
• Anweisungstrigger sind unabhängig von den
  Attributwerten
• der einzelnen Datensätze und reagieren nur auf
  die
• durchzuführende DML Anweisung

59
Anwendungsbeispiel Trigger

• über Anweisungstrigger lassen sich
  Berechtigungen im
• Mehrbenutzerbetrieb realisieren
• durch einen BEFORE - Anweisungstrigger, der z.B.
  einen
• Semaphor implementiert, können kritische
  Tabellenbereiche
• geschützt werden
• so können z.B. Dirty - Reads verhindert werden,
  da immer nur
• ein Nutzer kritische Tabelleninhalte verändern
  kann

60
SQL Constraints

• SQL Constraints sind direkt an Spalten in
  Tabellen gebunden
• und werden bei Erzeugung einer Tabelle mit dem
  Schlüsselwort
• CONSTRAINT definiert
• CREATE table personal (
• pnr Personalnummer,
• ght Gehalt,
• PRIMARY KEY (pnr),
• CONSTRAINT ght gt 5000,
• )
• Constraints haben keine Programmeigenschaften

61
Constraints vs. Trigger

• reguläre Constraints haben keine
  'Programmelemente', reine
• atomare Prüfungen
• Trigger können auch zur Integritätsprüfung
  verwendet werden,
• sind aber wesentlich mächtiger als Constraints
• es ist umstritten, ob man Trigger zur
  Integritätssicherung
• einsetzen sollte oder nicht

62
Quellen und Literatur

• Heuer, Saake Datenbanken Konzepte und
  Sprachen (c) 2000 mitp Verlag
• Türscher PL/SQL (c) 1997 Springer Verlag
• http//www.orafaq.com
• http//www.oracle.com
• DIS Skript 2003 N. Ritter, Uni Hamburg
• VSS Skript 2003 W.Lamersdorf / G.Gryczan, Uni
  Hamburg
• Oracle / SQL Tutorial http//www.db.cs.ucdavis.
  edu University of California