PROCEDURE Oracle : Créer et Utiliser des Procédures PL/SQL

PROCEDURE Oracle : Guide Complet pour Maîtriser les Procédures PL/SQL

La PROCEDURE Oracle est l’un des objets PL/SQL les plus utilisés en entreprise. Elle permet d’encapsuler une séquence d’instructions SQL et PL/SQL dans un bloc nommé, stocké directement dans la base de données. Grâce à la procédure Oracle, les développeurs peuvent réutiliser du code, améliorer les performances et renforcer la sécurité des traitements métier.

Définition et utilisation d’une PROCEDURE Oracle

Une procédure stockée Oracle est un sous-programme PL/SQL nommé, compilé et enregistré dans le dictionnaire de données Oracle. Contrairement à une fonction, elle ne retourne pas directement de valeur via un RETURN, mais peut transmettre des données grâce à des paramètres de type OUT ou IN OUT.

Pourquoi utiliser une procédure Oracle en entreprise ?

• Réutilisabilité : le code est écrit une seule fois et appelé depuis plusieurs applications ou scripts.
• Maintenabilité : une modification de la procédure se propage automatiquement à tous les appelants.
• Sécurité : on peut accorder l’exécution d’une procédure à un utilisateur sans lui donner accès direct aux tables sous-jacentes.
• Performance : le code PL/SQL est précompilé côté serveur, ce qui réduit les allers-retours réseau entre l’application et la base de données.
• Gestion des transactions : les instructions COMMIT et ROLLBACK peuvent être intégrées directement dans la procédure.

En contexte industriel, les procédures Oracle sont largement utilisées pour automatiser des traitements de masse (calcul de paie, clôtures comptables, mises à jour de stocks) ou pour exposer une API métier à une application Java ou Python.

Syntaxe complète d’une PROCEDURE Oracle

CREATE [OR REPLACE] PROCEDURE nom_procedure
   [ (parametre1 [IN | OUT | IN OUT] type_donnee [:= valeur_defaut],
      parametre2 [IN | OUT | IN OUT] type_donnee [:= valeur_defaut], ...) ]
IS | AS
   -- Section de déclaration des variables locales
   variable_locale type_donnee;
BEGIN
   -- Corps de la procédure : instructions SQL et PL/SQL
   NULL; -- au minimum une instruction
EXCEPTION
   -- Gestion optionnelle des erreurs
   WHEN OTHERS THEN
      RAISE;
END [nom_procedure];
/

Explication des paramètres essentiels

Exemples pratiques de PROCEDURE Oracle

Exemple 1 – Mise à jour du salaire d’un employé

Contexte métier : dans une application RH, le service paie doit pouvoir augmenter le salaire d’un employé identifié par son numéro, en précisant le pourcentage d’augmentation. La procédure doit valider que l’employé existe avant d’effectuer la mise à jour.

CREATE OR REPLACE PROCEDURE augmenter_salaire (
   p_emp_id   IN  employees.employee_id%TYPE,   -- Identifiant de l'employé (entrée)
   p_taux     IN  NUMBER,                        -- Taux d'augmentation en % (entrée)
   p_nouveau  OUT employees.salary%TYPE          -- Nouveau salaire calculé (sortie)
)
IS
   v_salaire_actuel employees.salary%TYPE;
BEGIN
   -- Récupération du salaire actuel
   SELECT salary
   INTO   v_salaire_actuel
   FROM   employees
   WHERE  employee_id = p_emp_id;

   -- Calcul et mise à jour du salaire
   p_nouveau := v_salaire_actuel * (1 + p_taux / 100);

   UPDATE employees
   SET    salary = p_nouveau
   WHERE  employee_id = p_emp_id;

   COMMIT; -- Validation de la transaction

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- L'employé n'existe pas : on lève une erreur explicite
      RAISE_APPLICATION_ERROR(-20001, 'Employé introuvable : ' || p_emp_id);
END augmenter_salaire;
/

Appel de la procédure depuis un bloc PL/SQL anonyme :

DECLARE
   v_nouveau_salaire employees.salary%TYPE;
BEGIN
   augmenter_salaire(
      p_emp_id  => 101,
      p_taux    => 10,
      p_nouveau => v_nouveau_salaire
   );
   DBMS_OUTPUT.PUT_LINE('Nouveau salaire : ' || v_nouveau_salaire);
END;
/

Exemple 2 – Archivage des commandes clôturées

Contexte métier : chaque nuit, un batch doit déplacer les commandes dont le statut est 'CLOTURE' depuis la table COMMANDES vers la table d’archive COMMANDES_ARCHIVE, puis les supprimer de la table principale.

CREATE OR REPLACE PROCEDURE archiver_commandes_cloturees
IS
   v_nb_lignes NUMBER := 0; -- Compteur de lignes traitées
BEGIN
   -- Insertion dans la table d'archive
   INSERT INTO commandes_archive
   SELECT * FROM commandes
   WHERE  statut = 'CLOTURE';

   -- Récupération du nombre de lignes insérées
   v_nb_lignes := SQL%ROWCOUNT;

   -- Suppression dans la table source
   DELETE FROM commandes
   WHERE  statut = 'CLOTURE';

   -- Validation globale de la transaction
   COMMIT;

   -- Journalisation du traitement
   DBMS_OUTPUT.PUT_LINE(v_nb_lignes || ' commande(s) archivée(s) avec succès.');

EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK; -- Annulation en cas d'erreur
      DBMS_OUTPUT.PUT_LINE('Erreur lors de l''archivage : ' || SQLERRM);
      RAISE;
END archiver_commandes_cloturees;
/

Appel simple :

BEGIN
   archiver_commandes_cloturees;
END;
/

Erreurs courantes avec les PROCEDURE Oracle

Erreur : PLS-00905 : object is invalid ou procédure compilée avec des warnings

Cause fréquente : la procédure référence une table ou une colonne qui n’existe pas, ou utilise un type de données incompatible avec la déclaration du paramètre. Oracle compile la procédure mais la marque comme invalide (INVALID).

Solution : après la création, vérifiez systématiquement le statut et les erreurs de compilation avec les requêtes suivantes :

-- Vérifier le statut de la procédure
SELECT object_name, status
FROM   user_objects
WHERE  object_type = 'PROCEDURE'
AND    object_name = 'NOM_DE_VOTRE_PROCEDURE';

-- Afficher le détail des erreurs de compilation
SELECT line, position, text
FROM   user_errors
WHERE  name = 'NOM_DE_VOTRE_PROCEDURE'
ORDER  BY line;

Sous SQL*Plus ou SQL Developer, vous pouvez également utiliser la commande SHOW ERRORS PROCEDURE nom_procedure; immédiatement après la création.

Résumé des points clés sur la PROCEDURE Oracle

2 bonnes pratiques Oracle

1. Utilisez %TYPE et %ROWTYPE pour déclarer vos variables et paramètres. Cela garantit que la procédure reste cohérente si la structure d’une table évolue, sans nécessiter de modification manuelle des types.
2. Centralisez la gestion des exceptions : définissez un package de gestion d’erreurs avec des codes applicatifs standardisés (-20000 à -20999) et loggez les erreurs dans une table de journalisation plutôt que de les ignorer silencieusement.

Aller plus loin avec PL/SQL Oracle

Maintenant que vous maîtrisez la PROCEDURE Oracle, voici trois sujets complémentaires pour approfondir vos compétences PL/SQL :

• FUNCTION Oracle PL/SQL : découvrez comment créer des fonctions stockées qui retournent une valeur scalaire et peuvent être appelées directement dans une requête SQL.
• PACKAGE Oracle PL/SQL : apprenez à regrouper vos procédures et fonctions dans un package pour mieux organiser votre code, gérer les dépendances et optimiser les performances grâce à la mise en cache des états.
• TRIGGER Oracle PL/SQL : explorez les déclencheurs Oracle pour exécuter automatiquement une procédure en réponse à des événements DML (INSERT, UPDATE, DELETE) sur une table.