Oracle.PL/SQL(Anonymous Block, Stored Block)

[ Oracle.PL/SQL(Anonymous Block, Stored Block) ]

 

1. Anonymous Block

    . 실행시점에 해당 Block이 네트웤을 타고 DBMS에 전단됨

    . SQL 처리과정과 동일하게 Parsing과 Execute과정을 거침

 

2. Stored Block

    . DBMS 서버 내에 저장 됨

    . 처음 실행 시에는 Parsing된 상태로 저장되기 때문에 빠르다고 생각함.

 

3. 참고

    . Anonymous,Stored Block 의 성능은 동일함

 

3. 각 프로그램별 처리 시간

    . 40만건을 처리시 수행 시간

   

 

4. Stored Block을 사용한 예제

    <SPAN style="COLOR: #000"><SPAN style="COLOR: #000">REM ---------------------------------------------------------------------------- REM -- PL/SQL Stored 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 ---------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE CALC_BONUS(A_ARRAY_SIZE NUMBER DEFAULT 20) IS 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; ------------------------------------------------------------ -- TABLE 변수 선언 ------------------------------------------------------------ V_DEPTNO T_DEPTNO; V_EMPNO T_EMPNO; V_SAL T_SAL; V_BONUS T_BONUS; V_YYYYMM VARCHAR2(6); ------------------------------------------------------------ -- ArraySize 크기를 Parameter 로 지정 ------------------------------------------------------------ V_ARRAYSIZE NUMBER(3) := 20; -- Default ArraySize BEGIN V_ARRAYSIZE := A_ARRAY_SIZE; V_YYYYMM := TO_CHAR(SYSDATE,'YYYYMM'); 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 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; END; / REM ---------------------------------------------------------------------------- REM 이전 데이타 삭제 REM ---------------------------------------------------------------------------- TRUNCATE TABLE BONUS_LARGE; REM ---------------------------------------------------------------------------- REM DBMS_OUTPUT 의 결과를 화면에 출력하는 기능 ON REM ---------------------------------------------------------------------------- SET SERVEROUTPUT ON REM ---------------------------------------------------------------------------- REM 데이타 처리 REM ---------------------------------------------------------------------------- DECLARE V_START_TIME VARCHAR2(8); BEGIN DBMS_OUTPUT.PUT_LINE('Procedural - PL/SQL Stored Block Array Processing'); ------------------------------------------------------------ -- 시작 시간 기록 ------------------------------------------------------------ V_START_TIME := TO_CHAR(SYSDATE,'HH24:MI:SS'); CALC_BONUS(20); -- ArraySize = 20 ------------------------------------------------------------ -- 처리 시간 출력 ------------------------------------------------------------ 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; </SPAN></SPAN>

 

5. Subquery를 사용한 예제

 

    <SPAN style="COLOR: #000"><SPAN style="COLOR: #000">REM ---------------------------------------------------------------------------- REM -- SubQuery를 사용하여 40만건 데이타를 비절차적(집합적)으로 처리 REM -- REM -- * PL/SQL Block은 Interface 기능만 수행 REM -- REM -- 40만건 데이타를 1번에 REM -- (1) EMP_LARGE에서 추출하면서 연산을 하고 BONUS_LARGE TABLE에 INSERT REM ---------------------------------------------------------------------------- REM ---------------------------------------------------------------------------- REM 이전 데이타 삭제 REM ---------------------------------------------------------------------------- TRUNCATE TABLE BONUS_LARGE; REM ---------------------------------------------------------------------------- REM DBMS_OUTPUT 의 결과를 화면에 출력하는 기능 ON REM ---------------------------------------------------------------------------- SET SERVEROUTPUT ON REM ---------------------------------------------------------------------------- REM 데이타 처리 REM ---------------------------------------------------------------------------- DECLARE V_START_TIME VARCHAR2(8); V_YYYYMM VARCHAR2(6); BEGIN V_YYYYMM := TO_CHAR(SYSDATE,'YYYYMM'); DBMS_OUTPUT.PUT_LINE('NON-Procedural Processing'); ------------------------------------------------------------ -- 시작 시간 기록 ------------------------------------------------------------ V_START_TIME := TO_CHAR(SYSDATE,'HH24:MI:SS'); ------------------------------------------------------------ -- 비절차적(집합적) 데이타 처리 ------------------------------------------------------------ INSERT INTO BONUS_LARGE(YYYYMM,EMPNO,SAL,BONUS) SELECT V_YYYYMM,EMPNO,SAL,ROUND(DECODE(DEPTNO, 10,SAL*0.3, 20,SAL*0.1, 30,SAL*0.05, SAL*0.2)) FROM EMP_LARGE; ------------------------------------------------------------ -- 40만건 COMMIT; ------------------------------------------------------------ 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; </SPAN></SPAN>