⑤ 테이블 컬럼 타입으로 중첩 테이블 사용하기
중첩 테이블도 VARRAY처럼 테이블의 컬럼 타입으로 사용 가능한데, VARRAY보다 훨씬 유연하며 더 나은 기능을 제공한다. VARRAY 타입을 테이블 컬럼으로 사용했던 직전 예제와 똑같이 중첩 테이블로 구현하면서 두 타입의 차이점을 알아 보자.
먼저 다음과 같이 중첩 테이블로 사용자 정의 타입을 생성한다.
입력
CREATE OR REPLACE TYPE country_nt IS TABLE OF VARCHAR2(30);
결과
TYPE COUNTRY_NT이(가) 컴파일되었습니다.
이제 중첩 테이블 타입을 컬럼으로 하는 ch11_continent_nt 테이블을 생성한다.
입력
CREATE TABLE ch11_continent_nt (
continent VARCHAR2(50), -- 대륙명
country_nm country_nt -- 국가명을 넣을 중첩 테이블 타입
);
결과
SQL 오류: ORA-22913: 내포된 테이블 열 또는 속성에 테이블 이름이 지정되어야 합니다.
이런! 오류가 발생했다. 이상한 점은 보이지 않는데 왜 오류가 발생한 것일까? 범인은 중첩 테이블 타입에 있다. 중첩 테이블을 컬럼 타입으로 가진 일반 테이블을 생성할 때는 해당 컬럼이 저장될 별도의 저장 공간을 명시해야 한다.
컬럼 타입이 중첩 테이블인 일반 테이블 생성 구문
CREATE TABLE 테이블명(
...
중첩 테이블_컬럼명 중첩 테이블 타입,
...)
NESTED TABLE 중첩 테이블_컬럼명 STORE AS 저장공간명;
따라서 ch11_continent_nt 테이블은 다음과 같이 생성해야 한다.
입력
CREATE TABLE ch11_continent_nt (
continent VARCHAR2(50), -- 대륙명
country_nm country_nt -- 국가명을 넣을 중첩 테이블 타입
)
NESTED TABLE country_nm STORE AS country_nm_nt;
결과
table CH11_CONTINENT_NT이(가) 생성되었습니다.
이제 데이터를 집어 넣고 이전 예제처럼 다른 국가로 갱신한 다음 출력해 보자.
입력
-- 새로운 국가 세팅
new_country country_nt := country_nt('이태리', '스페인', '네델란드', '체코', '포르투칼');
country_list country_nt;
BEGIN
-- 생성자를 사용해 국가명을 입력
INSERT INTO ch11_continent_nt
VALUES('아시아', country_nt('한국','중국','일본'));
INSERT INTO ch11_continent_nt
VALUES('북아메리카', country_nt('미국','캐나다','멕시코'));
INSERT INTO ch11_continent_nt
VALUES('유럽', country_nt('영국','프랑스','독일', '스위스'));
-- 새로운 국가로 update
UPDATE ch11_continent_nt
SET country_nm = new_country
WHERE continent = '유럽';
COMMIT;
-- UPDATE 됐는지 확인을 위해 국가명 컬럼을 중첩 테이블 변수에 받아 옴
SELECT country_nm
INTO country_list
FROM ch11_continent_nt
WHERE continent = '유럽';
-- 루프를 돌며 국가를 출력
FOR i IN country_list.FIRST .. country_list.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('유럽국가명 = ' || country_list(i));
END LOOP;
END;
결과
유럽국가명 = 이탈리아
유럽국가명 = 스페인
유럽국가명 = 네델란드
유럽국가명 = 체코
유럽국가명 = 포르투칼
이전 예제와 동일한 결과가 나왔다. 이 말은 중첩 테이블의 경우 VARRAY로 할 수 있는 모든 기능을 수행할 수 있다는 뜻이다. 하지만 중첩 테이블만 가능한 기능이 있다. 먼저 VARRAY를 사용해 ch11_continent 테이블을 조회해 보자.
입력
SELECT *
FROM ch11_continent;
결과
CONTINENT COUNTRY_NM
-------------- ---------------------------------------------------------------------
아시아 ORA_USER.COUNTRY_VAR('한국','중국','일본')
북아메리카 ORA_USER.COUNTRY_VAR('미국','캐나다','멕시코')
유럽 ORA_USER.COUNTRY_VAR('이탈리아','스페인','네델란드','체코','포르투칼')
일반 테이블과는 다른 형태로 결과가 추출되었다. 즉 country_nm 컬럼은 VARRAY 사용자 정의 타입으로 선언한 country_var와 컬렉션에 들어간 값이 위와 같은 형태로 출력되었다. 중첩 테이블 컬럼을 가진 테이블도 마찬가지인데, 이런 경우 “TABLE( )” 함수를 사용하면 컬렉션에 들어있는 값만 골라낼 수 있다. TABLE 함수는 ““TABLE(컬렉션타입_컬럼)” 형태로 사용되어 SELECT 문의 FROM 절에서 마치 실제 테이블처럼 사용할 수 있다.
입력
SELECT continent, b.*
FROM ch11_continent a, TABLE(a.country_nm) b
WHERE continent = '유럽';
결과
CONTINENT COLUMN_VALUE
----------- -----------------
유럽 이탈리아
유럽 스페인
유럽 네델란드
유럽 체코
유럽 포르투칼
원래는 유럽에 속한 국가가 콤마로 구분되어 가로로 들어가 있었는데, TABLE 함수를 사용했더니 컬렉션 컬럼 자체를 하나의 테이블처럼 인식해 로우로 추출해 낸 것이다. 여기서 눈여겨 봐야 할 것은 두 번째 컬럼 이름인 COLUMN_VALUE라는 키워드다. COLUMN_VALUE는 TABLE 함수를 써서 로우로 변환된 컬렉션 타입의 컬럼값을 나타내는 키워드다. 즉 위 SELECT 문에서 ‘b.*’ 대신 b.COLUMN_VALUE를 사용해도 같은 결과가 조회된다. 또한 다음과 같은 형태로도 사용할 수 있다.
입력
SELECT *
FROM TABLE(SELECT d.country_nm
FROM ch11_continent_nt d
WHERE d.continent = '유럽');
결과
COLUMN_VALUE
---------------------------
이탈리아
스페인
네델란드
체코
포르투갈
서브 쿼리 부분을 보면 해당 테이블에서 컬렉션 컬럼만 추출해 냈고, 이를 다시 TABLE 함수로 감싼 결과를 SELECT하여 유럽에 속한 국가가 로우 형태로 추출되었다. 주의할 점은 TABLE 함수를 서브 쿼리에서 사용할 때는 서브 쿼리의 SELECT 리스트에는 반드시 한 번에 한 컬렉션 컬럼만 올 수 있다는 것이다.