Introduction au langage
Transact SQL (T-SQL)
P2: Structures Alternatives
& Procédures stockées
Pr. MELAOUENE Noussaiba
[email protected] Année universitaire 2023/2024
Plan du Cours
Introduction au langage Transact SQL (T-SQL)
Structure d’un programme T-SQL
Les variables et Leurs types
Structures de contrôle (alternatives)
Les procédures stockées
Les fonctions Système
Les curseurs
La gestion des exceptions
Les déclencheurs
La gestion des utilisateurs
Gestion des rôles
Gestion des privilèges
SQL SERVER T-SQL
III. Structures de contrôle
IF … ELSE
IF condition Remarque
<instruction>
• Permet de créer des branchements
IF condition
<instruction> • Conditionner l’exécution de certaines instructions
ELSE
<instruction>
• Pas de clause THEN
IF condition
BEGIN
• Une seule instruction permise sinon, si plusieurs
<instruction1>
<instruction2> instruction les délimiter par BEGIN et END
…….
END
ELSE IF Condition
• Pas de ; après le END d’avant le ELSE
BEGIN
<instruction1> • Pas de END IF ni de ELSIF
<instruction2>
…….
END
ELSE
BEGIN
<instruction1>
<instruction2>
……
END;
SQL SERVER T-SQL
III. Structures de contrôle
IF … ELSE: EXEMPLE
IF(SELECT sum(score) FROM films WHERE genre ="SF") < 5
DELETE FROM film WHERE genre ="SF"
DECLARE @sal char;
if @sal=‘A’
(select salairebrut from pilote where salairebrut between 35000 and 50000)
else if @sal=‘B’
(select salairebrut from pilote WHERE salairebrut<35000)
else
(select salairebrut from pilote where salairebrut<15000);
SQL SERVER T-SQL
III. Structures de contrôle
WHILE Continue
WHILE condition Remarque
<instruction>
WHILE condition • Pas de clause LOOP
BEGIN
<instruction1> • Une seule instruction sinon, si plusieurs
<instruction2> instruction les délimiter par BEGIN et END
…….
END; • Pas de END LOOP
WHILE condition
BEGIN
-- code exécuté
!
IF condition
CONTINUE;
-- le code est ignoré si la condition
--est vérifiée
END
SQL SERVER T-SQL
III. Structures de contrôle
WHILE : exemples
BEGIN BEGIN
DECLARE @compt INT = 0; DECLARE @compt INT = 0;
WHILE @compt < 5 WHILE @compt < 5
BEGIN BEGIN
SET @compt = @compt + 1; SET @compt = @compt + 1;
IF @compt = 3 IF @compt <> 3
CONTINUE; CONTINUE;
PRINT @compt; PRINT @compt;
END; END;
END; END;
Résultat Résultat
1 3
2
4
5
SQL SERVER T-SQL
III. Structures de contrôle
WHILE Break
WHILE condition EXEMPLE
BEGIN
-- code exécuté BEGIN
IF condition DECLARE @compt INT = 0;
BREAK;
-- la boucle est abondonnée!!! WHILE @compt < 5
END BEGIN
SET @compt = @compt + 1;
IF @compt= 3
Break;
! END;
END;
PRINT @compt;
Résultat
1
2
SQL SERVER T-SQL
III. Structures de contrôle
CASE: Syntaxe
CASE input_expression
WHEN when_expression
THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END;
SQL SERVER T-SQL
III. Structures de contrôle
CASE: Exemple
SELECT nom, prenom,
codep = CASE
WHEN 'inf' THEN 'Informatique’
WHEN 'tge' THEN 'Genie Ele’
WHEN 'ele' THEN 'Electronique’
ELSE 'Aucun Programme’
END,
salaire
FROM etudiants ;
SELECT Anciennete = CASE Id_Client
WHEN '3' THEN 'ANCIEN’
WHEN '2' THEN 'PAS SI VIEUX’
WHEN '1' THEN 'RECENT'
ELSE 'ON SAIT PAS TROP'
END,
Id_Client, Nom_Client FROM Client ORDER BY Anciennete
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
PROCÉDURES STOCKÉES SQL SERVER : DEFINITION
Une procédure stockée SQL Server regroupe une ou plusieurs instructions Transact-SQL
dans une unité logique et est stockée en tant qu'objet dans le serveur de base de
données.
PROCÉDURES STOCKÉES SQL SERVER : INTERET
Lors qu'une procédure stockée est appelée pour la première fois, SQL Server crée un
plan d'exécution et le stocke dans le cache du plan.
Lors des exécutions ultérieures de la procédure stockée, SQL Server réutilise le plan afin
que la procédure stockée puisse s'exécuter très rapidement avec des performances
fiables
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
PROCEDURES STOCKÉES SQL SERVER : SYNTAXE
-- Transact-SQL Syntax for Stored Procedures in SQL Server
CREATE [ OR ALTER ] { PROC | PROCEDURE }
procedure_name
[ ( @parameter data_type [ = default ] [OUTPUT] )
]
AS
BEGIN
corps_procedure
END ;
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
PROCEDURES STOCKÉES SQL SERVER : BONNES PRATIQUES
• Eviter le préfixe sp_ réservé aux procédures du système
• #procedure_name pour les procédures temporaires locales (visibles dans la connexion)
• ##procedure_name pour les procédures temporaires globales (visibles dans toutes les
connexions)
• Utilisez l'instruction SET NOCOUNT ON en tant que première instruction dans le corps de la
procédure. Autrement dit, placez-le juste après le mot clé AS. Cela désactive les messages
que SQL Server renvoie au client après l'exécution de toute instruction SELECT, INSERT,
UPDATE et DELETE.
• Évitez l'utilisation de select * et réduire la sélection aux attributs nécessaires
• Évitez de traiter ou de retourner trop de données
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
PROCÉDURES STOCKÉES SQL SERVER : EXEMPLE 1
CREATE PROCEDURE uspListeProduits
AS
BEGIN
SELECT
no,
nom,
!
prix_conseille
FROM
e_produit
ORDER BY
nom;
END;
REMARQUE
• Dans T-SQL une procédure stockée peut ne contenir qu’une simple requête SQL
• Il est à éviter
• Quand on peut faire quelque chose avec SQL faut pas utiliser T-SQL
EXECUTION
EXECUTE uspListeProduits OU EXEC uspListeProduits
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
MODIFIER UNE PROCÉDURES STOCKÉES : EXEMPLE 2
no nom prix_conseille
ALTER PROCEDURE uspListeProduits -- on fait un ordre par le prix
13 Carte réseau 100
AS 14 Carte graphique 400
BEGIN 10 Hub 1000
SELECT 12 Switch 1500
6 PC P4 10000
no, 1 Onduleur X400 11000
nom, 5 Onduleur X400 11050
prix_conseille 2 Onduleur X405 12000
FROM 3 Onduleur X415 15000
4 Onduleur X489 16000
e_produit 7 Macintosh 16000
ORDER BY 11 Retour 40000
prix_conseille; 8 Satation Sun 50000
END; 9 Station HP 60000
VALIDER LA MODIFICATION : on appuie sur le bouton executer
Commands completed successfully --message de SQL SERVER
EXECUTION
EXECUTE uspListeProduits OU EXEC uspListeProduits
DETRUIRE UNE PROCÉDURES STOCKÉES
DROP PROCEDURE uspListeProduits;
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
PROCÉDURES STOCKÉES : LES PARAMETRES : EXEMPLE 3
ALTER PROCEDURE uspListeProduits (@seuil_min_prix AS DECIMAL)
AS
BEGIN
SELECT
no,
nom,
prix_conseille
FROM
e_produit
WHERE no nom prix_conseille
prix_conseille>@seuil_min_prix 14 Carte graphique 400
10 Hub 1000
ORDER BY 12 Switch 1500
prix_conseille; 6 PC P4 10000
END; 1 Onduleur X400 11000
5 Onduleur X400 11050
2 Onduleur X405 12000
3 Onduleur X415 15000
EXECUTION AVEC DES PARAMETRES EFFECTIFS 4 Onduleur X489
7 Macintosh
16000
16000
11 Retour 40000
EXEC uspListeProduits 100; 8 Satation Sun 50000
9 Station HP 60000
EXEC uspListeProduits 1000;
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
PROCÉDURES STOCKÉES : AVEC PLUSIEURS PARAMETRES : EXEMPLE 4
ALTER PROCEDURE uspListeProduits (
@seuil_min_prix AS DECIMAL,
no nom prix_conseille
@seuil_max_prix AS DECIMAL 14 Carte graphique 400
)
AS
BEGIN
SELECT no, nom, prix_conseille
FROM e_produit
WHERE prix_conseille>@seuil_min_prix AND prix_conseille<@seuil_max_prix
ORDER BY prix_conseille;
END;
EXECUTION AVEC PLUSIEURS PARAMETRES EFFECTIFS
EXEC uspListeProduits 100, 1000;
EXECUTION AVEC DES PARAMETRES NOMMES
EXEC uspListeProduits @seuil_min_prix=100, @seuil_max_prix=1000;
EXEC uspListeProduits @seuil_max_prix=1000, @seuil_min_prix=100;
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
PROCÉDURES STOCKÉES : AVEC PARAMETRES CHAINES : EXEMPLE 5
ALTER PROCEDURE uspListeProduits (
@seuil_min_prix AS DECIMAL,
@seuil_max_prix AS DECIMAL,
@nom_prod AS VARCHAR(25) --VARCHAR(max) quelque soit la taille,
) --SQL SERVER GERE AU MIEUX
AS
BEGIN
SELECT no, nom, prix_conseille
FROM e_produit
WHERE prix_conseille>@seuil_min_prix AND
prix_conseille<@seuil_max_prix AND
nom LIKE '%' + @nom_prod + '%'
ORDER BY prix_conseille;
END;
no nom prix_conseille
5 Onduleur X400 11050
EXECUTION AVEC DES PARAMETRES NOMMES
EXEC uspListeProduits @seuil_min_prix=11000, @seuil_max_prix=12000, @nom_prod='onduleur‘ ;
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
PROCÉDURES STOCKÉES :VALEUR PAR DEFAUT DES PARAMETRES : EXEMPLE 6
ALTER PROCEDURE uspListeProduits (
@seuil_min_prix AS DECIMAL=10000,
@seuil_max_prix AS DECIMAL=12000,
@nom_prod AS VARCHAR(max)
)
AS
BEGIN
SELECT no, nom, prix_conseille
FROM e_produit
WHERE prix_conseille>@seuil_min_prix AND
prix_conseille<@seuil_max_prix AND
nom LIKE '%' + @nom_prod + '%' no nom prix_conseille
ORDER BY prix_conseille; 1 Onduleur X400 11000
END; 5 Onduleur X400 11050
EXECUTION AVEC DES PARAMETRES A VALEUR PAR DEFAUT
EXEC uspListeProduits @nom_prod='onduleur‘ ;
SQL SERVER T-SQL
IV. PROCÉDURES STOCKÉES
PROCÉDURES STOCKÉES : ARG EN MODE ADRESSE : EXEMPLE 7
ALTER PROCEDURE uspListeProduits(
@ListeNomProd AS VARCHAR(max) OUTPUT,
@NbrProd AS INT OUTPUT
)
AS
BEGIN
SET @ListeNomProd ='';
SELECT @ListeNomProd = @ListeNomProd +NOM+CHAR(10)
FROM e_produit
ORDER BY nom;
SELECT @NbrProd =@@ROWCOUNT; -- Nombre de lignes traités
-- @@ROWCOUNT variable système
END;
EXECUTION AVEC DES PARAMETRES DE SORTIE
BEGIN
DECLARE @vListeNomProd AS VARCHAR(max), @vNbrProd AS INT;
EXEC uspListeProduits @vListeNomProd OUTPUT, @vNbrProd OUTPUT;
SELECT @vListeNomProd ;
SELECT @vNbrProd ;
END;
SQL SERVER T-SQL
