Oracle PL/SQL 프로시저[ForAll]

BluckCollet

커서를 오픈하여 SELECT 문을 실행할 때 OPEN-FETCH-CLOSE 구문을 데이터 1건씩 추출하며 , FOR LOOP를 이용하면 100건씩 추출한다 정해진 건수만큼 추출한 후에는 다음 데이터를 추출하기 위해  Fetch Call 이라는 내부  Call이 수행되는데, 대량 건수를 조회하면 다량의 Fetch Call 이 발생하여 서능이 저하된다.

 

대량의 데이터일 경우에는 BLUCK COLLECT 기능을 이용하여 한꺼번에 많은 데이터를 가져오도록 하는 것이 좋다.

DECLARE
    --bulk collect를 이용해 읽어들일 Nested Table 컬렉션 타입 생성
    TYPE emp_rec_arr_type IS TABLE OF EMP%ROWTYPE;
    emp_rec_arr emp_rec_arr_type;
    
    CURSOR
        emp_cur
        IS
        SELECT * FROM emp ORDER BY empno;
        
    BEGIN 
        --묵시적 BULKCollet
        --SELECT 문에서 바로 벌트로 모든 데이터를 컬렉션에 저장
        --추출 건수가 많을 경우 메모리 공간이 점유되므로 Memory 가 Full 되지 않도록 주의가 필요힘
        
        DBMS_OUTPUT.PUT_LINE('BULKCOLLECT #1');
        DBMS_OUTPUT.PUT_LINE('SELECT ~ BULK COLLECT INTO~');
        
        --BULK Collect 를 이용하여 한번에 컬렉션 변수에 저장
        SELECT * 
            BULK COLLECT INTO emp_rec_arr
            FROM emp;
            
            DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT' || SQL%ROWCOUNT);
            
            --컬렉션에 저장된 데이터 조작
            FOR i IN emp_rec_arr.FIRST..emp_rec_arr.LAST LOOP
                DBMS_OUTPUT.PUT_LINE(emp_rec_arr(i).empno||','||emp_rec_arr(i).sal);
            END LOOP;
            
            EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
                
                END;
                /

위의 구문을 이용해서 모든 대이터를 가져오도록  할수 있다.

SELECT 문과 BULK COLLECT INTO 를 사용하면 한번에 모든 데이터를 가져 올수 있다.

 

포문을 사용

DECLARE
    
    CURSOR emp_cur
        IS
        SELECT sal,empno FROM emp 
        ORDER BY sal;
        
    BEGIN 
        
            FOR  emp_rec IN emp_cur 
            LOOP
                
                DBMS_OUTPUT.PUT_LINE('EMP.DEPTNO : ' ||''||EMP_REC.EMPNO);
                DBMS_OUTPUT.PUT_LINE('EMP : ' || '' || EMP_REC.SAL);
                
            END LOOP;
            
            EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
                
                END;

 

둘다 결과 값을 보면 같지만 현재는 데이터 값이 많지가 않아서 경과시간은 평균적으로 비슷하다 

 

  DECLARE
    --bulk collect를 이용해 읽어들일 Nested Table 컬렉션 타입 생성
    TYPE emp_rec_arr_type IS TABLE OF EMP%ROWTYPE;
    emp_rec_arr emp_rec_arr_type;
    
    CURSOR
        emp_cur
        IS
        SELECT * FROM emp ORDER BY empno;
        
    BEGIN 
       
       --LIMIT 절을 이용한 명시적 BULK COLLECT 
       --OPEN -FETCH - CLOSE 와 함께 LIMIT 절을 이용하여 벌크러ㅗ 여러번에 나누어 컬렉션에 저장
       --여기서는 5건 단위로 Fetch를 하였음
       --건수가 많을 경우 500~1000 단위로 나누도록 했다
    
        DBMS_OUTPUT.PUT_LINE('BULKCOLLECT #1');
        DBMS_OUTPUT.PUT_LINE('SELECT ~ BULK COLLECT LIMIT~');
        
        --BULKCOLLECT 의 EXIT  WHEN 위치에 따른 차이
        --마지막 루프의 경우 남아 있는건수가 Fetch Array 크기보다 적을 경우 %NOTFOUND 가 TRUE 가 되어 
        --남아 있는 데이터 처리 없이 루프가 종료됨
        
        OPEN EMP_CUR;
            LOOP
                FETCH EMP_CUR BULK COLLECT INTO emp_rec_arr LIMIT 5;
                EXIT WHEN emp_cur%NOTFOUND;
                
                --컬렉션에 저장된 데이터 조회
            FOR i IN 1..emp_rec_arr.COUNT LOOP
                DBMS_OUTPUT.PUT_LINE(emp_rec_arr(i).empno||','||emp_rec_arr(i).sal);
            END LOOP;
            
                DBMS_OUTPUT.PUT_LINE('emp_cur ROWCOUNT ' || ''|| emp_cur%rowcount);
                END LOOP;
                CLOSE emp_cur;
            
            EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
                
                END;

 

현재는 5건씩 맞아 떨어지기 때문에 리밋절이 정상적으로 수행된결 확인할수 있다 하지만 만약 마지막건수가 4건이라면?

수행이 안되고 EXIT WHEN  구문에서 LOOP를 종료하게 된다.

 

  DECLARE
    --bulk collect를 이용해 읽어들일 Nested Table 컬렉션 타입 생성
    TYPE emp_rec_arr_type IS TABLE OF EMP%ROWTYPE;
    emp_rec_arr emp_rec_arr_type;
    
    CURSOR
        emp_cur
        IS
        SELECT * FROM emp ORDER BY empno;
        
    BEGIN 
       
       --LIMIT 절을 이용한 명시적 BULK COLLECT 
       --OPEN -FETCH - CLOSE 와 함께 LIMIT 절을 이용하여 벌크러ㅗ 여러번에 나누어 컬렉션에 저장
       --여기서는 5건 단위로 Fetch를 하였음
       --건수가 많을 경우 500~1000 단위로 나누도록 했다
    
        DBMS_OUTPUT.PUT_LINE('BULKCOLLECT #1');
        DBMS_OUTPUT.PUT_LINE('SELECT ~ BULK COLLECT LIMIT~');
        
        --BULKCOLLECT 의 EXIT  WHEN 위치에 따른 차이
        --마지막 루프의 경우 남아 있는건수가 Fetch Array 크기보다 적을 경우 %NOTFOUND 가 TRUE 가 되어 
        --남아 있는 데이터 처리 없이 루프가 종료됨
        
        OPEN EMP_CUR;
            LOOP
                FETCH EMP_CUR BULK COLLECT INTO emp_rec_arr LIMIT 5;
              
                
                --컬렉션에 저장된 데이터 조회
            FOR i IN 1..emp_rec_arr.COUNT LOOP
                DBMS_OUTPUT.PUT_LINE(emp_rec_arr(i).empno||','||emp_rec_arr(i).sal);
            END LOOP;
            
                DBMS_OUTPUT.PUT_LINE('emp_cur ROWCOUNT ' || ''|| emp_cur%rowcount);
                -----EXIT WHEN 절 위치를 바꿈
                  EXIT WHEN emp_cur%NOTFOUND;
             END LOOP;
     CLOSE emp_cur;
            
            EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
                
                END;

 

BULK COLLECT를 사용할때는 일반적으로 커서와 LOOP 를 이용하는것처럼 EXIT WHEN 절을 사용하는 방식 보다

위구문처럼 EXITWHEN 절의 위치를 바꿔주는게 좋다 이렇게 하면 5건이 안되더라도 4건이라고 해도 데이터가 조회가 된다.

 

DECLARE
    I_CURSOR INT := DBMS_SQL.OPEN_CURSOR;
    I_NUM_ROW DBMS_SQL.NUMBER_TABLE;
    I_EXEC INT;
    I_FETCHED_ROWS INT;
    
    BEGIN
    --SQL 파싱
    DBMS_SQL.PARSE(
        I_CURSOR,'SELECT EMPNO FROM EMP',
        DBMS_SQL.NATIVE);
        
        --Fetch Array 설정(5건씩)
        DBMS_SQL.DEFINE_ARRAY(I_CURSOR,1,I_NUM_ROW,5,1);
        
        --SQL 실행
        I_EXEC := DBMS_SQL.EXECUTE(I_CURSOR);
        --Fetch Array 단위만큼 데이터를 가져오기
        LOOP
            I_FETCHED_ROWS := DBMS_SQL.FETCH_ROWS(I_CURSOR);
            DBMS_SQL.COLUMN_VALUE(I_CURSOR,1,I_NUM_ROW);
            DBMS_OUTPUT.PUT_LINE('FETCHED' || I_FETCHED_ROWS || 'ROWS');
            EXIT WHEN I_FETCHED_ROWS < 5;
            
            END LOOP;
            
            --CUROSR닫
            DBMS_SQL.CLOSE_CURSOR(I_CURSOR);
            
            END;
            /

DBMS_SQL 패키지의 DEFINE_ARRAY 함수를 이용하여 추출 단위 건수를 조절하여 출력할 수도 있다