Technologies Oracle

1. Technologies Oracle Manipulation des données

2. 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 modifierou supprimer • De contrôler les transactions

3. L’ordre INSERT

4. 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

5. L’ordre INSERT Ajouter une ligne à une table Nouvelle ligne DEPARTMENTS Insérerune nouvelle lignedans la table DEPARTMENTS

6. 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...]);

7. 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.

8. 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'); 1 row created. INSERT INTO departments VALUES (100, 'Finance', NULL, NULL); 1 row created

9. Exemple avec des fonctions • La fonction SYSDATE retourne les date et heure courantes 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.

10. Exemple avec des dates • Ajouter un nouvel employé • Vérifier l’ajout 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.

11. 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.

12. 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.

13. Vous avez des questions ?

14. L’ordre UPDATE

15. Changer les données d’une table EMPLOYEES Mettreà jour la table EMPLOYEES:

16. 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];

17. Exemples • Lignes spécifiques avec la clause WHERE • Toutes les lignes de la table UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated. UPDATE copy_emp SET department_id = 110; 22 row updated.

18. Exemple avec sous-requêtes • Mettre à jour le métier et le salaire de l’employé 114 avec les données 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.

19. Utiliser une autre table • Utiliser les données d’une autre table grâce aux sous-requêtes 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.

20. DEFAULT • Utiliser la valeur par défaut d’une colonne • Peut-être utilisé dans les INSERT et UPDATE INSERT INTO deptm3 (department_id, department_name, manager_id) VALUES (300, 'Engineering', DEFAULT); UPDATE deptm3 SET manager_id = DEFAULT WHERE department_id = 10;

21. Vous avez des questions ?

22. Les ordres DELETE et TRUNCATE

23. Supprimer les lignes d’une table DEPARTMENTS Supprimeruneligne de la table DEPARTMENTS :

24. Syntaxe • Supprimer des lignes existantes d’une table DELETE [FROM] table [WHERE condition];

25. Exemples • Lignes spécifiques avec la clause WHERE • Toutes les lignes de la table DELETE FROM departments WHERE department_name = 'Finance'; 1 row deleted. DELETE FROM copy_emp; 22 rows deleted.

26. Exemple avec sous-requêtes • Supprimer des lignes d’une table en fonction des données d’une autre DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%'); 1 row deleted.

27. 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;

28. Vous avez des questions ?

29. L’ordre MERGE

30. 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

31. Syntaxe • L’ordre MERGE permet un UPDATE ou un INSERT selon une condition MERGE INTO table_nametable_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);

32. Exemple • Insérer ou modifier les lignes dans COPY_EMP pour correspondre à 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);

33. Exemple SQL> 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.

34. Vous avez des questions ?

35. Contrôle des transactions

36. Présentation • Une transaction de base de données consiste en un des points suivants : • Des ordres DML • Un ordre DDL • Un ordre DCL

37. 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

38. 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

39. Chronologie COMMIT Temps Transaction DELETE SAVEPOINTA INSERT UPDATE SAVEPOINTB INSERT ROLLBACKtoSAVEPOINT B ROLLBACKtoSAVEPOINT A ROLLBACK

40. Revenir en arrière • Créer un point de retour dans une transaction avec SAVEPOINT • Revenir en arrière avec ROLLBACK TO SAVEPOINT UPDATE... SAVEPOINT update_done ; Savepoint created. INSERT... ROLLBACK TO update_done ; Rollback complete.

41. 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

42. É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

43. É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

44. Exemple • Changement des données • Validation des données DELETE FROM employees WHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); 1 row created. COMMIT ; Commit complete.

45. É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; 22 rows deleted. ROLLBACK ; Rollback complete.

46. Exemple DELETE FROM test; 25,000 rows deleted. ROLLBACK; Rollback complete. DELETE FROM test WHERE id = 100; 1 row deleted. SELECT * FROM test WHERE id = 100; No row selected. COMMIT; Commit complete.

47. 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

48. 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

49. Lecture consistante UPDATE employees SET salary = 7000 WHERE last_name = 'Grant'; User A SELECT * FROM userA.employees; User B

50. Vous avez des questions ?