BI experience

Astuce curseur

| 2 Commentaires

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…

2 Commentaires

  1. 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 !

  2. Bonjour Yohann,

    Merci pour ces précisions. J’utiliserai ton astuce à l’avenir. :-)

    A bientôt

Laisser un commentaire

Champs Requis *.

*