Kontakt
+421 911 065 745
V PL/SQL sa funkcia BULK COLLECT používa na zvýšenie výkonu SQL dopytov načítaním viacerých riadkov naraz a ich ukladaním do kolekcií (polia alebo vnorené tabuľky) namiesto spracovania jedného riadka naraz. To znižuje počet kontextových prepínačov medzi SQL a PL/SQL, čo vedie k vyššej efektivite. Funkcia Bulk Collect sa často používa v spojení s príkazom FORALL, ktorý vám umožňuje efektívne vykonávať hromadné operácie DML. Základná syntax príkazu BULK COLLECT vyzerá:
DECLARE
TYPE collection_type IS TABLE OF table_name%ROWTYPE;
collection_name collection_type;
BEGIN
SELECT column1, column2, ...
BULK COLLECT INTO collection_name
FROM table_name
WHERE condition;
-- Process the data in the collection
END;
V syntaxi je typ_kolekcie PL/SQL (buď pole alebo vnorená tabuľka), ktorý by mal byť kompatibilný so štruktúrou vybratých stĺpcov.
Zlepšený výkon
Obmedzené prepínanie medzi obsahom: Načítaním viacerých riadkov naraz BULK COLLECT minimalizuje počet kontextových prepínačov medzi SQL a PL/SQL, čím sa zvyšuje výkon.
Znížená réžia príkazov SQL: Vykonanie jedného príkazu BULK COLLECT je často efektívnejšie ako vykonávanie viacerých individuálnych dotazov.
Úvahy o používaní
Využitie pamäte: Keďže BULK COLLECT načítava viaceré riadky do kolekcie, mali by ste zvážiť potenciálne zvýšenie spotreby pamäte, najmä pri práci s veľkými sadami výsledkov.
Selektivita: Najvýhodnejšie je, keď je klauzula WHERE dostatočne selektívna tzn. je viacej parametrov v podmienke where, aby sa znížil počet načítaných riadkov.
Príklad pre BULK COLLECT vyzerá nasledovne:
DECLARE
TYPE employee_collection IS TABLE OF employees%ROWTYPE;
emp_data employee_collection;
BEGIN
SELECT *
BULK COLLECT INTO emp_data
FROM employees
WHERE department_id = 10;
-- Process the data in the emp_data collection
FOR i IN 1..emp_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_data(i).employee_id || ', Name: ' || emp_data(i).first_name);
END LOOP;
END;
V tomto príklade sa do kolekcie emp_data načítajú viaceré riadky z tabuľky zamestnancov a údaje sa potom spracúvajú v rámci cyklu. Tzn najprv sa všetky údaje napoja v kolekcií a nasledne sú cez cyklus volané. Ďalšou možnosťou, torchu však komplikovanejšou, je využiť kurzor na načítanie údajov a následne BULK COLLECT:
DECLARE
--definicia kurzora
TYPE empcurtyp IS REF CURSOR;
--tabulka vsetkych mien
TYPE namelist IS TABLE OF employees.last_name%TYPE;
--tabulka vsetkych salary
TYPE sallist IS TABLE OF employees.salary%TYPE;
emp_cv empcurtyp;
names namelist;
sals sallist;
BEGIN
--otvorenie cursora pre select
OPEN emp_cv FOR
SELECT last_name, salary FROM employees
WHERE job_id = 'SA_REP'
ORDER BY salary DESC;
--Fetch do kolekcie
FETCH emp_cv
BULK COLLECT INTO names, sals;
--uzavretie kurzora
CLOSE emp_cv;
-- loop through the names and sals collections
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE
('Name = ' || names(i) || ', salary = ' || sals(i));
END LOOP;
END;
Veľmi silným nástrojom pre prácu s BULK collect je na základe výsledkov robiť zmeny v tabuľkách a využiť ho ako napr. merge len s oveľa vyšším výkonom. Príklad vyzerá takto:
DECLARE
P_MESSAGE_TEXT VARCHAR2(1500);
TYPE recData IS RECORD (
row_id ROWID,
employee_id hr.employees.employee_id%TYPE,
first_name hr.employees.first_name%TYPE,
last_name hr.employees.last_name%TYPE,
email hr.employees.email%TYPE,
phone_number hr.employees.phone_number%TYPE,
salary hr.employees.salary%TYPE,
hire_date hr.employees.hire_date%TYPE,
job_id hr.employees.job_id%TYPE,
manager_id hr.employees.manager_id%TYPE,
department_id hr.employees.department_id%TYPE
);
--nemozeme dat lebo rowid-----TYPE tabDataType IS TABLE OF hr.employees%ROWTYPE INDEX BY PLS_INTEGER;
TYPE tabDataType IS TABLE OF recData INDEX BY PLS_INTEGER;
TYPE tabPointerType IS TABLE OF PLS_INTEGER;
TYPE curDataType IS REF CURSOR;
curData curDataType;
tabData tabDataType;
tabInsPointer tabPointerType := tabPointerType();
tabUpdPointer tabPointerType := tabPointerType();
vSqlStatm varchar(3000);
nRecProcessed number;
BEGIN
vSqlStatm := 'SELECT e.ROWID row_id,
a.employee_id,
a.first_name first_name,
a.last_name last_name,
a.email email,
a.phone_number phone_number,
a.salary salary,
a.hire_date hire_date,
d.job_id job_id,
a.manager_id manager_id,
b.department_id department_id
FROM hr.emptst a,
hr.departments b,
hr.jobs d,
hr.employees e
WHERE b.department_id = a.department_id
and a.job_id=d.job_id
and a.employee_id=e.employee_id
union all
SELECT NULL row_id,
a.employee_id,
a.first_name first_name,
a.last_name last_name,
a.email email,
a.phone_number phone_number,
a.salary salary,
a.hire_date hire_date,
d.job_id job_id,
a.manager_id manager_id,
b.department_id department_id
FROM hr.emp_temp a,
hr.departments b,
hr.jobs d
WHERE b.department_id = a.department_id
and a.job_id=d.job_id
and NOT EXISTS (select 1 from employees where employee_id=a.employee_id)';
OPEN curData FOR vSqlStatm;
LOOP
tabData.DELETE;
tabInsPointer.DELETE;
tabUpdPointer.DELETE;
FETCH curData
BULK COLLECT INTO tabData;
FOR i IN tabData.FIRST .. tabData.LAST LOOP
CASE
WHEN tabData(i).row_id IS NULL
THEN
tabInsPointer.EXTEND;
tabInsPointer(tabInsPointer.LAST) := i;
ELSE
tabUpdPointer.EXTEND;
tabUpdPointer(tabUpdPointer.LAST) := i;
END CASE;
END LOOP;
FORALL i IN VALUES OF tabInsPointer
INSERT INTO hr.employees (
employee_id,
first_name,
last_name,
email,
phone_number,
salary,
hire_date,
job_id,
manager_id,
department_id
)
VALUES (
tabData(i).employee_id,
tabData(i).first_name,
tabData(i).last_name,
tabData(i).email,
tabData(i).phone_number,
tabData(i).salary,
tabData(i).hire_date,
tabData(i).job_id,
tabData(i).manager_id,
tabData(i).department_id
);
nRecProcessed := nRecProcessed + SQL%ROWCOUNT;
FORALL i IN VALUES OF tabUpdPointer
UPDATE hr.employees SET
employee_id = tabData(i).employee_id,
first_name = tabData(i).first_name,
last_name = tabData(i).last_name,
email = tabData(i).email,
phone_number = tabData(i).phone_number,
salary = tabData(i).salary,
hire_date =tabData(i).hire_date,
--job_id = tabData(i).job_id,
manager_id = tabData(i).manager_id
--department_id = tabData(i).department_id
WHERE ROWID = tabData(i).row_id;
nRecProcessed := nRecProcessed + SQL%ROWCOUNT;
EXIT WHEN curData%NOTFOUND;
END LOOP;
CLOSE curData;
exception when others
then DBMS_OUTPUT.PUT_LINE(P_MESSAGE_TEXT);
END;
V príklade sa záznamy doplnaju len ak je match medzi novými záznamami uvedenými v emp_temp tabuľke a upravujú len tie ktoré sú v emp_test tabuľke tzn. robí sa len výsek dát ktoré sa spracúvajú hromadne. Takto je postavená logika spracovania veľkého množstva dát cez DWH kde BULK vyhovuje.
BULK COLLECT je výkonná funkcia v PL/SQL, ktorá môže výrazne zvýšiť výkon operácií získavania údajov, najmä pri práci s veľkými množinami údajov. Treba ho však používať uvážlivo, berúc do úvahy faktory ako spotreba pamäte a selektivita. Ideálne s inmemorry database.
+421 911 065 745