GBase 8s SQL 指南：教程———4从复杂类型选择数据

4从复杂类型选择数据

本章描述如何查询复杂数据类型。复杂数据类型是使用SQL类型构造函数从其他数据类 型的组合构建的。SQL语句可以访问复杂数据类型中的个别组件。复杂数据类型是行类型 或集合类型。

ROW类型具有组合一个或多个相关数据字段的实例。这两种ROW类型是己命名和未命 名。

集合类型具有这样的实例：在其中，每种集合值包含具有相同数据类型的一组元素，这些 数据类型可以是任何基本或复杂数据类型。集合可以由LIST、SET或MULTISET数据 类型组成。

重要：对于复杂数据类型没有跨数据库的支持。只能在本地数据库中对它们进行操作。

有关数据库服务器支持的数据类型的更完整描述，请参阅《GBase 8s SQL参考指南》中的 数据类型一章。

有关如何创建使用复杂类型的信息，请参阅《GBase 8s SQL参考指南》和《GBase 8s SQL 指南：语法》。

4.1选择行类型数据

本节描述如何查询定义为行类型的数据。ROW类型是一个复杂类型，包含一个或多个相 关数据字段。

两种ROW类型如下：

已命名ROW类型

己命名的ROW类型可以定义表、列、其它行类型列的字段、程序变量、语句变量以 及例程返回值。

未命名ROW类型

未命名ROW类型可以定义列、其它行类型列的字段、程序变量、语句局部变量、例 程返回值和常量。

本节中使用的示例使用己命名ROW类型zip_t、address_t和employee_t，这些ROW类 型定义employee表。下图显示创建ROW类型和表的SQL语法。

图:创建ROW类型和表的SQL语法

CREATE ROW TYPE zip_t

(

z_code CHAR(5), z_suffix CHAR(4)

CREATE ROW TYPE address_t

(

street VARCHAR(20),

city VARCHAR(20),

state CHAR(2),

zip zip_t

)

CREATE ROW TYPE employee』

(

name VARCHAR(30),

address address_t, salary INTEGER

)

CREATE TABLE employee OF TYPE employee_t

已命名的ROW 类型zip_t、address_t和employee_t充当类型表employee的字段和列的模 板。类型表是在已命名ROW类型上定义的表。充当employee表的模板的employee_t类 型将address_t类型用作address字段的数据类型。address_t类型使用zip_t类型作为zip字 段的数据类型。

下图显示了创建student表的SQL语法。student表的s_address列定义为未命名的ROW 类型。(s_address列被定义为已命名的ROW类型。)

图:创建student表的SQL语法

CREATE TABLE student

(

s_name VARCHAR(30),

s_address ROW(street VARCHAR (20), city VARCHAR(20), state CHAR(2), zip VARCHAR(9)),

grade_point_avg DECIMAL(3,2)

)

4.1.1选择类型表的列

对类型表的查询与对任何其他表的查询没有区别。例如：下列查询使用星号（*）来指定返 回employee表所有列的SELECT语句。

图:查询

SELECT * FROM employee

employee表上的SELECT语句返回所有列的所有行。

图:查询结果

name	Paul, J.
	address salary	ROW(1O2 Ruby, Belmont, CA, 49932, 1000) 78000
	name	Davis, J.
	address	ROW(133 First, San Jose, CA, 85744, 4900)
	salary	75000

下列查询显示如何构造返回employee表的name和address列的行的查询。

图:查询

SELECT name, address FROM employee

图:查询结果

name	Paul, J.
	address	ROW(102 Ruby, Belmont, CA, 49932, 1000)
	name	Davis, J.
	address	ROW(133 First, San Jose, CA, 85744, 4900)

4.1.2选择包含行类型数据的列

行类型列是在己命名ROW类型或未命名ROW类型上定义的列。使用相同的SQL语法 来查询己命名ROW类型和未命名行类型列。

对行类型列的查询返回ROW类型的所有字段的数据。字段是ROW类型中的组件数据类 型。例如：employee表的address列包含street、city、state和zip字段。下列查询显示如何 构造返回address列的所有字段的查询。

图:查询

SELECT address FROM employee

图:查询结果

address ROW(102 Ruby, Belmont, CA, 49932, 1000)

address ROW(133 First, San Jose, CA, 85744, 4900)

address ROW(152 Topaz, Willits, CA, 69445, 1000))

要访问列包含的个别字段，使用单个点符号表示法来投影列的个别字段。例如：假设您要 访问employee表的address列中的特定字段。以下SELECT语句投影address列 的city和state字段。

图查询

SELECT address.city, address.state FROM employee

图:查询结果

city state

Belmont CA

San Jose CA

Willits CA

用对已命名行类型列构造查询所用的方法来对未命名行类型列构造查询。例如：假设您想 要访问图2中student表的s_address列的数据。可以使用点符号表示法来查询对未命名行类 型定义的列的个别字段。以下查询显示如何对student表构造返回s_address列 的city和state字段的行的SELECT语句。

图查询

SELECT s_address.city, s_address.state FROM student

图: 查询结果

city state

Belmont CA

Mount Prospect IL

Greeley CO

字段投影

不要混淆列和字段。列只与表相关联，并且列投影将格式为name_1.name2的常规点符号表 示法分别用于表和列。字段是ROW类型中的组件数据类型。使用ROW类型(和将ROW 类型指定给单个列的能力)，您可以使用形式为name_a.name_b.name_c.name_d的单点符

SBASe*

号表示法来投影列的各个字段。GBase 8s数据库服务器使用以下优先顺序规则来解释点符 号表示法：

1. table_name_a . column_name_b . field_name_c . field_name_d
2. column_name_a . field_name_b . field_name_c . field_name_d

当特定标识的意义有歧义时。数据库服务器使用优先顺序规则来确定标识指定哪个数据库 对象。考虑以下两个语句：

CREATE TABLE b (c ROW(d INTEGER, e CHAR(2))) CREATE TABLE c (d INTEGER)

在下列SELECT语句中，表达式c.d引用表c的列d (而不是b中列c的字段d)，原因是 表标识具有比列标识更高的优先顺序：

SELECT * FROM b,c WHERE c.d = 10

为了避免引用错误的数据库对象，可为字段投影指定完全符号表示法。例如：假定您想要 引用b中列c的字段d (而不是表c的列d)。以下语句指定想要引用的对象的表、列和字 段标识：

SELECT * FROM b,c WHERE b.c.d = 10

重要： 虽然优先顺序规则减少了数据库服务器误解字段投影的几率，但是建议对所有表、列

和字段标识使用唯一名称。

使用字段投影来选择嵌套字段

行类型通常是列，但可将任何行类型表达式用于字段投影。当行类型表达式本身包含其它 行类型时，表达式就包含嵌套字段。要访问表达式或个别字段中的嵌套字段，使用点符号 表示法。要访问行类型的所有字段，使用星号(*)。本节描述行类型访问的两种方法。

有关如何将点符号表示法和星号符号表示法与行类型表达式配合使用的讨论，请参阅 《GBase 8s SQL指南：语法》中的表达式段。

选择行类型的个别字段

考虑employee表的address列，它包含字段street、city、state和zip。此外，zip字段包含 嵌套字段：z_code和z_suffix。(您可能想要复查图1的行类型和表定义。)对zip字段的 查询返回z_code和z_suffix字段的行。但是，可以指定查询只返回特定嵌套字段。以下查 询显示如何使用点符号表示法来构造只返回address列中z_code字段的行的SELECT语 句。

图:查询

SELECT address.zip.z_code FROM employee

图:查询结果

z_code

39444

6500

76055

19004

使用星号表示法来访问行类型的所有字段

星号符号表示法仅在SELECT语句的选择列表中受支持。为投影列表中的行类型列指定列 名时，数据库服务器返回列的所有字段的值。想要投影ROW类型内的所有字段时，还可 以使用星号符号表示法。

下列查询使用星号符号表示法来返回employee表中address列的所有字段。

图:查询

SELECT address.* FROM employee;

图:查询结果

address ROW(102 Ruby, Belmont, CA, 49932, 1000)

address ROW(133 First, San Jose, CA, 85744, 4900)

address ROW(152 Topaz, Willits, CA, 69445, 1000))

星号符号表示法使得执行某些SQL任务更容易。假设您创建返回行类型值的函

数new_row()并且想要调用此函数并将返回的行插入到表中。数据库服务器没有提供处理 此类操作的简便方法。但是，以下查询显示如何使用星号表示法来返回new_row()的所有 字段并将返回的字段插入到tab_2表中。

图:查询

INSERT INTO tab_2 SELECT new_row(exp).* FROM tab_1

有关如何使用INSERT语句的信息，请参阅修改数据。

重要：只能对使用.*符号表示法的表达式求值一次。

4.2从集合中选择

本节描述如何查询对集合类型定义的列。集合类型是一种复杂数据类型。其中每个集合值 包含具有相同数据类型的一组元素。有关如何访问集合包含的个别元素的信息，请参阅处 理SELECT语句中的集合。

下图显示manager表，在本节的示例中使用了该表。manager表同时包含简单集合类型和嵌 套集合类型。简单集合是一种集合类型。它不包含本身就是集合类型的任何字 段。manager表的direct_reports列就是一个简单集合。嵌套集合是包含另一集合类型的集 合类型。manager表的projects列就是一个嵌套集合。

图:manager 表

CREATE TABLE manager

(
mgjname	VARCHAR(30),
department	VARCHAR(12),
direct_reports	SET(VARCHAR(30) NOT NULL),
projects	LIST(ROW(pro_name VARCHAR(15),
pro_members SET(VARCHAR(20) NOT NULL)
)NOT NULL) )

对于表中的每一行，对作为集合类型的列的查询返回特定集合包含的所有元素。例如：以 下查询显示对manager表的每一行返回department列中的数据和direct_reports列中的所有 元素的查询。

图:查询

SELECT department, direct_reports FROM manager

图:查询结果
department	marketing direct_reports	SET {Smith, Waters, Adams, Davis, Kurasawa}
Jones}	department ddirect_reports	engineering SET {Joshi, Davis, Smith, Waters, Fosmire, Evans,
	department direct_reports	publications SET {Walker, Fremont, Porat, Johnson}
	department direct_reports	accounting SET {Baker, Freeman, Jacobs}

对集合类型查询的输出总是包括类型构造函数，它指定集合是SET、MULTISET或 LIST。例如：在此结果中，SET构造函数位于每个集合的元素前面。花括号（｛｝）划分 集合的元素；逗号隔开集合的个别元素。

4.2.1选择嵌套集合

manager表的projects列是嵌套集合(请参阅图1)。对嵌套集合类型的查询返回特定集合 包含的所有元素。下列查询显示返回projects列的特定行中所有元素的查询。WHERE子句 将查询限制为单个行，在其中，mgr_name列中的值是Sayles。

图查询

SELECT projects

FROM manager

WHERE mgr_name = 'Sayles'

查询结果显示manager表的单个行的project列集合。查询返回管理Sayles描述的那些项目 的名称。对于LIST中的每个元素，集合包含项目名(pro_name)和指定给每个项目的成 员(pro_members)的 SET。

图:查询结果

projects LIST {ROW(voyager_project, SET{Simonian, Waters, Adams, Davis})}

projects LIST {ROW(horizon_project, SET{Freeman, Jacobs, Walker,

Cannan})}

projects LIST {ROW(sapphire_project, SET{Villers, Reeves, Doyle,

Strongin})}

4.2.2使用IN关键字来搜索集合中的元素

可以在SQL语句的WHERE子句中使用IN关键字来确定集合是否包含某元素。例如: 下列查询显示如何构造返回mgr_name和department的值的查询，其

中Adams是direct_reports列中集合的一个元素。

图查询

SELECT mgr_name, department

FROM manager

WHERE 'Adams' IN direct_reports

图: 查询结果

mgjname Sayles

department marketing 尽管可以使用带有IN关键字的WHERE子句来搜索简单集合中的特定集合。但是查询总 是返回整个集合。例如：下列查询返回集合的所有元素，其中Adams是direct_reports列中 集合的一个元素。

图查询

SELECT mgr_name, direct_reports

FROM manager WHERE 'Adams' IN direct_reports

图:查询结果

mgjname Sayles

direct_reports SET {Smith, Waters, Adams, Davis, Kurasawa}

如上所示，对集合列的查询返回整个集合，不是集合中的特定元素。

可以在WHERE子句中使用IN关键字来只引用简单集合。不能使用IN关键字来引用包 含本身就是集合的字段的集合。例如：不能使用IN关键字来引用manager表中 的projects列，原因是projects是嵌套集合。

可以在SELECT语句的WHERE子句中组合NOT和IN关键字来搜索不包含某元素 的集合。例如：下列查询显示返回mgr_name和department的值的查询，其中Adams不 是direct_reports列中集合的元素。

图查询

SELECT mgr_name, department

FROM manager WHERE 'Adams' NOT IN direct_reports
图: 查询结果
mgjname Williams department	engineering
mgr_name	Lyman
department	publications
mgr_name	Cole
department	accounting

有关如何对集合列中的元素进行技术的信息，请参阅基数函数。

4.3选择表层次结构中的行

本节描述如何从表层次结构内的表查询行。

下列查询显示创建本节中的示例使用的类型和表层次结构的语句。

图:创建类型和表层次飾的语句

CREATE ROW TYPE address_t
(
street	VARCHAR (20),
city	VARCHAR(20),
state	CHAR(2),
zip	VARCHAR(9)
)

CREATE ROW TYPE person_t (

name VARCHAR(30),

address address_t,

soc_sec CHAR(9)

)

CREATE ROW TYPE employee_t

(

salary INTEGER

)

UNDER person_t

CREATE ROW TYPE sales_rep_t (

rep_num SERIAL8,

region_num INTEGER

)

UNDER employee_t

CREATE TABLE person OF TYPE person_t

CREATE TABLE employee OF TYPE employee_t

UNDER person

CREATE TABLE sales_rep OF TYPE sales_rep_t

UNDER employee

下图显示上图中的行类型和表的层次关系。

图:类型和表层次结构

 

4.3.1不使用ONLY关键字选择超表的行

表层次结构允许您在单个SQL语句中构造其作用域是超表及其子表的查询。对超表的查 询同时从超表及其子表中返回行。下列查询显示对person表(它是表层次结构中的root超 表)的查询。

图:查询

SELECT * FROM person

图8返回超表中的所有列以及子表(employee和sales_rep)中继承自超表的那些列。对超 表的查询不返回不在超表中的子表的列。该查询显示person、employee和sales_rep表中 的 name、address 和 soc_sec 歹U。

图:查询结果

name Rogers, J.

address ROW(1O2 Ruby Ave, Belmont, CA, 69055)

soc_sec 454849344

name Sallie, A.

address ROW(134 Rose St, San Carlos, CA, 69025)

soc_sec 348441214

4.3.2使用ONLY关键字选择超表的行

尽管超表上的SELECT语句同时从该超表及其子表中返回行。但是不能辨别哪些行来自超

表，哪些行来自子表。要将查询的结果限制为只是超表，必须在SELECT语句中包含

ONLY关键字。例如，下列查询只返回person表中的行。

图:查询

SELECT * FROM ONLY(person);

图:查询结果

name Rogers, J.

address ROW(1O2 Ruby Ave, Belmont, CA, 69055)

soc_sec 454849344

4.3.3对超表使用别名

别名是FROM子句中紧跟在表名后面的词，可以在SELECT或UPDATE语句中为类型 表指定别名，然后将别名本身用作表达式。如果为超表创建别名，那么该别名可表示来自 该超表或继承自该超表的子表中的值。在DB-Access中，下列查询返回person、 employee和sales_rep表的所有实例的行值。

图:查询

SELECT p FROM person p;

GBase 8s ESQL/C不能识别此构造。在GBase 8s ESQL/C程序中，该查询返回一个错误。

4.4总结

本章介绍了通过使用SELECT语句来查询关系数据库以从复杂类型中选择数据的样本语 法和结果。选择行类型数据一节显示如何执行下列操作：

•从类型表和列中选择行类型数据

•将行类型表达式用于字段投影

从集合中选择一节显示如何执行下列操作：

•集合类型上定义的查询列

•查询定义为嵌套集合类型的列

•在嵌套集合类型上定义的查询列

选择表层次结构中的行一节显示如何执行下列操作：

•使用或不使用ONLY关键字来查询超表

• 为超表指定别名