array processing, BULK COLLECT FETCH 및 FORALL을 이용하여 대량데이터의 기존 1row fetch ~ LOOP 방식의 비효율을 획기적으로 개선할 수 있다.

단, 이 방법은 8i이상버전부터 가능.

DECLARE

CURSOR all_depts IS
SELECT deptno, dname
FROM dept
ORDER BY dname;

TYPE dept_id IS TABLE OF dept.deptno%TYPE;
TYPE dept_name IS TABLE OF dept.dname%TYPE;

dept_ids dept_id;
dept_names dept_name;
inx1 PLS_INTEGER;

v_InsertStmt VARCHAR2(2000);

rows_processed number:=0;
net_processed number:=0;
last_value number:=0;

BEGIN

OPEN all_depts;
FETCH all_depts BULK COLLECT INTO dept_ids, dept_names;     -- 한꺼번에 로딩
CLOSE all_depts;

last_value := SQL%ROWCOUNT;      -- 받아온 데이터 ROW 수

/** Load한 데이터의 변형 **/
while (rows_processed < last_value )  loop
begin

    FOR inx1 IN 1..dept_ids.count LOOP
    dept_names(inx1) := UPPER(dept_names(inx1) || '+');
    DBMS_OUTPUT.PUT_LINE (dept_ids(inx1) || ' ' || dept_names(inx1));
    END LOOP;
   
    FORALL x IN dept_ids.first..dept_ids.last
    insert into dept(deptno,dname)
    values (dept_ids(i),dept_names(i));
   
    rows_processed:=rows_processed+sql%ROWCOUNT;
    net_processed:=net_processed+sql%rowcount;
                                           
exception when others then
    begin
   
       rows_processed:=rows_processed+sql%ROWCOUNT+1;
       net_processed:=net_processed+sql%rowcount;
       commit;
    end;
end;
end loop;

END;