Correction complète et détaillée
Examen Transact-SQL / SQL Server
Session de juin 2024
Introduction
Ce document fournit une correction pas-à-pas de lensemble des questions posées. Chaque
réponse est accompagnée dexplications permettant de comprendre la logique sous-jacente et les
bonnes pratiques associées.
1 Question 1 : Variables
1.1 Déclaration
Trois variables sont créées avec des types adaptés aux données attendues :
DECLARE @nomClient VARCHAR(255); -- Chane jusqu’ 255 caractres
DECLARE @idProduit INT; -- Identifiant numrique
DECLARE @prixTotal DECIMAL(10,2); -- Montant avec 2 dcimales
Remarque Le format DECIMAL(10,2) autorise 8 chiffres entiers et 2 après la virgule – large-
ment suffisant pour des montants e-commerce.
1.2 Affectation
SET @nomClient = ’Hamza␣Mourabit’;
SET @idProduit = 873;
SET @prixTotal = 1999.99;
Linstruction SET est préférée pour laffectation unitaire ; SELECT peut le faire mais est moins
explicite.
1.3 Affichage avec PRINT
PRINT ’Nom␣Client␣:␣’ + @nomClient +
’␣|␣Id␣Produit␣:␣’ + CAST(@idProduit AS VARCHAR(10)) +
’␣|␣Prix␣Total␣:␣’ + CAST(@prixTotal AS VARCHAR(10));
-- Variante moderne (SQL Server 2012+)
1
PRINT CONCAT(’Nom␣Client␣:␣’, @nomClient,
’␣|␣Id␣Produit␣:␣’, @idProduit,
’␣|␣Prix␣Total␣:␣’, FORMAT(@prixTotal, ’N2’));
Explications La concaténation classique utilise +. Depuis SQL Server 2012, CONCAT gère auto-
matiquement le cast des types non-chaîne. FORMAT applique ici laffichage à 2 décimales.
2 Question 2 : Procédure stockée
2.1 Création de sp_AfficherCommandesClient
Objectif : lister les commandes dun client, total détaillé inclus.
CREATE OR ALTER PROCEDURE dbo.sp_AfficherCommandesClient
@id_client INT
AS
BEGIN
SET NOCOUNT ON;
/* Chaque commande est enrichie d’un montant calcul
partir de DetailCommande pour montrer le total rel
(utile si la colonne total n’est pas jour).
*/
SELECT c.id_commande,
CONVERT(char(10), c.date_commande, 103) AS date_commande,
[Link] AS total_enregistre,
SUM([Link] * dc.prix_unitaire) AS total_detaille
FROM Commande AS c
JOIN DetailCommande AS dc ON dc.id_commande = c.id_commande
WHERE c.id_client = @id_client
GROUP BY c.id_commande, c.date_commande, [Link]
ORDER BY c.date_commande DESC;
END;
GO
Points clés
— CREATE OR ALTER évite lerreur si la procédure existe déjà.
— La clause GROUP BY est nécessaire pour agréger les lignes DetailCommande.
— SET NOCOUNT ON; supprime les messages « x rows affected » et améliore les performances
côté client.
2.2 Appel de la procédure
EXEC dbo.sp_AfficherCommandesClient @id_client = 1;
2
3 Question 3 : Fonction scalaire
3.1 Création de fn_TotalCommandesClient
CREATE OR ALTER FUNCTION dbo.fn_TotalCommandesClient (@id_client INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @total DECIMAL(10,2);
SELECT @total = COALESCE(SUM(total), 0)
FROM Commande
WHERE id_client = @id_client;
RETURN @total;
END;
GO
Explications
— COALESCE revient à 0 si aucune commande nexiste.
— La fonction est marquée déterministe ; elle se prête à la persistance dindex si besoin.
3.2 Test de la fonction
SELECT dbo.fn_TotalCommandesClient(1) AS TotalClient1;
4 Question 4 : Curseur
Parcourir les produits dont le stock < 10 et afficher leur nom + prix.
DECLARE @nom VARCHAR(255),
@prix DECIMAL(10,2);
DECLARE produit_cursor CURSOR FOR
SELECT nom, prix
FROM Produit
WHERE quantite_en_stock < 10;
OPEN produit_cursor;
FETCH NEXT FROM produit_cursor INTO @nom, @prix;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONCAT(’Produit:␣’, @nom,
’,␣Prix:␣’, CAST(@prix AS VARCHAR(10)), ’␣DIRHAM’);
3
FETCH NEXT FROM produit_cursor INTO @nom, @prix;
END
CLOSE produit_cursor;
DEALLOCATE produit_cursor;
GO
À retenir Les curseurs sont coûteux ; pour un simple affichage, un SELECT suffit. Ici cest péda-
gogique.
5 Question 5 : Trigger de mise à jour du stock
Lorsque de nouvelles lignes sont insérées dans DetailCommande, on diminue le stock.
CREATE OR ALTER TRIGGER trg_MiseAJourStock
ON DetailCommande
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
/*
L’instruction UPDATE en jonction vite les boucles explicites.
On part de ’inserted’ (pseudotable) pour cibler uniquement
les produits concerns par la commande en cours.
*/
UPDATE p
SET p.quantite_en_stock = p.quantite_en_stock - [Link]
FROM Produit AS p
JOIN inserted AS i ON i.id_produit = p.id_produit;
END;
GO
6 Question 6 : Gestion dexceptions avec THROW
6.1 Procédure sp_MettreAJourStock
CREATE OR ALTER PROCEDURE dbo.sp_MettreAJourStock
@id_produit INT,
@nouvelle_quantite INT
AS
BEGIN
SET NOCOUNT ON;
4
BEGIN TRY
/* 1) Vrifier la quantit propose */
IF @nouvelle_quantite < 0
THROW 50001, N’La␣quantit␣en␣stock␣ne␣peut␣pas␣tre␣ngative.’, 1;
/* 2) Vrifier l’existence du produit */
IF NOT EXISTS (SELECT 1 FROM Produit WHERE id_produit = @id_produit)
THROW 50002, N’Aucun␣produit␣trouv␣avec␣l’’ID␣spcifi␣pour␣la␣mise␣␣jour.’,
1;
/* 3) Mettre jour */
UPDATE Produit
SET quantite_en_stock = @nouvelle_quantite
WHERE id_produit = @id_produit;
END TRY
BEGIN CATCH
DECLARE @errMsg NVARCHAR(4000) = ERROR_MESSAGE(),
@errNum INT = ERROR_NUMBER();
PRINT CONCAT(’Erreur␣’, @errNum, ’␣:␣’, @errMsg);
-- Pour relayer l’exception au caller : THROW;
END CATCH
END;
GO
Décodage rapide
— THROW (SQL Server 2012+) remplace avantageusement RAISERROR ; il propage un nu-
méro, un message et un state.
— Dans le CATCH, ERROR_MESSAGE() et ERROR_NUMBER() offrent un diagnostic clair pour
lappelant.
Fin de la correction — bravo pour votre travail !
