SQL Oracle - requêtes avancées

Copie de tables
Fonctions de groupe
hiérarchies
sous requête simple
traitements multi lignes et multi colonnes
sous requêtes synchronisées
opérateurs ensemblistes
jointures
requêtes distribuées
conditionnelles (decode)

Copie de tables

on utilise la commande create table...as select...

create table emp2 as
select * from emp;

copie de structure de table uniquement : il suffit de rajouter un prédicat faux et les lignes ne seront pas copiées...

create table emp2 as
select * from emp
where 1 = 2;

Les fonctions de groupe

Permet de faire des statistiques rudimentaires.
Toutes les colonne_critères de la clause select doivent être présentes dans la clause group by. Une colonne peut être présente dans la clause group by sans être sélectionnée.

forme générale
SELECT fonction de groupe [,fonction de groupe,...], colonne_critère[, colonne_critère...]
FROM nom_table
WHERE nom_colonne|constante|expression opérateur de comparaison nom_colonne|constante|expression
GROUP BY colonne critère[, colonne critère...]
HAVING nom colonne|constante|expression opérateur de comparaison nom_colonne|constante|expression

Exemple
Salaire le plus élevé dans chaque département

SQL> select d.deptno, d.dname, max(sal)
2 from emp e , dept d
3 where e.deptno = d.deptno
4 group by d.deptno, d.dname;

DEPTNO    DNAME          MAX(SAL)
--------- -------------- ---------
10        ACCOUNTING     5000
20        RESEARCH       3000
30        SALES          2850

Gestion des hiérarchies

forme générale
SELECT liste_col
FROM nom de table
[WHERE nom colonne|constante|expression opérateur nom colonne|constante|expression]
CONNECT BY PRIOR expression = expression [and|or nom colonne|constante|expression
opérateur de comparaison nom colonne|constante|expression]
START WITH nom colonne|constante|expression opérateur nom colonne|constante|expression]
[ORDER BY expression]

PRIOR indique si l'arbre doit être parcouru de la racine vers les feuilles ou des feuilles vers la racine. Il précède l'enfant si on parcourt l'arbre de la racine vers les feuilles. Il précède la clé étrangère (le parent) si on parcourt l'arbre des feuilles vers la racine. Une restriction dans une clause supprime les individus, mais par leur descendance (ou leur ascendance suivant le sens du parcours).  
Une restriction dans une clause CONNECT BY supprime les individus et leur descendance (ou leur ascendance suivant le sens du parcours).
START WITH indique le noeud à partir duquel l'arbre est parcouru. Un select avec gestion d'arborescence ne peut être une jointure. ORDER BY est exécuté après le tri arborescent.

Exemples
arborescence complète chef vers subordonné
SELECT e.ename  Organigramme
FROM emp E
START WITH e.mgr IS NULL
connect by E.MGR = prior E.EMPNO;

arborescence complète chef vers subordonné
Select lpad(E.ENAME,LENGTH(E.ENAME)+(LEVEL-1)*3,'+') "Organigramme horizontal"
from EMP E
where E.ENAME <> 'BLAKE'
start with E.MGR is null
connect by E.MGR = prior E.EMPNO; nous donne :

Organigramme horizontal
-----------------------
KING
+++JONES
++++++SCOTT
+++++++++ADAMS
++++++FORD
+++++++++SMITH
++++++ALLEN
++++++WARD
++++++MARTIN
++++++TURNER
++++++JAMES
+++CLARK
++++++MILLER

13 ligne(s) sélectionnée(s).
arborescence partielle du subordonné vers le chef

Select lpad(E.ENAME,LENGTH(E.ENAME)+(LEVEL-1)*3,'+') "Organigramme horizontal"
from EMP E
start with E.ENAME = 'ADAMS'
connect by E.EMPNO = prior E.MGR; nous donne :

Sous requête ou requetes imbriquées

C'est un SELECT qui contient un SELECT, ceci permet de rendre la partie droite du prédicat variable. L'ordre de résolution des select va du plus profond au moins profond. La partie gauche du prédicat doit avoir le même nombre d'arguments que la partie droite et ces arguments doivent être ordonnés et homogènes en type et en nombre.
L'opérateur de comparaison doit comporter les opérateurs IN, ALL ou ANY lorsque le select imbriqué doit ramener plusieurs valeurs.
En cas de prédicat where exists, le select imbriqué retourne la valeur VRAI s'il ramène au moins un enregistrement, FAUX sinon. Le select comportant le prédicat where exists ne s'exécute que si la valeur retournée par le select imbriqué est VRAI.

forme générale

SELECT (projection)
FROM nom de table
WHERE nom colonne|constante|expression opérateur de comparaison (SELECT ...)

Exemple

liste des employes dont le manager est BLAKE

Traitements multi-lignes et multi-colonnes

sous-requêtes

Lorsqu'une sous-requête ramène plusieur lignes l'utilisation de l'opérateur '=' dans la clause WHERE provoque une erreur. On utilisera alors des opérateurs spéciaux :
- ALL : compare à toutes les valeurs d'une liste ou d'une requête (doit suivre l'un des opérateurs suivants : =, !=, >, <, <=, >=
- IN : égal à n'importe laquelle des valeurs (équivallent à '=ANY' )
- ANY : compare à au moins une des valeurs de la liste ou du select (opérateurs idem à ALL)
- WHERE EXISTS : vrai si la sous-requête retourne au moins une ligne, n'est pas précédé d'un opérateur...

Exemples

liste de ceux qui gagnent plus qu'un employé du département 10

 ANY est différent de ALL, ainsi la requête suivante ne ramêne pas de ligne !

SQL> select ename,sal from emp
  2   where sal > all (select sal from emp where deptno=10);


Liste de ceux qui gagnent plus que n'importe lequel des managers   

SQL> select ename from emp
where sal > all
(select sal from emp
where job = 'MANAGER');

ENAME
-----
SCOTT
KING
FORD

update

Exemples

intégrons la commission aux salaires de Smith et Martin :

SQL> update emp set sal=sal+nvl(comm,0), comm=0
     where ename = 'SMITH' or ename = 'MARTIN';

augmentons le salaire de Mme Martin (au maximum) pour fêter son mariage avec Mr Martinez...

SQL> update emp set (ename, sal) = 
     (select 'MARTINEZ',max(sal) from emp )
     where ename = 'MARTIN';

1 ligne mise à jour.

remarque : on voit ici que le SELECT peut être utilisé pour fournir des valeurs constantes également...

insert

plutôt que d'insérer ligne par ligne avec des valeurs constantes, on peut insérer n lignes en même temps, résultant d'un SELECT :

SQL> insert into emp(empno, ename)
select no, nom from succursale;

Sous-requêtes synchronisées

Dans une sous-requête synchronisée ou correllée (correlated subquery), la requête interne reçoit des valeurs de la requête externe (appelante) pour chaque ligne traité dans le SELECT principal. Une colonne de la table de la requête principale sera référencée dans la requête interne, et peut être considéré comme un paramètre d'entrée de cette dernière. L'utilisation de synonyme de table est obligatoire pour faire la distinction entre colonne de la table principale et de la table secondaire.

forme générale

SELECT liste_col FROM T1 syn1
WHERE expr oper ( SELECT liste_col FROM T2 syn2
WHERE syn1.col1 oper syn2.col2 )

exemple

liste des employés dont le salaire (incluant leur éventuelle commission) dépasse celui d'un chef
SELECT ename, sal + NVL(comm,0)
FROM emp
WHERE sal+NVL(comm,0) >
( select max(SAL) FROM emp WHERE job = 'MANAGER' )

liste des employés dont le salaire (incluant leur éventuelle commission) dépasse celui DE LEUR chef
SELECT ename, sal + NVL(comm,0)
FROM emp x
WHERE sal+NVL(comm,0) >
( select SAL FROM emp WHERE empno = x.mgr )

Afficher les employés dont le salaire est le plus élevé de leur département.

Select empno, ename, deptno, sal
from EMP E where sal =
(Select max(SAL) from EMP
where e.deptno = deptno
group by deptno)

Les opérateurs ensemblistes : union, intersect, minus

SQL> select deptno from emp
2 intersect 
3 select deptno from dept;

DEPTNO
-------
10
20
30

exemples

SQL> Select E.EMPNO, E.ENAME, M.ENAME, D.LOC
from EMP E, EMP M, DEPT D
where E.MGR = M.EMPNO (+)
and E.DEPTNO = D.DEPTNO;

Donner le nombre d'employés de tous les départements existants.

On notera qu'il n'apparait que 13 lignes car Mr KING président de la société n'a pas de chef...Si on voulait le faire apparaitre quand même, il autait fallu faire une jointure externe...

requêtes distribuées

Une requête distribuée est une forme évoluée de requête distante, permettent d'accéder à des données provenant de tables situées sur des bases différentes (en général une locale et une distante).
On utilise un database link, nom logique référençant un compte utilisateur d'une base distante, via SQL*Net.

forme générale d'une requête distante

SELECT liste_col FROM nom_table@nom_database_link;

exemple

supposons qu'il existe un database link scott_lyon, pointant sur le compte de scott dans la base de production de lyon. On peut alors faire une requête du style :

SELECT ename , dname
FROM emp, dept@scott_lyon dpt
WHERE emp.deptno = dpt.deptno

on peut masquer cet accès distant en utilisant un synonyme (attention à la maintenance...)

create synonym departement for dept@scott_lyon dpt;
SELECT ename , dname
FROM emp, departement
WHERE emp.deptno = departement.deptno;

Conditionnelles - fonction Decode

L'utilisation de la fonction decode permet de renvoyer des valeurs de manière conditionnelle sans un SELECT. Cette fonction renvoie une valeur qui dépendant du résultat de l'expression évaluée et sinon une valeur par défaut.
Ainsi SELECT decode (no, 1, 'un', 2, 'deux', 'inconnu') from T1
renvoie suivant la valeur de la colonne no qui est un numérique ici, la chaîne de caractère 'un' ou 'deux' et 'inconnu' dans tous les autres cas...

exemple

SELECT ename, decode (deptno, 10,'xxxx',20,'xxxx', sal) "sals"
from emp;

Database links

Un database link permet de faire une requête sur un, voire plusieurs, objet(s) distant(s) au sein d'une session. Il suffit d'avoir les droits pour se connecter à la base distante (connaître le nom de la base distante, l'utilisateur distant et son mot de passe).

SQL> create database link scott_TESUN
connect to scott identified by tiger
using 'TESUN';
Lien de base de données créé.

SQL> select * from dept@scott_TESUN;

modifié le 20/11/2006

ecris moi

les forums techniques Oracle

mon BLOG Oracle, en Francais

l'autre vie de DD

mon CV

trucs et astuces

JOBs Oracle du jour

Mon site : Tout sur Oracle (et le web)

Utilisation de ces documents