Le développement de procédures stockées en PL/SQL est un pilier de la logique métier au sein des bases de données Oracle. Ce guide détaille la création, l'invocation et la gestion avancée des erreurs.

1. Création d'une procédure stockée

La création d'une procédure utilise la syntaxe CREATE OR REPLACE PROCEDURE. Il est crucial de terminer chaque instruction par un point-virgule et d'utiliser DBMS_OUTPUT.PUT_LINE pour le débogage.

-- Définition d'une procédure de classification d'âge
CREATE OR REPLACE PROCEDURE EVALUER_PROFIL_UTILISATEUR(
    p_nom IN VARCHAR2,
    p_annee_naissance IN NUMBER
) AS 
    -- Déclaration des variables locales
    v_annee_courante NUMBER(4);
    v_age_reel NUMBER(3);
    v_message_final VARCHAR2(300);
BEGIN
    -- Calcul de l'année en cours via SYSDATE
    v_annee_courante := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'));
    v_age_reel := v_annee_courante - p_annee_naissance;
    v_message_final := p_nom || ' est âgé de ' || v_age_reel || ' ans';

    -- Structure de contrôle conditionnelle
    IF v_age_reel < 18 THEN
        v_message_final := v_message_final || ' (Catégorie : Mineur)';
    ELSIF v_age_reel < 65 THEN
        v_message_final := v_message_final || ' (Catégorie : Actif)';
    ELSE 
        v_message_final := v_message_final || ' (Catégorie : Retraité)';
    END IF;

    DBMS_OUTPUT.PUT_LINE(v_message_final); 

EXCEPTION
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('Une erreur est survenue : ' || SQLERRM); 
END EVALUER_PROFIL_UTILISATEUR;

2. Exécution de la procédure

Il existe deux méthodes principales pour invoquer une procédure stockée :

• CALL : Commande SQL standard, compatible avec la majorité des outils et frameworks.
• EXEC (ou EXECUTE) : Commande spécifique à l'environnement SQL*Plus, nécessitant souvent l'activation préalable de l'affichage via SET SERVEROUTPUT ON.

-- Syntaxe standard
CALL EVALUER_PROFIL_UTILISATEUR('Jean Dupont', 1985);

3. Structures Conditionnelles

En PL/SQL, la syntaxe des conditions doit être rigoureuse. Une erreur commune est l'orthographe de ELSIF (sans le second 'E').

IF condition1 THEN
    -- Instructions
ELSIF condition2 THEN
    -- Instructions
ELSE
    -- Instructions par défaut
END IF;

4. Gestion des Exceptions

Oracle classifie les exceptions en trois catégories distinctes pour assurer la robustesse du code.

A. Exceptions Prédéfinies

Il s'agit d'erreurs standards Oracle automatiquement associées à des noms spécifiques.

B. Exceptions Non-Prédéfinies

Cette méthode permet d'associer un nom à un code erreur Oracle qui n'a pas de nom prédéfini en utilisant PRAGMA EXCEPTION_INIT.

DECLARE
    -- 1. Déclarer le nom de l'exception
    e_contrainte_fk EXCEPTION;
    -- 2. Associer le nom au code erreur ORA-02291 (clé parente introuvable)
    PRAGMA EXCEPTION_INIT(e_contrainte_fk, -2291);
BEGIN
    INSERT INTO EMPLOYES (id, service_id) VALUES (10, 999); -- 999 n'existe pas
EXCEPTION
    WHEN e_contrainte_fk THEN
        DBMS_OUTPUT.PUT_LINE('Erreur : Le service spécifié n''existe pas.');
END;

C. Exceptions Personnalisées

Ces exceptions sont définies par le développeur pour gérer des règles métier spécifiques. Elles sont déclenchées manuellement via RAISE ou RAISE_APPLICATION_ERROR.

CREATE OR REPLACE PROCEDURE VERIFIER_INSCRIPTION(
    p_nom IN VARCHAR2,
    p_annee IN NUMBER
) AS 
    v_err_code NUMBER;
    v_err_msg  VARCHAR2(200);
    
    -- Définition d'exceptions personnalisées
    ex_nom_vide EXCEPTION;
    ex_date_invalide EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(ex_nom_vide, -20101);
    PRAGMA EXCEPTION_INIT(ex_date_invalide, -20102);
BEGIN
    -- Validation des entrées
    IF p_nom IS NULL THEN
        RAISE_APPLICATION_ERROR(-20101, 'Le nom de l''utilisateur est obligatoire.');
    END IF;
    
    IF p_annee < 1900 THEN
        RAISE_APPLICATION_ERROR(-20102, 'L''année de naissance est trop ancienne.');
    END IF;

    DBMS_OUTPUT.PUT_LINE('Validation réussie pour ' || p_nom);

EXCEPTION
    WHEN ex_nom_vide THEN
        DBMS_OUTPUT.PUT_LINE('Erreur métier : ' || SQLERRM);
    WHEN ex_date_invalide THEN
        DBMS_OUTPUT.PUT_LINE('Erreur de validation : ' || SQLERRM);
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('Code : ' || SQLCODE || ' | Message : ' || SQLERRM); 
END;

L'utilisation de RAISE_APPLICATION_ERROR permet d'interrompre l'exécution et de renvoyer un message d'erreur personnalisé vers l'application appelante, en utilisant une plage de codes réservés (de -20000 à -20999).