Wenn Sie die Definition einer Sicht lediglich für eine einzige Abfrage benötigen, können Sie einen Tabellenausdruck verwenden.
Tabellenausdrücke sind temporäre Elemente und nur für die Dauer der SQL-Anweisung gültig. Anders als Sichten können sie nicht gemeinsam benutzt werden. Sie bieten jedoch im Vergleich zu Sichten eine größere Flexibilität.
Dieser Abschnitt beschreibt, wie allgemeine Tabellenausdrücke und verschachtelte Tabellenausdrücke in Abfragen eingesetzt werden.
Ein verschachtelter Tabellenausdruck ist eine temporäre Sicht, wobei die Definition in der Klausel FROM der Hauptabfrage verschachtelt (= direkt definiert) ist.
Die folgende Abfrage verwendet einen verschachtelten Tabellenausdruck, um den durchschnittlichen Gesamtverdienst, die Bildungsstufe und das Einstellungsjahr für Mitarbeiter mit einer höheren Bildungsstufe als 16 zu ermitteln:
SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY),7,2) FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR, SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE WHERE EDLEVEL > 16) AS PAY_LEVEL GROUP BY EDLEVEL, HIREYEAR ORDER BY EDLEVEL, HIREYEAR
Das Ergebnis lautet folgendermaßen:
EDLEVEL HIREYEAR 3 ------- ----------- --------- 17 1967 28850.00 17 1973 23547.00 17 1977 24430.00 17 1979 25896.50 18 1965 57970.00 18 1968 32827.00 18 1973 45350.00 18 1976 31294.00 19 1958 51120.00 20 1975 42110.00
In dieser Abfrage wird zunächst das Einstellungsjahr mit einem verschachtelten Tabellenausdruck aus der Spalte HIREDATE extrahiert, damit es anschließend in der Klausel GROUP BY verwendet werden kann. In diesem Fall ist es unter Umständen sinnvoller, keine Sicht zu erstellen, wenn ähnliche Abfragen mit unterschiedlichen Werten für EDLEVEL ausgeführt werden sollen.
Das vorstehende Beispiel verwendet die integrierte Skalarfunktion DECIMAL. DECIMAL gibt die Dezimaldarstellung einer Zahl oder einer Zeichenfolge zurück. Ausführliche Informationen zu Funktionen finden Sie im Handbuch SQL Reference.
Ein allgemeiner Tabellenausdruck ist ein Tabellenausdruck, der zur Verwendung in einer komplexen Abfrage erstellt wird. Er wird am Beginn der Abfrage in einer Klausel WITH definiert und benannt. Bei wiederholtem Bezug auf einen allgemeinen Tabellenausdruck wird dieselbe Ergebnismenge verwendet. Im Vergleich hierzu würde bei der Verwendung von verschachtelten Tabellenausdrücken oder Sichten die Ergebnismenge jedesmal erneut und möglicherweise mit unterschiedlichen Ergebnissen generiert.
Das folgende Beispiel listet alle Mitarbeiter im Unternehmen auf, die eine höhere Bildungsstufe als 16 aufweisen und deren Verdienst unter dem Durchschnittsverdienst der Mitarbeiter liegt, die in demselben Jahr eingestellt wurden und dieselbe Bildungsstufe haben. Die einzelnen Bestandteile der Abfrage werden im Anschluß an die Abfrage ausführlicher beschrieben.
(1)
WITH PAYLEVEL AS (SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR, SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE WHERE EDLEVEL > 16),
(2)
PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) FROM PAYLEVEL GROUP BY EDLEVEL, HIREYEAR)
(3)
SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2) FROM PAYLEVEL, PAYBYED WHERE EDLEVEL = EDUC_LEVEL AND HIREYEAR = YEAR_OF_HIRE AND TOTAL_PAY < AVG_TOTAL_PAY
Das Endergebnis lautet folgendermaßen:
EMPNO EDLEVEL YEAR_OF_HIRE TOTAL_PAY 5 ------ ------- ------------ ------------- --------- 000210 17 1979 20132.00 25896.50