Oracle PL/SQL BULK COLLECT : exemple FORALL

Qu’est-ce que la COLLECTE EN VRAC ?

BULK COLLECT réduit les changements de contexte entre SQL et le moteur PL/SQL et permet au moteur SQL de récupérer les enregistrements en même temps.

Oracle PL/SQL offre la fonctionnalité de récupération des enregistrements en masse plutôt que de les récupérer un par un. Ce BULK COLLECT peut être utilisé dans l'instruction 'SELECT' pour remplir les enregistrements en masse ou pour récupérer le curseur en masse. Étant donné que BULK COLLECT récupère l'enregistrement en BULK, la clause INTO doit toujours contenir une variable de type collection. Le principal avantage de l'utilisation de BULK COLLECT est qu'elle augmente les performances en réduisant l'interaction entre la base de données et le moteur PL/SQL.

syntaxe:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

Dans la syntaxe ci-dessus, BULK COLLECT est utilisé pour collecter les données des instructions 'SELECT' et 'FETCH'.

Clause FORALL

Le FORALL permet d'effectuer les opérations DML sur des données en masse. C'est similaire à celui de l'instruction de boucle FOR sauf dans Boucle POUR les choses se produisent au niveau de l'enregistrement alors que dans FORALL il n'y a pas de concept LOOP. Au lieu de cela, toutes les données présentes dans la plage donnée sont traitées en même temps.

syntaxe:

FORALL <loop_variable>in<lower range> .. <higher range> 

<DML operations>;

Dans la syntaxe ci-dessus, l'opération DML donnée sera exécutée pour toutes les données présentes entre les plages inférieure et supérieure.

Clause LIMITE

Le concept de collecte groupée charge l'intégralité des données dans la variable de collecte cible en masse, c'est-à-dire que l'ensemble des données sera renseigné dans la variable de collection en une seule fois. Mais cela n'est pas conseillé lorsque l'enregistrement total à charger est très volumineux, car lorsque PL / SQL essaie de charger toutes les données, il consomme plus de mémoire de session. Il est donc toujours bon de limiter la taille de cette opération de collecte en masse.

Cependant, cette limite de taille peut être facilement atteinte en introduisant la condition ROWNUM dans l'instruction 'SELECT', alors que dans le cas du curseur, cela n'est pas possible.

Pour surmonter cela Oracle a fourni la clause « LIMIT » qui définit le nombre d'enregistrements qui doivent être inclus dans le bloc.

syntaxe:

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

Dans la syntaxe ci-dessus, l'instruction de récupération du curseur utilise l'instruction BULK COLLECT avec la clause LIMIT.

BULK COLLECT Les attributs

Similaire à curseur les attributs BULK COLLECT ont %BULK_ROWCOUNT(n) qui renvoie le nombre de lignes affectées dans le n^th Instruction DML de l'instruction FORALL, c'est-à-dire qu'elle donnera le nombre d'enregistrements affectés dans l'instruction FORALL pour chaque valeur de la variable de collection. Le terme « n » indique la séquence de valeurs dans la collection pour laquelle le nombre de lignes est nécessaire.

Exemple 1: Dans cet exemple, nous projetterons tous les noms d'employés de la table emp en utilisant BULK COLLECT et nous allons également augmenter le salaire de tous les employés de 5000 en utilisant FORALL.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||c_emp_name);
END LOOP:
FORALL i IN lv_emp_name.FIRST .. lv emp_name.LAST
UPDATE emp SET salaiy=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;	
Dbms_output.put_line(‘Salary Updated‘);
CLOSE guru99_det;
END;
/

Sortie

Employee Fetched:BBB
Employee Fetched:XXX 
Employee Fetched:YYY
Salary Updated

Explication du code :

• Ligne de code 2: Déclaration du curseur guru99_det pour l'instruction 'SELECT emp_name FROM emp'.
• Ligne de code 3: Déclaration de lv_emp_name_tbl comme type de table de VARCHAR2(50)
• Ligne de code 4: Déclaration de lv_emp_name comme type lv_emp_name_tbl.
• Ligne de code 6 : Ouverture du curseur.
• Ligne de code 7 : Récupération du curseur à l'aide de BULK COLLECT avec la taille LIMIT de 5000 intl lv_emp_name variable.
• Ligne de code 8 à 11 : Configuration de la boucle FOR pour imprimer tous les enregistrements de la collection lv_emp_name.
• Ligne de code 12 : Utilisation de FORALL mettant à jour le salaire de tous les employés de 5000.
• Ligne de code 14 : Validation de la transaction.