I. Remplacer un curseur par une requête▲
Voici un cas classique : comment réaliser un cumul de valeurs ligne à ligne. Donnons-nous un jeu d'essai simple, mais coriace. Celui d'une partie de scrabble avec trois joueurs, dont la table des scores est réalisée comme suit :
CREATE TABLE T_SCORE_SCR
(SCR_TOUR INT NOT NULL,
JOR_ID INT NOT NULL,
SCR_SCORE INT NOT NULL,
CONSTRAINT PK_SCR PRIMARY KEY (SCR_TOUR, JOR_ID))
INSERT INTO T_SCORE_SCR VALUES (1, 1, 32)
INSERT INTO T_SCORE_SCR VALUES (1, 2, 19)
INSERT INTO T_SCORE_SCR VALUES (1, 3, 27)
INSERT INTO T_SCORE_SCR VALUES (2, 1, 85)
INSERT INTO T_SCORE_SCR VALUES (2, 2, 18)
INSERT INTO T_SCORE_SCR VALUES (2, 3, 21)
INSERT INTO T_SCORE_SCR VALUES (3, 1, 41)
INSERT INTO T_SCORE_SCR VALUES (3, 2, 76)
INSERT INTO T_SCORE_SCR VALUES (3, 3, 27)Pour réaliser le cumul, il vient à l'esprit d'ordonner le jeu de résultats dans l'ordre des tours et des participants et de prévoir autant de compteurs que de joueurs (au scrabble, la limite est de quatre) :
CREATE TABLE #CUMULS (SCR_TOUR INT, JOR_ID INT, SCR_SCORE INT)
DECLARE C_SCORE CURSOR
FOR
SELECT SCR_TOUR, JOR_ID, SCR_SCORE
FROM T_SCORE_SCR
ORDER BY SCR_TOUR, JOR_ID
OPEN C_SCORE
DECLARE @T INT, @J INT, @S INT, @J1 INT, @J2 INT, @J3 INT
SELECT @J1 = 0, @J2 = 0, @J3 = 0
FETCH C_SCORE INTO @T, @J, @S
WHILE @@FETCH_STATUS = 0
BEGIN
IF @J = 1
BEGIN
SET @J1 = @J1 + @S
INSERT INTO #CUMULS VALUES (@T, @J, @J1)
END
IF @J = 2
BEGIN
SET @J2 = @J2 + @S
INSERT INTO #CUMULS VALUES (@T, @J, @J2)
END
IF @J = 3
BEGIN
SET @J3 = @J3 + @S
INSERT INTO #CUMULS VALUES (@T, @J, @J3)
END
FETCH C_SCORE INTO @T, @J, @S
END
CLOSE C_SCORE
DEALLOCATE C_SCORE
SELECT * FROM #CUMULS ORDER BY 1Il est parfaitement possible de remplacer un tel traitement à l'aide d'une requête SQL tout à fait classique. Voici une telle requête :
SELECT T1.SCR_TOUR, T1.JOR_ID, SUM(T2.SCR_SCORE) CUMUL
FROM T_SCORE_SCR T1
LEFT OUTER JOIN T_SCORE_SCR T2
ON T1.SCR_TOUR >= T2.SCR_TOUR
AND T1.JOR_ID = T2.JOR_ID
GROUP BY T1.SCR_TOUR, T1.JOR_ID
ORDER BY T1.SCR_TOUR, T1.JOR_ID
SCR_TOUR JOR_ID CUMUL
----------- ----------- -----------
1 1 32
1 2 19
1 3 27
2 1 117
2 2 37
2 3 48
3 1 158
3 2 113
3 3 75Elle propose de joindre la table sur elle-même par une inéqui-jointure afin de faire le cumul.
Pensez donc à utiliser toutes les techniques de requêtes, comme les jointures non équi, les sous-requêtes et particulièrement celles corrélées. Une requête, même très complexe sera la plupart du temps plus efficace qu'un traitement itératif à l'aide d'une boucle par curseur. Cela est dû à l'optimiseur.
II. Remplacer un curseur par une requête avec paramètre▲
Avec les données suivantes :
La table T_JOUEUR est composée de la sorte :
CREATE TABLE T_JOUEUR_JOR
(JOR_ID INT NOT NULL PRIMARY KEY,
JOR_PRENOM VARCHAR(12))
INSERT INTO T_JOUEUR_JOR VALUES (1, 'Marcel')
INSERT INTO T_JOUEUR_JOR VALUES (2, 'Gérard')
INSERT INTO T_JOUEUR_JOR VALUES (3, 'Victor')
INSERT INTO T_JOUEUR_JOR VALUES (4, NULL)
INSERT INTO T_JOUEUR_JOR VALUES (5, 'Eugène')
INSERT INTO T_JOUEUR_JOR VALUES (6, 'Didier')… nous aimerions obtenir la liste concaténée des prénoms, par exemple : Marcel, Gérard, Victor, Eugène, Didier.
La solution classique utilisant un curseur est la suivante :
DECLARE C_PRENOM CURSOR
FOR
SELECT JOR_PRENOM
FROM T_JOUEUR_JOR
OPEN C_PRENOM
DECLARE @UN_PRENOM VARCHAR(12)
DECLARE @ALL_PRENOMS VARCHAR(8000)
SET @ALL_PRENOMS = ''
FETCH C_PRENOM INTO @UN_PRENOM
WHILE @@FETCH_STATUS = 0
BEGIN
IF @UN_PRENOM IS NOT NULL
SET @ALL_PRENOMS = @ALL_PRENOMS + @UN_PRENOM + ', '
FETCH C_PRENOM INTO @UN_PRENOM
END
CLOSE C_PRENOM
DEALLOCATE C_PRENOM
IF @ALL_PRENOMS IS NOT NULL
SET @ALL_PRENOMS = SUBSTRING(@ALL_PRENOMS, 1, LEN(@ALL_PRENOMS) - 1)
SELECT @ALL_PRENOMSMais il est parfaitement possible de ne pas utiliser de curseur pour obtenir un tel résultat. En effet, il suffit de se rappeler qu'un simple SELECT parcourt toutes les lignes de la table. Dès lors, on peut utiliser un SELECT d'affectation avec une variable pour empiler les prénoms à la queue leu leu. Voici un tel code :
-- déclaration de la variable d'empilement des prénoms
DECLARE @ALL_PRENOMS VARCHAR(8000)
-- assignation à blanc de cette variable
SET @ALL_PRENOMS = ''
-- concatène la variable avec un nouveau prénom pour chaque ligne lue en évitant les NULL
SELECT @ALL_PRENOMS = @ALL_PRENOMS + COALESCE(JOR_PRENOM + ', ', '')
FROM T_JOUEUR_JOR
-- supprime la virgule finale parasite si la chaîne est assignée
IF @ALL_PRENOMS IS NOT NULL
SET @ALL_PRENOMS = SUBSTRING(@ALL_PRENOMS, 1, LEN(@ALL_PRENOMS) - 1)
-- restitue l'information
SELECT @ALL_PRENOMS AS PRENOMS
PRENOMS
-----------------------------------------------------------
Marcel, Gérard, Victor, Eugène, Didier
(1 ligne(s) affectée(s))Les éléments importants à prendre en considération sont :
- la préassignation de la variable à une chaine vide, sinon, comme toute variable encore non assignée, elle est marquée à NULL et la concaténation de NULL avec n'importe quoi, donne toujours NULL ;
- l'utilisation du COALESCE afin d'éviter la rencontre d'une occurrence marquée à NULL dans la colonne JOR_PRENOM.
III. Remplacer un curseur par un jeu de requêtes avec table temporaire▲
Nous allons maintenant nous intéresser à réaliser une procédure permettant de recalculer toutes statistiques de toutes les tables de la base de données. Une telle procédure nécessite l'appel de la commande UPDATE STATISTICS qui ne peut se faire que pour une table donnée, d'où le recours à un curseur :
DECLARE C_STATS CURSOR
FOR
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN C_STATS
DECLARE @TABLE VARCHAR(128), @SCHEMA VARCHAR(128)
DECLARE @SQL VARCHAR(8000)
FETCH C_STATS INTO @TABLE, @SCHEMA
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE STATISTICS ' + @TABLE + '.' + @SCHEMA
EXEC (@SQL)
FETCH C_STATS INTO @TABLE, @SCHEMA
END
CLOSE C_STATS
DEALLOCATE C_STATSPour remplacer un tel code par une procédure sans curseur, il faut utiliser une astuce qui consiste à créer une table temporaire dans laquelle nous allons puiser un élément à travailler à l'aide de la clause spécifique à SQL serve : TOP 1. Lorsque cet élément aura été travaillé en lançant une requête dynamique, nous marquerons que le travail a été effectué en donnant une valeur particulière à une colonne spécialement établie à cet effet lors de la création de la table temporaire.
À partir de cet instant, cette ligne va disparaitre du jeu appelé par le SELECT TOP 1. Petit à petit tout sera traité !
-- création et remplissage de la table temporaire de travail
-- notez l'ajout de la colonne OK préassignée à la valeur zéro
SELECT TABLE_NAME, 0 AS OK
INTO #UPDSTAT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE @SQL VARCHAR(8000)
DECLARE @TBL VARCHAR(128)
-- tant qu'il y a au moins une table à traiter
WHILE EXISTS(SELECT *
FROM #UPDSTAT
WHERE OK = 0)
BEGIN
-- prendre le nom de la première table à traiter
SET @TBL = (SELECT TOP 1 TABLE_NAME
FROM #UPDSTAT WHERE OK = 0
ORDER BY 1)
-- construire le texte du traitement
SET @SQL = 'UPDATE STATISTICS [' + @TBL + ']'
-- exécuter le traitement
EXEC (@SQL)
-- flaguer l'objet traité dans la table des objets à traiter
UPDATE #UPDSTAT
SET OK = 1
WHERE TABLE_NAME = @TBL
END
-- supprimer la table temporaire de traitement
DROP TABLE #UPDSTATIV. Procédures non documentées▲
MS SQL Server, est doté depuis la version 6.5 de deux procédures non documentées permettant de réaliser un traitement pour chaque base de données et pour chaque table dans une base donnée. Ces procédures ont pour nom : sp_MSforeachdb et sp_MSforeachtable (attention, respectez bien la casse exacte avant du nom des procédures).
Ces deux procédures acceptent en paramètre une chaîne de caractères qui sera exécutée pour chaque base ou pour chaque table. En bonus, la présence du signe « ? » (point d'interrogation) dans la chaîne de caractères sera remplacée par le nom de la base ou de la table. Attention cependant, dans le cas du nom de la table, ce dernier est toujours composé des deux parties nom_propriétaire.nom_table. Si vous ne désirez obtenir que la partie nom de table, il faudra utiliser la fonction SUBSTRING combiné à la fonction CHARINDEX, de cette manière :
SUBSTRING(''?'', CHARINDEX(''.'', ''?'') + 2, LEN(''?'') - CHARINDEX(''.'', ''?'') - 2Voici deux exemples d'utilisation de ces fonctions :
-- affichage du nombre de tables de chaque base :
sp_MSforeachdb 'SELECT COUNT(*) AS "?" FROM ?.INFORMATION_SCHEMA.TABLES'
Northwind
-----------
31
master
-----------
37
model
-----------
2
msdb
-----------
82
pubs
-----------
14
tempdb
-----------
3
-- affichage du nombre de colonnes de chaque table dans une base donnée :
sp_MSforeachtable
'SELECT COUNT(*) AS [?] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
SUBSTRING(''?'', CHARINDEX(''.'', ''?'') + 2, LEN(''?'') - CHARINDEX(''.'', ''?'') - 2)'
[dbo].[Orders]
--------------
14
[dbo].[Products]
----------------
10
[dbo].[Order Details]
---------------------
5
[dbo].[CustomerCustomerDemo]
----------------------------
2
[dbo].[CustomerDemographics]
----------------------------
2
[dbo].[Region]
--------------
2
[dbo].[Territories]
-------------------
3
[dbo].[EmployeeTerritories]
---------------------------
2
[dbo].[Employees]
-----------------
18
[dbo].[Categories]
------------------
4
[dbo].[Customers]
-----------------
11
[dbo].[Shippers]
----------------
3
[dbo].[Suppliers]
-----------------
12(La procédure a été exécutée dans la base exemple Northwind.)
Attention cependant, qui dit procédure non documentée, dit portabilité suspecte, donc précautions. N'utilisez ces procédures, que dans un cadre léger et souple, par exemple une maintenance susceptible d'évoluer, et testez la non-régression à chaque patch comme à chaque nouvelle version de MS SQL Server.
Remerciements à Webman pour la relecture orthographique de cet article, ainsi qu'aux membres de la rubrique SGBD.