This option enables you to retrieve multiple rows of data in a single request. The retrieved data is then populated into a collection variables. This query runs significantly faster than if it were done without the bulk collect. The operations on collection variables are in memory operations and thus much much faster. There are three types of PL/SQL collection variables - Nested Tables, VArrays and Associative Arrays.
Here I will show you an example of bulk collect using nested table to speed up fetching. In the sample code, I will use sys_refcursor instead of declaring any explicit cursor. It is a week type cursor available from Oracle 9i. There is also another predefined cursor from Oracle 9i which is refcursor_pkg.strong ref_cursor. Here is the PL/SQL code:
declareYou can use limit option to limit the number of rows to be fetched. Like -
TYPE type_base_rec IS RECORD (cx NUMBER, event_date DATE,
balance NUMBER);
--instead of creating any type,
--I use record and it will be available only within PL/SQL block
TYPE type_base_tbl IS TABLE OF type_base_rec;
ref_cur_base SYS_REFCURSOR;
begin
OPEN ref_cur_base FOR 'select c1,c2,c3 from Tab';
FETCH ref_cur_base BULK COLLECT INTO base_data;
CLOSE ref_cur_base;
end;
/
FETCH ref_cur_base BULK COLLECT INTO base_data limit 5000;
Now, you can use the collection methods on the collection variables. Most of the time, you need count, First, last, trim, extend, delete or indexing like base_data(i). A common example where bulk collect can speed up your operation is writing files from database.
