Une expression de table permet de définir une vue qui n'est nécessaire que dans une seule requête.
Les expressions de table sont provisoires. Elles ne sont valables que pour la durée de l'instruction SQL considérée ; elles ne peuvent pas être partagées comme des vues, mais leur utilisation est cependant plus souple que celle des vues.
Vous trouverez ci-dessous des exemples d'utilisation, dans des requêtes, d'expressions de table, imbriquées ou non imbriquées.
Une expression de table imbriquée est une vue temporaire dont la définition est imbriquée (définie directement) dans la clause FROM de la requête principale.
La requête ci-après utilise une expression de table imbriquée pour générer une réponse à la question suivante : Quel est le salaire total moyen, ainsi que le niveau d'études et l'ancienneté, des employés dont le niveau d'études est supérieur à 16 :
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
Voici le résultat obtenu :
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
Cette requête nécessite l'utilisation d'une expression de table imbriquée pour extraire l'année d'embauche de la colonne HIREDATE. Cette année sera ensuite utilisée dans la clause GROUP BY. Vous pouvez choisir de ne pas générer l'expression en tant que vue, si vous avez l'intention d'exécuter les mêmes requêtes à l'aide de valeurs différentes de EDLEVEL.
L'exemple utilise la fonction scalaire intégrée DECIMAL qui renvoie la représentation décimale d'un nombre ou d'une chaîne de caractères. Pour plus de détails sur les fonctions, consultez le document SQL Reference.
Une expression de table commune est une expression de table que vous créez parce que vous avez l'intention de l'utiliser plusieurs fois dans une requête complexe. Définissez-la et attribuez-lui un nom dès le début de la requête, à l'aide d'une clause WITH. Les utilisations répétées d'une expression de table commune se serviront à chaque fois du même ensemble de résultats. Par comparaison, si vous utilisiez une expression de table imbriquée ou une vue imbriquée, l'ensemble de résultats serait généré à chaque fois, avec des résultats éventuellement différents.
L'exemple suivant permet de répertorier tous les employés de la société dont le niveau d'études est supérieur à 16, dont le salaire moyen est inférieur à celui des employés de même niveau d'études embauchés la même année. Les éléments de la requête sont décrits de façon plus détaillée à la suite de la requête.
(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
Voici le résultat obtenu :
EMPNO EDLEVEL YEAR_OF_HIRE TOTAL_PAY 5 ------ ------- ------------ ------------- --------- 000210 17 1979 20132,00 25896,50