Oracle Bulk SQL and Bulk Binding (bulk collect)

Oracle Bulk SQL and Bulk Binding

대량의 데이터 처리시 loop문에서 1회씩 매번 수행되는 것을 단 1번만으로 처리

create or replace procedure sp_test as

    type ty_emp is table of emp%rowtype;
    ty_data ty_emp;

    type ty_name is varray(10) of varchar2(100);
    ty_ename ty_name := ty_name();

    cursor cur_emp is
        select empno,
               ename,
               job,
               mgr,
               hiredate,
               sal,
               comm,
               deptno
          from emp
         order by empno;

begin

    ty_ename.extend(10);

    open cur_emp;

    loop
        -- 10 row 단위, PGA 메모리 감안
        fetch cur_emp bulk collect
            into ty_data limit 10;
   
        -- 수식로직
        for i in 1 .. ty_data.count
        loop
            ty_ename(i) := ty_data(i).ename || '_1';
        end loop;
   
        -- 처리로직
        forall i in ty_data.first .. ty_data.last
       
            update emp
               set ename = ty_ename(i),
                   sal = sal + 1
             where empno = ty_data(i).empno;
   
        dbms_output.put_line('count : ' || ty_data.count);
   
        exit when ty_data.count <= 0;
   
    end loop;

end;

참고

http://argolee.tistory.com/40

https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#LNPLS879

https://m.blog.naver.com/inhim/100154127152