Oracle PL/SQL BULK COLLECT: FORALL 예

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

BULK COLLECT는 SQL과 PL/SQL 엔진 간의 컨텍스트 전환을 줄이고 SQL 엔진이 한 번에 레코드를 가져올 수 있도록 합니다.

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

구문 :

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

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

FORALL 조항

FORALL을 사용하면 데이터에 대한 DML 작업을 대량으로 수행할 수 있습니다. FOR 루프문과 유사하지만 FOR 루프 모든 일이 레코드 수준에서 발생하는 반면 FORALL에는 LOOP 개념이 없습니다. 대신, 주어진 범위에 존재하는 전체 데이터가 동시에 처리됩니다.

구문 :

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

<DML operations>;

위 구문에서는 하위 범위와 상위 범위 사이에 존재하는 전체 데이터에 대해 지정된 DML 작업이 실행됩니다.

LIMIT 조항

대량 수집 개념은 전체 데이터를 대량으로 대상 컬렉션 변수에 로드합니다. 즉, 전체 데이터가 한 번에 컬렉션 변수에 채워집니다. 그러나 로드해야 하는 전체 레코드가 매우 큰 경우 이는 권장되지 않습니다. PL / SQL 전체 데이터를 로드하려고 시도하면 더 많은 세션 메모리가 소모됩니다. 따라서 이 대량 수집 작업의 크기를 제한하는 것이 항상 좋습니다.

그러나 이 크기 제한은 'SELECT' 문에 ROWNUM 조건을 도입하면 쉽게 달성할 수 있지만 커서의 경우에는 불가능합니다.

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

구문 :

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

위 구문에서 커서 페치 문은 LIMIT 절과 함께 BULK COLLECT 문을 사용합니다.

대량 수집 속성

유사하게 커서 BULK COLLECT 속성에는 n에서 영향을 받은 행 수를 반환하는 %BULK_ROWCOUNT(n)이 있습니다.th FORALL 문의 DML 문. 즉, 컬렉션 변수의 모든 단일 값에 대해 FORALL 문에서 영향을 받은 레코드 수를 제공합니다. 'n'이라는 용어는 행 개수가 필요한 컬렉션의 값 순서를 나타냅니다.

예제 1: 이 예에서는 BULK COLLECT를 사용하여 emp 테이블에서 모든 직원 이름을 투영하고 FORALL을 사용하여 모든 직원의 급여를 5000만큼 인상할 것입니다.

대량 수집 속성

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 salaiy=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

코드 설명 :

  • 코드 라인 2: 'SELECT emp_name FROM emp' 명령문에 대해 커서 guru99_det를 선언합니다.
  • 코드 라인 3: lv_emp_name_tbl을 VARCHAR2(50) 테이블 타입으로 선언
  • 코드 라인 4: lv_emp_name을 lv_emp_name_tbl 유형으로 선언합니다.
  • 코드 라인 6: 커서를 엽니다.
  • 코드 라인 7: LIMIT 크기를 5000 intl lv_emp_name 변수로 사용하여 BULK COLLECT를 사용하여 커서를 가져옵니다.
  • 코드 라인 8-11: lv_emp_name 컬렉션의 모든 레코드를 인쇄하도록 FOR 루프를 설정합니다.
  • 코드 라인 12: FORALL을 사용하여 모든 직원의 급여를 5000으로 업데이트합니다.
  • 코드 라인 14: 트랜잭션을 커밋합니다.