Bases des procédures stockées MySQL

Introduction

Dans ce laboratoire, vous apprendrez les bases des procédures stockées MySQL. L'objectif est de comprendre comment créer, appeler et modifier des procédures stockées pour gérer les données au sein d'une base de données MySQL.

Vous commencerez par créer une base de données et une table nommée employees. Ensuite, vous écrirez une procédure stockée nommée insert_employee pour insérer des données dans la table employees. Vous apprendrez comment appeler cette procédure en utilisant l'instruction CALL et comment ajouter des paramètres d'entrée à la procédure. Enfin, vous apprendrez comment supprimer la procédure en utilisant l'instruction DROP PROCEDURE.

Remarque : Pour ce laboratoire, vous n'avez besoin d'entrer dans le shell MySQL qu'une seule fois au début, et d'en sortir à la toute fin. Toutes les commandes SQL des étapes suivantes doivent être exécutées au sein de la même session MySQL. Il n'est pas nécessaire de se connecter ou de se déconnecter de MySQL de manière répétée entre les étapes.

Créer une procédure pour insérer des données

Dans cette étape, vous apprendrez comment créer une procédure stockée dans MySQL qui insère des données dans une table. Les procédures stockées sont des instructions SQL précompilées stockées dans la base de données. Elles peuvent être exécutées par leur nom, ce qui peut améliorer les performances et la sécurité.

Tout d'abord, ouvrez votre terminal et connectez-vous au serveur MySQL en utilisant la commande suivante :

sudo mysql -u root

Cette commande se connecte au serveur MySQL en tant qu'utilisateur root. Gardez cette session MySQL ouverte pour toutes les étapes suivantes.

Une fois connecté, vous serez dans le shell MySQL. Maintenant, basculez vers la base de données testdb qui a été créée lors de la configuration :

USE testdb;

Maintenant que nous sommes dans la bonne base de données, créons une procédure stockée pour insérer des données dans la table employees. Une procédure stockée est créée à l'aide de l'instruction CREATE PROCEDURE. Nous allons définir une procédure nommée insert_employee qui insère un nouvel enregistrement d'employé.

Voici le code SQL pour la procédure stockée :

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255))
BEGIN
    INSERT INTO employees (name, department) VALUES (employee_name, employee_department);
END //
DELIMITER ;

Analysons ce code :

• DELIMITER // : Ceci change le délimiteur d'instruction de ; à //. C'est nécessaire car la procédure elle-même contient des points-virgules, et nous devons indiquer à MySQL de traiter toute la définition de la procédure comme une seule instruction.
• CREATE PROCEDURE insert_employee : Ceci déclare la création d'une procédure stockée nommée insert_employee.
• (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255)) : Ceci définit les paramètres d'entrée de la procédure. employee_name et employee_department sont les noms des paramètres, et VARCHAR(255) est leur type de données. Le mot-clé IN indique qu'il s'agit de paramètres d'entrée.
• BEGIN ... END : Ce bloc contient les instructions SQL qui seront exécutées lorsque la procédure sera appelée.
• INSERT INTO employees (name, department) VALUES (employee_name, employee_department); : Ceci est l'instruction SQL qui insère une nouvelle ligne dans la table employees, en utilisant les valeurs passées en tant que paramètres d'entrée.
• DELIMITER ; : Ceci réinitialise le délimiteur d'instruction à ;.

Pour exécuter ce code, copiez-le et collez-le directement dans votre shell MySQL.

Après avoir exécuté le code, vous pouvez vérifier que la procédure a été créée en exécutant la commande suivante dans le shell MySQL :

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

Cette commande affichera des informations sur la procédure insert_employee, y compris son nom, sa base de données et sa date de création.

Vous avez maintenant créé avec succès une procédure stockée pour insérer des données dans la table employees. Dans la prochaine étape, vous apprendrez comment appeler cette procédure.

Appeler la procédure avec l'instruction CALL

Dans l'étape précédente, vous avez créé une procédure stockée nommée insert_employee. Dans cette étape, vous apprendrez comment appeler cette procédure en utilisant l'instruction CALL.

Rappel : Vous devriez toujours être dans le shell MySQL et utiliser la base de données testdb. Si ce n'est pas le cas, basculez-y avec :

USE testdb;

L'instruction CALL est utilisée pour exécuter une procédure stockée. La syntaxe est la suivante :

CALL nom_procedure(argument1, argument2, ...);

Dans notre cas, le nom de la procédure est insert_employee, et elle prend deux arguments : le nom de l'employé et le département de l'employé.

Appelons la procédure insert_employee pour insérer un nouvel employé nommé "Alice Smith" dans le département "Engineering" :

CALL insert_employee('Alice Smith', 'Engineering');

Cette instruction exécutera la procédure insert_employee avec les arguments spécifiés.

Pour vérifier que les données ont été correctement insérées, vous pouvez interroger la table employees dans le shell MySQL :

SELECT * FROM employees;

Vous devriez voir une nouvelle ligne dans la table avec le nom "Alice Smith" et le département "Engineering". L'id sera attribué automatiquement.

Insérons un autre employé, "Bob Johnson" dans le département "Marketing" :

CALL insert_employee('Bob Johnson', 'Marketing');

Là encore, vérifiez l'insertion en interrogeant la table employees :

SELECT * FROM employees;

Vous devriez maintenant voir deux lignes dans la table, une pour "Alice Smith" et une pour "Bob Johnson".

Vous avez maintenant appelé avec succès la procédure stockée insert_employee en utilisant l'instruction CALL et vérifié que les données ont été correctement insérées. Cela démontre comment les procédures stockées peuvent être utilisées pour encapsuler et réutiliser la logique SQL.

Ajouter un paramètre d'entrée à la procédure

Dans les étapes précédentes, vous avez créé et appelé une procédure stockée nommée insert_employee qui prend deux paramètres d'entrée : employee_name et employee_department. Dans cette étape, vous apprendrez comment ajouter un autre paramètre d'entrée à la procédure.

Ajoutons un paramètre employee_salary à la procédure insert_employee. Cela nous permettra de spécifier le salaire de l'employé lors de l'insertion d'un nouvel enregistrement.

Tout d'abord, vous devez supprimer la procédure existante. Si vous ne la supprimez pas, vous obtiendrez une erreur en essayant de créer une procédure portant le même nom. Dans votre shell MySQL, exécutez :

DROP PROCEDURE IF EXISTS insert_employee;

Maintenant, créons la procédure stockée modifiée avec le nouveau paramètre d'entrée.

DELIMITER //
CREATE PROCEDURE insert_employee (IN employee_name VARCHAR(255), IN employee_department VARCHAR(255), IN employee_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, department, salary) VALUES (employee_name, employee_department, employee_salary);
END //
DELIMITER ;

Analysons les changements :

• Nous avons ajouté un nouveau paramètre d'entrée IN employee_salary DECIMAL(10, 2) à la définition de la procédure. DECIMAL(10, 2) est le type de données pour le salaire, qui permet jusqu'à 10 chiffres avec 2 décimales.
• Nous avons modifié l'instruction INSERT pour inclure la colonne salary et le paramètre employee_salary.

Appelons maintenant la procédure modifiée insert_employee pour insérer un nouvel employé nommé "Charlie Brown" dans le département "Finance" avec un salaire de 60000.00 :

CALL insert_employee('Charlie Brown', 'Finance', 60000.00);

Pour vérifier que les données ont été correctement insérées, vous pouvez interroger la table employees dans le shell MySQL :

SELECT * FROM employees;

Vous devriez voir une nouvelle ligne dans la table avec le nom "Charlie Brown", le département "Finance" et le salaire 60000.00.

Vous avez maintenant ajouté avec succès un paramètre d'entrée à la procédure stockée insert_employee et vérifié que les données ont été correctement insérées. Cela démontre comment les procédures stockées peuvent être modifiées pour répondre à de nouvelles exigences.

Supprimer la procédure

Dans cette dernière étape, vous apprendrez comment supprimer (effacer) une procédure stockée de la base de données. La suppression d'une procédure la retire de la base de données, la rendant indisponible pour exécution.

Rappel : Vous devriez toujours être dans le shell MySQL et utiliser la base de données testdb.

L'instruction DROP PROCEDURE est utilisée pour supprimer une procédure stockée. La syntaxe est la suivante :

DROP PROCEDURE [IF EXISTS] nom_procedure;

La clause IF EXISTS est facultative mais recommandée. Elle évite qu'une erreur ne se produise si la procédure n'existe pas.

Dans notre cas, le nom de la procédure est insert_employee. Supprimons la procédure :

DROP PROCEDURE IF EXISTS insert_employee;

Cette instruction supprimera la procédure insert_employee de la base de données testdb.

Pour vérifier que la procédure a été supprimée, vous pouvez essayer d'afficher à nouveau le statut de la procédure dans le shell MySQL :

SHOW PROCEDURE STATUS WHERE db = 'testdb' AND name = 'insert_employee';

Cette commande devrait renvoyer un ensemble de résultats vide, indiquant que la procédure n'existe plus.

Alternativement, si vous essayez d'appeler la procédure, vous obtiendrez une erreur :

CALL insert_employee('Test', 'Test', 1000);

Cela entraînera un message d'erreur similaire à : ERROR 1305 (42000): PROCEDURE testdb.insert_employee does not exist.

Vous avez maintenant supprimé avec succès la procédure stockée insert_employee.

Vous pouvez maintenant quitter le shell MySQL en tapant :

exit

Ceci conclut l'exercice pratique sur la création, l'appel, la modification et la suppression de procédures stockées dans MySQL.

Résumé

Dans ce laboratoire, vous avez appris les bases des procédures stockées MySQL, en commençant par la création d'une base de données et d'une table nommée employees. Vous avez ensuite défini une procédure stockée nommée insert_employee en utilisant l'instruction CREATE PROCEDURE, qui insère des données dans la table employees. La commande DELIMITER a été utilisée pour gérer les points-virgules à l'intérieur de la définition de la procédure.

Le laboratoire a également couvert la manière de définir des paramètres d'entrée pour la procédure stockée, en spécifiant leurs noms et types de données. Cela vous permet de passer des valeurs à la procédure lorsqu'elle est appelée, la rendant plus flexible et réutilisable. Vous vous êtes entraîné à appeler la procédure stockée en utilisant l'instruction CALL et avez vérifié l'insertion des données. Enfin, vous avez appris à supprimer une procédure stockée en utilisant l'instruction DROP PROCEDURE.