Manipuler des données
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
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.
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 INTO table [(column [, column...])]
VALUES (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 GETDATE() 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',
GETDATE(), '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',
‘1999-02-13’,
'AC_ACCOUNT', 11000, NULL, 100, 30);
1 row created.
• Vérifiez l'ajout.
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 table
SET column = value [, column = value, ...]
[WHERE 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 copy_emp
SET department_id = 110;
22 rows 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 employees
SET department_id = 55
WHERE department_id = 110;
UPDATE employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPT_FK)
violated - parent key not found
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 [FROM] table
[WHERE condition];
Supprimer des lignes d'une table
• La clause WHERE permet de supprimer des lignes
spécifiques.
DELETE FROM departments
WHERE department_name = 'Finance';
1 row deleted.
• En cas d'absence de la clause WHERE, toutes les
lignes sont supprimées.
DELETE FROM copy_emp;
22 rows 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 FROM departments
WHERE department_id = 60;
DELETE FROM departments
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK)
violated - child record found
Utiliser une sous-interrogation dans une
instruction INSERT
INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id, salary,
department_id
FROM employees
WHERE department_id = 50)
VALUES (99999, 'Taylor', 'DTAYLOR',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000, 50);
1 row created.
Utiliser une sous-interrogation dans une
instruction INSERT
• Verify
SELECT the resultslast_name, email, hire_date,
employee_id,
job_id, salary, department_id
FROM employees
WHERE department_id = 50;
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 INTO departments
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);
ou spécifier les noms de colonnes
• DEFAULT avec UPDATE :
UPDATE departments
SET manager_id = DEFAULT 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 INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
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 *
FROM COPY_EMP;
no rows selected
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
...
WHEN NOT MATCHED THEN
INSERT VALUES...;
SELECT *
FROM COPY_EMP;
20 rows selected.
Synthèse
Ce chapitre vous a permis d'apprendre à utiliser des
instructions LMD .
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
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
