如何在 DB2 中使用嵌套结构类型（UDT）？

Question

我尝试使用 DB2 来使用嵌套结构类型 (UDT)，但遇到了一些问题。

以下是用于为用例创建类型、表、函数和转换的 SQL 语句。 每个语句都执行得很好，但是在尝试执行简单的操作时会发生错误 select * from t_author：

CREATE TYPE u_street_type AS (
  street VARCHAR(100),
  no VARCHAR(30)
) INSTANTIABLE MODE DB2SQL;

CREATE TYPE u_address_type AS (
  street u_street_type,
  zip VARCHAR(50),
  city VARCHAR(50),
  country VARCHAR(50),
  since DATE,
  code INT
) INSTANTIABLE MODE DB2SQL;

CREATE TABLE t_author (
  ID INT NOT NULL PRIMARY KEY,
  FIRST_NAME VARCHAR(50),
  LAST_NAME VARCHAR(50) NOT NULL,
  DATE_OF_BIRTH DATE NOT NULL,
  YEAR_OF_BIRTH INT,
  ADDRESS u_address_type
);

CREATE FUNCTION f_u_street_type_transform (street u_street_type) 
  RETURNS ROW (
    street VARCHAR(100), 
    no VARCHAR(30)
  )
  LANGUAGE SQL
  RETURN VALUES (
    street..street, 
    street..no
  );

CREATE TRANSFORM FOR u_street_type db2_program 
  (FROM SQL WITH FUNCTION f_u_street_type_transform);

CREATE FUNCTION f_u_address_type_transform (address u_address_type)
  RETURNS ROW (
    street VARCHAR(100),
    no VARCHAR(30),
    zip VARCHAR(50),
    city VARCHAR(50),
    country VARCHAR(50),
    since DATE,
    code INT
  )
  LANGUAGE SQL
  CONTAINS SQL
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN VALUES (
    address..street..street,
    address..street..no,
    address..zip,
    address..city,
    address..country,
    address..since,
    address..code
  );

CREATE TRANSFORM FOR u_address_type db2_program 
  (FROM SQL WITH FUNCTION f_u_address_type_transform);

当我尝试执行 select * from t_author; 时，会发生以下错误：

The function "F_U_ADDRESS_TYPE_TRANSFORM" resolved to specific function 
"SQL101230131003100" that is not valid in the context where it is used.. 
SQLCODE=-390, SQLSTATE=42887, DRIVER=3.57.82

您知道我做错了什么吗？

我正在使用 DB2 v9.5 (Linux)。

Answer 1

问题是 ADDRESS 列中的每个值都是标量值。当您因为有像“SELECT * FROM t_author”这样的查询而想要将结构化类型的值绑定到客户端应用程序（如 DB2 CLP）时，您必须使用标量转换函数，它将结构化值转换为VARCHAR、CLOB 或任何您需要的类型的单个值。您无法通过转换函数将其扩展为多个值，因为这必须类似于多个列。 模式；更不用说如果您将完全相同的查询用作具有另一种语义的子查询，则会出现问题。）

（这是不可能的，因为不同的转换函数可能返回不同数量的值，从而为查询提供完全不同的 返回具有超过 1 列的 ROW() 的函数只能在与外部 UDF（用 C/C++ 或 Java 编写）交换结构化类型的值时使用。

ps：我的建议是您使用常规的、规范化的关系数据库设计，并尽量避免结构化类型，除非您有充分的理由这样做。

Answer 2

从 DB2 的角度来看，jOOQ 只是一个数据库应用程序。因此，数据库应用程序的所有注意事项也与其相关。这意味着，jOOQ 还需要使用以标量函数作为变换函数的变换组。据我所知，没有内置支持。

您可以做的是：

• 创建一个外部函数，将单独的参数作为输入并将它们连接成 BLOB；然后，jOOQ 会将 BLOB 拆开并构造相应的 Java 对象
• ，将结构化类型转换为 XML 文档，然后 jOOQ 将解析 XML 文档并构造 Java 对象

简而言之：您必须注意保留值中的结构信息您自己在 DB2 和应用程序之间传输。您的应用程序本身也必须负责相应地解释数据。因此，这对您来说都是手动任务，例如以某种方式生成特定于类型的代码。

不幸的是，关系数据库系统中的面向对象功能总体上还没有发展到真正易于无缝使用的程度。由于它不易使用，因此只有极少数应用程序使用结构化类型。而且由于用户不多，该领域的改进不会得到高度重视。

Answer 3

感谢克努特提供的信息丰富的答案！

我知道我必须使用标量变换函数来转换结构化值
使用客户端应用程序时转换为单个值（即 select * from t_author）。
我通过创建一个转换函数来管理此问题，该函数将结构化值元素连接成单个 varchar 值。

这解决了从客户端应用程序检索数据的问题，但真正的原因是
让它发挥作用是在尝试支持数据库接口库jOOQ中的结构化类型时。
通过这个库，表、列、存储过程、函数、结构化类型等都被建模为生成的 Java 类。

我正在考虑使用 java.sql.Struct 从 java.sql.ResultSet 中的结构化值列检索数据，以用值填充生成的 Java 类。

当我使用转换函数将结构化类型转换为 varchar 值时，我设法使用 java.sql.Struct 检索数据，但这不是我想要的。我想要“直接”访问结构化类型的各个元素。

关于如何实现这一点有什么建议吗？