Programmation sql

avancee

1
 Présentation
Les SGBD-R en général, et MySQL en particulier, permettent
d’écrire des procédures de programmation impérative classique
(type Pascal, C, VB, PHP, Java, etc.) qui seront enregistrées dans
la base de données.
Dans un environnement client-serveur, chaque instruction SQL
donne lieu à l’envoi d’un message du client vers le serveur suivi
de la réponse du serveur vers le client.
Il est préférable de travailler avec un sous-programme (qui sera
stocké, en fait, côté serveur) plutôt qu’avec une suite
d’instructions SQL susceptibles d’encombrer le trafic réseau.

2
 Présentation
En effet, un bloc donne lieu à un seul échange sur le
réseau entre le client et le serveur.
Les résultats intermédiaires sont traités côté serveur et
seul le résultat final est retourné au client

3
Présentation

4
 Présentation
MySQL peut donc enregistrer des requêtes pour les rappeler
comme fonctions d’un programme
Il en existe 3 sortes:
Fonctions et procédures stockées: programmes invocables
depuis les commandes SQL
Déclencheurs (triggers): programmes qui déclenchent avant ou
après un évènement impliquant une table (delete, insert,
update)
Évènements: programmes exécutés à une certaine date,
régulièrement
5
 Procédures stockées
Il en existe 2 types:
Les fonctions qui retournent un résultat et sont appelées à
l’aide de la commande SELECT
Les procédures qui ne retournent pas de résultat et sont
appelées à l’aide de la commande CALL
Une procédure stockée étant une séquences d’instructions
SQL séparées par des « ; »,
il faut préciser au SGBD de la considérer comme une seule
instruction. Pour cela, on utilise un délimiteur
6
 Procédures stockées
Il en existe 2 types:
Les fonctions qui retournent un résultat et sont appelées à
l’aide de la commande SELECT
Les procédures qui ne retournent pas de résultat et sont
appelées à l’aide de la commande CALL
Une procédure stockée étant une séquences d’instructions SQL
séparées par des « ; »,
il faut préciser au SGBD de la considérer comme une seule
instruction. Pour cela, on utilise un délimiteur
7
 Procédures stockées
Pour définir un délimiteur, on utilise le mot clé DELIMITER suivi des
caractère choisi pour délimiteur.
Ex: DELIMITER||, DELIMITER //, DELIMITER $$, …
Le délimiteur est définit au début de chaque procédure et est placé
à la fin de la définition de celle ci

8
 Structure d’un bloc
Un bloc d’instructions est composé de :
BEGIN (section obligatoire) contient le code incluant ou non
des directives SQL se terminant par le symbole « ; » ;
DECLARE (directive optionnelle) déclare une variable, un
curseur, une exception, etc. ;
END ferme le bloc
Un bloc peut être imbriqué dans un autre bloc.
Un objet déclaré dans un bloc est accessible dans les sous-
blocs. En revanche, un objet déclaré dans un sous-bloc n’est pas
visible du bloc supérieur
9
 Structure d’un bloc
Un identificateur commence par une lettre (ou un chiffre) et n’est
pas limité en nombre de caractères.
Les autres caractères connus du langage sont interdits, le seul
autorisé est « _ ». Ex: code_pin
Les commentaires

10
 Variables
Une variable est déclarée par la directive DECLARE
DECLARE nomVariable1[, nomVariable2…] type [DEFAULT expression];

11
 Variables
Pour initialiser ou modifier une variable, on utilise SET ou
INTO:
SET variable = expression).
la directive DEFAULT ;
SELECT … INTO variable FROM …

12
 Variables
Ex: Utilisation de SET
declare nom varchar(25);
SET nom="nom1";
declare prenom varchar(25) default"prenom1";
declare age ;
declare nomComplet text;
SET age=15;
SET nomComplet=concat(nom,' ',prenom)

13
 Variables
Ex1: Utilisation de INTO
delimiter //
Create procedure AfficherTotaleVente()
begin
declare totale float default 0;
Select sum(quantiteVendue*prixVente) INTO total
from ventes;
select total; --afficher le totale
End //
Delimiter ;

14
 Variables
Ex2: Utilisation de INTO
Delimiter //
Create procedure AfficherTotaleVente()
begin
Select numE, nomE INTO my_num, my_nom
from emp
where numE =7369;
End //
Delimiter ;

15
 Variables de session
Les variables de session sont des variables externes au
bloc et qui seront utilisées comme paramètres d’entrée d’un
bloc.
Elles sont aussi appelées user variables
Elles n’existent que durant la session. On les déclare à
l’aide du symbole « @ ».
SET @var1 = expresssion1 [, @var2 = expression2]…

16
 Variables de session
Exemple

17
 Fonctions
La syntaxe d’une fonction est la suivante
DELIMITER ##
CREATE FUNCTION nom_fonction (var1 type1,…,varn typen)
RETURNS type_retour
DETERMINISTIC
BEGIN
Instruction 1;
…
Instruction n;
RETURN valeur;
END ##
DELIMITER ; 18
 Fonctions
EX: Soient les tables Articles (id, titre, description, #idCat), Categories (idcat,
nom). On veut compter le nombre d’articles par catégorie.
DELIMITER //
CREATE FUNCTION NB_ARTICLES_BY_CATEGORY(category VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE nbArticles INT;
SELECT COUNT([Link]) INTO nbArticles FROM articles
INNER JOIN categories ON [Link] = [Link]
WHERE [Link] = category;
RETURN nbArticles;
END //
DELIMITER ;
19
 Fonctions
Le mot clé INTO permet de stocker le résultat de la requête SELECT
dans la variable nbArticles
SELECT NB_ARTICLES_BY_CATEGORY(comedie) appelle la fonction, qui
va afficher le nombre d’article de la catégorie « comedie »

20
 Procédure
La syntaxe d’une procédure est la suivante
DELIMITER ##
CREATE PROCEDURE nom_procedure (var1:type1,…,varn:typen)
BEGIN
Instruction 1;
…
Instruction n;
END ##
DELIMITER ;

21
 Procédure
Ex: Procédure qui affiche la liste des articles qui coutent 10000
DELIMITER //
CREATE PROCEDURE GET_ARTICLES()
BEGIN
SELECT *
FROM articles
WHERE prix=10000;
END //
DELIMITER ;
Pour appeler la procédure: CALL GET_ARTICLES();

22
 Procédure
Les paramètres d’une procédure peuvent être précédés des
mots clés IN et OUT
IN spécifie qu’un paramètre est un paramètre d’entrée
OUT spécifie qu’un paramètre est un paramètre de sortie
INOUT spécifie qu’un paramètre est un paramètre
d’entrée/sortie

23
 Procédure
Ex: Procédure qui calcule la somme de 2 entiers
DELIMITER //
CREATE PROCEDURE somme( IN a INT, IN b INT, OUT r INT)
BEGIN
SET r = a + b;
END //
DELIMITER ;

24
 Procédure
Ex: Appel de la procédure
-- Déclare une variable pour stocker le résultat
SET @sum_result = 0;
-- Appel de la procédure en fournissant les paramètres
d'entrée et de sortie
CALL somme(5, 3, @sum_result);
-- Affiche le résultat de la somme
SELECT @sum_result;
25
 Procédure
Ex: Procédure d’insertion des données dans une table
DELIMITER //
CREATE PROCEDURE ajoutProduit(nomP VARCHAR(25), qteP FLOAT, prixP
FLOAT, marqueP VARCHAR(25))
BEGIN
INSERT INTO produit (libelle, qte, prix, marque)
VALUES (nomP, qteP, prixP, marqueP);
END //
DELIMITER ;
CALL ajoutProduit('Ordinateur', 10, 100000, 'HP');

26
 Instructions IF…THEN
Les instructions IF, ELSEIF, et ELSE sont utilisées dans les
fonctions stockées MySQL pour prendre des décisions
conditionnelles.
Elles permettent d'exécuter différentes parties du code en
fonction de certaines conditions.

27
 Instruction IF...THEN
Ex: Crée une nouvelle fonction stockée nommée « evaluer_note »
DELIMITER //
CREATE FUNCTION evaluer_note( IN note DECIMAL(5, 2))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE evaluation VARCHAR(255);
IF note >= 16 THEN SET evaluation = 'Très bien';
ELSEIF note >= 14 THEN SET evaluation = 'Bien';
ELSEIF note >= 12 THEN SET evaluation = 'Assez bien';
ELSEIF note >= 10 THEN SET evaluation = ‘Passable';
ELSE SET evaluation = 'Insuffisant';
END IF;
RETURN evaluation;
END //
DELIMITER;

28
 Instruction IF...THEN
Ex: Exemple d’appel de la fonction
SELECT evaluer_note(18); -- Renvoie 'Très bien'
SELECT evaluer_note(13); -- Renvoie ‘Assez Bien'
SELECT evaluer_note(11); -- Renvoie ‘Passable'
SELECT evaluer_note(9); -- Renvoie 'Insuffisant'

29
 Instruction IF…THEN
Ex: Calcul des frais de livraison en fonction de la distance
CREATE FUNCTION fraisLivraison(distanceKm INT) RETURNS DECIMAL(8,2)
BEGIN
DECLARE frais DECIMAL(8,2);
IF distanceKm <= 10 THEN
SET frais = 5.00;
ELSEIF distanceKm <= 20 THEN
SET frais = 10.00;
ELSE
SET frais = 15.00;
END IF;
RETURN frais;
END;

30
 Instruction CASE..WHEN
L'instruction CASE en SQL est utilisée pour effectuer une
évaluation conditionnelle et renvoyer une valeur spécifique en
fonction de différentes conditions.
Ex:
SELECT nom, age,
CASE
WHEN age < 18 THEN 'Mineur'
WHEN age >= 18 AND age < 65 THEN 'Adulte'
ELSE 'Senior'
END
AS categorie_age
FROM personnes;

31
 Boucle WHILE
Ex: procédure stockée pour afficher les nombres de 1 à 10.
DELIMITER //
CREATE PROCEDURE AfficherNombres()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE res text DEFAULT "";
WHILE i <= 10 DO
SET res=concat(res,' ',i);
SET i = i + 1;
END WHILE;
SELECT res;
END //
DELIMITER ;

32
 Boucle REPEAT
Ex: Créez une procédure stockée pour calculer la somme des entiers de 1 à 100.
DELIMITER //
CREATE PROCEDURE CalculerSomme()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE somme INT DEFAULT 0;
REPEAT
SET somme = somme + i;
SET i = i + 1;
UNTIL i > 100
END REPEAT;
SELECT somme;
END; //
DELIMITER ;

33
 Boucle LOOP
Ex: Procédure stockée pour afficher les noms des produits jusqu'à ce qu'un produit spécifique soit trouvé..

DELIMITER //
CREATE PROCEDURE AfficherNomsProduits()
BEGIN
DECLARE produitRecherche VARCHAR(25) DEFAULT 'ProduitX‘, nomProduit VARCHAR(25);
DECLARE i int default 0, res text default "";
boucle: LOOP
SELECT nom INTO nomProduit FROM produits LIMIT i,1;
SET res=concat(res,' ',nomProduit);
IF nomProduit = produitRecherche THEN
LEAVE boucle;
END IF;
set i=i+1;
END LOOP;
SELECT res;
END; //
DELIMITER ;

34