Les 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 dans 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 à l'aide de l'instruction CALL et comment ajouter des paramètres d'entrée à la procédure. Enfin, vous apprendrez comment supprimer la procédure à l'aide de l'instruction DROP PROCEDURE.

Skills Graph

Écrire une procédure pour insérer des données

Dans cette étape, vous apprendrez à 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 nom, ce qui peut améliorer les performances et la sécurité.

Tout d'abord, créons une simple table avec laquelle travailler. Ouvrez votre terminal et connectez-vous au serveur MySQL en utilisant 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 nommée testdb :

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

Ensuite, créons une table nommée employees avec la structure suivante :

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255)
);

Maintenant que nous avons une table, créons une procédure stockée pour insérer des données dans celle-ci. 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 nouveau enregistrement d'employé.

Voici le code SQL de 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 ;

Décortiquons ce code :

• DELIMITER // : Cela change le délimiteur d'instruction de ; à //. Cela 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 : Cela 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)) : Cela 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 que ce sont des paramètres d'entrée.
• BEGIN ... END : Ce bloc contient les instructions SQL qui seront exécutées lorsque la procédure est appelée.
• INSERT INTO employees (name, department) VALUES (employee_name, employee_department); : Il s'agit de 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 ; : Cela rétablit le délimiteur d'instruction à ;.

Pour exécuter ce code, vous pouvez le copier et le coller directement dans votre terminal 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 :

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, la base de données et la 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 l'étape suivante, vous apprendrez à 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 à appeler cette procédure à l'aide de l'instruction CALL.

Tout d'abord, assurez-vous d'être connecté au serveur MySQL et d'utiliser la base de données testdb. Si vous n'êtes pas déjà connecté, utilisez les commandes suivantes :

mysql -u root -p

Entrez le mot de passe lorsque vous y êtes invité.

USE testdb;

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

CALL procedure_name(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 service de l'employé.

Appelons la procédure insert_employee pour insérer un nouvel employé nommé "Alice Smith" dans le service "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é insérées correctement, vous pouvez interroger la table employees :

SELECT * FROM employees;

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

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

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

Vérifions à nouveau 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 à l'aide de l'instruction CALL et vérifié que les données ont été insérées correctement. 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 à 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 lorsque vous essayerez de créer une procédure avec le même nom. Connectez-vous à MySQL si vous n'y êtes pas déjà connecté :

mysql -u root -p

Entrez le mot de passe lorsque vous y êtes invité.

USE testdb;

Supprimez la procédure existante :

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) VALUES (employee_name, employee_department);
    -- Ajouter la mise à jour du salaire après l'insertion
    UPDATE employees SET salary = employee_salary WHERE name = employee_name AND department = employee_department;
END //
DELIMITER ;

Analysons les modifications :

• 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 ajouté une nouvelle colonne salary à la table employees. Vous devez ajouter cette colonne manuellement en utilisant la déclaration SQL suivante :

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

• Nous avons ajouté une instruction UPDATE pour mettre à jour le salaire du nouvel employé inséré. Étant donné que l'instruction INSERT ne prend pas directement en charge la définition du salaire, nous insérons d'abord l'employé, puis nous mettons à jour le salaire en fonction du nom et du service.

Maintenant, appelons la procédure insert_employee modifiée pour insérer un nouvel employé nommé "Charlie Brown" dans le service "Finance" avec un salaire de 60000,00 :

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

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

SELECT * FROM employees;

Vous devriez voir une nouvelle ligne dans la table avec le nom "Charlie Brown", le service "Finance" et le salaire de 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é insérées correctement. 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 étape finale, vous apprendrez à supprimer une procédure stockée de la base de données. Supprimer une procédure l'élimine de la base de données, ce qui la rend inutilisable pour l'exécution.

Tout d'abord, assurez-vous d'être connecté au serveur MySQL et d'utiliser la base de données testdb. Si vous n'êtes pas déjà connecté, utilisez les commandes suivantes :

mysql -u root -p

Entrez le mot de passe lorsque vous y êtes invité.

USE testdb;

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

DROP PROCEDURE [IF EXISTS] procedure_name;

La clause IF EXISTS est facultative mais recommandée. Elle empêche une erreur de se produire 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 :

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

Cette commande devrait retourner 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. Cela conclut l'exercice de laboratoire 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 dans la définition de la procédure.

Le laboratoire a également abordé la définition de paramètres d'entrée pour la procédure stockée, en spécifiant leurs noms et leurs 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.