Oracle PL/SQL BULK COLLECT : exemple FORALL

Qu’est-ce que la COLLECTE EN VRAC ?

BULK COLLECT réduit les changements de contexte entre les SQL et le moteur PL/SQL, et permet au moteur SQL de récupérer les enregistrements en une seule fois.

Oracle PL / SQL Cette fonction permet de récupérer les enregistrements en masse plutôt qu'un par un. Elle peut être utilisée dans une instruction SELECT pour remplir les enregistrements en bloc ou pour en récupérer un seul. curseur En effet, BULK COLLECT récupère les enregistrements en masse ; la clause INTO doit donc toujours contenir une variable de type collection. Le principal avantage de BULK COLLECT réside dans l’amélioration des performances grâce à la réduction des interactions entre la base de données et le moteur PL/SQL.

syntaxe:

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

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

Clause FORALL

L'instruction FORALL effectue Opérations DML La fonction FORALL traite les données en masse. Elle ressemble à une boucle FOR, à la différence que dans une boucle FOR, les actions s'effectuent au niveau de l'enregistrement, tandis que dans FORALL, il n'y a pas de notion de boucle. Au lieu de cela, toutes les données présentes dans la plage spécifiée sont traitées simultanément.

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 la plage inférieure et la plage supérieure.

Clause LIMITE

Le concept de collecte en bloc charge l'intégralité des données dans la variable de collecte cible en une seule opération. Cependant, cette méthode est déconseillée lorsque le nombre total d'enregistrements à charger est très important, car le chargement complet des données par PL/SQL consomme davantage de mémoire de session. Il est donc toujours préférable de limiter la taille de cette opération de collecte en bloc.

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

Pour surmonter cela, Oracle a fourni la clause LIMIT qui définit le nombre d'enregistrements à inclure dans le lot.

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 ainsi que la clause LIMIT.

BULK COLLECT Les attributs

À l'instar des attributs de curseur, BULK COLLECT possède %BULK_ROWCOUNT(n) qui renvoie le nombre de lignes affectées par la n-ième instruction DML de l'instruction FORALL. Autrement dit, cette méthode indique le nombre d'enregistrements affectés par l'instruction FORALL pour chaque valeur de la variable de collection. Le terme « n » désigne la position de la valeur dans la collection pour laquelle le nombre de lignes est recherché.

Exemple 1: Dans cet exemple, nous allons extraire tous les noms des employés de la table emp à l'aide de BULK COLLECT, et nous allons également augmenter le salaire de tous les employés de 5000 à l'aide de FORALL.

La capture d'écran ci-dessous montre cet exemple de BULK COLLECT et FORALL ainsi que sa sortie dans Oracle.

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 salary=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

Code Explication:

• Code ligne 2: Déclaration du curseur guru99_det pour l'instruction 'SELECT emp_name FROM emp'.
• Code ligne 3: Déclarer lv_emp_name_tbl comme un type de table VARCHAR2(50).
• Code ligne 4: Déclaration de lv_emp_name comme type lv_emp_name_tbl.
• Code ligne 6: Ouverture du curseur.
• Code ligne 7: Récupération du curseur à l'aide de BULK COLLECT avec une taille LIMIT de 5000 dans la variable lv_emp_name.
• Code lignes 8-11 : Mise en place d'une boucle FOR pour imprimer tous les enregistrements de la collection lv_emp_name.
• Code ligne 12: Utilisation de FORALL pour augmenter le salaire de tous les employés de 5000.
• Code ligne 14: S'engager transaction.

FAQ