컬렉션 타입의 사용

본 장에서는 Tibero의 컬렉션 타입과 사용방법에 대해서 설명합니다.

컬렉션 타입은 같은 타입의 값들을 모아두기 위해 정의할 수 있는 사용자 정의 타입의 한 종류입니다. 다음의 두 가지 형태 중 하나로 존재합니다.

가변 배열은 순서가 있는 같은 타입의 요소들의 모음입니다.
중첩 테이블은 갯수의 제한이 없는 순서가 정해지지 않은 같은 타입의 요소들의 모음입니다.

참고

현재 중첩 테이블은 PSM 내에서만 사용이 가능합니다.

컬렉션 타입 생성

CREATE TYPE 문을 사용해 컬렉션 타입을 생성합니다.

가변 배열 타입을 생성하기 위해서는 다음과 같이 AS VARRAY를 명시합니다. VARRAY 다음에 오는 괄호 안에 이 가변 배열이 담을 수 있는 요소 갯수의 최대값을 입력합니다. OF 뒤에 오는 요소 타입으로는 내장 타입 또는 사용자 정의 타입의 이름을 명시할 수 있습니다.

[예 1] 컬렉션 타입 생성

REATE TYPE str_varr_type AS VARRAY(10000) OF VARCHAR(100);
/

컬렉션 타입 생성은 객체 타입 생성과 마찬가지로 실제 컬렉션을 저장하기 위한 공간을 할당하는 것이 아니며 컬렉션의 모양만을 기술할 뿐입니다. LOB 타입 그리고 XMLType들에 대한 가변 배열은 생성할 수 없습니다. 컬렉션 타입은 테이블의 컬럼 타입, 객체 타입의 속성 타입, PSM 내에서의 변수 및 매개변수 그리고 반환값의 타입으로써 사용될 수 있습니다. 객체 테이블의 타입으로는 사용이 할 수 없습니다.

컬렉션(컬렉션 값) 생성

컬렉션(컬렉션 값)을 생성하기 위해서는 컬렉션 타입의 이름 다음에 괄호 안에 해당 컬렉션 타입의 요소들을 콤마(,)와 함께 나열하여 생성할 수 있습니다.

[예 2] 컬렉션(컬렉션 값) 생성

str_varr_type('ABC', 'DEFG', 'HH') 
str_varr_type()

괄호 안에 어떤 요소 값도 주지 않은 채로 컬렉션을 생성할 때 이것을 빈 컬렉션이라고 합니다. 빈 컬렉션은컬렉션에 참여하는 요소만 없을 뿐 NULL은 아닙니다.

다층 컬렉션 타입

컬렉션 타입의 요소의 타입이 또 다른 컬렉션 타입이거나, 요소의 타입이 객체 타입이고 이 객체 타입의 요소 중 하나가 컬렉션 타입일 경우 이것을 다층 컬렉션 타입이라고 합니다. 현재 SQL 쿼리에서는 가변 배열로만 다층 컬렉션 타입의 구성이 가능하며 중첩 테이블과 가변 배열을 섞어서 다층 컬렉션 타입을 구성 할 수는 없습니다.

컬렉션 타입을 사용할 수 있는 곳이라면 다층 컬렉션 타입도 사용 가능합니다. 다층 컬렉션(컬렉션 값)을 생성할 때도 일반 컬렉션과 마찬가지 방식으로 요소들을 명시하는데, 결국 이것은 다음과 같이 컬렉션 타입 이름을 중첩해서 명시합니다.

[예 3] 다층 컬렉션 타입

CREATE OR REPLACE TYPE str_varr_coll_type AS VARRAY(1000) OF str_varr_type;
/

CREATE TABLE nested_coll_tbl (id number, coll_val str_varr_coll_type); 

INSERT INTO nested_coll_tbl VALUES (1,
    str_varr_coll_type(str_varr_type('AB', 'CD'), str_varr_type())); 
INSERT INTO nested_coll_tbl VALUES (2,
    str_varr_coll_type(str_varr_type(), str_varr_type('EF', 'GH')));

사용 예제

본 절에서는 컬렉션 타입을 사용하는 경우에 따른 사용법을 설명합니다.

쿼리에서 사용

현재 SELECT의 결과 컬럼을 컬렉션으로 하여 컬렉션 내용을 클라이언트로 전달하는 기능은 구현되어 있지 않습니다. 그러나 TABLE() 표현식을 사용해서 컬렉션을 풀어헤치면 SELECT 쿼리에서도 컬렉션의 내용을 조회할 수가 있습니다. TABLE() 표현식은 FROM 절에 사용하여 컬렉션의 각 요소를 행으로 바꾼 테이블처럼 사용할 수 있도록 하는 표현식입니다.

[예 4] 쿼리에서의 컬렉션 타입 사용

CREATE TABLE coll_tbl (id number, coll_val str_varr_type);

INSERT INTO coll_tbl VALUES (1, str_varr_type('AB', 'CD')); 
INSERT INTO coll_tbl VALUES (2, str_varr_type('EF', 'GH')); 
INSERT INTO coll_tbl VALUES (3, str_varr_type());

SQL> SELECT id, d.* FROM coll_tbl c, TABLE(c.coll_val) d; 

        ID         COLUMN_VALUE
-----------        --------------
        1          AB
        1	   CD
        2	   EF
        2          GH
        
4 rows selected.

TABLE() 표현식의 인자로 공급되는 컬럼은 FROM 절에서 자신의 왼쪽에 있는 테이블의 컬럼 중 컬렉션 타입의 컬럼을 명시할 수 있습니다(이를 위해 일반적으로 위와 같이 테이블 별칭을 사용합니다). 위와 같이 내장 타입 혹은 컬렉션에 대한 컬렉션일 경우 TABLE() 표현식으로 인해 만들어지는 테이블은 COL UMN_VALUE라고 하는 하나의 컬럼만을 가집니다.

TABLE() 표현식에 대해서는 다음과 같이 외부 조인 연산자인 (+)로 외부 조인을 수행시킬 수 있습니다.

[예 5] 컬렉션 타입의 외부 조인 Select

SQL> SELECT id, d.* FROM coll_tbl c, TABLE(c.coll_val)(+) d; 

        ID         COLUMN_VALUE
-----------        --------------
        1          AB
        1	   CD
        2	   EF
        2          GH
        3
        
5 rows selected.

외부 조인을 수행하지 않았을 경우에는 빈 컬렉션에 해당하는 로우가 Select되지 않지만, 외부 조인을 수행하였을 경우에는 빈 컬렉션에 해당하는 로우도 출력되는 것을 볼 수 있습니다.

서브 쿼리의 결과가 하나의 컬렉션 값을 반환하는 스칼라 서브 쿼리일 경우 이 서브 쿼리를 TABLE() 표현식의 인자로 주어서 컬렉션 내용을 풀어 조회해 볼 수 있습니다. 즉, 서브 쿼리는 일반적인 값 표현식에 올 수 있는 스칼라 서브 쿼리만 허용됩니다.

[예 6] 서브 쿼리 결과가 컬렉션인 경우

SQL> SELECT * FROM TABLE(SELECT coll_val FROM coll_tbl WHERE id = 1);

COLUMN_VALUE
--------------
AB 
CD

2 rows selected.

위 예제의 서브쿼리에서 WHERE 절이 존재하지 않고 서브쿼리의 결과 로우의 갯수가 2개 이상일 경우는, 위 TABLE 쿼리는 정상 수행되지 않고 런타임 에러를 발생시킵니다.

다층 컬렉션의 경우 TABLE() 표현식을 반복해서 사용함으로써 다층 컬렉션의 임의의 계층에 들어있는 요소들도 추출이 가능합니다. 이 때 상위 계층의 TABLE() 표현식이 만들어 내는 컬렉션 컬럼을 그 다음 계층 의 TABLE() 표현식에 명시하기 위해서는 테이블 별칭이 필요합니다.

[예 7] 다층 컬렉션

SQL> SELECT id, z.* FROM nested_coll_tbl x, TABLE(x.coll_val) y,
                                TABLE(y.COLUMN_VALUE) z;

        ID         COLUMN_VALUE
-----------        --------------
        1          AB
        1	   CD
        2	   EF
        2          GH
        
4 rows selected.

컬렉션이 객체에 대한 컬렉션일 경우 TABLE() 표현식이 반환하는 테이블은 객체 테이블이 되며 객체 테이블에서 구사할 수 있는 각종 표현식을 똑같이 구사할 수 있습니다. 또한 이 테이블은 COLUMN_VALUE 컬럼을 가지지 않으며 객체 테이블의 기반 객체 타입의 속성 이름과 같은 이름의 컬럼들을 가집니다.

[예 8] 객체에 대한 컬렉션인 경우

/CREATE TYPE customer_type2 AS OBJECT 
    ( custno NUMBER,
    name VARCHAR2(40), 
    phone VARCHAR2(20),
    MEMBER FUNCTION tostring RETURN VARCHAR);
/

CREATE TYPE BODY customer_type2 AS
    MEMBER FUNCTION tostring RETURN VARCHAR IS 
    BEGIN
        RETURN custno || ':' || name || ':' || phone; 
    END;
END;
/

CREATE OR REPLACE TYPE cust_coll_type AS VARRAY(400) OF customer_type2;
/

CREATE TABLE cust_coll_tab 
    ( id NUMBER,
    coll_val cust_coll_type);

INSERT INTO cust_coll_tab VALUES (1, 
    cust_coll_type(customer_type2(1, 'Bob', '222-333-4444'),
                    customer_type2(2, 'Alice', '444-555-6666')));

SQL> SELECT id, d.*, VALUE(d).tostring() str FROM 
        cust_coll_tab c, TABLE(c.coll_val) d;
        
        ID    CUSTNO     NAME     PHONE            STR
----------    -------    ------   ------------     ----------------------
        1          1     Bob	  222-333-4444     1:Bob:222-333-4444
        1          2     Alice    444-555-6666     2:Alice:444-555-6666
        
2 rows selected.

DML에서 사용

현재 중첩 테이블에 대해서는 DML이 지원되지 않고 가변 배열에 대해서는 가변 배열값 전체를 Insert하거나 Update하는 것은 지원됩니다. 쿼리 내에서 가변 배열의 요소를 조작하는 것은 지원되지 않습니다.

Previous객체 타입의 사용 NextAppendix A. tbJDBC 예제

Last updated 2 months ago