Bulk Bind and Collect features were introduced to reduce the SQL processing overhead by efficient use of collections in PL/SQL code.
The PL/SQL engine executes procedural statements and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.
Bulk Collects (Reading data in bulk)
The bulk collect option instructs the SQL engine to bulk bind the output collections before returning them to the PL/SQL engine. This allows us to load data dynamically into collections at one shot for further processing.
Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements.
The PL/SQL engine executes procedural statements and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.
Bulk Collects (Reading data in bulk)
The bulk collect option instructs the SQL engine to bulk bind the output collections before returning them to the PL/SQL engine. This allows us to load data dynamically into collections at one shot for further processing.
Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements.
CREATE OR REPLACE PROCEDURE xxemp_prc (ENO NUMBER)
AS
TYPE NAME
IS
TABLE OF EMP.ENAME%TYPE
INDEX BY BINARY_INTEGER;
TYPE SALARY
IS
TABLE OF EMP.SAL%TYPE
INDEX BY BINARY_INTEGER;
N NAME;
P SALARY;
BEGIN
DELETE FROM my_emp
WHERE
EMPNO = ENO
RETURN
ename, sal BULK COLLECT INTO n,
p;
FOR i IN 1 .. n.COUNT
LOOP
INSERT INTO emp_DELETE (ENAME, SAL)
VALUES (n (i), p (i));
END LOOP;
END xxemp_prc;
|
