Structured Query Language
Modification de Données SQL
Certes, avant de sélectionner les données il faut pouvoir en ajouter dans une base a priori vide. Mais
avant d’apprendre `a modifier les données en SQL il faut savoir les sélectionner. C’est pourquoi nous
n’abordons que maintenant les requˆetes d’insertion, de suppression et de mise-`a-jour.
Il est sous-entendu ici que l’on a les droits de modification nécessaires sur les tables concernées. Par
ailleurs, il est conseillé d’inclure toutes les opérations de modifications des données dans une transaction,
non seulement parce que ces opérations peuvent échouer partiellement, mais aussi afin qu’elles figurent
au journal.
===== Insertion =====
En SQL on ne peut insérer des lignes que dans une table `a la fois. On peut ajouter :
– des données complètes (on précise alors toutes les colonnes)
Exemple :
<code>
1 BEGIN TRAN
2 INSERT clients -- LA table
3 VALUES (16, ’Razibus’, 3000000) -- toutes les colonnes et dans l’ordre
4 COMMIT TRAN
</code>
Remarque : on ne peut mettre qu’un VALUES par INSERT, mais plusieurs INSERT par transaction
– des données partielles (on ne précise que certaines colonnes)
<code>
Exemple :
1 BEGIN TRAN
2 INSERT clients(clt_nom, clt_num) -- l’ordre n’a pas d’importance
3 VALUES (’Fricotin’, 18) -- tant qu’il est le meme ici
4 COMMIT TRAN
</code>
Remarques : il est obligatoire d’insérer des valeurs
* compatibles avec le type de la colonne
* dans toutes les colonnes déclarées NOT NULL et qui n’ont pas de valeur par défaut
* des données issues d’une sélection (on introduit plusieurs lignes `a la fois)
Exemple : supposons que l’on dispose d’une table clients importants qui n’ai que la colonne
clt num
<code>
1 BEGIN TRAN
2 INSERT clients_importants(clt_num)
3 SELECT TOP 100 clt_num
4 FROM clients
5 ORDER BY clt_ca DESC
6 COMMIT TRAN
</code>
– dans une table temporaire (aucune clé primaire ni étrangère, donc aucune relation avec le schéma
relationnel)
Exemple : si la table clients importants n’existe pas encore
''1 SELECT TOP 100 clt_num
2 INTO clients_importants
3 FROM clients
4 ORDER BY clt_ca DESC''
Remarques :
– la table temporaire contient alors les mˆemes colonnes que le résultat de la requˆete SELECT ;
– on ne peut pas utiliser SELECT ... INTO dans une transaction ;
– ne pas oublier le DROP TABLE une fois qu’on a plus besoin de la table temporaire.
===== Update avec Jointure =====
<code>
UPDATE
VALEUR_RUBRIQUE_PDC_VRP
SET
VRP_MNTP_AJ = data.MNTP_AJ,
VRP_MNTPSIGNE_AJ = data.MNTPSIGNE_AJ
FROM
(
SELECT
VRP.ENT_ID,
coalesce(VRP.VRP_MNTP + coalesce(AJU_ECR.AJU_MONTANT_TOTAL,0),0) as MNTP_AJ,
coalesce(VRP.VRP_MNTPSIGNE + coalesce(AJU_ECR.AJU_MONTANT_TOTAL,0),0) as MNTPSIGNE_AJ,
DRP.DRP_ID
FROM
VALEUR_RUBRIQUE_PDC_VRP AS VRP
INNER JOIN dbo.DESCRIPTION_RUBRIQUE_PDC_DRP AS DRP ON VRP.DRP_ID = DRP.DRP_ID
LEFT OUTER JOIN
(
SELECT
AJU.DRP_ID,
SUM(AJU.AJU_MONTANT) AS AJU_MONTANT_TOTAL
FROM
ajustement_aju AS AJU
INNER JOIN dbo.ecriture_ecr AS ECR ON AJU.ECR_ID = ECR.ecr_id
WHERE
(
(ECR.ecr_statut >= @STATUT and ECR.ECR_SAISIE_UT = @UT ) -- en cours ou validées POUR l'utilisateur UNIQUEMENT
OR
(ECR.ecr_statut >= 2) -- validées
)
AND
(AJU.DRP_ID IS NOT NULL)
AND
(AJU.AJU_SUPPORT = 0) -- support Rubrique
AND
ECR.ECR_ENT_ID = @ENT_ID
GROUP BY
AJU.DRP_ID
) AS AJU_ECR ON DRP.DRP_ID = AJU_ECR.DRP_ID
WHERE
VRP.ENT_ID = @ENT_ID
) as DATA
WHERE
VALEUR_RUBRIQUE_PDC_VRP.ENT_ID = @ENT_ID
and
DATA.drp_id = VALEUR_RUBRIQUE_PDC_VRP.drp_id
</code>
===== Suppression =====
A nouveau, on ne peut supprimer des lignes que dans une table `a la fois. La syntaxe pour effectuer
une requˆete de suppression est :
''DELETE table (la table dans laquelle on supprime)
FROM tables (les tables utilisées dans la clause WHERE)
WHERE conditions (les lignes à supprimer)''
Exemple : supprimer les petits clients
''1 BEGIN TRAN
2 DELETE clients
3 FROM clients
4 WHERE clt_ca < 1000
5 COMMIT TRAN''
Autre exemple : supprimer tous les clients (vider la table, et non pas, supprimer la table)
1 BEGIN TRAN
2 DELETE clients
3 COMMIT TRAN
Remarques :
* il est très dangereux d’oublier la clause WHERE dans un DELETE ;
* à cause de la clé étrangère dans la table commandes, on ne peut pas supprimer les clients qui ont
des commandes.
===== Mise a jour =====
Encore une fois, on ne peut changer les lignes que d’une table à la fois. La syntaxe pour effectuer une
requête de mise à jour est :
UPDATE table (la table dans laquelle met à jour)
SET colonne1 = ..., colonne2 = ... (les colonnes que l’on met à jour)
FROM tables (les tables de la clause WHERE)
WHERE conditions (les lignes à mettre à jour)
Exemple : pour convertir tous les prix en euros
1 BEGIN TRAN
2 UPDATE articles
3 SET art_prix = art_prix / 6.55957
4 COMMIT TRAN
Remarques :
* on ne peut pas mettre `a jour une colonne IDENTITY ;
* comme une division est moins coˆuteuse qu’une multiplication, il est préférable d’inverser une bonne
fois pour toute le taux de conversion et de ne plus effectuer que des multiplications :
1 DECLARE @taux REAL
2 SET @taux = 1.0 / 6.55957
3 BEGIN TRAN
4 UPDATE articles
5 SET art_prix = art_prix * taux
6 COMMIT TRAN
– il faut se méfier des mises-à-jour corrélées, puisque la requête suivante ne fait pas se que l’on pense :
1 BEGIN TRAN
2 UPDATE articles
3 SET art_prix = art_prix * taux, art_prixTTC = art_prix * 1.196
4 /* malheureusement le art_prix utilise pour art_prixTTC
5 n’est pas celui qui vient d’etre mis-a-jour */
6 COMMIT TRAN
il faut la remplacer par :
1 BEGIN TRAN
2 UPDATE articles
3 SET art_prix = art_prix * taux
4
5 UPDATE articles
6 SET art_prixTTC = art_prix * 1.196
7 COMMIT TRAN
Curseur
* [[http://msdn.microsoft.com/fr-fr/library/ms180169.aspx|Declare Cursor sur la MSDN]]
DECLARE @ITEM_ID INT
DECLARE curseurTousLesItems CURSOR
FOR
SELECT ITEM_ID FROM ITEMS
OPEN curseurTousLesItems
FETCH NEXT FROM aju_curseurTousLesItems
INTO @ITEM_ID
WHILE @@FETCH_STATUS = 0
BEGIN
--Do WORK HERE
FETCH NEXT FROM curseurTousLesItems
INTO @ITEM_ID
END
CLOSE curseurTousLesItems
DEALLOCATE curseurTousLesItems
Vues
Une vue est une requêe SELECT `a laquelle on donne un nom et dont on peut se servir comme s’il
s’agissait d’une table. C¸ a n’est pas si surprenant puisque l’on peut voir une requˆete SELECT comme une
fonction (au sens informatique du terme) qui retourne une table. Contrairement `a ce que l’on pourrait
croire, les vues ne conservent pas une copie séparée des données.
===== Syntaxe =====
Exemple de déclaration d’une vue : on désire ne garder qu’une sous table de la table commandes tout
en affichant le nom du client et de l’article au lieu de leur numéro.
1 CREATE VIEW VueCommandes -- nom de la vue
2 ([Nom du client], [Article commandé]) -- nom des colonnes (plus parlants)
3 AS
4 SELECT b.clt_nom, c.art_nom
5 FROM commandes AS a
6 JOIN clients AS b ON a.cmd_clt = b.clt_num
7 JOIN articles AS c ON a.cmd_art = c.art_num
Puis on peut l’utiliser comme une table :
1 SELECT [Nom du client]
2 FROM VueCommandes
3 WHERE [Article commandé] = ’pinceau’
Remarques sur la création des vues :
– la requˆete SELECT de la vue ne doit ni contenir de clause ORDER BY ni faire référence `a un table
temporaire (cf. §5.1 page 29) ni utiliser de sous-requˆete ;
– il est conseillé de tester au préalable la requˆete SELECT seule ;
– on peut créer une vue `a partir d’autres vues, mais pour des questions de performances il vaut mieux
éviter et en revenir aux tables sous-jacentes.
Pour modifier une vue :
1 ALTER VIEW VueCommandes
2 ( ... ) -- les colonnes
3 AS
4 ... -- nouvelle requete SELECT
Pour supprimer une vue :
1 DROP VIEW VueCommandes
===== Intérêts =====
Désormais les utilisateurs n’accéderont aux données qu’au travers des vues, seuls les développeurs
manipuleront directement les tables. C’est particulièrement avantageux car :
– on peut traduire les intitulés des colonnes en différentes langues et de manière plus explicite que la
nomenclature adoptée pour la base ;
– cela simplifie les requˆetes que les développeurs vont écrire pour les utilisateurs (le travail de jointure
est déj`a fait dans la vue, les noms sont plus parlants et les colonnes utiles uniquement aux
développeurs (clt num et art num par exemple) sont cachées) ;
– cela simplifie la sécurisation des données (les données sensibles – responsables de l’intégrité de la
base – sont masquées et il suffira de gérer les autorisations d’accès aux vues et non pas aux tables) ;
– et surtout on peut changer la structure de la base (les tables) sans avoir `a modifier la programmation
pour les utilisateurs (on changera éventuellement la programmation des vues mais pas celle
des requˆetes qui utilisent ces vues).
Illustration du dernier point : admettons que la table commandes soit scindée en deux tables commandes2001
et commandes2002. Seules les requˆetes qui utilisent la table commandes doivent ˆetre re-programmées.
<code>
1 ALTER VIEW VueCommandes
2 ([Nom du client], [Article commandé])
3 AS
4 SELECT b.clt_nom, c.art_nom
5 FROM commandes2001 AS a
6 JOIN clients AS b ON a.cmd_clt = b.clt_num
7 JOIN article AS c ON a.cmd_art = c.art_num
8 UNION
9 SELECT b.clt_nom, c.art_nom
10 FROM commandes2002 AS a
11 JOIN clients AS b ON a.cmd_clt = b.clt_num
12 JOIN article AS c ON a.cmd_art = c.art_num
</code>
Toutes les requˆetes qui utilisent les vues restent inchangées.
1 SELECT [Nom du client]
2 FROM VueCommandes
3 WHERE [Articles commandé] = ’pinceau’
Lorsqu’une base de données est déployée `a l’échelle d’une entreprise, le mécanisme des vues offre une
interface entre l’implémentation (les tables) et les utilisateurs qui permet au code SQL une plus grande
facilité de maintenance
===== Modification de données =====
Comme on vient de voir, la consultation des données `a travers une vue ne pose pas de problème. Le
problème essentiel avec les vues est la grande difficulté de modifier les données. En effet, plusieurs cas
pathologiques peuvent en effet se présenter :
– il se peut qu’une colonne déclarée NOT NULL ne soit pas visible `a travers la vue
exemple : comment ajouter une commande avec la vue VueCommandes alors que :
– la colonne cmd num est clé primaire donc NOT NULL
– les colonnes cmd clt et cmd art sont clés étrangères et NOT NULL
et ne figurent pas dans la vue ?
– et comment ajouter des données dans une vue mutli-tables ?
exemple : on voudrait par exemple ajouter automatiquement un nouveau client `a sa première commande.
Malheureusement, la requˆete suivante n’est pas autorisée :
1 BEGIN TRAN
2 INSERT VueCommandes
3 VALUES(’Fricotin’, ’Stylo’)
4 COMMIT TRAN
La solution consiste `a employer un déclencheur INSTEAD OF. Exemple :
1 CREATE TRIGGER VueCommandes_insert
2 ON VueCommandes INSTEAD OF INSERT
3 AS
4 BEGIN
5 -- j’insere d’abord les nouveaux clients dans la table clients
6 INSERT clients(clt_nom)
7 SELECT [Nom du client]
8 FROM inserted
9 WHERE [Nom du client] NOT IN (SELECT clt_nom FROM clients)
10
11 -- j’insere ensuite les commandes elles-memes
12 -- avec tous les renseignements necessaires
13 INSERT commandes(cmd_date, cmd_clt, cmd_art)
14 SELECT GETDATE(), b.clt_num, c.art_num
15 FROM inserted AS a
16 JOIN clients AS b ON a.[Nom du client] = b.clt_nom
17 JOIN articles AS c ON a.[Article commandé] = c.art_nom
18 END
Avec ce déclencheur, la requˆete d’insertion précédente fonctionne.
Exercice : pourquoi n’a-t-on pas eu besoin de préciser ni clt num dans le premier INSERT ni cmd num
dans le deuxième ?
Remarques :
– GETDATE() renvoie la date d’aujourd’hui ;
– on a fortement supposé dans ce déclencheur que les clients portaient un nom unique et que les
articles aussi, c’est pourquoi il vaut mieux respecter les conseils suivant lors de la création d’une
vue :
– s’arranger pour ne jamais avoir de doublons dans la vue (¸ca peut vouloir dire par exemple ajouter
une contrainte UNIQUE `a la colonne clt nom dans la table client ou inclure la clé primaire) ;
– toutes les colonnes NOT NULL que l’on écarte doivent pouvoir recevoir une valeur calculée (c’est
le cas de cmd date, cmd clt et cmd art) ou une valeur par défaut (c’est le cas de cmd num) 13 ;
– le seul déclencheur disponible pour les vues est INSTEAD OF (et pas AFTER contrairement aux tables) ;
– quand on insère dans une vue avec SQL Server, il faut malheureusement remplir toutes les colonnes
et on ne peut pas faire appel `a la valeur NULL.
13. bref, c’est difficile de cacher les clés primaires, les clés étrangères et plus généralement toutes les colonnes NOT NULL car
une vue dénormalise les données, ce qui représente un danger
Illustration de ce dernier point : on modifie la précédente vue, en lui ajoutant deux colonnes
<code>
1 ALTER VIEW VueCommandes
2 ([Numéro de commande], [Nom du client], [Article commandé], Date)
3 AS
4 SELECT a.cmd_num, b.clt_nom, c.art_nom, a.cmd_date
5 FROM commandes AS a
6 JOIN clients AS b ON a.cmd_clt = b.clt_num
7 JOIN articles AS c ON a.cmd_art = c.art_num
</code>
on veut insérer dans cette vue (en utilisant le mˆeme déclencheur) mais en laissant SQL Server calculer
le numéro de commande et la date de commande :
<code>
1 BEGIN TRAN
2 INSERT VueCommandes
3 VALUES(’’, ’Fricotin’, ’Stylo’, ’’)
4 -- on est oblige d’employer des valeurs bidons
5 COMMIT TRAN
</code>
Stored Procedure
====== Stored Procedures Compilations ======
As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes
Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.
Note
SQL Server 2008 features statement-level recompilation of stored procedures. When SQL Server 2008 recompiles stored procedures, only the statement that caused the recompilation is compiled, rather than the entire procedure. As a result, SQL Server uses the parameter values as they exist in the recompiled statement when regenerating a query plan. These values may differ from those that were originally passed into the procedure.
====== Forcing a Stored Procedure to Recompile ======
--------------------------------------------------------------------------------
SQL Server provides three ways to force a stored procedure to recompile:
* The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.
* Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled each time it is executed.
If you only want individual queries inside the stored procedure to be recompiled, rather than the entire stored procedure, specify the RECOMPILE query hint inside each query you want recompiled. This behavior mimics SQL Server's statement-level recompilation behavior noted above, but in addition to using the stored procedure's current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when compiling the statement. Use this option when atypical or temporary values are used in only a subset of queries belonging to the stored procedure. For more information, see Query Hint (Transact-SQL).
* You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.
Note
If an object referenced by a stored procedure is deleted or renamed, an error is returned when the stored procedure is executed. If, however, an object referenced in a stored procedure is replaced with an object of the same name, the stored procedure executes without having to be re-created.
The generally accepted wisdom about stored procedures (or sprocs) is that because SQL can optimize and compile them, they run more quickly than the equivalent SQL statements executed from Query Analyzer (or perhaps passed in from some front-end app such as a Web page or VB program). This is true to some extent.
First, you need to know what SQL Server does with a new sproc. At creation time, it checks the syntax. If it doesn't find any errors, then it adds the sproc to the system tables: sysobjects, sysdepends, and syscomments (the latter stores the body of the sproc). By default, it doesn't compile the sproc at creation time.
Upon first execution of the sproc, SQL Server optimizes and compiles it. This is when SQL Server devises a query plan and stores it in its procedure cache. On subsequent invocations, SQL looks in the cache first, finds the sproc there, and doesn't compile it. If the sproc isn't in the cache, then SQL Server compiles it and places it in the cache.
My experience with the WITH RECOMPILE option
A while back, I was supporting a search page that allowed its users to search by any of several columns. Then the page called a sproc, passing a parameter to indicate which column to search. I examined the parameter using a CASE block, and then executed one of several queries, depending upon the column to search.
I knew something was wrong when I began to test my allegedly clever stored procedure. In theory, the performance of each search should at least be approximately the same, but that isn't what happened. When I performed multiple searches, regardless of the order, the first would be fast, and subsequent searches were much slower.
Finally, I realized that the first time the procedure was called, a query plan was devised and stored in the cache. As long as I searched on that particular column, everything would work as expected. The moment I switched columns, however, performance plummeted. Why did this happen?
The first search I performed created a query plan and stored it in the cache. For instance, say I was searching on the column OrderDate. If I switched the search to the CompanyName column, SQL would blindly use the cached query plan, searching for the target company name using the OrderDate index. No wonder performance would plummet so dramatically.
The fix is quite simple. I executed the sproc supplying the WITH RECOMPILE option:
EXEC MySproc_Select '12/31/2004' WITH RECOMPILEThis tells SQL Server to throw away the existing query plan and build another one--but only this once.
You can also add the WITH RECOMPILE directly to the stored procedure right before the AS keyword. This tells SQL Server to throw out the query plan on every execution of the sproc.
There is also a third option. I could have created a separate sproc for each search method, and then decide which one to execute within the CASE block. That way, the query plan associated with the sub-sprocs remains in the cache, where SQL can take advantage of them. Since each of the sprocs searched exactly one column, there is no need to recompile.
SQL Server's ability to optimize and compile a stored procedure is great but, if you aren't careful, it can bite you when you least expect it. Now that you know how to deal with the problem, perhaps there are a few situations in your own database that you might want to revisit.
Commentaires
Enregistrer un commentaire