Oracle.PL/SQL(Bulk-binding, Bulk Collect)

카테고리 없음 2018. 2. 24. 17:29

[ Oracle.PL/SQL(Bulk-binding, Bulk Collect) ]

1. 참고

2. Bulk-binding, Bulk Collect

1. 특징

- 빈번한 DBMS Call을 줄이기 위한 기법

3. PL_SQL 예문

REM ----------------------------------------------------------------------------
REM -- PL/SQL ANONYMOUS BLOCK을 이용해서 40만건 데이타를 N(EX 20,50,100,200)건 단위로 Array Processing
REM -- 
REM -- Array Processing(= BULK-Collect, Bulk-Binding)
REM -- 
REM -- 40만건 데이타를 N건 단위로
REM -- (1) EMP_LARGE에서 데이타를 Array Fetch로 추출하여 
REM -- (2) Array 단위로 보너스 계산후 
REM -- (3) BONUS_LARGE TABLE에 Array INSERT
REM ----------------------------------------------------------------------------

REM    ----------------------------------------------------------------------------
REM       이전 데이타 삭제
REM    ----------------------------------------------------------------------------

TRUNCATE TABLE BONUS_LARGE;

REM    ----------------------------------------------------------------------------
REM       DBMS_OUTPUT 의 결과를 화면에 출력하는 기능 ON
REM    ----------------------------------------------------------------------------

SET SERVEROUTPUT ON

REM    ----------------------------------------------------------------------------
REM       데이타 처리 
REM    ----------------------------------------------------------------------------

DECLARE
	CURSOR TEST_CURSOR IS
		SELECT DEPTNO,EMPNO,SAL FROM EMP_LARGE;

------------------------------------------------------------
        -- TABLE TYPE 선언
	--  ( PL/SQL의 TABLE DATA TYPE은 C언어의 Array 와 유사,
	--    크기가 미리정의되지 않은 동적 Array 의미로 생각    )
	------------------------------------------------------------
	TYPE T_DEPTNO IS TABLE OF EMP_LARGE.DEPTNO%TYPE  INDEX BY BINARY_INTEGER;
	TYPE T_EMPNO  IS TABLE OF EMP_LARGE.EMPNO%TYPE   INDEX BY BINARY_INTEGER;
	TYPE T_SAL    IS TABLE OF EMP_LARGE.SAL%TYPE	 INDEX BY BINARY_INTEGER;
	TYPE T_BONUS  IS TABLE OF BONUS_LARGE.BONUS%TYPE INDEX BY BINARY_INTEGER;

------------------------------------------------------------
	-- ArraySize 크기 지정   (ex   20 , 50, 100, 1000)
	------------------------------------------------------------
	V_ARRAYSIZE		NUMBER(3) := 20;	-- ArraySize

------------------------------------------------------------
	-- TABLE 변수 선언
	------------------------------------------------------------
	V_DEPTNO		T_DEPTNO;		
	V_EMPNO			T_EMPNO;		
	V_SAL			T_SAL;		
	V_BONUS  		T_BONUS;		
	
	V_YYYYMM		VARCHAR2(6);
	V_START_TIME		VARCHAR2(8);

BEGIN
	V_YYYYMM  := TO_CHAR(SYSDATE,'YYYYMM');		
	DBMS_OUTPUT.PUT_LINE('Procedural - PL/SQL Anonymous Block Array Processing');
	------------------------------------------------------------
	--  시작 시간 기록
	------------------------------------------------------------
	V_START_TIME := TO_CHAR(SYSDATE,'HH24:MI:SS');

OPEN	TEST_CURSOR;
	LOOP
		------------------------------------------------------------
		--  Bulk-Binding (Array Fetch)
		------------------------------------------------------------
		FETCH	TEST_CURSOR BULK COLLECT INTO V_DEPTNO,V_EMPNO,V_SAL LIMIT V_ARRAYSIZE;
	
		------------------------------------------------------------
		--   PL/SQL 로직으로 보너스 계산 처리(Array 단위로)
		------------------------------------------------------------
		FOR I IN 1..V_DEPTNO.COUNT
		LOOP
			IF    V_DEPTNO(I)=10 THEN 
				V_BONUS(I) := V_SAL(I)*0.3;
			ELSIF V_DEPTNO(I)=20 THEN
				V_BONUS(I) := V_SAL(I)*0.1;
			ELSIF V_DEPTNO(I)=30 THEN
				V_BONUS(I) := V_SAL(I)*0.05;
			ELSE
				V_BONUS(I) := V_SAL(I)*0.2;
			END IF;
		END LOOP;

------------------------------------------------------------
		-- Bulk Insert (Array Processing)
		-- 
		-- ** 주) Loop를 반복 하면서 Array에 있는 값을 Insert 문장에 Binding만 수행
		-- Insert는 1회만 수행..
		-- FORALL : Insert 문장 안의 Values절에 값을 Binding하는 행위만 반복함 
		-- Binding이 완료되면 Insert는 1회만 실행 함
		------------------------------------------------------------
		FORALL I IN 1..V_DEPTNO.COUNT
			INSERT INTO BONUS_LARGE(YYYYMM,EMPNO,SAL,BONUS) 
			VALUES(V_YYYYMM,V_EMPNO(I),V_SAL(I),ROUND(V_BONUS(I)));

EXIT    WHEN TEST_CURSOR%NOTFOUND;

END LOOP;

CLOSE	TEST_CURSOR;

COMMIT;
	------------------------------------------------------------ 
	--  처리 시간 출력
	------------------------------------------------------------
	DBMS_OUTPUT.PUT_LINE('Processing Time  : '||V_START_TIME||' ~ '||TO_CHAR(SYSDATE,'HH24:MI:SS'));
END;
/

SELECT COUNT(*) AS BONUS_LARGE_CNT FROM BONUS_LARGE;