technologies oracle

TECHNOLOGIES ORACLE

Manipulation des données

© sebvita.com

ObjectifsEn 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êtesUPDATE 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

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

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

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

INSERTROLLBACK 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

technologies oracle

Documents