Oracle PL/SQL BULK COLLECT: FORALL Voorbeeld

Wat is BULK COLLECT?

BULK COLLECT vermindert contextwisselingen tussen SQL en PL/SQL-engine en zorgt ervoor dat de SQL-engine de records in één keer kan ophalen.

Oracle PL/SQL biedt de functionaliteit om de records in bulk op te halen in plaats van ze één voor één op te halen. Deze BULK COLLECT kan worden gebruikt in de 'SELECT'-instructie om de records in bulk te vullen of om de cursor in bulk op te halen. Omdat BULK COLLECT het record in BULK ophaalt, moet de INTO-clausule altijd een variabele van het verzamelingstype bevatten. Het belangrijkste voordeel van het gebruik van BULK COLLECT is dat het de prestaties verhoogt door de interactie tussen de database en de PL/SQL-engine te verminderen.

Syntax:

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

In de bovenstaande syntaxis wordt BULK COLLECT gebruikt om de gegevens uit de 'SELECT'- en 'FETCH'-instructie te verzamelen.

FORALL-clausule

De FORALL maakt het mogelijk om DML-bewerkingen op data in bulk uit te voeren. Het is vergelijkbaar met de FOR-lusinstructie, behalve in For loop dingen gebeuren op recordniveau, terwijl er in FORALL geen LOOP-concept is. In plaats daarvan worden alle gegevens in het gegeven bereik tegelijkertijd verwerkt.

Syntax:

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

<DML operations>;

In de bovenstaande syntaxis wordt de gegeven DML-bewerking uitgevoerd voor alle gegevens die zich tussen het lagere en hogere bereik bevinden.

LIMIT-clausule

Het bulkcollect-concept laadt de volledige gegevens in bulk in de doelverzamelingsvariabele, dat wil zeggen dat de volledige gegevens in één keer in de verzamelingsvariabele worden gevuld. Maar dit is niet aan te raden als het totale record dat moet worden geladen erg groot is, want wanneer PL / SQL probeert alle data te laden, verbruikt het meer sessiegeheugen. Daarom is het altijd goed om de grootte van deze bulkverzamelbewerking te beperken.

Deze groottelimiet kan echter eenvoudig worden bereikt door de ROWNUM-voorwaarde in de 'SELECT'-instructie te introduceren, terwijl dit in het geval van de cursor niet mogelijk is.

Om dit te overwinnen Oracle heeft een 'LIMIT'-clausule geleverd die het aantal records definieert dat in de bulk moet worden opgenomen.

Syntax:

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

In de bovenstaande syntaxis gebruikt de cursorfetch-instructie de BULK COLLECT-instructie samen met de LIMIT-clausule.

BULK COLLECT-attributen

Soortgelijke cursor attributen BULK COLLECT heeft %BULK_ROWCOUNT(n) dat het aantal betrokken rijen in de n retourneert^th DML-instructie van de FORALL-instructie, dwz het geeft het aantal records dat in de FORALL-instructie is beïnvloed voor elke afzonderlijke waarde uit de verzamelingsvariabele. De term 'n' geeft de reeks waarden in de verzameling aan, waarvoor het aantal rijen nodig is.

Voorbeeld 1: In dit voorbeeld projecteren we de volledige werknemersnaam uit de emp-tabel met behulp van BULK COLLECT en we gaan ook het salaris van alle werknemers met 5000 verhogen met behulp van 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;
/

uitgang

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

Code Verklaring:

• Coderegel 2: De cursor guru99_det declareren voor instructie 'SELECT emp_name FROM emp'.
• Coderegel 3: lv_emp_name_tbl declareren als tabeltype van VARCHAR2(50)
• Coderegel 4: lv_emp_name declareren als lv_emp_name_tbl type.
• Coderegel 6: De cursor openen.
• Coderegel 7: De cursor ophalen met BULK COLLECT met de LIMIT-grootte als 5000 intl lv_emp_name variabele.
• Coderegel 8-11: De FOR-lus instellen om alle records in de verzameling lv_emp_name af te drukken.
• Coderegel 12: FORALL gebruiken om het salaris van alle werknemers bij te werken met 5000.
• Coderegel 14: Het plegen van de transactie.