Oracle PL/SQL BULK COLLECT: Esempio FORALL

By: Fiona Marrone Fiona Marrone
Hours aggiornato

Cos'è il BULK COLLECT?

BULK COLLECT riduce i cambi di contesto tra il motore SQL e PL/SQL e consente al motore SQL di recuperare i record contemporaneamente.

Oracle PL/SQL fornisce la funzionalità di recupero dei record in blocco anziché uno per uno. Questo BULK COLLECT può essere utilizzato nell'istruzione 'SELECT' per popolare i record in blocco o per recuperare il cursore in blocco. Poiché BULK COLLECT recupera il record in BULK, la clausola INTO dovrebbe sempre contenere una variabile di tipo raccolta. Il vantaggio principale dell'utilizzo di BULK COLLECT è che aumenta le prestazioni riducendo l'interazione tra il database e il motore PL/SQL.

Sintassi:

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

Nella sintassi precedente, BULK COLLECT viene utilizzato per raccogliere i dati dalle istruzioni "SELECT" e "FETCH".

Clausola FORALL

Il FORALL consente di eseguire operazioni DML sui dati in blocco. È simile a quello dell'istruzione del ciclo FOR tranne che in Ciclo FOR le cose accadono a livello record mentre in FORALL non esiste il concetto di LOOP. Invece tutti i dati presenti nell'intervallo specificato vengono elaborati contemporaneamente.

Sintassi:

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

<DML operations>;

Nella sintassi precedente, l'operazione DML specificata verrà eseguita per tutti i dati presenti tra l'intervallo inferiore e quello superiore.

Clausola LIMITE

Il concetto di raccolta in blocco carica tutti i dati nella variabile di raccolta di destinazione in blocco, ovvero tutti i dati verranno popolati nella variabile di raccolta in un'unica operazione. Ma questo non è consigliabile quando il record totale da caricare è molto grande, perché quando PL / SQL tenta di caricare tutti i dati consuma più memoria di sessione. Pertanto, è sempre bene limitare la dimensione di questa operazione di raccolta in blocco.

Tuttavia, questo limite di dimensione può essere facilmente raggiunto introducendo la condizione ROWNUM nell'istruzione 'SELECT', mentre nel caso del cursore ciò non è possibile.

Per superare questo Oracle ha fornito la clausola "LIMIT" che definisce il numero di record che devono essere inclusi nella massa.

Sintassi:

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

Nella sintassi precedente, l'istruzione di recupero del cursore utilizza l'istruzione BULK COLLECT insieme alla clausola LIMIT.

Attributi BULK COLLECT

Simile a cursore gli attributi BULK COLLECT hanno %BULK_ROWCOUNT(n) che restituisce il numero di righe interessate nell'nth Dichiarazione DML dell'istruzione FORALL, ovvero fornirà il conteggio dei record interessati nell'istruzione FORALL per ogni singolo valore della variabile di raccolta. Il termine 'n' indica la sequenza di valori nella raccolta, per la quale è necessario il conteggio delle righe.

esempio 1: In questo esempio, proietteremo tutti i nomi dei dipendenti dalla tabella emp utilizzando BULK COLLECT e aumenteremo anche lo stipendio di tutti i dipendenti di 5000 utilizzando FORALL.

Attributi BULK COLLECT

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;
/

ARTICOLI CORRELATI

Uscita

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

Spiegazione del codice:

  • Riga di codice 2: Dichiara il cursore guru99_det per l'istruzione 'SELECT emp_name FROM emp'.
  • Riga di codice 3: Dichiarazione di lv_emp_name_tbl come tipo di tabella di VARCHAR2(50)
  • Riga di codice 4: Dichiarando lv_emp_name come tipo lv_emp_name_tbl.
  • Riga di codice 6: Apertura del cursore.
  • Riga di codice 7: Recupero del cursore utilizzando BULK COLLECT con la dimensione LIMIT come variabile 5000 intl lv_emp_name.
  • Riga di codice 8-11: Impostazione del ciclo FOR per stampare tutti i record nella raccolta lv_emp_name.
  • Riga di codice 12: Utilizzando FORALL aggiornando lo stipendio di tutti i dipendenti entro 5000.
  • Riga di codice 14: Impegno della transazione.

Potrebbe piacerti: