SQL Server

TRANSACT-SQL

Hamza RAYD
 Introduction
• Transact SQL est un langage procédural (par opposition à SQL qui est
un langage déclaratif) qui permet de programmer des algorithmes de
traitement des données au sein des SGBDR Sybase Adaptive Server et
Microsoft SQL Server. Pour le SGBDR Oracle on utilise PL-SQL. 
• Il a été créé par Sybase INC. à la fin des années 80 pour répondre aux
besoins d'extension de la programmation des bases de données pour
son SGBDR.
• Ce langage est donc commun aux deux produits (MS SQL Server et
Sybase Adaptive) avec des différences mineures dans les
implémentations.
• Il sert à programmer des procédures stockées, des fonctions et des
triggers (déclencheurs).
2
 Les Variables et les Types de
Données
Pour déclarer une variable, utilisez le mot clé DECLARE
en utilisant la formule suivante :
Noms de variables:
Variable locale:
DECLARE @VariableName DataType;
Variable globale:
DECLARE @@VariableName DataType;
Déclaration multiples:
DECLARE @Variable1 DataType1, @Variable2 DataType2,
@Variable_n DataType_n;
3
Les règles de codage des variables
 ne peuvent dépasser 128 caractères.
 Ils doivent commencer par une lettre ou un
"underscore".
 Les caractères spéciaux et le blanc ne sont pas
admis.
 On se contentera d'utiliser les 37 caractères de
base : ['A'..'Z', '0'..'9', '_']
 La casse n'a pas d'importance.

4
Types de données

5
 Type de données
• Table : Type de données spécial qui permet de stocker
un jeu de résultats pour un traitement ultérieur. Son
utilisation principale concerne le stockage temporaire
d'un ensemble de lignes, qui doivent être renvoyées
sous forme de jeu de résultats d'une fonction table.
• Une variable de type table se comporte comme une
variable locale. Elle possède une portée bien définie,
qui représente la fonction, la procédure stockée ou le
lot d'instructions dans lequel elle est déclarée.

6
 Initialisation d'une Variable
• La formule utilisée est la suivante :
– SELECT @VariableName = DesiredValue
ou
– SET @VariableName = DesiredValue

• Initialisation multiple: avec Select seulement

select @i=3,@j=4,@str='TSDI‘

7
 Affichage des valeurs
• Dès qu'une variable a été initialisée, vous pouvez
rendre sa valeur disponible ou l'afficher selon
deux sorties:

• Affichage « Table» avec SELECT:

Select @i

• Affichage « Message » avec PRINT:

Print ‘Chaine de caractère’
8
 Exemple
DECLARE @FirstName nvarchar(20),
@LastName nvarchar(20),
@FullName nvarchar(40),
@DateHired datetime, @EmplStatus int,
@IsMarried bit, @WeeklyHours decimal(6,2),
@HourlySalary SmallMoney,
@WeeklySalary SmallMoney;
SET @FirstName = N'Samuel';
SET @LastName = N'Weinberg';
SET @FullName = @LastName + N', ' + @FirstName;
SET @DateHired = N'12/05/1998';
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @FullName As 'Full Name',
@DateHired AS 'Date Hired',
@EmplStatus AS 'Empl Status',
@IsMarried AS 'Married?',
@WeeklyHours AS 'Hours',
@HourlySalary AS 'Hourly', 9
@WeeklySalary AS 'Weekly';
Exemple de variable de type table :
Declare @stg table(numInsc int primary key, nom
varchar(20), prenom varchar(20),moyenne
numeric(4,2))
/*la particularité des variables de type table, est qu’on
peut utiliser des commandes insert, select,update,
delete */
insert into @stg values(103,'LAAROUSSI','SALAH',14)
insert into @stg values(107,'AADISSA','Youness',14.5)
insert into @stg values(200,'SOQRAT','Sanaa',12.5)
select * from @stg

10
Structures basiques

11
 Bloc d’instructions
• Un bloc d’instruction est un ensemble
d’instruction T-SQL qui sont considéré comme
un tout ( une seule).
• Un bloc d’instruction peut contenir d’autres
sous blocs.
Begin
--instruction(1)
--instruction(2)
…
--instruction(N)
end

12
 Instructions conditionnelles
Si une condition est True:
IF [NOT] condition instruction[ELSE instruction]

Exemple:

DECLARE @DateEmbauche datetime, @CurrentDate datetime

SET @DateEmbauche = '04/10/2010 '
SET @CurrentDate = ' 11/04/2011 '
IF @DateEmbauche < @CurrentDate
PRINT 'Expérience exigée'
ELSE
PRINT 'Bienvenue'

13
 Instructions conditionnelles
IF [NOT] EXISTS(requête select)
instruction [ELSE
instruction]

Exemple2:
if exists ( select * from pilote where ville = 'tanger')
print 'il exist des pilotes tangerois'
else
print 'aucun pilote n ' ' est tangerois'

14
 Instructions conditionnelles
• CASE: La fonction CASE est une expression
Transact-QL spéciale qui permet l'affichage
d'une valeur de remplacement en fonction de
la Valeur d'une colonne. Ce changement est
temporaire. Par conséquent, aucune
Modification permanente n'est apportée aux
données.
• Syntaxe: CASE expression
WHEN valeur1 THEN résultats
WHEN Value2 THEN résultats
WHEN Value_n THEN résultats END
15
 Instructions conditionnelles

• Exemple:
SELECT marque,capacite,
CASE
WHEN capacite<=400 THEN 'Petit avion'
WHEN capacite<600 and capacite>=400 THEN 'Avion Moyen'
WHEN capacite>=600 THEN 'Grand Avion'
END AS 'Comentaire'
FROM avion ORDER BY capacite

16
 Instructions itérative

WHILE condition instruction

DECLARE @Numéro As int

SET @numéro = 1
WHILE @numéro < 5
BEGIN
SELECT @ numéro as numéro
SET @ numéro = @numéro + 1
END

17
 Les curseurs- définition
Dans SQL Server, un curseur est un objet qui nous
permet d’exécuter un traitement sur un ensemble
d’enregistrements. Les curseurs sont des outils très
puissants, mais aussi très gourmands en ce qui
concerne les ressources. Il est donc conseillé de
modifier des lignes de résultat de manière
traditionnelle, avec un simple UPDATE ou une autre
instruction du DML, afin de consommer le moins de
ressources possibles.

18
 Déclaration des curseurs
DECLARE cursor_name CURSOR FOR select_statement

– cursor_name : Nom du curseur de serveur

Transact-SQL défini.
– select_statement : Instruction SELECT standard
qui définit le jeu de résultats du curseur.

19
 Les curseurs
• OPEN: Cette instruction permet de remplir le curseur
par le jeu de résultat de SELECT et le rendre ainsi
utilisable.
– Syntaxe:
• FETCH: C’est l’instruction qui permet d’extraire une ligne
du curseur et de valoriser les variables et leur contenu.
• @@FETCH_STATUS : c’est une variable globale qui établit
un rapport d'état de la dernière instruction FETCH: Si
l’exécution du FECTCH s’est bien passée , la variable
globale @@FETCH_STATUS est à 0.

20
 Les curseurs
– Syntaxe:
FETCH (arguments) Nom_Curseur INTO Liste_Variable

• Arguments:
– NEXT: Lit la ligne suivante.
– PRIOR: Lit la ligne précédente.
– FIRST: Lit la première ligne.
– LAST: Lit la dernière ligne.
– ABSOLUTE p: Lit la Pième ligne de l’ensemble.
– RELATIVE p: avance de p position (si n négatif recule de p
position)
• Liste_Variable: autant de variable que de colonne dans
l’ordre SELECT

21
 Les curseurs
• CLOSE: Cette instruction permet la fermeture du
curseur et la libération de la place mémoire où il
été contenu. Il est important de faire intervenir
cette opération dés que possible dans le souci
de libérer les ressources.
– Syntaxe: CLOSE Nom_Curseur

• DEALLOCATE : Cette instruction permet de

supprimer le curseur et les ressources associées.
– Syntaxe: DEALLOCATE Nom_Curseur

22
 Exemple
• Soit la table avion:

• Afficher les marques des avions avec leur types sous

le forme suivante:
Marque : AIRBUS - Type: A
Marque : Airbus - Type: civile
Marque : Boeing - Type: civile
Marque : F5 - Type: militaire
Marque : TEST - Type: T

23
 Exemple

Declare @Marque varchar(20), @type varchar(20)

DECLARE Avion_cursor CURSOR FOR SELECT distinct (marque), typeAvion FROM
Avion
OPEN Avion_cursor
FETCH NEXT FROM Avion_cursor INTO @marque, @type
While @@FETCH_STATUS=0
begin
print 'Marque : ' + @Marque + ' - Type: ' + @type
FETCH NEXT FROM Avion_cursor INTO @Marque, @type
End
CLOSE Avion_cursor
DEALLOCATE Avion_cursor
GO

24
 Exemple
• Afficher les marques des avions avec leur types sous
le forme suivante:
Marque : AIRBUS
Type :
-A
- civile
Marque : Boeing
Type :
- civile
Marque : F5
Type :
- militaire
Marque : TEST
Type :
-T
25
 Exemple
Declare @Marque varchar(20), @type varchar(20)
DECLARE Avion_cursor CURSOR FOR SELECT distinct (marque) FROM Avion
OPEN Avion_cursor
FETCH NEXT FROM Avion_cursor INTO @marque
While @@FETCH_STATUS=0
begin
print 'Marque : ' + @Marque
print ' Type :'

DECLARE Type_cursor CURSOR FOR SELECT DISTINCT typeAvion From Avion where marque =
@marque
OPEN Type_cursor
FETCH NEXT FROM Type_cursor INTO @type
while @@fetch_status=0
begin
print ' - ' + @type
FETCH NEXT FROM Type_cursor INTO @type
end
CLOSE type_cursor
DEALLOCATE type_cursor
FETCH NEXT FROM Avion_cursor INTO @Marque
End
CLOSE Avion_cursor
DEALLOCATE Avion_cursor 26
 LES SEQUENCES
DEFINITION
Une séquence est un objet lié par schéma défini par l'utilisateur qui
génère une séquence de valeurs numériques d'après la spécification
avec laquelle la séquence a été créée. La séquence de valeurs
numériques est générée dans un ordre croissant ou décroissant à un
intervalle défini et peut être configurée pour redémarrer (cycle)
lorsque épuisée. Les séquences, contrairement aux colonnes
d'identité, ne sont pas associées aux tables spécifiques. Les
applications font référence à un objet séquence pour extraire sa
valeur suivante. La relation entre les séquences et les tables est
contrôlée par l'application. Les applications utilisateur peuvent
référencer un objet séquence et coordonner les valeurs à travers
plusieurs lignes et tables.

27
 LES SEQUENCES
Syntaxe
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NOMINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NOMAXVALUE } ]
[ CYCLE | { NOCYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[;]
28
 LES SEQUENCES
Exemple:

CREATE SEQUENCE [Link]

AS decimal(3,0)
START WITH 125
INCREMENT BY 25
MINVALUE 100
MAXVALUE 200
CYCLE
CACHE 3
29
 LES SEQUENCES

NEXT VALUE FOR [ database_name . ] [ schema_name . ]

sequence_name
[ OVER (<over_order_by_clause>) ]

Génère un numéro séquentiel de l'objet

séquence spécifié.
Exemple:

30