BULK COLLECT using CURSOR FOR LOOP
- Rumesh Aponso (RMAX)
- Nov 21, 2024
- 1 min read
DECLARE
rec_limit_ NUMBER := 100;
fetch_limit_ CONSTANT PLS_INTEGER DEFAULT 9;
CURSOR get_co_info IS
SELECT t.order_no,
t.contract,
t.customer_no,
t.currency_code
FROM customer_order_tab t;
TYPE customer_order_list_tab IS TABLE OF get_co_info%ROWTYPE INDEX BY BINARY_INTEGER;
customer_order_list customer_order_list_tab;
PROCEDURE Print_Info (
rec_ IN get_co_info%ROWTYPE )
IS
BEGIN
Dbms_Output.Put_Line(rec_.order_no || ' - ' ||
rec_.contract || ' - ' ||
rec_.customer_no || ' - ' ||
rec_.currency_code);
END Print_Info;
BEGIN
OPEN get_co_info;
LOOP
FETCH get_co_info BULK COLLECT INTO customer_order_list LIMIT fetch_limit_;
EXIT WHEN rec_limit_ <= 0;
Dbms_Output.Put_Line('Retrieved: ' || customer_order_list.COUNT);
FOR i_ IN 1 .. customer_order_list.COUNT LOOP
Print_Info(customer_order_list(i_));
END LOOP;
rec_limit_ := rec_limit_ - fetch_limit_;
END LOOP;
CLOSE get_co_info;
END;
Comments