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