- Neben ROLLBACK und COMMIT gibt es noch eine dritte Anweisung aus der TCL-Sprache: SAVEPOINT
- SAVEPOINT name definiert einen Transaktionspunkt, bis zu der Anweisungen zurückgerollt werden können (siehe Seite 119).
- SAVEPOINT to name ist syntaktisch falsch.
CONSTRAINTS
- Es gibt unter Oracle mehrere Namensräume, und zwar einen für Tabellen, View, Sequenzen, Private Synonyme, benutzerdefinierte Datentypen je user, eine Namensraum für alle Indizes je user, einem Namensraum für alle CONSTRAINTS je user und einen generellen Namensraum mit Usernamen, Rollen und öffentlichen Synonymen.
- CONSTRAINT ohne Namen bekommen einen solchem vom System verpasst: SYS_scn;
- Ein NOT NULL CONSTRAINT muss immer als Spaltenconstraint vereinbart werden (heißen im Buch: inline constraint)
- Ein Foreign-Key-Constraunt kann auch auf einem UNIQUE KEY vereinbart werden.
- Tabellenbedingung = OUT OF LINE CONSTRAINT
- Auf den Datentypen BLOB, CLOB und TIMESTAMP WITH TIME ZONE kann kein Primärschlüssel und auch kein UNIQUE KEY definiert werden.
- Ein BLOB oder CLOB kann auf IS NULL geprüft werden.
- Es können nur CONSTRAINTS auf DEFERRED gesetzt werden, die vorher mit DEFERRABLE vereinbart wurden (siehe: SET CONSTRAINTS)
Datentypen für Zeitfelder unter Oracle
- DATE: beinhaltet year, month, date , hour, minute, second, keine Sekundenbruchteile
- NLS_DATE_FORMAT ist eine Systemvariable, die das Ausgabeformat festlegt, genau wie nls_language
- TIMESTAMP(n) hat zusätzlich zu DATE noch Sekundenbruchteile, Genauigkeit n, default n=6
- TIMESTAMP WITH TIME ZONE speichert zusätzlich die Zeitzone des DB-Servers beim Datumswert. z.B. +2:00
- TIMESTAMP WITH LOCAL TIME ZONE ist der Zeitstempel, der auf dem Datenbankclient gültig ist, nicht auf dem Server.
- Anfrage mit Datumsfunktionen : SELECT DBTIMEZONE, SESSIONTIMEZONE , SYSTIMESTAMP, LOCALTIMESTAMP FROM DUAL;
- INTERVAL YEAR(n) TO MONTH: Zeitinterval für Jahre und Monate, n=2 als Default
- INTERVAL DAY(n1) TO SECOND(n2) : Zeitinterval in Tagen, Stunden, Minuten, Sekunden und Sekundenbruchteilen,. DEFAULT n1 = 2, n2 = 6. Wert muss genau eingehalten werden (siehe Beispiel)
- Wer noch mehr wissen will: Orcale Datetime
Zum Testen:
DROP TABLE TEST_TIME;
CREATE TABLE TEST_TIME (
s0 NUMBER,
s1 TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
S2 TIMESTAMP WITH LOCAL TIME ZONE DEFAULT LOCALTIMESTAMP,
S3 INTERVAL YEAR(4)TO MONTH DEFAULT ’10-1′,
s4 INTERVAL DAY TO SECOND DEFAULT ’40 07:30:00.030000′);
INSERT into TEST_TIME(s0) VALUES (1);
SELECT * FROM TEST_TIME;
- Hier geht es um hierarchische Abfragen mit CONNECT BY, werden auch als rekursive Abfragen bezeichnet. Rekursive Abfragen beziehen sich immer nur auf eine Tabelle und sind damit eine Art von SELFJOIN.
- Es steht schon einiges im Datenbank-Wiki : CONNECT-BY.
- Jede rekursive Abfrage mit CONNECT BY muss mindestens ein PRIOR enthalten.
- Neue Funktion: SYS_CONNECT_BY_PATH kann genutzt werden, um den Pfad bis zum Ausgabewert anzuzeigen (S. 625)
- Neue Funktion: CONNECT_BY_ROOT gibt die Daten des Vaterknotens zusätzlich aus (S. 626).
- CONNECT BY kann zusätzlich auch eine Bedingung zum Ausschluss von Daten beinhalten (Seite 627), um ganze Äste auszuschließen. Die WHERE-Klausel schließt nur einzelne Knoten aus.
Multitable INSERT
- Neben den bekannten Möglichkeiten, per SUBQUERY in einem CREATE TABLE-Befehl, einem INSERT , UPDATE oder DELETE große Datenmengen in einem Befehl zu ändern, gibt es noch den MULTITABLE INSERT (Oracle spezifisch) und den MERGE-Befehl (SQL-Standard).
- Mit dem Multi Tabler INSERT können verschiedene Tabellen gleichzeitig mit dem Ergebnis einer Unterabfrage unter einer WHEN-Bedingung gefüllt werden.
- Der MULTITABLE INSERT bietet drei Möglichkeiten
- ALL: Ohne WHEN-Bedingung werden mehrere Tabellen mit den Daten aus einer Unterabfrage gefüllt (Seite 572)
- FIRST: Nur die erste WHEN ELSE-Klausel wird berücksichtigt (Seite 575)
- ALL geht auch beim matchen einer WHEN-Bedingung alle weiteren WHENs durch, wobei FIRST bei dem ersten match aufhört
- Multiple WHEN-Klauseln ohne ALL und FIRST: Je nach Bedingung sind verschiedene INSERTS in unterschiedliche Tabellen möglich (S. 576)
- Die Unterabfrage beim MULTITABLR-INSERT ist unverzichtbar.
- Table-Alias aus einer Subquery können nicht im INSERT verwendet werden, statt dessen sind Spaltennamen erforderlich (siehe Seite 579)
- Wenn ein Multi Table INSERT fehlschlägt, wird die komplette Anweisung zurückgerollt.
MERGE
- MERGE ist ein DML-Befehl, mit dem man einen INSERT, einen UPDATE und einen DELETE-Befehl gleichzeitig ausführen kann. (Syntax: Seite 583).
- USING … ON … wählt ähnlich wie bei einem JOIN die Tabellen, VIEW bzw. die Unterabfrage aus, die als Quelle für die MERGE-Anweisung genutzt werden soll.
- Ähnlich wie beim Multi-Table INSERT gibt es WHEN-MATCHED-Klauseln, die spezifizieren, wann eine UPDATE und wann eine INSERT erfolgen soll.
- Die DELETE-Klausel ist Bestandteil des UPDATE-Asts, d.h. wird nur ausgeführt, wenn die Daten vorher durch UPDATE geändert wurden und dann zusätzlich die zugehörige DELETE-WHERE-Klausel greift.
FLASH-BACK-Query
- Mit diesem Abfrage -Type können Daten aus der Vergangenheit abgefragt werden.
- Mit AS OF in SELECT können Daten abgefragt werden, die durch einen Zeitstempel in der Vergangenheit oder durch eine SCN gekennzeichnet werden.
- SELECT * FROM Test AS OF TIMESTAMP SYSTIMESTAMP – INTERVAL(0 0:01:30′ DAY to second fragt die Daten ab, die vor 0 Tagen, 0 Stunden, einer Minute und 30 Sekunden in der Tabelle Test standen.
- Das geht auch noch auf andere Weise… muss hier noch ergänzt werden, z. B. VERSIONS BETWEEN(Seite 594)
- Unter V$SYSTEM_PARAMETER gibt es mehrere undo-Parameter, unter anderem undo_retention. In undo_retention wird die Zeit in Sekunden gespeichert, bis zu der historische Daten abgefragt werden können. Möglich sind mehrere Minuten bis zu evt. mehrere Stunden. Jedoch steigt dadurch natürlich der Speicherplatzbedarf immens.
- Die V$Views können nur für einfache Abfragen genutzt werden, Oracle garantiert keine Lesekonsistenz bei komplexen Abfragen, z.B. über Joins.
- Man kann Kommentare zu Tabellen oder Spalten einer Tabelle schreiben.
- Die View Dictionary ist der Einstiegspunkt in das Data Dictionary.
- USER_CATALOG enthält Infos über Tabellen, Views, Synonyme und Sequences, USER_OBJECTS zusätzlich über alle anderen Objekte mit mehr Infos zu den einzelnen Objekten, wie z.B. den Status einer View.
- Der CONSTRAINT-TYPE (R= Foreign Key, P=Primary Key, U = UNIQUE, C = CHECK oder NOT NULL) in der View USER_CONSTRAINTS hat etwas überraschende Werte.
- Zu den Mengenoperatoren UNION, UNION ALL , INTERSECT und MINUS findet sich einiges im Datenbank-Wiki. z.B. in UNION , UNION ALL und Vereinigungskonform.
- UNION ALL eliminiert keine doppelten Einträge und ist daher schneller als UNION.
- Alle Mengenoperatoren UNION, UNION ALL , INTERSECT und MINUS haben die gleiche Priorität und werden daher nacheinander abgearbeitet.
- Ein SELECT mit mehreren Mengenoperatoren kann immer nur ein ORDER BY als letzte Zeile beinhalten: Ein ORDER BY position oder ein ORDER BY Referenz, wobei sich das ORDER BY REFERENZ(Spaltenname) auf den ersten SELECT bezieht.
- Create Sequence: The default „START WITH 1 INCREMENT BY 1“ is the reason why the first given number has the value 2
-- Which value has NEXTVAL if an insert statement fails?
CREATE TABLE SEQ_TEST (
id NUMBER NOT NULL,
description VARCHAR2(25) NOT NULL
);
CREATE SEQUENCE SEQ_TEST_ID;
SELECT SEQUENCE_NAME, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_TEST_ID';
-- SEQUENCE_NAME INCREMENT_BY LAST_NUMBER
-- ------------------------------ --------------- ---------------
-- SEQ_TEST_ID 1 1
— Second INSERT statement without description will violate NOT NULL constraint.
INSERT INTO SEQ_TEST (id, description) VALUES (SEQ_TEST_ID.NEXTVAL, 'first row');
INSERT INTO SEQ_TEST (id) VALUES (SEQ_TEST_ID.NEXTVAL);
INSERT INTO SEQ_TEST (id, description) VALUES (SEQ_TEST_ID.NEXTVAL, 'third row');
COMMIT;
SELECT id, description FROM SEQ_TEST;
-- ID DESCRIPTION
-- --------------- -------------------------
-- 2 first row
-- 4 third row
Ein CURRVAL einer SEQUENCE kann erst benutzt werden, wenn vorher ein NEXTVALUE benutzt wurde.
Und es gibt auch kein CREATE OR REPLACE SEQUENCE, sollte mal jemand danach fragen.
Ein MAXVALUE kann auch negative sein, wenn MINVALUE kleiner als MAXVALUE ist und zusätzlich CYCLE angegeben ist.
Hier habe ich wenig gefunden, stimmt ziemlich genau mit der DBS1-Vorlesung überein.
- Prioritätsreihenfolge Boolescher, logischer Operatoren: siehe Logischer Operator
- ORDER BY Name oder Spaltennummer (position)
- Verwendung IS NULL oder IS NOT NULL
- Sortierung von alphanumerischen Spalten: Alphabetisch…
- Sortierung von Datumsfeldern: in der Zeitachse
Link zur Homepage faeskorn-woyke.de
- siehe auch: Unterabfrage in Unterabfrage, skalare Unterabfrage und last not least korrelierte Unterabfrage. Korrelierte Unterabfragen können nicht ohne äußere SELECT-Abfrage ausgeführt werden. Skalare Unterabfragen geben genau eine Zeile und einen Spalte zurück und können nicht in einem GROUP-BY-Ausdruck benutzt werden.
- Unterabfragen können auch zum Kopieren von Daten in einem CREATE TABLE verwendet werden, daneben auch in SELECT-Klausel, INSERT, UPDATE oder DELETE.
- Korrelierte Unterabfragen können auch in UPDATE- und DELETE-Anweisungen verwendet werden.
- SOME ist ein Synonym für ANY, wird gleich verwendet
- Die WITH-Klausel ist im SQL-Standard für rekursive (heißen auch : hierarchisch) Abfragen vorgesehen, unter Oracle aber nur mit sehr eingeschränkter Syntax (siehe auch WITH-Klausel). Unterabfragen werden temporär mit einem Namen (alias) versehen.