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;

출처: 지가닷넷