E - Beuth Hochschule für Technik Berlin

SQL/92
Standardkataloge
- Eine Menge von Views (mit Standardnamen) beschreibt die Metadaten eines Kataloges.
- Es gibt eine "Schema Manipulation Language" zum Entfernen von
DB-Objekten - mit zwei Verhaltensweisen:
. RESTRICT = Verbot der Durchführung, wenn existierende Objekte
durch das Löschen in Mitleidenschaft gezogen werden.
. CASCADE = vollzieht die Löschung auch bei abhängigen Objekten
in irgendeiner Form.
- Datenbankobjekte:
SQL environment
implementation
character set
collation
TFH Berlin
authorization
identifier
table
column
catalog
/schema
viewed
table
constraint
domain
column
module
assertion
constraint
privilege
translation
SQL/92
Neue Datentypen
Data Type
Numeric
Exact
String
Datetime
Approximate
Bit
Fixed
Varying
Interval
Date
Time
Timestamp
Character
Fixed
Varying
Varying CHAR (Varying Length Character String) soll voll kompatibel mit dem festen Charaktertyp sein.
BIT-Strings sind ein neuer Datentyp. Es gibt eine Menge von Stringoperationen, z.B. Konkatenation,
Position, Substring, Gross-Klein-Transformation, Blank oder andere Elimination. Zeichenmengen und
-reihenfolgen werden benutzerdefiniert.
Der Typ DATE hat die Werte YEAR, MONTH, DAY.
Der Typ TIME hat die Werte HOUR, MINUTE, SECOND (und Bruchteile).
Der Typ TIMESTAMP hat die Werte YEAR, MONTH, DAY, HOUR, MINUTE, SECOND (und Bruchteile).
Werte für Intervalle können sein: YEAR, MONTH oder DAY, HOUR, MINUTE, SECOND (und Bruchteile).
TFH Berlin
SQL/92
Mehr Orthogonalität
Konstruktoren für Zeilen und Tabellen
INSERT INTO province
VALUES (('BC','British Columbia'), ('AB','Alberta'), ...('NF','Newfoundland'))
SELECT nation, population
FROM nations
UNION ALL
VALUES ('Quebec',6000000), ('California',24000000)
Prädikate operieren eher über Zeilen als über Skalare.
(SELECT lname, fname FROM people WHERE ...)
=
(SELECT last, first FROM hobbies WHERE ...)
Eine Subquery kann überall benutzt werden, wo ein Ausdruck erlaubt ist.
SELECT * FROM people
WHERE (lname, fname) = (SELECT last, first FROM hobbies
WHERE hobby = 'travel')
SELECT AVG(salary),
(SELECT AVG(salary) FROM mgr_employee),
(SELECT AVG(salary) FROM temp_employee)
SELECT last, first, (SELECT description
FROM hobby_description h
WHERE h.name = hobbies.hobby)
FROM hobbies
WHERE ...
UPDATE employee
SET salary = (SELECT MAX(salary)
FROM mgr_employee
WHERE employee.name = 'Doe')
UPDATE employee
SET salary = (SELECT MAX(salary)
FROM mgr_employee)
WHERE employee.name = 'Doe'
TFH Berlin
SQL/92
Neue Jointypen
People
Hobbies
lname fname
nick
last
first
hobby
Holland William
Smith Roberta
Bill
Bobbie
Holland William Fishing
Zysko Ana
Painting
cross join (neue Syntax)
SELECT * FROM (people
CROSS JOIN hobbies) AS result
früher: SELECT *
FROM people, hobbies
lname fname
nick
last
first
Holland William
Holland William
Smith Roberta
Smith Roberta
Bill
Bill
Bobbie
Bobbie
Holland William
Zysko Ana
Holland William
Zysko Ana
hobby
Fishing
Painting
Fishing
Painting
inner join (neue Syntax)
SELECT * FROM (people
INNER JOIN hobbies
ON lname=last) AS result
früher: SELECT *
FROM people, hobbies
WHERE lname = last
lname fname
nick
last
first
hobby
Holland William
Bill
Holland William Fishing
union join
SELECT * FROM (people
UNION JOIN hobbies) AS result
früher: -
lname fname
nick
last
Holland William
Holland William
null
null
null
null
Bill
Bill
null
null
null
null
null
null
Holland William
Zysko Ana
TFH Berlin
first
hobby
null
null
Fishing
Painting
SQL/92
Neue Jointypen
People
Hobbies
lname fname
nick
last
first
hobby
Holland William
Smith Roberta
Bill
Bobbie
Holland William Fishing
Zysko Ana
Painting
linker outer join
SELECT * FROM (people
LEFT OUTER JOIN hobbies
ON lname=last) AS result
früher: -
lname fname
nick
last
first
hobby
Holland William
Smith Roberta
Bill
Holland William Fishing
Bobbie null
null
null
rechter outer join
SELECT * FROM (people
RIGHT OUTER JOIN hobbies
ON lname=last) AS result
früher: -
lname fname
nick
last
first
hobby
Holland William
null
null
Bill
null
null
Holland William Fishing
Zysko Ana
Painting
voller outer join
SELECT * FROM (people
FULL OUTER JOIN hobbies
ON lname=last) AS result
früher: -
lname fname
nick
Holland William
Smith Roberta
null
null
Bill
Holland William Fishing
Bobbie null
null
null
null
Zysko Ana
Painting
TFH Berlin
last
first
hobby
SQL/92
Isolation levels
Isolierungsstufen stellen dar, wie sicher ein Benutzer sein kann, dass die von ihm gelesenen Zeilen
aktuellen Inhalt haben. Sie nehmen dem Programmierer die Aufgabe der eigenen Realisierung mit
Sperrbefehlen ab.
Isolierungsstufe 0:
Es wird nichts gesperrt.
(READ UNCOMMITTED)
Isolierungsstufe 1:
Es wird implizit eine Zeile gesperrt, wenn sie angefasst wird.
Es ist immer nur eine Zeile pro Tabelle gesperrt.
(READ COMMITTED)
Isolierungsstufe 2:
Es wird eine Zeile gesperrt, wenn sie angefasst wird.
Diese Sperre wird für wiederholtes Arbeiten gehalten.
(REPEATABLE READ)
Isolierungsstufe 3:
Es wird implizit eine ganze Ergebnismenge für wiederholtes Arbeiten gesperrt.
(CURSOR STABILITY)
Zugriffsmodus: READ ONLY and READ WRITE
Beispiel:
SET TRANSACTION READ WRITE
READ COMMITTED
DIAGNOSTICS SIZE 20;
SET TRANSACTION darf nicht innerhalb einer (anderen) aktiven Transaktion ausgeführt
werden.
TFH Berlin
SQL/92
Cursor
Cursor werden ausgebaut: Sie werden beweglich (scrollable) und erlauben Vorwärts- und
Rückwärtsbewegungen und Sprünge.
FETCH mit:
FIRST
LAST
NEXT
PRIOR
ABSOLUTE n
RELATIVE n
EXEC SQL DECLARE c SCROLL CURSOR FOR SELECT ...;
EXEC SQL OPEN c;
EXEC SQL FETCH ABSOLUTE 10 FROM c INTO ...;
EXEC SQL FETCH RELATIVE 32 FROM c INTO ...;
EXEC SQL FETCH PRIOR FROM c INTO ...;
Cursor können mit Vorabsperren angekündigt werden:
EXEC SQL DECLARE c1 CURSOR FOR
SELECT lname, fname FROM people
READ ONLY;
EXEC SQL DECLARE c2 CURSOR FOR
SELECT lname, fname, nick FROM people
FOR UPDATE OF nick;
TFH Berlin
SQL/92
Dynamisches SQL
Dynamisches SQL wird gebraucht, wenn die Tabellen, Spalten oder
Präikate zur Compilezeit nicht bekannt sind.
einmalige sofortige Ausführung
s = "INSERT INTO people VALUES ('Harris', ...)";
EXEC SQL EXECUTE IMMEDIATE :s;
mehrmalige Ausführung
EXEC SQL PREPARE stmt FROM :s;
EXEC SQL EXECUTE stmt;
EXEC SQL EXECUTE stmt;
dynamische Parametergenerierung
s = "INSERT INTO people VALUES (?, ?, ...)";
EXEC SQL PREPARE stmt FROM :s;
lname = "Harris"; fname = "Todd";
EXEC SQL EXECUTE stmt USING :fname, ...;
Ein dynamisches Descriptorfeld kann benutzt werden, wenn die Anzahl der dynamischen Parameter nicht
bekannt ist.
s = "INSERT INTO people VALUES (?, ?, ...)";
EXEC SQL PREPARE stmt FROM :s;
EXEC SQL ALLOCATE DESCRIPTOR 'input_params';
EXEC SQL DESCRIBE INPUT stmt
INTO SQL DESCRIPTOR 'input_params';
EXEC SQL GET DESCRIPTOR 'input_params';
:n = COUNT;
for (i=1;<n;i++)
{
EXEC SQL GET DESCRIPTOR 'input_params'
VALUE :i, t = TYPE, ...;
EXEC SQL SET DESCRIPTOR 'input_params'
VALUE :i, DATA = :d, INDICATOR = :ind;
}
EXEC SQL EXECUTE stmt
USING SQL DESCRIPTOR'input_params';
Dynamisierung bei Statementnamen, Cursornamen, Descriptor Area Namen.
s = "...";
s_stmt = "my_stmt";
s_desc = "my_descriptor";
s_cursor = "my_cursor";
EXEC SQL PREPARE :s_stmt FROM :s;
EXEC SQL ALLOCATE DESCRIPTOR :s_desc;
EXEC SQL ALLOCATE :s_cursor CURSOR FOR :s_stmt;
EXEC SQL OPEN :s_cursor;
TFH Berlin
SQL/92
SQLSTATE
SQLSTATE ersetzt SQLCODE und ist erweitert:
5 Zeichen:
2 Zeichen repräsentieren die Klasse,
3 Zeichen repräsentieren die Subklasse.
Klassen und Subklassen konnen vom Anwender erweitert werden.
SQLSTATE SQLCODE Beschreibung
'00000' 0
'02000' 100
'22001' - n
'22012' - n
erfolgreiche Ausführung
keine Daten
Datenproblem, rechtes Abschneiden
Datenproblem, Division durch Null
Zwischen einem SQL-Client und einem SQL-Server werden Connections hergestellt.
Es gibt eine SQL-Session zu jeder Connection.
env = "IBMSYS";
connect = "STLconnection";
user = "Todd";
EXEC SQL CONNECT TO :env AS :connect USER :user;
...
EXEC SQL COMMIT;
env = "IBMSYS2";
EXEC SQL SET CONNECTION :env;
TFH Berlin