Syntaxe:

Création d'une procédure stockée:

CREATE [definer = {utilisateur|utilisateur_actuel}] PROCEDURE nom_proc ([ param_proc [,param_proc ...]]) [ caractéristiques..] corps_procédure

Où:

param_proc : [IN|OUT|INOUT] nom_paramètre type

IN représente un paramètre d'entrée, OUT un paramètre de sortie, INOUT un paramètre à la fois d'entrée et de sortie; nom_paramètre est le nom du paramètre; type est le type de données du paramètre

Le corps d'une procédure stockée peut utiliser des structures composites, des contrôles de flux, des instructions SQL, des variables personnalisées, etc., similaires aux fonctions définies par l'utilisateur (UDF). Pour plus de détails, reportez-vous à l'article sur l'utilisation des fonctions personnalisées MySQL - Structures composites, variables personnalisées et contrôles de flux.

Appel d'une procédure stockée:

CALL nom_proc ([ param_proc [,param_proc ...]])

CALL nom_proc

Remarque: sans paramètres, on peut omettre les "()", mais avec paramètres, les "()" ne peuvent pas être omis.

Modification d'une procédure stockée:

L'instruction ALTER permet de modifier uniquement des éléments non essentiels comme les commentaires d'une procédure stockée, mais pas son corps. Pour modifier une procédure, il faut donc la supprimer et la recréer!

Suppression d'une procédure stockée:

DROP PROCEDURE [IF EXISTS] nom_proc

Exemples:

Création d'une procédure sans paramètres:

delimiter //
CREATE PROCEDURE afficheDate()
BEGIN
SELECT NOW();
END//
delimiter ;

CALL afficheDate();

Fonction: affiche la date et l'heure actuelles, sans grande utilité pratique

Création d'une procédure avec paramètres:

Avec un seul paramètre IN

delimiter //
CREATE PROCEDURE rechercheParId(IN identifiant SMALLINT UNSIGNED)
BEGIN
SELECT * FROM utilisateurs WHERE id = identifiant;
END//
delimiter ;

CALL rechercheParId(2);

Avec un paramètre IN et un paramètre OUT

delimiter //
CREATE PROCEDURE supprimeParId(IN identifiant SMALLINT UNSIGNED, OUT nbLignes SMALLINT UNSIGNED)
BEGIN
DELETE FROM utilisateurs WHERE id = identifiant;
SELECT ROW_COUNT() INTO nbLignes;
END//
delimiter ;

CALL supprimeParId(2,@lignesModifiees);
SELECT @lignesModifiees;

Explication: création de la procédure supprimeParId avec un paramètre IN et un paramètre OUT. À l'appel, on fournit l'ID à supprimer et une variable utilisateur @lignesModifiees pour stocker le nombre de lignes effectées. L'instruction SELECT @lignesModifiees; affiche ce nombre.

<strong>Différences entre procédures stockées et fonctions personnalisées:</strong>

Les procédures stockées implémentent des logiques plus complexes, tandis que les fonctions sont plus spécialisées;
Les procédures peuvent retourner plusieurs valeurs, alors que les fonctions personnalisées ne peuvent en retourner qu'une seule;
Les procédures sont généralement exécutées de manière indépendante, tandis que les fonctions sont souvent utilisées comme partie intégrante d'autres instructions SQL;

<strong>Avantages des procédures stockées:</strong>
Les procédures stockées permettent d'encapsuler les instructions SQL ou les logiques métier fréquemment utilisées, de les précompiler et de les stocker dans la base de données. Lorsqu'elles sont nécessaires, elles sont appelées directement depuis la base de données, évitant ainsi le processus de compilation.
Elles améliorent la vitesse d'exécution;
Elles réduisent le volume de données transférées sur le réseau (est-ce plus rapide d'envoyer un lot de code SQL ou simplement le nom d'une procédure stockée et quelques paramètres?);