Post on 04-Apr-2015
TECHNOLOGIES ORACLE
Manipulation des données
© sebvita.com
Objectifs
En suivant ce cours, vous serez capable :• De décrire tous les ordres
DML• D’insérer des lignes dans
une table• De les modifier ou
supprimer• De contrôler les
transactions
© sebvita.com
L’ordre INSERT
© sebvita.com
Présentation• Un ordre DML est exécuté lors :
– De l’ajout de lignes dans une table– De la modification de lignes– De la suppression de lignes
• Une transaction est un ensemble d’ordres DML qui constituent une unité logique de travail
© sebvita.com
L’ordre INSERTAjouter une ligne à une table
Insérer une nouvelle ligne dans la table
DEPARTMENTS
Nouvelle ligneDEPARTMENTS
© sebvita.com
Syntaxe• Ajouter une nouvelle ligne avec la clause
INSERT :
• Avec cette syntaxe on insère une ligne à la fois
INSERT INTO table [(column [, column...])]VALUES (value [, value...]);
© sebvita.com
Exemple basique• Insérer une ligne avec une valeur pour chaque
colonne• Lister les valeurs dans l’ordre des colonnes de
la table• Éventuellement, lister les colonnes dans la
clause
• Mettre les chaînes de caractères et les dates entre quotes simples
INSERT INTO departments(department_id, department_name, manager_id,location_id)VALUES (70, 'Public Relations', 100, 1700);
1 row created.
© sebvita.com
Exemple avec des valeurs NULL• Implicite : Ne pas mettre les colonnes
• Explicite : Écrire NULL dans les valeurs
INSERT INTO departments (department_id, department_name )VALUES (30, 'Purchasing');
INSERT INTO departmentsVALUES (100, 'Finance', NULL, NULL);
1 row created.
1 row created
© sebvita.com
Exemple avec des fonctions• La fonction SYSDATE retourne les date et
heure courantesINSERT 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.
© sebvita.com
Exemple avec des dates• Ajouter un nouvel employé
• Vérifier l’ajout
INSERT INTO employeesVALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30);
1 row created.
© sebvita.com
Exemple de création de script• Utiliser les variables de substitution
INSERT INTO departments (department_id, department_name, location_id)VALUES (&department_id,'&department_name',&location );
1 row created.
© sebvita.com
Copier les lignes d’une autre table
• Écrire l’INSERT avec une sous-requête
• Ne pas utiliser de clause VALUES• Faire correspondre les colonnes de
l’INSERT et de la sous-requête
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.
© sebvita.com
© sebvita.com
VOUS AVEZ DES QUESTIONS ?
L’ordre UPDATE
© sebvita.com
Changer les données d’une tableEMPLOYEES
Mettre à jour la table EMPLOYEES :
© sebvita.com
Syntaxe• Modifier des lignes existantes avec UPDATE
• Peut mettre à jour plusieurs lignes en même temps
UPDATE table SET column = value [, column = value, ...][WHERE condition];
© sebvita.com
Exemples• Lignes spécifiques avec la clause WHERE
• Toutes les lignes de la table
UPDATE employeesSET department_id = 70WHERE employee_id = 113;
UPDATE copy_empSET department_id = 110;
1 row updated.
22 row updated.
© sebvita.com
Exemple avec sous-requêtes• Mettre à jour le métier et le salaire de
l’employé 114 avec les données de l’employé 205UPDATE employeesSET 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.
© sebvita.com
Utiliser une autre table• Utiliser les données d’une autre table
grâce aux sous-requêtes
UPDATE copy_empSET 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.
© sebvita.com
DEFAULT• Utiliser la valeur par défaut d’une colonne• Peut-être utilisé dans les INSERT et UPDATEINSERT INTO deptm3 (department_id, department_name, manager_id)VALUES (300, 'Engineering', DEFAULT);
UPDATE deptm3SET manager_id = DEFAULTWHERE department_id = 10;
© sebvita.com
© sebvita.com
VOUS AVEZ DES QUESTIONS ?
Les ordres DELETE et TRUNCATE
© sebvita.com
Supprimer les lignes d’une table
DEPARTMENTS
Supprimer une ligne de la table DEPARTMENTS :
© sebvita.com
Syntaxe• Supprimer des lignes existantes d’une
table
DELETE [FROM] table[WHERE condition];
© sebvita.com
Exemples• Lignes spécifiques avec la clause WHERE
• Toutes les lignes de la table
DELETE FROM departmentsWHERE department_name = 'Finance';
DELETE FROM copy_emp;
1 row deleted.
22 rows deleted.
© sebvita.com
Exemple avec sous-requêtes• Supprimer des lignes d’une table en
fonction des données d’une autre
DELETE FROM employeesWHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');
1 row deleted.
© sebvita.com
L’ordre TRUNCATE• Supprimer toutes les lignes d’une table• Est un DDL et non un DML : ne peut pas
être annulé aussi facilement• Syntaxe :
• Exemple :
TRUNCATE TABLE table_name;
TRUNCATE TABLE copy_emp;
© sebvita.com
© sebvita.com
VOUS AVEZ DES QUESTIONS ?
L’ordre MERGE
© sebvita.com
Présentation• Permet une insertion ou mise à jour
conditionnelle• Lance un UPDATE si la ligne existe, un
INSERT dans le cas contraire :– Évite les mises à jour séparées– Simplifie l’utilisation et améliore les
performances– Très utile dans les applications de
datawarehouse
© sebvita.com
Syntaxe• L’ordre MERGE permet un UPDATE ou un INSERT selon une conditionMERGE 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);
© sebvita.com
Exemple• Insérer ou modifier les lignes dans COPY_EMP pour correspondre à la table EMPLOYEESMERGE 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_idWHEN 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);
© sebvita.com
ExempleSQL> SELECT * 2 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.
© sebvita.com
© sebvita.com
VOUS AVEZ DES QUESTIONS ?
Contrôle des transactions
© sebvita.com
Présentation• Une transaction de base de données
consiste en un des points suivants :– Des ordres DML– Un ordre DDL– Un ordre DCL
© sebvita.com
Présentation• Une transaction commence avec le
premier DML• Se termine avec :
– Un COMMIT ou ROLLBACK– Un DDL ou DCL (commit automatique)– L’utilisateur quitte iSQL*Plus– Le système plante
© sebvita.com
Présentation• Les avantages des transactions :
– Assurer la consistance des données– Prévisualiser les changements avant de
les rendre permanents– Grouper les opérations logiques
© sebvita.com
Chronologie
DELETE
INSERT
UPDATE
INSERT
ROLLBACK to SAVEPOINT B
ROLLBACK to SAVEPOINT A
ROLLBACK
SAVEPOINT B
SAVEPOINT A
Transaction
COMMITTemps
© sebvita.com
Revenir en arrière• Créer un point de retour dans une
transaction avec SAVEPOINT• Revenir en arrière avec ROLLBACK TO SAVEPOINT
INSERT...ROLLBACK TO update_done ;
UPDATE...SAVEPOINT update_done ;
Savepoint created.
Rollback complete.
© sebvita.com
Revenir en arrière• Un commit automatique a lieu :
– Lors d’un DDL– Lors d’un DCL– Lorsqu’on quitte iSQL*Plus sans COMMIT
ou ROLLBACK• Un rollback automatique a lieu lorsqu’on
quitte de manière anormale
© sebvita.com
État des données• Avant un COMMIT ou un ROLLBACK :
– L’état des données peut être retrouvé– L’utilisateur en cours peut prévisualiser
les données– Les autres utilisateurs ne peuvent pas
voir les changements– Les lignes affectées sont verrouillées ;
les autres utilisateurs ne peuvent pas les modifier
© sebvita.com
État des données• Après un COMMIT
– Les changements sont rendus permanents
– L’état précédent des données est perdu– Tous les utilisateurs peuvent voir le
résultat– Les verrous sont relâchés– Tous les points de retour sont effacés
© sebvita.com
Exemple• Changement des données
• Validation des données
DELETE FROM employeesWHERE employee_id = 99999;
COMMIT ;
INSERT INTO departmentsVALUES (290, 'Corporate Tax', NULL, 1700);
1 row deleted.
1 row created.
Commit complete.
© sebvita.com
État des données• Après un ROLLBACK
– Les changements sont annulés– L’état précédent est restauré– Les verrous sur les lignes sont relâchés
DELETE FROM copy_emp;
ROLLBACK ;
22 rows deleted.
Rollback complete.
© sebvita.com
ExempleDELETE FROM test;
ROLLBACK;
DELETE FROM test WHERE id = 100;
SELECT * FROM test WHERE id = 100;
COMMIT;
25,000 rows deleted.
Rollback complete.
1 row deleted.
No row selected.
Commit complete.
© sebvita.com
Rollback d’une requête• Si un seul DML plante pendant
l’exécution, seul ce DML est annulé• Le serveur Oracle implémente un SAVEPOINT implicite
• Tous les autres changements sont maintenus
• L’utilisateur peut terminer explicitement avec un COMMIT ou un ROLLBACK
© sebvita.com
Consistance des données• La lecture consistante des données
garantie la consistance des résultats à tout moment
• Les changements d’un utilisateur n’influent pas ceux d’un autre utilisateur
• Elle assure également que pour des mêmes données :– Les lecteurs n’attendent pas les
« écrivains »– Les « écrivains » n’attendent pas les
lecteurs© sebvita.com
Lecture consistante
User B
User A
UPDATE employeesSET salary = 7000WHERE last_name = 'Grant';
SELECT *FROM userA.employees;
© sebvita.com
© sebvita.com
VOUS AVEZ DES QUESTIONS ?
Manipulation des données
© sebvita.com
Résumé
Les transactionsLes ordres
DML
Ordres DDL
© sebvita.com
La lecture consistante
Ressources• http://www.oracle.com• http://otn.oracle.com
© sebvita.com