嵌套表的测试（一）

近期在使用代码设计中涉及到了嵌套表的使用，使用过程中发现一次取数据，无法完全显示所有的字段内容。

整个测试过程如下：

建立测试表：

–建立相关type

create type mc$column_obj_type1 is object( column_source varchar2(30), 

                                           column_name   varchar2(30));

create type mc$column_tab_type1 is table of mc$column_obj_type1;

–建立嵌套表

create table mc$etl_reference ( id         number,

                                column_map mc$column_tab_type1) 

nested table column_map store as mc$column_map1;

–插入测试数据

insert into mc$etl_reference values ( 1,

                                      mc$column_tab_type1(mc$column_obj_type1('a','b'),

                                                          mc$column_obj_type1('c','d')));

insert into mc$etl_reference values ( 2,

                                      mc$column_tab_type1(mc$column_obj_type1('e','f'),

                                                          mc$column_obj_type1('g','h')));

insert into mc$etl_reference values ( 3,

                                      mc$column_tab_type1(mc$column_obj_type1('e1','f1'),

                                                          mc$column_obj_type1('g1','h1'),

                                                          mc$column_obj_type1('A1','C1')));

commit;

–显示表格数据

SQL> select * from mc$etl_reference;

可见，一次查询无法显示所需数据。

那么换种思路，直接加入对嵌套表的查询：

SQL> select e.id, c.* from mc$etl_reference e, table(e.column_map) c;

 

        ID COLUMN_SOURCE                  COLUMN_NAME

———- —————————— ——————————

         1 a                              b

         1 c                              d

         2 e                              f

         2 g                              h

         3 e1                             f1

         3 g1                             h1

         3 A1                             C1

 

7 rows selected

检查之后发现数据显示正常。