Introduction au SQL procédural pour le
dynamisme, l’automatisation et l’optimisation
des requêtes complexes
I Les blocs et les procédures en SQL
procédural
II Les tables temporaires
III La déclaration de variables avec : SET,
DECLARE, @var
IV Les boucles et les conditions avec :
IF, CASE, LOOP, WHILE, REPEAT
V Parcourir ligne par ligne les résultats
d’une requête avec CURSOR
VI La création des fonctions
personnalisées
VII Cas pratique en assurance
I. Les blocs et les procédures en SQL procédural
La manipulation des variables en SQL procédural sert à introduire une logique plus
flexible et dynamique dans les requêtes ou procédures stockées.
Cela rapproche le SQL procédural des langages de programmation classiques en offrant la
possibilité de :
Stocker temporairement une valeur (calculée ou pas)
Paramétrer une requête ou une procédure
Structurer le code
Automatiser des calculs ou traitements périodiques
Pour se faire, nous utilisons :
[Link] blocs
Un bloc est une unité logique de code qui regroupe plusieurs instructions SQL et qui sont
exécutées ensemble.
Un bloc peut être anonyme (juste exécuté une fois) ou nommé (inclus dans une
procédure/fonction).
Nous utilisons :
BEGIN ... END : pour délimiter un bloc
En MySQL, nous utilisons DELIMITER $$, pour changer le délimiteur;
Pour exécuter les blocs anonymes en PostgreSQL, nous utilisons : DO $$ procédure $$;
2. Les procédures
Une procédure est un bloc de code nommé, enregistré dans la base de données et
réutilisable.
Pour créer une procédure, nous procédons comme suite :
CREATE PROCEDURE Nom_procedure (Paramètres)
BEGIN
Instructions/ bloc
END
Exemple : Résultat :
II. Les tables temporaires
Les tables temporaires sont des objets de base de données relationnelle en SQL.
Elles sont particulièrement utiles en SQL procédural, notamment pour stocker des
résultats intermédiaires et optimiser nos requêtes.
Une table temporaire est une table spéciale qui n’existe que pendant la session en cours
(ou jusqu’à ce qu’on la supprime explicitement).
Elle est utile pour stocker des résultats intermédiaires (par ex. résultats de curseurs,
agrégats, transformations).
Quand la connexion est fermée, la table disparaît automatiquement. Elles sont visibles
uniquement dans la session qui les a créées et ne sont pas partagées entre utilisateurs.
Exemple :
Résultat : Constat
Ici, nous avons créé la table, puis nous avons
inséré les données.
Nous pouvons aussi alimenter directement la
table avec le résultat du SELECT juste après la
création de la table temps_sp avec :
CREATE TEMPORARY TABLE temp_sp AS
SELECT ... ;
III. La déclaration de variables avec :
DECLARE, @var, SET
1. SET
Elle sert à attribuer une valeur à une variable.
Elle est utilisée après le DECLARE ou avec le @Nom_var.
A la place de SET, nous pouvons aussi utiliser SELECT ... INTO pour mettre le résultat d’une
requête dans une variable
2. DECLARE
Elle sert à déclarer une variable locale dans une procédure stockée, une fonction ou un bloc.
Les variables doivent être typées (INT, FLOAT, VARCHAR, ...).
Exemple :
2. @Nom_var
Elle permet de créer une variable de session (visibles uniquement dans la session en
cours, pas globales au serveur).
Exemple :
Résultat :
B.K
IV. Les boucles et les conditions avec :
IF, CASE, LOOP, WHILE, REPEAT
En SQL procédural, nous disposons de structures de contrôle comme dans un langage de
programmation classique : conditions (IF, CASE) et boucles (LOOP, WHILE, REPEAT).
Les conditions
Elles servent à exécuter du code seulement si une condition est vraie, ou choisir un
chemin parmi plusieurs.
1. IF ... THEN ... ELSE
Elle teste une condition et exécute un bloc selon vrai/faux.
Elle permet d’appliquer des règles métiers différentes selon les cas
2. CASE .... WHEN ... ELSE ... END
Elle affecte une valeur selon plusieurs cas.
Exemples :
Résultat :
B.K
Les boucles
Elles permettent de répéter des instructions tant qu’une condition est remplie.
1. WHILE <condition> DO ... END WHILE
Elle répète tant qu’une condition est vraie.
2. REPEAT ... UNTIL <condition> END UNTIL
Elle répète jusqu’à ce qu’une condition soit remplie
Résultat :
B.K
Les boucles
Elles permettent de répéter des instructions tant qu’une condition est remplie.
1. LOOP ... END LOOP
Elle sert à répéter un bloc jusqu’à une condition spécifique.
Exemple :
Résultat :
B.K
V. Parcourir ligne par ligne les résultats d’une requête avec
CURSOR
Un CURSOR est un pointeur qui permet de parcourir ligne par ligne le résultat d’une
requête SQL.
Pour se faire, nous procédons comme suite :
Déclarons le curseur :
DECLARE Nom_cur CURSOR FOR SELECT Vars FROM Nom_DB;
Déclarons un handler (pour stopper la boucle quand il n’y a plus de ligne à lire) :
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Nom_fin = 1;
Ouvrons le curseur :
OPEN Nom_cur;
Boucle avec LOOP :
FETCH Nom_cur INTO var1, var2... pour récupérer une ligne du curseur.
IF Nom_fin = 0 THEN INSERT INTO ...
si fin des résultats, Nom_fin = 1 et on LEAVE la boucle
Fermons le curseur :
CLOSE Nom_cur;
Exemple :
Résultat :
VI. La création des fonctions personnalisées
Une fonction personnalisée, contrairement aux fonctions intégrées comme SUM, est un
bloc de code SQL que l’utilisateur crée pour encapsuler une logique réutilisable.
Elle est définie pour répondre à un besoin précis.
Pour créer une fonction personnalisée, nous procédons comme suite :
CREATE FUNCTION Nom_fonction (Paramètres)
RETURNS Type
DETERMINISTIC/ NOT DETERMINISTIC
BEGIN
Instructions/Bloc
END;
DETERMINISTIC : la fonction retournera toujours la même sortie pour une même entrée
NOT DETERMINISTIC : la fonction peut donner des résultats différents même avec la
même entrée
Nous pouvons aussi utiliser : DELIMITER $$ pour éviter les erreurs de syntaxe lorsque nous
avons plusieurs instructions.
Exemple : Résultat :
VII. Cas pratique en assurance :
Calcul des IBNR avec la méthode Chain-Ladder
Besoin métier :
Une compagnie d’assurance souhaite estimer, grâce à la méthode de Chain Ladder,
combien lui coûteront les sinistres survenus mais non déclarés plus communément connu
sous l’acronyme : IBNR. Les données brutes de sinistres déclarés sont enregistrées par
année de survenance et par année de développement. L’objectif est d’obtenir de ces
données :
les sommes cumulées par année de survenance,
en déduire les facteurs de développement,
calculer les Ultimes,
calculer les IBNR.
Code SQL :
Résultat :
B.K