[PL/SQL] 컬렉션의 다양한 사용

1. 사용자 정의 데이터 컬럼

---

자주 사용하는 컬렉션 타입을 미리 정의해 놓고 마치 VARCHAR2, NUMBER 같은 빌트인(Built-in)타입 처럼 사용할 수 있게 해주는 기능. 

컬렉션에서는 VARRAY와 중첩 테이블이 사용이 가능하다.

사용자가 정의한 컬렉션을 Global 하게 사용할 수 있다는 개념으로 생각하면 될 듯 하다.

객체(OBJECT) 타입은 컬렉션은 아니지만 객체 지향 프로그래밍의 클래스 개념과 유사하며, 상속도 가능하다. 

VARRAY : CREATE OR REPLACE TYPE 타입명 IS VARRAY (최대 크기) OF 데이터 타입;

중첩 테이블 : CREATE OR REPLACE TYPE 타입명 IS TABLE OF 데이터 타입;

OBJECT : CREATE OR REPLACE 타입명 IS OBJECT (

멤버1 멤버1_데이터 타입,

멤버2 멤버2_데이터 타입, ... );

실제로 예제를 컴파일 해 보자.

CREATE OR REPLACE TYPE global_va_classname IS VARRAY(5) OF VARCHAR2(30);
CREATE OR REPLACE TYPE global_va_student IS OBJECT(
        st_name VARCHAR2(20),
        st_age NUMBER,
        st_phone_num VARCHAR2(15),
        st_address VARCHAR2(50),
        st_class VARCHAR2(30));

VARRAY 타입과 OBJECT 타입의 사용자 정의 변수를 선언하였다.

위에 있는 타입은 초등학교 반의 이름을 저장하는 가변 배열이다.

아래의 타입은 학생에 대한 정보를 저장하는 객체이다.

DECLARE
    va_classname global_va_classname;
    va_students global_va_student;
BEGIN
    va_classname := global_va_classname('새싹반', '참새반', '비둘기반', '제비반', '갈매기반');
    va_students := global_va_student('김민수', 12, '051-880-0808', '부산시 수영구 한빛아파트 101동 111호', '비둘기반');
  
    DBMS_OUTPUT.PUT_LINE('학급 정보를 출력합니다..');
    
  FOR i IN va_classname.FIRST .. va_classname.LAST
      LOOP
          DBMS_OUTPUT.PUT_LINE(va_classname(i));
     END LOOP;
  
      DBMS_OUTPUT.PUT_LINE('--------------');
      DBMS_OUTPUT.PUT_LINE('학생 정보를 출력합니다..');
      DBMS_OUTPUT.PUT_LINE('이름 : '||va_students.st_name);
      DBMS_OUTPUT.PUT_LINE('나이 : '||va_students.st_age);
      DBMS_OUTPUT.PUT_LINE('주소 : '||va_students.st_address);
      DBMS_OUTPUT.PUT_LINE('전화번호 : '||va_students.st_phone_num);
      DBMS_OUTPUT.PUT_LINE('학급 : '||va_students.st_class);
  
END;

 

미리 정의해 두었던 사용자 타입에 맞추어 변수를 선언하고,

실행부에서 데이터를 초기화 한 후 출력하였다. 물론 초기화 한 대로 값이 잘 출력된다. 

그럼 OBJECT 타입을 VARRAY의 원소로 사용할 수 있을까?

아래의 예제로 확인 해 보자.

DECLARE
    va_classname global_va_classname;
    TYPE va_students_list_ty IS VARRAY(3) OF global_va_student; 
    va_students_list va_students_list_ty;
BEGIN
    va_classname := global_va_classname('새싹반', '참새반', '비둘기반', '제비반', '갈매기반');
    va_students_list := va_students_list_ty(global_va_student('김민수', 12, '051-880-0808', '부산시 수영구 한빛아파트 101동 111호', '비둘기반'),
    global_va_student('김영희', 12, '051-885-2222', '부산시 수영구 한빛아파트 105동 301호', '비둘기반'),
    global_va_student('이은지', 12, '051-555-2108', '부산시 수영구 은마동 21번길 102', '참새반'));
  
    DBMS_OUTPUT.PUT_LINE('학급 정보를 출력합니다..');
    
  FOR i IN va_classname.FIRST .. va_classname.LAST
      LOOP
          DBMS_OUTPUT.PUT_LINE(va_classname(i));
     END LOOP;
  
      DBMS_OUTPUT.PUT_LINE('--------------');
      DBMS_OUTPUT.PUT_LINE('학생 정보를 출력합니다..');
      
  
      FOR i in va_students_list.FIRST .. va_students_list.LAST
      LOOP
      
      DBMS_OUTPUT.PUT_LINE('이름 : '||va_students_list(i).st_name);
      DBMS_OUTPUT.PUT_LINE('나이 : '||va_students_list(i).st_age);
      DBMS_OUTPUT.PUT_LINE('주소 : '||va_students_list(i).st_address);
      DBMS_OUTPUT.PUT_LINE('전화번호 : '||va_students_list(i).st_phone_num);
      DBMS_OUTPUT.PUT_LINE('학급 : '||va_students_list(i).st_class);
      DBMS_OUTPUT.PUT_LINE('--------------');
 
      END LOOP;
      
      DBMS_OUTPUT.PUT_LINE('---- 출력 종료 -------');
 
END;

PL/SQL 프로시저가 성공적으로 완료되었습니다.

학급 정보를 출력합니다..

새싹반

참새반

비둘기반

제비반

갈매기반

--------------

학생 정보를 출력합니다..

이름 : 김민수

나이 : 12

주소 : 부산시 수영구 한빛아파트 101동 111호

전화번호 : 051-880-0808

학급 : 비둘기반

--------------

이름 : 김영희

나이 : 12

주소 : 부산시 수영구 한빛아파트 105동 301호

전화번호 : 051-885-2222

학급 : 비둘기반

--------------

이름 : 이은지

나이 : 12

주소 : 부산시 수영구 은마동 21번길 102

전화번호 : 051-555-2108

학급 : 참새반

--------------

---- 출력 종료 -------

OBJECT 타입을 활용하면 자바 프로그래밍에서 VO(Value Object)와 ArrayList를 사용하여 데이터를 처리하는 것과 유사하게 PL/SQL에서도 사용할 수 있다.

상속에 대해서는 추후에 이 부분만 따로 포스팅 할 예정임.

2. 다차원 컬렉션

---

컬렉션 안의 컬렉션.

즉, 컬렉션 안의 요소로 같은 컬렉션 혹은 다른 요소를 포함시키는 것을 말한다.

보통 컬렉션은 1차원으로 표현된다. 즉, Row 하나에 대응될 수 밖에 없다.

만약 2차원의 Table 같은 구조를 표현하려면 하나의 컬렉션의 원소에 다른 컬렉션을 포함시키는 방식을 사용하여야 한다.

 

3. TABLE에서의 컬렉션 사용

TABLE에 컬렉션도 저장할 수 있다.

3-1) VARRAY를 일반 테이블에 저장하기

---

CREATE OR REPLACE TYPE country_var IS VARRAY(7) OF VARCHAR2(30);

VARRAY 타입으로 사용자 정의 타입을 선언하고 테이블을 생성한다.

CREATE TABLE test_continent( 
                  continent VARCHAR2(50), 
                  country_nm country_var);

테이블의 컬럼으로 아까 선언한 country_var 타입을 선언한다.

익명 블록을 선언하고 데이터를 insert 해 보자.

 
DECLARE
 
BEGIN
 
    INSERT INTO test_continent VALUES ('Asia', country_var('한국', '일본', '중국', '베트남', '방글라데시'));
    INSERT INTO test_continent VALUES ('Europe', country_var('프랑스', '영국', '독일', '오스트리아', '스페인'));
    INSERT INTO test_continent VALUES ('North America', country_var('미국', '캐나다'));
    INSERT INTO test_continent VALUES ('South America', country_var('페루', '칠레', '브라질'));
 
COMMIT;
 
END;

SELECT * FROM test_continent;

Select 한 결과는 아래와 같다.

타입명(원소1, 원소2..) 이런식으로 출력이 된다.

DECLARE
    va_continent country_var;
BEGIN
    SELECT country_nm INTO va_continent
    FROM test_continent 
    WHERE CONTINENT = 'Asia';
       FOR i IN va_continent.FIRST .. va_continent.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE(va_continent(i));
    END LOOP;
END;

Asia에 해당하는 VARRAY를 뽑아와서 하나씩 뽑은 결과이다.

PL/SQL 프로시저가 성공적으로 완료되었습니다.

한국

일본

중국

베트남

방글라데시

** TABLE 함수

TABLE( Collection이 저장된 컬럼명 ) 

혹은

TABLE( Collection을 반환하는 서브쿼리 )

으로 사용하면 블록을 사용하지 않더라도 일반적인 SQL문 안에서 컬렉션으로 저장된 정보를 출력할 수 있다.

컬렉션을 실제 테이블 처럼 다룰 수 있게 해 주는 함수이다.

 SELECT *
    FROM TABLE ( SELECT t.country_nm FROM test_continent t WHERE t.continent = 'Asia') ;

 SELECT continent, b.* FROM test_continent a, TABLE( a.country_nm ) b
 WHERE continent  = 'Asia';

위에서 COLUMN_VALUE 라는 값으로 컬렉션 안에 있는 값들이 출력되는데 이것은 TABLE 함수를 사용하여 Row로 변환된 컬렉션 타입의 컬럼값을 나타내는 키워드이다.

3-2) 중첩 테이블을 일반 테이블에 저장하기

---

중첩 테이블 데이터 타입을 일반 테이블에 저장하려고 할 때, VARRAY와는 약간 다른 방식을 사용한다.

CREATE TABLE 테이블명(

...

중첩 테이블 컬럼명 중첩 테이블 타입,

....)

NESTED TABLE 중첩 테이블 컬럼명 STORE AS 저장공간명;

CREATE OR REPLACE TYPE country_nt IS TABLE OF VARCHAR2(30);
 
CREATE TABLE test_continent_nested( 
                  continent VARCHAR2(50), 
                  country_nt country_nt)
NESTED TABLE country_nt STORE AS country_nt_sp;
 

중첩 테이블 타입을 선언하고 테이블을 만들었으니 데이터를 삽입하자

DECLARE
BEGIN
    INSERT INTO test_continent_nested VALUES ('Asia', country_nt('한국', '일본', '중국', '베트남', '방글라데시'));
    INSERT INTO test_continent_nested VALUES ('Europe', country_nt('프랑스', '영국', '독일', '오스트리아', '스페인'));
    INSERT INTO test_continent_nested VALUES ('North America', country_nt('미국', '캐나다'));
    INSERT INTO test_continent_nested VALUES ('South America', country_nt('페루', '칠레', '브라질'));
 
COMMIT;
 
END;

테이블 함수를 통해 데이터를 확인하면 아까 확인했던 것과 동일한 결과가 나타난다.

  SELECT *
    FROM TABLE ( SELECT t.country_nt FROM test_continent_nested t WHERE t.continent = 'Asia');
    

** 중첩 테이블을 통해 DML 사용하기

---

중첩 테이블은 컬렉션 형태로 들어간 데이터를 로우 단위로 처리하는 것이 아닌, 컬렉션 안에 있는 원소단위로 CRUD 작업을 할 수 있도록 지원한다. (이것은 중첩 테이블만의 기능임)

위의 예제를 계속 이어서 해 보자.

1. INSERT문

아시아 항목에 몽골 이라는 데이터를 추가 하려고 한다.

그럼 일반적으로 데이터를 삽입하는 것 처럼, INSERT문을 사용하면 쉽게 처리할 수 있다. 

INSERT INTO TABLE ( SELECT t.country_nt FROM test_continent_nested t WHERE t.continent = 'Asia') VALUES ('몽골');
COMMIT; 

2. UPDATE

    UPDATE TABLE ( SELECT f.country_nt FROM test_continent_nested f WHERE f.continent = 'Asia')t
            SET VALUE(t) = '싱가포르'
            WHERE t.COLUMN_VALUE = '몽골';
    COMMIT;

몽골을 싱가포르로 바꾼다.

3. DELETE

DELETE FROM TABLE ( SELECT f.country_nt FROM test_continent_nested f WHERE f.continent = 'Asia')t
WHERE t.COLUMN_VALUE = '싱가포르'; 
COMMIT;

싱가포르를 삭제한다.

위에서 보듯이 컬럼은 COLUMN_VALUE으로 접근할 수 있으며, 서브쿼리를 통해 특정 컬럼의 값에 해당하는 컬렉션을 가져와 처리할 수 있다.

이처럼 컬렉션을 사용하면 더 효율적이고 다양한 방식으로 데이터를 처리할 수 있으니 꼭 잘 숙지하자.