Dans certains cas, nous avons besoin des curseurs pour boucler sur une information afin de faire un traitement en masse.
Voici 2 cas ou j’ai eu besoin d’utiliser des curseurs avec SQL server:
CAS 1: je devais, pour répondre aux besoins d’une présentation, simuler des données. A partir d’une table ou j’avais des utilisateurs, j’ai alimenté une table répertoriant des connexions. Le but étant de voir comment les utilisateurs se connectent à une plateforme.
DECLARE db_cursor CURSOR
FOR SELECT id, userid FROM moodle_mdl_log
WHERE userid = 0 and module = ‘login’
DECLARE @id int
DECLARE @userid int
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id, @userid
WHILE @@FETCH_STATUS = 0
BEGIN
/*PRINT @id*/
UPDATE moodle_mdl_log
SET userid = (SELECT TOP 1 id FROM dbo.randuser order by NEWID())
WHERE CURRENT of db_cursor
FETCH NEXT FROM db_cursor INTO @id, @userid
END
CLOSE db_cursor
DEALLOCATE db_cursor
CAS 2 : A un instant t du projet, la base de production a été copiée en recette : des données de production se sont donc retrouvées en recette. Les utilisateurs m’ont alors demandés de tout vider et de tout recharger. Pour vider les tables en masse j’ai donc utilisé un cureur.
DECLARE db_cursor CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE Table_Type=’BASE TABLE’ and TABLE_NAME NOT IN (‘xxx’,'yyy’) and TABLE_NAME NOT LIKE (‘Tmp%’)
DECLARE @table varchar(50)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
/*PRINT @id*/
exec(‘TRUNCATE TABLE ‘ + @table)
FETCH NEXT FROM db_cursor INTO @table
END
CLOSE db_cursor
DEALLOCATE db_cursor
Rien d’extraordinaire pour ce post mais ça pourra peut être servir…
29 avril 2015 à 8 h 12 min
Bonjour,
Votre article est intéressant mais je pense que vous pouvez optimiser votre requête N°2.
Vous videz les tables sans tenir compte des contraintes FOREIGN KEY. Si votre script tente de vider une table référencée par une autre qui n’est pas encore vide alors votre script plante. Voila l’erreur obtenu après avoir exécuter votre script :
« Impossible de tronquer la table ‘XXXX’ parce qu’elle est actuellement référencée par une contrainte FOREIGN KEY. »
La bonne marche à suivre est de désactiver les FK, de supprimer vos lignes puis de réactiver vos FK.
Vous pouvez également vous passer de ce curseur :
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N »;
BEGIN TRANSACTION;
BEGIN TRY
— désactivation des contraintes d’intégrité référentielles pour les clefs étrangères
SELECT @SQL = @SQL + N’ALTER TABLE [' + TABLE_SCHEMA + N'].[' + TABLE_NAME + N'] ‘
+ N’ NOCHECK CONSTRAINT [' + CONSTRAINT_NAME + N'];’
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = N’FOREIGN KEY’;
EXEC (@SQL);
SET @SQL = N »;
— vidage des tables
SELECT @SQL = @SQL + ‘DELETE FROM [' + TABLE_SCHEMA + N'].[' + TABLE_NAME + N'];’
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’;
EXEC (@SQL);
— réactivation des contraintes d’intégrité référentielles pour les clefs étrangères
SELECT @SQL = @SQL + N’ALTER TABLE [' + TABLE_SCHEMA + N'].[' + TABLE_NAME + N'] WITH CHECK’
+ N’ CHECK CONSTRAINT [' + CONSTRAINT_NAME + N'];’
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = N’FOREIGN KEY’;
EXEC (@SQL);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK;
SELECT ERROR_MESSAGE()
END CATCH;
Dans tous les cas, merci pour le partage !
29 avril 2015 à 8 h 46 min
Bonjour Yohann,
Merci pour ces précisions. J’utiliserai ton astuce à l’avenir.
A bientôt