⑥ DML을 이용한 요소 처리
바로 이전 절에서 설명한 내용이 VARRAY와 중첩 테이블의 공통 기능이었고, 지금부터 설명할 내용은 중첩 테이블만 가능한 기능이다. 이전 예제에서는 VARRAY, 중첩 테이블 타입의 country_nm 컬럼에 데이터를 넣고 갱신했었는데 하나의 값이 아닌 컬럼 전체 값을 대상으로 했다. 즉 유럽에 속한 국가 목록 전체를 입력하고 수정했지, “영국,독일,이탈리아, 프랑스”에 ‘스페인’만 추가하거나 ‘영국’ 대신 ‘스페인’을 대체하는 식으로 처리하지는 못했다. 이처럼 VARRAY 컬럼 타입은 해당 컬렉션 컬럼에 속한 전체 요소를 일괄적으로 처리하는데 사용되는 것이 보통이다.
반면 중첩 테이블은 VARRAY와 같은 기능을 지원하면서도 동시에 컬렉션 요소 값을 개별적으로 처리할 수 있다. 예컨대, ‘영국’을 ‘스페인’으로 대체하거나 기존 요소 값에 새로운 값을 추가할 수 있다는 말이다. 어떤 식으로 동작하는지 예제를 통해 살펴 보자. 먼저 기존 컬럼에 새로운 요소 값을 추가해 보자.
입력
DECLARE
-- 출력용 중첩 테이블 변수 선언
country_list country_nt;
BEGIN
-- 기존 국가명을 받아 와 출력
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('유럽국가명(OLD) = ' || country_list(i));
END LOOP;
-- 유럽에 벨기에를 추가 TABLE 함수를 써서 INSERT가 가능
INSERT INTO TABLE ( SELECT d.country_nm
FROM ch11_continent_nt d
WHERE d.continent = '유럽')
VALUES ('벨기에');
COMMIT;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
-- 추가됐는지 확인
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('유럽국가명(NEW) = ' || country_list(i));
END LOOP;
END;
결과
유럽국가명(OLD) = 이탈리아
유럽국가명(OLD) = 스페인
유럽국가명(OLD) = 네델란드
유럽국가명(OLD) = 체코
유럽국가명(OLD) = 포르투칼
--------------------------------------------
유럽국가명(NEW) = 이탈리아
유럽국가명(NEW) = 스페인
유럽국가명(NEW) = 네델란드
유럽국가명(NEW) = 체코
유럽국가명(NEW) = 포르투칼
유럽국가명(NEW) = 벨기에
예상했듯이 새로 입력한 벨기에가 맨 끝에 추가되었다. INSERT, UPDATE, DELETE문은 로우 단위로 처리되는데, 컬렉션 컬럼을 가진 테이블은 한 로우당 해당 컬렉션 타입의 데이터는 여러 개라는 문제가 있다. 하지만 INSERT문에 TABLE 함수를 써서 중첩 테이블 타입의 컬럼 데이터를 로우 형식으로 변환하니 해당 컬럼에 신규 요소 값(벨기에)을 추가할 수 있었던 것이다.
이번에는 기존 요소 값을 변경해 보자. 변경을 해야 하니 당연히 UPDATE문을 사용한다.
입력
DECLARE
-- 출력용 중첩 테이블 변수 선언
country_list country_nt;
BEGIN
-- 이탈리아를 영국으로 변경
UPDATE TABLE( SELECT d.country_nm
FROM ch11_continent_nt d
WHERE d.continent = '유럽' ) t
SET VALUE(t) = '영국'
WHERE t.column_value = '이태리';
COMMIT;
-- 변경됐는지 확인
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('유럽국가명(NEW) = ' || country_list(i));
END LOOP;
END;
결과
유럽국가명(NEW) = 영국
유럽국가명(NEW) = 스페인
유럽국가명(NEW) = 네델란드
유럽국가명(NEW) = 체코
유럽국가명(NEW) = 포르투칼
유럽국가명(NEW) = 벨기에
일단 결과만 보면 UPDATE문을 사용해 원하던 대로 ‘이탈리아’가 ‘영국’으로 바뀌었는데, 어떻게 이 같은 결과가 나왔는지 해당 UPDATE문을 분석해 보자.
UPDATE TABLE( SELECT d.country_nm
FROM ch11_continent_ntd → ⓐ
WHERE d.continent = '유럽' )t
SET VALUE(t) = '영국' → ⓑ
WHERE t.COLUMN_VALUE = '이탈리아'; → ⓒ
ⓐ는 TABLE 함수를 써서 유럽에 속한 국가를 가져오고 있다. 다시 한 번 말하는데 country_nm은 중첩 테이블 타입의 컬럼이므로 값이 여러 개지만 TABLE 함수를 써서 컬럼 값을 로우로 반환할 수 있다. 따라서 이 부분은 유럽에 속한 국가 리스트를 반환하는 t라는 테이블이 된 것이다.
ⓑ는 SET절로 값을 갱신하는 부분인데, 지금 작업 단위는 ch11_continent_nt 테이블의 로우가 아닌 중첩 테이블 컬럼 값(요소)으로 이루어진 로우다. 이때 사용하는 “VALUE” 키워드는 ⓐ에서 반환된 로우 전체를 의미한다.
ⓒ는 COLUMN_VALUE 키워드를 써서 해당 국가가 ‘이탈리아’인 건만 갱신하도록 하는 WHERE 절이다. 만약 이 WHERE절이 빠지면, 유럽에 속한 모든 국가명이 ‘영국’으로 변경될 것이다.
마지막으로, 중첩 테이블 컬럼 값에 DELETE문을 적용해 보자. INSERT, UPDATE와 마찬가지로 DELETE문도 해당 컬렉션 컬럼 값을 작업 대상으로 한다.
입력
DECLARE
-- 출력용 중첩 테이블 변수 선언
country_list country_nt;
BEGIN
-- 변경된 영국을 지음
DELETE FROM TABLE( SELECT d.country_nm
FROM ch11_continent_nt d
WHERE d.continent = '유럽' ) t
WHERE t.column_value = '영국';
COMMIT;
-- 변경됐는지 확인
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('유럽국가명(NEW) = ' || country_list(i));
END LOOP;
END;
결과
유럽국가명(NEW) = 스페인
유럽국가명(NEW) = 네델란드
유럽국가명(NEW) = 체코
유럽국가명(NEW) = 포르투칼
유럽국가명(NEW) = 벨기에
직전에 설명한 UPDATE문의 동작 방식을 이해했다면, 이 DELETE문이 어떤 식으로 처리됐는지 알 수 있을 것이다. 이처럼 타입이 중첩 테이블인 컬럼이 있는 테이블은, 일반적인 DML문 처리도 가능하고 TABLE 함수를 사용해 중첩 테이블 컬럼 자체를 마치 하나의 테이블인양 사용해서 해당 컬렉션 요소에 대한 부분 범위 처리가 가능하다.