Oracle PL/SQL BULK COLLECT: FORALL 예

대량 수집이란 무엇입니까?

BULK COLLECT는 컨텍스트 전환을 줄여줍니다. SQL PL/SQL 엔진을 사용하며, SQL 엔진이 레코드를 한 번에 가져올 수 있도록 합니다.

Oracle PL / SQL BULK COLLECT는 레코드를 하나씩 가져오는 대신 일괄적으로 가져오는 기능을 제공합니다. 이 BULK COLLECT는 SELECT 문에서 레코드를 일괄적으로 채우거나 특정 레코드를 가져오는 데 사용할 수 있습니다. 커서 BULK COLLECT는 레코드를 일괄적으로 가져오기 때문에 INTO 절에는 항상 컬렉션 유형 변수가 포함되어야 합니다. BULK COLLECT를 사용하는 주요 이점은 데이터베이스와 PL/SQL 엔진 간의 상호 작용을 줄여 성능을 향상시키는 것입니다.

구문 :

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

위 구문에서 BULK COLLECT는 SELECT 및 FETCH 문에서 데이터를 수집하는 데 사용됩니다.

FORALL 조항

FORALL 문은 다음과 같은 작업을 수행합니다. DML 작업 FORALL은 대량의 데이터를 처리하는 데 사용됩니다. FOR 루프 문과 유사하지만, FOR 루프에서는 레코드 단위로 작업이 수행되는 반면, FORALL에는 반복 개념이 없습니다. 대신, 지정된 범위 내의 모든 데이터가 동시에 처리됩니다.

구문 :

FORALL <loop_variable> in <lower range> .. <higher range>

<DML operations>;

위 구문에서, 주어진 DML 작업은 하한 범위와 상한 범위 사이에 있는 전체 데이터에 대해 실행됩니다.

LIMIT 조항

대량 수집(bulk collect) 개념은 전체 데이터를 대상 컬렉션 변수에 일괄적으로 로드하는 방식입니다. 즉, 모든 데이터가 한 번에 컬렉션 변수에 채워집니다. 하지만 로드해야 할 레코드 수가 매우 많은 경우에는 이 방식이 권장되지 않습니다. PL/SQL이 전체 데이터를 로드하려고 하면 세션 메모리를 과도하게 사용하기 때문입니다. 따라서 대량 수집 작업의 크기를 제한하는 것이 좋습니다.

이러한 크기 제한은 SELECT 문에 ROWNUM 조건을 도입함으로써 쉽게 달성할 수 있지만, 커서의 경우에는 이것이 불가능합니다.

이를 극복하기 위해, Oracle 대량 처리에 포함해야 하는 레코드 수를 정의하는 LIMIT 절을 제공했습니다.

구문 :

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

위 구문에서 커서 가져오기 문은 LIMIT 절과 함께 BULK COLLECT 문을 사용합니다.

대량 수집 속성

커서 속성과 유사하게, BULK COLLECT에는 FORALL 문의 n번째 DML 문에서 영향을 받은 행 수를 반환하는 %BULK_ROWCOUNT(n) 속성이 있습니다. 즉, 컬렉션 변수의 각 값에 대해 FORALL 문에서 영향을 받은 레코드 수를 제공합니다. 여기서 'n'은 행 수를 계산해야 하는 컬렉션 내 값의 순서를 나타냅니다.

예 1 : 이 예제에서는 BULK COLLECT를 사용하여 emp 테이블에서 모든 직원 이름을 가져오고, FORALL을 사용하여 모든 직원의 급여를 5000만큼 증가시킬 것입니다.

아래 스크린샷은 BULK COLLECT 및 FORALL 예제와 그 출력 결과를 보여줍니다. Oracle.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line('Employee Fetched:'||c_emp_name);
END LOOP;
FORALL i IN lv_emp_name.FIRST .. lv_emp_name.LAST
UPDATE emp SET salary=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;
Dbms_output.put_line('Salary Updated');
CLOSE guru99_det;
END;
/

산출

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Salary Updated

Code 설명 :

• Code 2행: 'SELECT emp_name FROM emp' 문에 대한 커서 guru99_det를 선언합니다.
• Code 3행: lv_emp_name_tbl을 VARCHAR2(50) 테이블 유형으로 선언합니다.
• Code 4행: lv_emp_name을 lv_emp_name_tbl 형식으로 선언합니다.
• Code 6행: 커서를 엽니다.
• Code 7행: BLOK COLLECT를 사용하여 커서를 가져오고, LIMIT 크기를 5000으로 설정하여 lv_emp_name 변수에 저장합니다.
• Code 8-11행: lv_emp_name 컬렉션의 모든 레코드를 출력하는 FOR 루프를 설정합니다.
• Code 12행: FORALL 함수를 사용하여 모든 직원의 급여를 5000만큼 업데이트합니다.
• Code 14행: 저지르다 거래.

자주 묻는 질문