PL/SQL BULK COLLECT Using LIMIT Clause Practice:
set serveroutput on size 1000000
DECLARE TYPE member_table_type is table of member_list%rowtype
index by pls_integer;
member_table member_table_type;
CURSOR member_selected_cur is
select id,name,NULL as selected
from member_selected;
BEGIN
OPEN member_selected_cur;
LOOP
fetch member_selected_cur bulk collect into member_table LIMIT 10000;
forall i in member_table.FIRST .. member_table.LAST
update member_list set selected = 1 where id = member_table(i).id and name = member_table(i).name;
commit;
DBMS_OUTPUT.PUT_LINE(member_table.COUNT ||' rows');
EXIT WHEN member_selected_cur%NOTFOUND;
END LOOP;
CLOSE member_selected_cur;
END; /
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
10000 rows
3115 rows
[Reference]
1. http://www.dba-oracle.com/plsql/t_plsql_limit_clause.htm
2. http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html
