Exemple de procédure stockée PostgreSQL : tutoriel

Introduction

Les procédures stockées jouent un rôle important dans PostgreSQL et offrent plusieurs avantages pour la gestion de base de données. Dans ce tutoriel, on va voir comment utiliser les procédures stockées dans PostgreSQL pour gérer les opérations SQL de façon plus efficace et plus sécurisée. Cet article s’adresse aux développeurs et aux administrateurs de bases de données qui veulent améliorer leurs compétences grâce aux fonctionnalités plus avancées de PostgreSQL.

Qu’est-ce qu’une procédure stockée ?

Avant de passer aux exemples, il est important de comprendre le concept de procédure stockée. Dans PostgreSQL, une procédure stockée est un objet exécutable précompilé qui contient une série de commandes SQL. Son rôle principal est d’effectuer des opérations sur la base de données, comme l’insertion, la mise à jour, la suppression et la création. Elles sont particulièrement utiles pour les opérations en plusieurs étapes ou à la logique complexe, car elles permettent une meilleure gestion des transactions et réduisent la vulnérabilité de la base de données.

Remarque : les exemples et commandes de ce tutoriel sont compatibles avec PostgreSQL 11 et les versions plus récentes.

Prérequis

Pour ce tutoriel, tu dois avoir installé et configuré les éléments suivants :

1. Une installation de PostgreSQL sur ta machine locale ou sur un serveur.
2. Un outil en ligne de commande ou un outil graphique comme Beekeeper Studio pour exécuter les commandes SQL.

Créer une procédure stockée dans PostgreSQL

Historiquement, PostgreSQL utilise le mot-clé FUNCTION pour définir des routines qui peuvent renvoyer une valeur. Les fonctions PostgreSQL peuvent effectuer des opérations et doivent renvoyer une valeur ou un ensemble, qui peut être une valeur scalaire ou un tableau. Elles peuvent aussi être utilisées dans des requêtes SQL.

PROCEDURE, introduit dans PostgreSQL 11, sert à définir des routines qui ne renvoient pas de valeur. Les procédures permettent d’effectuer des actions comme modifier l’état de la base de données sans renvoyer directement de données. On les appelle avec l’instruction CALL, contrairement aux fonctions, qui sont souvent appelées au sein d’une instruction SELECT.

Utiliser PROCEDURE pour mettre à jour un tableau

Commençons par créer une procédure stockée simple dans PostgreSQL. On va créer une procédure qui met à jour le salaire d’un employé.

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

Dans le code ci-dessus, update_employee_salary est le nom de la procédure, qui accepte deux paramètres : l’ID de l’employé (p_id) et le nouveau salaire (p_salary).

Appeler une procédure stockée dans PostgreSQL

Pour appeler la procédure qu’on vient de créer, on utilise la commande CALL comme ci-dessous :

CALL update_employee_salary(1, 50000);

La commande ci-dessus met à jour le salaire de l’employé ayant l’ID 1 à 50000.

Usage avancé : procédures avec logique conditionnelle

Les procédures peuvent aussi utiliser une logique plus complexe, comme des instructions conditionnelles et la gestion des erreurs, pour effectuer des opérations plus élaborées.

Cette procédure met à jour le département d’un employé selon les conditions fournies, et montre comment utiliser des structures de contrôle dans les procédures.

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

Résultat attendu:

CREATE PROCEDURE

Utiliser FUNCTION pour récupérer des données

Contrairement à PROCEDURE, une FUNCTION dans PostgreSQL convient mieux aux opérations où une valeur de retour est attendue. C’est particulièrement utile pour la récupération de données et les calculs qui doivent faire partie de requêtes SQL. Ici, on va créer un exemple de fonction qui récupère des informations selon des conditions précises.

Cette fonction récupère les détails d’un employé à partir de son ID. Elle montre comment les fonctions peuvent renvoyer des données utilisables directement dans des requêtes SQL.

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

Résultat attendu:

CREATE FUNCTION

Appeler la fonction

Pour récupérer des données avec cette FUNCTION, tu utilises l’instruction SELECT, comme pour interroger un tableau.

SELECT * FROM get_employee_details(1);

Résultat attendu:

Ici, le résultat sera les détails de l’employé ayant l’ID 1. Si l’employé existe, son prénom, son nom et l’ID de son département s’affichent. S’il n’existe pas, le résultat sera vide.

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

Conclusion

Dans ce tutoriel, on a vu comment écrire des procédures stockées dans PostgreSQL avec PROCEDURE et FUNCTION, et comment les appeler. Savoir utiliser les procédures stockées aide à mieux organiser son code, à renforcer la sécurité et à accélérer l’exécution. Ces exemples sont simples, mais les concepts restent les mêmes pour des opérations plus complexes.

Les procédures stockées dans PostgreSQL offrent un moyen robuste d’encapsuler la logique, pour des opérations efficaces et sécurisées. En les intégrant à la conception de ta base de données, tu obtiendras des applications plus faciles à maintenir et plus fiables. Sers-toi de ce guide comme point de départ pour explorer les nombreuses possibilités offertes par les capacités procédurales de PostgreSQL.