Oracle PL/SQL BULK COLLECT: FORALL Eksempel

Hvad er BULK COLLECT?

BULK COLLECT reducerer kontekstskift mellem SQL og PL/SQL-motor og tillader SQL-motor at hente posterne på én gang.

Oracle PL/SQL giver funktionaliteten til at hente posterne i bulk i stedet for at hente én efter én. Denne BULK COLLECT kan bruges i 'SELECT'-sætningen til at udfylde posterne i bulk eller til at hente markøren i bulk. Da BULK COLLECT henter posten i BULK, bør INTO-udtrykket altid indeholde en samlingstypevariabel. Den største fordel ved at bruge BULK COLLECT er, at det øger ydeevnen ved at reducere interaktionen mellem databasen og PL/SQL-motoren.

Syntaks:

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

I ovenstående syntaks bruges BULK COLLECT til at indsamle data fra 'SELECT' og 'FETCH'-sætningen.

FORALL klausul

FORALL gør det muligt at udføre DML-operationer på data i bulk. Det svarer til FOR loop-sætningen undtagen i FOR sløjfe ting sker på rekordniveau, mens der i FORALL ikke er noget LOOP-koncept. I stedet behandles alle data, der er til stede i det givne område, på samme tid.

Syntaks:

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

<DML operations>;

I ovenstående syntaks vil den givne DML-operation blive udført for hele data, der er til stede mellem lavere og højere område.

LIMIT klausul

Masseindsamlingskonceptet indlæser alle data i målindsamlingsvariablen som en bulk, dvs. hele data vil blive udfyldt i indsamlingsvariablen på én gang. Men dette er ikke tilrådeligt, når den samlede post, der skal indlæses, er meget stor, for hvornår PL / SQL forsøger at indlæse alle data, det bruger mere sessionshukommelse. Derfor er det altid godt at begrænse størrelsen af ​​denne bulkopsamlingsoperation.

Denne størrelsesgrænse kan dog nemt opnås ved at introducere ROWNUM-betingelsen i 'SELECT'-sætningen, hvorimod dette ikke er muligt i tilfælde af cursor.

For at overvinde dette Oracle har leveret 'LIMIT'-klausul, der definerer antallet af poster, der skal inkluderes i bulken.

Syntaks:

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

I ovenstående syntaks bruger cursor fetch-sætningen BULK COLLECT-sætningen sammen med LIMIT-sætningen.

BULK COLLECT-attributter

Svarende til markøren attributter BULK COLLECT har %BULK_ROWCOUNT(n), der returnerer antallet af berørte rækker i n^th DML-sætning af FORALL-sætningen, dvs. den vil give antallet af poster, der er påvirket i FORALL-sætningen, for hver enkelt værdi fra samlingsvariablen. Udtrykket 'n' angiver rækkefølgen af ​​værdi i samlingen, for hvilken rækkeantallet er nødvendigt.

Eksempel 1: I dette eksempel vil vi projicere hele medarbejdernavnet fra emp-tabellen ved hjælp af BULK COLLECT, og vi vil også øge lønnen for alle medarbejdere med 5000 ved hjælp af 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;
/

Produktion

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

Kodeforklaring:

• Kodelinje 2: Erklærer markøren guru99_det for sætningen 'SELECT emp_name FROM emp'.
• Kodelinje 3: Erklærer lv_emp_name_tbl som tabeltype for VARCHAR2(50)
• Kodelinje 4: Erklærer lv_emp_name som lv_emp_name_tbl type.
• Kodelinje 6: Åbning af markøren.
• Kodelinje 7: Henter markøren ved hjælp af BULK COLLECT med størrelsen LIMIT som 5000 intl lv_emp_name variabel.
• Kodelinje 8-11: Opsætning af FOR-løkke til at udskrive hele posten i samlingen lv_emp_name.
• Kodelinje 12: Bruger FORALL opdatering af lønnen for alle medarbejdere med 5000.
• Kodelinje 14: Forpligtelse af transaktionen.