420-5D3 : BD – Procédure stockée

1 octobre 2023

Les procédures stockées (ou « stored procedures » en anglais) sous Oracle sont des objets de base de données qui permettent de regrouper un ensemble d’instructions SQL en une seule unité logique et réutilisable. Elles sont utilisées pour automatiser des tâches complexes, effectuer des opérations sur la base de données, et encapsuler la logique métier au sein de la base de données. Les procédures stockées offrent plusieurs avantages, notamment la réutilisabilité, la modularité, la sécurité et la facilité de maintenance.

Voici le rôle principal des procédures stockées dans Oracle :

Réutilisabilité : Une fois créées, les procédures stockées peuvent être appelées à plusieurs reprises par différentes parties de votre application ou par d’autres procédures et fonctions. Cela évite la duplication de code et facilite la maintenance.
Modularité : Les procédures stockées permettent de découper la logique métier en modules indépendants. Chaque procédure peut accomplir une tâche spécifique, ce qui rend le code plus organisé et plus facile à comprendre.
Performance : Les procédures stockées peuvent améliorer les performances en réduisant la latence du réseau. Lorsqu’une procédure stockée est appelée depuis une application, les données restent dans la base de données, ce qui réduit les transferts de données entre le client et le serveur.
Sécurité : Les procédures stockées peuvent être sécurisées en limitant les autorisations d’accès direct aux tables sous-jacentes. Les utilisateurs n’ont besoin que d’autorisations pour exécuter la procédure, ce qui renforce la sécurité des données.

Syntaxe

La syntaxe de base des procédures stockées dans Oracle suit un modèle standard, bien que les détails puissent varier en fonction des besoins spécifiques de votre procédure. Voici la structure de base d’une procédure stockée Oracle :

CREATE OR REPLACE PROCEDURE nom_de_la_procedure (
  parametre1 type_de_donnee1,
  parametre2 type_de_donnee2,
  ...
)
AS
  -- Déclarations de variables locales
  variable1 type_de_donnee1;
  variable2 type_de_donnee2;
  ...

BEGIN
  -- Logique de la procédure
  -- Utilisation des paramètres et des variables locales

  -- Instructions SQL
  -- Manipulation des données, mises à jour, insertions, etc.

  -- Gestion des exceptions (facultatif)
  -- Utilisation de blocs TRY...EXCEPTION pour gérer les erreurs

  -- COMMIT ou ROLLBACK (facultatif)
  -- Pour valider ou annuler la transaction

EXCEPTION
  WHEN nom_de_l_exception THEN
    -- Traitement des exceptions

END nom_de_la_procedure;
/

Explications des éléments clés de la syntaxe :

CREATE OR REPLACE PROCEDURE: Cette clause indique que vous créez ou remplacez une procédure stockée. Si la procédure existe déjà avec le même nom, elle sera remplacée par la nouvelle définition.
nom_de_la_procedure: C’est le nom que vous donnez à votre procédure. Assurez-vous de choisir un nom descriptif et unique.
(parametre1 type_de_donnee1, parametre2 type_de_donnee2, ...): Vous pouvez spécifier des paramètres en entrée pour votre procédure. Chaque paramètre est suivi de son type de données. Ces paramètres permettent à la procédure d’accepter des valeurs en entrée.
AS: Cette clause marque le début du corps de la procédure. C’est là que vous définissez les variables locales et commencez la logique de la procédure.
BEGIN: C’est le début du bloc de code où vous définissez la logique de la procédure. C’est ici que vous effectuez des opérations SQL, utilisez des paramètres et des variables locales, gérez les exceptions, et définissez le comportement de la procédure.
EXCEPTION: Cette section est utilisée pour gérer les exceptions qui peuvent survenir pendant l’exécution de la procédure. Vous pouvez spécifier différentes exceptions et définir comment elles doivent être gérées.
COMMIT ou ROLLBACK (facultatif) : Vous pouvez utiliser ces commandes pour valider ou annuler la transaction en cours, en fonction du résultat de la procédure.

Notez que la syntaxe de base des procédures stockées peut être étendue pour inclure des fonctionnalités plus avancées, telles que des boucles, des structures de contrôle conditionnelles, des transactions, des requêtes SQL complexes, etc. Cependant, la structure de base décrite ci-dessus vous permet de créer des procédures stockées simples et fonctionnelles.

Voici un exemple simple de création et d’utilisation d’une procédure stockée dans Oracle :

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

Supposons que vous ayez une table « Employes » contenant des données sur les employés, et vous souhaitez créer une procédure stockée pour ajouter un nouvel employé à la base de données. Voici comment vous pouvez créer une telle procédure :

CREATE OR REPLACE PROCEDURE AjouterEmploye (
  p_nom VARCHAR2,
  p_prenom VARCHAR2,
  p_salaire NUMBER
)
AS
BEGIN
  INSERT INTO Employes (Nom, Prenom, Salaire)
  VALUES (p_nom, p_prenom, p_salaire);
  COMMIT;
END AjouterEmploye;

Appel de la procédure stockée :

Vous pouvez ensuite appeler la procédure stockée pour ajouter un employé :

BEGIN
  AjouterEmploye('Doe', 'John', 50000);
END;

Dans cet exemple, la procédure stockée « AjouterEmploye » prend les informations d’un nouvel employé (nom, prénom et salaire) en tant que paramètres d’entrée et les insère dans la table « Employes ». La commande COMMIT assure que la transaction est validée.

Les procédures stockées peuvent devenir beaucoup plus complexes que cet exemple simple, et elles peuvent inclure des structures de contrôle, des transactions, des gestionnaires d’exceptions, et bien d’autres fonctionnalités pour gérer des tâches plus avancées.

Autres exemples d’utilisation

Mise à jour du salaire de tous les employés d’un département : Cette procédure met à jour le salaire de tous les employés d’un département spécifique en fonction d’un pourcentage donné.

   CREATE OR REPLACE PROCEDURE MettreAJourSalaireDepartement (
     p_departement_id NUMBER,
     p_pourcentage_augmentation NUMBER
   )
   AS
   BEGIN
     UPDATE Employes
     SET Salaire = Salaire * (1 + p_pourcentage_augmentation / 100)
     WHERE IDDepartement = p_departement_id;
     COMMIT;
   END MettreAJourSalaireDepartement;

Utilisation :

   BEGIN
     MettreAJourSalaireDepartement(1, 5); -- 1 est l'ID du département "Vente", 5% d'augmentation
   END;

Suppression d’un employé par ID : Cette procédure permet de supprimer un employé en spécifiant son ID.

   CREATE OR REPLACE PROCEDURE SupprimerEmployeParID (p_employe_id NUMBER)
   AS
   BEGIN
     DELETE FROM Employes WHERE ID = p_employe_id;
     COMMIT;


   END SupprimerEmployeParID;

Utilisation :

   BEGIN
     SupprimerEmployeParID(101);
   END;

Calcul de la prime annuelle pour tous les employés : Cette procédure calcule la prime annuelle pour tous les employés en fonction de leur salaire et de la performance de l’entreprise.

   CREATE OR REPLACE PROCEDURE CalculerPrimeAnnuelle
   AS
   BEGIN
     UPDATE Employes
     SET PrimeAnnuelle = Salaire * 0.1; -- 10% du salaire
     COMMIT;
   END CalculerPrimeAnnuelle;

Utilisation :

   BEGIN
     CalculerPrimeAnnuelle;
   END;

Vérification de l’admissibilité à la retraite : Cette procédure vérifie l’admissibilité à la retraite pour un employé en fonction de son âge et de ses années de service.

   CREATE OR REPLACE PROCEDURE VerifierAdmissibiliteRetraite (
     p_employe_id NUMBER
   )
   AS
     v_age NUMBER;
     v_annees_service NUMBER;
   BEGIN
     SELECT CalculerAge(DateEmbauche) INTO v_age FROM Employes WHERE ID = p_employe_id;
     SELECT MONTHS_BETWEEN(SYSDATE, DateEmbauche) / 12 INTO v_annees_service FROM Employes WHERE ID = p_employe_id;

     IF v_age >= 65 OR v_annees_service >= 30 THEN
       DBMS_OUTPUT.PUT_LINE('L\'employé est admissible à la retraite.');
     ELSE
       DBMS_OUTPUT.PUT_LINE('L\'employé n\'est pas encore admissible à la retraite.');
     END IF;
   END VerifierAdmissibiliteRetraite;

Utilisation :

   BEGIN
     VerifierAdmissibiliteRetraite(201);
   END;

Ajout d’un département avec des employés par défaut : Cette procédure crée un nouveau département dans la table « Departements » et ajoute automatiquement des employés par défaut à ce département.

   CREATE OR REPLACE PROCEDURE CreerDepartementAvecEmployes (
     p_nom_departement VARCHAR2,
     p_nb_employes NUMBER
   )
   AS
   BEGIN
     INSERT INTO Departements (Nom) VALUES (p_nom_departement);
     COMMIT;

     FOR i IN 1..p_nb_employes LOOP
       INSERT INTO Employes (Nom, Prenom, Salaire, IDDepartement)
       VALUES ('Nom' || i, 'Prenom' || i, 50000, (SELECT ID FROM Departements WHERE Nom = p_nom_departement));
     END LOOP;
     COMMIT;
   END CreerDepartementAvecEmployes;

Utilisation :

   BEGIN
     CreerDepartementAvecEmployes('R&D', 10);
   END;

420-5D3 : BD – Procédure stockée

Syntaxe

Autres exemples d’utilisation

LABORATOIRE