Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Cours Oracle - SQLA Module n° SQL Avancé PSBDMF! Programme de Formation de Supi

Cours Oracle - SQLA Module n° SQL Avancé PSBDMF! Programme de Formation de Supinfo Laboratoire Supinfo des Technologies Oracle Auteur : Thibault Blanchard Date : 01/01/1601 01:00 - Version 1.2 Nombre de Page : 32 http://www.labo-oracle.com Ecole Supérieure d'Informatique 23, rue Château Landon 75010 PARIS http://www.supinfo.com SQL Avancé - Version 1.2 Laboratoire Supinfo des Technologies Oracle Page 2 / 32 http://www.labo-oracle.com 07/03/2003 1 UTILISATION DE FONCTION DE GROUPE AVEC LES OPERATEURS CUBE ET ROLLUP __ 4 1.1 Rappel sur les fonctions de groupe _________________________________________ 4 1.2 Fonctions de groupes avancées ____________________________________________ 4 1.2.1 GROUP BY avec les opérateurs ROLLUP ET CUBE _______________________ 4 1.2.2 L’opérateur ROLLUP ________________________________________________ 4 1.2.3 Exemple d’utilisation de ROLLUP ______________________________________ 4 1.2.4 L’opérateur CUBE __________________________________________________ 5 1.2.5 Exemple d’utilisation de CUBE_________________________________________ 5 1.2.6 La fonction GROUPING ______________________________________________ 6 1.2.7 Exemple d’utilisation de GROUPING____________________________________ 6 1.3 Les fonctions analytiques _________________________________________________ 7 1.3.1 Description des fonctions analytiques ___________________________________ 7 1.3.2 La fonction RANK___________________________________________________ 7 1.3.3 La fonction CUME_DIST _____________________________________________ 8 2 RECUPERATION HIERARCHIQUE. ______________________________________________ 10 2.1 Aperçu des requêtes hiérarchiques ________________________________________ 10 2.1.1 Dans quel cas utiliser une requête hiérarchique ? _________________________ 10 2.1.2 Structure en arbre _________________________________________________ 10 2.1.3 Requêtes hiérarchiques _____________________________________________ 10 2.2 Parcourir l’arbre ________________________________________________________ 10 2.2.1 Point de départ____________________________________________________ 10 2.2.2 Sens du parcours __________________________________________________ 11 2.2.3 Exemple de parcours _______________________________________________ 11 2.3 Organiser les données ___________________________________________________ 12 2.3.1 Classer les lignes avec la pseudo colonne LEVEL ________________________ 12 2.3.2 Formatage d’un rapport hiérarchique à l’aide de LEVEL et LPAD _____________ 12 2.3.3 Eliminer une branche _______________________________________________ 12 2.3.4 Ordonner les données ______________________________________________ 13 2.3.5 La fonction ROW_NUMBER() ________________________________________ 13 3 ECRITURE DE SOUS REQUETES CORRELEES ___________________________________ 15 3.1 Sous requêtes __________________________________________________________ 15 3.2 Sous requêtes corrélées _________________________________________________ 15 3.2.1 Description des sous requêtes corrélées ________________________________ 15 3.2.2 Utilisation de requêtes corrélées ______________________________________ 15 3.2.3 L’opérateur EXISTS ________________________________________________ 16 3.2.4 L’opérateur NOT EXISTS____________________________________________ 17 3.3 UPDATE corrélés _______________________________________________________ 17 3.4 DELETE corrélés________________________________________________________ 18 4 UTILISATION DES OPERATEURS D’ENSEMBLE __________________________________ 19 4.1 Les opérateurs d’ensemble _______________________________________________ 19 4.2 UNION et UNION ALL ____________________________________________________ 19 4.2.1 L’opérateur UNION ________________________________________________ 19 4.2.2 L’opérateur UNION ALL _____________________________________________ 20 4.2.3 Utilisation de UNION et UNION ALL ___________________________________ 20 4.3 INTERSECT ____________________________________________________________ 21 4.3.1 L’opérateur INTERSECT ____________________________________________ 21 4.3.2 Utilisation de l’opérateur INTERSECT __________________________________ 21 4.4 MINUS_________________________________________________________________ 22 4.4.1 L’opérateur MINUS ________________________________________________ 22 4.4.2 Utilisation de l’opérateur MINUS ______________________________________ 22 SQL Avancé - Version 1.2 Page 3 / 32 Laboratoire Supinfo des Technologies Oracle 07/03/2003 http://www.labo-oracle.com 4.5 Règles syntaxiques des opérateurs d’ensemble ______________________________ 22 4.5.1 Règles sur les opérateurs d’ensemble__________________________________ 22 4.5.2 Faire correspondre la syntaxe des SELECT _____________________________ 22 4.6 Contrôler l’ordre des lignes _______________________________________________ 23 5 ECRIRE DES SCRIPTS AVANCES_______________________________________________ 24 5.1 Utilisation de SQL pour générer du SQL ____________________________________ 24 5.2 Création d’un script basique ______________________________________________ 24 5.3 Contrôler l’environnement ________________________________________________ 25 5.4 Un script complet _______________________________________________________ 26 5.5 Renvoyer le contenu d’une table vers un fichier ______________________________ 26 5.6 Générer un attribut dynamique ____________________________________________ 27 6 CREATION RAPPORTS AVEC SQL*PLUS ________________________________________ 28 6.1 La commande SET ______________________________________________________ 28 6.1.1 Les variables de la commande SET____________________________________ 28 6.1.2 Variables de la commande SET supplémentaires _________________________ 28 6.2 La commande COLUMN __________________________________________________ 28 6.3 La commande COMPUTE _________________________________________________ 28 6.3.1 Syntaxe de COMPUTE _____________________________________________ 28 6.3.2 Utilisation de la commande COMPUTE _________________________________ 29 7 ANNEXE : LES TABLES UTILISEES _____________________________________________ 31 7.1 La table EMP ___________________________________________________________ 31 7.2 La table DEPT __________________________________________________________ 31 7.3 La table EMP_HISTORY __________________________________________________ 31 SQL Avancé - Version 1.2 Laboratoire Supinfo des Technologies Oracle Page 4 / 32 http://www.labo-oracle.com 07/03/2003 1 UTILISATION DE FONCTION DE GROUPE AVEC LES OPERATEURS CUBE ET ROLLUP 1.1 Rappel sur les fonctions de groupe Cf. Cours SQLP « Module 2 : Techniques de récupération de données » § 2 « Les fonctions de groupe » 1.2 Fonctions de groupes avancées 1.2.1 GROUP BY avec les opérateurs ROLLUP ET CUBE Les fonctions CUBE et ROLLUP sont utilisées avec GROUP BY pour obtenir des super agrégats de lignes par références croisées aux colonnes. Les opérations de ROLLUP et de CUBE sont spécifiées dans la clause GROUP BY d’une requête. Le groupement ROLLUP retourne le même résultat q’une requête avec un GROUP BY, mais il retourne également des lignes de résultat prenant en compte tous les sur ensembles. Le groupement CUBE retourne le même résultat que le groupement ROLLUP mais il retourne également des lignes de résultat prenant en compte tous les sous-ensemble. 1.2.2 L’opérateur ROLLUP L’opérateur ROLLUP est une extension de la clause GROUP BY qui permet de produire des agrégats cumulatifs tels que des sous totaux. SELECT column, group_function FROM table [WHERE condition] [GROUP BY [ROLLUP] (group_by_expression)]; L’opérateur ROLLUP crée des groupements en parcourant dans une direction, « de la droite vers la gauche », la liste de colonnes spécifiée dans la clause GROUP BY. Il effectue ensuite la fonction de groupe à ces groupements. 1.2.3 Exemple d’utilisation de ROLLUP L’opérateur ROLLUP crée des sous totaux allant du niveau le plus détaillé à un total général, suivant la liste de groupement spécifié. Il calcule d’abord les valeurs standards de la fonction de groupe pour les groupements spécifiés dans la clause GROUP BY, puis il crée des sous totaux pour les sur ensembles, en parcourant la liste des colonnes de droite a gauche. Pour deux arguments dans l’opérateur ROLLUP d’un GROUP BY, la requête retournera n+1=2+1=3 groupements. Les lignes résultant des valeurs des n premiers arguments sont appelées lignes originales et les autres sont appelées lignes de grand ensemble. Commentaire : Il serait intéressant de faire pointer ceci vers le document adéquat sur le site du labo. 21/08 : Impossible car il faut un compte pour rentrer sur le site. SQL Avancé - Version 1.2 Page 5 / 32 Laboratoire Supinfo des Technologies Oracle 07/03/2003 http://www.labo-oracle.com Exemple: SQL> SELECT deptno,job, SUM(sal) 2 FROM emp 3 GROUP BY ROLLUP(deptno,job); DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 9400 29025 13 ligne(s) sélectionnée(s). -> Cette requête affiche la somme des salaires pour chaque fonction dans chaque département ainsi que la somme des salaires pour chaque département et pour tous les départements. 1.2.4 L’opérateur CUBE L’opérateur CUBE est une extension de la clause GROUP BY qui permet de retourner des valeurs de sous ensembles avec un ordre SELECT et qui peut être utilisé avec toutes les fonctions de groupe. SELECT column, group_function FROM table [WHERE condition] [GROUP BY [CUBE] (group_by_expression)]; Alors que ROLLUP ne retourne qu’une partie des combinaisons de sous totaux possibles, CUBE retourne toutes les combinaisons possibles des groupes spécifiés dans le GROUP BY ainsi qu’un total. Toutes les valeurs retournées sont calculées à partir de la fonction de groupe spécifiée dans la liste de SELECT. 1.2.5 Exemple d’utilisation de CUBE L’opérateur CUBE retourne donc le même résultat que ROLLUP mais il y a en plus la fonction de groupe appliquée au sous groupe. Le nombre de groupes supplémentaires dans le résultat est déterminé par le nombre de colonnes inclues dans la clause GROUP BY, car chaque combinaison de colonnes est utilisée pour produire de grands ensembles. Donc si il y a n colonnes ou expressions dans le GROUPY BY, il y aura 2ⁿ combinaisons de grands ensembles possibles. SQL Avancé - Version 1.2 Laboratoire Supinfo des Technologies Oracle Page 6 / 32 http://www.labo-oracle.com 07/03/2003 Exemple : SQL> SELECT deptno, job, SUM(sal) 2 FROM emp 3 GROUP BY CUBE(deptno, job); DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 ANALYST 6000 CLERK 4150 MANAGER 8275 PRESIDENT 5000 SALESMAN 5600 29025 ->Cette requête retourne le même résultat qu’avec l’opérateur ROLLUP en y ajoutant la somme des salaires pour chaque job. 1.2.6 La fonction GROUPING Lors de l’utilisation des opérateurs ROLLUP et CUBE, des champs vides apparaissent dans la présentation du résultat. Pour ne pas confondre ces champs avec des valeurs NULL il existe la fonction GROUPING. Elle permet également de déterminer le niveau du sous total, c'est-à-dire le ou les groupes à partir desquels sont calculés les sous totaux. SELECT column, group_function, GROUPING(expr) FROM table [WHERE condition] [GROUP BY [ROLLUP][CUBE] (group_by_expression)]; La fonction GROUPING ne peut recevoir qu’une seule colonne en argument. Cet argument doit être le même qu’une des expressions de la clause GROUP BY. 1.2.7 Exemple d’utilisation de GROUPING GROUPING se comporte comme une fonction booléenne. Elle renvoie 0 quand : Î Le champ a été utilisé pour calculer le résultat de la fonction Î La valeur NULL dans le champ correspond à une valeur NULL dans la table. Elle renvoie 1 quand : Î Le champ n’a pas été utilisé pour calculer le résultat de la fonction Î La valeur NULL dans le champ a été créée par ROLLUP/CUBE, à la suite d’un uploads/Science et Technologie/ sql-a-module-01.pdf