《Oracle PL/SQL开发指南》学习笔记29——源码调试——集合（第四部分——非对称复合表集合（Asymmetrical Composite Table Collections））

非对称复合表集合（Asymmetrical Composite Table Collections），是本人学习编程以来，遇到的最复杂的数据结构之一。

暂且不管其实用性如何，从理论学习的角度而言，了解一下也不错。

以下示例和电影《指环王》中的人种有关，所以，看过电影的朋友可能更容易理解这段示例代码。

“As qualified earlier, an asymmetrical composite  varray or table collection holds scalar and collection fields. The former has one row and the latter  has one to many rows.  Leveraging our examples from the prior section, let’s add a prominent_table collection  type that has a base type of prominent_object composite types. After all, the Lord of the Rings  trilogy has more than one prominent dwarf, elf, or man, as well as a few prominent women (though  unfortunately no female dwarves, females belong to their respective race of dwarves, elves, and men).”

SQL> DROP TYPE people_table FORCE;

类型已删除。

SQL> DROP TYPE people_object FORCE;

类型已删除。

SQL> DROP TYPE prominent_table FORCE;

类型已删除。

SQL> DROP TYPE prominent_object FORCE;

类型已删除。

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 prominent_table IS TABLE OF prominent_object;
  3
  4  /

类型已创建。

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

类型已创建。

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

类型已创建。

SQL>
SQL> DECLARE
  2    /* Declare a table collection. */
  3    lv_tolkien  PEOPLE_TABLE :=
  4                   people_table(
  5                       people_object(
  6                          'Men'
  7                         , prominent_table(
  8                               prominent_object('Aragorn','3rd Age')
  9                             , prominent_object('Boromir','3rd Age')
 10                             , prominent_object('Faramir','3rd Age')
 11                             , prominent_object('Eowyn','3rd Age')))
 12                     , people_object(
 13                          'Elf'
 14                         , prominent_table(
 15                               prominent_object('Legolas','3rd Age')
 16                             , prominent_object('Arwen','3rd Age'))));
 17  BEGIN
 18    /* Add a new record to collection. */
 19    lv_tolkien.EXTEND;
 20    lv_tolkien(lv_tolkien.COUNT) :=
 21       people_object('Drawf'
 22                    , prominent_table(
 23                          prominent_object('Gimili','3rd Age')
 24                        , prominent_object('Gloin','3rd Age')));
 25
 26    /* Read and print values in table collection. */
 27    FOR i IN lv_tolkien.FIRST..lv_tolkien.LAST LOOP
 28      FOR j IN lv_tolkien(i).exemplar.FIRST..lv_tolkien(i).exemplar.LAST LOOP
 29        dbms_output.put_line(
 30          lv_tolkien(i).race||': '||lv_tolkien(i).exemplar(j).name);
 31      END LOOP;
 32    END LOOP;
 33  END;
 34  /

PL/SQL 过程已成功完成。

SQL> set serveroutput on
SQL> /
Men: Aragorn
Men: Boromir
Men: Faramir
Men: Eowyn
Elf: Legolas
Elf: Arwen
Drawf: Gimili
Drawf: Gloin

PL/SQL 过程已成功完成。