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 nth 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
Code Forklaring:
- Code line 2: Erklรฆrer markรธren guru99_det for sรฆtningen 'SELECT emp_name FROM emp'.
- Code line 3: Erklรฆrer lv_emp_name_tbl som tabeltype for VARCHAR2(50)
- Code line 4: Erklรฆrer lv_emp_name som lv_emp_name_tbl type.
- Code linje 6: ร bning af markรธren.
- Code linje 7: Henter markรธren ved hjรฆlp af BULK COLLECT med stรธrrelsen LIMIT som 5000 intl lv_emp_name variabel.
- Code linje 8-11: Opsรฆtning af FOR-lรธkke til at udskrive hele posten i samlingen lv_emp_name.
- Code linje 12: Bruger FORALL opdatering af lรธnnen for alle medarbejdere med 5000.
- Code linje 14: Forpligtelse af transaktionen.

