《Oracle PL/SQL开发指南》学习笔记29——源码调试——集合（第三部分——复合表集合（Table Collection））

复合表集合（不可否认，这章的代码调试起来真费力气！）

有两种类型的复合集合，一种是对象类型的集合，另一种是嵌套集合的对象类型的集合。包含其他集合的集合称为多层集合。

1. 对称的复合表集合

SQL> CREATE OR REPLACE
  2    TYPE prominent_object IS OBJECT
  3    ( name     VARCHAR2(20)
  4    , age      VARCHAR2(10));
  5  /

类型已创建。

SQL> CREATE OR REPLACE
  2    TYPE people_object IS OBJECT
  3    ( race      VARCHAR2(10)
  4    , exemplar  PROMINENT_OBJECT);
  5  /

类型已创建。

SQL> CREATE OR REPLACE
  2    TYPE people_table IS TABLE OF people_object;
  3  /

类型已创建。

SQL> COLUMN EXEMPLAR FORMAT A40
SQL> SELECT  *
  2  FROM    TABLE(
  3            SELECT CAST(COLLECT(
  4                people_object(
  5                   'Men'
  6                  , prominent_object('Aragorn','3rd Age')
  7                )
  8              ) AS people_table
  9            )
 10            FROM  dual);

RACE       EXEMPLAR(NAME, AGE)
---------- ----------------------------------------
Men        PROMINENT_OBJECT('Aragorn', '3rd Age')

已选择 1 行。

2. 也可使用以下方法

SQL> CREATE OR REPLACE
  2    TYPE prominent_table IS TABLE OF prominent_object;
  3  /

类型已创建。

SQL> COLUMN EXEMPLAR FORMAT A40
SQL> SELECT  o.race, n.name, n.age
  2  FROM    TABLE(
  3            SELECT CAST(COLLECT(
  4                people_object(
  5                   'Men'
  6                  , prominent_object('Aragorn','3rd Age')
  7                )
  8              ) AS people_table
  9            )
 10            FROM  dual) o CROSS JOIN
 11          TABLE(
 12            SELECT CAST(COLLECT(exemplar) AS prominent_table)
 13            FROM dual) n;

RACE       NAME                 AGE
---------- -------------------- ----------
Men        Aragorn              3rd Age

已选择 1 行。

SQL> SELECT  *
  2  FROM    TABLE(
  3            people_table(
  4                people_object(
  5                   'Men'
  6                  , prominent_object('Aragorn','3rd Age'))
  7              , people_object(
  8                   'Elf'
  9                  , prominent_object('Legolas','3rd Age'))
 10              )) o CROSS JOIN
 11          TABLE(
 12            SELECT CAST(COLLECT(exemplar) AS prominent_table)
 13            FROM dual) n;

RACE       EXEMPLAR(NAME, AGE)                      NAME
---------- ---------------------------------------- --------------------
AGE
----------
Men        PROMINENT_OBJECT('Aragorn', '3rd Age')   Aragorn
3rd Age

Elf        PROMINENT_OBJECT('Legolas', '3rd Age')   Legolas
3rd Age

SQL> DECLARE
  2    /* Declare a record. */
  3    TYPE tolkien_record IS RECORD
  4    ( race      VARCHAR2(10)
  5    , name      VARCHAR2(20)
  6    , age       VARCHAR2(10));
  7
  8    /* Declare a table of the record. */
  9    TYPE tolkien_plsql_table IS TABLE OF TOLKIEN_RECORD;
 10
 11    /* Declare record and table collection variables. */
 12    lv_tolkien_record       TOLKIEN_RECORD;
 13    lv_tolkien_plsql_table  TOLKIEN_PLSQL_TABLE;
 14
 15    /* Declare a table collection. */
 16    lv_tolkien_table  PEOPLE_TABLE :=
 17                        people_table(
 18                          people_object(
 19                             'Men'
 20                            , prominent_object('Aragorn','3rd Age'))
 21                        , people_object(
 22                             'Elf'
 23                             , prominent_object('Legolas','3rd Age')));
 24  BEGIN
 25
 26    SELECT   o.race, n.name, n.age
 27    INTO     lv_tolkien_record
 28    FROM     TABLE(lv_tolkien_table) o CROSS JOIN
 29             TABLE(
 30               SELECT CAST(COLLECT(exemplar) AS prominent_table)
 31               FROM   dual) n
 32    WHERE    ROWNUM < 2;
 33
 34    dbms_output.put_line(
 35      '['||lv_tolkien_record.race||'] '||
 36      '['||lv_tolkien_record.name||'] '||
 37      '['||lv_tolkien_record.age ||']');
 38  END;
 39  /
[Men] [Aragorn] [3rd Age]

PL/SQL 过程已成功完成。

SQL> desc people_table
 people_table TABLE OF PEOPLE_OBJECT
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 RACE                                               VARCHAR2(10)
 EXEMPLAR                                           PROMINENT_OBJECT