PL/SQL — procédures, curseurs, exceptions

1. Procédures et fonctions stockées

• Déporter les traitements de manipulation vers le SGBD.
• Ensemble d'instructions procédurales + requêtes SQL.
• On les dit procédures/fonctions stockées.
• Traitement identique et commun à tous les applicatifs.
• Charge supplémentaire pour le SGBD (à arbitrer).

Une fonction renvoie une valeur ; une procédure exécute un effet.

Déclaration

CREATE [OR REPLACE] PROCEDURE nom_procédure (paramètres)
IS
    <bloc de variables>
BEGIN
    <bloc de traitements>
END;

CREATE [OR REPLACE] FUNCTION nom_fonction (paramètres)
RETURN type
IS
    <bloc de variables>
BEGIN
    <bloc de traitements>
    RETURN <result>;
END;

Paramètres — trois modes

CREATE OR REPLACE PROCEDURE nom_procédure (
    p_1 IN     <type1> DEFAULT <valeur>,
    p_2 OUT    <type2>,
    p_3 IN OUT <type3>)
IS
    <bloc de variables>
BEGIN
    <bloc de traitements>
END;

-- BON
CREATE OR REPLACE PROCEDURE ma_proc (
    p_1 IN NUMBER,
    p_2 IN VARCHAR2)

Variables, typage interne

CREATE [OR REPLACE] PROCEDURE nom_procédure (<paramètres>)
IS
    v_1 NUMBER;
    v_2 <table>.<colonne>%TYPE;
    v_3 <table>%ROWTYPE;
    TYPE t_rec IS RECORD (a t1, b t2);
    v_rec t_rec;
BEGIN
    <bloc de traitements>
END;

Appel

-- Dans un bloc PL/SQL
BEGIN
    v_res := f_nom(args);
    proc_nom(args);
END;

-- Depuis SQL*Plus
SQL> SELECT f_nom(args) FROM DUAL;
SQL> EXEC proc_nom(args);

Fonctions natives utiles

2. Curseurs

Deux types de déclaration :

• implicite : SELECT … INTO … FROM …; (résultat unique)
• explicite : CURSOR c IS …; avec OPEN / FETCH / CLOSE (résultat multiple)

Curseurs implicites

BEGIN
    -- SELECT INTO direct (1 seule ligne attendue)
    SELECT nom, prenom INTO v_nom, v_prenom
    FROM clients
    WHERE id = p_id_client;

    -- ou via SQL dynamique
    EXECUTE IMMEDIATE r_req INTO v_res USING v_num;
END;

Curseurs explicites — cycle de vie

Cycle de vie d'un curseur explicite : OPEN → FETCH en boucle → CLOSE.

CREATE OR REPLACE PROCEDURE nom_procédure
IS
    CURSOR cur IS
        SELECT * FROM table;
    v_item cur%ROWTYPE;
BEGIN
    OPEN cur;                              -- ouverture
    LOOP
        FETCH cur INTO v_item;             -- récupère ligne
        EXIT WHEN cur%NOTFOUND;             -- fin du parcours
        <traitements>
    END LOOP;
    CLOSE cur;                             -- fermeture !
END;

Pseudo-attributs d'un curseur

Parcours automatique — FOR cursor LOOP

-- Curseur déclaré puis parcouru avec FOR
CREATE OR REPLACE PROCEDURE nom_procédure
IS
    CURSOR cur IS
        SELECT … FROM …;
BEGIN
    FOR v_ligne IN cur LOOP
        <traitements>
    END LOOP;
END;

-- Curseur implicite (FOR sur SELECT direct)
BEGIN
    FOR v_ligne IN (SELECT … FROM …) LOOP
        <traitements>
    END LOOP;
END;

Curseur paramétré

CREATE OR REPLACE PROCEDURE nom_procédure
IS
    CURSOR cur(p_num NUMBER) IS
        SELECT * FROM table WHERE no = p_num;
BEGIN
    OPEN cur(12);
    …
END;

3. Transactions PL/SQL

Permettent d'isoler un ensemble de modifications sur les données ; vérifient les propriétés ACID (revoir CM3).

Les 4 composantes principales

BEGIN
    INSERT INTO …;
    INSERT INTO …;
    INSERT INTO …;
    INSERT INTO …;
    ROLLBACK;                -- Annule les 4 précédentes

    INSERT INTO …;
    INSERT INTO …;
    SAVEPOINT icionsauve;
    INSERT INTO …;
    INSERT INTO …;
    ROLLBACK TO icionsauve; -- Annule les 2 dernières
END;

BEGIN
    SET TRANSACTION NAME 'tr_nom';
    DELETE FROM …;
    INSERT INTO …;
    UPDATE …;
    COMMIT;
END;

Transaction autonome — PRAGMA AUTONOMOUS_TRANSACTION

• Une procédure stockée / fonction devrait ne pas réaliser de validation. Charge au code appelant.
• Mais on peut créer une transaction autonome via PRAGMA AUTONOMOUS_TRANSACTION qui s'engage indépendamment de la transaction appelante.

CREATE OR REPLACE PROCEDURE nom_procédure
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    <bloc variables>
BEGIN
    <traitements>
    COMMIT;  -- Commit indépendant de la transaction appelante
END;

4. Gestion des exceptions

Bloc gestion d'erreurs

BEGIN
    <traitements à réaliser>
EXCEPTION
    WHEN ex1 THEN …
    WHEN ex2 THEN …
    WHEN OTHERS THEN …  -- fourre-tout
END;

Exceptions classiques Oracle

Récupérer code et message d'erreur

BEGIN
    INSERT INTO …;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Erreur n° '||SQLCODE||' - '||SQLERRM);
        UPDATE …;
END;

• SQLCODE = code d'erreur Oracle (ORA-xxxxx)
• SQLERRM = message d'erreur explicite

Créer ses propres exceptions

1. Déclarer une variable EXCEPTION
2. Lui associer un code via PRAGMA EXCEPTION_INIT(nom, code)
3. La lever avec RAISE
4. La capturer dans un WHEN

CREATE OR REPLACE PROCEDURE verifier_age(p_age NUMBER)
IS
    e_age_invalide EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_age_invalide, -20001);
BEGIN
    IF p_age < 18 THEN
        RAISE e_age_invalide;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Âge valide');
EXCEPTION
    WHEN e_age_invalide THEN
        DBMS_OUTPUT.PUT_LINE('Âge invalide');
END;

Lever une erreur ponctuelle

RAISE_APPLICATION_ERROR(code, message);
-- code ∈ [-20999, -20000] (strictement < -20000)

Exception levée puis gérée par une procédure appelante

-- Côté procédure qui lève
c_num_monexc CONSTANT NUMBER := -20042;
ex_monexception EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_monexception, -20042);

BEGIN
    …
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(c_num_monexc, 'Erreur id : '||p_id);
END;

-- Côté procédure appelante
BEGIN
    …
EXCEPTION
    WHEN ex_monexception THEN
        DBMS_OUTPUT.PUT_LINE('Je gère l\'exception perso');
END;

5. Packages

• Regroupe des fonctionnalités, traitements au sein d'une même entité.
• Concerne : procédures/fonctions, exceptions, curseurs, variables/constantes.

Architecture en deux parties

Le package sépare l'interface publique (declaration) de l'implémentation (body).

Déclaration (header)

CREATE [OR REPLACE] PACKAGE nom_pkg
IS
    [déclaration des variables/constantes;]
    [déclaration des exceptions;]
    [déclaration de procédures;]
    [déclaration de fonctions;]
    …
END nom_pkg;

Body

CREATE [OR REPLACE] PACKAGE BODY nom_pkg
IS
    [déclaration de variables (privées);]
    [déclaration et corps de procédures;]
    [déclaration et corps de fonctions;]
    …
END nom_pkg;

Exemple complet

CREATE OR REPLACE PACKAGE pkg_utils IS
    c_PI    CONSTANT NUMBER := 3.14159;
    vg_user VARCHAR2(10);
    PROCEDURE logue(p_str IN VARCHAR2);
    PROCEDURE init;
    FUNCTION  rech(p_id IN user.id%TYPE) RETURN user.usr_no%TYPE;
END pkg_utils;
/

CREATE OR REPLACE PACKAGE BODY pkg_utils IS
    PROCEDURE logue(p_str IN VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(p_str);
    END logue;

    PROCEDURE init IS
    BEGIN
        SELECT USER INTO vg_user FROM DUAL;
    END init;

    FUNCTION  rech(p_id IN user.id%TYPE) RETURN user.usr_no%TYPE IS
        v_no user.usr_no%TYPE;
    BEGIN
        SELECT usr_no INTO v_no FROM user WHERE id = p_id;
        RETURN v_no;
    END rech;
END pkg_utils;
/

★ Réviser le chapitre

Pour vérifier ta compréhension

Flashcards éclair

Clique pour retourner. Tout retourner

FOR v IN cur LOOP
   …
END LOOP;
-- open/fetch/close auto

CURSOR c(p_id NUMBER) IS
  SELECT … WHERE id = p_id;
OPEN c(12);

e EXCEPTION;
PRAGMA EXCEPTION_INIT(e, -20001);
-- puis
RAISE e;

PRAGMA AUTONOMOUS_TRANSACTION;

CREATE OR REPLACE PACKAGE p IS
  PROCEDURE f;
END p;
/
CREATE OR REPLACE PACKAGE BODY p IS
  PROCEDURE f IS BEGIN … END;
END p;

Quiz éclair

Pour la feuille recto-verso