• Insertion des lignes

• Insertion multitable

• Mise à jour

• Suppression

• MERGE

12 La manipulation des
données
À la fin de ce module, vous serez à même d’effectuer les tâches suivantes :
• Effectuer des insertions, mises à jour et suppressions d’enregistrements.
• Effectuer des insertions multitable.
• Insérer plusieurs enregistrements à la fois dans une seule expression.
• Effectuer des mises à jour conditionnées.
• Mettre à jour des tables à partir des sous-requêtes.

Insertion des lignes 12-2 Suppression des données 12-14

Insertion et sous-requête 12-4 Contraintes d’intégrité 12-15

Insertion multitable 12-6 Mises à jour conditionnées 12-16

Modification des données 12-11

© Tsoft/Eyrolles – SQL pour Oracle 12c 9-1

Module 9 : La manipulation des données

Insertion des lignes

Ce chapitre présente le Langage de Manipulation de Données ou LMD qui permet d’effectuer les
modifications des données (mise à jour de lignes, ajout de lignes et suppression de lignes
sélectionnées). Bien que ces sujets n’aient pas été explicitement traités, les connaissances que vous
avez acquises sur SQL (les types de données, les opérations de calcul, le formatage de chaînes, la
clause « WHERE », etc.) peuvent être mises à profit ici.
L’un des problèmes essentiels posés au SGBDR est la manipulation simultanée des données de la
base par un grand nombre d’utilisateurs. Le SGBDR doit à la fois assurer une bonne disponibilité de
l’information et en garantir la cohérence.
La commande « INSERT » ajoute des lignes à une table. Avec cette instruction, vous fournissez
des valeurs et des expressions littérales à enregistrer sous forme de lignes dans une table.
Le terme « INSERT » peut induire en erreur s’il laisse supposer qu’on peut déterminer où, dans une
table, une ligne est insérée. Les bases de données relationnelles comportent une indépendance logique
des données qu’elles manipulent, en d’autres termes, une table ne possède aucun ordre implicite. Une
nouvelle ligne insérée est placée à un endroit arbitraire dans la table.
Deux possibilités sont offertes :
• création d’une nouvelle ligne dans une table à partir de valeurs extérieures transmises
sous forme de constantes ;
• création d’une ou de plusieurs lignes dans une table en tant que résultat d’une requête sur
la base de données.
La commande « INSERT » permet d’insérer une ligne dans une table en spécifiant les valeurs à
insérer par la syntaxe :
INSERT INTO NOM_TABLE[(COLONNE_1[,...])]VALUES(EXPRESSION_1[,...]);
NOM_TABLE La table dans laquelle la requête insère un enregistrement et
seulement un enregistrement.
COLONNE_N La liste des noms de colonnes de la table qui font l’objet d’une
insertion ; elle est optionnelle. Toute colonne qui ne se trouve
pas dans la liste reçoit la valeur « NULL ». En l’absence d’une
liste de colonnes, des valeurs doivent être spécifiées pour toutes
les colonnes de la table dans l’ordre défini lors de la création de
la table.
EXPRESSION_N L’expression doit être évaluée avec succès pour chacune des
colonnes de la table. Les valeurs possibles sont : une constante,
le résultat de l’expression, la valeur nulle « NULL ».
SQL> INSERT INTO CATEGORIES ( CODE_CATEGORIE, NOM_CATEGORIE,
2 DESCRIPTION ) VALUES ( 9, 'Poissons et fruits de mer',
3 'Poissons, fruits de mer, escargots') ;

1 ligne créée.
La requête précédente permet d’insérer une ligne dans la table CATEGORIES en spécifiant les valeurs
à insérer sous forme de constantes.
SQL> INSERT INTO CATEGORIES VALUES ( 9, 'Poissons et fruits de mer',
2 'Poissons, fruits de mer, escargots');

1 ligne créée.

SQL> SELECT count(*) FROM CATEGORIES WHERE CODE_CATEGORIE = 9;

9-2 © Tsoft/Eyrolles – SQL pour Oracle 12c

 Module 9 : La manipulation des données

COUNT(*)
----------
1

SQL> INSERT INTO CATEGORIES VALUES ( 9, 'Poissons et fruits de mer',

2 'Poissons, fruits de mer, escargots');
INSERT INTO CATEGORIES
*
ERREUR à la ligne 1 :
ORA-00001: violation de contrainte unique (STAGIAIRE.PK_CATEGORIES)
Les opérations de mise à jour des données doivent tenir compte des contraintes des tables. Dans
l’exemple suivant, vous pouvez constater la violation de la contrainte « PRIMARY KEY » définie
sur la colonne CODE_CATEGORIE.
Si vous tentez d’insérer une valeur qui dépasse la largeur d’une colonne de type caractère ou
l’étendue d’une colonne de type numérique, vous obtenez un message d’erreur. Vous devez respecter
les contraintes définies pour vos colonnes.
SQL> INSERT INTO EMPLOYES ( NO_EMPLOYE, NOM, PRENOM, FONCTION,
2 TITRE, DATE_NAISSANCE,DATE_EMBAUCHE, SALAIRE)
3 VALUES ( 12, 'Fuller', 'Jean-William', 'Vice-Président',
4 'Dr.', '19/02/1952', '14/08/1992', '10000');
VALUES ( 12, 'Fuller', 'Jean-William', 'Vice-Président',
*
ERREUR à la ligne 3 :
ORA-12899: valeur trop grande pour la colonne
"STAGIAIRE"."EMPLOYES"."PRENOM" (réelle : 12, maximum : 10)
Dans l’exemple précédent, le champ prénom ne peut contenir que dix caractères, mais le prénom
'Jean-William' inséré contient douze caractères.
SQL> DESC EMPLOYES
Nom NULL ? Type
----------------------------------------- -------- --------------
NO_EMPLOYE NOT NULL NUMBER(6)
REND_COMPTE NUMBER(6)
NOM NOT NULL NVARCHAR2(40)
PRENOM NOT NULL NVARCHAR2(10)
FONCTION NOT NULL VARCHAR2(30)
TITRE NOT NULL VARCHAR2(5)
DATE_NAISSANCE NOT NULL DATE
DATE_EMBAUCHE NOT NULL DATE
SALAIRE NOT NULL NUMBER(8,2)
COMMISSION NUMBER(8,2)

SQL> INSERT INTO EMPLOYES VALUES ( 10, 2, 'Davolio', 'Nancy',

2 'Représentant(e)', 'Mlle', '08/12/1968', '01/05/1992',
3 3135, 1500);

1 ligne créée.
La liste des noms de colonnes est optionnelle. Si elle est omise, la requête prendra par défaut la liste
de colonnes de la table dans l’ordre défini lors de la création de la table. Pour connaître l’ordre de
colonnes, vous pouvez utiliser la commande SQL*Plus « DESC NOM_TABLE ».
La requête précédente permet d’insérer une ligne dans la table EMPLOYES en spécifiant les valeurs à
insérer sous forme de constantes.

© Tsoft/Eyrolles – SQL pour Oracle 12c 9-3

Module 9: La manipulation des données

Si la liste des noms de colonnes est spécifiée, les colonnes ne figurant pas dans la liste auront la
valeur « NULL ».
Une correspondance positionnelle s’effectue entre les noms de colonnes de la liste et les valeurs
introduites.
SQL> INSERT INTO EMPLOYES ( NO_EMPLOYE, NOM, PRENOM, FONCTION,
2 TITRE, DATE_NAISSANCE, DATE_EMBAUCHE, SALAIRE )
3 VALUES ( 11, 'Fuller', 'Andrew', 'Vice-Président',
4 'Dr.', '19/02/1952', '14/08/1992', '10000');

1 ligne créée.

SQL> SELECT NO_EMPLOYE, NOM, REND_COMPTE, COMMISSION

2 FROM EMPLOYES WHERE NO_EMPLOYE = 11;

NO_EMPLOYE NOM REND_COMPTE COMMISSION

---------- --------------------------------- ----------- ----------
11 Fuller
La requête précédente effectue l’insertion d’une ligne dans la table EMPLOYES en spécifiant les
valeurs à insérer sous forme de constantes ; les colonnes REND_COMPTE et COMMISSION ne
figurant pas dans la liste des colonnes à insérer, leur valeur est « NULL ».
L’expression « DEFAULT » permet de définir une valeur par défaut pour la colonne, qui sera prise
en compte si aucune valeur n’est spécifiée dans une commande « INSERT ». Elle est spécifiée à la
création de la table et peut être une constante, une pseudo-colonne « USER », « SYSDATE » ou
tout simplement une expression. Il est également possible d’insérer pour une colonne une valeur
« NULL » de manière explicite.
SQL> INSERT INTO EMPLOYES ( NO_EMPLOYE, NOM, PRENOM, FONCTION,
2 TITRE, DATE_NAISSANCE, DATE_EMBAUCHE, SALAIRE, COMMISSION )
4 VALUES ( 200,'BIZOÏ', 'Razvan', 'Formateur',
5 'M.','03/02/1965', DEFAULT, 10000, NULL);

1 ligne créée.

SQL> SELECT NOM, PRENOM, DATE_EMBAUCHE, SYSDATE, COMMISSION

2 FROM EMPLOYES WHERE NO_EMPLOYE = 200;

NOM PRENOM DATE_EMBAU SYSDATE COMMISSION

------------ ------------ ---------- ---------- ----------
BIZOÏ Razvan 12/07/2011 12/07/2011

Insertion et sous-requête

La commande « INSERT » permet d’insérer une ligne dans une sous-requête en spécifiant les
valeurs à insérer par la syntaxe :
INSERT INTO SOUS-REQUETE VALUES (EXPRESSION_1[,...]);
SOUS-REQUETE La sous-requête doit être construite sur une seule table et doit
contenir tous les champs nécessitant une valeur.
EXPRESSION_N L’expression doit être évaluée avec succès pour chacune des
colonnes de la table. Les valeurs possibles sont : une constante,
le résultat de l’expression, la valeur nulle « NULL ».

9-4 © Tsoft/Eyrolles – SQL pour Oracle 12c

 Module 9: La manipulation des données
SQL> INSERT INTO ( SELECT NO_COMMANDE,CODE_CLIENT,NO_EMPLOYE,DATE_COMMANDE
2 FROM COMMANDES WHERE CODE_CLIENT = 'BONAP' AND NO_EMPLOYE = 4)
3 VALUES ( 20000, 'BONAP', 4, SYSDATE);

1 ligne créée.

SQL> INSERT INTO ( SELECT NO_COMMANDE,CODE_CLIENT,NO_EMPLOYE,DATE_COMMANDE

2 FROM COMMANDES WHERE CODE_CLIENT = 'BONAP' AND NO_EMPLOYE = 4)
3 VALUES ( 20001, 'FRANK', 5, SYSDATE+1);

1 ligne créée.

SQL> SELECT NO_COMMANDE, CODE_CLIENT, NO_EMPLOYE, DATE_COMMANDE

2 FROM COMMANDES WHERE NO_COMMANDE IN (20000,20001);

NO_COMMANDE CODE_ NO_EMPLOYE DATE_COMMA

----------- ----- ---------- ----------
20000 BONAP 4 12/07/2011
20001 FRANK 5 13/07/2011
Dans l’exemple précédent, vous pouvez remarquer que la condition décrite dans la clause
« WHERE » n’empêche pas l’insertion d’un enregistrement qui n’est pas vérifié par cette condition.
Il est possible de contrôler la cohérence des données qui sont insérées à l’aide d’une sous-requête. Le
contrôle est effectué par rapport aux conditions qui sont décrites dans la clause « WHERE », mais
uniquement quand la clause « WITH CHECK OPTION » est définie. La syntaxe de la sous-requête
qui utilise la clause « WITH CHECK OPTION » est la suivante :
SELECT liste_colonnes FROM NOM_TABLE
WHERE PREDICAT WITH CHECK OPTION ;
liste_colonnes La liste de toutes les colonnes que vous voulez insérer dans la
table. Il faut inclure obligatoirement toutes les colonnes qui
nécessitent une valeur.
PREDICAT Le prédicat qui contrôle la cohérence des enregistrements de
données qui doivent être mises à jour dans la table.
SQL> INSERT INTO ( SELECT NO_COMMANDE,CODE_CLIENT,NO_EMPLOYE,DATE_COMMANDE
2 FROM COMMANDES WHERE CODE_CLIENT = 'BONAP' AND NO_EMPLOYE = 4
3 WITH CHECK OPTION)
5 VALUES ( 20001, 'FRANK', 5, SYSDATE+1);
DATE_COMMANDE FROM COMMANDES
*
ERREUR à la ligne 2 :
ORA-01402: vue WITH CHECK OPTION - violation de clause WHERE
Dans l’exemple précédent, vous pouvez remarquer que le prédicat de la clause « WHERE » combiné
avec la clause « WITH CHECK OPTION » contrôle l’insertion des données en plus des contraintes
d’intégrité définies au niveau de la table.

Vous pouvez utiliser la clause « WITH CHECK OPTION » pour enrichir ponctuellement les
contrôles effectués par les contraintes des tables.
Cette démarche est surtout utilisée pour les traitements de masse comme l’alimentation d’une table à
l’aide d’une sous-requête.
Attention, si un enregistrement ne vérifie pas le prédicat, alors aucun enregistrement n’est inséré dans
la table.

© Tsoft/Eyrolles – SQL pour Oracle 12c 9-5

Module 9 : La manipulation des données

La commande « INSERT » permet d’insérer des données qui ont été sélectionnées dans une ou
plusieurs tables.
INSERT INTO NOM_TABLE [(COLONNE_1[,...])]SOUS-REQUETE;
NOM_TABLE Table dans laquelle la requête insère les enregistrements.
COLONNE_N Liste des noms de colonnes de la table qui font l’objet de
l’insertion, elle est optionnelle. Toute colonne qui ne se trouve
pas dans la liste reçoit la valeur « NULL ». En l’absence d’une
liste de colonnes, des valeurs doivent être spécifiées pour toutes
les colonnes de la table dans l’ordre défini lors de la création de
la table.
SOUS-REQUETE Requête SQL qui retourne la ou les lignes à insérer.
SQL> INSERT INTO CLIENTS (CODE_CLIENT,SOCIETE,ADRESSE,VILLE,
2 CODE_POSTAL,PAYS,TELEPHONE)
3 SELECT UPPER(SUBSTR(REPLACE(SOCIETE,' ',''),1,5)),
4 SOCIETE,ADRESSE,VILLE,CODE_POSTAL,PAYS, TELEPHONE
5 FROM FOURNISSEURS WHERE PAYS = 'France';

3 ligne(s) créée(s).
Dans l’exemple précédent, les données extraites de la table FOURNISSEURS sont insérées dans la
table CLIENTS. Notez que la clause « WHERE » de l’instruction « SELECT » peut extraire une
ou plusieurs lignes. Vous remarquerez que vous n’êtes pas tenu d’insérer telles quelles les valeurs
sélectionnées ; vous pouvez les modifier en utilisant des fonctions de chaîne, de date, ou numériques.
Les valeurs insérées représentent le résultat de ces fonctions.

Insertion multitable

La commande « INSERT » permet depuis la version Oracle 9i d’insérer des données dans plusieurs
tables.
La syntaxe de la commande « INSERT » est :
INSERT [ ALL | FIRST ]
[ WHEN CONDITION THEN ]
INTO NOM_TABLE_1 [VALUES( EXPRESSION_1[,...] )]
[ ELSE ]
INTO NOM_TABLE_2 [VALUES( EXPRESSION_1[,...] )]
...
SELECT EXPRESSION_1[,...] FROM ...;

NOM_TABLE_N Une des tables dans laquelle la requête insère les

enregistrements.
WHEN CONDITION La condition qui permet de vérifier l’insertion.
ALL Tous les inserts sont effectués.
FIRST Permet l’insertion seulement pour le premier insert qui vérifie la
condition, les autres étant ignorés.

9-6 © Tsoft/Eyrolles – SQL pour Oracle 12c

 Module 9 : La manipulation des données

EXPRESSION_N L’expression retournée par la requête SQL pour être insérée dans
la ou dans les tables cibles.
SOUS-REQUETE Requête SQL qui retourne la ou les lignes à insérer.

La commande « INSERT » permet les insertions dans plusieurs tables en un seul ordre suivant trois
types de syntaxes « INSERT » :
• sans condition ;
• conditionnel multitable ;
• conditionnel monotable.

Insertion sans condition

La commande « INSERT ALL » permet d’insérer des données d’une sous-requête dans une ou
plusieurs tables sans aucune condition. Il est également possible d’insérer plusieurs enregistrements
dans la même table à partir d’un enregistrement retourné par la sous-requête.
La syntaxe pour la commande « INSERT ALL » sans condition est :
INSERT ALL
INTO NOM_TABLE_1 [VALUES( EXPRESSION_1[,...] )]
INTO NOM_TABLE_2 [VALUES( EXPRESSION_1[,...] )]
[...]
SELECT EXPRESSION_1[,...] FROM ...;
SQL> DESC QUANTITES_CLIENTS
Nom NULL ? Type
--------------------------------- -------- -----------
ANNEE NUMBER
MOIS NUMBER
CODE_CLIENT CHAR(5)
QUANTITE NUMBER
PORT NUMBER

SQL> DESC VENTES_CLIENTS

Nom NULL ? Type
--------------------------------- -------- -----------
ANNEE NUMBER
MOIS NUMBER
CODE_CLIENT CHAR(5)
VENTE NUMBER
REMISE NUMBER

SQL> SELECT EXTRACT ( YEAR FROM DATE_COMMANDE) ANNEE,

2 EXTRACT ( MONTH FROM DATE_COMMANDE) MOIS,
3 CODE_CLIENT,
4 SUM(QUANTITE*PRIX_UNITAIRE) VENTE,
5 SUM(QUANTITE*PRIX_UNITAIRE*REMISE) REMISE,
6 SUM(QUANTITE) QUANTITE,
7 SUM(PORT) PORT
8 FROM COMMANDES NATURAL JOIN DETAILS_COMMANDES
9 GROUP BY EXTRACT ( YEAR FROM DATE_COMMANDE),
10 EXTRACT ( MONTH FROM DATE_COMMANDE),
11 CODE_CLIENT

© Tsoft/Eyrolles – SQL pour Oracle 12c 9-7

Module 9 : La manipulation des données
12 ORDER BY EXTRACT ( YEAR FROM DATE_COMMANDE),
13 EXTRACT ( MONTH FROM DATE_COMMANDE),
14 CODE_CLIENT;

ANNEE MOIS CODE_ VENTE REMISE QUANTITE PORT

---------- ----- ----- ------- ---------- ---------- -------
1996 7 BLONP 5880 0 50 5,53
1996 7 CENTC 504 0 11 33
1996 7 CHOPS 3126 342,9 57 3,45
1996 7 ERNSH 22417 4973,6 305 50,29
1996 7 FOLKO 3623 144,375 60 37
1996 7 FRANK 20155 2472 135 31,29
1996 7 GROSR 5506 0 14 6,63
1996 7 HANAR 16289 1302 162 18,60
...

SQL> INSERT ALL

2 INTO QUANTITES_CLIENTS
3 VALUES ( ANNEE, MOIS , CODE_CLIENT, QUANTITE, PORT)
4 INTO VENTES_CLIENTS
5 VALUES ( ANNEE, MOIS , CODE_CLIENT, VENTE, REMISE)
6 SELECT EXTRACT ( YEAR FROM DATE_COMMANDE) ANNEE,
7 EXTRACT ( MONTH FROM DATE_COMMANDE) MOIS,
8 CODE_CLIENT,
9 SUM(QUANTITE*PRIX_UNITAIRE) VENTE,
10 SUM(QUANTITE*PRIX_UNITAIRE*REMISE) REMISE,
11 SUM(QUANTITE) QUANTITE,
12 SUM(PORT) PORT
13 FROM COMMANDES NATURAL JOIN DETAILS_COMMANDES
14 GROUP BY EXTRACT ( YEAR FROM DATE_COMMANDE),
15 EXTRACT ( MONTH FROM DATE_COMMANDE),
16 CODE_CLIENT
17 ORDER BY EXTRACT ( YEAR FROM DATE_COMMANDE),
18 EXTRACT ( MONTH FROM DATE_COMMANDE),
19 CODE_CLIENT;

1274 ligne(s) créée(s).

Chaque enregistrement de la sous-requête est inséré dans les deux tables QUANTITES_CLIENTS et
VENTES_CLIENTS. Les cumuls des frais de port et des quantités vendues par client sont insérés
dans la table QUANTITES_CLIENTS et les cumuls des ventes ainsi que la remise par client sont
insérés dans la table VENTES_CLIENTS.

INSERT ALL
La commande « INSERT ALL » permet d’insérer des données d’une sous-requête dans une ou
plusieurs tables si les conditions correspondantes sont vérifiées.
La syntaxe pour la commande « INSERT ALL » sans condition est :
INSERT ALL
WHEN CONDITION THEN
INTO NOM_TABLE_1 [VALUES( EXPRESSION_1[,...] )]
WHEN CONDITION THEN
INTO NOM_TABLE_2 [VALUES( EXPRESSION_1[,...] )]

9-8 © Tsoft/Eyrolles – SQL pour Oracle 12c

 Module 9 : La manipulation des données

[...]
ELSE
INTO NOM_TABLE_N [VALUES( EXPRESSION_1[,...] )]
SELECT EXPRESSION_1[,...] FROM ...;
SQL> SELECT GROUPING_ID( EXTRACT ( YEAR FROM DATE_COMMANDE),
2 EXTRACT ( MONTH FROM DATE_COMMANDE),
3 CODE_CLIENT ) G,
4 EXTRACT ( YEAR FROM DATE_COMMANDE) ANNEE,
5 EXTRACT ( MONTH FROM DATE_COMMANDE) MOIS,
6 CODE_CLIENT,
7 SUM(QUANTITE*PRIX_UNITAIRE) VENTE,
8 SUM(QUANTITE*PRIX_UNITAIRE*REMISE) REMISE
9 FROM COMMANDES NATURAL JOIN DETAILS_COMMANDES
10 GROUP BY ROLLUP
11 (EXTRACT ( YEAR FROM DATE_COMMANDE),
12 EXTRACT ( MONTH FROM DATE_COMMANDE),
13 CODE_CLIENT);

G ANNEE MOIS CODE_ VENTE REMISE

-- ---------- ----- ----- ----------- ----------
0 1996 7 BLONP 5880,00 ,00
0 1996 7 CENTC 504,00 ,00
...
1 1996 7 150960,50 11651,03
...
0 1998 5 WHITC 4643,75 ,00
1 1998 5 99493,30 7825,15
3 1998 2348856,70 145737,37
7 6772292,95 443327,75

664 ligne(s) sélectionnée(s).

SQL> INSERT ALL

2 WHEN G = 3 THEN
3 INTO VENTES_ANNEES
4 VALUES ( ANNEE, VENTE, REMISE)
5 WHEN G = 1 THEN
6 INTO VENTES_MOIS
7 VALUES ( ANNEE, MOIS, VENTE, REMISE)
8 WHEN G = 0 AND ANNEE = 1996 THEN
9 INTO VENTES_CLIENTS_1996
10 VALUES ( MOIS, CODE_CLIENT, VENTE, REMISE)
11 WHEN G = 0 AND ANNEE = 1997 THEN
12 INTO VENTES_CLIENTS_1997
13 VALUES ( MOIS, CODE_CLIENT, VENTE, REMISE)
14 WHEN G = 0 AND ANNEE = 1998 THEN
15 INTO VENTES_CLIENTS_1998
16 VALUES ( MOIS, CODE_CLIENT, VENTE, REMISE)
17 SELECT GROUPING_ID( EXTRACT ( YEAR FROM DATE_COMMANDE),
18 EXTRACT ( MONTH FROM DATE_COMMANDE),
19 CODE_CLIENT ) G,
20 EXTRACT ( YEAR FROM DATE_COMMANDE) ANNEE,
21 EXTRACT ( MONTH FROM DATE_COMMANDE) MOIS,
22 CODE_CLIENT,

© Tsoft/Eyrolles – SQL pour Oracle 12c 9-9

Module 9 : La manipulation des données
23 SUM(QUANTITE*PRIX_UNITAIRE) VENTE,
24 SUM(QUANTITE*PRIX_UNITAIRE*REMISE) REMISE
25 FROM COMMANDES NATURAL JOIN DETAILS_COMMANDES
26 GROUP BY ROLLUP
27 (EXTRACT ( YEAR FROM DATE_COMMANDE),
28 EXTRACT ( MONTH FROM DATE_COMMANDE),
29 CODE_CLIENT);

689 ligne(s) créée(s).

SQL> SELECT * FROM VENTES_ANNEES;

ANNEE VENTE REMISE

---------- ----------- ----------
1996 1131492,50 91072,65
1997 3291943,75 206517,73
1998 2348856,70 145737,37

SQL> SELECT * FROM VENTES_MOIS;

ANNEE MOIS VENTE REMISE

---------- ----- ----------- ----------
1996 7 150960,50 11651,03
...
1998 4 673152,80 54159,39
1998 5 99493,30 7825,15

SQL> SELECT * FROM VENTES_CLIENTS_1998;

MOIS CODE_ VENTE REMISE

----- ----- ----------- ----------
1 ALFKI 4255,00 26,00
...
1 504273,60 33163,05
...
5 WHITC 4643,75 ,00
5 99493,30 7825,15
2348856,70 145737,37
Chaque enregistrement de la sous-requête est inséré dans toutes les tables si la condition est vérifiée.
La première condition vérifie que l’enregistrement est un cumul des ventes et remises pour une année
comme vous pouvez le voir dans la liste des enregistrements insérés de la table VENTES_ANNEES.
La deuxième condition vérifie que l’enregistrement est un cumul des ventes et remises pour un mois
dans l’année comme vous pouvez le constater dans la liste des enregistrements insérés de la table
VENTES_MOIS.
Les trois autres conditions vérifient que l’enregistrement est de l’année correspondant à la table. Il
faut remarquer que les cumuls annuels et mensuels sont également insérés dans ces tables comme
vous pouvez l’observer dans la liste des enregistrements insérés de la table
VENTES_CLIENTS_1998.

9-10 © Tsoft/Eyrolles – SQL pour Oracle 12c

 Module 9 : La manipulation des données

Un enregistrement peut être inséré dans plusieurs tables à la fois si les conditions imposées sont
vérifiées.
Il convient de faire attention dans le cas des fonctions analytiques, plusieurs champs peuvent être
« NULL » suivant le regroupement, ce qui peut induire des erreurs d’insertion.

INSERT FIRST
La commande « INSERT FIRST » permet d’insérer des données dans une table correspondant à
la première condition valide dans la liste.
SQL> INSERT FIRST
2 WHEN G = 3 THEN
3 INTO VENTES_ANNEES
4 VALUES ( ANNEE, VENTE, REMISE)
5 WHEN G = 1 THEN
6 INTO VENTES_MOIS
7 VALUES ( ANNEE, MOIS, VENTE, REMISE)
8 WHEN ANNEE = 1996 THEN
9 INTO VENTES_CLIENTS_1996
10 VALUES ( MOIS, CODE_CLIENT, VENTE, REMISE)
...
28 EXTRACT ( MONTH FROM DATE_COMMANDE),
29 CODE_CLIENT);

663 ligne(s) créée(s).

Dans l’exemple précédent, vous pouvez remarquer que la commande « INSERT FIRST » avec la
même syntaxe n’insère que 663 enregistrements à la place des 689 pour la commande « INSERT
ALL ».
Les enregistrements sous-totaux, regroupés par année et par mois dans l’année, sont insérés
uniquement dans les tables des cumuls respectifs.

Modification des données

La commande « UPDATE » modifie les valeurs d’une ou de plusieurs colonnes, dans une ou
plusieurs lignes existantes d’une table.
UPDATE NOM_TABLE
SET COLONNE_1 = EXPRESSION_1 [,...][WHERE PREDICAT];
NOM_TABLE Table dans laquelle la requête modifie un ou plusieurs
enregistrements suivant la clause WHERE.
SET Désigne les colonnes à modifier pour chaque enregistrement
sélectionné et indique le mode d’obtention de la nouvelle valeur.
COLONNE_N Colonnes mises à jour dans tous les enregistrements qui satisfont
le prédicat. L’expression peut faire référence aux anciennes
valeurs des colonnes de la ligne.
WHERE Clause agissant de façon analogue à la clause WHERE de l’ordre
« SELECT » et qui permet d’indiquer les lignes concernées
par la mise à jour.

© Tsoft/Eyrolles – SQL pour Oracle 12c 9-11

Module 9 : La manipulation des données
SQL> UPDATE EMPLOYES SET SALAIRE=SALAIRE*1.1,COMMISSION=COMMISSION*1.2 ;

9 ligne(s) mise(s) à jour.

Dans l’exemple précédent, les salaires sont augmentés de 10 % et les commissions de 20 % pour
l’ensemble des enregistrements de la table EMPLOYES.
Comme vous pouvez le constater, l’expression peut faire référence aux anciennes valeurs des
colonnes de la ligne.
Dans une commande « UPDATE », en l’absence de clause « WHERE », tous les enregistrements
de la table sont mis à jour.
SQL> SELECT CODE_CLIENT, SOCIETE, ADRESSE FROM CLIENTS
2 WHERE CODE_CLIENT = 'BLONP';

CODE_ SOCIETE ADRESSE

----- ----------------------------------- ------------------
BLONP Blondel père et fils 24, place Kléber

SQL> UPDATE CLIENTS SET ADRESSE = '104, rue Mélanie'

2 WHERE CODE_CLIENT = 'BLONP';

1 ligne mise à jour.

SQL> SELECT CODE_CLIENT, SOCIETE, ADRESSE FROM CLIENTS

2 WHERE CODE_CLIENT = 'BLONP';

CODE_ SOCIETE ADRESSE

----- ----------------------------------- ----------------
BLONP Blondel père et fils 104, rue Mélanie
Dans l’exemple précédent, la modification porte seulement sur le client 'BLONP' qui est le seul
enregistrement de la table CLIENTS qui respecte la clause « WHERE ».
SQL> SELECT NOM, SALAIRE FROM EMPLOYES WHERE NOM LIKE 'Peacock';

NOM SALAIRE
---------------------------------------- ----------
Peacock 2856

SQL> UPDATE EMPLOYES SET SALAIRE = ( SELECT AVG(SALAIRE) FROM EMPLOYES

2 WHERE FONCTION LIKE 'Rep%' ) WHERE NOM LIKE 'Peacock';

1 ligne mise à jour.

SQL> SELECT NOM, SALAIRE FROM EMPLOYES WHERE NOM LIKE 'Peacock' ;

NOM SALAIRE
---------------------------------------- ----------
Peacock 2760,17
Dans l’exemple précédent, le salaire de l’employé(e) 'Peacock' est le résultat d’un ordre
« SELECT » qui ramène la valeur du salaire moyen pour les employé(e)s qui ont une FONCTION
de 'Représentant(e)'.

9-12 © Tsoft/Eyrolles – SQL pour Oracle 12c

 Module 9 : La manipulation des données

L’ordre « SELECT » doit ramener une seule ligne. Il peut être également synchronisé avec la
requête principale « UPDATE ».
L’ordre « SELECT » de la clause « SET » peut aussi ramener plusieurs valeurs en utilisant la
syntaxe : SET (COLONNE_1, ...) = (SELECT ATTRIBUT_1, ...)

Dans l’exemple suivant, le salaire et la commission de chaque employé sont mis à jour avec la
moyenne des salaires et la commission maximum des employés qui occupent la même FONCTION.
SQL> UPDATE EMPLOYES A SET (SALAIRE, COMMISSION) =
2 ( SELECT AVG(SALAIRE), MAX(COMMISSION)
3 FROM EMPLOYES B WHERE B.FONCTION = A.FONCTION);

111 ligne(s) mise(s) à jour.

Lorsque vous employez les commandes « INSERT », « UPDATE » et « DELETE », il est
essentiel de construire la clause « WHERE » de façon qu’elle affecte (ou insère) uniquement les
lignes souhaitées.
Vous pouvez utiliser une sous-requête pour contrôler la mise à jour à l’aide du prédicat de la clause
« WHERE » combiné avec la clause « WITH CHECK OPTION ». Rappelez-vous que si un
enregistrement ne vérifie pas le prédicat, alors aucun enregistrement n’est mis à jour dans la table.
Dans l’exemple suivant, on veut modifier tous les salaires des employés qui ont un salaire compris
entre 10000 et 100000. Le nouveau salaire doit être la moyenne de salaires des employés qui ont la
même fonction, mais il ne doit pas sortir de la plage de 10000 à 100000.
SQL> SELECT NOM, FONCTION, SALAIRE, COMMISSION
2 FROM EMPLOYES WHERE SALAIRE BETWEEN 10000 AND 100000
3 UNION ALL
4 SELECT NULL, FONCTION, AVG(SALAIRE), MAX(COMMISSION)
5 FROM EMPLOYES GROUP BY FONCTION;

NOM FONCTION SALAIRE COMMISSION

------------ ------------------------------ ---------- ----------
Ragon Chef des ventes 13 000,0 5 980,0
Leger Chef des ventes 19 000,0 11 150,0
Cheutin Représentant(e) 10 000,0 570,0
Belin Chef des ventes 10 000,0 10 640,0
Fuller Vice-Président 96 000,0
Splingart Chef des ventes 16 000,0 16 480,0
Buchanan Chef des ventes 13 000,0 12 940,0
Chambaud Chef des ventes 12 000,0 11 600,0
Assistante commerciale 1 654,0
Chef des ventes 13 833,3 16 480,0
Président 150 000,0
Représentant(e) 7 531,5 1 980,0
Vice-Président 121 500,0

SQL> UPDATE (SELECT FONCTION, SALAIRE, COMMISSION FROM EMPLOYES

2 WHERE SALAIRE BETWEEN 10000 AND 100000 WITH CHECK OPTION) A
3 SET (SALAIRE, COMMISSION) =( SELECT AVG(SALAIRE), MAX(COMMISSION)
4 FROM EMPLOYES B WHERE B.FONCTION = A.FONCTION);
FROM EMPLOYES WHERE SALAIRE BETWEEN 10000 AND 100000
*
ERREUR à la ligne 2 :
ORA-01402: vue WITH CHECK OPTION - violation de clause WHERE

© Tsoft/Eyrolles – SQL pour Oracle 12c 9-13

Module 9: La manipulation des données

Suppression des données

L’instruction « DELETE » supprime une ou plusieurs lignes d’une table.

DELETE NOM_TABLE [WHERE PREDICAT];
NOM_TABLE Table dans laquelle la requête supprime un ou plusieurs
enregistrements suivant la clause WHERE.
WHERE Clause agissant de façon analogue à la clause WHERE de l’ordre
« SELECT » et qui permet d’indiquer les lignes concernées
par la suppression.
SQL> DELETE DETAILS_COMMANDES WHERE NO_COMMANDE = 217575;

33 ligne(s) supprimée(s).
Dans l’exemple précédent, les détails de la commande 217575 sont effacés.
Dans une commande « DELETE », en l’absence de clause « WHERE », l’ensemble des
enregistrements de la table sont supprimés.
SQL> SET TIMING ON
SQL> DELETE DETAILS_COMMANDES ;

476091 ligne(s) supprimée(s).

Ecoulé : 00 :00 :26.00
Dans l’exemple précédent, tous les enregistrements de la table DETAILS_COMMANDES sont effacés.
Dans Oracle, une deuxième commande « TRUNCATE » permet de supprimer tous les
enregistrements d’une table et de récupérer l’espace qu’ils occupaient sans éliminer la définition de la
table dans la base, à l’aide de la syntaxe suivante :
TRUNCATE TABLE [SCHEMA.]NOM_TABLE [{DROP|REUSE}STORAGE ];

La commande « TRUNCATE » est un ordre « LDD », langage de définition de données ; donc pas
de transaction et de « ROLLBACK ». Ainsi, cette opération est irréversible. (Voir la gestion des
transactions)
Lorsqu’il existe des déclencheurs pour supprimer les lignes qui dépendent de celles éliminées de la
table, ils ne sont pas exécutés.
Cette commande SQL supprime tous les enregistrements uniquement pour les tables qui ne sont pas
référencées par des contraintes d’intégrité référentielles. Sinon, elle n’aboutit pas, même si ces tables
sont vides.

SQL> SELECT COUNT(*) FROM DETAILS_COMMANDES;

COUNT(*)
----------
476091

SQL> SET TIMING ON

SQL> TRUNCATE TABLE DETAILS_COMMANDES;

Table tronquée.

Ecoulé : 00 :00 :00.79

SQL> SET TIMING OFF
SQL> ROLLBACK;

9-14 © Tsoft/Eyrolles – SQL pour Oracle 12c

 Module 9 : La manipulation des données
Annulation (rollback) effectuée.

SQL> SELECT COUNT(*) FROM DETAILS_COMMANDES;

COUNT(*)
----------
0

SQL> TRUNCATE TABLE COMMANDES;

TRUNCATE TABLE COMMANDES
*
ERREUR à la ligne 1 :
ORA-02266: les clés primaires/uniques de la table sont référencées par des
clés étrangères
Dans l’exemple précédent, la table DETAILS_COMMANDES a 476091 enregistrements. Une fois
que la commande « TRUNCATE » est exécutée, même si vous demandez une annulation de la
transaction, les enregistrements sont supprimés. Vous pouvez comparer également le temps
d’effacement des enregistrements avec la commande « DELETE » et le temps d’effacement avec la
commande « TRUNCATE ». L’exécution de la commande « TRUNCATE » pour la table
COMMANDES ne peut pas aboutir, car elle est référencée par la table DETAILS_COMMANDES.

La commande « TRUNCATE » est beaucoup plus rapide que la commande SQL de type « LMD »
(langage de manipulation de données), « DELETE ».
Par contre, elle ne peut pas être annulée. De plus, comme c’est une commande SQL de type
« LDD », elle valide toute transaction en cours pour la session qui l’exécute.
En somme, la commande « TRUNCATE » est une commande d’administration très utile, mais
dangereuse.

Contraintes d’intégrité

Une requête de modification du contenu de la base de données « INSERT », « UPDATE » ou

« DELETE », ne sera exécutée que si le résultat respecte toutes les contraintes d’intégrité définies
sur cette base.
SQL> UPDATE EMPLOYES SET DATE_NAISSANCE = NULL WHERE NO_EMPLOYE = 2;
SET DATE_NAISSANCE = NULL
*
ERREUR à la ligne 2 :
ORA-01407: impossible de mettre à jour
("STAGIAIRE"."EMPLOYES"."DATE_NAISSANCE") avec NULL
Dans l’exemple précédent, la contrainte d’intégrité « NOT NULL » interdit la mise à jour de la
colonne DATE_NAISSANCE.
SQL> DELETE EMPLOYES WHERE NO_EMPLOYE = 2;
DELETE EMPLOYES
*
ERREUR à la ligne 1 :
ORA-02292: violation de contrainte (STAGIAIRE.FK_EMPLOYES_EMPLOYES)
d'intégrité - enregistrement fils existant
Dans l’exemple précédent, la contrainte d’intégrité référentielle interdit la suppression de
l’enregistrement.

© Tsoft/Eyrolles – SQL pour Oracle 12c 9-15

Module 9 : La manipulation des données

Si un des enregistrements ne respecte pas une des contraintes, alors aucun enregistrement n’est mis à
jour dans la table.
Il faut noter que vous devez automatiquement respecter les conditions utilisées dans la clause
« WHERE » en cas d’utilisation d’une sous-requête avec la clause « WITH CHECK OPTION ».
Ceci peut paraître contraignant, mais permet d’avoir un contrôle très fin des mises à jour dans les
tables de votre base de données.

Mises à jour conditionnées

La commande « MERGE » extrait des enregistrements d’une table source ou d’une sous-requête
afin de mettre à jour « UPDATE », d’effacer « DELETE » ou d’insérer « INSERT » des
données dans une table cible.
Cette instruction permet de fusionner, en une seule requête, plusieurs opérations de mise à jour afin
d’éviter d’écrire des insertions ou des mises à jour multiples en plusieurs commandes.
La syntaxe de la commande « MERGE » est la suivante :
MERGE INTO NOM_TABLE_CIBLE
USING { NOM_TABLE_SOURCE | ( SOUS-REQUETE ) } [ALIAS ]
ON ( CONDITION )
WHEN MATCHED THEN
UPDATE SET COLONNE = { EXPRESSION | DEFAULT } [,...]
[ WHERE PREDICAT ]
[ DELETE WHERE PREDICAT ]
WHEN NOT MATCHED THEN
INSERT [ ( COLONNE [,...]) ]
VALUES ({ EXPRESSION | DEFAULT }[,...]);

NOM_TABLE_CIBLE La table de destination dans laquelle la requête effectue le

traitement d’un ou plusieurs enregistrements.
NOM_TABLE_SOURCE La table source à partir de laquelle la commande retrouve les
enregistrements.
SOUS-REQUETE La sous-requête source à partir de laquelle la commande retrouve
les enregistrements.
CONDITION La condition de jointure entre la table ou la sous-requête source
et la table de destination.
WHEN MATCHED La condition de jointure est valide, alors le traitement est une
opération de type mise à jour.
WHEN NOT MATCHED La condition de jointure n’est pas valide, alors le traitement est
une opération d’insertion.
DELETE Dans le cas d’une opération de mise à jour, on peut, suivant une
condition, effacer un certain nombre d’enregistrements.

9-16 © Tsoft/Eyrolles – SQL pour Oracle 12c

 Module 9: La manipulation des données

SET Désigne les colonnes à modifier pour chaque enregistrement

sélectionné et indique le mode d’obtention de la nouvelle valeur.
Dans l’exemple suivant, on recherche toutes les commandes du 30/06/2011. Pour toutes ces
commandes, si elles comportent la référence du produit 16 on ajoute dix produits de plus autrement
on insère un enregistrement. Il y a seulement huit enregistrements initialement et la commande
« MERGE » retourne la modification des vingt-cinq enregistrements, ainsi, dix-sept enregistrements
ont été insérés et huit modifiés.
SQL> SELECT NO_COMMANDE, REF_PRODUIT, QUANTITE
2 FROM COMMANDES NATURAL JOIN DETAILS_COMMANDES
3 WHERE DATE_COMMANDE = '30/06/2011' AND REF_PRODUIT = 16
4 ORDER BY NO_COMMANDE;

NO_COMMANDE REF_PRODUIT QUANTITE

----------- ----------- ----------
228415 16 60
228418 16 166
...
8 ligne(s) sélectionnée(s).

SQL> MERGE INTO DETAILS_COMMANDES CIBLE

2 USING ( SELECT NO_COMMANDE, PRIX_UNITAIRE
3 FROM COMMANDES, PRODUITS
4 WHERE DATE_COMMANDE = '30/06/2011' AND
5 REF_PRODUIT = 16 ) SOURCE
6 ON ( CIBLE.NO_COMMANDE = SOURCE.NO_COMMANDE AND
7 CIBLE.REF_PRODUIT = 16 )
8 WHEN MATCHED THEN UPDATE SET QUANTITE = QUANTITE + 10
9 WHEN NOT MATCHED THEN
10 INSERT (NO_COMMANDE,REF_PRODUIT,PRIX_UNITAIRE,QUANTITE,REMISE)
11 VALUES (SOURCE.NO_COMMANDE,16,SOURCE.PRIX_UNITAIRE,10,0);

25 lignes fusionnées.
Dans l’exemple suivant, vous pouvez observer la commande « MERGE » utilisée pour
l’augmentation de la remise de 10 % pour tous les détails des commandes passées entre le
29/06/2011 et le 30/06/2011, les détails des commandes du 28/06/2011 sont effacés pour
ces commandes, mais ils sont copiés pour les commandes du 01/07/2011.
SQL> INSERT INTO COMMANDES (NO_COMMANDE,CODE_CLIENT,NO_EMPLOYE,
2 DATE_COMMANDE,DATE_ENVOI,PORT)
3 SELECT 228438 + ROWNUM, CODE_CLIENT,NO_EMPLOYE,
4 DATE_COMMANDE+3,DATE_ENVOI,PORT
5 FROM (SELECT * FROM COMMANDES
6 WHERE DATE_COMMANDE = '28/06/2011' ORDER BY NO_COMMANDE);

33 ligne(s) créée(s).

SQL> WITH SOURCE AS (

2 SELECT DC.NO_COMMANDE,REF_PRODUIT,PRIX_UNITAIRE,QUANTITE,
3 REMISE,RETOURNE,ECHANGE,CO.DATE_COMMANDE
4 FROM COMMANDES CO JOIN DETAILS_COMMANDES DC ON (
5 DC.NO_COMMANDE = CO.NO_COMMANDE
6 AND CO.DATE_COMMANDE BETWEEN '28/06/2011'
7 AND '30/06/2011' )
8 UNION ALL
9 SELECT CO.NO_COMMANDE,REF_PRODUIT,PRIX_UNITAIRE,QUANTITE,
10 REMISE,RETOURNE,ECHANGE,CO.DATE_COMMANDE

© Tsoft/Eyrolles – SQL pour Oracle 12c 9-17

Module 9 : La manipulation des données
11 FROM COMMANDES CO JOIN DETAILS_COMMANDES DC ON (
12 DC.NO_COMMANDE = CO.NO_COMMANDE - 87
13 AND CO.DATE_COMMANDE = '01/07/2011' ))
14 SELECT DATE_COMMANDE, COUNT(*) FROM SOURCE
15 GROUP BY ROLLUP(DATE_COMMANDE);

DATE_COMMA COUNT(*)
---------- ----------
28/06/2011 1200
29/06/2011 973
30/06/2011 944
01/07/2011 1200 <= les enregistrements du '28/06/2011'
4317

SQL> MERGE INTO DETAILS_COMMANDES CIBLE

2 USING ( SELECT DC.NO_COMMANDE,REF_PRODUIT,PRIX_UNITAIRE,
3 QUANTITE,REMISE,RETOURNE,ECHANGE,CO.DATE_COMMANDE
4 FROM COMMANDES CO JOIN DETAILS_COMMANDES DC ON (
5 DC.NO_COMMANDE = CO.NO_COMMANDE
6 AND CO.DATE_COMMANDE BETWEEN '28/06/2011'
7 AND '30/06/2011' )
8 UNION ALL
9 SELECT CO.NO_COMMANDE,REF_PRODUIT,PRIX_UNITAIRE,QUANTITE,
10 REMISE,RETOURNE,ECHANGE,CO.DATE_COMMANDE
11 FROM COMMANDES CO JOIN DETAILS_COMMANDES DC ON (
12 DC.NO_COMMANDE = CO.NO_COMMANDE - 87
13 AND CO.DATE_COMMANDE = '01/07/2011' )) SOURCE
14 ON ( CIBLE.NO_COMMANDE = SOURCE.NO_COMMANDE AND
15 CIBLE.REF_PRODUIT = SOURCE.REF_PRODUIT )
16 WHEN MATCHED THEN
17 UPDATE SET REMISE = REMISE * 1.1
18 DELETE WHERE ( SOURCE.DATE_COMMANDE = '28/06/2011')
19 WHEN NOT MATCHED THEN
20 INSERT ( NO_COMMANDE,REF_PRODUIT,PRIX_UNITAIRE,QUANTITE,
21 REMISE,RETOURNE,ECHANGE)
22 VALUES ( SOURCE.NO_COMMANDE, SOURCE.REF_PRODUIT,
23 SOURCE.PRIX_UNITAIRE,SOURCE.QUANTITE,
24 SOURCE.REMISE,SOURCE.RETOURNE,SOURCE.ECHANGE);

4317 lignes fusionnées.

SQL> WITH SOURCE AS (

2 SELECT DC.NO_COMMANDE,REF_PRODUIT,PRIX_UNITAIRE,QUANTITE,
3 REMISE,RETOURNE,ECHANGE,CO.DATE_COMMANDE
4 FROM COMMANDES CO JOIN DETAILS_COMMANDES DC ON (
5 DC.NO_COMMANDE = CO.NO_COMMANDE
6 AND CO.DATE_COMMANDE BETWEEN '28/06/2011'
7 AND '01/07/2011' ))
8 SELECT DATE_COMMANDE, COUNT(*) FROM SOURCE
9 GROUP BY ROLLUP(DATE_COMMANDE);

DATE_COMMA COUNT(*)
---------- ----------
29/06/2011 973
30/06/2011 944
01/07/2011 1200
3117

9-18 © Tsoft/Eyrolles – SQL pour Oracle 12c