Oracle PL/SQL BULK COLLECT: Esempio FORALL

By: Fiona Marrone Fiona Marrone
Hours aggiornato

⚡ Riepilogo intelligente

RITIRO ALL'INGROSSO in Oracle PL/SQL recupera molte righe contemporaneamente in una collection, mentre FORALL invia operazioni DML in blocco al database. Entrambi riducono i cambi di contesto tra i motori SQL e PL/SQL, migliorando le prestazioni.

  • 📦 RITIRO ALL'INGROSSO: Consente di recuperare più righe in un'unica operazione e di memorizzarle in una variabile di tipo collezione, sostituendo il lento recupero riga per riga.
  • 🔁 PER TUTTI: Esegue un'unica operazione di INSERT, UPDATE o DELETE sull'intera collezione con un singolo cambio di contesto.
  • 📏 Clausola LIMITATIVA: Limita il numero di righe che ogni operazione di BULK COLLECT può caricare, proteggendo la memoria di sessione su tabelle di grandi dimensioni.
  • 📊 Attributi di BULK COLLECT: L'attributo %BULK_ROWCOUNT(n) indica quante righe sono state interessate dall'n-esima istruzione DML FORALL.
  • ⚙️ Raccolta di materiali necessari: La clausola INTO deve fare riferimento a un tipo di collezione, come una tabella nidificata o un array associativo.
  • 🤖 Assistenza AI: Gli assistenti basati sull'intelligenza artificiale, come GitHub Copilot, redigono blocchi BULK COLLECT e FORALL e segnalano l'eventuale mancanza di una clausola LIMIT.
Scopri di più

Oracle Panoramica sull'utilizzo di PL/SQL BULK COLLECT e FORALL con clausola LIMIT

Cos'è il BULK COLLECT?

BULK COLLECT riduce i cambi di contesto tra SQL e motore PL/SQL e consente al motore SQL di recuperare i record in una sola volta.

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

Sintassi:

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

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

Clausola FORALL

L'istruzione FORALL esegue Operazioni DML sui dati in blocco. Assomiglia a un ciclo FOR, tranne per il fatto che in un ciclo FOR le azioni avvengono a livello di record, mentre in FORALL non esiste il concetto di ciclo. Invece, tutti i dati presenti nell'intervallo specificato vengono elaborati contemporaneamente.

Sintassi:

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

<DML operations>;

Nella sintassi sopra riportata, l'operazione DML specificata verrà eseguita sull'intera quantità di dati presenti tra il limite inferiore e quello superiore.

Clausola LIMITE

Il concetto di "bulk collect" carica tutti i dati nella variabile di destinazione in blocco, ovvero tutti i dati vengono inseriti nella variabile in un'unica operazione. Tuttavia, questo approccio non è consigliabile quando il numero totale di record da caricare è molto elevato, perché quando PL/SQL tenta di caricare tutti i dati consuma più memoria di sessione. Pertanto, è sempre opportuno limitare le dimensioni di questa operazione di "bulk collect".

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

Per superare questo, Oracle ha fornito la clausola LIMIT che definisce il numero di record che devono essere inclusi nel blocco.

Sintassi:

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

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

Attributi BULK COLLECT

Analogamente agli attributi del cursore, BULK COLLECT ha %BULK_ROWCOUNT(n) che restituisce il numero di righe interessate nell'n-esima istruzione DML dell'istruzione FORALL, ovvero fornisce il conteggio dei record interessati nell'istruzione FORALL per ogni singolo valore della variabile di raccolta. Il termine 'n' indica la sequenza del valore nella raccolta per cui è 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.

Lo screenshot qui sotto mostra questo esempio BULK COLLECT e FORALL insieme al suo output in Oracle.

RACCOLTA IN BLOCCO con LIMIT e FORALL esempio di aggiornamento dello stipendio del dipendente in Oracle PL / SQL

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

ARTICOLI CORRELATI

Uscita

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

Code Spiegazione:

  • Code riga 2: Dichiarazione del cursore guru99_det per l'istruzione 'SELECT emp_name FROM emp'.
  • Code riga 3: Dichiarazione di lv_emp_name_tbl come tipo di tabella VARCHAR2(50).
  • Code riga 4: Dichiarazione di lv_emp_name come tipo lv_emp_name_tbl.
  • Code riga 6: Apertura del cursore.
  • Code riga 7: Recupero del cursore tramite BULK COLLECT con dimensione LIMIT pari a 5000 nella variabile lv_emp_name.
  • Code righe 8-11: Impostazione di un ciclo FOR per stampare tutti i record nella collezione lv_emp_name.
  • Code riga 12: Utilizzo della funzione FORALL per aggiornare lo stipendio di tutti i dipendenti di 5000.
  • Code riga 14: Commettere il delle transazioni.

DOMANDE FREQUENTI

No. Una BULK COLLECT SELECT non genera mai NO_DATA_FOUND; restituisce invece una collection vuota. Testa sempre la collection con il metodo .COUNT prima di cercareping, altrimenti potresti elaborare zero righe senza alcun messaggio di errore.

SAVE EXCEPTIONS consente a FORALL di continuare a funzionare quando le singole righe non riescono. Le righe non riuscite vengono memorizzate in SQL%BULK_EXCEPTIONS, quindi Oracle genera ORA-24381, che viene intercettato in un eccezione gestore per ispezionare ogni errore.

Utilizzare BULK COLLECT ogni volta che un ciclo legge molte righe. cursore Il ciclo FOR recupera una riga per ogni operazione, quindi il recupero in blocco combinato con FORALL può risultare molto più veloce su set di risultati di grandi dimensioni.

BULK COLLECT restituisce molte righe contemporaneamente, quindi necessita di un contenitore multi-riga. La destinazione INTO deve essere un collezione come ad esempio una tabella annidata, un VARRAY o un array associativo, non una singola variabile scalare.

No. Un'intestazione FORALL gestisce esattamente un'operazione INSERT, UPDATE, DELETE o MERGE. Solo i valori nelle clausole VALUES e WHERE possono cambiare a ogni iterazione. Per più istruzioni, utilizzare istruzioni FORALL separate.

L'elaborazione in blocco può essere da diverse a oltre cento volte più veloce rispetto all'elaborazione riga per riga, perché BULK COLLECT e FORALL comprimono migliaia di cambi di contesto del motore in pochi, riducendo drasticamente il sovraccarico su grandi volumi di dati.

Sì. Le serrature scorrevoli portatili e i catenacci a superficie possono essere usati per mettere in sicurezza una porta a scomparsa dall'esterno. Alcuni kit con catena di sicurezza consentono anche il bloccaggio esterno con chiave o manopola girevole. Copilota GitHub Il codice genera bozze di operazioni BULK COLLECT, cicli DML FORALL e clausole LIMIT a partire da un commento, e suggerisce dichiarazioni di tipi di raccolta, sebbene sia consigliabile rivedere autonomamente le dimensioni dei batch e la gestione degli errori.

Gli assistenti basati sull'intelligenza artificiale analizzano i cicli che recuperano o modificano una riga alla volta e suggeriscono di riscriverli utilizzando BULK COLLECT, LIMIT e FORALL. Questa analisi basata sull'apprendimento automatico individua i limiti mancanti e i colli di bottiglia delle prestazioni prima della messa in produzione.

Riassumi questo post con: