Oracle PL/SQL BULK COLLECT: FORALL Exemplu

Ce este BULK COLLECT?

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

Oracle PL/SQL oferă funcționalitatea de preluare a înregistrărilor în bloc, mai degrabă decât de preluare una câte una. Această BULK COLLECT poate fi folosită în instrucțiunea „SELECT” pentru a popula înregistrările în bloc sau pentru a prelua cursorul în bloc. Deoarece BULK COLLECT preia înregistrarea în BULK, 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 <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

În sintaxa de mai sus, BULK COLLECT este utilizat pentru colectarea datelor din instrucțiunile „SELECT” și „FETCH”.

Clauza FORALL

FORALL permite efectuarea operațiunilor DML pe date în vrac. Este similar cu cel al instrucțiunii de buclă FOR, cu excepția în bucla FOR lucrurile se întâmplă la nivel record, în timp ce în FORALL nu există conceptul de LOOP. Î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 care sunt prezente între intervalul inferior și superior.

Clauza LIMIT

Conceptul de colectare în bloc încarcă datele întregi în variabila de colectare țintă în bloc, adică toate datele vor fi populate în variabila de colectare într-o singură trecere. Dar acest lucru nu este recomandabil atunci când înregistrarea totală care trebuie încărcată este foarte mare, deoarece când PL / SQL încearcă să încarce toate datele, consumă mai multă memorie de sesiune. Prin urmare, este întotdeauna bine să limitați dimensiunea acestei operațiuni de colectare în vrac.

Cu toate acestea, această limită de dimensiune poate fi atinsă cu ușurință prin introducerea condiției ROWNUM în instrucțiunea „SELECT”, în timp ce în cazul cursorului 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 cursor fetch folosește instrucțiunea BULK COLLECT împreună cu clauza LIMIT.

Atribute BULK COLLECT

similar cursor atributele BULK COLLECT are %BULK_ROWCOUNT(n) care returnează numărul de rânduri afectate în n^th Instrucțiunea DML a instrucțiunii FORALL, adică va oferi numărul de înregistrări afectate în instrucțiunea FORALL pentru fiecare valoare din variabila de colecție. Termenul „n” indică secvența de valori din colecție, pentru care este necesar numărul de rânduri.

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

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

producție

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

Explicația codului:

• Linia de cod 2: Declararea cursorului guru99_det pentru instrucțiunea „SELECT emp_name FROM emp”.
• Linia de cod 3: Declararea lv_emp_name_tbl ca tip de tabel al VARCHAR2(50)
• Linia de cod 4: Declararea lv_emp_name ca tip lv_emp_name_tbl.
• Linia de cod 6: Deschiderea cursorului.
• Linia de cod 7: Preluarea cursorului folosind BULK COLLECT cu dimensiunea LIMIT ca variabilă 5000 intl lv_emp_name.
• Linia de cod 8-11: Configurarea buclei FOR pentru a tipări toate înregistrările din colecția lv_emp_name.
• Linia de cod 12: Folosind FORALL actualizarea salariului tuturor angajatilor cu 5000.
• Linia de cod 14: Angajarea tranzacției.