DATOR

Oracle Nested Table

Document URL : http://www.dator.co.kr/354544
Oracle DB | Posted on September 02nd, 2012 at 18:55 by 밀오 | 조회수 : 16087

1. 서문

 

개발을 진행 하면서, 이런저런 쿼리를 만들 때 답답했던 부분 중의 하나는,

 

일반적인 프로그래밍에 비해서,

내가 원하는 데이터를 뽑아내기 위해, 생각보다 먼길을 돌아가야 한다는 점이었습니다.

 

DBMS 에서도 이런 점들을 보완하기 위해,

여러가지 기법과 Data Type들을 제공하고 있는데요,

 

그 중에 Oracle의 Nested Table에 대해서 정리해 보겠습니다. 

 

2. Nested Table 이란?

 

오라클 Document는 다음과 같이 설명하고 있습니다.

 

원문: http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#autoId35

 

Nested Table Type은, 정렬되지 않은 데이터들의 집합을 나타내는 Type으로,

해당 데이터에는 Oracle 내장 Type이나 유저 지정 Type의 데이터가 올 수 있다.

 

Nested Table은

컬럼 1개 짜리의 테이블 또는,

(Object Type을 이용할 경우) 여러 컬럼을 가진 테이블 이라고 볼 수 있다.

 

Nested Table을 정의한다고 해서 디스크 공간을 차지하는 것은 아니고,

단지, 아래 리스트에서 사용할 수 있도록, 데이터 Type을 제공해주는 역할을 한다.

  • 테이블 컬럼의 data type

  • Object Type의 속성

  • PL/SQL '변수' 나 '파라미터' 또는 '함수' 리턴값

  •  

    Nested Table Type이 테이블의 컬럼이나, Object 테이블의 속성에 지정될 경우,

    Oracle에선 모든 하위 Nested Table의 데이터를 상위테이블 하나로 저장한다.                 

     

    다른 말로 풀어보자면,

     

    Oracle의 Table을 Type으로 지정한 것으로 볼 수 있습니다.

    그리고, 이 Table은 1개의 Type을 담을 수 있으며, 이 Type에는 Nested Table 또한 포함됩니다.

    때문에, Nested Table이라고 이름 붙여진 것이겠죠.

     

    좀더 붙이자면,

    Nested Table은 Oracle의 User Defined Type에 속하며

    User Defined Type은 현재(11gR2) Object Type,  REF Data Type,  Varry,  Nested Table 이 있고,

    각각 CREATE TYPE 문을 이용해서 생성합니다.

     

    관계

    User Defined Type

     └Object Type

     └  REF Data Type

     └  Varry

     └  Nested Type

          Nested Type 은 다음을 포함

            └ Oracle Built-In(내장) Type

            └ User Defined Type

               └  (재귀 루프)

     

    3. 왜? 언제? Nested Table을 사용하는가?

     

    --쿼리 작성/관리의 편의를 위해 사용할 수 있습니다.

    예를 들어,

    로그인 로그데이터가 있을 때, 각 로그에서 특정 ID는 언제 로그인 했는지 찾으려고 합니다.

    이 기능을 사용하는 쿼리가 여기저기 많이 있어서,

    매번 작성하기 귀찮으니 따로 PL/SQL 함수로 구현하려고 합니다.

    함수의 입력은 사용자ID 출력은 로그ID입니다.

     

    그런데, 쿼리문에서 해당 함수를 호출하면, 한번에 여러개의 결과값이 나오게 됩니다.

    함수의 출력값이 1개가 아니라면 어떻게 해야 쿼리에 이용할 수 있을까요?

     

    이런 때를 위해 PL/SQL에서 만들어놓은 Collection 이라는 개념이 있습니다.

     

    사용법은

    1. Collection Type을 미리 만들어 두고,

    2. 함수의 출력을 해당 Collection Type으로 담은 다음에,

    3. FROM 절에 TABLE( ...) 로 감싸면,

    일반 Table 처럼 이용할 수 있습니다.

     

    -- 스칼라 서브 쿼리의 결과값으로 Multi Row, Multi Column을 지정할 수 있습니다.

    위의 예와 유사한 경우로 함수대신 스칼라 서브쿼리를 이용하는 경우입니다.

     

    스칼라 서브 쿼리의 결과값은 말 그대로 스칼라(단일 값) 이지만,

    Nested Table Type을 이용하면, 테이블이 하나의 단위가 됩니다.

    즉 테이블 전체가 단일 스칼라로 간주되어, 이를 스칼라 서브 쿼리의 결과로 이용할 수 있습니다.

     

    함수대신, 서브쿼리를 아래의

    CAST (MULTISET (subquery) AS my_nested_table_type)

    문장에 집어넣고 이용하면 됩니다.

     

    이 경우,

    함수만큼은 아니지만 어느정도의 편의성과,

    함수로는 얻을 수 없는 성능상의 이점을 얻을 수 있습니다.

     

    -- 서브 쿼리 결과를 Table로 지정할 수 있습니다.

        CAST (MULTISET (subquery) AS my_nested_table_type)

       을 사용하면,  서브 쿼리가 nested table type으로 변환됩니다.

     

        subquery Factoring clause (WITH 절)와 마찬가지 결과이나, 좀 더 유연하게 사용할 수 있습니다.

     

    -- Object Type으로 Table을 만들 수도 있습니다.

    이렇게 하면, 거의 개발언어의 Class와 유사하게 사용할 수 있습니다.

    Table대신 Object 기반의 DB를 구축할 때의 경우입니다.

    아직은 사용 예를 본적이 없어서, 자세한건 나중으로 미루겠습니다.

     

    4. Nested Table과 다른 Collection 들과의 차이점

     

    -- PL/SQL Collection 종류

    PL/SQL에서 지원하는 Collection 에는 Nested Table말고도.

    Associative array, Varry이 더 있습니다.

     

    자세한 건  이 링크를 참고해 주시고,  

    Using PL/SQL Collections and Records

     - http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#BABFACFA

     

    여기서는 간단히만 정리해보겠습니다.

     

    Varry 는 개발언어의 배열과 유사하고,

    Nested Table은 개발언어의 List와 유사합니다.

    (Oracle Doc에는 Set과 Bag을 유사한 것으로 들고 있으나, 제가 이 두 가지는 모르는 관계로 ^^;)

    Associative array 는 개발언어의 Map (또는 Hash Table,  LookUp Table) 등과 유사합니다.

     

    -- 중요한 차이점은

    Varry는 정의할 때 배열의 크기를 지정해야 합니다.

    때문에, 만들 당시에 최대 크기를 알아야 한다는 단점이 있고,

    대신 공간의 낭비없이 효율적으로 저장 됩니다.

     

    Associative array 는 최대 크기를 지정하지 않아도 되고, 검색에도 유리하나

    위에서 Varry와 Nested Table, 이 두 가지만 SQL의 컬럼으로 사용할 수 있습니다.

    때문에 함수의 입출력으로 사용하려면 Associative array 는 부적합합니다.

     

    Nested Table에는 지정된 Index가 없기 때문에,

    특정값 검색에는 비효율 적입니다.

    중간에 데이터가 삭제될 경우, 해당 공간이 낭비되기도 합니다.

     

    -- 정리하면

    Nested Table은,

    SQL 함수의 입출력으로 Collection을 사용할 때에,

    그리고 담겨질 데이터의 갯수를 모르거나 가변적일 때에,

    다른 Collection으로는 대체 불가능한 Type입니다.

     

    Varry는 다음과 같이 정의합니다.

    DECLARE
    TYPE varray_type IS VARRAY(5) OF INTEGER;


     

    Nested Table은 다음과 같이 정의합니다.

    DECLARE
    TYPE nested_type IS TABLE OF VARCHAR2(30);

     

    Associative array는 위 Nested Table에 INDEX BY가 추가된 형태입니다.

    DECLARE

    TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

     

    --Collection Type은 아니나, Cursor Type으로도 Nested Table과 유사한 기능을 수행할 수 있습니다.

    Cursor Type은 SQL 의 결과를 가리키는 Pointer와 유사합니다.

    다만 SQL의 결과값(Result set)이 연결(binding) 되어 있는 상태로,

    관리상 좀더 주의해야 할 부분이 있습니다.

     

    Nested Table의 TABLE(table_type)과 마찬가지로,

    Cursor 또한  CURSOR( subquery )  를 이용해서,

    함수의 출력값을 쿼리로 뽑아올 수 있습니다.

     

    Cursor에 대해서는 다음에 다시 정리해 보겠습니다.

     

    참고1: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i45288

    참고2: http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions005.htm#i1035107

     

    5. 사용 예시

     

    -- 우선 사용하고자 하는 데이터를 Type 으로 등록합니다.

    이 Type은 PL/SQL뿐만 아니라 Oracle SQL 에서도 사용할 수 있는 Type  입니다.

     

    CREATE TYPE table_number_typ
       AS TABLE OF NUMBER;

    /

     

    -- 해당 타입을 함수의 리턴값으로 지정합니다.

    CREATE FUNCTION fs_test_nstable(...blar blar..) RETURN table_number_typ IS

    BEGIN

    ....

    END;

    /

     

    또는 서브쿼리 결과를 CAST MULTISET 의 타입으로도 지정할 수 있습니다.

    SELECT

    CAST (MULTISET (subquery) AS table_number_typ) as temp_table

    FROM DUAL;

     

    -- 리턴값 또는 CAST MULTISET의 결과 column을 TABLE(...)로 감싸서,

    FROM 절의 재료 테이블로 넣어줍니다.

     

    SELECT *

    FROM TABLE(fs_test_nstable((...blar blar..)));

     

    또는,

     

    SELECT *

    FROM TABLE(

      SELECT

      CAST (MULTISET (subquery) AS table_number_typ) as temp_table

      FROM DUAL;

    );

     

    이런 식으로 응용하면,

     입출력 값이 테이블인 함수도 만들 수 있고,

    서브쿼리 또한 좀더 유연하게 사용할 수 있습니다.

     

    6. 부록

    TABLE() 함수

    http://docs.oracle.com/cd/B19306_01/server.102/b14220/sqlplsql.htm

     

    Table functions are functions

    that produce a collection of rows, a nested table or a varray,

    that can be queried like a physical database table or assigned to a PL/SQL collection variable.

     

    You can use a table function

    like the name of a database table, in the FROM clause of a query,

    or like a column name in the SELECT list of a query.

     

    A table function can take a collection of rows as input.

    An input collection parameter can be either a collection type,

    such as a VARRAY or a PL/SQL table, or a REF CURSOR.

     

    Use PIPELINED

    to instruct Oracle to return the results of a table function iteratively.

     

    A table function returns a nested table or varray collection type.

    You query table functions

    by using the TABLE keyword before the function name in the FROM clause of the query.

     

    CAST ( ) 함수

    http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm#i1269136

     

    Syntax

    Description of cast.gif follows

     

    Tagged :
       

    Comments : 0