Oracle PL/SQL BULK COLLECT: FORALL Exemplu

Ce este BULK COLLECT?

BULK COLLECT reduce schimbările de context între SQL și motorul PL/SQL și permite motorului SQL să preia înregistrările simultan.

Oracle PL / SQL oferă funcționalitatea de preluare a înregistrărilor în bloc, în loc de preluarea lor individuală. Această instrucțiune BULK COLLECT poate fi utilizată într-o instrucțiune SELECT pentru a popula înregistrările în bloc sau pentru a prelua o cursor în bloc. Deoarece BULK COLLECT preia înregistrările în bloc, clauza INTO ar trebui să conțină întotdeauna o variabilă de tip colecție. Principalul avantaj al utilizării BULK COLLECT este că crește performanța prin reducerea interacțiunii dintre baza de date și motorul PL/SQL.

Sintaxă:

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

În sintaxa de mai sus, BULK COLLECT este utilizat pentru a colecta datele din instrucțiunile SELECT și FETCH.

Clauza FORALL

Instrucțiunea FORALL execută Operațiuni DML pe date în bloc. Seamănă cu o instrucțiune de buclă FOR, cu excepția faptului că într-o buclă FOR acțiunile au loc la nivel de înregistrare, în timp ce în FORALL nu există conceptul de BUCLĂ. În schimb, toate datele prezente în intervalul dat sunt procesate în același timp.

Sintaxă:

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

<DML operations>;

În sintaxa de mai sus, operația DML dată va fi executată pentru toate datele prezente între intervalul inferior și cel superior.

Clauza LIMIT

Conceptul de colectare în bloc încarcă toate datele în variabila de colectare țintă ca o operațiune în bloc, adică toate datele vor fi populate în variabila de colectare dintr-o singură dată. Însă acest lucru nu este recomandabil atunci când numărul total de înregistrări care trebuie încărcate este foarte mare, deoarece atunci când PL/SQL încearcă să încarce toate datele, consumă mai multă memorie de sesiune. Prin urmare, este întotdeauna bine să se limiteze dimensiunea acestei operațiuni de colectare în bloc.

Această limită de dimensiune poate fi ușor atinsă prin introducerea condiției ROWNUM în instrucțiunea SELECT, în timp ce în cazul unui cursor acest lucru nu este posibil.

Pentru a depăși acest lucru, Oracle a furnizat clauza LIMIT care definește numărul de înregistrări care trebuie incluse în bloc.

Sintaxă:

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

În sintaxa de mai sus, instrucțiunea fetch a cursorului utilizează instrucțiunea BULK COLLECT împreună cu clauza LIMIT.

Atribute BULK COLLECT

Similar atributelor cursorului, BULK COLLECT are %BULK_ROWCOUNT(n) care returnează numărul de rânduri afectate în a n-a instrucțiune DML a instrucțiunii FORALL, adică oferă numărul de înregistrări afectate în instrucțiunea FORALL pentru fiecare valoare din variabila colecției. Termenul „n” indică secvența valorii din colecție pentru care este necesar numărul de rânduri.

Exemplu 1: În acest exemplu, vom proiecta toate numele angajaților din tabela emp folosind BULK COLLECT și vom crește, de asemenea, salariul tuturor angajaților cu 5000 folosind FORALL.

Captura de ecran de mai jos prezintă acest exemplu BULK COLLECT și FORALL împreună cu rezultatul său în 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;
/

producție

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

Code Explicaţie:

• Code linia 2: Declararea cursorului guru99_det pentru instrucțiunea „SELECT emp_name FROM emp”.
• Code linia 3: Se declară lv_emp_name_tbl ca un tip de tabel de tip VARCHAR2(50).
• Code linia 4: Declararea lui lv_emp_name ca fiind tipul lv_emp_name_tbl.
• Code linia 6: Deschiderea cursorului.
• Code linia 7: Preluarea cursorului folosind BULK COLLECT cu dimensiunea LIMIT de 5000 în variabila lv_emp_name.
• Code liniile 8-11: Configurarea unei bucle FOR pentru a afișa toate înregistrările din colecția lv_emp_name.
• Code linia 12: Se utilizează FORALL pentru a actualiza salariul tuturor angajaților cu 5000.
• Code linia 14: Angajarea tranzacție.

Întrebări frecvente