컬렉션 - 2
컬렉션 연산
컬렉션 변수 간의 할당
컬렉션은 할당 연산자 :=를 사용하여 동일 타입의 변수 간에 값의 복사가 가능하다,
DECLARE
TYPE string_array IS TABLE OF VATCHAR2(100);
v_arr1 string_array;
v_arr2 string_array;
PROCEDURE p_print_collection_count(a_title VARCHAR2, a_coll string_array)
IS
BEGIN
IF a_coll IS NULL THEN
DBMS_OUTPUT_PUT_LINE(a_title || ': '|| '컬렉션이 NULL입니다.');
ELSE
DBMS_OUTPUT_PUT_LINE(a_title || ': '|| '컬렉션항목이' || a_coll.COUNT|| '건입니다.');
END IF;
END;
BEGIN
v_arr1 := string_array('사과', '배', '수박', '망고');
v_arr2 := v_arr1; -- 컬렉션 변수 간의 할당 연산을 통한 복사
p_print_collection_count('1, v_arr1', v_arr1):
p_print_collection_count('2, v_arr2', v_arr2):
--NULL 할당
v_arr2 := NULL;
p_print_collection_count('3, v_arr3', v_arr3):
END;1. v_arr1: 컬렉션항목이 4건입니다.
2. v_arr2: 컬렉션항목이 4건입니다.
3. v_arr3: 컬렉션이 NULL입니다.주의할 점은 실제적인 구조가 동일하더라도 타입명이 다르면 할당이 불가능하다는 것이다.
컬렉션 비교
Associative Array끼리는 비교가 불가능하다. VARRAY와 Nested Table만 제한된 범위 내에서 비교가 가능하다.
- NULL과의 비교: VARRAY와 Nested Table은 IS NULL 연산을 지원한다.
DECLARE
TYPE string_array IS TABLE OF VATCHAR2(100);
v_arr1 string_array := string_array('사과', '수박', '망고', '배');
v_arr2 string_array;
BEGIN
IF v_arr1 IS NULL THEN
DBMS_OUTPUT_PUT_LINE('v_arr1 IS NULL');
ELSE
DBMS_OUTPUT_PUT_LINE('v_arr1 IS NOT NULL');
END IF;
IF v_arr2 IS NOT NULL THEN
DBMS_OUTPUT_PUT_LINE('v_arr2 IS NOT NULL');
ELSE
DBMS_OUTPUT_PUT_LINE('v_arr2 IS NULL');
END IF;
END;
- 동치 혹은 부동 비교: VARRAY와 Nested Table은 동치 비교(= 비교) 또는 부등 비교(<>, !=, ~=, ^=)를 지원한다.
두 컬렉션이 같은지 여부는 컬렉션의 항목 개수와 값이 동일한지의 여부다.
DECLARE
TYPE string_array IS TABLE OF VARCHAR2(100);
v_arr1 string_array := string_array('사과', '수박', '망고', '배');
v_arr2 string_array := string_array('사과', '수박', '망고', '배');
v_arr3 string_array := string_array('사과', '수박');
BEGIN
IF v_arr1 = v_arr2 THEN
DBMS_OUTPUT_PUT_LINE('v_arr1 = v_arr2');
ELSE
DBMS_OUTPUT_PUT_LINE('v_arr1 <> v_arr2');
END IF;
IF v_arr1 = v_arr3 THEN
DBMS_OUTPUT_PUT_LINE('v_arr1 = v_arr3');
ELSE
DBMS_OUTPUT_PUT_LINE('v_arr1 <> v_arr3');
END IF;
IF v_arr1 <> v_arr3 THEN
DBMS_OUTPUT_PUT_LINE('v_arr1 <> v_arr3');
ELSE
DBMS_OUTPUT_PUT_LINE('v_arr1 = v_arr3');
END IF;
END;
컬렉션 메소드
컬렉션에 대한 정보를 얻거나 컬렉션에 대한 변경을 위해 PL/SQL 내장 컬렉션 메소드가 제공된다.
컬렉션변수.컬렉션메소드(파라미터)파라미터는 메소드에 따라 개수가 다르며 동일 메소드라도 용도에 따라 다른 개수의 파라미터를 가지도록 중복 정의되었다.
- 컬렉션 메소드
- DELETE : 프로시저
-> 컬렉션에서 항목을 삭제한다. - DELETE: 컬렉션의 모든 항목을 삭제
- DELETE(n): n번째 항목을 삭제
- DELETE(m, n) : m번째 항목부터 시작해서 n개의 항목을 삭제
-> DELETE는 세가지 컬렉션 유형 모두에서 사용할 수 있지만 DELETE(n), DELETE(m, n)은 VARRAY와 Nested Table에만 사용 사능하다.
- TRIM: 프로시저
-> VARRAY이나 Nested Table의 끝 부분의 항목을 삭제한다. - TRIM: 마지막 한 개 항목을 삭제
- TRIM(n): 마지막의 n개 항목을 삭제
- EXTEND: 프로시저
-> VARRAY나 Nested Table의 끝에 항목을 추가한다. - EXTEND: 컬렉션에 한개의 NULL항목 추가
- EXTEND(n): 컬렉션에 n개의 NULL항목 추가
- EXTEND(n, i): 컬렉션에 i번째 항목의 복사본을 n개 추가
- EXISTS: 함수
-> VARRAY나 Nested Table의 지정된 항목이 존재하면 TRUE를 반환한다. 존재하지 않는 항목에 대해서는 FALSE을 반환한다. EXISTS(n): n번째 항목이 존재하면 TRUE, 아니면 FALSE을 반환한다.
- FIRST: 함수
-> 컬렉션의 첫번째 인덱스를 반환한다.
- LAST: 함수
-> 컬렉션의 마지막 인덱스를 반환한다.
-COUNT : 함수
-> 컬렉션이 가진 항목의 개수를 반환한다.
- LIMIT: 함수
-> 컬렉션이 가질 수 있는 항목의 최대 개수를 반환한다. VARRAY만이 LIMIT값을 가지며, 항목의 최대 개수 제한이 없는 Associative Array와 Nested Table의 LIMIT는 NULL이다.
- PRIOR: 함수
-> 지정된 인덱스 바로 앞의 인덱스를 반환한다. 컬렉션의 바로 앞 인덱스가 비어 있지 않다면 지정된 인덱스보다 1이 작은 값을 반환한다. 컬렉션의 첫 번째 인덱스(FIRST)에 대해서는 NULL을 반환한다. NEXT와 함께 컬렉션 항목의 앞/뒤로 이동하기 위해 사용된다,
- PRIOR(n): n번쨰 항목의 바로 이전 항목의 인덱스를 반환
-NEXT: 함수
-> 지정된 인덱스 바로 뒤의 인덱스를 반환한다. 컬렉션의 바로 다음 인덱스가 비어있지 않다면 지정된 인덱스보다 1이 큰 값을 반환한다. 컬렉션의 마지막 인덱스(LAST)에 대해서는 NULL을 반환한다.
- NEST(n): n번쨰 항목의 바로 다음 항목의 인덱스를 반환
다차원 컬렉션
오라클 PL/SQL은 기본적으로 다차원 컬렉션을 지원하지 않는다.
하지만 유사하게 사용할 수는 있다.
DECLARE
TYPE arr_1d_type IS TABLE OF VARCHAR2(100); -- Nested Table
v_arr1_1 arr_1d_type := arr_id_type('사과', '배'); -- 초기화
v_arr1_2 arr_1d_type := arr_id_type('오렌지', '자몽', '망고'); -- 초기화
v_arr1_3 arr_1d_type := arr_id_type('포도', '앵두'); -- 초기화
TYPE arr_2d_type IS TABLE OF arr_1d_type; -- 2차원 Nested Table 타입 선언
v_arr2 arr_2d_type := arr_2d_type(v_arr1_1, v_arr1_2); -- 2차원 컬렉션 초기화(1차원 컬렉션 사용)
BEGIN
v_arr2.EXTEND;
v_arr2(3) := v_arr1_3;
DBMS_OUTPUT_PUT_LINE('v_arr2(2)(3) = ' ||v_arr2(2)(3)); -- 다차원 배열의 항목 참조
END;
배열 처리
PL/SQL의 배열처리는 BULK COLLECT 또는 FORALL 키워드를 사용하여 하나의 SQL문으로 여러 건의 데이터를 한 번에 처리할 수 있도록 하는 기능이다.
| 항목 | BULK COLLECT | FORALL |
| 동작 방식 | SELECT문이 서버에서 실행되어 여러 건의 결과가 추출되면 결과 로우를 한 번에 클라이언트로 회신하여 배열에 저장 | DML 처리할 여러 건의 데이터를 배열에 담아 서버로 한 번에 전송하여 서버에서 한 번에 실행 |
| 데이터 이동 방향 | 테이블 -> 컬렉션 | 컬렉션 -> 테이블 |
| 사용 구문 | SELECT, FETCH | INSERT, UPDATE, DELETE, MERGE |
배열 처리를 사용하여 얻을 수 있는 대표적인 장점
- 단순성: 배열을 사용하면 SQL처리가 간단해진다. LOOP를 사용하여 처리하지 않고도 여러 개의 레코드를 하나의 SQL문으로 처리할 수 있다.
- 성능 개선: 배열 처리는 SQL의 실행횟수와 서버로의 왕복횟수를 동시에 줄임으로써 응답시간, CPU사용률, 때로는 네트워크 사용량 측면에서 PL/SQL의 실행 성능을 개선시킨다. 예를 들어 LOOP를 사용하여 10건의 결과를 한 로우씩 읽어 들이는 경우에는 최소 10번의 데이터베이스 호출이 필요하지만 BULK COLLECT INTO를 사용하여 배열로 읽어 들인다면 한 번의 데이터베이스 호출만 실행하면 된다.
사실 오라클 PL/SQL에서 말하는 배열이란 앞에서 설명한 컬렉션이다.
배열처리에는 세가지 유형의 컬렉션이 모두 사용 가능하다.
단 Associative Array의 경우 인덱스가 정수인 유형에만 배열 처리에 사용할 수 있고, 문자열을 인덱스로 사용하는 유형에는 사용할 수 없다.
SELECT문에서의 배열 처리
SELECT문에서의 배열처리는 SELECT문의 결과 로우를 서버에서 클라이언트로 가져올 때 한번에 여러 건씩 가져오는 것을 말한다. PL/SQL에서 SELECT문의 배열 처리에는 BULK COLLECT INTO 키워드가 사용된다.
SELECT문의 배열처리 유형은
1. LIMIT 키워드 미사용 : 전체 결과를 한 번의 오퍼레이션으로 PL/SQL의 컬렉션 변수에 저장한다.
LIMIT 키워드를 사용하지 않는 경우에는 커서와 LOOP문을 사용하여 반복적으로 결과를
FETCH하는 것을 지원하지 않는다.
이 유형은 SELECT문의 결과의 최대 건수를 미리 알고 있고 건수가 많지 않은 경우에 사용하기 적합
- LIMIT 키워드를 사용하지 않는 SELECT문에서의 배열 처리 유형
| 유형 | 설명 |
| 건수 제한 없음 | SELECT될 건수를 제한하지 않는다. 테이블의 전체 로우를 가져오거나 WHERE절의 조건을 만족하는 로우 전체를 가져온다. |
| ROWNUM 사용 | WHERE절에 ROWNUM을 사용하여 SELECT될 최대 건수를 제한한다. |
| SAMPLE 사용 | SAMPLE 키워드를 사용하여 샘플링할 비율을 제한하거나 샘플링할 블록의 수를 제한한다. |
| FETCH FIRST 사용 | FETCH FIRST절을 사용하여 가져올 최대 건수를 지정한다. 오라클 버전 12c부터 사용 가능하다. |
2. FETCH문에 LIMIT 키워드 사용
: 커서 FETCH문에서 한 번에 읽어 들이는 건수를 제한하여 배열로 읽어들이며 LOOP를 사용해 이를 반복처리한다.
결과의 최대 건수를 모르거나 결과 건수가 많아서 메모리 부담이 있을 경우 사용하기에 적합하다.
-- LIMIT 키워드 없이 전체 건 조회 또는 ROWNUM을 사용하여 SELECT 건수 제한
DECLARE
TYPE emp_rec IS TABLE OF emp%ROWTYPE;
v_emp_arr emp_rec;
BEGIN
-- 한 번의 실행으로 emp 테이블의 모든 로우를 배열에 읽어 들인다.
SELECT *
BULK COLLECT INTO v_emp_arr
FROM emp;
DBMS_OUTPUT_PUT_LINE('건수1: '||v_emp_arr.COUNT);
-- LIMIT 키워드 없이도 ROWNUM을 사용하여 건수 제한이 가능
-- 한 번의 실행으로 emp 테이블의 로우 10건을 배열에 읽어 들인다.
-- 최대 10까지가 v_emp에 담길 수 있다.
SELECT *
BULK COLLECT INTO v_emp_arr
FROM emp
WHERE ROWNUM <= 10;
DBMS_OUTPUT_PUT_LINE('건수2 : '||v_emp_arr.COUNT);
END;-- LIMIT 키워드 없이 SAMPLE 또는 FETCH FIRST을 사용하여 SELECT 건수 제한
DECLARE
TYPE emp_tab_type IS TABLE OF emp%ROWTYPE;
v_emp emp_tab_type;
BEGIN
-- SAMPLE절을 사용하여 건수 제한
-- SAMPLE 뒤의 숫자 10은 건수가 아니라 퍼센트(%)를 지정하는 숫자
--정확히 10%를 조회하는 것이 아니라 10%에 해당하는 건수를 추정하는 방법을 사용하므로
-- 실제로 조회되는 결과 건수는 매 실행 시마다 달라질 수 있다.
SELECT *
BULK COLLECT INTO v_emp
FROM emp SAMPLE(10); -- 10%를 샘플링하여 조회
DBMS_OUTPUT_PUT_LINE('SAMPLE 건수: '||v_emp.COUNT);
-- FETCH FIRST절을 사용하여 건수 제한
-- 12c에서 새로 생긴 기능
SELECT *
BULK COLLECT INTO v_emp
FROM emp
FETCH FIRST 10 ROWS ONLY;
DBMS_OUTPUT_PUT_LINE('FETCH FIRST 10 건수 : '||v_emp.COUNT);
END;FETCH FIRST는 건수를 지정하거나 비율을 지정하는 두 가지 형태로 사용할 수 있다.
ONLY는 지정된 건수 또는 비율의 건수를 가져오고, WITH TIES는 ORDER BY와 같이 사용되어 마지막 FETCH되는 건과 동일한 정렬키를 가지는 로우들을 모두 추가로 가져온다.
-- LIMIT절을 사용하여 SELECT 건수 제한
DECLARE
TYPE emp_rec IS TABLE OF emp%ROWTYPE;
v_emp_arr emp_rec;
c_size_limit CONSTANT PLS_INTEGER := 10; -- 한 번에 읽어 들일 최대 건수 제한
v_fetched PLS_INTEGER;
CURSOR c IS -- 이 유형의 처리는 CURSOR를 사용
SELECT *
FROM emp;
BEGIN
v_fetched := 0;
OPEN c; -- 커서 열기
LOOP
FETCH c BULK COLLECT INTO v_emp_arr
LIMIT c_size_limit; -- LIMIT를 사용하여 한 번에 읽어들일 최대 건수를 제한한다.
DBMS_OUTPUT_PUT_LINE(v_emp_arr.COUNT||'건');
IF 0 < v_emp_arr.COUNT THEN
FOR i IN v_emp_arr.FIRST .. v_emp_arr.LAST
LOOP
DBMS_OUTPUT_PUT_LINE(CHR(9)||'순서 = '|| TO_cHAR(v_fetched+i, '99') ||
' 사번 = '|| v_emp_arr(i).empno||', 이름 = '|| v_emp_arr(i).ename);
END LOOP;
v_fetched := c%ROWCOUNT; -- 처리된 건수
END IF;
EXIT WHEN c%NOTFOUND; -- 더 이상의 데이터가 없으면 종료. 모든 처리가 끝난 후 호출해야 함
END LOOP;
CLOSE c;
END;DML문에서의 배열 처리
INSERT, DELETE, UPDATE, MERGE문과 같은 DML문에서의 배열처리에는 FORALL 키워드가 사용된다,
FORALL 인덱스명 IN 범위절
DML문;범위절의 표현방식은 다음과 같은 세가지가 있다.
- '..'을 사용: 데이터 배열에서 처리할 범위를 지정한다. 배열에는 빈 항목이 있으면 안된다. 하한값 .. 상한값 형태로 사용되며 범위가 연속적인 경우에 사용하기에 적합하다.
- 'INDICES OF'를 사용: 데이터 배열에서 유효한(비어있지 않은) 항목만을 처리하도록 지정한다. 배열에 빈 항목이 있다면 처리 대상에서 제외된다, 중간에 빈 항목이 있는 경우에 유용하게 사용할 수 있다.
- 'VALUES OF' 사용: 데이터 배열에서 처리할 항목의 인덱스를 또 다른 배열로 제공한다. 항목 중의 일부만을 간접적으로 지정할 때 유용하게 사용할 수 있다.
CREATE TABLE t(
id INTEGER PRIMARY KEY,
name VARCHAR2(100)
);
DECLARE
TYPE id_arr_type IS TABLE OF PLS_INTEGER;
TYPE name_arr_type IS TABLE OF t.name%TYPE;
v_id_arr id_arr_type := id_arr_type(1,2,3,4,5);
v_name_arr name_arr_type := name_arr_type('이순신', '강감찬', '을지문덕', '김유신', '김윤후');
v_name_arr2 name_arr_type := name_arr_type('강희안', '김홍도', '신윤복', '정선', '장승업');
BEGIN
DELETE FROM t;
-- INSERT문에서의 배열처리. ".."을 사용하여 범위 지정
FORALL i IN v_id_arr.FIRST .. v_id_arr.LAST
INSERT INTO t(id, name) VALUES(v_id_arr(i), v_name_arr(i));
DBMS_OUTPUT_PUT_LINE('INSERT COUNT = '||SQL%ROWCOUNT);
-- UPDATE문에서의 배열처리. INDICES OF를 사용하여 범위지정
FORALL i IN INDICES OF v_id_arr
UPDATE t
SET name = v_name_arr(i)
WHERE ID = v_id_arr(i);
DBMS_OUTPUT_PUT_LINE('UPDATE COUNT = '||SQL%ROWCOUNT);
-- MERGE문에서의 배열처리. ".."를 사용하여 범위지정
FORALL i IN v_id_arr.FIRST .. v_id_arr.LAST
MERGE INTO t
USING(
SELECT id
FROM t
WHERE id = v_id_arr(i)) u
ON (t.id = u.id)
WHEN MATCHED THEN
INSERT (id, name)
VALUES (v_id_arr(i), v_name_arr(i));
DBMS_OUTPUT_PUT_LINE('MERGE COUNT = '||SQL%ROWCOUNT);
-- DELETE문에서의 배열처리. VALUES OF를 사용하여 범위지정
FORALL i IN VALUES OF v_id_arr
DELETE FROM t WHERE id = v_id_arr(i);
DBMS_OUTPUT_PUT_LINE('DELETE COUNT = '||SQL%ROWCOUNT);
END;
FORALL문과 예외 처리
기본적으로 FORALL 처리 중 오류를 만나면 FORALL문 전체가 롤백된다.
하지만 예외처리가 되어 있는 경우 다르게 동작하는데 오류를 만나기 전까지 처리된 건을 롤백하지 않고 오류를 만난 건만 처리가 롤백되며 문장은 중단된다.