Fonctions MySQL et logique personnalisée

Introduction

Dans ce laboratoire (lab), vous apprendrez à créer et à utiliser des fonctions personnalisées dans MySQL. Le laboratoire se concentre sur l'encapsulation de la logique réutilisable au sein de votre base de données en créant une fonction pour calculer l'aire d'un rectangle.

Les étapes consistent à vous connecter au serveur MySQL, à créer une base de données (si nécessaire), à définir la fonction calculate_rectangle_area avec des paramètres d'entrée pour la longueur et la largeur, et à spécifier le type de données de retour. Le corps de la fonction calcule l'aire et renvoie le résultat. Les étapes suivantes couvriront l'utilisation de cette fonction dans une requête SELECT, le test de sa sortie avec des données d'échantillon, et enfin, la suppression de la fonction.

Skills Graph

Créer une fonction pour calculer une valeur

Dans cette étape, vous apprendrez à créer une fonction dans MySQL. Les fonctions sont un moyen puissant d'encapsuler une logique réutilisable au sein de votre base de données. Nous allons créer une fonction simple qui calcule l'aire d'un rectangle.

Tout d'abord, connectons-nous au serveur MySQL. Ouvrez votre terminal et exécutez la commande suivante :

mysql -u root -p

Vous serez invité à saisir le mot de passe de l'utilisateur root. Entrez le mot de passe et appuyez sur Entrée.

Maintenant, créons une base de données pour travailler. Si vous avez déjà une base de données que vous souhaitez utiliser, vous pouvez sauter cette étape. Sinon, exécutez la commande SQL suivante :

CREATE DATABASE IF NOT EXISTS my_functions_db;
USE my_functions_db;

Ensuite, nous allons créer la fonction. La syntaxe pour créer une fonction dans MySQL est la suivante :

CREATE FUNCTION function_name (parameter1 data_type, parameter2 data_type, ...)
RETURNS return_data_type
DETERMINISTIC
BEGIN
  -- Function body
  RETURN return_value;
END;

• function_name : Le nom de la fonction.
• parameter1, parameter2, ... : Les paramètres d'entrée de la fonction, ainsi que leurs types de données.
• return_data_type : Le type de données de la valeur renvoyée par la fonction.
• DETERMINISTIC : Ce mot-clé indique que la fonction renvoie toujours le même résultat pour les mêmes valeurs d'entrée. Cela est important à des fins d'optimisation.
• BEGIN et END : Ces mots-clés encadrent le corps de la fonction.
• RETURN return_value : Cette instruction renvoie la valeur calculée par la fonction.

Maintenant, créons une fonction appelée calculate_rectangle_area qui prend la longueur et la largeur d'un rectangle en entrée et renvoie son aire. Exécutez la commande SQL suivante :

CREATE FUNCTION calculate_rectangle_area (length INT, width INT)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE area INT;
  SET area = length * width;
  RETURN area;
END;

Dans cette fonction :

• Nous déclarons deux paramètres d'entrée, length et width, tous deux de type INT.
• La fonction renvoie une valeur de type INT, qui représente l'aire du rectangle.
• Nous déclarons une variable locale area de type INT pour stocker l'aire calculée.
• Nous calculons l'aire en multipliant la longueur et la largeur et nous la stockons dans la variable area.
• Enfin, nous renvoyons la valeur de la variable area.

Pour vérifier que la fonction a été créée avec succès, vous pouvez exécuter la commande SQL suivante :

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

Cette commande affichera des informations sur la fonction calculate_rectangle_area, y compris son nom, la base de données et la date de création.

Utiliser la fonction dans une requête SELECT

Dans cette étape, vous apprendrez à utiliser la fonction que vous avez créée à l'étape précédente dans une requête SELECT. Cela vous permet d'appliquer la logique de la fonction aux données extraites de vos tables de base de données.

Tout d'abord, assurez-vous d'être connecté au serveur MySQL et d'utiliser la base de données my_functions_db (ou la base de données où vous avez créé la fonction). Si vous avez fermé la connexion, reconnectez-vous en utilisant :

mysql -u root -p

Ensuite, sélectionnez la base de données :

USE my_functions_db;

Maintenant, créons une table nommée rectangles pour stocker la longueur et la largeur de différents rectangles. Exécutez la commande SQL suivante :

CREATE TABLE rectangles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  length INT,
  width INT
);

Cette commande crée une table avec trois colonnes :

• id : Un entier auto-incrémenté qui sert de clé primaire.
• length : Un entier représentant la longueur du rectangle.
• width : Un entier représentant la largeur du rectangle.

Ensuite, insérons quelques données d'exemple dans la table rectangles. Exécutez les commandes SQL suivantes :

INSERT INTO rectangles (length, width) VALUES (5, 10);
INSERT INTO rectangles (length, width) VALUES (7, 8);
INSERT INTO rectangles (length, width) VALUES (12, 3);

Maintenant que nous avons des données dans la table rectangles, nous pouvons utiliser la fonction calculate_rectangle_area dans une requête SELECT pour calculer l'aire de chaque rectangle. Exécutez la commande SQL suivante :

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

Cette requête récupère les colonnes id, length et width de la table rectangles, et elle appelle également la fonction calculate_rectangle_area avec les colonnes length et width comme entrées. Le résultat de l'appel de la fonction est aliasé en area.

La sortie de la requête sera une table avec quatre colonnes : id, length, width et area. La colonne area contiendra l'aire calculée de chaque rectangle.

Vous devriez voir une sortie similaire à celle-ci :

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
+----+--------+-------+------+

Cela démontre comment utiliser une fonction dans une requête SELECT pour effectuer des calculs sur les données extraites d'une table.

Tester la sortie de la fonction avec des données d'exemple

Dans cette étape, nous allons tester la sortie de la fonction avec différentes données d'exemple pour nous assurer qu'elle fonctionne correctement. Nous allons insérer de nouvelles données dans la table rectangles puis utiliser la fonction calculate_rectangle_area pour vérifier les aires calculées.

Tout d'abord, assurez-vous d'être connecté au serveur MySQL et d'utiliser la base de données my_functions_db. Si vous avez fermé la connexion, reconnectez-vous en utilisant :

mysql -u root -p

Ensuite, sélectionnez la base de données :

USE my_functions_db;

Maintenant, insérons quelques autres données d'exemple dans la table rectangles avec différentes valeurs pour la longueur et la largeur. Exécutez les commandes SQL suivantes :

INSERT INTO rectangles (length, width) VALUES (10, 10);
INSERT INTO rectangles (length, width) VALUES (2, 5);
INSERT INTO rectangles (length, width) VALUES (15, 4);

Ces commandes insèrent trois nouvelles lignes dans la table rectangles avec les valeurs suivantes :

• Longueur = 10, Largeur = 10
• Longueur = 2, Largeur = 5
• Longueur = 15, Largeur = 4

Maintenant, utilisons la requête SELECT avec la fonction calculate_rectangle_area pour calculer l'aire de tous les rectangles, y compris les nouveaux insérés. Exécutez la commande SQL suivante :

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

Cette requête récupérera toutes les lignes de la table rectangles et calculera l'aire de chaque rectangle en utilisant la fonction calculate_rectangle_area.

La sortie de la requête devrait maintenant inclure les aires des nouveaux rectangles :

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
|  4 |     10 |    10 |  100 |
|  5 |      2 |     5 |   10 |
|  6 |     15 |     4 |   60 |
+----+--------+-------+------+

Vérifiez que les aires calculées sont correctes pour les nouvelles données :

• Le rectangle de longueur 10 et de largeur 10 a une aire de 100 (10 * 10 = 100).
• Le rectangle de longueur 2 et de largeur 5 a une aire de 10 (2 * 5 = 10).
• Le rectangle de longueur 15 et de largeur 4 a une aire de 60 (15 * 4 = 60).

Si les aires calculées sont correctes, cela confirme que la fonction calculate_rectangle_area fonctionne comme prévu. Cette étape permet d'assurer la fiabilité de votre fonction en la testant avec diverses entrées.

Supprimer la fonction

Dans cette étape, vous apprendrez à supprimer la fonction que vous avez créée précédemment. Supprimer une fonction l'élimine de la base de données, il est donc important d'être certain de ne plus en avoir besoin avant de la supprimer.

Tout d'abord, assurez-vous d'être connecté au serveur MySQL et d'utiliser la base de données my_functions_db. Si vous avez fermé la connexion, reconnectez-vous en utilisant :

mysql -u root -p

Ensuite, sélectionnez la base de données :

USE my_functions_db;

Pour supprimer la fonction calculate_rectangle_area, exécutez la commande SQL suivante :

DROP FUNCTION IF EXISTS calculate_rectangle_area;

• DROP FUNCTION : C'est la commande SQL utilisée pour supprimer une fonction.
• IF EXISTS : Cette clause empêche une erreur de se produire si la fonction n'existe pas. Il est recommandé de l'inclure pour éviter les erreurs inattendues.
• calculate_rectangle_area : C'est le nom de la fonction que vous souhaitez supprimer.

Après avoir exécuté cette commande, la fonction calculate_rectangle_area sera supprimée de la base de données my_functions_db.

Pour vérifier que la fonction a été supprimée, vous pouvez essayer de sélectionner des données de la table rectangles en utilisant la fonction. Exécutez la commande SQL suivante :

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

Cette commande devrait maintenant retourner une erreur car la fonction calculate_rectangle_area n'existe plus. Le message d'erreur sera similaire à : ERROR 1305 (42000): FUNCTION my_functions_db.calculate_rectangle_area does not exist.

Alternativement, vous pouvez utiliser à nouveau la commande SHOW FUNCTION STATUS :

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

Cette commande devrait retourner un ensemble de résultats vide, indiquant que la fonction n'existe plus.

En supprimant la fonction, vous l'avez éliminée de la base de données et libéré des ressources. N'oubliez de supprimer uniquement les fonctions dont vous êtes certain de ne plus avoir besoin.

Résumé

Dans ce laboratoire (lab), vous avez appris à créer une fonction dans MySQL pour encapsuler une logique réutilisable. Le processus a consisté à vous connecter au serveur MySQL, à créer une base de données (si nécessaire), puis à définir une fonction avec des paramètres d'entrée, un type de données de retour et le mot-clé DETERMINISTIC.

Plus précisément, vous avez créé la fonction calculate_rectangle_area, qui prend en entrée la longueur et la largeur d'un rectangle et renvoie son aire. Le corps de la fonction incluait la déclaration d'une variable locale pour stocker l'aire calculée et l'utilisation de l'instruction RETURN pour renvoyer le résultat.