074 - [Oracle PL/SQL] Bulk Binding

아래는 기본적인 pl/sql 실행 구조이다. 

여기서 인서트문이 자주 발생하면 서버에 부하가 많이 발생한다.

 

 

 

 

 

 

• 기본적인 문법은 아래와 같다.

 

 

 

• 예외 처리 관련

 

 

 

• 아래는 간단한 업데이트문을 for문으로 처리한 경우와 벌크로 처리한 경우의 샘플 코드입니다.

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY from employees
where employee_id in (100,101,102);
---------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24100
        101 Neena                Kochhar                        17000
        102 Lex                  De Haan                        17000



create or replace procedure update_sal_withoutbulk
    ( p_amount number )
is
    type emp_table_type is table of number index by binary_integer;
    emp_table emp_table_type;
begin
    -- 배열을 만들어서 저장.
    emp_table(1):=100;
    emp_table(2):=101;
    emp_table(3):=102;
    -- for문으로 3번 실행 & 업데이트.
    for i in emp_table.first..emp_table.last
    loop
        update employees
        set salary=salary+p_amount
        where employee_id =emp_table(i);
    end loop;
    commit;
end;
/

execute update_sal_withoutbulk(10);

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY from employees
where employee_id in (100,101,102);
---------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24110
        101 Neena                Kochhar                        17010
        102 Lex                  De Haan                        17010





-------------------------------------------------------------
--here with bulk using  forall
--no need for the loop
create or replace procedure update_sal_withbulk
    ( p_amount number )
is
    type emp_table_type is table of number index by binary_integer;
    emp_table emp_table_type;
begin
    emp_table(1):=100;
    emp_table(2):=101;
    emp_table(3):=102;

    -- for문 없음
    -- 업데이트문이 메모리에 저장되었다가 한번에 서버로 전송됨.
    forall i in emp_table.first..emp_table.last
    update employees
    set salary=salary+p_amount
    where employee_id =emp_table(i);
    commit;
end;

execute update_sal_withbulk(10);

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY from employees
where employee_id in (100,101,102);
---------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24120
        101 Neena                Kochhar                        17020
        102 Lex                  De Haan                        17020

 

 

 

• 예외처리 코드 포함된 샘플(벌크 아님, for문)

# 준비단계

drop table ename;

create table ename
as select distinct first_name from employees;

select first_name  
from ename;
 

# plsql 오류 및 예외처리 코드
declare
    type ename_t  is table of varchar2(100); -- 여기서 인덱스는 숨김처리, 시작은 1부터.
    -- 어레이를 생성하면서 초기화 할당하는 코드를 한번에 처리.
    -- ename_table ename_t;
    -- ename_table :=ename_t();
    -- 참고. https://unsungit.tistory.com/566
    ename_table ename_t:=ename_t();
    c number:=0;
    t varchar2(100); -- 디버깅용 변수.
begin
    -- first_name 을 모두 가져와서 인덱스에 모두 저장.
    for i in (select * from ename )
    loop
        c:=c+1;
        ename_table.extend;
        ename_table(c):=i.first_name;
    end loop;

    for i in ename_table.first.. ename_table.last
    loop
        ---begin
        t:=ename_table(i);--오류위치 저장.
        update ename
        set first_name=first_name||' to be added:)' --14 char, 20자 제한때문에 오류 발생.
        where first_name=ename_table(i);
        --exception
        -- 여기서 예외처리를 하지 않아서 오류 발생시 plsql 이 종료됨.
        ---end
    end loop;

    exception 
        when others 
        then dbms_output.put_line('error, first name is: ['||t||']');--오류위치 출력.

end;


# 예외처리 결과
--error, first name is: [Shelley]


# 실제 데이터 결과와 에러 메시지 비교
select * from ename
----------------------------

FIRST_NAME          
--------------------
David to be added:)
Shelli to be added:)
Amit to be added:)
Lex to be added:)
Adam to be added:)
Renske to be added:)
Jack to be added:)
Eleni to be added:)
Ki to be added:)
Shelley
Alexander
...

 

 

 

• 벌크 처리 샘플 코드 with 예외처리(save exceptions)

drop table ename;
/
create table ename
as select distinct first_name from employees;
/
select first_name  
from ename;
 

declare
    type ename_t  is table of varchar2(100);
    ename_table ename_t:=ename_t();
    c number:=0;
begin
    for i in (select * from ename )
    loop
        c:=c+1;
        ename_table.extend;
        ename_table(c):=i.first_name;
    end loop;

    forall i in ename_table.first.. ename_table.last save exceptions
    update ename
    set first_name=first_name||' to be added:)' --14 char
    where first_name=ename_table(i);

    exception 
    -- 이전처럼 디버깅이 불가하여 성공한 row 개수를 출력함, forall 은 하나의 sql만 처리 가능
        when others 
        then dbms_output.put_line('error, succeed count: , ['||SQL%ROWCOUNT||']');--오류위치 출력.
        -- then null;

end;

select * from ename
--------------------
--성공한 개수가 62개임, error, succeed count: , [62]

FIRST_NAME          
--------------------
David to be added:)
Shelli to be added:)
Amit to be added:)
Lex to be added:)
Adam to be added:)
Renske to be added:)
Jack to be added:)
Eleni to be added:)
Ki to be added:)
Shelley
Alexander
Vance to be added:)
Neena to be added:)
Randall
....

 

 

•  save exceptions 이 없으면 처음 발생하는 오류 위치에서 종료됨, 

# 실행결과는 아래와 같고 성공한 라인수는 , error, succeed count: , [9] 임.

FIRST_NAME          
--------------------
David to be added:)
Shelli to be added:)
Amit to be added:)
Lex to be added:)
Adam to be added:)
Renske to be added:)
Jack to be added:)
Eleni to be added:)
Ki to be added:)
Shelley
Alexander
...