[SQL Server] Saga transact-SQL Episode 7 : Modification de données
On va expliquer ici comment fonctionnent les transactions et comment écrire des instructions Insert, Update et Delete pour modifier les données dans les tables.
Utilisation des transactions
Une transaction est une séquence d’opérations exécutée en tant qu’unité logique de travail unique. Le programmeur doit définir la séquence de modifications des données de manière à laisser ces dernières dans un état de cohérence conforme aux règles d’activité de l’entreprise.
Sql server vous permet de démarrer des transactions selon 3 modes : explicite, auto validé ou implicite.
Les transactions explicite commencent par une instruction « Begin Transaction » et se finissent par un « Commit Transaction » ou un « Rollback Transaction ».
Les transactions auto validées constituent le mode d’exploitation par défaut. Chaque instruction transact distincte est validée lors de son exécution. Il n’est pas nécessaire de spécifier d’instruction pour contrôler les transactions.
Les transactions du mode implicite sont définies par l’instruction « Set implicit_transaction on ». Dans ce mode, l’instruction suivante démarre automatiquement une nouvelle transaction. Une fois cette transaction terminée, l’instruction transact suivante en démarre une nouvelle. Il faudra penser à remettre « Set implicit_transaction off » pour mettre fin à ce mode de fonctionnement sur la session.
Le mode de transactionnel est paramétré par session. Si une session donnée change de mode de transaction, ce changement n’a aucun effet sur le mode de transaction de cette session.
L’instruction « commit » ou « rollback » vous permet de mettre fin à une transaction.
Commit indique que la transaction s’est bien passé et que sql doit valider ses transformations, commit garantie que l’ensemble des modifications apportées par la transaction fait en permanence partie intégrante de la base de données. Par ailleurs une instruction « commit » libère les ressources utilisées par une transaction par exemple les verrous.
L’instruction Rollback permet d’annuler une transaction. Elle annule toute les modifications apportées au cours de la transaction en ramenant les données à l’état dans lequel elles étaient avant la transaction. Lorsqu’une erreur se produit au sein d’une transaction, sql applique automatiquement une instruction « rollback » à la transaction en cours.
Exemple : une transaction qui vire 100 du compte épargne pour le déposer sur le compte courant. Si une erreur survient on annule tout (rollback) sinon on valide tout (commit).
Begin Transaction
Update table_compte_epargne
set solde=solde - 100
where Idclient=12345
if @@Error<>0
Begin
Raiserror ('Erreur, la transaction a échouée ! ',16,-1)
rollback Transaction
end
Update table_compte_courant
set solde=solde + 100
where Idclient=12345
if @@Error<>0
Begin
Raiserror ('Erreur, la transaction a échouée ! ',16,-1)
rollback Transaction
end
Commit transaction
Insertion des données
On va insérer des données par le biais d’une transaction en spécifiant un ensemble de valeurs ou en insérant les résultats d’une instruction select. On peut créer une table et insérer directement des données. Il n’est pas nécessaire d’insérer des valeurs dans la totalité des champs présents sur une ligne.
Insertion d’une ligne de données par valeur
On doit se conformer aux contraintes de la table de destination sinon l’instruction échouera.
On doit donner la liste des colonnes qui contiendront les valeurs désirées et lister ces valeurs entre parenthèse après la clause VALUES dans le même ordre que les colonnes spécifiées.
INSERT INTO [AdventureWorks].[Person].[Contact]
([Title],[FirstName],[LastName]
,[Suffix],[EmailAddress],[EmailPromotion])
VALUES
('chef','monprenom','monnom'
,'MR' ,'toto@titi.fr',8)
Utilisation de l’instruction Insert … select
L’instruction insert…select permet d’ajouter des lignes dans une table en insérant l’ensemble de résultats d’une instruction select.
Bien sûr il faut au préalable que la table de destination existe et que les types des colonnes correspondent aux types des valeurs retournées.
insert into customers
select nom, prenom, addresse
from employees
Création d’une table à l’aide de l’instruction select into
L’instruction select into permet de placer l’ensemble de résultats d’une requête dans une nouvelle table. Il faut prendre le nom d’une table qui n’existe pas sinon l’instruction échoue.
Vous pouvez créer une table temporaire locale (faire précéder le nom de la table de #, cette table ne sera visible que de la session en cours) ou globale (faire précéder le nom de la table de ##, cette table sera visible de toutes les session). Vous pouvez aussi créer une table permanente auquel cas il faudra que l’option de base de données select into/bulkcopy soit activé.
Exemple : table permanente
select FirstName,LastName,EmailAddress
into dbo.thetable
from Person.Contact
Exemple : table temporaire locale
select FirstName,LastName,EmailAddress
into dbo.#thetable
from Person.Contact
Exemple : table temporaire globale
select FirstName,LastName,EmailAddress
into dbo.##thetable
from Person.Contact
Insertion de données partielles
Si une colonne a une valeur par défaut ou accepte les valeurs NULL, vous pouvez l’exclure de l’instruction insert. Sql insère automatiquement les valeurs. Donc lors d’une insertion partielle on ne va énumérer que les colonnes pour lesquelles on a des valeurs à fournir. Evitez d’insérer une valeur NULL.
Exemple : on insère une valeur dans une colonne qui n’accepte pas les null et on laisse le reste à null
Ceci est peu utilisé.
Insertion de données à l’aide de valeur par défaut des colonnes
Lorsque vous insérez des lignes dans une table, vous pouvez gagner du temps lors de la saisie des valeurs en utilisant les mots clés « Default » ou « Default Values » avec l’instruction insert. Bien sûr il faut que la table sur laquelle vous voulez insérer des valeurs qui définissent des valeurs par défaut pour les colonnes sur lesquelles vous voulez utiliser cette fonctionnalité.
Exemple : on a une table qui contient une valeur par défaut sur la colonne « sAdrPerso1 » donc si on ne rentre rien dans une cellule de cette table sql la remplie avec la valeur « inconnue ».
Suppression de données
L’instruction delete supprime une ou plusieurs lignes dans une table.
DELETE from matable where mes conditions
Attention delete supprimera toutes les lignes d’une table sauf si vous utilisez une clause WHERE.
L’instruction « Truncate table » supprime toutes les données présentes dans une table.
TRUNCATE TABLE base_données.propriétaire.table
Exemple : truncate table orders
L’instruction trancate table s’exécute plus vite que l’instruction delete et lorsqu’une table dispose d’une colonne IDENTITY, elle la réinitialise à sa valeur de départ (ce que ne fait pas l’instruction delete).
Suppression de données en fonction des données contenues dans d’autres tables
Utilisez l’instruction delete conjointement à des jointures ou des sous-requêtes pour supprimer les lignes d’une table à l’aide de données contenues dans d’autres tables. Ce procédé est plus efficace que d’écrire plusieurs instructions delete d’une seule ligne.
Exemple : on veut supprimer toutes les lignes de la table détail de commande (
SalesOrderDetail)
Pour les lignes qui correspondent à des commandes passées le 16/01/2008.
delete from Sales.SalesOrderDetail
from Sales.SalesOrderHeader as entete
inner join Sales.SalesOrderDetail as detail
on entete.SalesOrderID=detail.SalesOrderID
where OrderDate='16/01/2008'
Autre façon d’obtenir le même résultat
delete from Sales.SalesOrderDetail
where SalesOrderID in (
select SalesOrderID
from Sales.SalesOrderHeader
where OrderDate='16/01/2008')
Mise à jour de données contenues dans une table
L’instruction update modifie des données existantes.
On va utiliser l’instruction update pour mettre à jour une ligne ou un ensemble de ligne ou toutes les lignes d’une table en fonction de la clause where qu’on lui associera.
On spécifie la ou les colonnes à modifier à l’aide le la clause SET. Bien sûr il faudra que les nouvelles valeurs correspondent aux types des colonnes.
Sql ne mettra pas à jour les lignes qui ne respectent pas les contraintes d’intégrité et l’instruction sera annulée.
On ne peut modifier qu’une seule table à la fois.
Exemple :
update Sales.SalesOrderDetail
set OrderQty=12,ModifiedDate=getdate()
where SalesOrderID=43659 and SalesOrderDetailID=3
Mise à jour de ligne en fonction des données contenues dans d’autres tables
Utilisez l’instruction update avec l’instruction from pour modifier une table en fonction des valeurs contenues dans d’autres tables.
Exemple : augmenter les prix de 2 dans les bons de commandes pour les produite qui coutent moins de 100 (oui je sais c’est mal !!)
update Sales.SalesOrderDetail
set UnitPrice=UnitPrice + 2
from Production.Product
inner join Sales.SalesOrderDetail
on Production.Product.ProductID=Sales.SalesOrderDetail.ProductID
where Production.Product.StandardCost<100
Exemple : même résultat mais sans inner join mais avec une sous requête
update Sales.SalesOrderDetail
set UnitPrice=UnitPrice + 2
where ProductID in (
select ProductID
from Production.Product
where StandardCost<100)