PL/SQL 커서 - BULK COLLECT

 

1. BULK COLLECT

BULK COLLECT 를 이용하면 데이터베이스에 암시적이나 명시적으로 한번만 질의를 하여도 다중행을 검색할 수 있다. BULK COLLECT는 PL/SQL과 SQL문의 실행 엔진 사이에 문맥전환 수를 줄여주고, 데이터를 검색하는 부담을 줄여준다.

질의의 INTO요소에 BULK COLLECT절을 사용한다. 커서에서 이 절을 사용하면, PL/SQL로 제어를 반환하기 전에, 질의에서 지정한 컬렉션으로 페치되는 다중 행들을 대량으로 바인드하게 한다.

구문

... BULK COLLECT INTO collection_name[, collection_name]...

규칙과 제약사항

- 9i 이전에는 정적 SQL에서만 BULK COLLECT를 사용할수 있었다. 9i에서는 동적, 정적SQL에서 BULK COLLECT를 사용할 수 있다.

- 다음과 같은 절에서 BULK COLLECT 키워드를 사용할 수 있다.(SELECT INTO, FETCH INTO, RETURNING INTO)

- 참조하는 컬렉션에는 스칼라 값만 저장할 수 있다.(문자열, 숫자, 날짜) 즉 레코드 구조로 이루어진 컬렉션은 페치할 수 없다

- SQL엔진은 BULK COLLECT절에서 사용하는 컬렉션을 자동으로 초기화하고 확장한다. 첫번째 인덱스부터 컬렉션을 채우기 시작하여 연속적으로 요소들을 삽입하고, 먼저 정의되었던 요소들의 값은 덮어씌운다.

- FORALL문에서는 SELECT BULK COLLECT문을 사용할 수 없다.

ex)

DECLARE names name_varray; mileages number_varray; BEGIN SELECT name, mileage FROM transportation BULK COLLECT INTO names, mileages WHERE TYPE = 'AUTOMOBILE' AND mileage < 20; -- 컬렉션에서 데이터를 처리한다. END; -- 명시적 커서 사용 예제 DECLARE CURSOR major_polluters IS SELECT name, mileage FROM transportation WHERE TYPE = 'AUTOMOBILE' AND mileage <20; names name_varray; mileages number_varray; BEGIN OPEN major_polluters; FETCH major_polluters BULK COLLECT INTO names, mileages; -- 컬렉션에서 데이터를 처리한다. END;

검색되는 행의 제한

데이터베이스에서 페치되는 행의 수를 제한하기 위해서 오라클은 BULK COLLECT에서 LIMIT절을 제공한다.

FETCH ... BULK COLLECT INTO ...[LIMIT rows];

rows는 문자, 변수, NUMBER로 평가되는 표현식이다.

ex)

DECLARE CURSOR major_polluters IS SELECT name FROM transportation WHERE TYPE = 'AUTOMOBILE' AND mileage < 20; names name_varray; BEGIN OPEN major_polluters; FETCH major_polluters BULK COLLECT INTO names LIMIT 5000; ... END;

다중 컬럼의 댜량 페치

한 컬럼 이상의 내용을 대량으로 페치할 수 있으며 오라클 9i 릴리스2에서 다중컬럼을 단일 레코드 컬렉션으로 페치할 수 있게 되었다.

ex)

DECLARE -- 컬렉션 유형을 선언 TYPE VehTab IS TABLE OF transportation%ROWTYPE; -- TYPE으로부터 특정 컬렉션을 예시 gas_guzzlers VehTab; BEGIN SELECT * BULK COLLECT INTO gas_guzzlers FROM transportation WHERE mileage < 20; ... 9i 릴리스2 이전에는 오류가 발생하였다. PLS-00597: expression 'GAS_GUZZLERS' in the INTO list is of wrong type 이전 버전에서는 다중 컬렉션을 선언하고 개별적으로 페치해야 한다. DECLARE guzzler_type name_varray; guzzler_name name_varray; guzzler_mileage number_varray; CURSOR low_mileage_cur IS SELECT vehicle_type, name, mileage FROM transportation WHERE mileage < 10; BEGIN OPEN low_mileage_cur; FETCH low_mileage_cur BULK COLLECT INTO guzzler_type, guzzler_name, guzzler_mileage; END;

다른 BULK COLLECT문장에서와 같이 레코드의 컬렉션에도 LIMIT절을 사용할 수 있다.

BULK 연산에서 RETURNING절의 사용

RETURNING절을 이용하기 위해 FORALL문에서도 BULK COLLECT를 사용할 수 있다.

DML문을 수행한 수의 정보를 추가적 질의 없이 얻을 수 있다.

ex)

- 급여를 가장 많이 받는 직원이 가장 적게 받는 직원의 급여보다 50배를 넘지 않게 한다.

- CEO는 급여를 내리지 않고 총 보상 패키지의 1/50보다 적게 받는 사람들의 급여를 증가시키고 CEO제외 상위 관리자의 급여는 내린다.

CREATE TABLE compensation ( name VARCHAR2(100), title VARCHAR2(100), salary NUMBER, bonus NUMBER, stock_options INTEGER, mercedes_benz_allowance NUMBER, yacht_allowance NUMBER ); INSERT INTO compensation VALUES ( 'John DayAndNight', 'JANITOR', 10000, 500, NULL, NULL, NULL); INSERT INTO compensation VALUES ( 'Holly Cubicle', 'PROGRAMMER', 50000, 2000, NULL, NULL, NULL); INSERT INTO compensation VALUES ( 'Sandra Watchthebucks', 'CFO', 20000000, 2000000, 2000000, 500000, 500000); INSERT INTO compensation VALUES ( 'Hiram Coldheart, XVII', 'CEO', 100000000, 20000000, 20000000, 2500000, 2500000); CREATE OR REPLACE FUNCTION salforexec (title_in IN VARCHAR2) RETURN NUMBER /* Why "for Exec"? Cause the query assumes NOT NULL values for all of that good stuff you in the SELECT below. */ IS CURSOR ceo_compensation IS SELECT salary + bonus + stock_options + mercedes_benz_allowance + yacht_allowance FROM compensation WHERE title = title_in; big_bucks NUMBER; BEGIN OPEN ceo_compensation; FETCH ceo_compensation INTO big_bucks; RETURN big_bucks; END; / CREATE TYPE name_tab IS TABLE OF VARCHAR2(200); / CREATE TYPE number_tab IS TABLE OF NUMBER; / DECLARE big_bucks NUMBER := salforexec ('CEO'); min_sal NUMBER := big_bucks / 50; names name_tab; old_salaries number_tab; new_salaries number_tab; CURSOR affected_employees (ceosal IN NUMBER) IS SELECT name, salary + bonus old_salary FROM compensation WHERE title != 'CEO' AND ((salary + bonus < ceosal / 50) -- underpaid OR (salary + bonus > ceosal / 10)) -- overpaid and NOT CEO ; BEGIN OPEN affected_employees (big_bucks); FETCH affected_employees BULK COLLECT INTO names, old_salaries; CLOSE affected_employees; FORALL indx IN names.FIRST .. names.LAST -- DECODE문을 이용하여 급여를 조정한다. UPDATE compensation SET salary = DECODE ( GREATEST (min_sal, salary), min_sal, min_sal, salary / 5) WHERE name = names (indx) RETURNING salary BULK COLLECT INTO new_salaries; FOR indx IN names.FIRST .. names.LAST LOOP DBMS_OUTPUT.PUT_LINE ( RPAD (names(indx), 20) || RPAD (' Old: ' || old_salaries(indx), 15) || ' New: ' || new_salaries(indx) ); END LOOP; END; /

* FORALL문을 실행하여 반환하는 RETURNING 컬럼 값이나 표현식은 먼저 반환된 값 이후의 컬렉션에 추가된다.

대량으로 처리하지 않고 FOR루프 내부에서 RETURNING을 사용하면 기존의 값들은 가장 최근에 실행되는 DML의 결과 값으로 바뀌게 된다.

SELECT...FOR UPDATE

레코드를 질의하기위해 SELECT문을 사용할 경우 조회되는 행에 락이 걸리지 않는다.

변경되었으나 아직 커밋되지 않은 레코드는 락이 걸린다. 다른 사용자들은 변경 전의 모스습으로 레코드를 읽을 수 있다.

레코드를 변경하기 전에 락이 걸리기를 원할 때 SELECT ... FOR UPDATE 문을 사용한다.

이 문을 수행하면 추출되는 모든 행에 배타적인 행 레벨의 락을 걸고 ROLLBACK이나 COMMIT을 수행 할 때까지 다른사람이 행을 변경할 수 없게 한다.

ex) 사용 예

CURSOR toys_cur IS SELECT name, manufacturer, preference_level, sell_at_yardsale_flag FROM my_sons_collection WHERE hours_used = 0 FOR UPDATE; CURSOR fall_jobs_cur IS SELECT task, expected_hours, tools_required, do_it_yourself_flag FROM winterize WHERE year = TO_CHAR(SYSDATE, 'YYYY') FOR UPDATE OF task;

- 첫번째 커서는 제한되지 않은 FOR UPDATE절 사용이며, 두번째 커서는 컬럼명으로 FOR UPDATE를 제한하였다.

다중 테이블에서도 SELECT문에 FOR UPDATE절을 사용 가능하며 OF절에 사용된 컬럼이 있는 테이블의 행에만 락이 걸린다.

다른 사용자가 테이블에 락을 걸었으면 오라클에게 기다리지 말라고 알려주는 키워드 NOWAIT를 추가할 수 있다.

COMMIT을 이용한 락 해제

- FOR UPDATE절로 커서가 OPEN되면 결과집합에 있는 모든 행은 락이 걸린다. 세션에서 변경사항을 저장하기 위해 COMMIT문이나 취소를 위한 ROLLBACK을 실행할 때가지 잠긴상태로 있는다.

- COMMIT이나 ROLLBACK 이후에는 커서에서 위치를 잃어버리므로 FOR UPDATE커서가 다른 FETCH를 수행할 수 없다.

- 루프문 내에서 FOR UPDATE커서를 이용 후 COMMIT과 ROLLBACK을 실행할 필요가 있을 경우 더이상 페치하지 않게하는 코드를 작성해야한다(EXIT이나 다른 로직)

WHERE CURRENT OF절

커서 내부에서 UPDATE와 DELETE문에 WHERE CURRENT OF 절을 사용할 수 있다. 이 절을 이용하면 가장 최근에 페치된 행들을 쉽게 변경할 수 있다.

WHERE CURRENT OF 절은 커서를 참조할 뿐 페치되는 행이 저장된 레코드가 아니다.

이 절을 사용할 경우 테이블에 있는 행을 확인하기 위해 사용했던 조건을 두곳에 코딩할 필요가 없다. UPDATE와 DELETE의 WHERE절과 커서의 WHERE절이 반복될 경우 WHERE CURRENT OF절을 사용하면 코드의 중복을 최소화 할 수 있다.

ex)

DECLARE CURSOR fall_jobs_cur IS SELECT task, expected_hours, tools_required, do_it_yourself_flag FROM winterize WHERE year = TO_CHAR(SYSDATE, 'YYYY'); job_rec fall_jobs_cur%ROWTYPE; BEGIN OPEN fall_jobs_cur; LOOP FETCH fall_jobs_cur INTO job_rec; IF fall_jobs_cur%NOTFOUND THEN EXIT; ELSIF job_rec.do_it_yourself_flag = 'YOUCANDOIT' THEN UPDATE winterize SET responsible = 'STEVEN' WHERE CURRENT OF fall_jobs_cur; COMMIT; EXIT; END IF; END LOOP; CLOSE fall_jobs_cur; END;

 

출처 : http://deviant86.tistory.com/467

 

-------------------------------------------------------------------------------

------------------------------------  LINE ------------------------------------

-------------------------------------------------------------------------------

 

SELECTS문에서 한번에 대량의 레코들 취득 하는 경우, BULK COLLECT구를 사용하면

한번에 여러개의 레코드를 취득할수 있으므로 퍼포먼스 향상

 

Patten 1

-------------------------------------------------------------------------------

DECLARE
TYPE empno_tbl_type IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
empno_tbl empno_tbl_type;
BEGIN
SELECT EMPNO BULK COLLECT INTO empno_tbl FROM EMP;
IF empno_tbl.COUNT > 0 THEN
FOR i IN empno_tbl.FIRST..empno_tbl.LAST LOOP
UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = empno_tbl( i );
END LOOP;
END IF;
END;
/

 

Patten 2

-------------------------------------------------------------------------------

DECLARE
TYPE emp_tbl_type IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tbl emp_tbl_type;
BEGIN
SELECT * BULK COLLECT INTO emp_tbl FROM EMP;
IF emp_tbl.COUNT > 0 THEN
FOR i IN emp_tbl.FIRST..emp_tbl.LAST LOOP
UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = emp_tbl( i ).EMPNO;
END LOOP;
END IF;
END;
/

 

Patten 3 커서 이용

-------------------------------------------------------------------------------

DECLARE
CURSOR emp_cur IS
SELECT * FROM EMP;
TYPE emp_tbl_type IS TABLE OF emp_cur%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tbl emp_tbl_type;
BEGIN
OPEN emp_cur;
FETCH emp_cur BULK COLLECT INTO emp_tbl;
CLOSE emp_cur;
IF emp_tbl.COUNT > 0 THEN
FOR i IN emp_tbl.FIRST..emp_tbl.LAST LOOP
UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = emp_tbl( i ).EMPNO;
END LOOP;
END IF;
END;
/

 

즉,커서를 이용할시 취득할 데이터 수가 많을듯하면 Limit를 사용하여 일정 레코드 단위로

Fetch하는 것이 성능면에서 좋다.

 

FETCH emp_cur BULK COLLECT INTO emp_tbl LIMIT 100;

 

출처 : http://avang.tistory.com/65