BULK COLLECT e FORALL
Prima di tutto ricordiamo che fa la fetch:
FETCH <nome cursore> [(<lista di variabili>)];
Il comando fetch assegna i valori degli attributi selezionati dal cursore (e quindi una singola riga alla volta) ad una lista di variabili.
Dopo un comando di fetch, il cursore avanza alla successiva tupla nell'insieme del risultato dell’istruzione select ottenuto con il comando open.
Da notare che le variabili nella lista devono avere lo stesso tipo di dati dei valori delle tuple selezionate. Dopo che tutte le tuple sono state processate, si utilizza il comando close per chiudere e disabilitare il cursore.
BULK COLLECTION viene usato quando si vogliono estrarre righe multiple con una sola operazione di fetch.
FORALL: usato per effettuare insert, update e delete di righe multiple contemporaneamente.
In una procedura pl/sql possono esserci istruzioni pl/sql che vengono eseguite dal motore PL/SQL e istruzioni sql che vengono eseguite dal motore SQL.
Lo switch tra i due motori rallenta il programma (noto come context switches).
Si parla infatti di "row by row" switch che viene anche indicato come "slow by slow processing".
Bulk Collect esegue un solo context switch per raccogliere (fetch) più righe contemporaneamente in una collection.
Forall impacchetta tutte le dml e le passa al motore SQL in un singolo context switch aumentando così le prestazioni. Se occorre effettuare 2 update e 1 delete occorre utilizzare tre FORALL statement.
Esempio
La tabella A contiene due colonne b e c. La procedura ha come parametro di ingresso il numero di righe che devono essere lette dalla istruzione bulk.
La procedura restituisce in output le colonne lette della tabella A lette
CREATE OR REPLACE PROCEDURE popolatabellabulk1 ( righe IN NUMBER) IS
TYPE a_cols_rt IS RECORD (
b a.b%TYPE,
c a.c%TYPE );
TYPE a_rt IS TABLE OF a_cols_rt;
l_a a_rt;
CURSOR a_cur IS
SELECT b,c FROM a@dbltest;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur
BULK COLLECT INTO l_a
LIMIT righe;
dbms_output.put_line('letti: '||l_a.COUNT);
FOR indx IN 1 .. l_a.COUNT
LOOP
dbms_output.put_line('b: '||l_a(indx).b||' c: '||l_a(indx).c);
END LOOP;
EXIT WHEN l_a.COUNT = 0;
END LOOP;
CLOSE a_cur;
END popolatabellabulk1;
/
Se il parametro righe è maggiore del numero delle righe della tabella A (nell'esempio sono 9 record), allora viene eseguita una sola fetch :
SQL> set serveroutput on;
SQL> execute popolatabellabulk1(10);
letti: 9
b: 2 c: a
b: 3 c: a
b: 4 c: a
b: 5 c: a
b: 6 c: a
b: 7 c: a
b: 8 c: a
b: 9 c: a
b: 10 c: a
letti: 0
PL/SQL procedure successfully completed.
FETCH <nome cursore> [(<lista di variabili>)];
Il comando fetch assegna i valori degli attributi selezionati dal cursore (e quindi una singola riga alla volta) ad una lista di variabili.
Dopo un comando di fetch, il cursore avanza alla successiva tupla nell'insieme del risultato dell’istruzione select ottenuto con il comando open.
Da notare che le variabili nella lista devono avere lo stesso tipo di dati dei valori delle tuple selezionate. Dopo che tutte le tuple sono state processate, si utilizza il comando close per chiudere e disabilitare il cursore.
BULK COLLECTION viene usato quando si vogliono estrarre righe multiple con una sola operazione di fetch.
FORALL: usato per effettuare insert, update e delete di righe multiple contemporaneamente.
In una procedura pl/sql possono esserci istruzioni pl/sql che vengono eseguite dal motore PL/SQL e istruzioni sql che vengono eseguite dal motore SQL.
Lo switch tra i due motori rallenta il programma (noto come context switches).
Si parla infatti di "row by row" switch che viene anche indicato come "slow by slow processing".
Bulk Collect esegue un solo context switch per raccogliere (fetch) più righe contemporaneamente in una collection.
Forall impacchetta tutte le dml e le passa al motore SQL in un singolo context switch aumentando così le prestazioni. Se occorre effettuare 2 update e 1 delete occorre utilizzare tre FORALL statement.
Esempio
La tabella A contiene due colonne b e c. La procedura ha come parametro di ingresso il numero di righe che devono essere lette dalla istruzione bulk.
La procedura restituisce in output le colonne lette della tabella A lette
CREATE OR REPLACE PROCEDURE popolatabellabulk1 ( righe IN NUMBER) IS
TYPE a_cols_rt IS RECORD (
b a.b%TYPE,
c a.c%TYPE );
TYPE a_rt IS TABLE OF a_cols_rt;
l_a a_rt;
CURSOR a_cur IS
SELECT b,c FROM a@dbltest;
BEGIN
OPEN a_cur;
LOOP
FETCH a_cur
BULK COLLECT INTO l_a
LIMIT righe;
dbms_output.put_line('letti: '||l_a.COUNT);
FOR indx IN 1 .. l_a.COUNT
LOOP
dbms_output.put_line('b: '||l_a(indx).b||' c: '||l_a(indx).c);
END LOOP;
EXIT WHEN l_a.COUNT = 0;
END LOOP;
CLOSE a_cur;
END popolatabellabulk1;
/
Se il parametro righe è maggiore del numero delle righe della tabella A (nell'esempio sono 9 record), allora viene eseguita una sola fetch :
SQL> set serveroutput on;
SQL> execute popolatabellabulk1(10);
letti: 9
b: 2 c: a
b: 3 c: a
b: 4 c: a
b: 5 c: a
b: 6 c: a
b: 7 c: a
b: 8 c: a
b: 9 c: a
b: 10 c: a
letti: 0
PL/SQL procedure successfully completed.
Se invece il parametro righe è inferiore alle righe della tabella vengono effettuate n fetch.
SQL> execute popolatabellabulk1(4);
letti: 4
b: 2 c: a
b: 3 c: a
b: 4 c: a
b: 5 c: a
letti: 4
b: 6 c: a
b: 7 c: a
b: 8 c: a
b: 9 c: a
letti: 1
b: 10 c: a
letti: 0
PL/SQL procedure successfully completed.
