CREATE OR REPLACE
PROCEDURE
EMP_BULKCOLLECT
as
type TY_EMP IS TABLE OF EMP%ROWTYPE;
V_EMP TY_EMP;
BEGIN
SELECT * BULK COLLECT INTO V_EMP FROM EMP;
FOR i IN 1 .. V_EMP.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(i|| ' ' ||V_EMP(i).ENAME);
END LOOP;
END;
The most
important thing to remember when you learn about and start to take advantage of
features such as BULK COLLECT is that there is no free lunch. There is almost
always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like
so many other performance-enhancing features, is "run faster but consume
more memory."
Specifically,
memory for collections is stored in the program global area (PGA), not the
system global area (SGA). SGA memory is shared by all sessions connected to
Oracle Database, but PGA memory is allocated for each session. Thus, if a
program requires 5MB of memory to populate a collection and there are 100
simultaneous connections, that program causes the consumption of 500MB of PGA
memory, in addition to the memory allocated to the SGA.
Consequently, you
should avoid this sort of "unlimited" use of BULK COLLECT. Instead,
move the SELECT statement into an explicit cursor declaration and then use a
simple loop to fetch many, but not all, rows from the table with each execution
of the loop body, as shown in below example.
CREATE OR REPLACE
PROCEDURE
EMP_BULKCOLLECT_LIMIT(
limit_in IN PLS_INTEGER)
as
type TY_EMP IS TABLE OF EMP%ROWTYPE;
V_EMP TY_EMP;
CURSOR C1
IS
SELECT * FROM EMP;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT INTO V_EMP LIMIT
LIMIT_IN;
FOR i IN 1 .. V_EMP.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(i|| ' ' ||V_EMP(i).ENAME);
END LOOP;
close C1;
END;
