October 2005
Intermediate to advanced
454 pages
14h 44m
English
If you fetch records , one by one, via a PL/SQL loop, you will incur the overhead of context switching between SQL and PL/SQL once per record. This will dramatically increase the elapsed time if you are processing a large number of records. You can reduce the number of context switches by using a bulk fetch (BULK COLLECT INTO) to query records in sets or all at once.
First, here is an example of what I mean by fetching one record at a time:
CREATE OR REPLACE PROCEDURE one_at_a_time AS
CURSOR curs_get_ord IS
SELECT order_number,
order_date
FROM orders
ORDER BY order_number;
v_order_number NUMBER;
v_order_date DATE;
BEGIN
FOR v_order_rec IN curs_get_ord LOOP
do_something;
END LOOP;
END;If the ORDERS table contains 100 records, then 100 context switches would occur. Here is the bulk fetch version of the code.
CREATE OR REPLACE PROCEDURE all_at_once AS
CURSOR curs_get_ord IS
SELECT order_number,
order_date
FROM orders
ORDER BY order_number;
-- local collections to hold bulk fetched values
TYPE v_number_t IS TABLE OF NUMBER;
TYPE v_date_t IS TABLE OF DATE;
v_order_number v_number_t;
v_order_date v_date_t;
BEGIN
-- get all orders at once
OPEN curs_get_ord;
FETCH curs_get_ord BULK COLLECT INTO v_order_number, v_order_date;
CLOSE curs_get_ord;
-- if any orders were found then loop through the local
-- collections to process them
IF NVL(v_order_number.COUNT,0) > 0 THEN
FOR counter IN v_order_number.FIRST..v_order_number.LAST LOOP
do_something;
END LOOP;
END IF;
END;For large record ...