Array Processing 활용
PL/SQL에서 대량의 데이터를 처리할 때 요긴하게 사용할 수 있음
-- 데이터를 Bulk로 읽음 Source 테이블 create table emp as select object_id empno, object_name ename, object_type job ,round(dbms_random.value(1000,5000), -2) sal ,owner deptno, created hirdate from all_objects where rownum <= 10000; -- 데이터를 Bulk로 넣을 Target 테이블 create table emp2 as select * from emp where 1=2; DECLARE l_fetch_size NUMBER DEFAULT 1000; -- 1,000건씩 Array 처리 CURSOR c IS SELECT empno, ename, job, sal, deptno, hirdate FROM emp; TYPE array_empno IS TABLE OF emp.empno%type; TYPE array_ename IS TABLE OF emp.ename%type; TYPE array_job IS TABLE OF emp.job%type; TYPE array_sal IS TABLE OF emp.sal%type; TYPE array_deptno IS TABLE OF emp.deptno%type; TYPE array_hiredate IS TABLE OF emp.hirdate%type; l_empno array_empno := array_empno (); l_ename array_ename := array_ename (); l_job array_job := array_job (); l_sal array_sal := array_sal (); l_deptno array_deptno := array_deptno (); l_hiredate array_hiredate := array_hiredate(); PROCEDURE insert_t( p_empno IN array_empno , p_ename IN array_ename , p_job IN array_job , p_sal IN array_sal , p_deptno IN array_deptno , p_hiredate IN array_hiredate ) IS BEGIN FORALL i IN p_empno.first..p_empno.last INSERT INTO emp2 VALUES ( p_empno (i) , p_ename (i) , p_job (i) , p_sal (i) , p_deptno (i) , p_hiredate(i) ); EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); RAISE; END insert_t; BEGIN OPEN c; LOOP FETCH c BULK COLLECT INTO l_empno, l_ename, l_job, l_sal, l_deptno, l_hiredate LIMIT l_fetch_size; insert_t( l_empno, l_ename, l_job, l_sal, l_deptno, l_hiredate ); EXIT WHEN c%NOTFOUND; END LOOP; CLOSE c; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END;
PROCEDURE process_all_rows IS TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; l_employees employees_aat; BEGIN SELECT * BULK COLLECT INTO l_employees FROM employees; FOR indx IN 1 .. l_employees.COUNT LOOP analyze_compensation (l_employees(indx)); END LOOP; END process_all_rows;참조 사이트 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26279996
최신 댓글