Oracle PL/SQL BULK COLLECT: FORALL Voorbeeld

Wat is BULK COLLECT?

BULK COLLECT vermindert contextwisselingen tussen de SQL en een PL/SQL-engine, waardoor de SQL-engine de records in één keer kan ophalen.

Oracle PL / SQL Deze functie biedt de mogelijkheid om records in bulk op te halen in plaats van één voor één. BULK COLLECT kan worden gebruikt in een SELECT-statement om records in bulk te vullen of om een ​​lijst met records op te halen. cursor In bulk. Omdat BULK COLLECT de records in bulk ophaalt, moet de INTO-clausule altijd een variabele van het type 'collectie' bevatten. Het belangrijkste voordeel van het gebruik van BULK COLLECT is dat het de prestaties verbetert door de interactie tussen de database en de PL/SQL-engine te verminderen.

Syntax:

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

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

FORALL-clausule

De FORALL-instructie voert de volgende taken uit: DML-bewerkingen Bij het verwerken van grote hoeveelheden data. Het lijkt op een FOR-lus, met dit verschil dat acties in een FOR-lus op recordniveau plaatsvinden, terwijl er in FORALL geen lusconcept is. In plaats daarvan wordt alle data binnen het opgegeven bereik tegelijkertijd verwerkt.

Syntax:

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

<DML operations>;

In de bovenstaande syntax wordt de opgegeven DML-bewerking uitgevoerd voor alle gegevens die zich binnen het bereik van de onder- en bovengrens bevinden.

LIMIT-clausule

Het bulkcollectieconcept laadt alle gegevens in één keer in de doelcollectievariabele, oftewel alle gegevens worden in één keer in de collectievariabele geladen. Dit is echter niet aan te raden wanneer het totale aantal te laden records erg groot is, omdat PL/SQL dan meer sessiegeheugen verbruikt. Daarom is het altijd verstandig om de omvang van deze bulkcollectiebewerking te beperken.

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

Om dit te overwinnen, Oracle heeft de LIMIT-clausule toegevoegd, die het aantal records definieert dat in de bulkverwerking moet worden opgenomen.

Syntax:

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

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

BULK COLLECT-attributen

Net als cursorattributen heeft BULK COLLECT de parameter %BULK_ROWCOUNT(n), die het aantal rijen retourneert dat wordt beïnvloed in de n-de DML-instructie van de FORALL-instructie. Dit betekent dat het aantal records wordt geteld dat wordt beïnvloed in de FORALL-instructie voor elke afzonderlijke waarde uit de verzamelingsvariabele. De term 'n' geeft de volgorde aan van de waarde in de verzameling waarvoor het aantal rijen nodig is.

Voorbeeld 1: In dit voorbeeld projecteren we alle werknemersnamen uit de tabel 'emp' met behulp van BULK COLLECT, en verhogen we tevens het salaris van alle werknemers met 5000 met behulp van FORALL.

De onderstaande schermafbeelding toont dit BULK COLLECT- en FORALL-voorbeeld, samen met de bijbehorende uitvoer. 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;
/

uitgang

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

Code Uitleg:

• Code lijn 2: De cursor guru99_det wordt gedeclareerd voor de instructie 'SELECT emp_name FROM emp'.
• Code lijn 3: De tabel lv_emp_name_tbl declareren als een tabeltype van VARCHAR2(50).
• Code lijn 4: Declaratie van lv_emp_name als het type lv_emp_name_tbl.
• Code lijn 6: De cursor openen.
• Code lijn 7: De cursor ophalen met BULK COLLECT met een LIMIT-grootte van 5000 in de variabele lv_emp_name.
• Code regel 8-11: Een FOR-lus instellen om alle records in de collectie lv_emp_name af te drukken.
• Code lijn 12: De functie FORALL gebruiken om het salaris van alle werknemers met 5000 te verhogen.
• Code lijn 14: Het vastleggen van de transactie.

Veelgestelde vragen