Procédures stockées dans MySQL : comment les utiliser ?

Informatec Digital » Base de données » Procédures stockées dans MySQL : comment les utiliser ?

Les procédures stockées sont des routines stockées dans la base de données MySQL qui encapsulent les opérations SQL et la logique de programmation. Ils agissent comme des mini-programmes qui peuvent être invoqués pour effectuer des tâches répétitives ou complexes.

Ils offrent de multiples avantages tels que la modularité, la réutilisation du code, une plus grande sécurité et de meilleures performances. Par conséquent, la maîtrise des procédures stockées est essentielle pour tout développeur ou administrateur MySQL.

Ci-dessous, nous verrons ce qu'ils sont exactement, leurs avantages, comment créer et utiliser des procédures stockées dans MySQL étape par étape et quelques exemples pratiques pour que vous puissiez en tirer le meilleur parti.

Que sont les procédures stockées dans MySQL ?

Les procédures stockées sont des ensembles de instructions SQL précompilées qui sont stockés dans la base de données MySQL avec un nom et des paramètres d'entrée et de sortie.

Ils peuvent être invoqués par leur nom en passant des valeurs pour leurs paramètres. Lorsqu'ils sont exécutés, ils exécutent les opérations définies en leur sein, telles que les requêtes, les mises à jour, la logique de contrôle de flux, etc.

Ils sont comme des mini-programmes résidant dans la base de données qui encapsulent des opérations complexes pour :

• Simplifier les tâches administratives répétitives
• Centraliser la logique métier sur le serveur
• Réduire le trafic client/serveur
• Améliorer les performances lors de la précompilation des requêtes

Lorsqu'ils sont stockés sur le serveur, ils offrent des fonctionnalités intéressantes telles que :

• Modularité: Ils décomposent la logique en morceaux gérables.
• Réutilisation: Éliminez la duplication de code.
• Sécurité: Autorisations au niveau de la procédure.
• Logique cachée : Le client ne voit pas les détails internes.

En bref, les procédures stockées sont un outil très utile pour les développeurs MySQL. Voyons plus en détail ses principaux avantages.

Avantages de l'utilisation de procédures stockées

Les procédures stockées offrent des avantages importants :

1. Meilleure organisation et maintenance du code

Ils permettent de modulariser des opérations complexes en parties distinctes et facilement gérables. Le code peut être réutilisé à partir de plusieurs endroits en appelant la procédure stockée.

Cela conduit à un code plus organisé et plus facile à maintenir avec moins de duplication.

2. Réduction du trafic entre le client et le serveur

En étant stocké sur le serveur, le transfert d'informations est réduit puisqu'il n'est pas nécessaire d'envoyer des requêtes SQL à chaque fois. Cela améliore les performances.

3. Opérations et transactions plus rapides

MySQL ne compile la procédure qu'une seule fois et la réutilise. Cela accélère l’exécution en éliminant le besoin de recompiler à chaque fois.

De plus, ils peuvent être utilisés pour encapsuler des transactions complexes qui s'exécuteront plus rapidement.

4. Sécurité et autorisations renforcées

Les autorisations et privilèges peuvent être attribués au niveau de la procédure stockée, plutôt que de donner un accès complet aux tables de base de données.

Cela permet un meilleur contrôle sur les données et les opérations que les utilisateurs peuvent effectuer.

5. Facile à utiliser et à entretenir par d'autres développeurs

Comme ils sont partagés dans la base de données, tout développeur disposant des autorisations peut exécuter les procédures sans connaître leur code interne. Pour approfondir la gestion des autorisations, consultez utilisateurs dans MySQL.

Cela facilite la maintenance et réduit les erreurs.

6. Portabilité entre différentes applications

Étant donné que sa logique est contenue dans la base de données, elle peut être invoquée à partir de n’importe quel langage de programmation qui la prend en charge, y compris Créer des fonctions dans MySQL.

Comment créer des procédures stockées dans MySQL

Créer une procédure stockée dans MySQL est simple en utilisant la syntaxe SQL :

CRÉER UNE PROCÉDURE procedure_name()
COMMENCER
instructions;
FIN

La procédure est créée avec un nom unique et entre BEGIN – END nous placerons les instructions SQL à exécuter lorsqu'elle est invoquée.

Par exemple, cette procédure enregistre une nouvelle vente :

CRÉER LA PROCÉDURE insert_sale(
p_value FLOAT,
p_client VARCHAR(50)
)
COMMENCER
INSÉRER DANS ventes(valeur, client)
VALEURS(p_valeur, p_client);
FIN

Cette procédure reçoit les paramètres p_value et p_customer, insère une nouvelle vente dans la table des ventes et renvoie l'ID généré automatiquement. Si vous souhaitez approfondir vos connaissances en matière de gestion des données, consultez Requêtes MySQL en PHP avec exemples.

Les procédures prennent également en charge du code plus avancé tel que les variables locales, les curseurs, la gestion des erreurs avec BEGIN-EXCEPTION-END, etc.

Comment utiliser les procédures stockées dans MySQL

Une fois la procédure stockée créée, elle peut être appelée dans n'importe quelle requête SQL à l'aide de CALL :

APPELER procedure_name(parameter_value1, parameter_value2);

Par exemple, pour insérer une vente :

APPELER insert_sale(125.50, 'Juan Perez');

Les procédures peuvent également être invoquées à partir de n’importe quel langage de programmation en passant des valeurs aux paramètres. À titre d’exemple, en PHP :

$result = $mysqli->query("APPEL insert_sale(125.50, 'Juan Perez')");

Et depuis Java :

CallableStatement stmt = conn.prepareCall("{CALL insert_sale(?, ?)}");
stmt.setDouble(1, 125.50);
stmt.setString(2, "John Perez");
stmt.executeUpdate();

Exemples de procédures stockées dans MySQL

Voyons quelques exemples pratiques pour mieux exploiter cette puissante fonctionnalité MySQL :

Obtenez les ventes totales par client

CRÉER LA PROCÉDURE total_sales_customer(IN p_customer VARCHAR(50))
COMMENCER
SÉLECTIONNEZ SOMME(valeur) COMME total
DES ventes
OÙ client = p_client;
FIN

Utilisation:

APPELER total_sales_customer('Ana Valdez');

Lister les produits sous un prix donné

CRÉER LA PROCÉDURE cheap_products(IN p_price DECIMAL(10,2))
COMMENCER
SÉLECTIONNEZ l'identifiant, le nom et le prix parmi les produits
OÙ prix <= p_price ;
FIN

Utilisation:

APPELER cheap_products(500);

Supprimer et réinsérer des enregistrements

CRÉER LA PROCÉDURE regenerate_table()
COMMENCER
SUPPRIMER DE ma_table;
— Insérer de nouveaux enregistrements
FIN;

Pratique pour régénérer les tables de test. Pour plus d'informations sur la structure de la base de données, visitez éléments de base de données.

Appeler une autre procédure interne

CRÉER UNE PROCÉDURE ma_procédure()
COMMENCER
APPELER autre_procédure();

— plus d'instructions
FIN;

Permet de décomposer en pièces réutilisables.

Transférer des fonds entre des comptes

CRÉER UNE PROCÉDURE transfert(
p_cuentaorigen INT,
p_cuentaDestino INT,
p_montant décimal(10,2)
)
COMMENCER
COMMENCER LA TRANSACTION;

METTRE À JOUR les comptes
SET solde = solde – p_montant
OÙ id = p_cuentaOrigen;

METTRE À JOUR les comptes
SET solde = solde + p_montant
OÙ id = p_cuentaDestino;

COMMIT;
FIN;

Encapsule la logique de transfert dans une transaction.

Conclusions sur les procédures stockées

Les procédures stockées sont une fonctionnalité très puissante de MySQL que chaque développeur devrait maîtriser. Pour approfondir d'autres fonctionnalités connexes, consultez bases de données relationnelles.

Ils permettent de modulariser des opérations complexes, de réutiliser du code, de réduire le trafic entre le client et le serveur, d'améliorer les performances et la sécurité.

En plus des procédures stockées, MySQL offre une variété de fonctions qui sont également essentielles pour tout développeur. Ces fonctionnalités permettent de réaliser des opérations spécifiques et complexes de manière efficace et modulaire. Certaines des fonctions les plus couramment utilisées dans MySQL incluent « CONCAT » pour concaténer des chaînes, « SUBSTRING » pour extraire des sous-chaînes et des fonctions mathématiques comme « ROUND » et « CEIL » pour arrondir. et vous pouvez aussi