BULK COLLECT et FORALL sont les deux techniques de « traitement en masse » de PL/SQL : elles échangent des milliers de lignes entre le moteur SQL et le moteur PL/SQL en un seul aller-retour, au lieu d’une ligne à la fois. Résultat : des traitements souvent 10 à 100 fois plus rapides. Voici comment les utiliser correctement.

Le problème : le « context switch » ligne à ligne

Une boucle curseur classique fait un aller-retour SQL↔PL/SQL par ligne. Sur 100 000 lignes, ces changements de contexte dominent le temps d’exécution. BULK COLLECT (lecture) et FORALL (écriture) les regroupent.

BULK COLLECT : lire en masse

DECLARE
  TYPE t_clients IS TABLE OF clients%ROWTYPE;
  v_clients t_clients;
BEGIN
  SELECT * BULK COLLECT INTO v_clients
  FROM clients WHERE ville = 'Paris';

  DBMS_OUTPUT.put_line('Chargés : ' || v_clients.COUNT);
END;

Toutes les lignes arrivent dans la collection en une fois. Voir aussi les curseurs pour la lecture ligne à ligne classique.

⚠️ Limiter la mémoire avec LIMIT

Charger des millions de lignes d’un coup épuise la mémoire (PGA). En production, lisez par paquets avec LIMIT :

DECLARE
  CURSOR c IS SELECT * FROM grosse_table;
  TYPE t IS TABLE OF grosse_table%ROWTYPE;
  v t;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v LIMIT 1000;   -- paquets de 1000
    EXIT WHEN v.COUNT = 0;
    -- traiter le paquet v ...
  END LOOP;
  CLOSE c;
END;

Le LIMIT (souvent 100 à 1000) est la règle d’or pour un BULK COLLECT robuste.

FORALL : écrire en masse

FORALL applique un INSERT/UPDATE/DELETE à toute une collection en un seul appel SQL :

DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM clients WHERE actif = 'N';

  FORALL i IN 1 .. v_ids.COUNT
    DELETE FROM logs WHERE client_id = v_ids(i);

  DBMS_OUTPUT.put_line(SQL%ROWCOUNT || ' lignes supprimées');
END;

FORALL n’est pas une boucle : c’est une seule instruction envoyée au moteur SQL pour tous les indices.

Continuer malgré les erreurs : SAVE EXCEPTIONS

Pour qu’un INSERT de masse n’échoue pas dès la première ligne fautive :

BEGIN
  FORALL i IN 1 .. v_data.COUNT SAVE EXCEPTIONS
    INSERT INTO cible VALUES v_data(i);
EXCEPTION
  WHEN OTHERS THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.put_line('Ligne ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX
        || ' : ' || SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
    END LOOP;
END;

Le combo gagnant : BULK COLLECT + FORALL

DECLARE
  CURSOR c IS SELECT id, montant FROM ventes WHERE traite = 'N';
  TYPE t IS TABLE OF c%ROWTYPE;
  v t;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v LIMIT 500;
    EXIT WHEN v.COUNT = 0;
    FORALL i IN 1 .. v.COUNT
      UPDATE ventes SET commission = v(i).montant * 0.05, traite = 'O'
      WHERE id = v(i).id;
    COMMIT;  -- valider par paquet
  END LOOP;
  CLOSE c;
END;

Bonnes pratiques

• Toujours un LIMIT sur les gros volumes (mémoire maîtrisée).
• SAVE EXCEPTIONS pour ne pas tout perdre sur une ligne fautive.
• Mais d’abord : un seul ordre SQL ensembliste suffit-il ? Si oui, pas besoin de PL/SQL du tout — c’est encore plus rapide.

FAQ

Quel gain de performance attendre ?

Souvent ×10 à ×100 vs une boucle ligne à ligne, car les changements de contexte SQL↔PL/SQL disparaissent.

Quelle valeur de LIMIT choisir ?

Entre 100 et 1000 dans la plupart des cas : un bon compromis entre performance et consommation mémoire (PGA).

FORALL est-il une boucle ?

Non : c’est une seule instruction SQL appliquée à tous les éléments de la collection. On ne peut pas y mettre plusieurs ordres.

Veuillez laisser ce champ vide

Un nouveau tutoriel SQL par semaine

Nous ne spammons pas ! Consultez notre politique de confidentialité pour plus d’informations.

Vérifiez votre boite de réception ou votre répertoire d’indésirables pour confirmer votre abonnement.