Oracle PL/SQL - Collection

프로그래밍

Oracle PL/SQL - Collection

RainIron 2021. 9. 23. 15:51

※ Oracle PL/SQL은 컴포지트 데이터 타입으로 컬렉션과 레코드를 지원한다.

- 컬렉션(Collection): 동일 타입 데이터의 반복

- 레코드(Record): 서로 다른 타입의 데이터 모음

1. 컬렉션 타입의 종류

	Associative Array	VARRAY	Nested Table
컬렉션 항목의 개수	미지정	지정	미지정
인덱스 유형	문자열 또는 정수	정수	정수
초기화되지 않은 상태	Empty	NULL	NULL
ADT(Abstract Data Type) 정의	불가능	가능	가능

- VARRAY, Nested Table 컬렉션 변수는 사용하기 전에 반드시 초기화해야 한다.

* 컬렉션 타입과 대응되는 일반 자료 구조

일반 자료 구조	PL/SQL 컬렉션 타입
해시 테이블, 비순서 테이블	Associative Array
배열, 벡터	VARRAY
집합, 중복 허용 집합	Nested Table

2. Associative Array

== 키-값 쌍으로 이루어진 무한 배열(키값은 순서대로 정렬되어 저장)

	Associative Array
컬렉션 항목의 개수	미지정
인덱스 유형	문자열 또는 정수
초기화되지 않은 상태	Empty
ADT(Abstract Data Type) 정의	불가능

- 컬렉션 항목의 개수: 미지정, 항목의 개수에 한계가 없음.

- 초기화되지 않은 상태: Empty, 존재하지만 항목을 가지지 않는 항목의 개수가 0인 컬렉션

-> 비어있는 컬렉션에 항목을 추가하려면 EXTEND 메소드를 호출

- 서버프로그램 내에서 사용될 소규모의 참조 테이블에서 사용하거나, 컬렉션을 서버로 전달 또는 서버로부터 전달받을 때 유용하게 사용할 수 있다.

* 선언 방법

TYPE 타입명 IS TABLE OF 데이터타입 INDEX BY 인덱스데이터타입;
변수명 타입명;

* 사용 예시

DECLARE
    TYPE city IS TABLE OF VARCHAR2(64) INDEX BY PLS_INTEGER;
    TYPE population IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
    
    v_city city;
    v_Population population;
BEGIN
    v_city(-1) := '서울';
    v_city(0) := '부산';
    v_city(1) := '대전';
    
    v_Population('서울') := 10373232;
    v_Population('부산') := 73232;
    v_Population('대전') := 33232;
    
    DBMS_OUTPUT.PUT_LINE('도시별 인구(2000년 기준)');
    DBMS_OUTPUT.PUT_LINE('======================');
    DBMS_OUTPUT.PUT_LINE(v_city(-1) || ': ' || TO_CHAR(v_Population(v_city(-1)), '99,999,999'));
    DBMS_OUTPUT.PUT_LINE(v_city(0) || ': ' || TO_CHAR(v_Population(v_city(0)), '99,999,999'));
    DBMS_OUTPUT.PUT_LINE(v_city(1) || ': ' || TO_CHAR(v_Population(v_city(1)), '99,999,999'));
END;

* 사용 예시2

DECLARE
    TYPE string_array IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
    v_arr string_array;
BEGIN
    SELECT ename
    BULK COLLECT INTO v_arr
    FROM emp;
    DBMS_OUTPUT.PUT_LINE('Associative Array 컬렉션 건수 = '|| v_arr.COUNT);
END;

- BULK COLLECT INTO 를 사용하면 조회된 여러 건의 로우로부터의 결과를 로우 건수만큼의 항목을 가지는 배열에 넣어준다.

- 변수명.COUNT는 컬렉션 변수의 항목 개수를 조회하는 메서드다.

* 사용 예시3

DECLARE
    TYPE int_array IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
    v_fibonacci int_array;
    c_order CONSTANT PLS_INTEGER := 20;
    
    /*
        N개의 피보나치 수열을 계산하여 배열을 반환하는 함수
    */
    FUNCTION fibonacci_sequence(num IN PLS_INTEGER) RETURN int_array
    IS
        v_arr int_array;
    BEGIN
        v_arr(1) := 0;
        v_arr(2) := 1;
        FOR i IN 3..num
            LOOP
                v_arr(i) := v_arr(i-1) + v_arr(i-2);
            END LOOP;
        RETURN v_arr;
    END;

BEGIN
    v_fibonacci := fibonacci_sequence(c_order);
    DBMS_OUTPUT.PUT_LINE('피보나치 수열의 '||c_order||'개 항');
    FOR i IN 1..c_order
        LOOP
            DBMS_OUTPUT.PUT(CASE WHEN 1<i THEN ', ' END || v_fibonacci(i));
        END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
END;

3. VARRAY(Variable-Size Array)

	VARRAY
컬렉션 항목의 개수	지정
인덱스 유형	정수
초기화되지 않은 상태	NULL
ADT(Abstract Data Type) 정의	가능

- 실제 크기는 최소 0부터 타입 선언 시에 지정된 최대 크기 사이에서 동적으로 할당된다.

- 인덱스는 1부터 시작

* 선언 방법

TYPE 타입명 IS VARRAY(크기) OF 데이터타입;
변수명 타입명;

* 사용 예시

DECLARE
    TYPE languages IS VARRAY(10) OF VARCHAR2(64);
    v_lang languages; -- v_lang은 NULL
    v_lang2 languages := languages('한국어', '중국어', '영어'); -- 변수 선언 시 생성자를 사용하여 초기화
BEGIN
    v_lang := languages(); -- 컬렉션 생성자를 사용하여 Empty로 초기화
    v_lang := languages('한국어', '중국어'); -- 크기가 2인 VARRAY로 재초기화
    
    v_lang.EXTEND(2); -- 크기 증가
    v_lang(3) := '영어';
    v_lang(4) := '일본어';
    
    -- 출력
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('언어 목록');
    DBMS_OUTPUT.PUT_LINE('===========');
    FOR i in v_lang.FIRST .. v_lang.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE(TO_CHAR(i) || ' : ' || v_lang(i));
        END LOOP;
END;

- EXTEND는 컬렉션의 항목 수를 증가시키는 메소드

- FIRST와 LAST는 각각 컬렉션의 첫 번째 항목의 인덱스와 마지막 항목의 인덱스를 반환하는 메소드

* 사용 예시2

DECLARE
    TYPE string_array IS VARRAY(20) OF VARCHAR2(100);
    v_arr string_array;
BEGIN
    SELECT ename
    BULK COLLECT INTO v_arr
    FROM emp
    WHERE ROWNUM <= 20;
    DBMS_OUTPUT.PUT_LINE('VARRAY 컬렉션 건수 = ' || v_arr.COUNT);
END;

4. Nested Table

	Nested Table
컬렉션 항목의 개수	미지정
인덱스 유형	정수
초기화되지 않은 상태	NULL
ADT(Abstract Data Type) 정의	가능

- 순서가 고정되어 있지 않고 크기도 고정되지 않은 데이터의 집합을 저장하는데 적합한 컬렉션

- 인덱스는 1부터 시작

- 크기는 동적으로 증가될 수 있다.

- 처음에는 밀집형이지만 데이터 중 일부를 삭제하면 희소형으로 바뀔 수 있다.

- 개수가 확정되지 않거나, 인덱스가 연속적이지 않거나, 컬렉션의 일부 항목을 삭제 또는 변경할 필요가 있는 경우에 유용하게 사용할 수 있다.

* 선언 방법

TYPE 타입명 IS TABLE OF 데이터타입;
변수명 타입명;

* 사용 예시

DECLARE
    TYPE city IS TABLE OF VARCHAR2(64);
    
    v_city city;
    v_city2 city := city('서울', '부산', '대전');
BEGIN
    v_city := city('서울', '부산', '대전', '광주', '인천');
    
    v_city := city();
    v_city.EXTEND; v_city(1) := '서울';
    v_city.EXTEND; v_city(2) := '부산';
    v_city.EXTEND; v_city(3) := '대구';
    v_city.EXTEND; v_city(4) := '광주';
    DBMS_OUTPUT.PUT_LINE('도시 개수 : ' || v_city.COUNT||'개');
    FOR i in v_city.FIRST .. v_city.LAST
    LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(CHR(9) || 'v_city(' || TO_CHAR(i) || '):' || v_city(i));
        END IF;
    END LOOP;
    
    v_city.DELETE(3);
    DBMS_OUTPUT.PUT_LINE('도시 개수 : ' || v_city.COUNT||'개');
    
    FOR i in v_city.FIRST .. v_city.LAST
    LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(CHR(9) || 'v_city(' || TO_CHAR(i) || '):' || v_city(i));
        END IF;
    END LOOP;
END;

5. 컬렉션 연산

* 할당 연산(:=) : 동일 타입의 변수 간에 값의 복사가 가능하다. 구조가 동일하더라도 타입명이 다르면 할당 연산이 불가능하다.

* 비교 연산

- Associative Array: 비교 불가능

- VARRAY, Nested Table: 제한된 범위 내에서 비교가 가능

- VARRAY, Nested Table은 동치 비교(=) or 부등 비교(<>, !=, ~=, ^=)를 지원

* 메소드

메소드	설명
DELETE	컬렉션에서 항목을 삭제한다. - DELETE: 모든 항목 삭제 - DELETE(N): N번째 항목 삭제 - DELETE(M, N): M번째 항목부터 시작해서 N개의 항목 삭제
TRIM	VARRAY, Nested Table의 끝 부분의 항목을 삭제한다. - TRIM: 마지막 1개 항목 삭제 - TRIM(N): 마지막의 N개 항목 삭제
EXTEND	VARRAY, Nested Table의 끝 부분의 항목을 추가한다. - EXTEND: 마지막 NULL 항목 추가 - EXTEND(N): N개의 NULL 항목 추가 - EXTEND(N, I): I번째 항목의 복사본을 N개 추가
EXISTS	VARRAY, Nested Table의 지정된 항목이 존재하면 TRUE를 반환, 그렇지 않으면 FALSE 반환
FIRST, LAST, COUNT	첫 번째 인덱스, 마지막 인덱스 반환, 항목의 개수 반환
LIMIT	컬렉션이 가질 수 있는 항목의 최대 개수를 반환(VARRAY만 값을 가짐)
PRIOR	지정된 인덱스 바로 앞의 인덱스를 반환(FIRST에 대해서는 NULL 반환)
NEXT	지정된 인덱스 바로 뒤의 인덱스를 반환(LAST에 대해서는 NULL 반환)

6. 배열 처리

항목	BULK COLLECT	FORALL
동작 방식	SELECT문이 서버에서 실행되고, 결과를 클라이언트에 회신하여 배열에 저장	DML로 처리할 데이터를 배열에 담아 서버로 전송하여 실행
데이터 이동 방향	테이블 -> 컬렉션	컬렉션 -> 테이블
사용 구문	SELECT, FETCH	INSERT, UPDATE, DELETE, MERGE

- 장점: 단순성, 성능 개선

- PL/SQL에서 배열을 사용하면 PL/SQL 엔진은 배열을 위한 메모리를 PGA(Program Global Area, 사용자 서버 프로세스별로 개별 할당되는 메모리)에서 할당한다. 과도하게 큰 크기의 배여을 사용하게 되면 서버의 메모리 부족을 유발할 수 있으며, 서버의 자원 고갈로 인해 장애로 이어질 수 있다.

* BULK COLLECT

(예시)

DECLARE
    TYPE emp_rec IS TABLE OF emp%ROWTYPE;
    v_emp_arr emp_rec;
BEGIN
    SELECT *
    BULK COLLECT INTO v_emp_arr
    FROM emp;
    DBMS_OUTPUT.PUT_LINE('건수1: '||v_emp_arr.COUNT);
    
    SELECT *
    BULK COLLECT INTO v_emp_arr
    FROM emp
    WHERE ROWNUM <= 10;
    DBMS_OUTPUT.PUT_LINE('건수2: '||v_emp_arr.COUNT);
END;

* FORALL

FORALL 인덱스명 IN 범위절
	DML문;

- 범위절의 표현 방식

1) '..' 사용

2) 'INDICES OF' 사용: 배열에서 유효한 항목(비어 있지 않은) 항목만을 처리하도록 지정한다.

3) 'VALUES OF' 사용: 배열에서 처리할 항목의 인덱스를 또 다른 배열로 제공한다.

(예시)

CREATE TABLE t(
    id INTEGER PRIMARY KEY,
    name VARCHAR(20)
);

DECLARE
    TYPE id_arr_type IS TABLE OF PLS_INTEGER;   -- Nested Table
    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;
    
    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);
    
    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);
    
    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
            UPDATE SET t.name = v_name_arr2(i)
        WHEN NOT MATCHED THEN
            INSERT (id, name)
            VALUES (v_id_arr(i), v_name_arr2(i));
        DBMS_OUTPUT.PUT_LINE('MERGE COUNT = '||SQL%ROWCOUNT);
END;

- FORALL 처리 중에 오류를 만나면 FORALL문 전체가 롤백된다. 하지만 예외 처리가 되어있을 경우, 오류를 만난 건만 처리가 롤백되며, 문장은 중단된다.

※ DBMS_OUTPUT.PUT_LINE을 보기 위해 아래의 명령을 실행해야 한다.

SET SERVEROUTPUT ON;

※ SQLPLUS 사이즈 조절

SET LINESIZE 300
SET PAGESIZE 100

※ 출처: 개발자를 위한 PL/SQL 프로그래밍

'프로그래밍' 카테고리의 다른 글

Oracle PL/SQL - Cursor (0)	2021.09.24
Oracle PL/SQL - Record (0)	2021.09.23
Eclipse Spring Package - Git 연동 (0)	2021.08.14
[MyBatis] ORM 프레임워크 활용(1) (0)	2021.07.06
[MySQL] With Recursive 문, 세션 변수 활용, Pagination (0)	2021.07.06

현재글Oracle PL/SQL - Collection

26살! 계획과 실행을 좋아합니다:) 소프트웨어 전공생

PL/SQL, Python, 회계관리, jsp, hive, 실습, oracle, pyspark, SpringMVC, 로지스틱회귀분석, CSS, Pandas, spring, r, matplotlib, kmeans, SQL, 모델평가, HTML, 빅데이터분석기사,

Today :
Yesterday :

일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

일상 정리하기