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.

(Einführung in SQL: Tipps und Tricks)

(Einführung in SQL: Tipps und Tricks)

Die letzte ID abfragen

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

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:

Firebird-Version

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

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

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

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:

Oracle-Version

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;

Ausgabe

1 row inserted;
Last ID = 23

Weitere Informationen über Sequenzen siehe Oracle: Sequenzen.

Tabellenstruktur auslesen

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

Übersicht

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

Übersicht

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

Übersicht

-- 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

Übersicht

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

Weitere Erläuterungen sind in den folgenden Kapiteln zu finden:

• Fremdschlüssel-Beziehungen
• SQL-Programmierung