Oracle - Procedure 예제_BULK COLLECT INTO

[ Oracle - Procedure 예제_BULK COLLECT INTO ]

 

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;