3-Manipuler Des Données

8
Manipuler des données
Copyright © Oracle Corporation, 2001. Tous droits réservés.

Objectifs
A la fin de ce chapitre, vous pourrez :

• décrire chaque instruction LMD
• insérer des lignes dans une table
• modifier des lignes dans une table
• supprimer des lignes d'une table
• fusionner des lignes dans une table
• contrôler les transactions
8-2 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Langage de manipulation de données
• Une instruction LMD est exécutée lorsque vous :

– ajoutez des lignes à une table,
– modifiez des lignes existantes dans une table,
– supprimez des lignes d'une table.
• Une transaction est un ensemble d'instructions
LMD formant une unité de travail logique.

Ajouter une nouvelle ligne dans une table
Nouvelle
DEPARTMENTS ligne
… insérer une
nouvelle ligne
dans la table
DEPARMENTS …

Syntaxe de l'instruction INSERT
• L'instruction INSERT permet d'ajouter de nouvelles

lignes dans une table.
INSERT
INSERT INTO
INTO table
table [(column
[(column [,
[, column...])]
column...])]
VALUES
VALUES (value
(value [,
[, value...]);
value...]);
• Cette syntaxe n'insère qu'une seule ligne à la fois.

Insérer de nouvelles lignes
• Insérez une nouvelle ligne en précisant une valeur

pour chaque colonne.
• Indiquez les valeurs dans l'ordre par défaut des
colonnes dans la table.
• Indiquez éventuellement les colonnes dans la
clause INSERT.
INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
1 row created.
• Placez les valeurs de type caractère et date entre

apostrophes.

Insérer des lignes contenant
des valeurs NULL
• Méthode implicite : n'indiquez pas la colonne dans

la liste.
INSERT INTO departments (department_id,

department_name )
VALUES (30, 'Purchasing');
1 row created.
• Méthode explicite : indiquez le mot-clé NULL dans

la clause VALUES.
INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);
1 row created.

Insérer des valeurs spéciales
La fonction SYSDATE enregistre la date et l'heure en

cours.
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, 100);
1 row created.

Insérer des dates
dans un format spécifique
• Ajoutez un nouvel employé.

INSERT INTO employees
VALUES (114,
'Den', 'Raphealy',
'DRAPHEAL', '515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'AC_ACCOUNT', 11000, NULL, 100, 30);
1 row created.
• Vérifiez l'ajout.

Créer un script
• Utilisez le caractère de substitution & dans une

instruction SQL de saisie de valeurs.
• & est une marque de réservation pour les
variables.
INSERT INTO departments
(department_id, department_name, location_id)
VALUES (&department_id, '&department_name',&location);
1 row created.

Copier des lignes d'une autre table
• Ecrivez votre instruction INSERT en précisant une

sous-interrogation.
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
4 rows created.
• N'utilisez pas la clause VALUES.

• Le nombre de colonnes de la clause INSERT doit
correspondre à celui de la sous-interrogation.

Modifier les données d'une table
EMPLOYEES
Mettez à jour les lignes de la table EMPLOYEES.

Syntaxe de l'instruction UPDATE
• Utilisez l'instruction UPDATE pour modifier des

lignes existantes.
UPDATE
UPDATE table
table
SET
SET column
column == value
value [,
[, column
column == value,
value, ...]
...]
[WHERE
[WHERE condition];
condition];
• Si nécessaire, vous pouvez modifier plusieurs

lignes à la fois.

Modifier des lignes d'une table
• La clause WHERE permet de modifier une ou

plusieurs lignes spécifiques.
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
1 row updated.
• En cas d'absence de la clause WHERE, toutes les

lignes sont modifiées.
UPDATE
UPDATE copy_emp
copy_emp
SET
SET department_id
department_id == 110;
110;
22
22 rows
rows updated.
updated.

Modifier deux colonnes à l'aide d'une
sous-interrogation
Modifiez le poste et le salaire de l'employé 114 pour

qu'ils correspondent à ceux de l'employé 205.
UPDATE employees
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id = 205),
salary = (SELECT salary
FROM employees
WHERE employee_id = 205)
WHERE employee_id = 114;
1 row updated.

Modifier des lignes en fonction d'une
autre table
Utilisez des sous-interrogations dans l'instruction

UPDATE pour modifier des lignes d'une table à l'aide
de valeurs d'une autre table.
UPDATE copy_emp
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 200);
1 row updated.

Erreur de contrainte d'intégrité lors de la
modification de lignes
UPDATE
UPDATE employees
employees
SET
SET department_id
department_id == 55
55
WHERE
WHERE department_id
110;
UPDATE
UPDATE employees
employees
**
ERROR
ERROR at
at line
line 1:
1:
ORA-02291:
ORA-02291: integrity
integrity constraint
constraint (HR.EMP_DEPT_FK)
(HR.EMP_DEPT_FK)
violated
violated -- parent
parent key
key not
not found
found
Le numéro de service 55 n'existe pas.

Supprimer une ligne d'une table
DEPARTMENTS
Supprimez une ligne de la table DEPARTMENTS.

Instruction DELETE
Vous pouvez supprimer des lignes d'une table au

moyen de l'instruction DELETE.
DELETE
DELETE [FROM]
[FROM] table
table
[WHERE
[WHERE condition];
condition];

Supprimer des lignes d'une table
• La clause WHERE permet de supprimer des lignes

spécifiques.
DELETE
DELETE FROM
FROM departments
departments
WHERE
WHERE department_name
department_name == 'Finance';
'Finance';
11 row
row deleted.
deleted.
• En cas d'absence de la clause WHERE, toutes les

lignes sont supprimées.
DELETE
DELETE FROM
FROM copy_emp;
copy_emp;
22
22 rows
rows deleted.
deleted.

Supprimer des lignes associées à des
valeurs d'une autre table
Utilisez des sous-interrogations dans l'instruction
DELETE pour supprimer des lignes dont certaines
valeurs correspondent à celles d'une autre table.
DELETE FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name LIKE '%Public%');
1 row deleted.

Erreur de contrainte d'intégrité lors de la
suppression de lignes
DELETE
DELETE FROM
FROM departments
departments
WHERE
WHERE department_id
60;
DELETE
DELETE FROM
FROM departments
departments
**
ERROR
ERROR at
at line
line 1:
1:
ORA-02292:
ORA-02292: integrity
integrity constraint
constraint (HR.EMP_DEPT_FK)
(HR.EMP_DEPT_FK)
violated
violated -- child
child record
record found
found
Vous ne pouvez pas supprimer une ligne qui

contient une clé primaire utilisée comme clé
étrangère dans une autre table.

Utiliser une sous-interrogation dans une
instruction INSERT
INSERT
INSERT INTO
INTO
(SELECT
(SELECT employee_id,
employee_id, last_name,
last_name,
email,
email, hire_date,
hire_date, job_id,
job_id, salary,
salary,
department_id
department_id
FROM
FROM employees
employees
WHERE
WHERE department_id
department_id == 50)
50)
VALUES
VALUES (99999,
(99999, 'Taylor',
'Taylor', 'DTAYLOR',
'DTAYLOR',
TO_DATE('07-JUN-99',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'DD-MON-RR'),
'ST_CLERK',
'ST_CLERK', 5000,
5000, 50);
50);
11 row
row created.
created.

Utiliser une sous-interrogation dans une
instruction INSERT
• Verify
SELECT
SELECT the results last_name,
employee_id,
employee_id, last_name, email,
email, hire_date,
hire_date,
job_id,
job_id, salary,
salary, department_id
department_id
FROM
FROM employees
employees
WHERE
WHERE department_id
50;

Utiliser le mot-clé WITH CHECK OPTION
avec les instructions LMD
• Une sous-interrogation permet d'identifier la table
et les colonnes des instructions LMD.
• Le mot-clé WITH CHECK OPTION vous empêche de
modifier les lignes qui ne sont pas présentes dans
la sous-interrogation.
INSERT
INSERT INTO
INTO (SELECT
(SELECT employee_id,
employee_id, last_name,
last_name, email,
email,
hire_date, job_id, salary
hire_date, job_id, salary
FROM
FROM employees
employees
WHERE
WHERE department_id == 50
department_id 50 WITH
WITH CHECK
CHECK OPTION)
OPTION)
VALUES (99998, 'Smith', 'JSMITH',
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'DD-MON-RR'),
'ST_CLERK', 5000);
'ST_CLERK', 5000);
INSERT INTO
INSERT INTO
**
ERROR
ERROR at
at line
line 1:
1:
ORA-01402:
ORA-01402: view WITH
view WITH CHECK
CHECK OPTION
OPTION where-clause
where-clause violation
violation

Valeur par défaut explicite : présentation
• La fonction de définition de valeur par défaut

explicite vous permet d'utiliser le mot-clé DEFAULT
en tant que valeur de colonne lorsque vous avez
besoin d'une valeur de colonne par défaut.
• L'intégration de cette fonction permet la
conformité à la norme SQL: 1999.
• L'utilisateur peut ainsi contrôler quand et où la
valeur par défaut doit être appliquée aux données.
• Les valeurs par défaut explicites peuvent être
utilisées dans les instructions INSERT et UPDATE.

Utiliser des valeurs explicites par défaut
• DEFAULT avec INSERT :

INSERT
INSERT INTO
INTO departments
departments
(department_id,
(department_id, department_name,
department_name, manager_id)
manager_id)
VALUES
VALUES (300,
(300, 'Engineering',
'Engineering', DEFAULT);
DEFAULT);
• DEFAULT avec UPDATE :

UPDATE
UPDATE departments
departments
SET
SET manager_id
manager_id == DEFAULT
DEFAULT WHERE
WHERE department_id
10;

Instruction MERGE
• Permet de mettre à jour ou d'insérer des données

dans une table, de façon conditionnelle.
• Exécute une instruction UPDATE si la ligne existe
et une instruction INSERT s'il s'agit d'une nouvelle
ligne :
– Evite des mises à jour distinctes
– Améliore les performances et facilite l'utilisation
– S'avère particulièrement utile dans les applications
de data warehouse

Syntaxe de l'instruction MERGE
L'instruction MERGE vous permet de mettre à jour ou

d'insérer des lignes dans une table de façon
conditionnelle.
MERGE
MERGE INTO
INTO table_name
table_name table_alias
table_alias
USING
USING (table|view|sub_query)
(table|view|sub_query) alias
alias
ON
ON (join
(join condition)
condition)
WHEN
WHEN MATCHED
MATCHED THEN
THEN
UPDATE
UPDATE SET
SET
col1
col1 == col_val1,
col_val1,
col2
col2 == col2_val
col2_val
WHEN
WHEN NOT
NOT MATCHED
MATCHED THEN
THEN
INSERT
INSERT (column_list)
(column_list)
VALUES
VALUES (column_values);
(column_values);

Fusionner des lignes
Insérez ou mettez à jour des lignes dans la table

COPY_EMP pour qu'elle corresponde à la table
EMPLOYEES.
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
...
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);

Fusionner des lignes
SELECT
SELECT **
FROM
FROM COPY_EMP;
COPY_EMP;
no
no rows
rows selected
selected
MERGE
MERGE INTO
INTO copy_emp
copy_emp cc
USING
USING employees
employees ee
ON
ON (c.employee_id
(c.employee_id == e.employee_id)
e.employee_id)
WHEN MATCHED THEN
WHEN MATCHED THEN
UPDATE
UPDATE SET
SET
...
...
WHEN
WHEN NOT MATCHED
NOT MATCHED THEN
THEN
INSERT VALUES...;
INSERT VALUES...;
SELECT
SELECT **
FROM
FROM COPY_EMP;
COPY_EMP;
20
20 rows
rows selected.
selected.

Transactions de la base de données
Une transaction de base de données est constituée

de l'un des éléments suivants :
• des instructions LMD effectuant une modification
cohérente des données,
• une instruction LDD,
• une instruction LCD.

Transactions de la base de données
• Commence à l'exécution de la première instruction

LMD SQL.
• Se termine par l'un des événements suivants :
– Une instruction COMMIT ou ROLLBACK est lancée.
– Une instruction LDD ou LCD (validation
automatique) est exécutée.
– L'utilisateur quitte iSQL*Plus.
– Le système tombe en panne.

Avantages des instructions
COMMIT et ROLLBACK
Les instructions COMMIT et ROLLBACK vous permettent :

• de garantir la cohérence des données,
• d'afficher le résultat des modifications de données
avant qu'elles ne soient définitives,
• de regrouper de manière logique des opérations
associées.

Contrôler les transactions
Temps
COMMIT
Transaction
DELETE
SAVEPOINT A
INSERT
UPDATE
SAVEPOINT B
INSERT ROLLBACK ROLLBACK ROLLBACK

to SAVEPOINT B to SAVEPOINT A

Annuler des modifications
jusqu'à une étiquette
• Créez une étiquette dans la transaction courante à

l'aide de l'instruction SAVEPOINT.
• Annulez la transaction jusqu'à cette étiquette en
utilisant l'instruction ROLLBACK TO SAVEPOINT.
UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.

Traitement implicite des transactions
• Une validation automatique a lieu dans les

situations suivantes :
– exécution d'une instruction LDD,
– exécution d'une instruction LCD,
– sortie normale d'iSQL*Plus, sans instruction
COMMIT ou ROLLBACK explicite.
• Il se produit une annulation automatique en cas de
sortie anormale d'iSQL*Plus ou d'une panne du
système.

Etat des données avant
COMMIT ou ROLLBACK
• Il est possible de restaurer l'état précédent des données.

• L'utilisateur en cours peut afficher le résultat des
opérations LMD au moyen de l'instruction SELECT.
• Les résultats des instructions LMD exécutées par
l'utilisateur courant ne peuvent pas être affichés par
d'autres utilisateurs.
• Les lignes concernées sont verrouillées. Aucun autre
utilisateur ne peut les modifier.

Etat des données après COMMIT
• Les modifications des données dans la base sont

définitives.
• L'état précédent des données est irrémédiablement
perdu.
• Tous les utilisateurs peuvent voir le résultat des
modifications.
• Les lignes verrouillées sont libérées et peuvent de
nouveau être manipulées par d'autres utilisateurs.
• Tous les savepoints sont effacés.

Valider des données
• Effectuez les modifications.

DELETE
DELETE FROM
FROM employees
employees
WHERE
WHERE employee_id
employee_id == 99999;
99999;
11 row
row deleted.
deleted.
INSERT
INSERT INTO
INTO departments
departments
VALUES
VALUES (290,
(290, 'Corporate
'Corporate Tax',
Tax', NULL,
NULL, 1700);
1700);
11 row
row inserted.
inserted.
• Validez les modifications.

COMMIT;
Commit complete.

Etat des données après ROLLBACK
L'instruction ROLLBACK permet de rejeter toutes les

modifications de données en cours :
• Les modifications sont annulées.
• Les données retrouvent leur état précédent.
• Les lignes verrouillées sont libérées.
DELETE FROM copy_emp;
22 rows deleted.
ROLLBACK;
Rollback complete.

Annulation au niveau instruction
• Si une instruction LMD échoue pendant

l'exécution, seule cette instruction est annulée.
• Le serveur Oracle met en oeuvre un savepoint
implicite.
• Toutes les autres modifications sont conservées.
• L'utilisateur doit terminer explicitement les
transactions en exécutant une instruction COMMIT
ou ROLLBACK.

Cohérence en lecture
• La cohérence en lecture garantit à tout moment

une vue homogène des données.
• Les modifications apportées par un utilisateur
n'entrent pas en conflit avec celles d'un autre
utilisateur.
• La cohérence en lecture garantit sur les mêmes
données que :
– la lecture ignore les écritures en cours,
– l'écriture ne perturbe pas la lecture.

Implémenter la cohérence en lecture
Utilisateur A
UPDATE employees Blocs de
SET salary = 7000 données
WHERE last_name = 'Goyal';
Segments
d'annulation
Données
SELECT * modifiées
FROM userA.employees; Image et non
cohérente modifiées
en lecture avant modi-
fication des
"anciennes"
Utilisateur B données

Verrouillage
Les verrous d'une base de données Oracle :

• évitent les risques de destruction des données en
cas de transactions simultanées,
• n'exigent aucune intervention de l'utilisateur,
• s'appliquent au niveau de restriction le plus bas,
• sont actifs durant toute la transaction,
• sont de deux types : explicite et implicite.

Verrouillage implicite
• Deux modes de verrouillage :

– Verrou de type Exclusive : Verrouille l'accès à tous
les autres utilisateurs
– Verrou de type Share : Permet aux autres
utilisateurs d'accéder également aux données
• Haut niveau de simultanéité d'accès aux données
– LMD : Partage des tables, row exclusive
– Interrogations : Aucun verrou requis
– LDD : Protège les définitions d'objet
• Verrouillage maintenu jusqu'à validation ou
annulation

Synthèse
Ce chapitre vous a permis d'apprendre à utiliser des
instructions LMD et à contrôler les transactions.
Instruction Description
INSERT Ajoute une nouvelle ligne dans une table
UPDATE Modifie des lignes dans une table
DELETE Supprime des lignes d'une table
MERGE Insère ou met à jour des données dans une table

de façon conditionnelle
Valide toutes les modifications de données en
COMMIT cours jusqu'à l'étiquette du savepoint
Permet une annulation jusqu'à un savepoint

SAVEPOINT
Annule toutes les modifications de données en
ROLLBACK instance

Présentation de l'exercice 8
Dans cet exercice, vous allez :

• insérer des lignes dans une table
• mettre à jour et supprimer des lignes dans une
table
• contrôler des transactions

3-Manipuler Des Données

Transféré par

Droits d'auteur :

Formats disponibles

3-Manipuler Des Données

Transféré par

Informations du document

Copyright

Formats disponibles

Partager ce document

Partager ou intégrer le document

Options de partage

Avez-vous trouvé ce document utile ?

Ce contenu est-il inapproprié ?

Droits d'auteur :

Formats disponibles

3-Manipuler Des Données

Transféré par

Droits d'auteur :

Formats disponibles

8

Manipuler des données

Copyright © Oracle Corporation, 2001. Tous droits réservés.

A la fin de ce chapitre, vous pourrez :

8-2 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• Une instruction LMD est exécutée lorsque vous :

8-3 Copyright © Oracle Corporation, 2001. Tous droits réservés.

8-4 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• L'instruction INSERT permet d'ajouter de nouvelles

• Cette syntaxe n'insère qu'une seule ligne à la fois.

8-5 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• Insérez une nouvelle ligne en précisant une valeur

• Placez les valeurs de type caractère et date entre

8-6 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• Méthode implicite : n'indiquez pas la colonne dans

INSERT INTO departments (department_id,

• Méthode explicite : indiquez le mot-clé NULL dans

8-7 Copyright © Oracle Corporation, 2001. Tous droits réservés.

La fonction SYSDATE enregistre la date et l'heure en

8-8 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• Ajoutez un nouvel employé.

8-9 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• Utilisez le caractère de substitution & dans une

8-10 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• Ecrivez votre instruction INSERT en précisant une

• N'utilisez pas la clause VALUES.

8-11 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Mettez à jour les lignes de la table EMPLOYEES.

8-12 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• Utilisez l'instruction UPDATE pour modifier des

• Si nécessaire, vous pouvez modifier plusieurs

8-13 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• La clause WHERE permet de modifier une ou

• En cas d'absence de la clause WHERE, toutes les

8-14 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Modifiez le poste et le salaire de l'employé 114 pour

8-15 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Utilisez des sous-interrogations dans l'instruction

8-16 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Le numéro de service 55 n'existe pas.

8-17 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Supprimez une ligne de la table DEPARTMENTS.

8-18 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Vous pouvez supprimer des lignes d'une table au

8-19 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• La clause WHERE permet de supprimer des lignes

• En cas d'absence de la clause WHERE, toutes les

8-20 Copyright © Oracle Corporation, 2001. Tous droits réservés.

8-21 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Vous ne pouvez pas supprimer une ligne qui

8-22 Copyright © Oracle Corporation, 2001. Tous droits réservés.

8-23 Copyright © Oracle Corporation, 2001. Tous droits réservés.

8-24 Copyright © Oracle Corporation, 2001. Tous droits réservés.

8-25 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• La fonction de définition de valeur par défaut

8-26 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• DEFAULT avec INSERT :

• DEFAULT avec UPDATE :

8-27 Copyright © Oracle Corporation, 2001. Tous droits réservés.

• Permet de mettre à jour ou d'insérer des données