Bulk Collect And Forall in Oracle
BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
FORALL: inserts, updates, and deletes that use collections to change multiple rows of data very quickly
CONTEXT SWITCHES
Whenever we write a code , it contains pl/sql and Sql. PL/SQL code are executed by PL/SQL Engine but SQL statements are sent to the SQL Engine for processing by PL/SQL engine. Then SQL engine process the information and returns the result back to PL/SQL Engine. This Trnasfer of control is called context switching.
Context switches also happens from SQL Engine to PL/SQL Engine. When we call a user defined function from SQL statements. In this case SQL Engine encounters a function then handed over the control to PL/SQL engine then PL/SQL engine performs operations and transfer the control back to SQL engine with results.
BULK processing in Oracle PL/SQL
This is designed to reduce the number of context switches required to communicate from PL/SQL engine to SQL engine.
Use the bulk collect to fetch multiple rows into one or more collections with a single contect switch.
Use FORALL statement when we need to execute DML statement repeatedly for different bind variable.(INSERT UPDATE & DELETE)
Example Bulk processing.
CREATE OR REPLACE PROCEDURE sal_hike (
Deptno IN emp.deptno%TYPE,
Inc_pct IN NUMBER
)
IS
TYPE emp_col IS TABLE OF emp.empid%type INDEX BY PLS_INTEGER;
V_emp_col emp_col;
BEGIN
SELECT empid BULK COLLECT INTO v_emp_col FROM emp WHERE deptno=deptno;
FORALL ind IN 1..v_emp_col.COUNT
UPDATE emp SET sal=sal+sal*inc_pct WHERE empno=v_emp_col(ind);
END sal_hike;
Bulk COLLECT
It can be used with all three types of collection(ASSOCIATIVE ARRAY, VARRAY, NESTED TABLE).
The Collection is always populated densely , starting from index 1.
If no rows fetched , then collection is empty.
Example
DECLARE
TYPE emp_rec IS RECORD
(
Empno emp.empno%TYPE,
Sal emp.sal%TYPE
);
TYPE emp_col IS TABLE OF emp_rec INDEXED BY PLS_INTEGER;
V_emp_col emp_col;
BEGIN
SELECT empno,sal BULK COLLECT INTO v_emp_col from emp WHERE deptno=10;
END;
In case of cursor Use
FETCH- BULK COLLECT INTO
DECLARE
TYPE emp_rec IS RECORD
(
Empno emp.empno%TYPE,
Sal emp.sal%TYPE
);
TYPE emp_col IS TABLE OF emp_rec INDEXED BY PLS_INTEGER;
Cursor emp_cur IS SELECT emono,sal FROM emp WHERE deptno=10;
V_emp_col emp_col;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur BULK COLLECT INTO v_emp_col LIMIT 200;
EXIT WHEN emp_cur%NOTFOUND;
END LOOP;
END;
NOTE : LIMIT is used to restrict the number of record fetch in one ontext switch. It helps us to increase memory utilization.
FORALL
FORALL is not a loop. It is a declarative statement to the PL/SQL engine that generate all the DML statement that would have been executed one row at a time and send them all across to the SQL engine.
When collection is not fully densely filled , then use INDICES OF orvalues OF in FORALL.
If we want the PL/SQL engine to execute as many as DML statement possible , even if errors raised along the way , add a SAVE EXCEPTIONS clause to the FORALL header.Then trap those error using SQL%BULK_EXCEPTIONS to find out which error has occured.
FORALL indx IN 1..v_emp_col.COUNT SAVE EXCEPTIONS
UPDATE emp set sal=sal+sal*inc_pct WHERE empno=v_emp_col(indx);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE= -24381 THEN
FOR indx IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
Dbms_output.put_line(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX||':'||SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
END LOOP;
END IF;
FORALL with SPARSE COLLECTIONS
If we try to use 1..collection.COUNT and there is an undefined index value in that range then oracle will raise ORA-22160:element at index does not exist error.
To avoid this error, we can use INDICES OF or VALUES OF clauses.
FOR idx IN INDICES OF v_emp_col
UPDATE emp set sal=sal+sal*inc_pct WHERE empno=v_emp_col(idx);
In this case we are instructing pl/sql engine to use only those index that are defined in v_emp_col collection. Oracle will simply skip the undefined indexes.
No comments:
Post a Comment