Oracle Database 11g : Les fondamentaux du langage SQL Guide

ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Oracle Database 11g : Les fondamentaux du langage SQL
Guide Pour les Exercices
1 Extraire des données à l'aide de l'instruction SQL
SELECT
1.1
Instruction SELECT de base
Instruction SELECT de base
Dans sa forme la plus simple, une instruction SELECT doit inclure les éléments
suivants :
Une clause SELECT, qui indique les colonnes à afficher.
Une clause FROM, qui identifie la table contenant les colonnes répertoriées dans la
clause SELECT.
Dans la syntaxe :
SELECT
Désigne une liste d'une ou plusieurs colonnes.
*
Sélectionne toutes les colonnes.
DISTINCT
Supprime les doublons.
column|expression Sélectionne la colonne ou l'expression nommée.
alias
Attribue des en-têtes différents
aux colonnes sélectionnées.
FROM table
Indique la table contenant les colonnes.
1
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
1.2
Sélectionner toutes les colonnes
Vous pouvez afficher toutes les colonnes de données d'une table en indiquant un
astérisque (*) après le mot-clé SELECT. Dans l'exemple ci-dessus, la table
DEPARTMENTS contient quatre colonnes : DEPARTMENT_ID,
DEPARTMENT_NAME, MANAGER_ID et LOCATION_ID.
Vous pouvez également afficher toutes les colonnes de la table en les indiquant
toutes après le mot-clé SELECT. Par exemple, l'instruction SQL suivante (de même
que l'exemple de la diapositive ci-dessus) affiche toutes les colonnes et toutes les
lignes de la table DEPARTMENTS :
SELECT department_id, department_name, manager_id, location_id
FROM departments;
1.3
Sélectionner des colonnes spécifiques
Sélectionner des colonnes spécifiques
Vous pouvez utiliser l'instruction SELECT pour afficher des colonnes spécifiques de
la table, en indiquant leurs noms séparés par des virgules. L'exemple ci-dessus
affiche tous les numéros de département et de lieu de la table DEPARTMENTS.
2
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Dans la clause SELECT, indiquez les colonnes dans l'ordre dans lequel elles doivent
apparaître dans le résultat.
1.4
Expressions arithmétiques
Expressions arithmétiques
Vous pouvez être amené à modifier la façon dont les données s'affichent, à effectuer
des calculs ou à examiner des scénarios de simulation. Tout cela est possible à
l'aide d'expressions arithmétiques. Une expression arithmétique peut contenir des
noms de colonne, des valeurs numériques constantes et des opérateurs
arithmétiques.
Opérateurs arithmétiques
Vous pouvez utiliser des opérateurs arithmétiques dans n'importe quelle clause
d'une instruction SQL (à l'exception de la clause FROM).
Remarque : Avec les types de données DATE et TIMESTAMP, vous pouvez
seulement utiliser les opérateurs d'addition et de soustraction.
1.5
Utiliser des opérateurs arithmétiques
SELECT last_name, salary, 12*(salary+100)
FROM
employees;
Utiliser des opérateurs arithmétiques
L'exemple ci-dessus utilise l'opérateur d'addition pour calculer une augmentation de
salaire de 300 $ pour tous les employés. Une colonne nommée SALARY+300
apparaît dans le résultat à l'affichage uniquement.
Règles de priorité :
•Les multiplications et les divisions sont traitées avant les additions et les
soustractions.
•Les opérateurs présentant la même priorité sont évalués de gauche à droite.
3
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
•Les parenthèses permettent de passer outre les priorités par défaut ou de clarifier
l'instruction.
1.6
Définir une valeur NULL
SELECT last_name, job_id, salary, commission_pct
FROM
employees;
Définir une valeur NULL
Si la valeur d'une donnée est manquante pour une colonne donnée, cette valeur est
considérée comme NULL.
Une valeur NULL est une valeur non disponible, non attribuée, inconnue ou
inapplicable. Une valeur NULL est différente d'un zéro ou d'un blanc. La valeur zéro
est un nombre, tandis que le blanc est un caractère.
Toutes les colonnes peuvent contenir des valeurs NULL, quel que soit leur type.
Toutefois, certaines contraintes (NOT NULL et PRIMARY KEY) empêchent
l'utilisation de valeurs NULL dans les colonnes.
Dans la colonne COMMISSION_PCT de la table EMPLOYEES, notez qu'un seul
vendeur ou responsable des ventes peut percevoir une commission. Les autres
employés ne peuvent pas percevoir de commissions. Une valeur NULL représente
cet état de fait.
Remarque : Par défaut, SQL Developer utilise le littéral (null) pour identifier les
valeurs NULL. Toutefois, vous pouvez le remplacer par la chaîne de votre choix.
Pour ce faire, sélectionnez Preferences dans le menu Tools. Dans la boîte de
dialogue Preferences, développez le noeud Database. Cliquez sur Advanced
Parameters et, dans le volet de droite, entrez la valeur appropriée pour "Display Null
value As".
1.7
Valeurs NULL dans les expressions arithmétiques
SELECT last_name, 12*salary*commission_pct
FROM
employees;
Valeurs NULL dans les expressions arithmétiques
Dès lors qu'une valeur de colonne d'une expression arithmétique est NULL, le
résultat est NULL. Par exemple, si vous tentez de diviser une valeur par zéro, vous
obtenez une erreur. En revanche, si vous divisez un nombre par NULL, le résultat est
NULL ou inconnu.
Dans l'exemple ci-dessus, l'employé King n'obtient aucune commission. Etant donné
que la colonne COMMISSION_PCT utilisée dans l'expression arithmétique contient
la valeur NULL, le résultat est NULL.
4
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
1.8
Utiliser des alias de colonne
SELECT last_name AS name, commission_pct comm
FROM
employees;
SELECT last_name "Name" , salary*12 "Annual Salary"
FROM
employees;
Utiliser des alias de colonne
Le premier exemple affiche les noms et les pourcentages de commission de tous les
employés. Le mot-clé facultatif AS a été utilisé avant l'alias de colonne. Le résultat de
l'interrogation est le même que le mot-clé AS soit utilisé ou non. Dans l'instruction
SQL les alias de colonne name et comm sont en minuscules, alors que le résultat de
l'interrogation affiche les en-têtes de colonne en majuscules. Comme mentionné
dans la diapositive précédente, les en-têtes de colonne apparaissent par défaut en
majuscules.
Le deuxième exemple affiche le nom et la rémunération annuelle de tous les
employés. L'alias Annual Salary contenant un espace, il a été placé entre guillemets.
Notez que l'en-tête de colonne du résultat est exactement identique à l'alias de
colonne.
1.9
Opérateur de concaténation
SELECT
FROM
last_name||job_id AS "Employees"
employees;
Opérateur de concaténation
Vous pouvez associer des colonnes à d'autres colonnes, expressions arithmétiques
ou constantes pour créer une expression de type caractère, à l'aide de l'opérateur
de concaténation (||). Les colonnes situées de part et d'autre de l'opérateur sont
associées afin de générer une colonne de résultat unique.
5
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Dans l'exemple, les colonnes LAST_NAME et JOB_ID sont concaténées, et l'alias
Employees est attribué à la colonne résultante. Le nom et l'ID de poste de l'employé
sont associés afin de générer une colonne de résultat unique.
Le mot-clé AS avant l'alias facilite la lecture de la clause SELECT.
Valeurs NULL avec l'opérateur de concaténation
Si vous concaténez une valeur NULL avec une chaîne de caractères, vous obtenez
une chaîne de caractères. LAST_NAME || NULL donne LAST_NAME.
Remarque : Vous pouvez également concaténer des expressions de type Date avec
d'autres expressions ou colonnes.
1.10 Utiliser des chaînes de caractères littérales
SELECT last_name ||' is a '||job_id
AS "Employee Details"
FROM
employees;
Utiliser des chaînes de caractères littérales
L'exemple de la diapositive ci-dessus affiche le nom et l'ID de poste de tous les
employés.
La colonne présente l'en-tête Employee Details. Notez les espaces entre les
apostrophes dans l'instruction SELECT. Ces espaces améliorent la lisibilité du
résultat.
Dans l'exemple suivant, le nom et le salaire de chaque employé sont concaténés
avec un littéral afin de rendre les lignes renvoyées plus significatives :
SELECT last_name ||': 1 Month salary = '||salary Monthly
FROM employees;
1.11 Opérateur de délimitation (q) alternatif
SELECT department_name || ' Department' ||
q'['s Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
6
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Opérateur de délimitation (q) alternatif
De nombreuses instructions SQL utilisent des littéraux de type caractère dans des
expressions ou des conditions. Si le littéral lui-même contient une apostrophe, vous
pouvez utiliser l'opérateur de délimitation (q) et sélectionner votre propre délimiteur.
Vous pouvez choisir n'importe quel délimiteur, mono-octet ou multi-octet, ou l'une
des paires de caractères suivantes : [ ], { }, ( ) ou < >.
Dans l'exemple présenté, la chaîne contient une apostrophe, qui est normalement
interprétée
en tant que délimiteur d'une chaîne de caractères. L'opérateur q permet d'utiliser des
crochets [] comme délimiteurs. La chaîne figurant entre crochets est interprétée en
tant que chaîne de caractères littérale.
1.12 Lignes en double
SELECT department_id
FROM
employees;
SELECT DISTINCT department_id
FROM
employees;
Lignes en double
Sauf indication contraire, les résultats d'une interrogation SQL sont affichés sans
suppression des lignes en double. Le premier exemple ci-dessus affiche tous les
numéros de département de la table EMPLOYEES. Notez que ces numéros sont
répétés.
Pour éliminer les lignes en double du résultat, incluez le mot-clé DISTINCT dans la
clause SELECT immédiatement après le mot-clé SELECT. Dans le deuxième
7
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
exemple, la table EMPLOYEES contient 20 lignes, mais seulement sept numéros de
département uniques.
Vous pouvez indiquer plusieurs colonnes après le mot-clé DISTINCT. Il porte alors
sur toutes les colonnes sélectionnées et le résultat est toute combinaison distincte
des colonnes.
SELECT DISTINCT department_id, job_id
FROM employees;
1.13 Afficher la structure d'une table
DESC[RIBE] tablename
Afficher la structure d'une table
Dans SQL Developer, vous pouvez afficher la structure d'une table à l'aide de la
commande DESCRIBE. Cette commande affiche le nom et le type de données des
colonnes. Elle indique également si une colonne doit contenir des données (c'est-àdire si la colonne comporte une contrainte NOT NULL).
Dans la syntaxe, tablename est le nom d'une table ou d'une vue existante, ou un
synonyme accessible par l'utilisateur.
Dans l'interface de SQL Developer, vous pouvez sélectionner la table dans
l'arborescence Connections et cliquer sur l'onglet Columns pour afficher sa structure.
Remarque : La commande DESCRIBE est prise en charge à la fois par SQL*Plus et
par SQL Developer.
8
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
2 Guide Exercice 2
Restreindre et trier les données
2.1 Limiter les lignes sélectionnées
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM
table
[WHERE condition(s)];
Limiter les lignes sélectionnées
Vous pouvez limiter le nombre de lignes renvoyées par l'interrogation à l'aide de la
clause WHERE. Celle-ci suit immédiatement la clause FROM et contient une
condition qui doit être satisfaite. Une ligne n'est renvoyée que si elle remplit cette
condition.
Dans la syntaxe :
WHERE
Restreint l'interrogation aux lignes remplissant une
condition.
condition
constantes
Est composée de noms de colonne, d'expressions, de
et d'un opérateur de comparaison. Une condition est une
combinaison d'expressions et d'opérateurs logiques
(booléens)
qui renvoie la valeur TRUE, FALSE ou UNKNOWN.
La clause WHERE permet de comparer les valeurs de colonnes, de littéraux,
d'expressions arithmétiques ou de fonctions. Elle se compose de trois éléments :
•Nom de colonne
•Condition de comparaison
•Nom de colonne, constante ou liste de valeurs
2.2 Utiliser la clause WHERE
SELECT employee_id, last_name, job_id, department_id
FROM
employees
WHERE department_id = 90 ;
Utiliser la clause WHERE
Dans l'exemple ci-dessus, l'instruction SELECT extrait l'ID d'employé, le nom, l'ID de
poste et le numéro de département de tous les employés du département 90.
Remarque : Vous ne pouvez pas utiliser d'alias de colonne dans la clause WHERE.
9
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
2.3 Chaînes de caractères et dates
SELECT last_name, job_id, department_id
FROM
employees
WHERE last_name = 'Whalen' ;
SELECT last_name
FROM
employees
WHERE hire_date = '17-FEB-96' ;
Chaînes de caractères et dates
Dans la clause WHERE, les chaînes de caractères et les dates doivent être
indiquées entre apostrophes (' '). Ce n'est pas le cas, en revanche, des constantes
numériques.
Toutes les recherches sur les chaînes de caractères distinguent les majuscules des
minuscules. Dans l'exemple suivant, aucune ligne n'est renvoyée car la table
EMPLOYEES stocke tous les noms avec différentes casses :
SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = 'WHALEN';
La base de données Oracle stocke les dates selon un format numérique interne,
représentant le siècle, l'année, le mois, le jour, les heures, les minutes et les
secondes. Le format par défaut d'affichage de la date est DD-MON-RR.
Remarque : Pour plus d'informations sur le format RR et sur la modification du
format de date par défaut, reportez-vous au chapitre "Utiliser des fonctions
monolignes pour personnaliser les résultats". Ce même chapitre présente aussi
l'utilisation de fonctions monolignes telles que UPPER et LOWER pour passer outre
la distinction entre majuscules et minuscules.
2.4 Opérateurs de comparaison
10
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Opérateurs de comparaison
Les opérateurs de comparaison sont utilisés dans les conditions comparant une
expression à une autre valeur ou expression. Ils sont utilisés dans la clause WHERE
selon le format suivant :
Syntaxe
... WHERE expr operator value
Exemple
... WHERE hire_date = '01-JAN-95'
... WHERE salary >= 6000
... WHERE last_name = 'Smith'
Vous ne pouvez pas utiliser d'alias dans la clause WHERE.
Remarque : Les symboles != et ^= peuvent aussi représenter la condition non égal
à.
2.5 Utiliser des opérateurs de comparaison
SELECT last_name, salary
FROM
employees
WHERE salary <= 3000 ;
Utiliser des opérateurs de comparaison
Dans l'exemple ci-dessus, l'instruction SELECT extrait de la table EMPLOYEES le
nom et le salaire de tout employé dont le salaire est inférieur ou égal à 3 000 $.
Notez qu'une valeur explicite est indiquée dans la clause WHERE. La valeur explicite
3000 est comparée au montant stocké dans la colonne SALARY de la table
EMPLOYEES.
2.6
Indication d'une plage à l'aide
de l'opérateur BETWEEN
SELECT last_name, salary
FROM
employees
WHERE salary BETWEEN 2500 AND 3500 ;
Indication d'une plage à l'aide de l'opérateur BETWEEN
Vous pouvez afficher des lignes en fonction d'une plage de valeurs en utilisant
l'opérateur BETWEEN. La plage que vous indiquez est définie par une limite
inférieure et une limite supérieure.
L'instruction SELECT ci-dessus renvoie les lignes de la table EMPLOYEES qui
correspondent aux employés dont le salaire est compris entre 2 500 $ et 3 500 $.
Les valeurs associées à l'opérateur BETWEEN sont prises en compte dans la plage.
Vous devez d'abord indiquer la limite inférieure.
Vous pouvez également utiliser l'opérateur BETWEEN pour des chaînes de
caractères :
SELECT last_name
FROM employees
WHERE last_name BETWEEN 'King' AND 'Smith';
11
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
2.7
Condition d'appartenance à l'aide
de l'opérateur IN
SELECT employee_id, last_name, salary, manager_id
FROM
employees
WHERE manager_id IN (100, 101, 201) ;
Condition d'appartenance à l'aide de l'opérateur IN
Pour effectuer un test sur l'appartenance à un jeu de valeurs donné, utilisez
l'opérateur IN.
La condition définie à l'aide de cet opérateur est appelée condition d'appartenance.
L'exemple de la diapositive ci-dessus affiche l'ID d'employé, le nom, le salaire et l'ID
de manager de tous les employés dont l'ID de manager est 100, 101 ou 201.
L'opérateur IN peut être utilisé avec n'importe quel type de données. L'exemple
suivant extrait de la table EMPLOYEES les lignes qui correspondent aux employés
dont le nom est inclus dans la liste associée à la clause WHERE :
SELECT employee_id, manager_id, department_id
FROM employees
WHERE last_name IN ('Hartstein', 'Vargas');
Si la liste comporte des caractères ou des dates, ceux-ci doivent être placés entre
apostrophes ('').
Remarque : L'opérateur IN est évalué en interne par le serveur Oracle comme un
ensemble de conditions "ou" (OR), par exemple a=value1 ou a=value2 ou a=value3.
Par conséquent, l'utilisation de l'opérateur IN ne présente aucun avantage en termes
de performances. Elle sert uniquement à simplifier la formulation logique.
2.8
Correspondance avec un modèle à l'aide
de l'opérateur LIKE
SELECT
first_name
FROM
employees
WHERE
first_name LIKE 'S%' ;
Correspondance avec un modèle à l'aide de l'opérateur LIKE
Il peut arriver que vous ne connaissiez pas la valeur exacte à rechercher. Dans ce
cas, vous pouvez sélectionner les lignes correspondant à un modèle à l'aide de
l'opérateur LIKE. L'opération de mise en correspondance avec un modèle est
appelée recherche avec caractères génériques. Deux symboles peuvent être utilisés
pour construire la chaîne de recherche.
L'instruction SELECT ci-dessus renvoie, à partir de la table EMPLOYEES, le prénom
de tout employé dont le prénom commence par la lettre "S". Notez le "S" majuscule.
Les prénoms commençant par un "s" minuscule ne sont pas pris en compte.
L'opérateur LIKE peut être utilisé comme raccourci pour certaines comparaisons
BETWEEN. L'exemple suivant affiche le prénom et la date d'embauche de tous les
employés embauchés entre janvier 1995 et décembre 1995 :
SELECT last_name, hire_date
FROM employees
WHERE hire_date LIKE '%95';
12
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
2.9
Combiner des caractères génériques
SELECT last_name
FROM
employees
WHERE last_name LIKE '_o%' ;
Combiner des caractères génériques
Les symboles % et _ peuvent être associés librement à des caractères littéraux.
L'exemple de la diapositive ci-dessus affiche le nom de tous les employés dont le
nom comporte la lettre "o" comme deuxième caractère.
Identificateur ESCAPE
Lorsque vous avez besoin d'une correspondance exacte pour les caractères % et _,
utilisez l'identificateur ESCAPE. Cette option indique le caractère d'échappement
utilisé. Par exemple, pour rechercher des chaînes contenant SA_, vous pouvez
utiliser l'instruction SQL suivante :
SELECT employee_id, last_name, job_id
FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';
2.10 Utiliser les conditions NULL
SELECT last_name, manager_id
FROM
employees
WHERE manager_id IS NULL ;
Utiliser les conditions NULL
Il existe deux conditions NULL : IS NULL et IS NOT NULL.
La condition IS NULL recherche les valeurs NULL. Une valeur NULL est une valeur
non disponible, non attribuée, inconnue ou inapplicable. Vous ne pouvez donc pas
faire de test avec = car une valeur NULL ne peut pas être égale ou non égale à une
valeur. L'exemple ci-dessus extrait le nom et le manager de tous les employés qui
n'ont pas de manager.
2.11 Définir des conditions à l'aide des opérateurs logiques
Définir des conditions à l'aide des opérateurs logiques
Une condition logique peut combiner le résultat de deux conditions pour produire un
résultat unique, ou inverser le résultat d'une condition. Il faut que le résultat global de
la condition soit vrai pour qu'une ligne soit renvoyée.
Trois opérateurs logiques sont disponibles en langage SQL :
13
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
•AND
•OR
•NOT
Tous les exemples présentés jusqu'ici comprenaient une seule condition dans la
clause WHERE. Vous pouvez utiliser plusieurs conditions dans une clause WHERE
unique à l'aide des opérateurs AND et OR.
2.12 Utiliser l'opérateur AND
SELECT employee_id, last_name, job_id, salary
FROM
employees
WHERE salary >= 10000
AND
job_id LIKE '%MAN%' ;
Utiliser l'opérateur AND
Dans l'exemple ci-dessus, les deux conditions doivent être vraies pour qu'un
enregistrement soit sélectionné. Par conséquent, seuls les employés dont l'intitulé de
poste contient la chaîne 'MAN' et qui gagnent au moins 10 000 $ sont sélectionnés.
Etant donné que toutes les recherches sur les chaînes de caractères distinguent les
majuscules des minuscules, aucune ligne n'est renvoyée si 'MAN' n'est pas en
majuscules. Par ailleurs, les chaînes de caractères doivent être indiquées entre
apostrophes.
2.13 Utiliser l'opérateur OR
SELECT employee_id, last_name, job_id, salary
FROM
employees
WHERE salary >= 10000
OR
job_id LIKE '%MAN%' ;
Utiliser l'opérateur OR
Dans l'exemple ci-dessus, il suffit que l'une ou l'autre des conditions soit vraie pour
qu'un enregistrement soit sélectionné. Par conséquent, tout employé dont l'ID de
poste contient la chaîne 'MAN' ou qui gagne au moins 10 000 $ est sélectionné.
2.14 Utiliser l'opérateur NOT
SELECT last_name, job_id
FROM
employees
WHERE job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;
Utiliser l'opérateur NOT
L'exemple de la diapositive ci-dessus affiche le nom et l'ID de poste de tous les
employés dont l'ID de poste n'est pas IT_PROG, ST_CLERK ou SA_REP.
Table de vérité de l'opérateur NOT
Remarque : L'opérateur NOT peut aussi être utilisé avec d'autres opérateurs SQL,
tels que BETWEEN, LIKE et NULL.
... WHERE
... WHERE
... WHERE
... WHERE
job_id NOT IN ('AC_ACCOUNT', 'AD_VP')
salary NOT BETWEEN 10000 AND 15000
last_name NOT LIKE '%A%'
commission_pct IS NOT NULL
14
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
2.15 Règles de priorité
Règles de priorité
Les règles de priorité déterminent l'ordre dans lequel les expressions sont évaluées
et calculées. Le tableau ci-dessus indique l'ordre de priorité par défaut. Toutefois,
vous pouvez modifier cet ordre par défaut en plaçant des parenthèses autour des
expressions à calculer en premier.
2.16 Règles de priorité
SELECT
FROM
WHERE
OR
AND
last_name, job_id, salary
employees
job_id = 'SA_REP'
job_id = 'AD_PRES'
salary > 15000;
SELECT last_name, job_id, salary
FROM
employees
WHERE (job_id = 'SA_REP'
OR
job_id = 'AD_PRES')
AND
salary > 15000;
Règles de priorité
Priorité de l'opérateur AND : Exemple
Cet exemple comprend deux conditions :
•La première est que l'ID de poste soit égal à AD_PRES et que le salaire soit
supérieur à 15 000 $.
•La deuxième est que l'ID de poste soit égal à SA_REP.
Par conséquent, l'instruction SELECT se lit comme suit :
"Sélectionner la ligne si un employé est président et gagne plus de 15 000 $, ou si
l'employé est vendeur".
Utilisation des parenthèses : Exemple
Cet exemple comprend deux conditions :
•La première est que l'ID de poste soit égal à SA_REP ou SA_REP.
15
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
•La deuxième est que le salaire soit supérieur à 15 000 $.
Par conséquent, l'instruction SELECT se lit comme suit :
"Sélectionner la ligne si un employé est président ou vendeur, et si l'employé gagne
plus de 15 000 $".
2.17 Utiliser la clause ORDER BY
SELECT
last_name, job_id, department_id, hire_date
FROM
employees
ORDER BY hire_date ;
Utiliser la clause ORDER BY
Les lignes renvoyées dans le résultat d'une interrogation n'ont pas d'ordre spécifique.
Pour les trier, vous pouvez utiliser la clause ORDER BY. Celle-ci doit être la dernière
clause de l'instruction SQL. Vous pouvez également indiquer une expression, un
alias ou une position de colonne comme condition de tri.
Syntaxe
SELECT
expr
FROM
table
[WHERE
condition(s)]
[ORDER BY {column, expr, numeric_position} [ASC|DESC]];
Dans la syntaxe :
ORDER BY
Indique l'ordre dans lequel les lignes extraites s'affichent.
ASC
Trie les lignes par ordre croissant (ordre par défaut).
DESC
Trie les lignes par ordre décroissant.
Lorsque la clause ORDER BY n'est pas utilisée, l'ordre de tri n'est pas défini et le
serveur Oracle peut extraire les lignes dans des ordres différents pour plusieurs
exécutions d'une même interrogation. Utilisez la clause ORDER BY pour afficher les
lignes dans un ordre spécifique.
Remarque : Utilisez les mots-clés NULLS FIRST ou NULLS LAST pour indiquer si
les lignes renvoyées contenant des valeurs NULL doivent apparaître en premier ou
en dernier dans la séquence de tri.
2.18 Tri
SELECT
last_name, job_id, department_id, hire_date
FROM
employees
ORDER BY hire_date DESC ;
SELECT employee_id, last_name, salary*12 annsal
FROM
employees
ORDER BY annsal ;
Tri
Par défaut, le tri est effectué par ordre croissant :
•Les valeurs numériques sont affichées de la plus petite à la plus grande (par
exemple, de 1 à 999).
•Les dates sont affichées de la plus ancienne à la plus récente (par exemple, 01JAN-92 avant 01-JAN-95).
•Les chaînes de caractères sont affichées par ordre alphabétique (par exemple, de A
à Z).
16
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
•Les valeurs NULL sont affichées en dernier pour les séquences croissantes et en
premier pour les séquences décroissantes.
•Vous pouvez aussi effectuer le tri sur la base d'une colonne ne figurant pas dans la
liste SELECT.
Exemples :
Pour inverser l'ordre d'affichage des lignes, indiquez le mot-clé DESC après le nom
de colonne dans la clause ORDER BY. L'exemple de la diapositive ci-dessus trie les
résultats en fonction de la date d'embauche des employés, de la plus récente à la
plus ancienne.
Vous pouvez aussi utiliser un alias de colonne dans la clause ORDER BY. L'exemple
ci-dessus trie les données selon la rémunération annuelle.
2.19 Utiliser une variable de substitution avec esperluette simple
SELECT employee_id, last_name, salary, department_id
FROM
employees
WHERE employee_id = &employee_num ;
Utiliser une variable de substitution avec esperluette simple
Lors de l'exécution d'un état, les utilisateurs ont souvent besoin de restreindre les
données renvoyées de façon dynamique. SQL*Plus ou SQL Developer fournit cette
souplesse avec des variables utilisateur. Utilisez une esperluette d'interprétation (&)
pour identifier chaque variable dans votre instruction SQL. Vous n'avez pas besoin
de définir la valeur de chaque variable.
L'exemple ci-dessus crée une variable de substitution SQL Developer pour un ID
d'employé. Lorsque l'instruction est exécutée, SQL Developer invite l'utilisateur à
indiquer un ID d'employé, puis il affiche l'ID d'employé, le nom, le salaire et le
numéro de département de cet employé.
Avec l'esperluette simple, l'utilisateur reçoit une invite à chaque exécution de la
commande si la variable n'existe pas.
2.20 Chaînes de caractères et dates avec variables de substitution
SELECT last_name, department_id, salary*12
FROM
employees
WHERE job_id = '&job_title' ;
Chaînes de caractères et dates avec variables de substitution
Dans une clause WHERE, les dates et les chaînes de caractères doivent être
placées entre apostrophes. La même règle s'applique aux variables de substitution.
Placez chaque variable entre apostrophes au sein de l'instruction SQL.
La diapositive ci-dessus présente une interrogation permettant d'extraire le nom, le
numéro de département et la rémunération annuelle de tous les employés sur la
17
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
base de la valeur d'intitulé de poste, qui est associée à une variable de substitution
SQL Developer.
2.21 Indiquer des noms de colonne, des expressions et du texte
SELECT employee_id, last_name, job_id,&column_name
FROM
employees
WHERE &condition
ORDER BY &order_column ;
Indiquer des noms de colonne, des expressions et du texte
Vous pouvez utiliser les variables de substitution non seulement dans la clause
WHERE d'une instruction SQL, mais aussi en substitution de noms de colonne,
d'expressions ou de texte.
Exemple :
L'exemple de la diapositive ci-dessus extrait de la table EMPLOYEES l'ID d'employé,
le nom, l'intitulé de poste et toute autre colonne indiquée par l'utilisateur lors de
l'exécution.
Pour chaque variable de substitution figurant dans l'instruction SELECT, vous êtes
invité
à saisir une valeur, puis à cliquer sur OK pour continuer.
Si vous n'entrez pas de valeur pour la variable de substitution, vous obtenez une
erreur lors
de l'exécution de l'instruction précédente.
Remarque : Une variable de substitution peut être utilisée n'importe où dans
l'instruction SELECT, sauf en première position à l'invite de commande.
2.22 Utiliser une variable de substitution avec esperluette double
SELECT
employee_id, last_name, job_id, &&column_name
FROM
employees
ORDER BY &column_name ;
Utiliser une variable de substitution avec esperluette double
Vous pouvez utiliser une variable de substitution avec esperluette d'interprétation
double (&&) si vous souhaitez réutiliser la valeur de la variable sans solliciter
l'utilisateur à chaque fois. L'utilisateur est invité une seule fois à saisir la valeur. Dans
l'exemple ci-dessus, l'utilisateur est invité à fournir la valeur de la variable
column_name une seule fois. La valeur fournie par l'utilisateur (department_id) est
utilisée à la fois pour l'affichage et pour le tri des données. Si vous exécutez à
nouveau l'interrogation, vous n'êtes pas invité à fournir la valeur de la variable.
SQL Developer stocke la valeur fournie à l'aide de la commande DEFINE et la
réutilise chaque fois que vous référencez le nom de la variable. Une fois qu'une
variable utilisateur est en place, vous devez utiliser la commande UNDEFINE pour la
supprimer : UNDEFINE column_name
2.23 Utiliser la commande DEFINE
DEFINE employee_num = 200
SELECT employee_id, last_name, salary, department_id
FROM
employees
WHERE employee_id = &employee_num ;
18
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
UNDEFINE employee_num
Utiliser la commande DEFINE
L'exemple ci-dessus crée une variable de substitution correspondant à un ID
d'employé à l'aide de la commande DEFINE. L'exécution de cette interrogation
entraîne l'affichage de l'ID d'employé, du nom, du salaire et du numéro de
département de cet employé.
La variable étant créée à l'aide de la commande DEFINE de SQL Developer,
l'utilisateur n'est pas invité à saisir une valeur pour l'ID d'employé. La valeur définie
pour la variable est substituée automatiquement dans l'instruction SELECT.
La variable de substitution EMPLOYEE_NUM reste présente dans la session jusqu'à
ce que l'utilisateur annule sa définition ou quitte la session SQL Developer.
2.24 Utiliser la commande VERIFY
SET VERIFY ON
SELECT employee_id, last_name, salary
FROM
employees
WHERE employee_id = &employee_num;
Utiliser la commande VERIFY
Pour vérifier les modifications d'une instruction SQL, utilisez la commande VERIFY.
L'option SET VERIFY ON force SQL Developer à afficher le texte d'une commande
après le remplacement des variables de substitution par des valeurs. Pour voir le
résultat de la commande VERIFY, utilisez l'icône Run Script (F5) dans SQL
Worksheet. Dans l'onglet Script Output, SQL Developer affiche le texte de la
commande après remplacement des variables de substitution par des valeurs,
comme illustré dans la diapositive ci-dessus.
L'exemple de la diapositive affiche la nouvelle valeur de la colonne EMPLOYEE_ID
dans l'instruction SQL, suivie du résultat.
Variables système SQL*Plus
SQL*Plus utilise différentes variables système contrôlant l'environnement de travail.
L'une de ces variables est VERIFY. Pour obtenir la liste complète des variables
système, vous pouvez exécuter la commande SHOW ALL à l'invite de commande
SQL*Plus.
19
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
3 Guide Exercice 3
3.1
Fonctions de type caractère
Les fonctions monolignes de type caractère acceptent les entrées de type caractère,
et peuvent renvoyer des valeurs de type caractère ou des valeurs numériques. Les
fonctions
de type caractère se divisent comme suit :
•Fonctions de conversion de casse
•Fonctions de manipulation de caractères
20
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Remarque : Voici certaines des fonctions entièrement ou partiellement compatibles
avec SQL:2003 :
UPPER
LOWER
TRIM
LENGTH
SUBSTR
INSTR
3.2
Fonctions de conversion de casse
Fonctions de conversion de casse
LOWER, UPPER et INITCAP sont les trois fonctions de conversion de casse.
•LOWER : Convertit en minuscules les chaînes de caractères en majuscules ou à
casse mixte.
•UPPER : Convertit en majuscules les chaînes de caractères en minuscules ou à
casse mixte.
21
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
•INITCAP : Convertit la première lettre de chaque mot en majuscules et les autres
lettres en minuscules.
SELECT 'The job id for '||UPPER(last_name)||' is '
||LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;
3.3
Utiliser les fonctions de conversion de casse
SELECT employee_id, last_name, department_id
FROM
employees
WHERE last_name = 'higgins';
SELECT employee_id, last_name, department_id
FROM
employees
WHERE LOWER(last_name) = 'higgins';
Utiliser les fonctions de conversion de casse
L'exemple ci-dessus affiche le numéro d'employé, le nom et le numéro de
département de l'employé Higgins.
La clause WHERE de la première instruction SQL indique le nom d'employé sous la
forme higgins. Puisque toutes les données de la table EMPLOYEES sont stockées
dans la casse appropriée, aucune correspondance n'est trouvée pour le nom higgins
et aucune ligne n'est sélectionnée.
La clause WHERE de la deuxième instruction SQL compare les noms d'employé de
la table EMPLOYEES avec la chaîne higgins après conversion de la colonne
LAST_NAME en minuscules. Les noms étant maintenant en minuscules, une
correspondance est trouvée et une ligne est sélectionnée. La clause WHERE peut
être réécrite de la manière suivante afin de produire le même résultat :
...WHERE last_name = 'Higgins'
Le nom apparaît dans le résultat tel qu'il a été stocké dans la base de données. Pour
afficher le nom en majuscules, utilisez la fonction UPPER dans l'instruction SELECT.
SELECT employee_id, UPPER(last_name), department_id
FROM employees
WHERE INITCAP(last_name) = 'Higgins';
22
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
3.4
Fonctions de manipulation de caractères
Fonctions de manipulation des caractères
Les fonctions de manipulation de caractères traitées dans ce chapitre sont CONCAT,
SUBSTR, LENGTH, INSTR, LPAD, RPAD et TRIM.
•CONCAT : Joint des valeurs. (Vous ne pouvez utiliser que deux paramètres avec
CONCAT.)
•SUBSTR : Extrait une chaîne d'une longueur déterminée.
•LENGTH : Affiche la longueur d'une chaîne sous la forme d'une valeur numérique.
•INSTR : Recherche la position numérique d'un caractère.
•LPAD : Renvoie une expression complétée à gauche par une expression de type
caractère jusqu'à une longueur de n caractères.
•RPAD : Renvoie une expression complétée à droite par une expression de type
caractère jusqu'à une longueur de n caractères.
•TRIM : Tronque les caractères de début ou de fin (ou les deux) d'une chaîne de
caractères. (Si le paramètre trim_character ou trim_source est un littéral de type
caractère, vous devez le mettre entre apostrophes.)
Remarque : Les fonctions telles que UPPER et LOWER admettent les variables de
substitution avec esperluette. Par exemple, utilisez UPPER('&job_title')afin que
l'utilisateur n'ait pas besoin de saisir l'intitulé du poste dans une casse spécifique.
3.5
Utiliser les fonctions de manipulation de caractères
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM
employees
WHERE SUBSTR(job_id, 4) = 'REP';
Utiliser les fonctions de manipulation de caractères
L'exemple de la diapositive ci-dessus affiche la concaténation du nom et du prénom
d'employé, la longueur du nom d'employé, et la position numérique de la lettre "a"
23
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
dans le nom d'employé pour tous les employés pour lesquels la chaîne REP apparaît
à partir de la quatrième position dans l'ID de poste.
Exemple :
Modifiez l'instruction SQL de la diapositive ci-dessus de façon à afficher les données
des employés dont le nom finit par la lettre "n".
SELECT employee_id, CONCAT(first_name, last_name) NAME,
LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(last_name, -1, 1) = 'n';
3.6
Fonctions numériques
Fonctions numériques
Les fonctions numériques acceptent les entrées de type numérique et renvoient des
valeurs numériques. La présente section décrit quelques-unes de ces fonctions.
3.7
Utiliser la fonction ROUND
SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM
DUAL;
Utiliser la fonction ROUND
La fonction ROUND arrondit la colonne, l'expression ou la valeur à n décimales. Si le
deuxième argument est 0 ou n'est pas indiqué, la valeur est arrondie à un nombre
entier, sans chiffre après le séparateur décimal. Si le deuxième argument est 2, la
valeur est arrondie à deux décimales. A l'inverse, si le deuxième argument est –2, la
valeur est arrondie à deux chiffres à gauche de la virgule (arrondie à la plus proche
unité de 100).
La fonction ROUND peut également être utilisée avec les fonctions de date. Des
exemples sont proposés plus loin dans ce chapitre.
Table DUAL
La table DUAL, qui appartient à l'utilisateur SYS, est accessible par tous les
utilisateurs. Elle contient une seule colonne, nommée DUMMY, et une seule ligne
comportant la valeur X. La table DUAL est utile lorsque vous souhaitez renvoyer une
valeur une fois seulement (par exemple la valeur d'une constante, d'une pseudocolonne ou d'une expression qui n'est pas issue d'une table comportant des données
utilisateur). La table DUAL est généralement utilisée pour compléter la syntaxe de la
clause SELECT, car les clauses SELECT et FROM sont obligatoires, et certains
calculs n'ont pas besoin de sélection dans les tables réelles.
24
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
3.8
Utiliser la fonction TRUNC
SELECT TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923,-1)
FROM
DUAL;
Utiliser la fonction TRUNC
La fonction TRUNC tronque la colonne, l'expression ou la valeur à n décimales.
Les arguments de la fonction TRUNC sont semblables à ceux de la fonction
ROUND. Si le deuxième argument est 0 ou n'est pas indiqué, la valeur est tronquée
au nombre entier (sans chiffre après le séparateur décimal). Si le deuxième
argument est 2, la valeur est tronquée à deux décimales. A l'inverse, si le deuxième
argument est –2, la valeur est tronquée à deux chiffres à gauche de la virgule. Si le
deuxième argument est –1, la valeur est tronquée à un chiffre à gauche de la virgule.
Tout comme la fonction ROUND, la fonction TRUNC peut être utilisée avec des
fonctions de date.
3.9
Utiliser la fonction MOD
SELECT last_name, salary, MOD(salary, 5000)
FROM
employees
WHERE job_id = 'SA_REP';
Utiliser la fonction MOD
La fonction MOD calcule le reste de la division du premier argument par le deuxième.
L'exemple de la diapositive ci-dessus calcule le reste du salaire après la division de
ce dernier par 5 000 pour tous les employés dont l'ID de poste est SA_REP.
Remarque : La fonction MOD est souvent utilisée pour déterminer si une valeur est
paire ou impaire.
3.10 Utiliser des dates
SELECT last_name, hire_date
FROM
employees
WHERE hire_date < '01-FEB-88';
Utiliser des dates
La base de données Oracle stocke les dates selon un format numérique interne,
représentant le siècle, l'année, le mois, le jour, les heures, les minutes et les
secondes.
Le format par défaut de saisie et d'affichage de la date est DD-MON-RR. Les dates
Oracle valides sont comprises entre le 1er janvier 4712 avant J-C et le 31 décembre
9999
après J-C.
Dans l'exemple de la diapositive ci-dessus, la colonne de résultat HIRE_DATE est
affichée
au format par défaut DD-MON-RR. Toutefois, dans la base de données, les dates ne
sont pas stockées selon ce format. Tous les composants de date et d'heure sont
enregistrés. Ainsi, même si une date HIRE_DATE telle que 17-JUN-87 est affichée
sous la forme jour, mois et année, les informations relatives à l'heure et au siècle lui
sont également associées. Les données complètes sont donc June 17, 1987,
5:10:43 PM.
25
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
3.11 Format de date RR
Format de date RR
Le format de date RR est similaire au format YY, à ceci près que vous pouvez
l'utiliser pour indiquer différents siècles. Utilisez le format RR à la place du format YY
pour que le siècle de la valeur renvoyée varie en fonction de l'année à deux chiffres
indiquée et des deux derniers chiffres de l'année en cours. Le tableau de la
diapositive ci-dessus récapitule le comportement du format RR.
3.12 Utiliser la fonction SYSDATE
SELECT sysdate
FROM
dual;
Utiliser la fonction SYSDATE
SYSDATE est une fonction de date qui renvoie la date du jour et l'heure actuelle du
serveur de base de données. Vous pouvez l'utiliser comme n'importe quel autre nom
de colonne.
Par exemple, vous pouvez afficher la date du jour en sélectionnant SYSDATE dans
une table. Il est de règle de sélectionner SYSDATE à partir d'une table factice
nommée DUAL.
Remarque : SYSDATE renvoie la date du jour et l'heure actuelle pour le système
d'exploitation sur lequel réside la base de données. Par conséquent, si vous vous
trouvez en Australie tout en étant connecté à une base distante située aux EtatsUnis, la fonction sysdate renvoie la date et l'heure des Etats-Unis. Dans ce cas, vous
pouvez utiliser la fonction CURRENT_DATE qui renvoie la date du jour selon le
fuseau horaire de la session.
3.13 Utiliser des opérateurs arithmétiques avec des dates
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM
employees
WHERE department_id = 90;
Utiliser des opérateurs arithmétiques avec des dates
L'exemple ci-dessus affiche le nom et le nombre de semaines d'ancienneté pour tous
les employés du département 90. Il soustrait la date à laquelle l'employé a été
26
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
embauché de la date du jour (SYSDATE) et divise le résultat par 7 pour calculer le
nombre de semaines d'ancienneté d'un employé.
Remarque : SYSDATE est une fonction SQL qui renvoie la date du jour et l'heure
actuelle. Vos résultats peuvent différer selon la date et l'heure définies pour le
système d'exploitation de votre base de données locale lorsque vous exécutez
l'interrogation SQL.
Lorsqu'une date est soustraite d'une date plus ancienne, la différence est un nombre
négatif.
3.14 Fonctions de manipulation de dates
Fonctions de manipulation de dates
Les fonctions de type date opèrent sur des dates Oracle. Toutes les fonctions de
date renvoient une valeur dont le type de données est DATE, à l'exception de
MONTHS_BETWEEN qui renvoie une valeur numérique.
•MONTHS_BETWEEN(date1, date2): Recherche le nombre de mois entre date1 et
date2. Le résultat peut être positif ou négatif. Si date1 est postérieure à date2, le
résultat est positif. Si date1 est antérieure à date2, le résultat est négatif. La partie
non entière du résultat représente une partie du mois.
•ADD_MONTHS(date, n) : Ajoute n mois calendaires à date. La valeur de n doit être
entière et peut être négative.
•NEXT_DAY(date, 'char') : Recherche la date du jour de la semaine ('char') suivant
date. La valeur de char peut être un nombre représentant un jour, ou bien une
chaîne de caractères.
•LAST_DAY(date) : Recherche la date du dernier jour du mois contenant date.
La liste ci-dessus n'est qu'un sous-ensemble des fonctions de type date disponibles.
Les fonctions numériques ROUND et TRUNC peuvent aussi être utilisées pour
manipuler des valeurs de type date, comme illustré ci-après.
•ROUND(date[,'fmt']) : Renvoie la date arrondie à l'unité indiquée par le modèle de
format fmt. Si le modèle de format fmt est omis, date est arrondie au jour le plus
proche.
•TRUNC(date[, 'fmt']) : Renvoie date avec la partie heure du jour tronquée à l'unité
indiquée par le modèle de format fmt. Si le modèle de format fmt est omis, date est
tronquée au jour le plus proche.
Les modèles de format sont traités en détail dans le chapitre "Utiliser des fonctions
de conversion et des expressions conditionnelles".
27
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
3.15 Utiliser les fonctions de type date
Utiliser les fonctions de type date
Dans l'exemple ci-dessus, la fonction ADD_MONTHS ajoute un mois à la valeur de
date fournie, "31-JAN-96", et renvoie "29-FEB-96". La fonction reconnaît 1996
comme une année bissextile et renvoie donc le dernier jour du mois de février. Si
vous remplacez la valeur de date d'entrée par "31-JAN-95", la fonction renvoie "28FEB-95".
Par exemple, affichez le numéro d'employé, la date d'embauche, le nombre de mois
d'ancienneté, la date d'évaluation à six mois, le premier vendredi suivant la date
d'embauche et le dernier jour du mois d'embauche pour tous les employés dont
l'ancienneté est inférieure à 100 mois.
SELECT employee_id, hire_date,
MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
ADD_MONTHS (hire_date, 6) REVIEW,
NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 100;
3.16 Utiliser les fonctions ROUND et TRUNC avec des dates
Utiliser les fonctions ROUND et TRUNC avec des dates
Il est possible d'utiliser les fonctions ROUND et TRUNC pour des valeurs
numériques et des valeurs de date. Dans le cas de dates, ces fonctions procèdent à
un arrondi ou à une troncature selon le modèle de format indiqué. Vous pouvez ainsi
arrondir des dates à l'année ou au mois le plus proche. Si le modèle de format est
month, les dates comprises entre 1 et 15 renvoient le premier jour du mois en cours.
Les dates comprises entre 16 et 31 renvoient le premier jour du mois suivant. Si le
modèle de format est year, les mois 1 à 6 renvoient le 1er janvier de l'année en
cours. Les mois 7 à 12 renvoient le 1er janvier de l'année suivante.
Exemple :
Comparez les dates d'embauche de tous les employés embauchés en 1997. Affichez
le numéro d'employé, la date d'embauche et le mois d'embauche à l'aide des
fonctions ROUND et TRUNC.
28
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH'), TRUNC(hire_date,
'MONTH') FROM employees
WHERE hire_date LIKE '%97';
4 Guide Exercice 4
Utiliser des fonctions de conversion
et des expressions conditionnelles
4.1
Utiliser la fonction TO_CHAR avec des dates
TO_CHAR(date, 'format_model')
Utiliser la fonction TO_CHAR avec des dates
TO_CHAR convertit un type de données date-heure en valeur de type VARCHAR2
au format indiqué par format_model. Un modèle de format est un littéral de type
caractère qui décrit le format de date-heure stocké dans une chaîne de caractères.
Par exemple, le modèle de format de la chaîne '11-Nov-1999' est 'DD-Mon-YYYY'.
Vous pouvez utiliser la fonction TO_CHAR pour convertir une date de son format par
défaut dans un format que vous indiquez.
Règles
•Le modèle de format doit être inclus entre apostrophes. Il distingue les majuscules
des minuscules.
•Il peut inclure n'importe quel élément de format de date valide. Veillez toutefois à
séparer la valeur de date du modèle de format par une virgule.
•Dans le résultat, les noms des jours et des mois sont automatiquement complétés
par des espaces.
•Pour supprimer les espaces de complément ou les zéros de début, utilisez l'élément
de mode de remplissage fm.
SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM employees
WHERE last_name = 'Higgins';
4.2
Eléments du modèle de format de date
4.3
Utiliser la fonction TO_CHAR avec des dates
SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATE
29
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
FROM
employees;
Utiliser la fonction TO_CHAR avec des dates
L'instruction SQL de la diapositive ci-dessus affiche le nom et la date d'embauche de
tous les employés. La date d'embauche apparaît sous la forme 17 June 1987.
Exemple :
Modifiez l'exemple de la diapositive pour afficher les dates dans un format tel que
"Seventeenth of June 1987 12:00:00 AM".
SELECT last_name,
TO_CHAR(hire_date,
'fmDdspth "of" Month YYYY fmHH:MI:SS AM')
HIREDATE
FROM employees;
4.4
Utiliser la fonction TO_CHAR avec des nombres
TO_CHAR(number, 'format_model')
Utiliser la fonction TO_CHAR avec des nombres
Lorsque vous utilisez des valeurs numériques telles que des chaînes de caractères,
vous devez convertir ces valeurs en données alphanumériques en utilisant la
fonction TO_CHAR. Cette fonction convertit une valeur de type NUMBER en une
valeur de type VARCHAR2. Cette technique est particulièrement utile pour la
concaténation.
4.5
Utiliser la fonction TO_CHAR avec des nombres
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM
employees
WHERE last_name = 'Ernst';
Utiliser la fonction TO_CHAR avec des nombres (suite)
•Le serveur Oracle affiche une chaîne de dièses (#) à la place du nombre complet
lorsque le nombre de chiffres dépasse le nombre indiqué dans le modèle de format.
•Le serveur Oracle arrondit la valeur décimale stockée au nombre de décimales
indiqué dans le modèle de format.
4.6
Utiliser les fonctions TO_NUMBER et TO_DATE
30
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, 'format_model'])
Utiliser les fonctions TO_NUMBER et TO_DATE
Vous pouvez convertir une chaîne de caractères en nombre ou en date. Pour cela,
utilisez les fonctions TO_NUMBER ou TO_DATE. Le modèle de format que vous
sélectionnez est basé sur les éléments décrits précédemment.
Le modificateur fx indique qu'il doit y avoir une correspondance exacte entre
l'argument de type caractère et le modèle de format de date d'une fonction
TO_DATE :
•La ponctuation et le texte indiqués entre apostrophes dans l'argument de type
caractère doivent être similaires (à l'exception de la casse) aux parties
correspondantes du modèle de format.
•L'argument de type caractère ne peut pas comporter d'espaces supplémentaires.
Sans le modificateur fx, le serveur Oracle ignore les espaces supplémentaires.
•Les données numériques de l'argument de type caractère doivent comporter le
même nombre de chiffres que l'élément correspondant du modèle de format. Sans le
modificateur fx, les zéros de début des nombres de l'argument peuvent être omis.
4.7
Fonctions imbriquées
SELECT last_name,
UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
FROM
employees
WHERE department_id = 60;
Fonctions imbriquées
L'exemple ci-dessus affiche le nom des employés du département 60. L'évaluation
de l'instruction SQL comprend trois étapes :
1.
La fonction interne extrait les huit premiers caractères du nom.
Result1 = SUBSTR (LAST_NAME, 1, 8)
2.
La fonction externe concatène le résultat avec _US.
Result2 = CONCAT(Result1, '_US')
3.
La fonction la plus à l'extérieur convertit les résultats en majuscules.
L'expression entière devient l'en-tête de colonne car aucun alias de colonne n'a été
fourni.
Exemple :
Affichez la date du vendredi situé à six mois de la date d'embauche. La date obtenue
doit être Friday, August 13th, 1999. Triez les résultats par date d'embauche.
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS
(hire_date, 6), 'FRIDAY'),
'fmDay, Month ddth, YYYY')
"Next 6 Month Review"
FROM
employees
ORDER BY hire_date;
4.8
Utiliser la fonction NVL
SELECT last_name, salary, NVL(commission_pct, 0),
31
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;
Utiliser la fonction NVL
Pour calculer la rémunération annuelle de tous les employés, vous devez multiplier le
salaire mensuel par 12, puis ajouter le pourcentage de commission au résultat :
SELECT last_name, salary, commission_pct,
(salary*12) + (salary*12*commission_pct) AN_SAL
FROM employees;
4.9
Utiliser la fonction NVL2
SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM
employees WHERE department_id IN (50, 80);
Utiliser la fonction NVL2
La fonction NVL2 examine la première expression. Si sa valeur n'est pas NULL, la
fonction NVL2 renvoie la valeur de la deuxième expression. Sinon, elle renvoie la
valeur de la troisième expression.
Syntaxe
NVL2(expr1, expr2, expr3)
Dans la syntaxe :
•expr1 est l'expression ou la valeur source qui peut contenir une valeur NULL.
•expr2 est la valeur qui est renvoyée si la valeur de expr1 n'est pas NULL.
•Expr3 est la valeur qui est renvoyée si la valeur de expr1 est NULL.
L'exemple de la diapositive ci-dessus examine la colonne COMMISSION_PCT. Si
une valeur est détectée, la valeur renvoyée est celle de la deuxième expression,
SAL+COMM. Si la colonne COMMISSION_PCT contient une valeur NULL, la valeur
renvoyée est celle de la troisième expression, SAL.
L'argument expr1 peut présenter n'importe quel type de données. Les arguments
expr2 et expr3 peuvent avoir n'importe quel type de données sauf LONG. Si les
types de données de expr2 et expr3 sont différents, le serveur Oracle convertit expr3
vers le type de données de expr2 avant comparaison, sauf si expr3 est une
constante NULL. Dans ce dernier cas, il n'y a pas besoin de conversion de type de
données. Le type de données de la valeur renvoyée est toujours identique à celui de
expr2, sauf si expr2 contient des données alphanumériques, auquel cas la valeur
renvoyée est de type VARCHAR2.
4.10 Utiliser la fonction NULLIF
SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM
employees;
Utiliser la fonction NULLIF
La fonction NULLIF compare deux expressions. Si elles sont égales, la fonction
renvoie une valeur NULL. Sinon, elle renvoie la valeur de la première expression.
32
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Toutefois, vous ne pouvez pas indiquer la valeur littérale NULL pour la première
expression.
Syntaxe
NULLIF (expr1, expr2)
Dans la syntaxe :
•NULLIF compare les expressions expr1 et expr2. Si elles sont égales, la fonction
renvoie une valeur NULL. Sinon, elle renvoie la valeur de expr1. Toutefois, vous ne
pouvez pas indiquer la valeur littérale NULL pour expr1.
Dans l'exemple de la diapositive ci-dessus, la longueur du prénom dans la table
EMPLOYEES est comparée à la longueur du nom dans la même table. Si ces deux
longueurs sont égales, une valeur NULL est affichée. Sinon, la longueur du prénom
est affichée.
Remarque : La fonction NULLIF est logiquement équivalente à l'expression CASE
ciaprès. L'expression CASE sera traitée dans une page ultérieure.
CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
4.11 Utiliser la fonction COALESCE
SELECT last_name, employee_id,
COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),
'No commission and no manager')
FROM employees;
Utiliser la fonction COALESCE (suite)
Dans l'exemple ci-dessus, la valeur de manager_id est affichée si elle n'est pas
NULL. Si elle est NULL, c'est la valeur de commission_pct qui est affichée, si ellemême n'est pas NULL. Si les valeurs de manager_id et commission_pct sont toutes
deux NULL, le message "No commission and no manager" apparaît. Notez que la
fonction TO_CHAR est appliquée de sorte que toutes les expressions présentent le
même type de données.
4.12 Utiliser l'expression CASE
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP'
THEN 1.20*salary
ELSE
salary END
"REVISED_SALARY"
FROM
employees;
Utiliser l'expression CASE
L'instruction SQL de la diapositive ci-dessus teste la valeur de JOB_ID. Si cette
valeur est égale à IT_PROG, l'augmentation de salaire est de 10 %. Si elle est égale
à ST_CLERK, l'augmentation de salaire est de 15 %. Si elle est égale à SA_REP,
l'augmentation de salaire est de 20 %. Pour tous les autres postes, il n'y a aucune
augmentation de salaire.
La même instruction peut être écrite avec la fonction DECODE.
Voici un exemple d'expression de recherche utilisant la clause CASE. Dans une telle
expression, la recherche s'effectue de gauche à droite jusqu'à ce qu'une occurrence
de la condition indiquée soit trouvée. L'expression de retour est alors renvoyée. Si
33
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
aucune condition n'est vérifiée et qu'il existe une clause ELSE, c'est l'expression de
retour figurant dans cette clause qui est renvoyée. Sinon, c'est une valeur NULL.
SELECT last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN salary<10000 THEN 'Medium'
WHEN salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
4.13 Fonction DECODE
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP',
1.20*salary,
salary)
REVISED_SALARY
FROM
employees;
Utiliser la fonction DECODE
L'instruction SQL de la diapositive ci-dessus teste la valeur de JOB_ID. Si cette
valeur est égale à IT_PROG, l'augmentation de salaire est de 10 %. Si elle est égale
à ST_CLERK, l'augmentation de salaire est de 15 %. Si elle est égale à SA_REP,
l'augmentation de salaire est de 20 %. Pour tous les autres postes, il n'y a aucune
augmentation de salaire.
La même instruction peut être exprimée en pseudocode sous la forme d'une
instruction
IF-THEN-ELSE :
IF job_id = 'IT_PROG' THEN salary = salary*1.10
IF job_id = 'ST_CLERK' THEN salary = salary*1.15
IF job_id = 'SA_REP'
THEN salary = salary*1.20
ELSE salary = salary
4.14 Utiliser la fonction DECODE
SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM
employees
WHERE department_id = 80;
Utiliser la fonction DECODE
La diapositive ci-dessus présente un autre exemple d'utilisation de la fonction
DECODE.
34
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Dans cet exemple, vous déterminez le taux d'imposition de chaque employé du
département 80 sur la base du salaire mensuel. Les taux d'imposition sont les
suivants :
Plage de salaires mensuels
Taux d'imposition
De 0 à 1 999,99 $
0%
De 2 000 à 3 999,99 $
9%
De 4 000 à 5 999,99 $
20 %
De 6 000 à 7 999,99 $
30 %
De 8 000 à 9 999,99 $
40 %
De 10 000 à 11 999,99 $
42 %
De 12 200 à 13 999,99 $
44 %
14 000 $ et plus
45 %
5 Guide Exercice 5
Créer un état avec des données agrégées
à l'aide des fonctions de groupe
5.1
Fonctions de groupe : Syntaxe
SELECT
group_function(column), ...
FROM
table
[WHERE
condition]
[ORDER BY column];
Fonctions de groupe : Syntaxe
La fonction de groupe est placée après le mot-clé SELECT. Lorsque plusieurs
fonctions de groupe sont indiquées, elles doivent être séparées par des virgules.
Règles d'utilisation des fonctions de groupe :
•Avec le mot-clé DISTINCT, la fonction considère seulement les valeurs qui sont
uniques. Avec le mot-clé ALL, elle prend toutes les valeurs en compte, y compris les
valeurs en double. Le mot-clé par défaut est ALL (il est donc inutile de l'indiquer).
•Les types de données des fonctions dotées d'un argument expr peuvent être CHAR,
VARCHAR2, NUMBER ou DATE.
•Toutes les fonctions de groupe ignorent les valeurs NULL. Pour remplacer les
valeurs par des valeurs réelles, utilisez les fonctions NVL, NVL2 ou COALESCE.
5.2
Utiliser les fonctions AVG et SUM
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM
employees
35
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
WHERE
job_id LIKE '%REP%';
Utiliser les fonctions AVG et SUM
Vous pouvez utiliser les fonctions AVG, SUM, MIN et MAX sur les colonnes qui
peuvent stocker des données numériques. L'exemple ci-dessus affiche la moyenne
et la somme des salaires mensuels de tous les commerciaux, ainsi que le salaire le
plus élevé et le salaire le plus bas.
5.3
Utiliser les fonctions MIN et MAX
SELECT MIN(hire_date), MAX(hire_date)
FROM
employees;
Utiliser les fonctions MIN et MAX
Vous pouvez utiliser les fonctions MAX et MIN pour les données de type nombre,
caractère et date. L'exemple de la diapositive ci-dessus affiche les employés
présentant la plus faible et la plus grande ancienneté.
L'exemple suivant affiche les noms du premier et du dernier employé dans la liste
alphabétique de tous les employés :
SELECT MIN(last_name), MAX(last_name)
FROM employees;
Remarque : Les fonctions AVG, SUM, VARIANCE et STDDEV ne peuvent être
utilisées qu'avec des données numériques. Les fonctions MAX et MIN ne peuvent
pas être utilisées avec les données de type LOB et LONG.
5.4
Utiliser la fonction COUNT
SELECT COUNT(*)
FROM
employees
WHERE department_id = 50;
SELECT COUNT(commission_pct)
FROM
employees
WHERE department_id = 80;
Utiliser la fonction COUNT
La fonction COUNT admet trois formats :
•COUNT(*)
•COUNT(expr)
•COUNT(DISTINCT expr)
36
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
COUNT(*) renvoie le nombre de lignes d'une table qui satisfont aux critères de
l'instruction SELECT, en prenant en compte les lignes en double et les lignes
contenant des valeurs NULL dans n'importe quelle colonne. Si une clause WHERE
est incluse dans l'instruction SELECT, COUNT(*) renvoie le nombre de lignes qui
satisfont à la condition de cette clause.
COUNT(expr) renvoie le nombre de valeurs non NULL figurant dans la colonne
identifiée par expr.
COUNT(DISTINCT expr) renvoie le nombre de valeurs non NULL uniques figurant
dans la colonne identifiée par expr.
Exemples :
1.
Le premier exemple de la diapositive ci-dessus affiche le nombre d'employés
du département 50.
2.
Le deuxième exemple affiche le nombre d'employés du département 80 qui
perçoivent une commission.
5.5
Utiliser le mot-clé DISTINCT
SELECT COUNT(DISTINCT department_id)
FROM
employees;
Utiliser le mot-clé DISTINCT
Utilisez le mot-clé DISTINCT pour supprimer le comptage des valeurs en double
dans une colonne.
L'exemple de la diapositive ci-dessus affiche le nombre de valeurs de département
distinctes figurant dans la table EMPLOYEES.
5.6
Fonctions de groupe et valeurs NULL
SELECT AVG(commission_pct)
FROM
employees;
SELECT AVG(NVL(commission_pct, 0))
FROM
employees;
Fonctions de groupe et valeurs NULL
Toutes les fonctions de groupe ignorent les valeurs NULL de la colonne considérée.
Toutefois, la fonction NVL permet de les forcer à inclure ces valeurs.
Exemples :
1.
La moyenne est calculée uniquement sur la base des lignes de la table pour
lesquelles une valeur valide est stockée dans la colonne COMMISSION_PCT. Elle
est calculée comme le total des commissions versées aux employés divisé par le
nombre d'employés percevant une commission (quatre).
2.
La moyenne est calculée sur la base de toutes les lignes de la table, même si
des valeurs NULL sont stockées dans la colonne COMMISSION_PCT. La moyenne
est calculée comme le total des commissions versées aux employés divisé par le
nombre d'employés de la société (20).
37
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
5.7
Créer des groupes de données : Syntaxe
de la clause GROUP BY
SELECT
column, group_function(column)
FROM
table
[WHERE
condition]
[GROUP BY group_by_expression]
[ORDER BY column];
Créer des groupes de données : Syntaxe de la clause GROUP BY
Vous pouvez utiliser la clause GROUP BY pour diviser une table en groupes. Vous
pouvez ensuite utiliser les fonctions de groupe pour renvoyer des informations
récapitulatives pour chaque groupe.
Dans la syntaxe :
group_by_expression
Indique les colonnes dont les valeurs sont utilisées
pour la constitution des groupes de lignes.
Règles
•Lorsque vous incluez une fonction de groupe dans une clause SELECT, vous ne
pouvez pas sélectionner les résultats individuellement, sauf si la colonne individuelle
apparaît dans la clause GROUP BY. Si vous n'incluez pas la liste de colonnes dans
la clause GROUP BY, vous obtenez un message d'erreur.
•Vous pouvez utiliser une clause WHERE pour exclure des lignes avant d'effectuer la
répartition dans des groupes.
•Vous devez inclure les colonnes dans la clause GROUP BY.
•Vous ne pouvez pas utiliser un alias de colonne dans la clause GROUP BY.
5.8
Utiliser la clause GROUP BY
SELECT
department_id, AVG(salary)
FROM
employees
GROUP BY department_id ;
Utiliser la clause GROUP BY
Lors de l'utilisation de la clause GROUP BY, veillez à ce que toutes les colonnes de
la liste SELECT qui ne figurent pas dans des fonctions de groupe soient incluses
dans la clause GROUP BY. L'exemple de la diapositive ci-dessus affiche le numéro
de chaque département et le salaire moyen correspondant. Cette instruction
SELECT, qui contient une clause GROUP BY, est évaluée comme suit :
•La clause SELECT indique les colonnes à extraire, comme suit :
-Colonne des numéros de département de la table EMPLOYEES.
-Moyenne de tous les salaires du groupe que vous avez indiqué dans la clause
GROUP BY.
•La clause FROM indique les tables auxquelles la base de données doit accéder : la
table EMPLOYEES.
•La clause WHERE indique les lignes à extraire. En l'absence de clause WHERE,
toutes les lignes sont extraites par défaut.
•La clause GROUP BY indique comment les lignes doivent être regroupées. Les
lignes étant regroupées par numéro de département, la fonction AVG appliquée à la
colonne de salaire calcule le salaire moyen pour chaque département.
38
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
5.9
Utiliser la clause GROUP BY
SELECT
AVG(salary)
FROM
employees
GROUP BY department_id ;
Utiliser la clause GROUP BY
La colonne GROUP BY ne doit pas nécessairement figurer dans la liste SELECT.
Par exemple, l'instruction SELECT de la diapositive affiche le salaire moyen de
chaque département sans afficher le numéro de département correspondant.
Toutefois, sans les numéros de département, les résultats ne sont pas très
significatifs.
Vous pouvez aussi utiliser la fonction de groupe dans la clause ORDER BY :
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary);
5.10 Utiliser la clause GROUP BY
sur plusieurs colonnes
SELECT
department_id dept_id, job_id, SUM(salary)
FROM
employees
GROUP BY department_id, job_id
ORDER BY department_id;
Utiliser la clause GROUP BY sur plusieurs colonnes
Vous pouvez renvoyer des résultats récapitulatifs pour des groupes et des sousgroupes en indiquant plusieurs colonnes GROUP BY. Vous pouvez déterminer
l'ordre de tri par défaut des résultats en fonction de l'ordre des colonnes dans la
clause GROUP BY. Dans l'exemple de la diapositive ci-dessus, l'instruction SELECT
contenant une clause GROUP BY est évaluée comme suit :
•La clause SELECT indique la colonne à extraire :
-Numéro de département dans la table EMPLOYEES.
-ID de poste dans la table EMPLOYEES.
-Somme de tous les salaires du groupe que vous avez indiqué dans la clause
GROUP BY.
•La clause FROM indique les tables auxquelles la base de données doit accéder : la
table EMPLOYEES.
•La clause GROUP BY indique la manière de regrouper les lignes :
-Les lignes sont d'abord regroupées par numéro de département.
-Elles sont ensuite regroupées par ID de poste au sein des groupes par numéro de
département.
La fonction SUM est donc appliquée à la colonne de salaire pour chaque ID de poste
et pour chaque numéro de département.
5.11 Interrogations non autorisées
avec les fonctions de groupe
SELECT department_id, COUNT(last_name)
FROM
employees;
39
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
SELECT department_id, job_id, COUNT(last_name)
FROM
employees
GROUP BY department_id;
Interrogations non autorisées avec les fonctions de groupe
Chaque fois que vous utilisez une combinaison d'éléments individuels
(DEPARTMENT_ID) et de fonctions de groupe (COUNT) dans la même instruction
SELECT, vous devez inclure une clause GROUP BY qui indique les éléments
individuels (dans l'exemple, DEPARTMENT_ID). Si la clause GROUP BY est
absente, le message d'erreur "not a single-group group function" apparaît et un
astérisque (*) désigne la colonne fautive. Vous pouvez corriger l'erreur dans le
premier exemple de la diapositive ci-dessus en ajoutant la clause GROUP BY :
SELECT department_id, count(last_name)
FROM employees
GROUP BY department_id;
Toute colonne ou expression de la liste SELECT qui n'est pas une fonction
d'agrégation doit figurer dans la clause GROUP BY. Dans le deuxième exemple de
la diapositive, job_id ne figure pas dans la clause GROUP BY et n'est pas utilisé par
une fonction de groupe. Une erreur "not a GROUP BY expression" est donc générée.
Vous pouvez corriger l'erreur dans le deuxième exemple de la diapositive en ajoutant
job_id dans la clause GROUP BY.
SELECT department_id, job_id, COUNT(last_name)
FROM employees
GROUP BY department_id, job_id;
5.12 Interrogations non autorisées
avec les fonctions de groupe
SELECT
FROM
WHERE
GROUP BY
department_id, AVG(salary)
employees
AVG(salary) > 8000
department_id;
Interrogations non autorisées avec les fonctions de groupe (suite)
La clause WHERE ne peut pas être utilisée pour restreindre des groupes.
L'instruction
SELECT de l'exemple ci-dessus génère une erreur car elle utilise la clause WHERE
pour limiter l'affichage des salaires moyens aux départements dont le salaire moyen
est supérieur
à 8 000 $.
Vous pouvez corriger cette erreur en utilisant la clause HAVING pour restreindre
les groupes :
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;
40
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
5.13 Restreindre les résultats d'un groupe
avec la clause HAVING
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
column, group_function
table
condition]
group_by_expression]
group_condition]
column];
Restreindre les résultats d'un groupe avec la clause HAVING
Vous pouvez utiliser la clause HAVING pour indiquer les groupes à afficher, limitant
ainsi davantage les groupes sur la base d'informations agrégées.
Dans la syntaxe, group_condition limite les groupes de lignes renvoyés aux groupes
pour lesquels la condition indiquée est vraie.
Lorsque vous utilisez la clause HAVING, le serveur Oracle effectue les opérations
suivantes :
1.
Les lignes sont regroupées.
2.
La fonction de groupe est appliquée au groupe.
3.
Les groupes correspondant aux critères de la clause HAVING sont affichés.
La clause HAVING peut précéder la clause GROUP BY, mais il est recommandé de
placer la clause GROUP BY en premier pour une raison de logique. Les groupes
sont formés et les fonctions de groupe sont calculées avant application de la clause
HAVING aux groupes de la liste SELECT.
5.14 Utiliser la clause HAVING
SELECT
FROM
GROUP BY
HAVING
department_id, MAX(salary)
employees
department_id
MAX(salary)>10000 ;
Utiliser la clause HAVING
L'exemple de la diapositive ci-dessus affiche le numéro de département et le salaire
maximum des départements où le salaire maximum est supérieur à 10 000 $.
Vous pouvez utiliser la clause GROUP BY sans utiliser de fonction de groupe dans la
liste SELECT. Si vous limitez le nombre de lignes sur la base du résultat d'une
fonction de groupe, vous devez disposer d'une clause GROUP BY en plus de la
clause HAVING.
L'exemple suivant affiche le numéro de département et le salaire moyen des
départements où le salaire maximum est supérieur à 10 000 $ :
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING max(salary)>10000;
5.15 Utiliser la clause HAVING
SELECT
FROM
WHERE
job_id, SUM(salary) PAYROLL
employees
job_id NOT LIKE '%REP%'
41
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
GROUP BY job_id
HAVING
SUM(salary) > 13000
ORDER BY SUM(salary);
Utiliser la clause HAVING (suite)
L'exemple de la diapositive ci-dessus affiche l'ID de poste et le total des salaires
mensuels pour chaque poste dont la masse salariale dépasse 13 000 $. Il exclut les
commerciaux et trie la liste sur la base du total des salaires mensuels.
5.16 Imbriquer des fonctions de groupe
SELECT
MAX(AVG(salary))
FROM
employees
GROUP BY department_id;
Imbriquer des fonctions de groupe
Les fonctions de groupe peuvent être imbriquées sur deux niveaux. L'exemple de la
diapositive ci-dessus calcule le salaire moyen pour chaque department_id, puis
affiche le salaire moyen maximum.
Notez que la clause GROUP BY est obligatoire lors de l'imbrication de fonctions de
groupe.
42
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
6 Guide Exercice 6
Afficher des données provenant
de plusieurs tables
6.1
Joindre des tables à l'aide de la syntaxe SQL:1999
SELECT
table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];
Joindre des tables à l'aide de la syntaxe SQL:1999
Dans la syntaxe :
table1.column indique la table et la colonne à partir desquelles les données sont
extraites.
NATURAL JOIN joint deux tables en fonction de colonnes portant le même nom.
JOIN table2 USING column_name effectue une équijointure basée sur un nom de
colonne.
JOIN table2 ON table1.column_name = table2.column_name effectue une
équijointure basée sur la condition de la clause ON.
LEFT/RIGHT/FULL OUTER permet d'effectuer des jointures externes.
CROSS JOIN renvoie le produit cartésien des deux tables.
Pour plus d'informations, reportez-vous à la section SELECT du manuel Oracle
Database SQL Language Reference 11g, Release 1 (11.1).
6.2
Extraire des enregistrements
avec des jointures naturelles
SELECT department_id, department_name,
location_id, city
FROM
departments
NATURAL JOIN locations ;
Extraire des enregistrements avec des jointures naturelles
Dans l'exemple de la diapositive ci-dessus, la table LOCATIONS est jointe à la table
DEPARTMENTS par la colonne LOCATION_ID, qui est la seule colonne figurant
dans les deux tables. S'il existait d'autres colonnes communes, la jointure les
utiliserait toutes.
Jointures naturelles avec une clause WHERE
43
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Il est possible de définir des restrictions supplémentaires sur une jointure naturelle à
l'aide d'une clause WHERE. L'exemple suivant limite les résultats aux lignes dont l'ID
de département est égal à 20 ou à 50 :
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations
WHERE department_id IN (20, 50);
6.3
Extraire des enregistrements
avec la clause USING
SELECT employee_id, last_name,
location_id, department_id
FROM
employees JOIN departments
USING (department_id) ;
Extraire des enregistrements avec la clause USING
L'exemple de la diapositive ci-dessus effectue une jointure des colonnes
DEPARTMENT_ID des tables EMPLOYEES et DEPARTMENTS afin d'afficher le
paramètre LOCATION_ID du département dans lequel travaille un employé.
6.4
Utiliser des alias de table avec la clause USING
SELECT l.city, d.department_name
FROM
locations l JOIN departments d
USING (location_id)
WHERE d.location_id = 1400;
Utiliser des alias de table avec la clause USING
Lors d'une jointure avec la clause USING, vous ne pouvez pas qualifier une colonne
qui est utilisée dans la clause USING elle-même. En outre, si cette colonne est
utilisée ailleurs dans l'instruction SQL, vous ne pouvez pas lui attribuer d'alias. Par
exemple, dans l'interrogation mentionnée dans la diapositive ci-dessus, vous ne
devez pas attribuer d'alias à la colonne location_id dans la clause WHERE car cette
colonne est utilisée dans la clause USING.
Aucun qualificatif (nom ou alias de table) relatif aux colonnes référencées dans la
clause USING ne doit être indiqué dans l'instruction SQL. Par exemple, l'instruction
suivante est valide :
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;
En effet, il est nécessaire d'indiquer un alias de table comme préfixe pour les
colonnes qui sont communes aux deux tables mais ne sont pas utilisées dans la
clause USING, sinon vous obtenez l'erreur "column ambiguously defined".
Dans l'instruction ci-après, la colonne manager_id est présente dans les tables
employees et departments, et si aucun alias de table ne lui est ajouté comme préfixe,
l'erreur "column ambiguously defined" est générée.
La formulation suivante est valide :
44
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
SELECT first_name, d.department_name, d.manager_id
FROM employees e JOIN departments d USING (department_id)
WHERE department_id = 50;
6.5
Extraire des enregistrements avec la clause ON
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id);
Extraire des enregistrements avec la clause ON
Dans l'exemple de la diapositive, les colonnes DEPARTMENT_ID des tables
EMPLOYEES et DEPARTMENTS sont jointes à l'aide de la clause ON. Chaque fois
qu'un ID de département de la table EMPLOYEES est égal à un ID de département
de la table DEPARTMENTS, la ligne correspondante est renvoyée. L'alias de table
est nécessaire pour qualifier les noms de colonne identiques.
Vous pouvez aussi utiliser la clause ON pour joindre des colonnes portant des noms
différents. Les parenthèses entourant les colonnes jointes dans l'exemple de la
diapositive, (e.department_id = d.department_id), sont facultatives. Ainsi, vous
pouvez simplement indiquer ON e.department_id = d.department_id.
Remarque : SQL Developer ajoute le suffixe '_1' pour faire la différence entre les
deux valeurs department_id.
6.6
Créer des jointures à trois liens avec la clause ON
SELECT employee_id, city, department_name
FROM
employees e
JOIN
departments d
ON
d.department_id = e.department_id
JOIN
locations l
ON
d.location_id = l.location_id;
Créer des jointures à trois liens avec la clause ON
Une jointure à trois liens est une jointure entre trois tables. Dans la syntaxe conforme
à la norme SQL:1999, les jointures sont effectuées de gauche à droite. La première
jointure à réaliser est donc EMPLOYEES JOIN DEPARTMENTS. La première
condition de jointure peut référencer les colonnes des tables EMPLOYEES et
DEPARTMENTS, mais pas celles de la table LOCATIONS. La deuxième condition
de jointure peut référencer les colonnes des trois tables.
Remarque : L'exemple de code de la diapositive ci-dessus peut également être
réalisé avec la clause USING :
SELECT e.employee_id, l.city, d.department_name
FROM employees e
JOIN departments d
USING (department_id)
JOIN locations l
USING (location_id)
45
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
6.7
Appliquer des conditions supplémentaires
à une jointure
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id)
AND
e.manager_id = 149 ;
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id)
WHERE
e.manager_id = 149 ;
Appliquer des conditions supplémentaires à une jointure
Vous pouvez appliquer des conditions supplémentaires à la jointure.
L'exemple de la diapositive réalise une jointure sur les tables EMPLOYEES et
DEPARTMENTS, et affiche seulement les employés pour lesquels l'ID de manager
est 149. Pour ajouter des conditions supplémentaires à la clause ON, vous pouvez
ajouter des clauses AND. Vous pouvez également utiliser une clause WHERE.
6.8
Auto-jointures à l'aide de la clause ON
SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker JOIN employees manager
ON
(worker.manager_id = manager.employee_id);
Auto-jointures à l'aide de la clause ON
La clause ON peut aussi être utilisée pour joindre des colonnes portant des noms
différents, au sein de la même table ou dans des tables différentes.
L'exemple présente une auto-jointure de la table EMPLOYEE, portant sur les
colonnes EMPLOYEE_ID et MANAGER_ID.
Remarque : Les parenthèses entourant les colonnes jointes,
(e.manager_id = m.employee_id), sont facultatives. Vous pouvez simplement
indiquer ON e.manager_id = m.employee_id.
6.9
Extraire des enregistrements
à l'aide de non-équijointures
SELECT e.last_name, e.salary, j.grade_level
FROM
employees e JOIN job_grades j
ON
e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
Extraire des enregistrements à l'aide de non-équijointures
L'exemple de la diapositive ci-dessus crée une non-équijointure afin d'évaluer le
niveau de salaire d'un employé. Le salaire doit être compris entre la valeur inférieure
et la valeur supérieure de n'importe quelle plage de salaires.
Il est important de remarquer que chaque employé apparaît une seule fois lors
de l'exécution de cette interrogation. Aucun employé ne figure en double dans la
46
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
liste.
Ce, pour deux raisons :
•Aucune ligne de la table JOB_GRADES ne contient des niveaux qui se
chevauchent.
Cela signifie que la valeur du salaire d'un employé ne peut se trouver qu'entre la
valeur inférieure et la valeur supérieure d'une ligne de la table des niveaux de
salaire.
•Le salaire de tous les employés se situe dans les limites de cette table. Cela signifie
qu'aucun employé ne gagne moins que la valeur la plus faible de la colonne
LOWEST_SAL ou plus que la valeur la plus élevée de la colonne HIGHEST_SAL.
Remarque : D'autres conditions (telles que <= et >=) peuvent être utilisées, mais
BETWEEN est la plus simple. Veillez à bien préciser la valeur inférieure en premier
et la valeur supérieure en dernier lors de l'utilisation de la condition BETWEEN. Le
serveur Oracle convertit la condition BETWEEN en une paire de conditions AND. La
condition BETWEEN ne présente aucun avantage en termes de performances. Elle
sert uniquement à simplifier la formulation logique.
Dans l'exemple de la diapositive ci-dessus, des alias de table ont été indiqués pour
des raisons de performances et non en raison d'une possible ambiguïté.
6.10 LEFT OUTER JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM
employees e LEFT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;
LEFT OUTER JOIN
L'interrogation de la diapositive extrait toutes les lignes de la table EMPLOYEES, qui
est la table de gauche, même s'il n'y a pas de correspondance dans la table
DEPARTMENTS.
6.11 RIGHT OUTER JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM
employees e RIGHT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;
RIGHT OUTER JOIN
L'interrogation de la diapositive extrait toutes les lignes de la table DEPARTMENTS,
qui est la table de droite, même s'il n'y a pas de correspondance dans la table
EMPLOYEES.
6.12 FULL OUTER JOIN
SELECT e.last_name, d.department_id, d.department_name
FROM
employees e FULL OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;
FULL OUTER JOIN
L'interrogation de la diapositive extrait toutes les lignes de la table EMPLOYEES,
même s'il n'y a aucune correspondance dans la table DEPARTMENTS. Elle extrait
47
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
également toutes les lignes de la table DEPARTMENTS, même en l'absence de
correspondance dans la table EMPLOYEES.
6.13 Créer des jointures croisées
SELECT last_name, department_name
FROM
employees
CROSS JOIN departments ;
Créer des jointures croisées
L'exemple de la diapositive ci-dessus génère un produit cartésien des tables
EMPLOYEES
et DEPARTMENTS.
7 Guide Exercice 7
Utiliser des sous-interrogations
pour résoudre des interrogations
7.1
Syntaxe des sous-interrogations
SELECT
select_list
FROM table
WHERE
expr operator
(SELECT
select_list
FROM
table);
Syntaxe des sous-interrogations
Une sous-interrogation est une instruction SELECT imbriquée dans la clause d'une
autre instruction SELECT. Vous pouvez rédiger des instructions puissantes à partir
d'instructions plus simples en utilisant des sous-interrogations. Ces dernières
peuvent être très utiles pour sélectionner les lignes d'une table avec une condition
qui dépend des données de la table ellemême.
Vous pouvez placer la sous-interrogation dans de nombreuses clauses SQL,
notamment :
•Clause WHERE
•Clause HAVING
•Clause FROM
Dans la syntaxe :
operator représente une condition de comparaison, telle que >, = ou IN.
Remarque : Les conditions de comparaison peuvent être classées en deux
catégories : les opérateurs monolignes (>, =, >=, <, <>, <=) et les opérateurs
multilignes (IN, ANY, ALL).
La sous-interrogation est souvent appelée instruction SELECT imbriquée, sousinstruction SELECT ou instruction SELECT interne. La sous-interrogation est
généralement exécutée en premier et son résultat est utilisé pour l'exécution de la
condition de l'interrogation principale (ou externe).
7.2
Utiliser une sous-interrogation
SELECT last_name, salary
48
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
FROM
WHERE
employees
salary >
(SELECT salary
FROM
employees
WHERE last_name = 'Abel');
Utiliser une sous-interrogation
Dans la diapositive ci-dessus, l'interrogation interne détermine le salaire de l'employé
Abel. L'interrogation externe prend le résultat de l'interrogation interne et l'utilise pour
afficher tous les employés qui gagnent plus que l'employé Abel.
7.3
Sous-interrogations monolignes
Sous-interrogations monolignes
Une sous-interrogation monoligne renvoie une ligne à partir de l'instruction SELECT
interne. Ce type de sous-interrogation utilise un opérateur monoligne. La diapositive
cidessus fournit la liste des opérateurs monolignes.
Exemple :
Affichez les employés dont l'ID de poste est identique à celui de l'employé 141 :
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141);
7.4
Exécuter des sous-interrogations monolignes
SELECT last_name, job_id, salary
FROM
employees
WHERE job_id =
(SELECT job_id
FROM
employees
WHERE last_name = 'Taylor')
AND
salary >
(SELECT salary
49
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
FROM
employees
WHERE last_name = 'Taylor');
Exécuter des sous-interrogations monolignes
Une instruction SELECT peut être considérée comme un bloc d'interrogation.
L'exemple de la diapositive ci-dessus affiche les employés qui occupent le même
poste que Taylor, mais gagnent un salaire plus élevé que lui.
L'exemple se compose de trois blocs d'interrogation : l'interrogation externe et deux
interrogations internes. Les blocs d'interrogation internes sont exécutés en premier et
produisent respectivement les résultats SA_REP et 8600. Le bloc d'interrogation
externe est ensuite traité. Il utilise les valeurs renvoyées par les interrogations
internes pour exécuter ses conditions de recherche.
Etant donné que les deux interrogations internes renvoient des valeurs uniques
(respectivement SA_REP et 8600), cette instruction SQL est appelée une sousinterrogation monoligne.
Remarque : Les interrogations externes et internes peuvent obtenir des données à
partir de différentes tables.
7.5
Utiliser des fonctions de groupe
dans une sous-interrogation
SELECT last_name, job_id, salary
FROM
employees
WHERE salary =
(SELECT MIN(salary)
FROM
employees);
Utiliser des fonctions de groupe dans une sous-interrogation
Vous pouvez afficher des données à partir d'une interrogation principale en utilisant
une fonction de groupe dans une sous-interrogation afin de renvoyer une ligne
unique.
La sous-interrogation, qui figure entre parenthèses, est placée après la condition de
comparaison.
L'exemple de la diapositive ci-dessus affiche le nom, l'ID de poste et le salaire de
tous les employés dont le salaire est égal au salaire minimum. La fonction de groupe
MIN renvoie une valeur unique (2500) pour l'interrogation externe.
7.6
Clause HAVING avec des sous-interrogations
SELECT
FROM
GROUP BY
HAVING
department_id, MIN(salary)
employees
department_id
MIN(salary) >
(SELECT MIN(salary)
FROM
employees
WHERE department_id = 50);
Clause HAVING avec des sous-interrogations
Vous pouvez utiliser des sous-interrogations non seulement dans la clause WHERE,
mais aussi dans la clause HAVING. Le serveur Oracle exécute la sous-interrogation
et les résultats sont renvoyés dans la clause HAVING de l'interrogation principale.
50
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
L'instruction SQL de la diapositive ci-dessus affiche tous les départements dont le
salaire minimum est supérieur à celui du département 50.
Exemple :
Cherchez le poste présentant le salaire moyen le plus bas.
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);
7.7
Qu'est-ce qui est incorrect
dans cette instruction ?
SELECT employee_id, last_name
FROM
employees
WHERE salary =
(SELECT
MIN(salary)
FROM
employees
GROUP BY department_id);
Qu'est-ce qui est incorrect dans cette instruction ?
Lors de l'utilisation de sous-interrogations, une erreur courante est le renvoi de
plusieurs lignes pour une sous-interrogation monoligne.
Dans l'instruction SQL de la diapositive ci-dessus, la sous-interrogation contient une
clause GROUP BY, ce qui implique qu'elle renverra plusieurs lignes (une pour
chaque groupe trouvé). Dans ce cas, les résultats de la sous-interrogation sont 4400,
6000, 2500, 4200, 7000, 17000 et 8300.
L'interrogation externe prend ces résultats et les utilise dans sa clause WHERE. La
clause WHERE contient un opérateur d'égalité (=), c'est-à-dire un opérateur de
comparaison monoligne qui attend une seule valeur. L'opérateur = ne peut pas
accepter plusieurs valeurs de la part de la sous-interrogation et génère donc une
erreur.
Pour corriger cette erreur, remplacez l'opérateur = par IN.
7.8
L'interrogation interne ne renvoie aucune ligne
SELECT last_name, job_id
FROM
employees
WHERE job_id =
(SELECT job_id
FROM
employees
WHERE last_name = 'Haas');
L'interrogation interne ne renvoie aucune ligne
Un problème courant concernant les sous-interrogations se produit lorsqu'aucune
ligne n'est renvoyée par l'interrogation interne.
Dans l'instruction SQL de la diapositive ci-dessus, la sous-interrogation contient une
clause WHERE. L'objectif est de trouver l'employé dont le nom est Haas.
L'instruction est correcte, mais aucune ligne n'est sélectionnée lors de son exécution.
51
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Cela est dû au fait qu'il n'existe aucun employé nommé Haas. La sous-interrogation
ne renvoie donc aucune ligne. L'interrogation externe prend les résultats de la sousinterrogation (NULL) et les utilise dans sa clause WHERE. L'interrogation externe ne
trouve aucun employé dont l'ID de poste est NULL et ne renvoie donc aucune ligne.
Même s'il existait un poste de valeur NULL, la ligne correspondante ne serait pas
renvoyée car la comparaison de deux valeurs NULL renvoie une valeur NULL. La
condition WHERE n'est donc pas vraie.
7.9
Sous-interrogations multilignes
Sous-interrogations multilignes
Les sous-interrogations qui renvoient plusieurs lignes sont appelées des sousinterrogations multilignes. Avec de telles sous-interrogations, vous utilisez un
opérateur multiligne au lieu d'un opérateur monoligne. L'opérateur multiligne attend
une ou plusieurs valeurs :
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Exemple :
Cherchez les employés qui gagnent un salaire équivalent au salaire minimum pour
chaque département.
L'interrogation interne est exécutée en premier et produit un résultat. Le bloc
d'interrogation principal est ensuite traité. Il utilise les valeurs renvoyées par
l'interrogation interne pour exécuter sa condition de recherche. En réalité,
l'interrogation principale apparaît comme suit pour le serveur Oracle :
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300,
8600, 17000);
7.10 Utiliser l'opérateur ANY dans
des sous-interrogations multilignes
SELECT employee_id, last_name, job_id, salary
52
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
FROM
WHERE
employees
salary < ANY
AND
(SELECT salary
FROM
employees
WHERE job_id = 'IT_PROG')
job_id <> 'IT_PROG';
Utiliser l'opérateur ANY dans des sous-interrogations multilignes
L'opérateur ANY (et son synonyme, l'opérateur SOME) compare une valeur à
chaque valeur renvoyée par une sous-interrogation. L'exemple de la diapositive cidessus affiche les employés qui ne sont pas programmeurs en informatique et dont
le salaire est inférieur à celui de n'importe quel programmeur. Le salaire maximum
d'un programmeur est de 9 000 $.
<ANY signifie moins que le maximum. >ANY signifie plus que le minimum. =ANY
équivaut à IN.
7.11 Utiliser l'opérateur ALL dans
des sous-interrogations multilignes
SELECT employee_id, last_name, job_id, salary
FROM
employees
WHERE salary < ALL
(SELECT salary
FROM
employees
WHERE job_id = 'IT_PROG')
AND
job_id <> 'IT_PROG';
Utiliser l'opérateur ALL dans des sous-interrogations multilignes
L'opérateur ALL compare une valeur à toutes les valeurs renvoyées par une sousinterrogation. L'exemple de la diapositive ci-dessus affiche les employés dont le
salaire est inférieur à celui de tous les employés dont l'ID de poste est IT_PROG, et
dont le poste n'est pas IT_PROG.
>ALL signifie plus que le maximum et <ALL, moins que le minimum.
L'opérateur NOT peut être utilisé avec les opérateurs IN, ANY et ALL.
7.12 Valeurs NULL dans une sous-interrogation
SELECT emp.last_name
FROM
employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM
employees mgr);
Valeurs NULL dans une sous-interrogation
L'instruction SQL de la diapositive ci-dessus tente d'afficher tous les employés qui
n'ont aucun subordonné. Logiquement, cette instruction SQL devrait renvoyer
12 lignes. Toutefois, elle n'en renvoie aucune. L'une des valeurs renvoyées par
l'interrogation interne étant NULL, l'interrogation entière ne renvoie aucune ligne.
Cela est dû au fait que les conditions qui effectuent une comparaison avec une
valeur NULL donnent un résultat NULL. Par conséquent, lorsque l'ensemble de
53
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
résultats d'une sous-interrogation est susceptible de comprendre des valeurs NULL,
n'utilisez pas l'opérateur NOT IN. Cet opérateur équivaut à <> ALL.
Notez que la présence de la valeur NULL dans le jeu de résultats d'une sousinterrogation ne pose aucun problème si vous utilisez l'opérateur IN. Celui-ci
équivaut à =ANY. Par exemple, pour afficher les employés qui ont des subordonnés,
utilisez l'instruction SQL suivante :
SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id IN
(SELECT mgr.manager_id
FROM employees mgr);
8 Guide Exercice 8
Utiliser des opérateurs ensemblistes
8.1
Utiliser l'opérateur UNION
SELECT
FROM
UNION
SELECT
FROM
employee_id, job_id
employees
employee_id, job_id
job_history;
Utiliser l'opérateur UNION
L'opérateur UNION élimine les doublons. Si des enregistrements qui se trouvent à la
fois dans la table EMPLOYEES et dans la table JOB_HISTORY sont identiques, ils
s'affichent une seule fois. Dans le résultat de la diapositive ci-dessus,
l'enregistrement correspondant à l'employé pour lequel EMPLOYEE_ID = 200
apparaît deux fois car la valeur JOB_ID est différente sur chaque ligne.
Considérons l'exemple suivant :
SELECT employee_id, job_id, department_id
FROM employees
UNION
SELECT employee_id, job_id, department_id
FROM job_history;
8.2
Utiliser l'opérateur UNION ALL
SELECT employee_id, job_id, department_id
FROM
employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM
job_history
ORDER BY employee_id;
Utiliser l'opérateur UNION ALL
Dans l'exemple, 30 lignes sont sélectionnées. La combinaison des deux tables
donne 30 lignes. L'opérateur UNION ALL n'élimine pas les lignes en double. UNION
54
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
renvoie toutes les lignes distinctes sélectionnées par l'une ou l'autre des
interrogations. UNION ALL renvoie toutes les lignes sélectionnées par l'une ou l'autre
des interrogations, y compris tous les doublons. Reprenons l'interrogation de la
diapositive ci-dessus, maintenant écrite avec la clause UNION :
SELECT employee_id, job_id,department_id
FROM employees
UNION
SELECT employee_id, job_id,department_id
FROM job_history
ORDER BY employee_id;
L'interrogation précédente renvoie 29 lignes. La ligne suivante étant en double, elle
est éliminée :
8.3
Utiliser l'opérateur INTERSECT
SELECT employee_id, job_id
FROM
employees
INTERSECT
SELECT employee_id, job_id
FROM
job_history;
Utiliser l'opérateur INTERSECT
Dans l'exemple de la diapositive, l'interrogation renvoie uniquement les
enregistrements qui présentent les mêmes valeurs dans les colonnes sélectionnées
dans les deux tables.
Quels résultats obtiendrez-vous si vous ajoutez la colonne DEPARTMENT_ID à
l'instruction SELECT sur la table EMPLOYEES et ajoutez la colonne
DEPARTMENT_ID à l'instruction SELECT sur la table JOB_HISTORY, puis exécutez
cette interrogation ? Les résultats seront différents en raison de l'introduction d'une
autre colonne dont les valeurs peuvent être en double ou non.
Exemple :
SELECT employee_id, job_id, department_id
FROM employees
INTERSECT
SELECT employee_id, job_id, department_id
FROM job_history;
L'employé 200 ne fait plus partie des résultats car la valeur
EMPLOYEES.DEPARTMENT_ID est différente de la valeur
JOB_HISTORY.DEPARTMENT_ID.
8.4
Utiliser l'opérateur MINUS
SELECT
FROM
MINUS
SELECT
FROM
employee_id
employees
employee_id
job_history;
Utiliser l'opérateur MINUS
Dans l'exemple de la diapositive ci-dessus, les ID d'employé de la table
JOB_HISTORY sont soustraits de ceux de la table EMPLOYEES. L'ensemble de
55
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
résultats affiche les employés restant après soustraction. Il comprend les lignes qui
existent dans la table EMPLOYEES, mais pas dans la table JOB_HISTORY. Il s'agit
des enregistrements des employés qui n'ont jamais changé de poste.
8.5
Assurer la correspondance
des instructions SELECT
SELECT location_id, department_name "Department",
TO_CHAR(NULL) "Warehouse location"
FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department",
state_province
FROM locations;
Assurer la correspondance des instructions SELECT
Il est impératif que les listes SELECT des interrogations comprennent un nombre
identique d'expressions. Pour faire en sorte que cette règle soit respectée, vous
pouvez utiliser les colonnes factices et les fonctions de conversion de type de
données. Dans la diapositive cidessus, le nom Warehouse location est utilisé comme
en-tête de colonne factice.
La fonction TO_CHAR est utilisée dans la première interrogation pour assurer la
correspondance avec le type de données VARCHAR2 de la colonne state_province
extraite par la deuxième interrogation. De même, la fonction TO_CHAR de la
deuxième interrogation est utilisée pour assurer la correspondance avec le type de
données VARCHAR2 de la colonne department_name extraite par la première
interrogation.
Le résultat de l'interrogation est le suivant :
8.6
Assurer la correspondance des instructions SELECT :
Exemple
SELECT
FROM
UNION
SELECT
FROM
employee_id, job_id,salary
employees
employee_id, job_id,0
job_history;
Assurer la correspondance des instructions SELECT : Exemple
Les tables EMPLOYEES et JOB_HISTORY ont plusieurs colonnes en commun
(notamment EMPLOYEE_ID, JOB_ID et DEPARTMENT_ID). Mais que se passe-t-il
si vous souhaitez afficher l'ID d'employé, l'ID de poste et le salaire à l'aide de
l'opérateur UNION, sachant que le salaire n'existe que dans la table EMPLOYEES ?
L'exemple de code de la diapositive ci-dessus met en correspondance les colonnes
EMPLOYEE_ID et JOB_ID des tables EMPLOYEES et JOB_HISTORY. La valeur
littérale 0 est ajoutée à l'instruction JOB_HISTORY SELECT pour assurer la
correspondance avec la colonne numérique SALARY dans l'instruction
EMPLOYEES SELECT.
Dans les résultats affichés dans la diapositive, chaque ligne du résultat qui
correspond à un enregistrement de la table JOB_HISTORY contient un 0 dans la
colonne SALARY.
56
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
8.7
Utiliser la clause ORDER BY dans
des opérations ensemblistes
SELECT employee_id, job_id,salary
FROM
employees
UNION
SELECT employee_id, job_id,0
FROM
job_history
ORDER BY 2;
Utiliser la clause ORDER BY dans des opérations ensemblistes
La clause ORDER BY ne peut être utilisée qu'une seule fois dans une interrogation
composée. Elle doit alors être placée à la fin de l'interrogation. Elle accepte un nom
de colonne ou un alias. Par défaut, le résultat est trié par ordre croissant sur la base
de la première colonne de la première interrogation SELECT.
Remarque : La clause ORDER BY ne reconnaît pas les noms de colonne de la
deuxième interrogation SELECT. Pour éviter toute confusion entre les noms de
colonne, il est courant de procéder au tri sur la base des positions de colonne.
Par exemple, pour l'instruction suivante, le résultat sera affiché par ordre croissant de
la valeur de job_id.
SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0
FROM job_history
ORDER BY 2;
Si vous omettez la clause ORDER BY, le résultat est par défaut trié par ordre
croissant de la valeur employee_id. Vous ne pouvez pas utiliser les colonnes de la
deuxième interrogation pour trier le résultat.
57
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
9 Guide Exercice 9
Manipuler des données
Syntaxe de l'instruction INSERT
INSERT INTO
table [(column [, column...])]
VALUES
(value [, value...]);
Syntaxe de l'instruction INSERT
Vous pouvez ajouter de nouvelles lignes à une table en exécutant l'instruction
INSERT.
Dans la syntaxe :
table
Est le nom de la table.
column
Est le nom de la colonne dans la table à remplir.
value
Est la valeur correspondante de la colonne.
Remarque : Pour ajouter une seule ligne à la fois à une table, utilisez la clause
VALUES.
9.1
Insérer de nouvelles lignes
INSERT INTO departments(department_id,
department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
Insérer de nouvelles lignes
Puisque vous pouvez insérer une nouvelle ligne contenant des valeurs pour chaque
colonne, la liste de colonnes n'est pas obligatoire dans la clause INSERT. Toutefois,
si vous n'indiquez pas de liste de colonnes, vous devez énumérer les valeurs selon
l'ordre par défaut des colonnes de la table et fournir une valeur pour chaque colonne.
DESCRIBE departments
Pour plus de clarté, précisez la liste de colonnes dans la clause INSERT.
Placez les valeurs de type date et de type caractère entre apostrophes. En revanche,
il est déconseillé d'en faire autant avec les valeurs numériques.
9.2
Insérer des lignes comprenant des valeurs NULL
INSERT INTO
VALUES
departments (department_id,
department_name)
(30, 'Purchasing');
INSERT INTO
VALUES
departments
(100, 'Finance', NULL, NULL);
Insérer des lignes comprenant des valeurs NULL
Assurez-vous de pouvoir utiliser des valeurs NULL dans la colonne considérée, à
l'aide
de la commande DESCRIBE.
Le serveur Oracle applique automatiquement l'ensemble des types de données, des
plages
58
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
de données et des contraintes d'intégrité des données. Toute colonne qui n'est pas
indiquée explicitement reçoit une valeur NULL dans la nouvelle ligne.
Les erreurs courantes qui peuvent se produire pendant la saisie par l'utilisateur sont
vérifiées dans l'ordre suivant :
•Valeur obligatoire manquante pour une colonne NOT NULL.
•Valeur en double violant une contrainte de clé unique ou primaire.
•Valeur quelconque (Any) violant une contrainte CHECK.
•Préservation de l'intégrité référentielle en cas de contrainte de clé étrangère.
•Non-concordance des types de données ou valeurs trop longues pour tenir dans la
colonne.
Remarque : Il est recommandé d'indiquer la liste de colonnes car l'instruction
INSERT est ainsi plus lisible et plus fiable, ou moins sujette aux erreurs.
9.3
Insérer des valeurs spéciales
INSERT INTO employees (employee_id,
first_name, last_name,
email, phone_number,
hire_date, job_id, salary,
commission_pct, manager_id,
department_id)
VALUES
(113,
'Louis', 'Popp',
'LPOPP', '515.124.4567',
SYSDATE, 'AC_ACCOUNT', 6900,
NULL, 205, 110);
Insérer des valeurs spéciales
Vous pouvez utiliser des fonctions pour entrer des valeurs spéciales dans une table.
L'exemple de la diapositive ci-dessus enregistre des informations concernant
l'employé Popp dans la table EMPLOYEES. Il insère la date du jour et l'heure
actuelle dans la colonne HIRE_DATE. Il utilise pour cela la fonction SYSDATE qui
renvoie la date du jour et l'heure actuelle du serveur de base de données. Vous
pouvez aussi utiliser la fonction CURRENT_DATE pour obtenir la date du jour
correspondant au fuseau horaire de la session. Lors de l'insertion de lignes dans une
table, vous pouvez par ailleurs utiliser la fonction USER , qui enregistre le nom de
l'utilisateur en cours.
Vérifier les ajouts à la table
SELECT employee_id, last_name, job_id, hire_date, commission_pct
FROM employees
WHERE employee_id = 113;
9.4
Insérer des valeurs de date et d'heure spécifiques
INSERT INTO employees
VALUES
(114,
'Den', 'Raphealy',
'DRAPHEAL', '515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'SA_REP', 11000, 0.2, 100, 60);
59
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Insérer des valeurs de date et d'heure spécifiques
Le format utilisé pour l'insertion d'une valeur de date est généralement DD-MON-RR.
Avec le format RR, le système fournit automatiquement le siècle correct.
Vous pouvez également fournir la date au format DD-MON-YYYY. Ce format est
recommandé car il indique clairement le siècle et ne dépend pas de la logique
interne associée au format RR.
Si une date doit être saisie dans un format autre que celui par défaut (par exemple,
avec un autre siècle ou une heure spécifique), vous devez utiliser la fonction
TO_DATE.
L'exemple de la diapositive ci-dessus enregistre des informations concernant
l'employé Raphealy dans la table EMPLOYEES. Il attribue la valeur February 3, 1999
à la colonne HIRE_DATE.
9.5
Créer un script
INSERT INTO departments
(department_id, department_name, location_id)
VALUES
(&department_id, '&department_name',&location);
Créer un script
Vous pouvez enregistrer des commandes comprenant des variables de substitution
dans un fichier, puis exécuter ce fichier. L'exemple de la diapositive ci-dessus
enregistre des informations concernant un département dans la table
DEPARTMENTS.
Lorsque vous exécutez le fichier script, vous êtes invité à saisir une valeur pour
chacune des variables avec esperluette (&) d'interprétation. Après avoir saisi la
valeur appropriée, cliquez sur le bouton OK. Les valeurs que vous indiquez sont
utilisées dans l'instruction. Vous pouvez ainsi exécuter indéfiniment le même fichier
script, en fournissant un jeu de valeurs différent à chaque fois.
9.6
Copier des lignes depuis une autre table
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM
employees
WHERE job_id LIKE '%REP%';
Copier des lignes depuis une autre table
Vous pouvez utiliser l'instruction INSERT pour ajouter à une table des lignes dont les
valeurs proviennent de tables existantes. Dans l'exemple de la diapositive ci-dessus,
pour que l'instruction INSERT INTO fonctionne, vous devez avoir créé la table
sales_reps à l'aide de l'instruction CREATE TABLE. Cette instruction sera décrite
dans le chapitre suivant, "Utiliser des instructions LDD pour créer et gérer des
tables".
A la place de la clause VALUES, vous utilisez une sous-interrogation.
Syntaxe
INSERT INTO table [ column (, column) ] subquery;
Dans la syntaxe :
table
Est le nom de la table.
column
Est le nom de la colonne à remplir dans la table.
subquery
Est la sous-interrogation qui renvoie des lignes vers la table.
60
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Les colonnes indiquées dans la clause INSERT doivent correspondre en termes de
nombre et de type de données aux valeurs de la sous-interrogation. Le nombre de
lignes ajoutées dépend du nombre de lignes renvoyées par la sous-interrogation.
Pour créer une copie des lignes d'une table, utilisez SELECT * dans la sousinterrogation :
INSERT INTO copy_emp
SELECT *
FROM employees;
9.7
Modifier des données dans une table
Syntaxe de l'instruction UPDATE
UPDATE
SET
[WHERE
table
column = value [, column = value, ...]
condition];
Syntaxe de l'instruction UPDATE
Vous pouvez modifier les valeurs existantes d'une table à l'aide de l'instruction
UPDATE.
Dans la syntaxe :
table
Est le nom de la table.
column
Est le nom de la colonne à remplir dans la table.
value
Est la valeur ou la sous-interrogation associée à la colonne.
condition
Identifie les lignes à mettre à jour et se compose de noms de colonne,
d'expressions, de constantes, de sous-interrogations et
d'opérateurs
de comparaison.
Vérifiez l'opération de mise à jour en interrogeant la table pour afficher les lignes
modifiées.
Pour plus d'informations, reportez-vous à la section "UPDATE" du manuel Oracle
Database SQL Language Reference 11g, Release 1 (11.1).
Remarque : En règle générale, utilisez la colonne de clé primaire dans la clause
WHERE pour identifier une ligne unique à mettre à jour. L'utilisation d'autres
colonnes peut entraîner la mise à jour inattendue de plusieurs lignes. Par exemple, il
peut s'avérer dangereux d'identifier une ligne unique de la table EMPLOYEES en
fonction du nom, car plusieurs employés peuvent porter le même nom.
9.8
Mettre à jour des lignes d'une table
UPDATE employees
SET
department_id = 50
WHERE employee_id = 113;
UPDATE
SET
copy_emp
department_id = 110;
Mettre à jour des lignes d'une table
61
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Si la clause WHERE est indiquée, l'instruction UPDATE modifie les valeurs d'une ou
de plusieurs lignes spécifiques. L'exemple de la diapositive ci-dessus illustre le
transfert de l'employé 113 (Popp) vers le département 50.
Si vous omettez la clause WHERE, les valeurs de toutes les lignes de la table sont
modifiées. Examinez les lignes mises à jour dans la table COPY_EMP.
SELECT last_name, department_id
FROM copy_emp;
Par exemple, un employé qui occupait le poste SA_REP occupe désormais le poste
IT_PROG. Son JOB_ID doit donc être mis à jour et la valeur NULL doit être attribuée
au champ de commission.
UPDATE employees
SET job_id = 'IT_PROG', commission_pct = NULL
WHERE employee_id = 114;
Remarque : La table COPY_EMP contient les mêmes données que la table
EMPLOYEES.
9.9
Mettre à jour deux colonnes avec
une sous-interrogation
UPDATE
SET
WHERE
employees
job_id = (SELECT job_id
FROM
employees
WHERE
employee_id = 205),
salary = (SELECT salary
FROM
employees
WHERE
employee_id = 205)
employee_id
= 113;
Mettre à jour deux colonnes avec une sous-interrogation
Vous pouvez mettre à jour plusieurs colonnes dans la clause SET d'une instruction
UPDATE en écrivant plusieurs sous-interrogations. La syntaxe est la suivante :
UPDATE table
SET column =
(SELECT column
FROM table
WHERE condition)
[,
column =
(SELECT column
FROM table
WHERE condition)]
[WHERE condition ]
;
L'exemple de la diapositive ci-dessus peut aussi être écrit comme suit :
UPDATE employees
62
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
SET (job_id, salary) = (SELECT job_id, salary
FROM employees
WHERE employee_id = 205)
WHERE employee_id = 113;
9.10 Mettre à jour des lignes sur la base
d'une autre table
UPDATE
SET
copy_emp
department_id
=
WHERE
job_id
=
(SELECT department_id
FROM employees
WHERE employee_id = 100)
(SELECT job_id
FROM employees
WHERE employee_id = 200);
Mettre à jour des lignes sur la base d'une autre table
Vous pouvez utiliser les sous-interrogations des instructions UPDATE pour mettre à
jour les valeurs d'une table. L'exemple de la diapositive ci-dessus met à jour la table
COPY_EMP sur la base des valeurs de la table EMPLOYEES. Il remplace l'ID de
département de tous les employés dotés de l'ID de poste 200 par l'ID de
département actuel de l'employé 100.
9.11 Supprimer des lignes d'une table
DELETE FROM departments
WHERE department_name = 'Finance';
DELETE FROM
copy_emp;
Supprimer des lignes d'une table
Vous pouvez supprimer des lignes spécifiques en indiquant la clause WHERE dans
l'instruction DELETE. Le premier exemple de la diapositive ci-dessus supprime le
département Accounting de la table DEPARTMENTS. Vous pouvez vérifier
l'opération de suppression en affichant les lignes supprimées à l'aide de l'instruction
SELECT.
SELECT *
FROM departments
WHERE department_name = 'Finance';
Toutefois, si vous omettez la clause WHERE, toutes les lignes de la table sont
supprimées.
Le deuxième exemple de la diapositive supprime toutes les lignes de la table
COPY_EMP car aucune clause WHERE n'a été indiquée.
Exemple :
Supprimez les lignes identifiées dans la clause WHERE.
DELETE FROM employees WHERE employee_id = 114;
DELETE FROM departments WHERE department_id IN (30, 40);
63
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
9.12 Supprimer des lignes sur la base
d'une autre table
DELETE FROM employees
WHERE department_id =
(SELECT department_id
FROM
departments
WHERE department_name
LIKE '%Public%');
Supprimer des lignes sur la base d'une autre table
Vous pouvez utiliser les sous-interrogations pour supprimer des lignes d'une table
sur la base des valeurs d'une autre table. L'exemple de la diapositive ci-dessus
supprime tous les employés des départements dont le nom contient la chaîne Public.
La sous-interrogation recherche dans la table DEPARTMENTS l'ID de département
sur la base du nom de département contenant la chaîne Public. La sous-interrogation
fournit ensuite l'ID de département à l'interrogation principale, qui supprime des
lignes de données de la table EMPLOYEES sur la base de cet ID de département.
9.13 Instruction TRUNCATE
TRUNCATE TABLE table_name;
TRUNCATE TABLE copy_emp;
Instruction TRUNCATE
La méthode la plus efficace pour vider une table consiste à utiliser l'instruction
TRUNCATE.
Vous pouvez recourir à cette instruction pour supprimer rapidement toutes les lignes
d'une table ou d'un cluster. Il est plus rapide de supprimer des lignes avec
l'instruction TRUNCATE qu'avec l'instruction DELETE pour les raisons suivantes :
•TRUNCATE est une instruction LDD (langage de définition de données) qui ne
génère aucune donnée d'annulation (rollback). Les données d'annulation seront
traitées ultérieurement dans ce chapitre.
•Le fait de vider une table n'active pas les déclencheurs (triggers) de suppression de
la table.
Si la table est le parent d'une contrainte d'intégrité référentielle, vous ne pouvez pas
la vider. Vous devez désactiver la contrainte avant d'exécuter l'instruction
TRUNCATE. La désactivation des contraintes sera traitée dans un autre chapitre.
9.14 Transactions de base de données
Une transaction de base de données se compose
des éléments suivants :
•Des instructions LMD qui apportent une modification cohérente aux données
•Une instruction LDD
•Une instruction LCD (langage de contrôle de données)
9.15 Transactions de base de données : Début et fin
Transactions de base de données : Début et fin
Quand commence et quand se termine une transaction ?
64
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Une transaction commence lors de l'exécution de la première instruction LMD.
Elle se termine dans l'un des cas suivants :
•Une instruction COMMIT ou ROLLBACK est exécutée.
•Une instruction LDD, telle que CREATE, est exécutée.
•Une instruction LCD est exécutée.
•L'utilisateur quitte SQL Developer ou SQL*Plus.
•Un ordinateur ou le système connaît une défaillance.
Après la fin d'une transaction, l'instruction SQL exécutable suivante démarre
automatiquement la transaction suivante.
Une instruction LDD ou LCD est validée (commit) automatiquement, ce qui met fin de
façon implicite à la transaction
9.16 Avantages des instructions COMMIT et ROLLBACK
Avec les instructions COMMIT et ROLLBACK, vous pouvez :
•garantir la cohérence des données
•prévisualiser les modifications apportées aux données
avant de les rendre définitives
•regrouper de façon logique les opérations associées
9.17 Instructions explicites de contrôle
des transactions
Instructions explicites de contrôle des transactions
Vous pouvez contrôler la logique des transactions à l'aide des instructions COMMIT,
SAVEPOINT et ROLLBACK.
Remarque : Vous ne pouvez pas procéder à une validation (COMMIT) jusqu'à une
instruction SAVEPOINT. SAVEPOINT ne fait pas partie du langage SQL normalisé
par l'ANSI.
9.18 Annuler des modifications jusqu'à un marqueur
UPDATE...
SAVEPOINT update_done;
INSERT...
65
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
ROLLBACK TO update_done;
Annuler des modifications jusqu'à un marqueur
Vous pouvez créer un marqueur dans la transaction en cours à l'aide de l'instruction
SAVEPOINT, qui divise la transaction en sections de plus petite taille. Vous pouvez
ensuite annuler les modifications en attente jusqu'à ce marqueur à l'aide de
l'instruction ROLLBACK TO SAVEPOINT.
Notez que si vous créez un deuxième savepoint portant le même nom qu'un
savepoint antérieur, ce dernier est supprimé.
9.19 Traitement implicite des transactions
•Une validation automatique a lieu dans les cas suivants :
–Une instruction LDD est exécutée.
–Une instruction LCD est exécutée.
–Fermeture normale de SQL Developer ou de SQL*Plus,
sans exécution explicite d'instructions COMMIT ou ROLLBACK.
•Une annulation automatique se produit en cas d'arrêt anormal de SQL Developer ou
de SQL*Plus, ou de défaillance du système.
9.20 Etat des données avant exécution de l'instruction COMMIT ou
ROLLBACK
Etat des données avant exécution de l'instruction COMMIT ou ROLLBACK
Toute modification de données effectuée au cours de la transaction est temporaire
jusqu'à la validation (commit) de la transaction.
L'état des données avant exécution des instructions COMMIT ou ROLLBACK peut
être décrit comme suit :
•Les opérations de manipulation des données affectent initialement le tampon de la
base de données. L'état antérieur des données peut donc être récupéré.
•L'utilisateur actuel peut visualiser les résultats des opérations de manipulation des
données en interrogeant les tables.
•Les autres utilisateurs ne peuvent pas afficher les résultats des opérations de
manipulation des données effectuées par l'utilisateur actuel. Le serveur Oracle
institue la cohérence en lecture afin que chaque utilisateur voit les données dans
l'état où elles se trouvaient lors de la dernière validation.
•Les lignes affectées sont verrouillées. Les autres utilisateurs ne peuvent donc pas
modifier les données de ces lignes.
9.21 Etat des données après exécution
de l'instruction COMMIT
Etat des données après exécution de l'instruction COMMIT
Rendez définitives toutes les modifications en attente à l'aide de l'instruction
COMMIT. Voici ce qui se produit après l'exécution d'une instruction COMMIT :
•Les modifications apportées aux données sont écrites dans la base.
•L'état antérieur des données ne peut plus être obtenu avec des interrogations SQL
normales.
•Tous les utilisateurs peuvent visualiser les résultats de la transaction.
•Les verrous externes (locks) des lignes affectées sont libérés et ces lignes peuvent
de nouveau être modifiées par les autres utilisateurs.
66
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
•Tous les savepoints sont effacés.
9.22 Valider les données
DELETE FROM employees
WHERE employee_id = 99999;
INSERT INTO departments
VALUES (290, 'Corporate Tax', NULL, 1700);
COMMIT;
Valider des données
Dans l'exemple de la diapositive ci-dessus, une ligne est supprimée de la table
EMPLOYEES et une nouvelle ligne est insérée dans la table DEPARTMENTS. Les
modifications sont enregistrées par l'exécution de l'instruction COMMIT.
Exemple :
Supprimez les départements 290 et 300 de la table DEPARTMENTS, et mettez à
jour une ligne de la table EMPLOYEES. Enregistrez la modification apportée aux
données.
DELETE FROM departments
WHERE department_id IN (290, 300);
UPDATE employees
SET department_id = 80
WHERE employee_id = 206;
COMMIT;
9.23 Etat des données après exécution
de l'instruction ROLLBACK
DELETE FROM copy_emp;
ROLLBACK ;
Etat des données après exécution de l'instruction ROLLBACK
Annulez toutes les modifications en attente à l'aide de l'instruction ROLLBACK, ce
qui entraîne les opérations suivantes :
•Les modifications apportées aux données sont annulées.
•L'état antérieur des données est restauré.
•Les verrous externes des lignes affectées sont libérés.
9.24 Etat des données après exécution de l'instruction
ROLLBACK : Exemple
DELETE FROM test;
25,000 rows deleted.
ROLLBACK;
67
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Rollback complete.
DELETE FROM test WHERE
1 row deleted.
id = 100;
SELECT * FROM
test WHERE
No rows selected.
id = 100;
COMMIT;
Commit complete.
Etat des données après exécution de l'instruction ROLLBACK : Exemple
Lors d'une tentative de suppression d'un enregistrement de la table TEST, vous
risquez de vider toute la table de façon accidentelle. Toutefois, vous pouvez corriger
l'erreur, puis exécuter l'instruction appropriée avant de rendre la modification
définitive.
9.25 Implémenter la cohérence en lecture
Implémenter la cohérence en lecture
La cohérence en lecture est implémentée automatiquement. Elle conserve une copie
partielle de la base de données dans les segments d'annulation. L'image cohérente
en lecture est élaborée en utilisant les données validées (commit) de la table et les
anciennes données en cours de modification qui n'ont pas encore été validées à
partir du segment d'annulation.
Lorsqu'une opération d'insertion, de mise à jour ou de suppression est lancée sur la
base, le serveur Oracle enregistre une copie des données dans un segment
d'annulation avant qu'elles soient modifiées.
Tous les utilisateurs qui lisent les données, à l'exception de celui qui effectue la
modification, continuent à voir la base telle qu'elle était avant la modification. En
réalité, il s'agit du "cliché" (snapshot) des données qui figure dans le segment
d'annulation.
Seul l'utilisateur qui modifie les données voit les changements apportés à la base
avant leur validation. Tous les autres utilisateurs voient le cliché du segment
d'annulation. Les utilisateurs lisent ainsi des données cohérentes qui ne sont pas en
cours de modification.
Une fois que l'instruction LMD est validée, la modification apportée à la base devient
visible pour tout utilisateur qui exécute une instruction SELECT. L'espace occupé par
les anciennes données dans le segment d'annulation est libéré et peut être réutilisé.
68
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Si la transaction est annulée (rollback), les modifications le sont également :
•La version d'origine (l'ancienne version) des données, qui figure dans le segment
d'annulation, est réécrite dans la table.
•Tous les utilisateurs voient la base telle qu'elle était avant le début de la transaction.
9.26 Clause FOR UPDATE dans une instruction SELECT
SELECT employee_id, salary, commission_pct, job_id
FROM employees
WHERE job_id = 'SA_REP'
FOR UPDATE
ORDER BY employee_id;
Clause FOR UPDATE dans une instruction SELECT
Lorsque vous exécutez une instruction SELECT sur la base de données pour
interroger certains enregistrements, les lignes sélectionnées ne sont pas verrouillées.
Cela est généralement dû au fait que le nombre d'enregistrements verrouillés
simultanément est
(par défaut) maintenu le plus faible possible. Seuls les enregistrements modifiés
mais non encore validés sont verrouillés. Malgré tout, les autres utilisateurs peuvent
lire ces enregistrements tels qu'ils apparaissaient avant la modification (image
"avant" des données). Toutefois, dans certains cas, vous pouvez avoir besoin de
verrouiller un ensemble d'enregistrements avant leur modification dans le
programme. La clause FOR UPDATE de l'instruction SELECT proposée par Oracle
permet d'effectuer cette opération.
Lorsque vous exécutez une instruction SELECT...FOR UPDATE, le système de
gestion de base de données relationnelle (SGBDR) obtient automatiquement des
verrous externes (locks) exclusifs de niveau ligne sur toutes les lignes identifiées par
l'instruction SELECT, les réservant ainsi uniquement pour vos modifications.
Personne d'autre ne peut modifier ces enregistrements jusqu'à ce que vous
exécutiez une instruction ROLLBACK ou COMMIT.
Vous pouvez ajouter le mot-clé facultatif NOWAIT à la fin de la clause FOR UPDATE
pour indiquer au serveur Oracle de ne pas attendre si la table a été verrouillée par un
autre utilisateur. Dans ce cas, le programme ou l'environnement SQL Developer
reprend immédiatement le contrôle de sorte que vous puissiez effectuer d'autres
tâches ou simplement patienter un peu avant de réessayer. En l'absence de la
clause NOWAIT, le traitement reste bloqué jusqu'à la mise à disposition de la table
(libération des verrous externes par l'autre utilisateur via l'exécution d'une commande
COMMIT ou ROLLBACK).
9.27 Clause FOR UPDATE : Exemples
SELECT e.employee_id, e.salary, e.commission_pct
FROM employees e JOIN departments d
USING (department_id)
WHERE job_id = 'ST_CLERK'
AND location_id = 1500
FOR UPDATE
ORDER BY e.employee_id;
Clause FOR UPDATE : Exemples
69
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Dans l'exemple de la diapositive ci-dessus, l'instruction verrouille les lignes de la
table EMPLOYEES pour lesquelles JOB_ID = ST_CLERK et LOCATION_ID = 1500,
ainsi que les lignes de la table DEPARTMENTS pour lesquelles LOCATION_ID =
1500.
Vous pouvez utiliser FOR UPDATE OF column_name pour qualifier la colonne que
vous avez l'intention de modifier. La liste OF de la clause FOR UPDATE ne vous
empêche pas de modifier d'autres colonnes que celles des lignes sélectionnées. Des
verrous externes sont placés sur toutes les lignes. Si vous indiquez simplement FOR
UPDATE dans l'interrogation et n'incluez aucune colonne après le mot-clé OF, la
base de données verrouille toutes les lignes identifiées dans toutes les tables
énumérées dans la clause FROM.
L'instruction suivante verrouille uniquement les lignes de la table EMPLOYEES pour
lesquelles la valeur de LOCATION_ID est 1500 pour ST_CLERK. Aucune ligne n'est
verrouillée dans la table DEPARTMENTS :
SELECT e.employee_id, e.salary, e.commission_pct
FROM employees e JOIN departments d
USING (department_id)
WHERE job_id = 'ST_CLERK' AND location_id = 1500
FOR UPDATE OF e.salary
ORDER BY e.employee_id;
10 Guide Exercice 10
Utiliser des instructions LDD pour créer
et gérer des tables
10.1 Objets de base de données
Objets de base de données
Une base Oracle peut contenir plusieurs structures de données. Chaque structure
doit être exposée dans la conception de la base afin de pouvoir être créée au cours
de la construction de la base.
•Table : Stocke des données.
•Vue : Sous-ensemble de données issues d'une ou de plusieurs tables.
•Séquence : Génère des valeurs numériques.
•Index : Améliore les performances de certaines interrogations.
•Synonyme : Attribue un autre nom à un objet.
Tables Oracle
70
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
•Il est possible de créer des tables à tout moment, y compris pendant que des
utilisateurs exploitent la base.
•Il est inutile d'indiquer la taille d'une table. Au bout du compte, la taille est la quantité
d'espace allouée à la base de données dans son ensemble. Il est toutefois important
d'estimer la quantité d'espace qu'utilisera une table au fil du temps.
•La structure de la table peut être modifiée pendant que celle-ci est en ligne.
Remarque : Il existe d'autres objets de base de données, mais ils ne sont pas
étudiés dans le cadre de ce cours.
10.2 Règles d'appellation
Règles d'appellation
Vous devez nommer les colonnes et les tables en respectant les règles standard
d'appellation des objets de base de données Oracle :
•Les noms de table et de colonne doivent commencer par une lettre et comporter
entre 1 et 30 caractères.
•Ils admettent uniquement les caractères A–Z, a–z, 0–9, _ (trait de soulignement), $
et # (caractères autorisés, mais déconseillés).
•Les noms ne doivent pas être identiques au nom d'un autre objet appartenant au
même utilisateur du serveur Oracle.
•Les noms ne doivent pas être des mots réservés au serveur Oracle.
-Vous pouvez indiquer le nom d'un objet entre guillemets, auquel cas vous devez
utiliser les guillemets chaque fois que vous faites référence à cet objet. Les
identificateurs entre guillemets peuvent être des mots réservés, mais cela n'est pas
recommandé.
Règles d'appellation
Attribuez des noms descriptifs aux tables et aux autres objets de base de données.
Remarque : Pour les noms standard, il n'y a pas de distinction entre majuscules et
minuscules. Par exemple, EMPLOYEES est traité comme eMPloyees ou
eMpLOYEES. En revanche, la distinction est effectuée pour les identificateurs entre
guillemets.
Pour plus d'informations, reportez-vous à la section Schema Object Names and
Qualifiers du manuel Oracle Database SQL Language Reference 11g, Release 1
(11.1).
10.3 Instruction CREATE TABLE
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
Instruction CREATE TABLE
Pour créer des tables permettant de stocker des données, exécutez l'instruction SQL
CREATE TABLE. Cette instruction fait partie du sous-ensemble des instructions SQL
LDD, qui sont utilisées pour créer, modifier ou supprimer des structures de base de
données Oracle. Ces instructions ont un effet immédiat sur la base, mais enregistrent
également des informations dans le dictionnaire de données.
Pour créer une table, un utilisateur doit disposer du privilège CREATE TABLE ainsi
que d'une zone de stockage dans laquelle créer des objets. L'administrateur de base
71
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
de données (DBA) utilise des instructions LCD (langage de contrôle de données)
pour accorder des privilèges aux utilisateurs.
Dans la syntaxe :
schema
Correspond au nom du propriétaire.
table
Correspond au nom de la table.
DEFAULT expr
Indique une valeur par défaut lorsqu'une valeur est omise
dans l'instruction INSERT.
column
Correspond au nom de la colonne.
datatype
Correspond au type de données et à la longueur de la colonne.
10.4 Référencer les tables d'un autre utilisateur
Référencer les tables d'un autre utilisateur
Un schéma est un ensemble de structures logiques de données ou d'objets de
schéma. Un schéma appartient à un utilisateur de la base et porte le même nom que
cet utilisateur. Chaque utilisateur possède un seul schéma.
Les différents objets de schéma peuvent être créés et manipulés à l'aide
d'instructions SQL : tables, vues, synonymes, séquences, procédures stockées,
index, clusters et liens de base de données.
Pour référencer une table qui ne vous appartient pas, vous devez lui ajouter un
préfixe correspondant au nom de son propriétaire. Par exemple, si des schémas
nommés USERA et USERB comportent tous deux une table EMPLOYEES et que
USERA souhaite accéder à la table EMPLOYEES appartenant à USERB, USERA
doit ajouter le nom de schéma comme préfixe au nom de la table :
SELECT *
FROM userb.employees;
Si USERB souhaite accéder à la table EMPLOYEES appartenant à USERA, il doit de
même ajouter le nom de schéma au nom de la table :
SELECT *
FROM usera.employees;
10.5 Option DEFAULT
CREATE TABLE hire_dates
(id
NUMBER(8),
hire_date DATE DEFAULT SYSDATE);
Option DEFAULT
Lorsque vous définissez une table, vous pouvez indiquer qu'une colonne doit se voir
attribuer une valeur par défaut, à l'aide de l'option DEFAULT. Cette option empêche
l'introduction de valeurs NULL dans la colonne lors de l'insertion de lignes qui ne
contiennent pas de valeur pour la colonne. La valeur par défaut peut être un littéral,
72
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
une expression ou une fonction SQL (par exemple SYSDATE ou USER), mais il ne
peut pas s'agir du nom d'une autre colonne ou d'une pseudo-colonne (par exemple
NEXTVAL ou CURRVAL). L'expression par défaut doit présenter le même type de
données que la colonne.
Considérons les exemples suivants :
INSERT INTO hire_dates values(45, NULL);
L'instruction ci-dessus insérera la valeur NULL à la place de la valeur par défaut.
INSERT INTO hire_dates(id) values(35);
L'instruction ci-dessus insérera SYSDATE pour la colonne HIRE_DATE.
Remarque : Dans SQL Developer, pour exécuter les instructions LDD, cliquez sur
l'icône Run Script ou appuyez sur [F5]. Les messages renvoyés apparaissent dans
l'onglet Script Output.
10.6 Créer des tables
CREATE TABLE dept
(deptno
dname
loc
create_date
NUMBER(2),
VARCHAR2(14),
VARCHAR2(13),
DATE DEFAULT SYSDATE);
DESCRIBE dept
Créer des tables
L'exemple de la diapositive ci-dessus crée la table DEPT, qui comporte quatre
colonnes : DEPTNO, DNAME, LOC et CREATE_DATE. La colonne CREATE_DATE
comporte une valeur par défaut. Si aucune valeur n'est fournie pour une instruction
INSERT, la date système est automatiquement insérée.
Pour vérifier que la table a été créée, exécutez la commande DESCRIBE.
Etant donné que l'instruction de création d'une table est une instruction LDD, une
validation (commit) automatique a lieu lors de son exécution.
10.7 Types de données
Types de données
73
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Lorsque vous identifiez une colonne pour une table, vous devez lui attribuer un type
de données. Vous avez le choix entre les types suivants :
10.8 Types de données date-heure
Types de données date-heure
10.9 Inclure des contraintes
•Les contraintes appliquent des règles de niveau table.
•Elles empêchent la suppression d'une table dotée
de dépendances.
•Les types de contrainte valides sont les suivants :
–NOT NULL
–UNIQUE
–PRIMARY KEY
–FOREIGN KEY
–CHECK
10.10 Règles relatives aux contraintes
Règles relatives aux contraintes
Toutes les contraintes sont stockées dans le dictionnaire de données. Elles sont
faciles à référencer si vous leur attribuez un nom significatif. Le nom d'une contrainte
doit être conforme aux règles standard d'appellation des objets, à ceci près qu'il ne
peut pas être identique au nom d'un autre objet appartenant au même utilisateur. Si
vous n'attribuez pas de nom à une contrainte, le serveur Oracle en génère un au
format SYS_Cn, où n est un entier garantissant l'unicité de ce nom.
Les contraintes peuvent être définies lors de la création de la table ou après. Vous
pouvez définir une contrainte de niveau colonne ou table. D'un point de vue
fonctionnel, une contrainte de niveau table est identique à une contrainte de niveau
colonne.
Pour plus d'informations, reportez-vous à la section "Constraints" du manuel Oracle
Database SQL Language Reference 11g, Release 1 (11.1).
10.11 Définir des contraintes
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
column [CONSTRAINT constraint_name] constraint_type,
74
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
Définir des contraintes
La diapositive ci-dessus fournit la syntaxe à utiliser pour la définition de contraintes
lors de la création d'une table. Vous pouvez créer des contraintes de niveau colonne
ou de niveau table. Les contraintes de niveau colonne sont incluses lors de la
définition de la colonne. Les contraintes de niveau table sont définies après la table.
Elles doivent référencer la ou les colonnes auxquelles elles se rapportent, entre
parenthèses. Les contraintes de niveau colonne et de niveau table se distinguent
essentiellement par leur syntaxe. Elles sont identiques d'un point de vue fonctionnel.
Les contraintes NOT NULL doivent être définies au niveau colonne.
Les contraintes qui s'appliquent à plusieurs colonnes doivent être définies au niveau
table.
Dans la syntaxe :
schema
Correspond au nom du propriétaire.
table Correspond au nom de la table.
DEFAULT expr
Indique la valeur par défaut à utiliser lorsqu'une valeur
est omise dans l'instruction INSERT.
column
Correspond au nom de la colonne.
datatype
Correspond au type de données et à la longueur
de la colonne.
column_constraint Est une contrainte d'intégrité faisant partie
de la définition de la colonne.
table_constraint
Est une contrainte d'intégrité faisant partie
de la définition de la table.
10.12 Définir des contraintes
CREATE TABLE employees(
employee_id NUMBER(6)
CONSTRAINT emp_emp_id_pk PRIMARY KEY,
first_name
VARCHAR2(20),
...);
CREATE TABLE employees(
employee_id NUMBER(6),
first_name
VARCHAR2(20),
...
job_id
VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));
Définir des contraintes
Les contraintes sont généralement créées en même temps que la table. Elles
peuvent cependant être ajoutées à une table après sa création et peuvent également
être désactivées de façon temporaire.
Les deux exemples de la diapositive ci-dessus créent une contrainte de clé primaire
sur la colonne EMPLOYEE_ID de la table EMPLOYEES.
75
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
1.
Le premier exemple utilise la syntaxe de niveau colonne pour définir la
contrainte.
2.
Le deuxième exemple utilise la syntaxe de niveau table pour définir la
contrainte.
Des détails supplémentaires sur la contrainte de clé primaire seront fournis
ultérieurement dans ce chapitre.
10.13 Contrainte NOT NULL
Contrainte NOT NULL
La contrainte NOT NULL garantit que la colonne ne contient aucune valeur NULL.
Par défaut, les colonnes non concernées par la contrainte NOT NULL peuvent
contenir des valeurs NULL. Les contraintes NOT NULL doivent être définies au
niveau colonne. Dans la table EMPLOYEES, la colonne EMPLOYEE_ID hérite d'une
contrainte NOT NULL car elle est définie en tant que clé primaire. En outre, la
contrainte NOT NULL est appliquée aux colonnes LAST_NAME, EMAIL,
HIRE_DATE et JOB_ID.
Remarque : La contrainte de clé primaire sera traitée en détail ultérieurement dans
ce chapitre.
10.14 Contrainte UNIQUE
CREATE TABLE employees(
employee_id
NUMBER(6),
last_name
VARCHAR2(25) NOT NULL,
email
VARCHAR2(25),
salary
NUMBER(8,2),
commission_pct
NUMBER(2,2),
hire_date
DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));
Contrainte UNIQUE
Les contraintes UNIQUES peuvent être définies au niveau colonne ou au niveau
table. Vous définissez une contrainte au niveau table lorsque vous souhaitez créer
une clé unique composée. Vous devez indiquer une clé composée lorsqu'il n'existe
pas d'attribut unique permettant d'identifier une ligne de façon unique. Dans ce cas,
vous pouvez créer une clé unique composée d'au moins deux colonnes dont la
combinaison permettra toujours d'identifier les lignes de façon unique.
L'exemple de la diapositive ci-dessus applique la contrainte UNIQUE à la colonne
EMAIL de la table EMPLOYEES. Le nom de la contrainte est EMP_EMAIL_UK.
Remarque : Le serveur Oracle applique la contrainte UNIQUE en créant de façon
implicite un index unique sur la ou les colonnes de clé unique.
10.15 Contrainte PRIMARY KEY
Contrainte PRIMARY KEY
Une contrainte PRIMARY KEY crée une clé primaire pour une table. Une seule clé
primaire peut être créée pour chaque table. La contrainte PRIMARY KEY définit une
colonne ou un ensemble de colonnes identifiant de façon unique chaque ligne d'une
table. Elle impose l'unicité des valeurs de la colonne ou de la combinaison de
76
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
colonnes. Par ailleurs, aucune colonne faisant partie de la clé primaire ne peut
contenir de valeur NULL.
Remarque : L'unicité fait partie de la définition d'une contrainte de clé primaire. Le
serveur Oracle l'applique en créant de façon implicite un index unique sur la ou les
colonnes de clé primaire.
10.16 Contrainte FOREIGN KEY
Contrainte FOREIGN KEY
La contrainte FOREIGN KEY (ou contrainte d'intégrité référentielle) désigne une
colonne ou une combinaison de colonnes comme clé étrangère, et établit une
relation avec une clé primaire ou une clé unique dans la même table ou dans une
autre table.
Dans l'exemple de la diapositive ci-dessus, la colonne DEPARTMENT_ID a été
définie comme clé étrangère dans la table EMPLOYEES (table enfant ou table
dépendante). Elle référence la colonne DEPARTMENT_ID de la table
DEPARTMENTS (la table parent ou table référencée).
Règles
•La valeur d'une clé étrangère doit correspondre à une valeur existante de la table
parent ou être NULL.
•Les clés étrangères sont basées sur des valeurs. Il s'agit de pointeurs purement
logiques (et non physiques).
10.17 Contrainte FOREIGN KEY
CREATE TABLE employees(
employee_id
NUMBER(6),
last_name
VARCHAR2(25) NOT NULL,
email
VARCHAR2(25),
salary
NUMBER(8,2),
commission_pct
NUMBER(2,2),
hire_date
DATE NOT NULL,
...
department_id
NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
Contrainte FOREIGN KEY (suite)
Les contraintes FOREIGN KEY peuvent être définies au niveau colonne ou au
niveau table. Une clé étrangère composée doit être créée au niveau table.
L'exemple ci-dessus définit une contrainte FOREIGN KEY sur la colonne
DEPARTMENT_ID de la table EMPLOYEES, à l'aide de la syntaxe de niveau table.
Le nom de la contrainte est EMP_DEPT_FK.
La clé étrangère peut aussi être définie au niveau colonne, à condition que la
contrainte soit basée sur une colonne unique. La syntaxe diffère dans le sens où les
mots-clés FOREIGN KEY n'apparaissent pas. Par exemple :
CREATE TABLE employees
(...
department_id NUMBER(4) CONSTRAINT emp_deptid_fk
REFERENCES departments(department_id),
...
77
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
)
10.18 Contrainte FOREIGN KEY : Mots-clés
Contrainte FOREIGN KEY : Mots-clés
La clé étrangère est définie dans la table enfant. La table contenant la colonne
référencée est la table parent. La clé étrangère est définie à l'aide d'une combinaison
des mots-clés suivants :
•FOREIGN KEY est utilisé pour définir la colonne dans la table enfant, au niveau
table.
•REFERENCES identifie la table et la colonne dans la table parent.
•ON DELETE CASCADE indique qu'en cas de suppression d'une ligne dans la table
parent les lignes dépendantes de la table enfant sont aussi supprimées.
•ON DELETE SET NULL indique qu'en cas de suppression d'une ligne dans la table
parent les valeurs de clé étrangère deviennent NULL.
Le comportement par défaut, appelé règle de restriction, désactive la mise à jour ou
la suppression des données référencées.
En l'absence de l'option ON DELETE CASCADE ou ON DELETE SET NULL, une
ligne de la table parent ne peut être supprimée que si elle est référencée dans la
table enfant.
10.19 CREATE TABLE : Exemple
CREATE TABLE employees
( employee_id
NUMBER(6)
CONSTRAINT
emp_employee_id
PRIMARY KEY
, first_name
VARCHAR2(20)
, last_name
VARCHAR2(25)
CONSTRAINT
emp_last_name_nn NOT NULL
, email
VARCHAR2(25)
CONSTRAINT
emp_email_nn
NOT NULL
CONSTRAINT
emp_email_uk
UNIQUE
, phone_number
VARCHAR2(20)
, hire_date
DATE
CONSTRAINT
emp_hire_date_nn NOT NULL
, job_id
VARCHAR2(10)
CONSTRAINT
emp_job_nn
NOT NULL
, salary
NUMBER(8,2)
CONSTRAINT
emp_salary_ck
CHECK (salary>0)
, commission_pct NUMBER(2,2)
, manager_id
NUMBER(6)
CONSTRAINT emp_manager_fk REFERENCES
employees (employee_id)
, department_id NUMBER(4)
CONSTRAINT
emp_dept_fk
REFERENCES
departments (department_id));
CREATE TABLE : Exemple
L'exemple ci-dessus présente l'instruction utilisée pour créer la table EMPLOYEES
dans le schéma HR.
78
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
10.20 Violation de contraintes
UPDATE employees
SET
department_id = 55
WHERE department_id = 110;
Violation de contraintes
Lorsque des contraintes appliquées à des colonnes sont enfreintes, une erreur est
renvoyée. C'est le cas, par exemple, si vous tentez de mettre à jour un
enregistrement dont la valeur est liée à une contrainte d'intégrité.
Dans l'exemple de la diapositive ci-dessus, le département 55 n'existe pas dans la
table parent DEPARTMENTS. L'erreur de violation ORA-02291 "parent key not
found" est générée.
10.21 Violation de contraintes
DELETE FROM departments
WHERE department_id = 60;
Violation de contraintes
Si vous tentez de supprimer un enregistrement dont la valeur est liée à une
contrainte d'intégrité, une erreur est renvoyée.
L'exemple de la diapositive tente de supprimer le département 60 de la table
DEPARTMENTS. Cela entraîne la génération d'une erreur car cet ID de département
est utilisé comme clé étrangère dans la table EMPLOYEES. Si l'enregistrement
parent
que vous tentez de supprimer a des enregistrements enfant, vous recevez l'erreur
de violation ORA-02292 "child record found".
L'instruction suivante fonctionne car le département 70 ne compte aucun employé :
DELETE FROM departments
79
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
WHERE department_id = 70;
10.22 Créer une table
à l'aide d'une sous-interrogation
CREATE TABLE table
[(column, column...)]
AS subquery;
Créer une table à l'aide d'une sous-interrogation
Une deuxième méthode permettant de créer une table consiste à appliquer la clause
AS subquery, qui crée la table et y insère les lignes renvoyées par la sousinterrogation.
Dans la syntaxe :
table Est le nom de la table.
column
Définit le nom de la colonne, la valeur par défaut et la contrainte
d'intégrité.
subquery
Est l'instruction SELECT qui définit l'ensemble de lignes à insérer
dans la nouvelle table.
Règles
•La table est créée avec les noms de colonne indiqués, et les lignes extraites par
l'instruction SELECT sont insérées dans la table.
•La définition des colonnes peut contenir uniquement le nom de la colonne et la
valeur par défaut.
•Si des spécifications de colonnes sont précisées, le nombre de colonnes doit être
égal à celui des colonnes de la liste SELECT de la sous-interrogation.
•Si aucune spécification de colonne n'est indiquée, les noms de colonne de la table
sont les mêmes que ceux de la sous-interrogation.
•Les définitions de type de données de colonne et la contrainte NOT NULL sont
transmis à la nouvelle table. Seule la contrainte NOT NULL explicite est héritée. La
colonne PRIMARY KEY ne transmet pas la caractéristique NOT NULL à la nouvelle
colonne. Aucune autre règle de contrainte n'est transmise à la nouvelle table. Vous
pouvez toutefois ajouter des contraintes dans la définition de la colonne.
10.23 Créer une table
à l'aide d'une sous-interrogation
CREATE TABLE
dept80
AS
SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM
employees
WHERE
department_id = 80;
Créer une table à l'aide d'une sous-interrogation
L'exemple ci-dessus crée une table nommée DEPT80, qui contient les détails de
tous les employés travaillant dans le département 80. Notez que les données de
cette table proviennent de la table EMPLOYEES.
Vous pouvez vérifier l'existence d'une table de base de données, ainsi que la
définition de ses colonnes, à l'aide de la commande DESCRIBE.
80
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Veillez toutefois à fournir un alias de colonne lors de la sélection d'une expression.
L'expression SALARY*12 se voit attribuer l'alias ANNSAL. Sans cet alias, l'erreur
suivante serait générée :
10.24 Instruction ALTER TABLE
Instruction ALTER TABLE
Vous pouvez avoir besoin de modifier la structure d'une table existante pour les
raisons suivantes :
•Vous avez omis une colonne.
•Vous devez modifier la définition ou le nom d'une colonne.
•Vous devez supprimer des colonnes.
•Vous souhaitez placer la table en mode lecture seule.
Utilisez dans ce cas l'instruction ALTER TABLE.
10.25 Tables en lecture seule
ALTER TABLE employees READ ONLY;
-- perform table maintenance and then
-- return table back to read/write mode
ALTER TABLE employees READ WRITE;
Tables en lecture seule
Avec Oracle Database 11g, vous pouvez indiquer READ ONLY pour placer une table
en mode lecture seule. Lorsque la table est dans ce mode, vous ne pouvez pas
exécuter d'instructions LMD affectant la table, ni d'instructions SELECT ... FOR
UPDATE. Vous pouvez exécuter des instructions LDD à condition qu'elles ne
modifient aucune donnée de la table. Lorsque la table est en mode lecture seule, les
opérations sur les index associés sont autorisées.
Pour replacer la table en mode lecture/écriture, indiquez READ/WRITE.
Remarque : Vous pouvez supprimer une table qui est en mode READ ONLY. En
effet, la commande DROP étant exécutée uniquement dans le dictionnaire de
données, l'accès au contenu de la table n'est pas requis. L'espace utilisé par la table
n'est réclamé que lorsque le tablespace repasse en mode lecture/écriture. Les
modifications requises peuvent alors être apportées aux en-têtes de segment de
bloc, etc.
10.26 Supprimer une table
DROP TABLE dept80;
Supprimer une table
L'instruction DROP TABLE place une table dans la corbeille, ou supprime
entièrement la table et l'ensemble de ses données de la base. Si vous n'indiquez pas
la clause PURGE, l'instruction DROP TABLE n'entraîne pas de restitution d'espace
au tablespace pour d'autres objets. L'espace reste décompté du quota alloué à
l'utilisateur. La suppression d'une table invalide les objets dépendants et supprime
les privilèges objet associés à la table.
81
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Lorsque vous supprimez une table, la base perd toutes les données de cette table,
de même que tous les index associés.
Syntaxe
DROP TABLE table [PURGE]
Dans la syntaxe, table correspond au nom de la table.
Règles
•Toutes les données sont supprimées de la table.
•Les vues et les synonymes sont conservés, mais ne sont plus valides.
•Toutes les transactions en attente sont validées (commit).
•Seul le créateur de la table ou un utilisateur disposant du privilège DROP ANY
TABLE peut supprimer une table.
Remarque : Vous pouvez utiliser l'instruction FLASHBACK TABLE pour restaurer
une table supprimée à partir de la corbeille. Cette opération est traitée en détail dans
le cours Oracle Database 11g : Les fondamentaux du langage SQL (II).
82
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
11 Guide Exercice 11
Créer d'autres objets de schéma
11.1 Vue : Présentation
Vue : Présentation
Vous pouvez afficher des sous-ensembles ou des combinaisons logiques de
données en créant des vues des tables. Une vue est une table logique basée sur
une table ou sur une autre vue. Elle ne contient aucune donnée propre, mais
s'apparente à une fenêtre par l'intermédiaire de laquelle les données des tables
peuvent être consultées ou modifiées. Les tables sur lesquelles une vue est basée
sont appelées tables de base. Une vue est stockée en tant qu'instruction SELECT
dans le dictionnaire de données.
Avantages des vues
•Les vues limitent l'accès aux données car elles affichent uniquement des colonnes
spécifiques d'une table.
•Elles permettent d'extraire les résultats d'interrogations complexes en exécutant des
interrogations simples. Par exemple, un utilisateur peut y recourir pour afficher les
informations de plusieurs tables sans savoir comment écrire une instruction de
jointure.
•Indépendantes des données, les vues conviennent aux utilisateurs et aux
applications ad hoc. Une même vue peut en effet être utilisée pour extraire des
données de plusieurs tables.
•Les vues permettent à des groupes d'utilisateurs d'accéder aux données en fonction
de critères particuliers.
Pour plus d'informations, reportez-vous à la section "CREATE VIEW" du manuel
Oracle Database SQL Language Reference 11g, Release 1 (11.1).
11.2 Créer une vue
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
83
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Créer une vue
Vous pouvez créer une vue en imbriquant une sous-interrogation dans l'instruction
CREATE VIEW.
Dans la syntaxe :
OR REPLACE
Recrée la vue si elle existe déjà.
FORCE
Crée la vue, que les tables de base existent ou non.
NOFORCE Ne crée la vue que si les tables de base existent
(il s'agit de l'option par défaut).
view Est le nom de la vue.
alias Indique le nom des expressions sélectionnées par l'interrogation
de la vue. (Le nombre d'alias doit correspondre au nombre
d'expressions sélectionnées par la vue.)
subquery
Est une instruction SELECT complète. (Vous pouvez utiliser
des alias pour les colonnes de la liste SELECT.)
WITH CHECK OPTION Indique que seules les lignes accessibles par la vue
peuvent
être insérées ou mises à jour.
constraint
Est le nom attribué à la contrainte CHECK OPTION.
WITH READ ONLY Garantit qu'aucune opération LMD ne peut être effectuée
sur la vue.
Remarque : Dans SQL Developer, pour exécuter des instructions LDD (langage de
définition de données), cliquez sur l'icône Run Script ou appuyez sur [F5]. Les
messages renvoyés apparaissent dans l'onglet Script Output.
11.3 Créer une vue
CREATE VIEW
empvu80
AS SELECT employee_id, last_name, salary
FROM
employees
WHERE
department_id = 80;
Créer une vue
L'exemple de la diapositive crée une vue contenant le numéro, le nom et le salaire
de chaque employé du département 80.
Vous pouvez afficher la structure de la vue à l'aide de la commande DESCRIBE.
Règles
•La sous-interrogation qui définit une vue peut contenir une syntaxe SELECT
complexe, incluant des jointures, des groupes et des sous-interrogations.
•Si vous ne précisez pas de nom de contrainte pour une vue créée avec l'option
WITH CHECK OPTION, le système attribue à la contrainte un nom par défaut de la
forme SYS_Cn.
•Vous pouvez utiliser l'option OR REPLACE pour changer la définition d'une vue.
Cela vous évite de supprimer la vue, de la recréer, puis d'accorder à nouveau les
privilèges objet.
11.4 Créer une vue
CREATE VIEW
salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
84
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
FROM
WHERE
employees
department_id = 50;
Créer une vue
Vous pouvez contrôler les noms de colonne en incluant des alias de colonne dans la
sousinterrogation.
L'exemple de la diapositive ci-dessus crée une vue contenant le numéro d'employé
(EMPLOYEE_ID) avec l'alias ID_NUMBER, le nom (LAST_NAME) avec l'alias
NAME et le salaire annuel (SALARY) avec l'alias ANN_SALARY pour chaque
employé du département 50.
Vous pouvez également utiliser des alias après l'instruction CREATE et avant la
sous-interrogation SELECT. Le nombre d'alias doit correspondre au nombre
d'expressions sélectionnées dans la sous-interrogation.
CREATE OR REPLACE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)
AS SELECT employee_id, last_name, salary*12
FROM employees
WHERE department_id = 50;
11.5 Extraire des données d'une vue
SELECT *
FROM
salvu50;
Extraire des données d'une vue
Vous pouvez extraire des données d'une vue, tout comme à partir de n'importe
quelle table. Vous pouvez afficher le contenu de la vue entière, ou simplement des
lignes et des colonnes spécifiques.
11.6 Modifier une vue
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' '
|| last_name, salary, department_id
FROM
employees
WHERE
department_id = 80;
Modifier une vue
L'option OR REPLACE permet de créer une vue même s'il en existe déjà une portant
ce nom. La version la plus ancienne est alors remplacée pour son propriétaire. Cela
signifie qu'il est possible de modifier une vue sans avoir à la supprimer, puis à la
recréer et à accorder de nouveau les privilèges objet.
Remarque : Lors de l'attribution d'alias de colonne dans la clause CREATE OR
REPLACE VIEW, rappelez-vous que les alias sont listés dans le même ordre que les
colonnes de la sousinterrogation.
11.7 Créer une vue complexe
CREATE OR REPLACE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
85
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
AS SELECT
d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id)
GROUP BY d.department_name;
Créer une vue complexe
L'exemple de la diapositive ci-dessus crée une vue complexe comportant le nom et
les salaires minimum, maximum et moyen de chaque département. Notez que des
alias ont été indiqués pour cette vue. Cette condition est obligatoire si une colonne
de la vue est dérivée d'une fonction ou d'une expression.
Vous pouvez afficher la structure de la vue à l'aide de la commande DESCRIBE.
Affichez le contenu de la vue en exécutant une instruction SELECT.
SELECT *
FROM dept_sum_vu;
11.8 Règles relatives à l'exécution
d'opérations LMD sur une vue
Règles relatives à l'exécution d'opérations LMD sur une vue
Vous pouvez exécuter des opérations LMD sur des données par l'intermédiaire d'une
vue si ces opérations respectent certaines règles.
Vous pouvez supprimer une ligne d'une vue, sauf si celle-ci contient l'un des
éléments suivants :
•des fonctions de groupe,
•une clause GROUP BY,
•le mot-clé DISTINCT,
•le mot-clé ROWNUM de pseudo-colonne.
11.9 Utiliser la clause WITH CHECK OPTION
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM
employees
WHERE
department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
Utiliser la clause WITH CHECK OPTION
Il est possible d'effectuer des vérifications d'intégrité référentielle par l'intermédiaire
de vues. Vous pouvez également appliquer des contraintes au niveau de la base de
données. Les vues peuvent être utilisées pour préserver l'intégrité des données,
mais cette utilisation est très limitée.
La clause WITH CHECK OPTION indique que les opérations INSERT et UPDATE
effectuées par l'intermédiaire de la vue ne peuvent pas créer de lignes que la vue ne
peut pas sélectionner. Ceci permet d'appliquer des contraintes d'intégrité et des
vérifications de validation sur les données à insérer ou à mettre à jour. Si un
utilisateur tente de lancer des opérations LMD sur des lignes que la vue n'a pas
sélectionnées, une erreur s'affiche, avec le nom de la contrainte s'il a été indiqué.
UPDATE empvu20
SET department_id = 10
WHERE employee_id = 201;
86
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
entraîne :
Remarque : Aucune ligne n'est mise à jour car, si l'ID de département en cours était
remplacé par l'ID 10, la vue n'afficherait plus les employés concernés. Par
conséquent, avec la clause WITH CHECK OPTION, la vue ne peut afficher que les
employés du département 20 et l'ID de département de ces employés ne peut pas
être modifié par l'intermédiaire de la vue.
11.10 Empêcher les opérations LMD
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM
employees
WHERE
department_id = 10
WITH READ ONLY ;
Empêcher les opérations LMD
Vous pouvez empêcher l'exécution d'opérations LMD sur une vue en créant cette
dernière avec l'option WITH READ ONLY. L'exemple ci-dessus modifie la vue
EMPVU10 de façon à empêcher l'exécution d'opérations LMD sur celle-ci.
Toute tentative de suppression d'une ligne dans une vue définie avec une contrainte
de lecture seule provoque une erreur :
DELETE FROM empvu10
WHERE employee_number = 200;
De manière identique, toute tentative d'insertion ou de modification d'une ligne à
l'aide d'une vue définie avec une contrainte de lecture seule se traduit par la même
erreur.
11.11 Supprimer une vue
DROP VIEW empvu80;
Supprimer une vue
Utilisez l'instruction DROP VIEW pour supprimer une vue. Cette instruction supprime
la définition de la vue dans la base de données. Toutefois, la suppression d'une vue
n'a aucune incidence sur les tables sur lesquelles la vue est basée. En revanche, les
vues ou autres applications basées sur la vue supprimée ne sont plus valides. Une
vue ne peut être supprimée que par son créateur ou par un utilisateur doté du
privilège DROP ANY VIEW.
Dans la syntaxe :
view Est le nom de la vue.
87
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
11.12 Séquences
Séquences
Une séquence est un objet de base de données créé par un utilisateur, qui peut être
partagé par plusieurs utilisateurs pour générer des nombres entiers.
Vous pouvez définir une séquence pour générer des valeurs uniques, ou pour
recycler des numéros afin de les réutiliser.
Les séquences sont généralement utilisées pour générer des valeurs de clé primaire,
lesquelles doivent être uniques pour chaque ligne. Une séquence est générée et
incrémentée (ou décrémentée) par un sous-programme Oracle interne. Cet objet
permet de gagner du temps, en réduisant la quantité de code applicatif nécessaire à
l'écriture d'un sous-programme de génération de séquence.
Les numéros de séquence sont stockés et générés indépendamment des tables.
Ainsi, la même séquence peut être utilisée pour plusieurs tables.
11.13 Instruction CREATE SEQUENCE : Syntaxe
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
Instruction CREATE SEQUENCE
Générez automatiquement des numéros séquentiels à l'aide de l'instruction CREATE
SEQUENCE.
Dans la syntaxe :
sequence
Est le nom du générateur de numéros de séquence.
INCREMENT BY n Indique l'intervalle entre les numéros de la séquence,
n étant un nombre entier. (Si cette clause est omise,
les numéros sont incrémentés de 1.)
START WITH n
Indique le premier numéro de séquence à générer.
(Si cette clause est omise, la séquence commence par 1.)
MAXVALUE n
Indique la valeur maximale pouvant être générée
par la séquence.
NOMAXVALUE
Indique une valeur maximale de 10^27 pour une séquence
croissante et de –1 pour une séquence décroissante.
(Il s'agit de l'option par défaut.)
MINVALUE n
Indique la valeur minimale de la séquence.
NOMINVALUE
Indique une valeur maximale de 1 pour une séquence
croissante et de –(10^26) pour une séquence décroissante.
(Il s'agit de l'option par défaut.)
88
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
11.14 Créer une séquence
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
Créer une séquence (suite)
CYCLE | NOCYCLE
valeurs
CACHE n | NOCACHE
conservées
Indique si la séquence continue de générer des
une fois la valeur maximale ou minimale atteinte.
(NOCYCLE est l'option par défaut.)
Indique le nombre de valeurs préallouées et
en mémoire par le serveur Oracle. (Par défaut,
le serveur Oracle conserve 20 valeurs dans la
mémoire
cache.)
L'exemple de la diapositive crée une séquence nommée DEPT_DEPTID_SEQ à
utiliser pour la colonne DEPARTMENT_ID de la table DEPARTMENTS. La séquence
démarre à 120, ne permet pas la mise en mémoire cache et n'utilise pas l'option
CYCLE.
N'utilisez pas l'option CYCLE si la séquence est utilisée pour générer des valeurs de
clé primaire, sauf si vous disposez d'un mécanisme fiable qui supprime les
anciennes lignes plus rapidement que les valeurs de la séquence ne se recyclent.
11.15 Pseudo-colonnes NEXTVAL et CURRVAL
Pseudo-colonnes NEXTVAL et CURRVAL
Dès lors qu'une séquence est créée, elle génère des numéros de séquence pouvant
être utilisés dans les tables. Vous référencez les valeurs de la séquence à l'aide des
pseudocolonnes NEXTVAL et CURRVAL.
La pseudo-colonne NEXTVAL est utilisée pour extraire les numéros successifs d'une
séquence déterminée. Vous devez qualifier NEXTVAL avec le nom de la séquence.
Lorsque vous référencez sequence.NEXTVAL, un nouveau numéro de séquence est
généré et le numéro de séquence actuel est placé dans CURRVAL.
La pseudo-colonne CURRVAL permet de faire référence au numéro de séquence
que l'utilisateur actuel vient de générer. Pour cela, NEXTVAL doit être utilisée au
préalable pour générer un numéro de séquence dans la session de l'utilisateur
actuel. Vous devez qualifier CURRVAL avec le nom de la séquence. Lorsque vous
référencez sequence.CURRVAL, la dernière valeur renvoyée au processus de
l'utilisateur s'affiche.
11.16 Utiliser une séquence
INSERT INTO departments(department_id,
department_name, location_id)
VALUES
(dept_deptid_seq.NEXTVAL,
'Support', 2500);
89
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
SELECT
dept_deptid_seq.CURRVAL
FROM dual;
Utiliser une séquence
L'exemple de la diapositive ci-dessus insère un nouveau département dans la table
DEPARTMENTS. Il utilise la séquence DEPT_DEPTID_SEQ pour générer un nouvel
ID de département comme suit.
Vous pouvez afficher la valeur actuelle de la séquence à l'aide de
sequence_name.CURRVAL, comme illustré dans le deuxième exemple cidessus. Le
résultat de l'interrogation est le suivant :
A présent, supposons que vous souhaitiez embaucher des employés pour le
nouveau département. L'instruction INSERT à exécuter pour les nouveaux employés
peut inclure le code suivant :
INSERT INTO employees (employee_id, department_id, ...)
VALUES (employees_seq.NEXTVAL, dept_deptid_seq .CURRVAL, ...);
Remarque : Dans l'exemple précédent, il est supposé qu'une séquence nommée
EMPLOYEES_SEQ a été créée pour générer de nouveaux numéros d'employé.
11.17 Mettre en mémoire cache les valeurs
d'une séquence
Mettre en mémoire cache les valeurs d'une séquence
Vous pouvez mettre les valeurs d'une séquence en mémoire cache afin de permettre
un accès plus rapide à ces valeurs. Le cache est alimenté la première fois que vous
référencez la séquence. Lors de chaque interrogation, la valeur suivante de la
séquence est extraite du cache. Lorsque la dernière valeur de la séquence est
utilisée, l'interrogation suivante place une nouvelle série de valeurs en mémoire
cache.
Trous dans une séquence
Les générateurs de séquence produisent des numéros qui se suivent, mais cette
action est effectuée indépendamment des opérations de validation (commit) ou
d'annulation (rollback). Ainsi, si vous annulez une instruction contenant une
séquence, le numéro correspondant est perdu.
La défaillance du système peut également provoquer des trous dans une séquence.
Si des valeurs de la séquence se trouvent en mémoire cache, elles sont perdues.
Etant donné que les séquences ne sont pas directement liées aux tables, la même
séquence peut être utilisée pour plusieurs tables. Toutefois, dans ce cas, chaque
table peut présenter des numéros de séquence discontinus.
11.18 Modifier une séquence
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
90
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Modifier une séquence
Lorsque vous atteignez la limite MAXVALUE de la séquence, aucune nouvelle valeur
n'est allouée et une erreur signale le dépassement. Si vous souhaitez continuer à
utiliser la séquence, vous pouvez la modifier à l'aide de l'instruction ALTER
SEQUENCE.
Syntaxe
ALTER SEQUENCE
sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
Dans la syntaxe, sequence correspond au nom du générateur de numéros de
séquence.
Pour plus d'informations, reportez-vous à la section "ALTER SEQUENCE" du manuel
Oracle Database SQL Language Reference 11g, Release 1 (11.1).
11.19 Suppression d'une séquence
DROP SEQUENCE dept_deptid_seq;
Suppression d'une séquence
•Pour pouvoir modifier une séquence, vous devez être son propriétaire ou être doté
du privilège ALTER pour cette séquence. Pour pouvoir la supprimer, vous devez être
son propriétaire ou disposer du privilège DROP ANY SEQUENCE.
•Seuls les numéros de séquence ultérieurs sont affectés par l'instruction ALTER
SEQUENCE.
•L'option START WITH ne peut pas être modifiée à l'aide de l'instruction ALTER
SEQUENCE. La séquence doit être supprimée et recréée si vous souhaitez la
redémarrer à partir d'un autre numéro.
•Une validation est effectuée. Par exemple, il n'est pas possible d'imposer une
nouvelle valeur MAXVALUE inférieure au numéro de séquence actuel.
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 90
NOCACHE
NOCYCLE;
11.20 Index
Index
91
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
Dans le serveur Oracle, un index est un objet de schéma permettant d'accélérer
l'extraction de lignes à l'aide d'un pointeur. Les index peuvent être créés de manière
explicite ou automatique. Si la colonne ne comporte pas d'index, un balayage
complet de table (full table scan) est effectué.
Un index permet un accès direct et rapide aux lignes d'une table. Il réduit les
entrées-sorties (E/S) disque grâce à un chemin indexé permettant de localiser
rapidement les données. Un index est utilisé et géré automatiquement par le serveur
Oracle. Une fois l'index créé, aucune activité directe n'est requise de la part de
l'utilisateur.
Les index sont logiquement et physiquement indépendants de la table qu'ils
indexent. Cela signifie qu'ils peuvent être créés ou supprimés à tout moment, sans
aucune incidence sur les tables de base ou sur les autres index.
Remarque : Lorsque vous supprimez une table, les index correspondants sont
également supprimés.
11.21 Modes de création d'un index
Modes de création d'un index
Vous pouvez créer deux types d'index.
Index unique : Le serveur Oracle crée automatiquement un index lorsque vous
définissez une contrainte PRIMARY KEY ou UNIQUE sur une colonne d'une table.
Le nom de l'index est le nom attribué à la contrainte.
Index non unique : Il s'agit d'un index qui peut être créé par l'utilisateur. Par
exemple, vous pouvez créer un index sur une colonne FOREIGN KEY pour une
jointure définie dans une interrogation afin d'accélérer l'extraction.
Remarque : Vous pouvez créer un index unique manuellement, mais il est plutôt
recommandé de le créer implicitement par le biais de la définition d'une contrainte
UNIQUE.
11.22 Créer un index
CREATE [UNIQUE][BITMAP]INDEX index
ON table (column[, column]...);
CREATE INDEX
emp_last_name_idx
ON
employees(last_name);
Créer un index
Créez un index sur une ou plusieurs colonnes à l'aide de l'instruction CREATE
INDEX.
Dans la syntaxe :
•index
Est le nom de l'index.
•table
Est le nom de la table.
•column
Est le nom de la colonne à indexer dans la table.
Précisez UNIQUE pour indiquer que la valeur de la colonne (ou des colonnes) sur
laquelle est basé l'index doit être unique. Précisez BITMAP pour indiquer qu'il faut
créer un index avec un bitmap pour chaque clé distincte au lieu d'indexer chaque
ligne séparément. Les index bitmap stockent en tant que bitmap les rowid associés à
une valeur de clé.
92
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
11.23 Règles de création d'un index
11.24 Supprimer un index
DROP INDEX emp_last_name_idx;
Supprimer un index
Vous ne pouvez pas modifier un index directement. Vous devez le supprimer, puis le
recréer.
Pour supprimer la définition d'un index dans le dictionnaire de données, exécutez
l'instruction DROP INDEX. Pour supprimer un index, vous devez en être le
propriétaire ou disposer du privilège DROP ANY INDEX.
Dans la syntaxe, index correspond au nom de l'index.
Remarque : Si vous supprimez une table, les index et les contraintes sont
automatiquement supprimés, mais les vues et les séquences sont conservées.
11.25 Créer un synonyme pour un objet
CREATE [PUBLIC] SYNONYM synonym
FOR
object;
Créer un synonyme pour un objet
Pour faire référence à une table appartenant à un autre utilisateur, vous devez
ajouter au nom de la table un préfixe constitué du nom de l'utilisateur qui l'a créée,
suivi d'un point. La création d'un synonyme évite d'avoir à qualifier le nom de l'objet
avec le schéma, qu'il s'agisse d'une table, d'une vue, d'une séquence, d'une
procédure ou d'autres objets. Cette méthode peut s'avérer particulièrement utile avec
les noms d'objet longs tels que les noms de vue.
Dans la syntaxe :
PUBLIC
Crée un synonyme accessible à tous les utilisateurs.
synonym
Est le nom du synonyme à créer.
object
Identifie l'objet pour lequel le synonyme est créé.
Règles
•L'objet ne peut pas être contenu dans un package.
•Tout synonyme privé doit être différent du nom des autres objets appartenant au
même utilisateur.
93
Daphné Nougier
ESUFA-3A / U2014 - TP
FA-LAB3415 – Travaux Pratiques Bases de Données
Séance 1-2-3 – 17/11/2014
11.26 Créer et supprimer des synonymes
CREATE SYNONYM d_sum
FOR dept_sum_vu;
DROP SYNONYM d_sum;
Créer et supprimer des synonymes
Créer un synonyme
L'exemple de la diapositive ci-dessus crée un synonyme pour la vue
DEPT_SUM_VU, afin qu'elle puisse être référencée plus facilement.
L'administrateur de base de données peut créer un synonyme public accessible à
tous les utilisateurs. L'exemple suivant crée un synonyme public nommé DEPT pour
la table DEPARTMENTS d'Alice :
CREATE PUBLIC SYNONYM dept
FOR alice.departments;
Supprimer un synonyme
Pour supprimer un synonyme, utilisez l'instruction DROP SYNONYM. Seul
l'administrateur de la base de données peut supprimer un synonyme public.
DROP PUBLIC SYNONYM dept;
94
Daphné Nougier