내용 목차
본 장에서는 Tibero의 컬렉션 타입과 사용방법에 대해서 설명한다.
컬렉션 타입은 같은 타입의 값들을 모아 두기 위해 정의할 수 있는 사용자 정의 타입의 한 종류이다.
다음의 두 가지 형태 중 하나로 존재한다.
현재 중첩 테이블은 PSM 내에서만 사용이 가능하다.
CREATE TYPE 문을 사용해 컬렉션 타입을 생성한다.
가변 배열 타입을 생성하기 위해서는 다음과 같이 AS VARRAY를 명시한다. VARRAY 다음에 오는 괄호 안에 이 가변 배열이 담을 수 있는 요소 갯수의 최대값을 입력한다. OF 뒤에 오는 요소 타입으로는 내장 타입 또는 사용자 정의 타입의 이름을 명시할 수 있다.
컬렉션 타입 생성은 객체 타입 생성과 마찬가지로 실제 컬렉션을 저장하기 위한 공간을 할당하는 것이 아니며 컬렉션의 모양만을 기술할 뿐이다. LOB 타입 그리고 XMLType 들에 대한 가변 배열은 생성할 수 없다. 컬렉션 타입은 테이블의 컬럼 타입, 객체 타입의 속성 타입, PSM 내에서의 변수 및 매개변수 그리고 반환값의 타입으로써 사용될 수 있다. 객체 테이블의 타입으로는 사용이 불가능하다.
컬렉션(컬렉션 값)을 생성하기 위해서는 컬렉션 타입의 이름 다음에 괄호 안에 해당 컬렉션 타입의 요소들을 콤마(,)와 함께 나열하여 생성할 수 있다.
괄호 안에 어떤 요소 값도 주지 않은 채로 컬렉션을 생성할 때 이것을 빈 컬렉션이라고 한다. 빈 컬렉션은 컬렉션에 참여하는 요소만 없을 뿐 NULL은 아니다.
컬렉션 타입의 요소의 타입이 또 다른 컬렉션 타입이거나, 요소의 타입이 객체 타입이고 이 객체 타입의 요소 중 하나가 컬렉션 타입일 경우 이것을 다층 컬렉션 타입이라고 한다. 현재 SQL 쿼리에서는 가변 배열로만 다층 컬렉션 타입의 구성이 가능하며 중첩 테이블과 가변 배열을 섞어서 다층 컬렉션 타입을 구성할 수는 없다.
컬렉션 타입을 사용할 수 있는 곳이라면 다층 컬렉션 타입도 사용이 가능하다. 다층 컬렉션(컬렉션 값)을 생성할 때도 일반 컬렉션과 마찬가지 방식으로 요소들을 명시하는데, 결국 이것은 다음과 같이 컬렉션 타입 이름을 중첩해서 명시한다.
[예 7.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 절에 사용하여 컬렉션의 각 요소를 행으로 바꾼 테이블처럼 사용할 수 있도록 하는 표현식이다.
[예 7.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() 표현식으로 인해 만들어지는 테이블은 COLUMN_VALUE라고 하는 하나의 컬럼만을 가진다.
TABLE() 표현식에 대해서는 다음과 같이 외부 조인 연산자인 (+)를 명시하여 외부 조인을 수행시킬 수 있다.
[예 7.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() 표현식의 인자로 주어서 컬렉션 내용을 풀어헤쳐 조회해 볼 수 있다. 즉, 서브 쿼리는 일반적인 값 표현식에 올 수 있는 스칼라 서브 쿼리만 허용된다.
[예 7.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.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 컬럼을 가지지 않으며 객체 테이블의 기반 객체 타입의 속성 이름과 같은 이름의 컬럼들을 가진다.
[예 7.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.