Présentation
Les procédures stockées MySQL regroupent plusieurs tâches en un seul et enregistrez 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.
Prérequis
- 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 pré-compilées Instructions SQL 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 invoquer des procédures stockées, vous pouvez utiliser le CALL déclaration 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 lesquelles la requête opère et renvoie des résultats.
Les procédures stockées peuvent également inclure le IF , CASE , et LOOP instructions de flux de contrôle 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 :
DELIMITER //
CREATE PROCEDURE procedure_name ( IN | OUT | INOUT parameter_name parameter_datatype (length), … )
BEGIN
SQL statements
END //
DELIMITER ;
Par défaut, la syntaxe est associée à la base de données utilisée, mais vous pouvez également utiliser la syntaxe d'une autre base de données en spécifiant le nom de la base de la manière suivante :database_name.procedure_name .
Ici, le premier DELIMITER l'argument définit le délimiteur par défaut sur // , tandis que le dernier DELIMITER l'argument le remet au point-virgule ; . Pour utiliser plusieurs instructions, spécifiez différents délimiteurs comme $$ .
Le nom de la procédure vient après le CREATE PROCEDURE argument. 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– Utilisez pour passer 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 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 deINetOUTparamètres. Le programme appelant passe l'argument et la procédure peut modifier leINOUTparamètre, en passant la nouvelle valeur au programme.
Par exemple :
Exécutez la procédure stockée en l'appelant :
CALL procedure_name;
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 lancer l'assistant.
Étape 2 : Spécifiez le nom de la procédure et entrez le code dans le BEGIN … FIN bloquer.
É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 : CALL 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 la liste de toutes les procédures stockées auxquelles vous avez accès, y compris leurs caractéristiques, utilisez la syntaxe suivante :
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
Le SHOW PROCEDURE STATUS L'instruction 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.
Le LIKE L'argument 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 :
Le WHERE L'argument vous permet de lister 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 'liste_clients ’ base de données.
2. Utilisez le dictionnaire de données
Le schéma d'information la base de données 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 voir toutes les procédures stockées d'une base de données :
SELECT
routine_name
FROM
information_schema.routines
WHERE
routine_type = 'PROCEDURE'
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 le Navigateur rubrique.
Étape 2 :Développez les procédures stockées élément déroulant.
Cet élément affiche toutes les procédures stockées pour la base de données actuelle.
Modifier la procédure stockée
Modifier une procédure stockée signifie changer les caractéristiques d'une procédure. Il n'y a aucune déclaration 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
Modifier une caractéristique de procédure en utilisant le ALTER PROCEDURE déclaration. Par exemple, nous pouvons ajouter un commentaire à une procédure que nous avons créée précédemment. La syntaxe est :
ALTER PROCEDURE procedure_name
COMMENT 'Insert comment here';
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 drops la procédure stockée existante et crée un nouveau après que les modifications aient été 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'option Modifier la procédure stockée… élément.
É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 - suppression de la procédure stockée existante et création d'une nouvelle contenant les modifications.
Cliquez sur Appliquer puis 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
Supprimer une procédure stockée du serveur en utilisant la DROP PROCEDURE déclaration.
La syntaxe de base est :
DROP PROCEDURE [IF EXISTS] stored_procedure_name;
Le IF EXISTS supprime la procédure stockée uniquement si elle existe sur le serveur. Saisissez le nom de la procédure stockée à la place de stored_procedure_name syntaxe.
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.
Suppression d'une procédure inexistante sans le IF EXISTS paramètre 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. Faites un clic 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 Abandonner maintenant 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 :
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. Il s'agit d'un défi pour les nouveaux développeurs et entraîne des coûts de maintenance supplémentaires.