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
[Bearbeiten]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
[Bearbeiten]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
[Bearbeiten]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
[Bearbeiten]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
[Bearbeiten]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
[Bearbeiten]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
[Bearbeiten]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
[Bearbeiten]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
[Bearbeiten]-- 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
[Bearbeiten]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
[Bearbeiten]Weitere Erläuterungen sind in den folgenden Kapiteln zu finden: