INSERT, UPDATE et DELETE
DML (Data Manipulation Langage)
Le DML est une partie essentielle du SQL. Quand on veuille attacher, réactualiser ou effacer des données dans une base de données on exécute une instruction du DML. Plusieurs instructions de DML groupées dans une unité logique font une TRANSACTION.
L’instruction INSERT est utilisée pour ajouter de nouvelles lignes dans la base de données. Ceci peut être fait a raison d’une ligne a la fois en utilisant l’expression VALUES, ou avec un ensemble d’enregistrements en utilisant une sous-requete. La syntaxe de l’instruction INSERT est :
INSERT INTO schema.table [(colonne [, colonne,…])]
VALUES (value [, value…]) sous - requête ;
Ou :
*Les objets entre les ‘ [ ] ‘ peuvent manquer, tandis que les objets entre ‘ ( ) ‘ on doit les mettre toujours.
1. Ajouter une nouvelle ligne dans une table
On peut ajouter des nouvelles lignes dans une table en utilisant l’instruction INSERT à la syntaxe suivante :
INSERT INTO tableau [(colonne [, colonne,…])] VALUES (valeur [, valeur…]);
Ou :
Note : Cette instruction avec la requête VALUES ajoute seulement une ligne à la fois.
Cet exemple insère une ligne dans la table dept. Les séries dept_seq et adrs_seq sont utilisées pour extraire les valeurs numériques suivantes de dept_no et adrs_id.
2. Insérer des nouvelles lignes
Si on insère une nouvelle ligne qui contienne des valeurs pour chaque colonne il n’est pas nécessaire de mettre les noms des colonnes dans la requête INSERT. Mais les valeurs doivent être listées dans l’ordre des colonnes du tableau, et on doit avoir une valeur pour chaque colonne.
Si plusieurs lignes doivent être insérées, l’instruction INSERT aurait du être exécute pour chaque ligne individuelle. Si une sous-requete peut être utilisée, plusieurs lignes seraient insérées pour chaque ligne rendue par la sous-requete.
Cet exemple va insère toutes les lignes de la table candidates correspondant au numéro de département 30. Comme le numéro de département et l’identification du manager sont connues, ces informations sont utilisées en tant que constantes dans la sous-requete.
CONSEIL : Pour avoir la certitude que la ligne serait correctement introduite, mettez les noms des colonnes dans la requête INSERT.
On doit fermer la date et les mots dans des apostrophes mais n’est pas recommandée de fermer aussi les valeurs numériques dans des apostrophes car on peut avoir une conversion implicite des valeurs numériques correspondantes aux colonnes du type NUMBER.
EXEMPLE :
3. Insérer des lignes avec des valeurs nulles
Pour insérerdes lignes avec des valeurs nulles on utilise deux méthodes :
1. Méthode implicite : Mettre la liste avec les noms des colonnes, mais omettre les noms des colonnes pour lesquelles il y a une valeur nulle dans la ligne qu’on veut insérer comme dans l’exemple suivant :
2. Méthode explicite : Sans mettre la liste des colonnes on écrive seulement le mot « NULL » a la place des valeurs nulles dans la ligne.
Si on insère une nouvelle ligne dans un tableau qui contienne les colonnes : deptno, dname, mgr, loc, on aura :
Le serveur ORACLE impose automatiquement tous les types des données et tous les conditions d’intégrité et pour chaque colonne qui n’est pas listée explicitement on va avoir la valeur nulle dans la nouvelle ligne.
Les erreurs les plus fréquentes sont les suivantes :
4. Insérer des valeurs spéciales
Pour mettre des valeurs spéciales dans le tableau on peut utiliser des fonctions comme par exemple, la fonction SYSDATE qui enregistre la date et l’heure curent :
On peut aussi utiliser la fonction USER si on veuille enregistrer le nom de l’utilisateur dans la nouvelle ligne qu’un veuille insérer :
5. Pour confirmer l’addition dans le tableau on fait :
6. Insérer des données avec des valeurs spécifiées
Le format le plus fréquent utilisé pour insérer la date est : DD-MON-YY. La valeur de défaut du siècle est la valeur currente. Pour l’heure, la valeur de défaut est le minuit (00 :00 :00).On doit utiliser la fonction TO_DATE si on veuille mettre la datte dans un format différente que celui de défaut.
Si au lieu du exemple ci-dessous on utilise l’exemple suivant l’année de la colonne « date » va être Feb 7, 2096 :
7. Créer un script pou manipuler les données
On peut sauver des commandes avec la substitution des variables dans un fichier et exécuter les commandes dans le fichier. Si on exécute le fichier avec le script nous sommes invites à introduire les variables &substitution. Les valeurs introduites seront remplacées dans l’instruction. Ça nous permette d’exécuter le même script plusieurs fois, mais fournir des valeurs différentes chaque fois.
Le « & » est une location pour le valeur de le variable !
8. Copier des lignes d’un autre tableau
On peut utiliser l’instruction INSERT pour attacher des lignes dans un tableau ou les valeurs sont prises d’un autre tableau mais au lieu de la requête VALUES on utilise une sous requête.
On peut copier des lignes dans un tableau prises d’un autre tableau en utilisant l’instruction INSERT INTO qui a la syntaxe suivante :
INSERT INTO table [(colonne [, colonne,…])] sous requête ;
ou :
Sous requête est la sous requête qui retourne les lignes dans la table.
Le numéro des colonnes et leur type de données dans la liste de la requête INSERT doit être en concordance avec le numéro et le type des valeurs dans la sous requête Pour créer une copie des lignes d’un tableau on doit utiliser SELECT* dans la sous requête
9. Changement des données dans un tableau
On modifie les lignes déjà existantes en utilisant l’instruction UPDATE qui a la syntaxe suivante :
UPDATE schema.table
SET column(s) = expr sub que
WHERE condition
ou :
On confirme l’opération de réactualisation en faisant une interrogation sur la table pour afficher les lignes réactualisées.
10. Réactualiser des lignes dans un tableau
L’instruction UPDATE modifie les lignes spécifiées si la requête WHERE est utilisée :
Si on ne met pas la requête WHERE toutes les lignes du tableau vont être modifiées :
Cet exemple va transférer un employé nomme JOHN DOE dans le département 30. S’il y a plus d’un JOHN DOE, d’autres restrictions devront être apportées à la clause WHERE.
Cet exemple de mise à jour va donner à toutes les personnes de la table emp une augmentation de salaire de 5 pour cents.
Cet exemple va donner des augmentations aux employés localises a Rochester. Le montant de l’augmentation est traite par l’instruction DECODE qui évalue le nom du département Les employés du département des ventes (Sales) vont recevoir une augmentation de 10 pour cents, ceux du département administratif (Admin) sept pour cents et tous les autres six pour cents.
11. Réactualisation de deux colonnes avec une sous requête
On peut réactualiser plusieurs colonnes dans la requête SET d’une instruction UPDATE en écrivant des sous requêtes multiples. Pour faire ça on utilise la syntaxe suivante :
UPDATE tableau
SET colonne= (SELECT colonne FROM tableau WHERE condition)
[ , colonne= (SELECT colonne FROM tableau WHERE condition) ]
[WHERE condition] ;
Dans l’exemple suivant on va réactualiser le salaire, le nom du département, et le ID du département du employer avec le ID 41 pour être les mêmes avec celles du employée avec le ID 31 :
Note : Si on n’a pas réactualisé aucune ligne on va recevoir le message « 0 rows updated » .
12. Réactualiser des lignes en utilisant un autre tableau
On peut utiliser des sous requêtes dans l’instruction UPDATE pour réactualiser lignes dans un tableau en utilisant des valeurs prises d’un autre tableau.
Dans l’exemple suivant réactualise le tableau copy_départements en utilisant les valeurs du tableau départements. Change le nom du département de tous le employées avec l’ID = 31 avec le nom du département du empluyee avec le ID =71.
13. Réactualisation des lignes : erreur donnée par la condition d’intégrité
Une erreur apparaisse si on essaye de réactualiser une enregistration avec une valeur qui est reliée à une condition d’intégrité.
Note : Les conditions d’intégrité assure que les données sont en concordance avec un group des règles prédéfinies.
Dans l’exemple suivant , le département numéro 17 n’existe pas dans le tableau principal ainsi que on receivera la violation de la contrainte « parent key » ORA-02291
14. Effacer une ligne d’un tableau
L’instruction DELETE est utilisée pour retirer des lignes de la base de données La syntaxe de DELETE est :
DELETE [FROM] table [WHERE condition] ;
DELETE FROM schema.table
WHERE condition
Ou :
Si employé John Doe quitte l’entreprise, on va probablement vouloir le supprimer de la base de donnée Une manière de faire ceci est de supprimer la ligne contenant son nom des tables adresses et emp. Afin de trouver John Doe dans la table adresses, il faut exécute une sous-requete en utilisant la table emp. Par conséquent, l’entrée de la table emp doit être la dernière ligne a être supprimée, sans quoi il y aurait une ligne orpheline dans la table adresses
Dans cet exemple, toutes les lignes de la table dept vont être supprimées si la valeur correspondante adrs_id est nulle.
Une opération de suppression est permanente ! Une fois faite, il est impossible de récupérer la ou les ligne(s) autrement que par une opération INSERT. Il n’y a pas de commande undo.
Note : Si aucune ligne n’est pas effacée un meddage « 0 rows deleted » apparaisse.
Les lignes spécifiées vont être effacées si on utilise la requête WHERE de la manieremontredansl’exemplesutvant
Si on n’utilise pas la requête WHERE toutes les lignes vont être effacées :
On peut effacer des lignes spécifiées en utilisant la requête WHERE dans l’instruction DELETE. On peut confirmer l’opération en affichant les lignes effacées avec l’instruction SELECT :
15. Effacer des lignes en utilisant un autre tableau
On peut utiliser des sous requêtes dans l’instruction DELETE pour effacer des lignes d’un tableau en utilisant des valeurs qui se trouvent dans un autre tableau .
Dans l’exemple suivant on efface tous les employées qui sont dans le département dont le nom contiennent le mot « finance » :
16. Effacer lignes : erreur donnée par la condition d’intégrité
Si on a l’intention de effacer une enregistration qui contienne une valeur reliée à une condition d’intégrité le résultat obtenu sera une erreur.
On peut pas effacer une ligne qui contienne une clé primaire qui est utilisée comment clé secondaire dans un autre tableau :
L’exemple ci-dessus comprend une tentative de effacer le département numéro 10 du tableau « départements », mais le résultat obtenu est une erreur, car le numéro du département est utilise comment clé secondaire dans le tableau « employées». L’erreur n’est pas valable si dans le département 10 il n’y a pas aucun employé.
17. Utiliser une sous requête dans une instruction INSERT
On peut remplacer le nom du table dans une requête INTO d’une une instruction INSERT. La liste select de la sous requête doit avoir le même numéro des colonnes que la liste des colonnes dans la requête VALUES. Pour éviter les erreurs dans l’instruction INSERT on doit mettre les colonnes dans la même ordre que dans le table de base.
18. Utilisation du mot-clé WITH CHECK OPTION dans les instructions DML
On utilise le mot-clé WITH CHECK OPTION pour indiquer que si une sous requête est utilisée pour un tableau dans une instruction INSERT, UPDATE ou DELETE, aucun changement de nature a modifier les valeurs sur une colonne qui n’est pas dans la sous requête ne serait pas possible.
L’exemple suivant monstre comment la sous requête introduit des valeurs pour la location 151 dans le tableau « départements », mais le ID de la location n’apparaisse pas dans la liste du SELECT et n’a pas aucune valeur dans la liste des valeurs, ainsi que si on insère cette ligne on aura pour le ID de la location la valeur nulle qui n’apparaisse pas dans la liste des valeurs.
On obtiendra donc l’erreur « view WITH CHECK OPTION where-clause.
DEFAULT
En utilisant le vue d’ensemble du dispositif explicite du défaut, on peut utilisez le mot clé DEFAULT comme une valeur du colonne, la ou le nomme défaut du colonne est désirée. L’adition de ce dispositif a été fait pour la concordance avec le SQL : 1999 Standard. Cette facilite permet l’utilisateur contrôler ou est quand le valeur défaut doit être utilise. Le dispositif explicite du défaut peut être utilisez dans les déclarations de type INSERT et UPDATE pour identifie la valeur défaut d’une colonne. Si la valeur défaut n’existe pas, une valeur nul est utilise.
L’utilisation des valeurs Défaut explicites :
On doit spécifier DEFAULT pour placer la valeur utilisées au paravant comme la valeur défaut pour le colonne ; si aucune valeur n’était pas spécifiée, Oracle va placer la valeur nul Dans le premier exemple, la déclaration INSERT utilise une valeur défaut pour la colonne MANAGER_ID ; si aucune valeur était spécifiée pour la colonne, la valeur nul est placée. Dans le second exemple, la déclaration UPDATE est utilisé pour placer une valeur défaut pour la colonne 10. si pour le colonne n’est pas aucune valeur définie, la déclaration change la valeur en nul
La déclaration MERGE (de fusion)
La déclaration MERGE est utilisé pour actualiser ou insère des données dans une table de base de données et en effet c’est une UPDATE si la ligne existe et une INSERT pour une nouvelle ligne. L’avantage pour cette opération c’est que il est très utile pour éviter les actualisations indépendantes de la base de données et en même temps est assez facile et rapide à utiliser, essentiellement dans les applications de la gestion. Dans ce cas, il est besoin de travailler avec des données provenant des sources diffrerentes st donc le risque d’avoir les mmêmesdonnées deux ou plusieurs fois est assez grande ; mais, en utilisant la déclaration MERGE, les lignes peuvent êtres modifiées ou insérées dans une manière conditionnée.
Au cause du fait que la déclaration est déterministe, le même ligne ne peut pas être réactualisée plusieurs fois en même déclaration MERGE. Ce qui est assez important, ce le fait que pour utiliser le MERGE, on doit avoir le droit de INSERT et UPDATE.
La syntaxe pour MERGE c’est le suivant :
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) ;
TRANSACTIONS
Une transaction est une opération sur la base de données ; une transaction implique un ou plusieurs modifications, opereees sur une ou plusieurs tables. Ils existent deux classe des transactions : DML – qui contuinet une nombre quelconque des bloques DML et qui est une changement effective des données, tratees par ORACLE comme une seule unité logique de travail et DDL – qui contient une seule bloque DDL. Pendant l’exécution d’une transaction, toutes les modifications sont effectuées sur la base de données, ou aucune modification n’est pas accomplie. Les transactions offrent l’utilisateur une degré assez élevée de flexibilité et assurent la consistance des données dans le cas d’une erreur.
Une transaction sur la base de données comnece avec la première commande DML ou DDL exécutable est finie dans une des situations suivantes :
Une bloque DDL est automatiquement exécutée est c’est le raison pour laquelle il finie implicitement une transaction. après finir une transaction, le suivant bloque SQL exécutable va commencer la suivante transaction.
En utilisant COMMIT et ROLLBACK on peut assurer la consistance des données, les prévisualisations des modifications en avant de les permanantisees et le groupement logique des opérations reliées.
Pour que les modifications soit permanentes, elles doivent être exécutées sur la base des données : le COMMIT réalise la permanentisation des modifications, au temps que ROLLBACK permet l’abandon ou l’annulation des modifications. Les modifications qui ne sont pas exécutées peuvent être abandonnes en utilisant le ROLLBACK, qui va attribue aux données les valeurs initiales (par les valeurs initiales on entend les valeurs après exécution de la dernière commande COMMIT), par l’annulation des toutes les modifications après les dernière COMMIT. La modification ou les modifications sur la base des données entre deux commandes COMMIT représente une transaction. jusque le moment que le transaction est exécute, aucune modification n’est pas visible pour les utilisateurs. Before COMMIT et ROLLBACK, les lignes qui sont en cours de modification sont ‘locked’, les autres utilisateurs ne peuvent pas changer les données sur cette lignes.
Exemple
SAVEPOINT
Le SAVEPOINT peut être utilise pour partager une transaction en parties ; les savepoints (points de sauvegarde) permet l’utilisateur de sauver le travail dans quelque point (moment de temps), avec l’option d’enregistre plus tard ou d’annuler plus tard une partie ou l’entière. Comme exemple, pour une transaction assez longue, on peut sauver des parties pendant exécution Le grande avantage c’est que une erreur ne détermine exécution des toutes les bloques.
La syntaxe pour SAVEPOINT (point de sauvegarde) est la suivante :
SAVEPOINT nomme_savepoint
Exemple :
SAVEPOINT finir_modifications
A la création d’une nouveau savepoints avec le nomme du précédant savepoints, le premier est efface. Le nombre maximum des savepoints pour un utilisateur est implicitement 5, mais la limite peut être change.
ROLLBACK avec SAVEPOINT
L’instruction ROLLBACK est utilise pour refaire quelque chose ; ROLLBACK avec la clause SAVEPOINT signifie le retour a un savepoints ROLLBACK sans TO SAVEPOINT finir la transaction, annule les modifications dans la transaction courrant, efface toutes les savepoints de la transaction et les lignes ne sont plus bloquées
Dans le cas des erreurs (system crash etc.) , si un seule opération DML est affecté, seulement cet opération est ROLLBACK. Le serveur ORACLE crée un savepoints implicitement et toutes les autres modifications sont retenues. L’utilisateur doit finir le transaction explicitement en utilisant une déclaration COMMIT ou ROLLBACK. En même temps, ORACLE fait un COMMIT implicitement avant et après quelque déclaration DDL ; de cette raison la, même si la déclaration DDL n’est pas exécute correctement, l’utilisateur ne peut pas faire une rollback a la déclaration antérieure a cause du fait que le serveur a fait le commit.
Donc, le retour a un savepoints :
L’uuniformitédu lecture doit être assure pour la base de données L’utilisateurs de la base de données faits deux types des opérations : de lecture (SELECT) et d’écriture (INSERT, UPDATE, DELETE). La consistance de la lecture concerne l’uniformité de cette opérations Les modifications faites par une utilisateur ne doivent être pas en conflit avec les modifications des autres utilisateurs. Le but de uniformité de la lecture est d’assurer une concordance et synchronisation des opérations fait par différents utilisateurs : les lecteurs ne doivent pas atteindre pour les opérations faites par des ecriteures et en même temps les ecriteures ne doivent pas atteindre pour les lecteurs. En effet, chaque utilisateur voit les données comme a le dernier COMMIT, avant le commencement d’une opération DML (les données sont retenues dans un segment spécial) . Quand les opérations écriture dans la base de données sont en cours de déroulement, ORACLE fait une copie de données avant la modification. Toutes les lecteurs, en exceptant ceux qui on fait la modification, voient la base de données qui existait auparavant (une lecture uniforme, sans les modifications en cours de déroulement). exécution d’un bloque DML fait visible pour toutes les utilisateurs (qui fait un SELECT) les modifications dans la base de données En ce moment la, le segment est elibere et peut être réutilise. Si la transaction est ROLLBACK, toutes les modifications sont annulées : la version retenue des données est placer de nouveau dans le tableau et toutes les utilisateurs voit la base de données comme il était en avant le début de la transaction.
Le blocage en ORACLE est utilise pour contrôler l’accès concurrent aux données ; les blocages sont des mécanismes qui prévient l’interaction destructive entre les utilisateurs qui accessent les mêmes données et sont faites automatiquement, sans aucune requête de l’utilisateur. Les blocages implicites sont faites pour des déclarations SQL si’l est nécessaire ( la seule déclaration pour laquelle le blocage n’est pas effectue est SELECT ). Les utilisateurs peuvent faire aussi des blocages explicitement. Les principaux buts de blocages sont la concurrence des données et uniformité de lecture.
La concurrence de données :
uniformité de la lecture
Les blocages implicites sont de trois types : exclusive (fait le blocage des autres utilisateurs), share(partage permet accès pour les autres utilisateurs ) et le blocage de type DDL.Une blocage exclusive est fait automatiquement pour chaque ligne modifiee par une opération DML, en évitant qu’une ligne soumis a une transaction qui n’est pas finie (commit ou rollback) soit soumis a une autre transaction. en effet le blocage exclusive assure le fait que deux ou plusieurs utilisateurs ne peuvent pas modifier la même ligne en même temps. Une blocage de type partage est fait automatiquement au niveau table pendant les opérations DML ; en utilisant ce type de blocage, plusieurs transactions peuvent faire des blocages sur les mêmes ressources. Le blocage DDL est réalise a la modification d’un objet de la base de données (comme le table).
Toutes le blocages accumules pendant une transaction st eliberees au moment le transaction est finie, toutes les blocages accumules après un savepoints sont eliberees au moment la transaction est retournée au savepoints.