Apprendre SQL : Fonctions et Procédures Stockées

Pourquoi créer des fonctions et procédures ?

À la manière des fonctions/procédures utilisées dans un langage de programmation, en SQL, les fonctions et les procédures stockées permettent d’encapsuler des blocs de code SQL. Bien que ces deux éléments aient leur propre fonctionnement, créer ses fonctions et procédures stockées présentent de nombreux intérêts communs, dont les principaux sont :

Réutilisabilité

Cela facilite la réutilisation du code, car vous pouvez appeler la fonction/procédure à chaque fois que vous avez besoin d’exécuter ces requêtes, au lieu de les réécrire à chaque fois.

Abstraction

En encapsulant la logique SQL dans une fonction/procédure, vous pouvez cacher les détails de l’implémentation. Cela simplifie la compréhension et la lisibilité du code.

Maintenabilité

L’encapsulation du code au sein de fonctions/procédures SQL rend le code plus modulaire et maintenable.

Sécurité

Les fonctions/procédures SQL peuvent contribuer à renforcer la sécurité en limitant l’accès direct aux données. Vous pouvez contrôler qui peut y accéder et quelles opérations ils peuvent effectuer.

Tests unitaires

Les fonctions/procédures SQL peuvent être testées de manière unitaire, ce qui facilite la validation de la logique SQL.

Fonctions

Fonctions natives VS fonctions customs

Comme la plupart des langages, SQL possède lui aussi son lot de fonctions natives.

SUM(), AVG(), COUNT(), MIN(), MAX()… Le rôle d’une fonction SQL est simple : prendre une/des donnée(s) en entrée et produire en sortie un résultat basé sur ces données.

Les fonctions SQL encapsulent dans un sous-programme du code calculant des valeurs basées sur les données. Il s’agit d’une requête ou d’un ensemble de requêtes SQL retournant une donnée spécifique (calculs, agrégations, etc.).

Il peut s’agir de calculer une moyenne, compter des valeurs, additionner des valeurs, etc.

Cette donnée peut être calculée en se basant sur :

Les paramètres de la fonction
Des données déclarées dans la fonction avec DECLARE et SET (abordé par la suite)
Des données de la base récupérées au sein de la fonction via SELECT

Une fois définies, ces fonctions peuvent être appelées pour récupérer un résultat et simplifier les requêtes SQL complexes.

Outre l’usage des fonctions natives du langage, SQL vous offre la possibilité d’encapsuler cette logique dans votre propre fonction dédiée, on parle de User-Defined Functions (UDF).

Créer une fonction UDF

Il est temps de se pencher sur la syntaxe permettant de créer ses propres fonctions pour le SGBD MySQL.

1. Délimiteurs

En SQL, le point-virgule (;) est utilisé comme délimiteur pour indiquer la fin d’une instruction SQL.

Chaque instruction SQL doit se terminer par un point-virgule pour que le moteur de base de données comprenne où se termine une instruction et où commence la suivante, surtout lorsque plusieurs instructions SQL sont incluses dans un seul script SQL.

La première chose à faire lorsque l’on souhaite créer une UDF est de modifier le caractère de délimiteur.

copié !

DELIMITER //

-- Ma fonction ici...

DELIMITER ;

Dans cet exemple, nous définissons temporairement le délimiteur comme // avant de créer la UDF. Cela permet d’utiliser ; à l’intérieur de la définition de la UDF sans perturber MySQL.

Après avoir défini la UDF, nous rétablissons le délimiteur par défaut en utilisant la commande DELIMITER ;.

2. Déclaration de la fonction

On crée une fonction avec :

La commande CREATE FUNCTION
Suivie de son nom
Auquel on ajoute des parenthèses (), dédiées à la déclaration de paramètres optionnels

copié !

DELIMITER //

CREATE FUNCTION EUROS_TO_FRANCS()

//

DELIMITER ;

En MySQL et dans de nombreux systèmes de gestion de bases de données, les fonctions UDF sont généralement nommées en UPPERCASE (cf. fonctions natives AVG(), SUM(), etc.). Cela permet d’améliorer la lisibilité du code en permettant de distinguer facilement les fonctions des colonnes. Cependant, il n’y a pas de règle stricte imposant cette convention de casse particulière ; la PascalCase ou encore la camelCase pourraient aussi bien être utilisées.

3. Paramètres

Les paramètres d’une UDF MySQL sont des valeurs que vous pouvez transmettre à la fonction pour qu’elle les exploite lors de son traitement.

Comme n’importe quelle fonction d’un langage, il est possible de renseigner 0, 1 ou plusieurs paramètres.

Chaque paramètre sera défini par son nom et son type.

copié !

DELIMITER //

CREATE FUNCTION EUROS_TO_FRANCS(euros DECIMAL(10, 2))

//

DELIMITER ;

Les types existants sont ceux supportés par votre SGBD. Il s’agit donc de ceux que vous pourriez utiliser lors de la création de colonnes dans vos tables (Pour MySQL : INT, VARCHAR, DATETIME…).

4. Type de retour

Le mot-clé RETURNS permet d’indiquer le type de retour de la fonction.

copié !

DELIMITER //

CREATE FUNCTION EUROS_TO_FRANCS(euros DECIMAL(10, 2))
RETURNS DECIMAL(11, 2)

//

DELIMITER ;

5. Logique de la fonction

La logique de la fonction va être contenue entre les mots-clés BEGIN et END. Vous pouvez y mettre n’importe quelle instruction SQL valide.

copié !

DELIMITER //

CREATE FUNCTION EUROS_TO_FRANCS(euros DECIMAL(10, 2))
RETURNS DECIMAL(11, 2)
BEGIN
	DECLARE changeRate DECIMAL(6, 5);
	DECLARE francs DECIMAL(11, 2);
	SET changeRate = 6.55957;
	SET francs = euros * changeRate;
	RETURN francs;
END

//

DELIMITER ;

Ici, les mots-clés DECLARE et SET permettent respectivement de définir une variable locale (sans oublier de la typer), puis lui affecter une valeur.

L’instruction se termine par le mot-clé RETURN qui retourne la valeur calculée.

Une fonction SQL peut également travailler avec des données calculées à partir de données en base. Imaginez une base de données comportant les 2 tables en relation suivantes :

ARTICLES

id
title
description
content

CATEGORIES

id
name

Ici, un article peut appartenir à une seule catégorie et une catégorie peut être associée à plusieurs articles.

Ainsi, il serait intéressant de mettre au point une fonction COUNT_ARTICLES_BY_CATEGORY() comptant le nombre d’articles pour une certaine catégorie.

copié !

DELIMITER //

CREATE FUNCTION COUNT_ARTICLES_BY_CATEGORY(category VARCHAR(50))
RETURNS INT
BEGIN
	DECLARE nbArticles INT;
	SELECT COUNT(articles.id) INTO nbArticles FROM articles
	INNER JOIN categories ON articles.category_id = categories.id
	WHERE categories.name = category;
	RETURN nbArticles;
END

//

DELIMITER ;

Le mot-clé INTO permet de stocker le résultat de la requête SELECT dans une variable locale préalablement déclarée avec DECLARE.

Utiliser une fonction UDF

Pour utiliser une fonction UDF depuis une requête SQL, il suffira de l’appeler par son nom et lui transmettre des arguments si elle en possède.

Pour en afficher le résultat, on la place en toute logique derrière un SELECT :

copié !

SELECT EUROS_TO_FRANCS(10); -- Retourne 65.60 francs

Reprenons l’exemple de la fonction COUNT_ARTICLES_BY_CATEGORY() de tout à l’heure. Il est plus généralement possible d’exploiter sa valeur de retour n’importe où au sein d’une requête :

copié !

SELECT categories.name, COUNT(articles.id) AS nbArticles FROM articles
INNER JOIN categories ON articles.category_id = categories.id
GROUP BY categories.name
HAVING nbArticles > COUNT_ARTICLES_BY_CATEGORY('musique');

Ici, on liste toutes les catégories qui contiennent plus d’articles que la catégorie « musique ».

Procédures stockées

À la différence des fonctions, qui sont utilisées afin de retourner explicitement une donnée (via le mot-clé RETURN), les procédures stockées n’ont pas de valeur de retour définie. Elles encapsulent dans un sous-programme du code exécutant une tâche spécifique (requête ou ensemble de requêtes SQL).

Les procédures stockées sont principalement utilisées dans deux cas :

Effectuer des opérations d’écriture sur les données (CREATE, UPDATE, DELETE).
Effectuer des opérations de lecture sur les données (READ). À la différence des fonctions, les procédures sont utilisées pour afficher des enregistrements issus de tableaux et non pour retourner une donnée spécifique (par exemple issue d’un calcul ou d’une agrégation).

Créer une procédure stockée

Il est temps de se pencher sur la syntaxe permettant de créer ses propres procédures stockées pour le SGBD MySQL.

1. Délimiteurs

Une procédure stockée va contenir des scripts SQL, au même titre qu’une UDF, il est alors important de modifier temporairement le délimiteur ;.

copié !

DELIMITER //

-- Ma procédure stockée ici...

DELIMITER ;

2. Déclaration de la procédure stockée

On crée une procédure stockée avec :

La commande CREATE PROCEDURE
Suivie de son nom
Auquel on ajoute des parenthèses (), dédiées à la déclaration de paramètres optionnels

copié !

DELIMITER //

CREATE PROCEDURE GET_ARTICLES()

//

DELIMITER ;

De la même manière que pour les fonctions, l’UPPERCASE est à privilégier.

3. Paramètres

Une procédure stockée peut avoir des paramètres, au même titre qu’une fonction.

copié !

DELIMITER //

CREATE PROCEDURE GET_ARTICLES(tag VARCHAR(255))

//

DELIMITER ;

4. Logique de la procédure stockée

La logique de la procédure stockée va être contenue entre les mots-clés BEGIN et END. Vous pouvez y mettre n’importe quelle instruction SQL valide.

copié !

DELIMITER //

CREATE PROCEDURE GET_ARTICLES(tag VARCHAR(255))
BEGIN
	SELECT articles.title
	FROM articles
	INNER JOIN articles_get_tags ON articles.id = articles_get_tags.article_id
	INNER JOIN tags ON articles_get_tags.tag_id = tags.id
	WHERE tags.name = tag;
END

//

DELIMITER ;

Utiliser une procédure stockée

À la différence d’une UDF, il n’est pas possible d’appeler une procédure stockée depuis une autre requête SQL. En revanche, on pourra l’exécuter à l’aide de l’instruction CALL, et lui transmettre des arguments si elle en possède.

copié !

CALL GET_ARTICLES('mon_tag'); -- Retourne les articles liés à ce tag

Newsletter

Pourquoi créer des fonctions et procédures ?

Réutilisabilité

Abstraction

Maintenabilité

Sécurité

Tests unitaires

Fonctions

Fonctions natives VS fonctions customs

Créer une fonction UDF

1. Délimiteurs

2. Déclaration de la fonction

3. Paramètres

4. Type de retour

5. Logique de la fonction

Utiliser une fonction UDF

Procédures stockées

Créer une procédure stockée

1. Délimiteurs

2. Déclaration de la procédure stockée

3. Paramètres

4. Logique de la procédure stockée

Utiliser une procédure stockée