Tutoriel Oracle PL/SQL

← Retour aux tutoriels | ← Voir le tutoriel SQL

Si vous devez transposer vos automatismes PL/SQL vers PostgreSQL, consultez aussi le tutoriel PostgreSQL PL/pgSQL.

Parcours d'apprentissage (novice vers expert)

Objectif: progresser des blocs PL/SQL élémentaires vers des objets industrialisés (packages, triggers, bulk).

Niveau novice: comprendre DECLARE, BEGIN, EXCEPTION.
Niveau intermédiaire: écrire procédures/fonctions avec gestion d'erreurs.
Niveau avancé: optimiser avec BULK COLLECT/FORALL et structurer en packages.

Fonctionnement: teste d'abord les blocs simples, puis ajoute contraintes métier et journalisation.

1. Structure de base

Bloc anonyme

DECLARE
    -- Section de déclaration (optionnel)
    v_nom VARCHAR2(50);
    v_salaire NUMBER;
BEGIN
    -- Section exécutable (obligatoire)
    SELECT first_name, salary 
    INTO v_nom, v_salaire
    FROM employees 
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE('Nom: ' || v_nom || ', Salaire: ' || v_salaire);
EXCEPTION
    -- Section exception (optionnel)
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employé introuvable');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Erreur: ' || SQLERRM);
END;
/

Variables et types

DECLARE
    -- Types de base
    v_nombre NUMBER := 10;
    v_texte VARCHAR2(100) := 'Hello';
    v_date DATE := SYSDATE;
    v_bool BOOLEAN := TRUE;
    
    -- Type ancré (%TYPE)
    v_nom employees.first_name%TYPE;
    v_dept_id employees.department_id%TYPE;
    
    -- Constante
    c_tax_rate CONSTANT NUMBER := 0.20;
    
    -- Record type
    TYPE t_emp_record IS RECORD (
        nom VARCHAR2(50),
        salaire NUMBER,
        bonus NUMBER
    );
    v_emp t_emp_record;
    
    -- Record ancré (%ROWTYPE)
    v_employee employees%ROWTYPE;
    
    -- Collection (TABLE)
    TYPE t_num_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_salaires t_num_table;
    
    -- VARRAY
    TYPE t_noms IS VARRAY(10) OF VARCHAR2(50);
    v_liste_noms t_noms := t_noms();
BEGIN
    v_employee.first_name := 'John';
    v_salaires(1) := 5000;
    v_salaires(2) := 6000;
END;
/

2. Structures de contrôle

IF / ELSIF / ELSE

DECLARE
    v_salaire NUMBER := 5000;
    v_bonus NUMBER;
BEGIN
    IF v_salaire > 10000 THEN
        v_bonus := v_salaire * 0.15;
    ELSIF v_salaire > 5000 THEN
        v_bonus := v_salaire * 0.10;
    ELSE
        v_bonus := v_salaire * 0.05;
    END IF;
    
    -- IF inline (12c+)
    v_bonus := CASE 
        WHEN v_salaire > 10000 THEN v_salaire * 0.15
        WHEN v_salaire > 5000 THEN v_salaire * 0.10
        ELSE v_salaire * 0.05
    END;
END;
/

CASE

DECLARE
    v_grade CHAR(1) := 'A';
    v_description VARCHAR2(50);
BEGIN
    -- CASE simple
    CASE v_grade
        WHEN 'A' THEN v_description := 'Excellent';
        WHEN 'B' THEN v_description := 'Bien';
        WHEN 'C' THEN v_description := 'Moyen';
        ELSE v_description := 'Insuffisant';
    END CASE;
    
    -- CASE recherché
    CASE
        WHEN v_salaire > 10000 THEN v_bonus := 1000;
        WHEN v_salaire > 5000 THEN v_bonus := 500;
        ELSE v_bonus := 0;
    END CASE;
END;
/

Boucles

DECLARE
    v_counter NUMBER := 0;
BEGIN
    -- LOOP basique
    LOOP
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 10;
        -- ou
        IF v_counter > 10 THEN
            EXIT;
        END IF;
    END LOOP;
    
    -- WHILE LOOP
    v_counter := 0;
    WHILE v_counter < 10 LOOP
        v_counter := v_counter + 1;
    END LOOP;
    
    -- FOR LOOP
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
    
    -- FOR LOOP inverse
    FOR i IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
    
    -- CONTINUE (11g+)
    FOR i IN 1..10 LOOP
        IF MOD(i, 2) = 0 THEN
            CONTINUE;  -- Sauter les pairs
        END IF;
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

3. Curseurs

Curseur explicite

DECLARE
    CURSOR c_emp IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 50;
    
    v_emp_id employees.employee_id%TYPE;
    v_nom employees.first_name%TYPE;
    v_sal employees.salary%TYPE;
BEGIN
    OPEN c_emp;
    
    LOOP
        FETCH c_emp INTO v_emp_id, v_nom, v_sal;
        EXIT WHEN c_emp%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(v_nom || ': ' || v_sal);
    END LOOP;
    
    CLOSE c_emp;
END;
/

Curseur avec FOR LOOP (recommandé)

BEGIN
    FOR emp_rec IN (
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 50
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);
    END LOOP;
END;
/

-- Curseur nommé avec FOR
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 50;
BEGIN
    FOR emp_rec IN c_emp LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name);
    END LOOP;
END;
/

Curseur paramétré

DECLARE
    CURSOR c_emp (p_dept_id NUMBER, p_min_sal NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id
          AND salary > p_min_sal;
BEGIN
    FOR emp_rec IN c_emp(50, 5000) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name);
    END LOOP;
END;
/

Attributs de curseur

DECLARE
    CURSOR c_emp IS SELECT * FROM employees;
BEGIN
    OPEN c_emp;
    
    -- %ISOPEN : Curseur ouvert?
    IF c_emp%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('Curseur ouvert');
    END IF;
    
    FETCH c_emp INTO ...;
    
    -- %FOUND : Ligne trouvée?
    IF c_emp%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Ligne récupérée');
    END IF;
    
    -- %NOTFOUND : Pas de ligne?
    IF c_emp%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('Fin du curseur');
    END IF;
    
    -- %ROWCOUNT : Nombre de lignes
    DBMS_OUTPUT.PUT_LINE('Lignes: ' || c_emp%ROWCOUNT);
    
    CLOSE c_emp;
END;
/

FOR UPDATE et WHERE CURRENT OF

DECLARE
    CURSOR c_emp IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = 50
        FOR UPDATE OF salary NOWAIT;  -- Lock les lignes
BEGIN
    FOR emp_rec IN c_emp LOOP
        UPDATE employees
        SET salary = salary * 1.10
        WHERE CURRENT OF c_emp;  -- Ligne courante du curseur
    END LOOP;
    
    COMMIT;
END;
/

4. Gestion des exceptions

Exceptions prédéfinies

BEGIN
    -- Code risqué
    NULL;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- SELECT INTO ne trouve rien
        DBMS_OUTPUT.PUT_LINE('Aucune donnée');
        
    WHEN TOO_MANY_ROWS THEN
        -- SELECT INTO trouve plusieurs lignes
        DBMS_OUTPUT.PUT_LINE('Trop de résultats');
        
    WHEN DUP_VAL_ON_INDEX THEN
        -- Violation de contrainte UNIQUE
        DBMS_OUTPUT.PUT_LINE('Valeur dupliquée');
        
    WHEN VALUE_ERROR THEN
        -- Erreur de conversion/troncature
        DBMS_OUTPUT.PUT_LINE('Erreur de valeur');
        
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Division par zéro');
        
    WHEN INVALID_CURSOR THEN
        DBMS_OUTPUT.PUT_LINE('Curseur invalide');
        
    WHEN OTHERS THEN
        -- Toutes les autres exceptions
        DBMS_OUTPUT.PUT_LINE('Erreur: ' || SQLCODE || ' - ' || SQLERRM);
        RAISE;  -- Relancer l'exception
END;
/

Exceptions personnalisées

DECLARE
    -- Déclaration
    e_salary_too_high EXCEPTION;
    e_invalid_dept EXCEPTION;
    
    -- Associer à un code Oracle
    PRAGMA EXCEPTION_INIT(e_invalid_dept, -02291);  -- FK violation
    
    v_salary NUMBER := 50000;
BEGIN
    -- Lever une exception
    IF v_salary > 40000 THEN
        RAISE e_salary_too_high;
    END IF;
    
    -- Lever avec message personnalisé
    RAISE_APPLICATION_ERROR(-20001, 'Salaire trop élevé: ' || v_salary);
    
EXCEPTION
    WHEN e_salary_too_high THEN
        DBMS_OUTPUT.PUT_LINE('Erreur: salaire supérieur à 40000');
    WHEN e_invalid_dept THEN
        DBMS_OUTPUT.PUT_LINE('Département invalide');
END;
/

5. Procédures

Procédure simple

CREATE OR REPLACE PROCEDURE augmenter_salaire (
    p_emp_id IN NUMBER,
    p_pct IN NUMBER DEFAULT 10
) IS
    v_old_salary NUMBER;
    v_new_salary NUMBER;
BEGIN
    -- Récupérer l'ancien salaire
    SELECT salary INTO v_old_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    
    -- Calculer le nouveau
    v_new_salary := v_old_salary * (1 + p_pct/100);
    
    -- Mettre à jour
    UPDATE employees
    SET salary = v_new_salary
    WHERE employee_id = p_emp_id;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Salaire augmenté de ' || v_old_salary || 
                         ' à ' || v_new_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employé ' || p_emp_id || ' introuvable');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END augmenter_salaire;
/

-- Appel
BEGIN
    augmenter_salaire(100, 15);
    augmenter_salaire(p_emp_id => 101, p_pct => 20);
END;
/

Paramètres IN / OUT / IN OUT

CREATE OR REPLACE PROCEDURE calculer_stats (
    p_dept_id IN NUMBER,
    p_count OUT NUMBER,
    p_avg_salary OUT NUMBER,
    p_max_salary OUT NUMBER
) IS
BEGIN
    SELECT COUNT(*), AVG(salary), MAX(salary)
    INTO p_count, p_avg_salary, p_max_salary
    FROM employees
    WHERE department_id = p_dept_id;
END calculer_stats;
/

-- Appel
DECLARE
    v_count NUMBER;
    v_avg NUMBER;
    v_max NUMBER;
BEGIN
    calculer_stats(50, v_count, v_avg, v_max);
    DBMS_OUTPUT.PUT_LINE('Employés: ' || v_count);
    DBMS_OUTPUT.PUT_LINE('Salaire moyen: ' || v_avg);
END;
/

-- IN OUT
CREATE OR REPLACE PROCEDURE doubler_valeur (
    p_nombre IN OUT NUMBER
) IS
BEGIN
    p_nombre := p_nombre * 2;
END;
/

6. Fonctions

Fonction simple

CREATE OR REPLACE FUNCTION calculer_bonus (
    p_salaire IN NUMBER,
    p_performance IN NUMBER DEFAULT 1
) RETURN NUMBER
IS
    v_bonus NUMBER;
BEGIN
    v_bonus := p_salaire * p_performance * 0.10;
    RETURN v_bonus;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END calculer_bonus;
/

-- Utilisation en SQL
SELECT 
    first_name, 
    salary,
    calculer_bonus(salary, 1.5) AS bonus
FROM employees;

Fonction déterministe

-- Fonction pure (même entrée = même sortie)
CREATE OR REPLACE FUNCTION get_tax_rate (
    p_country IN VARCHAR2
) RETURN NUMBER
DETERMINISTIC  -- Optimisation possible
IS
BEGIN
    RETURN CASE p_country
        WHEN 'FR' THEN 0.20
        WHEN 'US' THEN 0.15
        WHEN 'UK' THEN 0.18
        ELSE 0.10
    END;
END;
/

Fonction pipeline

-- Type de collection
CREATE TYPE t_emp_row AS OBJECT (
    employee_id NUMBER,
    full_name VARCHAR2(100),
    salary NUMBER
);
/

CREATE TYPE t_emp_table AS TABLE OF t_emp_row;
/

-- Fonction pipeline
CREATE OR REPLACE FUNCTION get_high_earners (
    p_min_salary NUMBER
) RETURN t_emp_table PIPELINED
IS
BEGIN
    FOR emp_rec IN (
        SELECT employee_id, first_name || ' ' || last_name, salary
        FROM employees
        WHERE salary > p_min_salary
    ) LOOP
        PIPE ROW(t_emp_row(
            emp_rec.employee_id,
            emp_rec.first_name || ' ' || emp_rec.last_name,
            emp_rec.salary
        ));
    END LOOP;
    
    RETURN;
END;
/

-- Utilisation
SELECT * FROM TABLE(get_high_earners(10000));

7. Packages

Spécification (header)

CREATE OR REPLACE PACKAGE pkg_employee IS
    -- Constantes publiques
    c_max_salary CONSTANT NUMBER := 50000;
    
    -- Variables publiques
    g_last_update DATE;
    
    -- Procédures et fonctions publiques
    PROCEDURE hire_employee (
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2,
        p_email IN VARCHAR2,
        p_salary IN NUMBER,
        p_emp_id OUT NUMBER
    );
    
    FUNCTION get_employee_count (
        p_dept_id IN NUMBER
    ) RETURN NUMBER;
    
    PROCEDURE fire_employee (
        p_emp_id IN NUMBER
    );
    
    -- Surcharge (overloading)
    FUNCTION calculate_bonus (
        p_salary IN NUMBER
    ) RETURN NUMBER;
    
    FUNCTION calculate_bonus (
        p_salary IN NUMBER,
        p_years IN NUMBER
    ) RETURN NUMBER;
    
END pkg_employee;
/

Corps (body)

CREATE OR REPLACE PACKAGE BODY pkg_employee IS
    -- Variables privées
    g_default_dept_id NUMBER := 10;
    
    -- Fonction privée
    FUNCTION generate_email (
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2
    ) RETURN VARCHAR2
    IS
    BEGIN
        RETURN LOWER(SUBSTR(p_first_name, 1, 1) || p_last_name || '@company.com');
    END;
    
    -- Implémentation des fonctions publiques
    PROCEDURE hire_employee (
        p_first_name IN VARCHAR2,
        p_last_name IN VARCHAR2,
        p_email IN VARCHAR2,
        p_salary IN NUMBER,
        p_emp_id OUT NUMBER
    ) IS
        v_email VARCHAR2(100);
    BEGIN
        -- Générer email si non fourni
        v_email := NVL(p_email, generate_email(p_first_name, p_last_name));
        
        -- Insérer l'employé
        INSERT INTO employees (
            employee_id, first_name, last_name, 
            email, hire_date, salary, department_id
        ) VALUES (
            employees_seq.NEXTVAL, p_first_name, p_last_name,
            v_email, SYSDATE, p_salary, g_default_dept_id
        ) RETURNING employee_id INTO p_emp_id;
        
        g_last_update := SYSDATE;
        COMMIT;
    END hire_employee;
    
    FUNCTION get_employee_count (
        p_dept_id IN NUMBER
    ) RETURN NUMBER
    IS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*)
        INTO v_count
        FROM employees
        WHERE department_id = p_dept_id;
        
        RETURN v_count;
    END get_employee_count;
    
    PROCEDURE fire_employee (
        p_emp_id IN NUMBER
    ) IS
    BEGIN
        DELETE FROM employees WHERE employee_id = p_emp_id;
        
        IF SQL%ROWCOUNT = 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Employé introuvable');
        END IF;
        
        g_last_update := SYSDATE;
        COMMIT;
    END fire_employee;
    
    -- Surcharges
    FUNCTION calculate_bonus (
        p_salary IN NUMBER
    ) RETURN NUMBER
    IS
    BEGIN
        RETURN p_salary * 0.10;
    END;
    
    FUNCTION calculate_bonus (
        p_salary IN NUMBER,
        p_years IN NUMBER
    ) RETURN NUMBER
    IS
    BEGIN
        RETURN p_salary * 0.10 * (1 + p_years * 0.05);
    END;
    
-- Section d'initialisation (optionnel)
BEGIN
    g_last_update := SYSDATE;
    DBMS_OUTPUT.PUT_LINE('Package pkg_employee initialisé');
END pkg_employee;
/

Utilisation du package

DECLARE
    v_emp_id NUMBER;
    v_count NUMBER;
    v_bonus NUMBER;
BEGIN
    -- Embaucher un employé
    pkg_employee.hire_employee(
        p_first_name => 'John',
        p_last_name => 'Doe',
        p_email => NULL,  -- Auto-généré
        p_salary => 5000,
        p_emp_id => v_emp_id
    );
    
    -- Utiliser la constante
    IF 5000 > pkg_employee.c_max_salary THEN
        DBMS_OUTPUT.PUT_LINE('Salaire trop élevé');
    END IF;
    
    -- Appeler fonction
    v_count := pkg_employee.get_employee_count(10);
    
    -- Surcharge
    v_bonus := pkg_employee.calculate_bonus(5000);
    v_bonus := pkg_employee.calculate_bonus(5000, 5);
    
    -- Variable publique
    DBMS_OUTPUT.PUT_LINE('Dernière MAJ: ' || pkg_employee.g_last_update);
END;
/

8. Triggers

Trigger BEFORE INSERT/UPDATE

CREATE OR REPLACE TRIGGER trg_employees_audit
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
    v_user VARCHAR2(50);
BEGIN
    v_user := USER;
    
    -- Audit
    IF INSERTING THEN
        :NEW.created_by := v_user;
        :NEW.created_date := SYSDATE;
    END IF;
    
    IF UPDATING THEN
        :NEW.updated_by := v_user;
        :NEW.updated_date := SYSDATE;
    END IF;
    
    -- Validation
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salaire ne peut être négatif');
    END IF;
    
    -- Auto-incrémentation
    IF INSERTING AND :NEW.employee_id IS NULL THEN
        SELECT employees_seq.NEXTVAL INTO :NEW.employee_id FROM DUAL;
    END IF;
END;
/

Trigger AFTER

CREATE OR REPLACE TRIGGER trg_salary_history
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary <> OLD.salary)  -- Condition
BEGIN
    INSERT INTO salary_history (
        employee_id, old_salary, new_salary, change_date
    ) VALUES (
        :NEW.employee_id, :OLD.salary, :NEW.salary, SYSDATE
    );
END;
/

Trigger de table composé

CREATE OR REPLACE TRIGGER trg_dept_summary
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER
    
    TYPE t_dept_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    g_depts_modified t_dept_tab;
    
    -- BEFORE STATEMENT
    BEFORE STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Début de l''opération');
    END BEFORE STATEMENT;
    
    -- AFTER EACH ROW
    AFTER EACH ROW IS
    BEGIN
        IF INSERTING OR UPDATING THEN
            g_depts_modified(:NEW.department_id) := 1;
        END IF;
        IF DELETING OR UPDATING THEN
            g_depts_modified(:OLD.department_id) := 1;
        END IF;
    END AFTER EACH ROW;
    
    -- AFTER STATEMENT
    AFTER STATEMENT IS
        v_dept_id NUMBER;
    BEGIN
        -- Mettre à jour les statistiques pour chaque département modifié
        v_dept_id := g_depts_modified.FIRST;
        WHILE v_dept_id IS NOT NULL LOOP
            UPDATE department_stats
            SET employee_count = (
                SELECT COUNT(*) FROM employees WHERE department_id = v_dept_id
            ),
            last_updated = SYSDATE
            WHERE department_id = v_dept_id;
            
            v_dept_id := g_depts_modified.NEXT(v_dept_id);
        END LOOP;
    END AFTER STATEMENT;
    
END trg_dept_summary;
/

Instead Of Trigger (vues)

-- Vue non modifiable
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Trigger pour la rendre modifiable
CREATE OR REPLACE TRIGGER trg_v_emp_dept
INSTEAD OF UPDATE ON v_emp_dept
FOR EACH ROW
BEGIN
    UPDATE employees
    SET first_name = :NEW.first_name
    WHERE employee_id = :NEW.employee_id;
END;
/

9. Optimisation PL/SQL

BULK COLLECT

DECLARE
    TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
    v_employees t_emp_tab;
BEGIN
    -- Récupérer toutes les lignes en une fois
    SELECT * BULK COLLECT INTO v_employees
    FROM employees
    WHERE department_id = 50;
    
    FOR i IN 1..v_employees.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_employees(i).first_name);
    END LOOP;
END;
/

-- Avec LIMIT (pour très grandes tables)
DECLARE
    CURSOR c_emp IS SELECT * FROM employees;
    TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
    v_employees t_emp_tab;
    c_limit CONSTANT PLS_INTEGER := 100;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp BULK COLLECT INTO v_employees LIMIT c_limit;
        EXIT WHEN v_employees.COUNT = 0;
        
        -- Traiter le batch
        FOR i IN 1..v_employees.COUNT LOOP
            NULL;
        END LOOP;
    END LOOP;
    CLOSE c_emp;
END;
/

FORALL (bulk DML)

DECLARE
    TYPE t_id_tab IS TABLE OF NUMBER;
    TYPE t_sal_tab IS TABLE OF NUMBER;
    
    v_emp_ids t_id_tab;
    v_salaries t_sal_tab;
BEGIN
    -- Charger les données
    SELECT employee_id, salary * 1.10
    BULK COLLECT INTO v_emp_ids, v_salaries
    FROM employees
    WHERE department_id = 50;
    
    -- Mise à jour en bulk
    FORALL i IN 1..v_emp_ids.COUNT
        UPDATE employees
        SET salary = v_salaries(i)
        WHERE employee_id = v_emp_ids(i);
    
    DBMS_OUTPUT.PUT_LINE('Lignes mises à jour: ' || SQL%ROWCOUNT);
    COMMIT;
END;
/

-- FORALL avec SAVE EXCEPTIONS
DECLARE
    TYPE t_id_tab IS TABLE OF NUMBER;
    v_emp_ids t_id_tab := t_id_tab(100, 101, 999, 103);  -- 999 n'existe pas
    
    bulk_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
    FORALL i IN 1..v_emp_ids.COUNT SAVE EXCEPTIONS
        DELETE FROM employees WHERE employee_id = v_emp_ids(i);
    
EXCEPTION
    WHEN bulk_errors THEN
        FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('Erreur ' || i || ': ' ||
                SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
        END LOOP;
END;
/

Collections associatives (performantes)

DECLARE
    -- INDEX BY PLS_INTEGER (plus rapide)
    TYPE t_sal_by_id IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_salaires t_sal_by_id;
    
    -- INDEX BY VARCHAR2
    TYPE t_sal_by_name IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
    v_sal_by_name t_sal_by_name;
BEGIN
    v_salaires(100) := 5000;
    v_salaires(101) := 6000;
    
    v_sal_by_name('John') := 5000;
    
    -- Parcourir
    DECLARE
        v_idx PLS_INTEGER;
    BEGIN
        v_idx := v_salaires.FIRST;
        WHILE v_idx IS NOT NULL LOOP
            DBMS_OUTPUT.PUT_LINE(v_idx || ': ' || v_salaires(v_idx));
            v_idx := v_salaires.NEXT(v_idx);
        END LOOP;
    END;
END;
/

NOCOPY (passage par référence)

-- Par défaut, OUT et IN OUT copient les données (lent pour collections)
CREATE OR REPLACE PROCEDURE process_employees (
    p_employees IN OUT NOCOPY t_emp_table  -- Pas de copie
) IS
BEGIN
    FOR i IN 1..p_employees.COUNT LOOP
        p_employees(i).salary := p_employees(i).salary * 1.10;
    END LOOP;
END;
/

10. Bonnes pratiques

Nommage :
- Variables : v_nom
- Paramètres : p_nom
- Constantes : c_nom
- Globales : g_nom
- Curseurs : c_nom
- Types : t_nom
Performances :
- Utiliser BULK COLLECT et FORALL pour DML en masse
- Préférer SQL à PL/SQL quand possible
- Utiliser NOCOPY pour grandes collections
- Limiter les context switches SQL ↔ PL/SQL
Transactions :
- Toujours COMMIT ou ROLLBACK explicitement
- Utiliser SAVEPOINT pour transactions complexes
- Gérer les exceptions avec ROLLBACK
Packages :
- Grouper fonctions/procédures liées
- Utiliser variables package pour cache/état
- Séparer spec et body pour encapsulation
Sécurité :
- Éviter SQL dynamique (injection SQL)
- Utiliser DBMS_ASSERT pour validation
- Définir AUTHID CURRENT_USER si nécessaire
Debug :
- DBMS_OUTPUT.PUT_LINE pour traces
- UTL_FILE pour logs fichiers
- DBMS_APPLICATION_INFO pour monitoring

Défi progression (vers expert)

Mission: industrialiser un flux métier PL/SQL avec package, trigger et traitement de masse.

Réussite niveau 1: procédure fonctionnelle avec gestion d'erreurs métier.
Réussite niveau 2: package complet avec fonctions surchargées et journalisation.
Réussite niveau 3: optimisation BULK COLLECT/FORALL et stratégie de rollback robuste.

← Voir le tutoriel SQL | ← Retour aux tutoriels