Introduction
Les procédures stockées MySQL regroupent plusieurs tâches en une seule et enregistrent la tâche sur le serveur pour une utilisation future.
Les procédures stockées simplifient la gestion des bases de données et réduisent le trafic réseau. Par exemple, l’envoi d’une requête au serveur MySQL traite la requête et renvoie les résultats. L’utilisation de procédures stockées enregistre les requêtes sur le serveur afin qu’elles puissent être exécutées ultérieurement.
Dans ce didacticiel, vous apprendrez à créer, répertorier, modifier et supprimer des procédures stockées.
Conditions préalables
- MySQL Server et MySQL Workbench installés
- Un compte utilisateur MySQL avec des privilèges root
Que Sont Les Procédures Stockées Dans MySQL ?
Les procédures stockées MySQL sont des instructions SQL pré-compilées stockées dans une base de données. Ce sont des sous-routines contenant un nom, une liste de paramètres et des instructions SQL.
Tous les systèmes de bases de données relationnelles prennent en charge les procédures stockées et ne nécessitent aucun package d’environnement d’exécution supplémentaire.
Comment Utiliser Les Procédures Stockées ?
Pour appeler des procédures stockées, vous pouvez utiliser l’ instruction CALL ou d’autres procédures stockées. La première fois qu’une procédure stockée est invoquée, MySQL la recherche dans le catalogue de la base de données, compile le code, le place dans la mémoire cache et l’exécute.
Les exécutions suivantes dans la même session exécutent des procédures stockées à partir de la mémoire cache, ce qui les rend extrêmement utiles pour les tâches répétitives.
Les procédures stockées utilisent des paramètres pour transmettre des valeurs et personnaliser les résultats. Les paramètres sont utilisés pour spécifier les colonnes d’une table dans laquelle la requête opère et renvoie des résultats.
Les procédures stockées peuvent également inclure les instructions de flux de contrôle IF , CASE et LOOP qui implémentent le code de manière procédurale.
Créer Une Procédure Stockée
Créez une procédure stockée de deux manières :
1. Utilisez MySQL Shell
Utilisez la syntaxe suivante pour créer une procédure stockée dans MySQL :
DÉLIMITEUR // CREATE PROCEDURE nom_procédure ( IN | OUT | INOUT nom_paramètre type_données_paramètre (longueur), … ) COMMENCER Instructions SQL FIN // DELIMITER ;
Ici, le premier argument DELIMITER définit le délimiteur par défaut sur // , tandis que le dernier argument DELIMITER le redéfinit sur le point-virgule ; . Pour utiliser plusieurs instructions, spécifiez différents délimiteurs comme €€ .
Le nom de la procédure vient après l’ argument CREATE PROCEDURE . Après le nom de la procédure, utilisez des parenthèses pour spécifier les paramètres à utiliser dans la procédure, le nom du paramètre, le type de données et la longueur des données. Séparez chaque paramètre par une virgule.
Les modes de paramètres sont :
- IN – Sert à transmettre un paramètre en entrée. Lorsqu’elle est définie, la requête passe un argument à la procédure stockée. La valeur du paramètre est toujours protégée.
- OUT – Utilisez pour transmettre un paramètre en tant que sortie. Vous pouvez modifier la valeur dans la procédure stockée et la nouvelle valeur est renvoyée au programme appelant.
- INOUT – Une combinaison de paramètres IN et OUT . Le programme appelant transmet l’argument et la procédure peut modifier le paramètre INOUT , en transmettant la nouvelle valeur au programme.
Par exemple:
Exécutez la procédure stockée en l’appelant :
CALL nom_procédure ;
La requête renvoie des résultats pour la procédure stockée.
2. Utilisez MySQL Workbench
Une autre façon de créer une procédure stockée consiste à utiliser l’assistant MySQL Workbench. L’assistant est intuitif et simplifie le processus puisque vous n’avez pas à placer de délimiteurs ni à vous soucier du format.
Suivez ces étapes:
Étape 1 : Cliquez avec le bouton droit sur Procédures stockées dans la fenêtre Navigateur de MySQL Workbench et choisissez Créer une procédure stockée… pour démarrer l’assistant.
Étape 2 : Spécifiez le nom de la procédure et entrez le code dans le bloc BEGIN … END .
Étape 3 : Vérifiez le code et cliquez sur Appliquer .
Étape 4 : Confirmez l’exécution en cliquant sur Appliquer et créez la procédure en cliquant sur Terminer .
Étape 5 : Exécutez la procédure pour voir si cela fonctionne. Créez un nouvel onglet SQL pour exécuter des requêtes.
Étape 6 : APPELEZ la procédure dans l’onglet SQL et cliquez sur Exécuter .
Si aucune erreur ne revient, MySQL exécute la procédure stockée et affiche les résultats.
Répertorier Les Procédures Stockées
Il existe trois façons d’afficher une liste de toutes les procédures stockées :
1. Utilisez MySQL Shell
Pour obtenir une liste de toutes les procédures stockées auxquelles vous avez accès, y compris leurs caractéristiques, utilisez la syntaxe suivante :
AFFICHER L'ÉTAT DE LA PROCÉDURE
L’ instruction SHOW PROCEDURE STATUS renvoie une longue sortie. L’instruction affiche les noms et les caractéristiques des procédures stockées auxquelles vous avez accès sur le serveur.
Faites défiler la sortie pour trouver les procédures actuellement sur le serveur.
L’ argument LIKE recherche les procédures stockées contenant un mot spécifique dans leur nom. Utilisez % pour remplacer n’importe quel nombre de caractères, y compris zéro.
Par exemple:
L’ argument WHERE vous permet de répertorier les procédures stockées uniquement dans une base de données particulière.
Par exemple:
Dans cet exemple, l’instruction renvoie uniquement les procédures stockées pour la base de données ‘ customer_list ‘.
2. Utilisez le dictionnaire de données
La base de données information_schema contient une table appelée routines , qui contient des informations sur les procédures stockées et les fonctions liées à toutes les bases de données sur le serveur MySQL actuel.
Utilisez la syntaxe suivante pour afficher toutes les procédures stockées d’une base de données :
SÉLECTIONNER nom_routine DE information_schema.routines OÙ type_routine = 'PROCÉDURE' AND routine_schema = 'database_name' ;
3. Utilisez MySQL Workbench
Pour une approche graphique de l’affichage des procédures stockées, utilisez MySQL Workbench. Suivez ces étapes pour voir les procédures stockées :
Étape 1 : Double-cliquez sur la base de données que vous souhaitez utiliser dans la section Navigateur .
Étape 2 : Développez l’ élément déroulant Procédures stockées .
Cet élément affiche toutes les procédures stockées pour la base de données en cours.
Modifier La Procédure Stockée
Modifier une procédure stockée signifie modifier les caractéristiques d’une procédure. Il n’y a pas d’instruction dans MySQL pour modifier les paramètres ou le corps d’une procédure stockée. Pour modifier les paramètres ou le corps, supprimez la procédure stockée et créez-en une nouvelle.
Modifier une procédure stockée de deux manières :
1. Utilisez MySQL Shell
Modifiez une caractéristique de procédure à l’aide de l’ instruction ALTER PROCEDURE . Par exemple, nous pouvons ajouter un commentaire à une procédure que nous avons créée précédemment. La syntaxe est :
ALTER PROCEDURE nom_procédure COMMENT 'Insérer un commentaire ici';
2. Utilisez MySQL Workbench
L’interface graphique de MySQL Workbench permet aux utilisateurs de modifier une procédure stockée où les utilisateurs peuvent ajouter des paramètres ou modifier le code. MySQL Workbench supprime la procédure stockée existante et en crée une nouvelle une fois les modifications apportées.
Suivez ces étapes:
Étape 1 : Dans la section Navigateur, cliquez avec le bouton droit sur la procédure stockée que vous souhaitez modifier. Sélectionnez l’ item Modifier la procédure mémorisée… .
Étape 2 : lorsque l’onglet s’ouvre, apportez les modifications souhaitées à la procédure stockée existante et cliquez sur Appliquer .
Étape 3 : Une fenêtre de révision du script SQL apparaît, montrant le processus – en supprimant la procédure stockée existante et en en créant une nouvelle contenant les modifications.
Cliquez sur Appliquer puis sur Terminer dans la fenêtre suivante pour exécuter le script.
Supprimer La Procédure Stockée
Pour déposer (supprimer) une procédure :
1. Utilisez MySQL Shell
Supprimez une procédure stockée du serveur à l’aide de l’ instruction DROP PROCEDURE .
La syntaxe de base est :
DROP PROCEDURE nom_procédure_stockée ;
Le paramètre IF EXISTS supprime la procédure stockée uniquement si elle existe sur le serveur. Entrez le nom de la procédure stockée à la place de la syntaxe nom_procédure_stockée .
Par exemple:
Puisqu’il n’y a pas de procédure nommée ‘ test ‘ sur le serveur, la sortie indique que 0 lignes ont été affectées et que la procédure spécifiée n’existe pas.
La suppression d’une procédure inexistante sans le paramètre IF EXISTS renvoie une erreur.
2. Utilisez MySQL Workbench
Pour supprimer une procédure stockée avec MySQL Workbench, procédez comme suit :
Étape 1 : Développez l’élément Procédures stockées dans la section Navigateur. Cliquez avec le bouton droit sur la procédure stockée que vous souhaitez supprimer et choisissez Supprimer la procédure stockée… dans le menu contextuel.
Étape 2 : Dans la fenêtre de confirmation, cliquez sur Drop Now pour supprimer la procédure stockée.
Cette action supprime définitivement la procédure.
Avantages Et Inconvénients Des Procédures Stockées MySQL
Les procédures stockées présentent plusieurs avantages et inconvénients car elles s’adaptent à des besoins spécifiques. Vous trouverez ci-dessous certains des avantages et des inconvénients.
Avantages De L’utilisation De Procédures Stockées
Les avantages des procédures stockées sont :
Réduction du trafic réseau
Les procédures stockées permettent de réduire le trafic réseau entre les applications et MySQL Server en conservant toute la logique de programmation sur le serveur. Au lieu d’envoyer plusieurs résultats de requête sur le réseau, les applications n’envoient que le nom de la procédure et l’entrée du paramètre.
Sécurité améliorée
L’administrateur de la base de données accorde aux applications des privilèges pour appeler et accéder uniquement à des procédures stockées spécifiques sans leur donner un accès direct aux tables. Les procédures stockées aident à prévenir les attaques par injection de script puisque les paramètres d’entrée sont traités comme des valeurs et non comme du code exécutable.
Logique métier centralisée
Les procédures stockées encapsulent la logique métier réutilisable par plusieurs applications. Cela aide à réduire la duplication de la même logique dans de nombreuses applications différentes et rend la base de données plus cohérente.
Inconvénients De L’utilisation De Procédures Stockées
Les inconvénients des procédures stockées sont :
L’utilisation des ressources
L’utilisation de nombreuses procédures stockées et opérations logiques entraîne une augmentation significative de l’utilisation de la mémoire et du processeur pour chaque connexion.
Pas de portabilité
Il n’est pas facile de transférer des procédures stockées écrites dans un langage spécifique d’une installation à une autre. S’appuyer sur une procédure stockée lie également l’utilisateur à une base de données particulière.
Dépannage et test
MySQL ne fournit pas d’utilitaires pour tester et déboguer les procédures stockées, il peut donc être difficile de les déboguer. Le développement et la maintenance de procédures stockées nécessitent des connaissances approfondies. C’est un défi pour les nouveaux développeurs et cela entraîne des coûts de maintenance supplémentaires.
Conclusion
Après avoir lu cet article, vous savez ce que sont les procédures stockées et quand les utiliser. Vous savez également comment créer, modifier, voir toutes les procédures stockées disponibles et supprimer celles dont vous n’avez plus besoin.
Cet article a-t-il été utile?
Oui Non
