内容


DB2 基础

结构化数据类型和类型化表简介

Comments

系列内容:

此内容是该系列 # 部分中的第 # 部分: DB2 基础

敬请期待该系列的后续内容。

此内容是该系列的一部分:DB2 基础

敬请期待该系列的后续内容。

IBM® DB2® Universal Database™ (DB2 UDB) for Linux™、UNIX® 和 Windows® 都支持结构化数据类型。结构化数据类型 是一种用户定义的数据类型,它包含了一系列的属性,每一个属性都有一个数据类型。属性 是专门用来帮助描述类型实例的特性。例如,如果我们定义了一个称为 address_t 的结构化类型,那么 city 可能就是这个结构化类型的一个属性。结构化类型可以很轻松就将地址这样的数据用作一个独立单元或单独的数据项,无需将每个项(或属性)存储到单独的列中。

最常见的情况是,结构化类型被存储到数据库中:

  • 对于一个或多个列中的值,可以使用结构化类型作为数据类型对它们进行定义。(参见 清单 1清单 2。)
  • 对于表(类型化表)中的行,其中的列是根据结构化类型的属性定义的。在这种情况下,表是用结构化类型创建的,并且您不必在表定义中指定单独的列。(参见 清单 3。)

结构化数据类型

结构化数据类型可以用作普通表中某一列的类型(清单 1 和清单 2),也可以用作整个表(或视图)的类型,或者用作另一个结构化类型的属性(清单 3)。当用作表的类型时,该表就被认为是一个类型化表(清单 3)。

您可以用同样方式使用结构化类型列创建表,也可以只使用 DB2 内置数据类型创建一个表。在两种情况下,都必须为表中的每一列分配一个数据类型。如果列是结构化类型列,则需要将结构化类型的名称指定为其数据类型(清单 1)。

结构化数据类型展示了一个称为继承 的行为。结构化类型可以包含 子类型,以及重用其所有属性并包含自己的特定属性的其他结构化类型。子类型所继承的属性来自哪个类型,该类型就被认为是这个子类型的 超类型类型层次结构 是基于同一超类型的一组子类型;层次结构中最顶端的超类型被认为是该层次结构的 根类型

可以使用 CREATE TYPE 语句创建一个结构化类型,并使用 DROP 语句删除结构化类型。

当您创建结构化类型时,DB2 为类型自动创建一个构造函数,并为类型的属性创建 mutator 方法和 observer 方法。您可以使用构造函数和 mutator 方法来创建结构化类型的实例,然后将这些实例插入表的列中。

  • 构造函数 和它关联的结构化类型有相同的名称。构造函数没有参数,并且返回的类型实例中的所有属性都为空。
  • mutator 方法 是为某个结构化类型(structured type)的每个属性而存在的。当您在结构化类型的实例上调用一个 mutator 方法并为其相关的属性指定一个新值时,该方法将返回一个新的实例,这个实例的属性已经更新为新值。
  • observer 方法 是为某个结构化类型的每个属性而存在的。当您在结构化类型的实例上调用 observer 方法并为其相关联的属性指定一个新值时,该方法将返回这个实例的一个属性值。

为了在结构化类型的实例上调用 mutator 或 observer 方法,请使用双点(..)运算符(清单 1、清单 2 和清单 3)。

类型化表

类型化表是一个使用用户定义的结构化类型定义的表。类型化表以行的形式存储结构化类型的实例,在这些行中,类型的每个属性都存储在单独的列中;事实上,结构化类型的属性的名称和数据类型变成了类型化表的列的名称和数据类型。与结构化类型类似,类型化表可以是由单独的 根表超表子表 组成的 表层次结构 的一部分。

可以使用 CREATE TABLE 语句创建类型化表,并使用 DROP 语句删除类型化表。正被删除的类型化表不能拥有任何子类型。通过在 DROP 语句(DROP TABLE HIERARCHY <root-table-name>)中指定 HIERARCHY 关键词,还可以删除整个表层次结构。

一个详细的例子

在该例中,我们将使用 DB2 UDB 附带的 SAMPLE 数据库。例子只要求一个数据库连接来启动操作,不要求其他任何东西,对此,SAMPLE 数据库将表现得非常出色。

我们将创建一个有 4 个列的名为 CLIENTS 的表。CLIENT_ID 是系统生成的标识列;CLIENT_LNAME 和 CLIENT_FNAME 分别包含客户的名和姓,并且 ADDRESS 是类型 Address_t 的结构化类型列。(一个有益的习惯是,使用 '_t'后缀来命名结构化类型,使它们的结构化类型特征变得更明显。)在试图创建 CLIENTS 表(清单 1)之前,需要创建这个结构化类型。我们将使用 CREATE TYPE 语句的最简单形式,只指定 4 个属性(streetcityprovincepostal_code)以及必需的 MODE DB2_SQL 子句。

为了从表中检索结构化类型数据,必须有一些将类型转换成一个标量值,换句话说,这些值的类型必须基于某种内置的 DB2 数据类型。为了完成这个转换,首先必须创建一个 FROM SQL 转换函数,然后用 转换组 将转换函数联系起来。

首先,我们将使用 CREATE FUNCTIONSQL ScalarTableRow)语句,创建一个名为 ADDRESS_TRANSFORM 的标量转换函数。在此例中,我们指定了一个 Address_t 类型的名为addr 的输入参数。并指定该函数返回一个足以保留连接的地址属性的 VARCHAR(42)。SQL-function-body 由 RETURN 语句组成,在该语句中,结构化类型 Address_t 的实例的地址属性通过 observer 方法(被'..' 运算符指定的)进行检索并连接('||'运算符)到单一的字符串来形成一个邮寄地址。

在使用这个转换函数之前,我们必须使用 CREATE TRANSFORM 语句来联合带有组名和类型的 ADDRESS_TRANSFORM 转换函数。CREATE TRANSFORM 允许将现有的函数用作转换函数。如果在运行一个引用结构化类型的应用程序时,没有指定组名,那么 DB2 就会尝试使用一个称为 DB2_PROGRAM 的组名,并假定那就是为结构化类型定义的组名。如果 DB2_PROGRAM 还没有对结构化类型进行定义,那么您可以为结构化类型创建组。

我们将发出 CREATE TRANSFORM 语句,指定 Address_t 结构化类型和 DB2_PROGRAM 组名。FROM SQL 子句定义了特定的函数,可以使用该函数将将一个值转换成表示结构化类型的内置数据类型。WITH FUNCTION 子句指定了转换函数(在这里,该函数为 ADDRESS_TRANSFORM)。

现在我们准备把一些值插入 CLIENTS 表中。INSERT 语句的 VALUES 子句包括对 address_t() 的调用,它调用了address_t 结构化类型的构造函数来创建一个所有属性都设置为空值的某一类型的实例。双点运算符调用了 mutator 方法来设置每个地址属性的值。

随后的查询指定了 CLIENTS 表中的 ADDRESS 列,从中检索地址信息,并将其作为连接的邮寄地址。

清单 1. 使用一个结构化数据类型作为普通表中某一列的类型
connect to sample
...
create type address_t as (street varchar(12), city varchar(12), province varchar(12), postal_code char(6)) mode db2sql create table clients (client_id integer generated always as identity, client_lname varchar(12), client_fname varchar(12), address address_t) create function address_transform (addr address_t) returns varchar(42) language sql return addr..street || ', ' || addr..city || ', ' || addr..province || ' ' || addr..postal_code create transform for address_t db2_program (from sql with function address_transform) insert into clients (client_lname, client_fname, address) values ('Nicholson', 'James', address_t() ..street('20 Indian Rd') ..city('Toronto') ..province('Ontario') ..postal_code('M6T2R1')) select client_id, client_fname, client_lname, address from clients CLIENT_ID CLIENT_FNAME CLIENT_LNAME ADDRESS ----------- ------------ ------------ ------------------------------------------ 21 James Nicholson 20 Indian Rd, Toronto, Ontario M6T2R1 1 record(s) selected. connect reset

现在,假设我们想从 CLIENTS 表中检索一个地址元素(比如说,城市)。为了实现这个目标,我们将调用 observer 方法(使用双点运算符)获取 ADDRESS 列中的结构化类型值的城市属性。(清单 2)。

同样的,如果想更新城市属性值,可以调用 UPDATE 语句中的 mutator 方法(清单 2)。

清单 2. 更新普通表的某一列中的一个结构化类型实例
connect to sample
...
select client_id, client_lname, address..city as city from clients CLIENT_ID CLIENT_LNAME CITY ----------- ------------ ------------ 21 Nicholson Toronto 1 record(s) selected. update clients set address..city = 'Oakville' where client_id = 21 select client_id, client_lname, address..city as city from clients CLIENT_ID CLIENT_LNAME CITY ----------- ------------ ------------ 21 Nicholson Oakville 1 record(s) selected. connect reset

为了阐明一个简单的类型化表层次结构和继承属性,我们将创建三个新的结构化类型(清单 3):Emp_t、Salesperson_t 和 Engineer_t。Emp_t 是根类型,并且它的定义恰好包括我们先前创建的 Address_t 结构化类型。REF USING INTEGER 子句指定了 INTEGER 数据类型,将使用这个数据类型表示该结构化类型及其所有子类型的 REFERENCE 类型。反过来,REFERENCE 是一个系统类型,它是任何类型化表的对象标识符的数据类型。(因为类型化表包含其他对象引用的对象,所以每个类型化表都必须像它的第一列那样拥有一个对象标识符。)

Salesperson_t 和 Engineer_t 是在 Emp_t 之下创建的子类型,Emp_t 是它们的超类型。这些子类型的定义包括不属于 Emp_t 类型的其他属性,但那就是这些子类型与它们的超类型的不同之处;属性是超类型定义的一部分,不过,也可以被其子类型所继承。

已经创建了所需的结构化类型,现在我们就可以创建基于这些类型(清单 3)的类型化表。小型表层次结构的超表称为 EMP。在富有想象力的创造中,我们命名了对象标识符列 OID,并指定它的值由用户生成。插入一个 OID 列的值之后,该值就无法再修改。INHERIT SELECT PRIVILEGES 子句指定,在超表上保留了SELECT 权限的用户或组都将获准在新表上拥有同等的权限。

我们现在准备把数据插入到 ENGINEER 和 SALESPERSON 表中。两个例子中 INSERT 语句的 VALUES 子句包括了对 address_t()的调用,像先前的例子一样,它为 address_t 结构化类型调用了构造函数,创建了一个所有属性值都为空的类型实例。双点运算符调用了 mutator 方法来设置每个地址属性的值。VALUES 子句还为用户指定的 OID 列的值包括了一个 casting 函数,因为该值必须强制转换成目标表的 REFERENCE 类型。默认情况下,casting 函数的名称和结构化类型的名称相同(在这里,这两个名称分别是 Engineer_t 和 Salesperson_t)。

针对层次结构中的三个表执行的查询表明,ENGINEER 和 SALESPERSON 子表已经继承了其超表(EMP)的列,其中包括对象标识符列(OID)。

清单 3. 类型化表层次结构,演示层次结构
connect to sample
...
create type emp_t as (empno integer, lname varchar(12), fname varchar(12), deptno char(4), salary decimal(7,2), address address_t) ref using integer mode db2sql create type salesperson_t under emp_t as (commission decimal(7,2)) mode db2sql create type engineer_t under emp_t as (perf_bonus decimal(7,2), recog_award decimal(7,2)) mode db2sql create table emp of emp_t (ref is oid user generated) create table salesperson of salesperson_t under emp inherit select privileges create table engineer of engineer_t under emp inherit select privileges insert into engineer (oid, empno, lname, fname, deptno, salary, perf_bonus, recog_award, address) values (engineer_t(1), 42, 'Kidman', 'Jennifer', 'Z004', 65000.00, 4000.00, 2000.00, address_t() ..street('7 Dorval Rd') ..city('Markham') ..province('Ontario') ..postal_code('L6G2R1')) insert into salesperson (oid, empno, lname, fname, deptno, salary, commission, address) values (salesperson_t(2), 69, 'Theron', 'Maggie', 'C012', 49000.00, 15000.00, address_t() ..street('7 River St') ..city('Ottawa') ..province('Ontario') ..postal_code('K9G6R2')) select * from emp OID EMPNO LNAME FNAME DEPTNO SALARY ADDRESS ----------- ----------- ------------ ------------ ------ --------- -------------------- 1 42 Kidman Jennifer Z004 65000.00 7 Dorval Rd, Mark... 2 69 Theron Maggie C012 49000.00 7 River St, Ottaw... 2 record(s) selected. select oid, empno, lname, deptno, salary, perf_bonus, recog_award, address from engineer OID EMPNO LNAME DEPTNO SALARY PERF_BONUS RECOG_AWARD ADDRESS ----------- ----------- ------------ ------ --------- ---------- ----------- ---------- 1 42 Kidman Z004 65000.00 4000.00 2000.00 7 Dorva... 1 record(s) selected. select oid, empno, lname, deptno, salary, commission, address from salesperson OID EMPNO LNAME DEPTNO SALARY COMMISSION ADDRESS ----------- ----------- ------------ ------ --------- ---------- ---------------------- 2 69 Theron C012 49000.00 15000.00 7 River St, Ottawa,... 1 record(s) selected. connect reset

结束语

我们已经看到,用户定义的结构化数据类型是表示结构化(非原子的)数据的一种有效方法,这些数据需要作为单独的数据元素或一个独立的单元进行处理,这主要取决于应用程序。这个话题很广泛,并且可能很复杂。本文向您介绍了结构化数据类型、类型层次结构和类型化表的基本概念,这些概念都通过实际例子得以阐明,您可以立刻使用和运行这些例子。要想了解结构化数据类型的更多信息,或者想获得与本文介绍的所有主题有关的更多详细信息,请参阅 IBM DB2 Universal Database SQL Reference, Volume 2


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=89118
ArticleTitle=DB2 基础: 结构化数据类型和类型化表简介
publish-date=06302005