Les procédures stockées aident à optimiser la base de données et à améliorer la réutilisabilité. Au lieu d'avoir à exécuter la même requête, vous pouvez créer la tâche sous forme de procédure stockée que vous appellerez chaque fois que nécessaire. Vous apprendrez tout sur les procédures stockées à la fin de cet article.
Travailler avec des procédures stockées dans PostgreSQL
En tant qu'utilisateur de PostgreSQL, vous avez peut-être remarqué que les fonctions PostgreSQL n'exécutent pas de transactions. Bien qu’il soit possible de créer une transaction, il n’est pas possible de la valider ou de la restaurer à l’état précédent. Cependant, ces limitations sont contournées à l'aide de procédures stockées.
Voici la syntaxe de base pour créer une procédure stockée dans PostgreSQL :
CRÉER OU REMPLACER UNE PROCÉDURE nom_procédure(
paramètre[s] type_données
)
LANGUE plpsql ;
COMME $$
DÉCLARER
variables_if_any type_données
COMMENCER
logique
FIN;
$$
Les éléments clés à noter dans la syntaxe donnée sont le « nom_procédure » qui est le nom que vous utiliserez pour la procédure stockée, les paramètres que vous souhaitez inclure et leurs types de données, et la logique qui sont principalement les instructions SQL.
Donnons trois exemples pour vous aider à comprendre comment créer les procédures stockées dans PostgreSQL.
Exemple 1 : une procédure stockée pour calculer le carré d'un nombre
Pour notre premier exemple, nous créons une procédure stockée qui utilise l'instruction « RAISE NOTICE » comme moyen d'imprimer la sortie sur le terminal. La procédure stockée prend la valeur entière que vous lui donnez lors de son appel et calcule son carré.
Voici comment nous créons la procédure stockée :
Nous nommons notre paramètre « num1 », et c'est un entier. Pour la partie logique, nous définissons comment il obtient le carré de « num1 » et le stocke comme variable carrée. Lorsque nous exécutons la commande, nous obtenons la sortie « CREATE PROCEDURE » qui confirme que nous avons réussi à créer la procédure stockée avec succès.
La tâche suivante consiste à appeler la procédure et à lui donner l'argument attendu.
CALL nom_procédure (arguments);Vous obtiendrez la sortie CALL indiquant que la procédure stockée a été exécutée et nous obtiendrons la sortie attendue qui, dans ce cas, est le carré de l'argument que nous avons ajouté.
Exemple 2 : une procédure stockée pour insérer les valeurs dans une entrée de table
Les deux exemples suivants montrent comment créer une procédure stockée qui fonctionne avec une table de base de données. Créons rapidement la table « étudiants » avec laquelle nous allons travailler.
Pour cet exemple, nous créons une procédure stockée qui permet à un utilisateur d'insérer les valeurs dans la table nouvellement créée. Remarquez comment nous spécifions les paramètres que nous espérons ajouter comme arguments lorsque nous appelons la procédure stockée. De plus, nous définissons la logique qui prend les arguments ajoutés et exécute une instruction INSERT SQL dans la table « étudiants ».
Nous pouvons vérifier les procédures stockées disponibles en exécutant la commande suivante :
\dfLa première procédure stockée que nous pouvons voir dans la sortie suivante est le « add_student » que nous avons créé précédemment.
Maintenant, appelons la procédure stockée pour l'exécuter. L'image suivante montre comment nous avons une table vide, mais nous avons appelé la procédure stockée pour ajouter le premier étudiant :
Si nous répertorions les valeurs dans notre tableau, remarquez comment les arguments que nous avons ajoutés avec la commande call procedure sont les valeurs de notre premier étudiant dans notre tableau. C'est ainsi que vous créez une procédure stockée pour insérer les valeurs dans une table.
Notez que lors de la création de la procédure stockée, les paramètres que vous spécifiez doivent correspondre à ce qui est attendu dans votre table pour éviter les erreurs. De plus, le type de données doit correspondre.
Exemple 3 : une procédure stockée pour mettre à jour une entrée de table
Continuons, créons une autre procédure stockée qui met à jour une entrée de table. Si vous souhaitez disposer d'un moyen rapide de mettre à jour les valeurs de notre table, vous pouvez créer une procédure stockée de mise à jour comme suit :
Spécifiez la colonne que vous souhaitez mettre à jour à l'aide du mot-clé WHERE et la nouvelle valeur à l'aide du mot-clé SET. Vous devez ensuite ajouter le mot-clé COMMIT pour conserver les modifications.
Appelons la procédure stockée update et ajoutons les arguments attendus : « student_id » et le nouveau cours.
Si nous répertorions les entrées dans notre tableau, nous pouvons vérifier que nous disposons du cours mis à jour pour l'étudiant particulier que nous avons ciblé. C’est ainsi que fonctionne une procédure stockée de mise à jour.
Conclusion
Vous pouvez créer n'importe quelle procédure stockée dans PostgreSQL. Il vous suffit de comprendre la syntaxe à suivre puis de définir votre logique pour la procédure stockée. À partir de là, appelez la procédure stockée et vérifiez qu'elle s'est exécutée comme prévu. Cet article expliquait les procédures stockées dans PostgreSQL et fournissait des exemples sur la façon de les créer.