Data Transmission Service:NESTEDテーブル

背景情報

Oracleネストテーブルの詳細な機能については、「 http://www.orafaq.com/wiki/NESTED_TABLE 」をご参照ください。

NESTED TABLEは、複数値属性を含む列をサポートするために使用されるOracleデータ型です。 この例では、列はサブテーブル全体を収容することができる。

CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);  

CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)  
       NESTED TABLE col1 STORE AS col1_tab;

INSERT INTO nested_table VALUES (1, my_tab_t('A'));  
INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));  
INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));  
COMMIT;

SQL> SELECT * FROM nested_table;  
        ID COL1  
---------- ------------------------  
         1 MY_TAB_T('A')  
         2 MY_TAB_T('B', 'C')  
         3 MY_TAB_T('D', 'E', 'F')

SQL> SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.col1) t2;  
        ID COLUMN_VALUE  
---------- ------------------------  
         1 A  
         2 B  
         2 C  
         3 D  
         3 E  
         3 F  
6 rows selected.

PostgreSQLネストテーブルとの互換性

PostgreSQLは、同じシナリオ要件を満たすために配列と複合型を使用します。

1. 複合タイプを作成します。

   postgres=# create type thisisnesttable1 as (c1 int, c2 int, c3 text, c4 timestamp);  
   CREATE TYPE  
     
   or
   create table nesttablename (...) ; -- Implicitly create a composite type.

   説明

   この型がシステムで既に作成されている場合、または使用するTABLEが既に作成されている場合は、再度作成する必要はありません。

2. ネストされたテーブルを作成します。

   postgres=# create table hello (id int, info text, nst thisisnesttable1[]);  
   CREATE TABLE

   説明

   helloテーブルの入れ子テーブルとしてのthisisnesttable 1

3. データを挿入します。

   postgres=# insert into hello values (1,'test',array['(1,2,"abcde","2018-01-01 12:00:00")'::thisisnesttable1,  '(2,3,"abcde123","2018-01-01 12:00:00")'::thisisnesttable1]);  
   INSERT 0 1  
     
   Or use the row construction method
   insert into hello values (
     1,
     'test', 
     (array
       [
         row(1,2,'hello',now()),  
         row(1,3,'hello',now())
       ]
     )::thisisnesttable1[]
   );

   説明

   複数の行は配列として格納され、ネストされたテーブルの最大制限は1GB (PostgreSQLのさまざまな種類のストレージの上限) です。

   詳しくは、「 https://www.postgresql.org/docs/11/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS 」をご参照ください。

4. クエリ

   postgres=# select * from hello ;  
    id | info |                                       nst                                          
   ----+------+----------------------------------------------------------------------------------  
     1 | test | {"(1,2,abcde,\"2018-01-01 12:00:00\")","(2,3,abcde123,\"2018-01-01 12:00:00\")"}  
   (1 row)

5. unnestを使用して、ネストされたテーブルのコンテンツをアンネストできます。

   postgres=# select id,info,(unnest(nst)).* from hello ;  
    id | info | c1 | c2 |    c3    |         c4            
   ----+------+----+----+----------+---------------------  
     1 | test |  1 |  2 | abcde    | 2018-01-01 12:00:00  
     1 | test |  2 |  3 | abcde123 | 2018-01-01 12:00:00  
   (2 rows)  
     
   postgres=# select id,info,(unnest(nst)).c1 from hello ;  
    id | info | c1   
   ----+------+----  
     1 | test |  1  
     1 | test |  2  
   (2 rows)