产生集合派生表的 Row 类型

如果没有指定派生列名，那么数据库服务器的行为取决于基本集合元素的数据类型。

虽然集合派生表看起来包含单独的数据类型的列，但这些列实际上是 ROW 数据类型的字段。ROW 类型的数据类型和列名取决于几个元素。

如果基础集合表达式元素的数据类型是 type，那么数据库服务器通过以下规则来确定集合派生表的 ROW 类型：
下表给出的引申示例举例说明了这些规则。该表使用以下模式为例：
CREATE ROW TYPE person (name CHAR(255), id INT);
        CREATE TABLE parents
        (
        name CHAR(255), 
        id INT,
        children LIST (person NOT NULL)
        );
        CREATE TABLE parents2
        (
        name CHAR(255), 
        id INT,
        children_ids LIST (INT NOT NULL)
        );
ROW 类型 显式派生列列表 集合派生表产生的 ROW 类型 代码示例
是 否 Type

SELECT (SELECT c_table.name FROM TABLE(parents.children) c_table WHERE c_table.id = 1002) FROM parents WHERE parents.id = 1001;

在此示例中，c_table 的 ROW 类型是 parents。

是 是 未命名的 ROW 类型，它的列类型是 Type ，而列名是派生列的列表中的名称

SELECT (SELECT c_table.c_name FROM TABLE(parents.children) c_table(c._name, c_id) WHERE c_table.c_id = 1002) FROM parents WHERE parents.id = 1001;

在此示例中，c_table 的 ROW 类型是 ROW(c_name CHAR(255), c_id INT)。

No No 未命名的 ROW ，它包含一个已指定依实现而定的名称的 Type 列 在以下示例中，如果不指定 c_id，数据库服务器会为派生列指定一个名称。在这种情况下，表 c_table 的 ROW 类型是 ROW(server_defined_name INT)。
否 是 未命名的 ROW 类型，它包含一个 Type 列。列名在派生列的列表中

SELECT(SELECT c_table.c_id FROM TABLE(parents2.child_ids) c_table (c_id) WHERE c_table.c_id = 1002) FROM parents WHERE parents.id = 1001;

这里，c_table 的 ROW 类型是 ROW(c_id INT)。

下面的程序分段用返回单个值的 SPL 函数创建了一个集合派生表：
CREATE TABLE wanted(person_id int);
        CREATE FUNCTION 
        wanted_person_count (person_set SET(person NOT NULL))
        RETURNS INT;
        RETURN( SELECT COUNT (*)
        FROM TABLE (person_set) c_table, wanted
        WHERE c_tabel.id = wanted.person_id);
        END FUNCTION;
下面的程序段给出了用返回多个值的 SPL 函数创建一个集合派生表的更通用的示例：
-- Table of categories and child categories, 
        -- allowing any number of levels of subcategories
        CREATE TABLE CategoryChild (
        categoryId      INTEGER,
        childCategoryId SMALLINT
        );
        
        INSERT INTO CategoryChild VALUES (1, 2);
        INSERT INTO CategoryChild VALUES (1, 3);
        INSERT INTO CategoryChild VALUES (1, 4);
        INSERT INTO CategoryChild VALUES (2, 5);
        INSERT INTO CategoryChild VALUES (2, 6);
        INSERT INTO CategoryChild VALUES (5, 7);
        INSERT INTO CategoryChild VALUES (7, 8);
        INSERT INTO CategoryChild VALUES (7, 9);
        INSERT INTO CategoryChild VALUES (4, 10);
        
        -- "R" == ROW type
        CREATE ROW TYPE categoryLevelR (
        categoryId      INTEGER,
        level   SMALLINT );
        
        -- DROP FUNCTION categoryDescendants ( 
        --               INTEGER, SMALLINT );
        CREATE FUNCTION categoryDescendants (
        pCategoryId INTEGER,
        pLevel      SMALLINT DEFAULT 0 )
        RETURNS MULTISET (categoryLevelR NOT NULL)
        
        -- "p" == Prefix for Parameter names
        -- "l" == Prefix for Local variable names
        DEFINE lCategoryId LIKE CategoryChild.categoryId;
        DEFINE lRetSet MULTISET (categoryLevelR NOT NULL);
        DEFINE lCatRow categoryLevelR;
        
        -- TRACE ON;
        -- Must initialize collection before inserting rows
        LET lRetSet = 'MULTISET{}' :: MULTISET (categoryLevelR NOT NULL);
FOREACH
          SELECT childCategoryId INTO lCategoryId 
          FROM CategoryChild WHERE categoryId = pCategoryId;
          INSERT INTO TABLE (lRetSet)
          VALUES (ROW (lCategoryId, pLevel+1)::categoryLevelR);
          
          -- INSERT INTO TABLE (lRetSet);
          -- EXECUTE FUNCTION categoryDescendantsR ( lCategoryId,      
          -- pLevel+1 );
          -- Need to iterate over results and insert into SET.
          -- See the SQL Tutorial, pg. 10-52:
          -- "Tip: You can only insert one value at a time 
          -- into a simple collection."
          FOREACH
          EXECUTE FUNCTION categoryDescendantsR ( lCategoryId, pLevel+1 )
          INTO lCatRow;
          INSERT INTO TABLE (lRetSet)
          VALUES (lCatRow);
          END FOREACH;
          END FOREACH;
          
          RETURN lRetSet;
          END FUNCTION
          ;
          -- "R" == recursive
          -- DROP FUNCTION categoryDescendantsR (INTEGER, SMALLINT);
          CREATE FUNCTION categoryDescendantsR (
          pCategoryId INTEGER,
          pLevel      SMALLINT DEFAULT 0
          )
          RETURNS categoryLevelR; 
          DEFINE lCategoryId      LIKE CategoryChild.categoryId;
          DEFINE lCatRow          categoryLevelR;
          
          FOREACH   
          SELECT  childCategoryId
          INTO    lCategoryId
          FROM    CategoryChild
          WHERE   categoryId = pCategoryId
          RETURN ROW (lCategoryId, pLevel+1)::categoryLevelR WITH RESUME;
          
          FOREACH
          EXECUTE FUNCTION categoryDescendantsR ( lCategoryId, pLevel+1 )
          INTO    lCatRow
          RETURN  lCatRow WITH RESUME;
          END FOREACH;
          END FOREACH; 
          END FUNCTION;
          
          -- Test the functions:
          SELECT lev, col
          FROM    TABLE ((
          categoryDescendants (1, 0)
          )) AS CD (col, lev);
父主题： 集合派生表