![关闭 [x]](http://dw1.s81c.com/i/c.gif){kind=small}
SQL 被设计来让程序员可以抽象出各种代码算法，用于处理、操纵或转换数据，并且不丢失存取数据细节上的算法实现。对于这一目的，SQL 是极其成功的。利用它可以创建非常复杂的 OLTP 应用程序、报告系统、数据仓库和业务分析。SQL 和数据库设计的关系模型彼此促进，并且 SQL 的强大性和灵活性已使得关系模型成为一种在过去几十年间占绝对优势的数据模型。
在这场竞争中失败的是那些层次数据库。这些数据库让数据结构化成数据林 (forest of data)，其中各个记录类型之间具有父子关系。在允许程序员抽象出数据访问和持久存储的机制方面，很多用于这些数据库的编程语言都没有 SQL 那么成功，这一局限性导致了它们的衰落。
XML 文档使用层次表示实体之间的关系。XQuery 用于处理一个或多个文档中的 XML 数据。XQuery 跟 SQL 一样，允许程序员抽象出数据访问和持久存储的机制，但是 XQuery 提供的编程实用工具比 SQL 的更好一些。至于如何在高层次处理数据，从技术上说，又绕回到了起点。XQuery 有一些有趣的方面，使之对程序员更具吸引力。本文中，我将讨论这些优点。
首先，来看一些 XQuery 明显优于 SQL 的情况。在 RDBMS 中，很容易找到用两个单独数据库表表示的父-子关系。例如，一个带有多个待采购项的采购订单，在数据库中可能会通过一个 purchase_order 表（采购表）和一个 items 表（货物表）来表示。（purchase_order 表包含订单号和客户号。items 表包含订单号和货物号。）items 表也在 purchase_order 表上具有一个外键关系。SQL 非常善于处理这类关系。参见 图 1 和 清单 1。
图 1. 为采购订单设计的父-子表
清单 1. 用于找到一个给定采购订单中的所有货物的 SQL 语句
select item_no, item_desc from purchase_order po, items i where ord_no = 'A12345' and po.ord_no = i.ord_no |
当数据具有一个长度不定的关系链时，SQL 的弱点就暴露出来了。例如，一个经理可以管着多个员工，而这些员工可能又会各自管着多个员工，依此类推。在这种情况下，用单独的父-子表来表示这样的数据是不切实际的。比如说，必须设计最大关系长度那么多个表 — 如果管理链最多有 6 层之高，那么就必须设计 6 个表。这样做是很糟糕的。更糟的是，从 x 层经理处到达 n 层的所有员工，必须联结从 x 层到 n 层的表。这种方法在用到的资源方面是昂贵的。
采纳的典型解决方案是具有单个表，其中表的每一行都具有与其他各行的父-子关系，如 图 2 所示。员工 ID、经理 ID 和员工姓名这些列指出了关系。在本例中，Jack Brown 管理着 2 名员工，即 John Silver 和 Ron McDonald。John Silver 又管理着 Jon Carino。不幸的是，在 XML 中查询这样的数据将导致难以编写和维护的查询。关于创建一个 SQL 语句，用于找到直接或间接向某个经理汇报的员工，代码请参见 清单 2。
图 2. 为员工数据设计的具有关联行的单个表
清单 2. 用于找到直接或间接向某个经理汇报的员工的 SQL 语句
with reporting_to(emp_id, emp_name) as
(
select emp_id, emp_name
from employees
where emp_name = 'John Silver'
union all
select direct_reports.emp_id, direct_reports.emp_name
from employees direct_reports, reporting_to
where reporting_to.emp_id = direct_reports.mgr_id
)
select emp_id, emp_name
from reporting_to
|
可以看到，这个查询使用了递归，并且需要费很大的劲才能弄明白。使用 XML 数据类型和 XQuery 解决这个问题就好多了。在本例中，可以将整个组织表示在表中一行的单个 XML 文档中。清单 3 和 4 展示了对应的实现，现在您可以判断出哪种方法在编程方面更容易构造和维护了。
清单 3. 员工数据的 XML 表示
<?xml version="1.0"?>
<org>
<employee id="0001">
<name>Jack Brown</name>
<employee id="0002">
<name>John Silver</name>
<employee id="0004">
<name>John Silver</name>
</employee>
</employee>
<employee id="0003">
<name>Ron McDonald</name>
</employee>
</employee>
</org>
|
清单 4. 使用 XML 数据类型时找到员工列表
select emp.emp_id, emp.emp_name
from employees, xmltable(
'$ORG/org//employee[name = "John Silver"]/descendant-or-self::employee'
columns
emp_id char(4) path '@id',
emp_name varchar(254) path 'name/text()'
) emp
|
同样注意，层次数据比您想象的更常见一些。例如，在前一个例子中，采购订单不能包含其他采购订单，所以表面上看，这个例子似乎绝对不会是层次结构。采购订单可能会失败；就是说，采购的 20 种货物中，可能有 2 种没货或者缺货。因此，现有采购订单以买到有货的货物而结束，并为没买到的货物生成一个新的采购订单。在这样的情况下，新采购订单最好包含一个到旧订单的链接。然后就有了一个采购订单链，其关系链接就像刚才讨论的员工数据一样。
非层次数据是建模在关系数据世界中的更大一块数据。重要的是，任何用于访问或写入数据库的编程语言都要能够很好地处理这些数据实体。在下面这些例子中，我要描述 XQuery 如何非常好地处理此类数据，以及从程序员的角度来看，是一种超越于 SQL 的解决方案。
本例处理的情况中，编程任务被组织成竞争 ("events")。对于每一个这样的事件，事件的创建者或管理者都给它分配一定数量的点 ("points")。提供了一个价格点查找表，每个价格点都有一个整数标签，称之为级别 ("level")。问题是找到与事件 "points" 值最匹配的价格点，并使用这个值作为该级别的 "price"（竞争中的赢者得到的评价）。如果有两个级别最匹配，那么使用较高级别来确定适用的价格点。
清单 5. 使用非-XML 数据类型的数据库表设计
[db2pe@lc4eb4168274532 code]$ db2 describe table pricing
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EVENT_ID SYSIBM INTEGER 4 0 No
POINTS SYSIBM INTEGER 4 0 Yes
PRICE SYSIBM INTEGER 4 0 Yes
3 record(s) selected.
[db2pe@lc4eb4168274532 code]$ db2 describe table pricing_tier
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
LEVEL SYSIBM INTEGER 4 0 No
PAYMENT SYSIBM INTEGER 4 0 Yes
2 record(s) selected.
|
清单 6. 表中的样本数据
[db2pe@lc4eb4168274532 code]$ db2 "select * from pricing fetch first 5 rows only"
EVENT_ID POINTS PRICE
---------- ---------- -----------
10472 640 0
10471 220 0
10470 190 0
10469 180 0
10466 780 0
5 record(s) selected.
[db2pe@lc4eb4168274532 code]$ db2 "select * from pricing_tier"
LEVEL PAYMENT
----------- -----------
0 60
1 120
2 240
3 360
4 480
5 600
6 720
7 840
8 record(s) selected.
|
清单 7 展示了所需的 SQL 更新语句。
清单 7. 使用标准 SQL 用于更新定价表的更新语句
update pricing o
set (price) = (
select payment
from (
select pricing_info.event_id event_id , max(level) matched_level
-- "min_values" will have the smallest difference between the current
-- price and all the pre-defined price points for each event. "pricing_info"
-- will contain an index of all the price point differences tabulated
-- by the price point level. A join of these two tables by event_id
-- and price point difference, should get you the price point level that
-- you are seeking for each event.
from (
select event_id, min(absdiff)
from (
-- For each event_id, calculate the absolute difference
-- between the existing price and the price points
select event_id, abs(points - payment) absdiff, level
from pricing, pricing_tier
)
group by event_id
) as min_values(event_id, closest_match), (
-- For each event_id, calculate the absolute difference
-- between the existing price and the price points
select event_id, abs(points - payment) absdiff, level
from pricing, pricing_tier
) as pricing_info(event_id, absdiff, level)
where min_values.event_id = pricing_info.event_id
and closest_match = absdiff
group by pricing_info.event_id
)x , pricing_tier y
where x.matched_level = y.level
and x.event_id = o.event_id
)
|
现在来看一种使用 XML 数据类型的实现。pricing_tier 表和事件详细情况可以用清单 8 和 9 中的 XML 文档来表示。
清单 8. 定价信息作为一个 XML 文档
<pricing>
<pricingtier level="0"><price>60</price></pricingtier>
<pricingtier level="1"><price>120</price></pricingtier>
<pricingtier level="2"><price>240</price></pricingtier>
<pricingtier level="3"><price>360</price></pricingtier>
<pricingtier level="4"><price>480</price></pricingtier>
<pricingtier level="5"><price>600</price></pricingtier>
<pricingtier level="6"><price>720</price></pricingtier>
<pricingtier level="7"><price>840</price></pricingtier>
</pricing>
|
清单 9. 事件详细信息作为一个 XML 文档
<event id="9083" eventstate="Cancelled: Client request">
<title>UCD research 5</title>
<points>170</points>
</event>
|
清单 10 展示了所使用的表设计。
清单 10. 使用 XML 数据类型时的数据库表设计
[db2pe@lc4eb4168274532 code]$ db2 describe table events
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EVENT_ID SYSIBM INTEGER 4 0 No
EVENT SYSIBM XML 0 0 No
2 record(s) selected.
[db2pe@lc4eb4168274532 code]$ db2 describe table pricing
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PRICING SYSIBM XML 0 0 Yes
1 record(s) selected.
|
此设计假设定价信息被作为 "points" 元素的对等元素插入到文档中。清单 11 展示了做这件事的更新语句。
清单 11. 使用 XML 数据类型和 XQuery 的更新语句
update events o
set (event) = (
select xmlquery('
(:
: First build a series of "pair" elements which map a pricing level number
: to a value that is the absolute value of the difference between the
: event points and the price point. Once you have this sequence, sort by
: the difference and pick the lowest value. This results in you picking the
: closest price match. Because you break ties for the closest value by picking
: the higher level, you add a second sort key (level) in descending order.
:)
let $closestMatch :=
(
for $pair in ( for $p in $PRICING/pricing/pricingtier
let $lp := xs:int($EVENT/event/points)
let $absdiff := abs($lp - xs:int($p/price))
return
<pair>
<level>{$p/@level}</level>
<diff>{$absdiff}</diff>
</pair>
)
order by xs:int($pair/diff/text()),
xs:int($pair/level/text()) descending
return $pair
)[1]
return
transform
copy $e := $EVENT
modify
do insert
<pricing>{
$PRICING/pricing/pricingtier[@level = $closestMatch/level/@level]/
price/text()
}</pricing> after $e/event/points
return $e
')
from events a, (select pricing from pricing fetch first row only) b
where a.event_id = o.event_id
)
|
这里要注意的主要事情是，编程风格非常类似于过程式编程 — 进行了中间计算，并将结果赋值给变量，然后变量又会在其他地方被重用。这里甚至有嵌套的 for 循环，这是过程式编程中所常用的。清单 7 中的 SQL 语句相当于函数和方法，但是较难理解，因此这种编程风格与普通过程式风格相去甚远。XQuery 风格相当容易构造和维护，从而导致了程序员生产力的提高。
临时数据是实体状态的表示，用来表示实体随时间而变化的一个或多个方面。有很多方式捕获这种变化的状态。为了简单起见，我们主要关注下面这个例子中单个变量中的变化。来看数据挖掘中一个相当常见的数据操作：概要说，您在试图识别一个实体集合，其变化的状态具有某些有趣的特征。例如，这个集合可能是一个客户，他在购买了汤之后，很快购买洗涤剂。本例中，来看前一个例子的扩展。查找 2000 年到 2009 年期间有过不止一次晋级的员工。参见 清单 12。
清单 12. 捕获员工晋级历史的表结构
[db2pe@lc4eb4168274532 code]$ db2 describe table pay_history
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EMP_ID SYSIBM CHARACTER 4 0 No
START_TIME SYSIBM TIMESTAMP 10 6 No
END_TIME SYSIBM TIMESTAMP 10 6 Yes
JOB_LEVEL SYSIBM INTEGER 4 0 No
4 record(s) selected.
|
清单 13 展示了所需的 SQL 语句。
清单 13. 用于识别在指定期间晋级不止一次的员工的选择语句
select emp_id, count(emp_id) from pay_history where start_time > '2000-01-01-00.00.00.000000' and end_time < '2010-01-01-00.00.00.000000' group by emp_id having count(emp_id) > 1 |
清单 14 展示了基于 XML 数据类型的表设计。
清单 14. 使用 XML 数据类型捕获员工晋级历史的表结构
[db2pe@lc4eb4168274532 code]$ db2 describe table pay_history
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EMP_ID SYSIBM CHARACTER 4 0 No
HISTORY SYSIBM XML 0 0 No
2 record(s) selected.
|
清单 15 展示了表示工作历史的 XML 数据。
清单 15. 使用 XML 数据类型捕获员工晋级历史的表结构
<employee id="0001">
<pay startDate="2001-11-23T00:00:00.000000Z" endDate="2002-10-07T00:00:00.000000Z"
level="4">70500</pay>
<pay startDate="2002-10-07T00:00:00.000000Z" endDate="2005-06-18T00:00:00.000000Z"
level="5">81500</pay>
<pay startDate="2005-06-18T00:00:00.000000Z" endDate="2007-06-01T00:00:00.000000Z"
level="6">96700</pay>
<pay startDate="2007-06-01T00:00:00.000000Z" level="7">120000</pay>
</employee>
|
清单 16 展示了基于 XQuery、等价于 清单 13 中的选择语句。
清单 16. 使用 XML 数据类型捕获员工晋级历史的表结构
select emp_id
from pay_history
where xmlexists('
let $numPromotions := count(
$HISTORY/employee/pay[@startDate gt "2000-01-01T00:00:00.000000Z"
and @endDate lt "2010-01-01T00:00:00.000000Z"]
)
return
if($numPromotions gt 1)then
(
true()
)else()
')
|
XQuery 形式中似乎没有任何改进。我们来考虑一个额外的需求。员工会被降级或晋级。每个工作历史记录都可能表示一次降级或者晋级。如果只关注晋级，而排除所有降级，那么 XQuery 语句现在就更改为 清单 17 中的代码。
清单 17. XQuery 只关注工作历史中的晋级过程
select emp_id
from pay_history
where xmlexists('
let $i := 0
(:
: You have to work around the fact that the DB2 pureXML implementation of XQuery
: does not support the preceding-sibling axis. To do this, iterate through the
: "pay" elements for a given employee, and tack on the position of the element.
: Later on, use this position to determine the previous "pay" element.
:)
let $pairs := (
for $jobChanges in $HISTORY/employee/pay[@startDate gt
"2000-01-01T00:00:00.000000Z" and @endDate lt "2010-01-01T00:00:00.000000Z"]
let $i := $i + 1
return
<pair><position>{$i}</position>{$jobChanges}</pair>
)
let $numPromotions := count(
for $p in $pairs
let $currentPos := xs:int($p/position)
return
(: If this is the first "pay" element, its not a demotion :)
if($currentPos eq 1)then
(
$p/pay
)else(
if($pairs[$currentPos - 1]/data(@level) lt $p/data(@level))then
(
$p/pay
)else()
)
)
return
if($numPromotions gt 1)then
(
true()
)else()
')
|
现在，使用非-XML 数据类型的等价 SQL 语句需要一个自我联结 (self-join) 来实现。即使能够实现，也会相当复杂且难以构造。为了简化，可能需要向 pay_history 表添加一个 "job history row number（工作历史行号）" 列，然后允许在这一列上进行自我联结。这种方法留给读者自己去练习。
本文并不是想要一一列举 XQuery 能够做到的事情。相反，文中例子旨在说明它提供的强大的实用工具如何让程序员能够更快地开发应用程序并使得应用程序更容易维护。依照作者的观点，这是鼓励在数据库中增加采用 XML 数据类型的一个好理由。
学习
- 将 CONNECT BY 移植到 DB2（Serge Rielau，developerWorks，2005 年 10 月）：文中精彩地介绍了对处理层次数据使用递归查询。
- DB2 9 中的 pureXML：怎样查询您的 XML 数据？（Matthias Nicola 和 Fatma Ozcan，developerWorks，2007 年 8 月）：在这篇非常详细的教程中，更多地了解 DB2 pureXML 提供来处理 XML 数据类型的各种实用工具。
- XML 新手入门 获得学习 XML 所需的资源。
- developerWorks 中国网站 XML 技术专区：在 XML 专区获取提高您的专业技能所需的资源，包括 DTD、模式和 XSLT。访问 XML 技术文档库，获得广泛的技术文章和技巧、教程、标准和 IBM 红皮书。
- IBM XML 认证：了解如何才能成为一名 IBM 认证的 XML 和相关技术的开发人员。
- developerWorks 技术活动 和 网络广播：随时关注这些活动中的技术。
- developerWorks 演示中心：观看演示，包括面向初学者的产品安装和设置演示，以及为经验丰富的开发人员提供的高级功能。
- IBM Rational Twitter：立即加入并关注 developerWorks tweets。
- developerWorks 播客：收听面向软件开发人员的有趣访谈和讨论。
- developerWorks 演示中心：观看演示，包括面向初学者的产品安装和设置演示，以及为经验丰富的开发人员提供的高级功能。
获得产品和技术
- IBM 产品评估试用版软件：下载或 IBM SOA 人员沙箱，并开始使用来自 DB2®、Lotus®、Rational®、
Tivoli® 和 WebSphere® 的应用程序开发工具和中间件产品。
讨论
- developerWorks 个人资料：马上创建您的个人资料并 设置关注列表。
- XML 专区讨论论坛：参与任何一个 XML 相关讨论。
- The developerWorks 中文社区：查看开发人员推动的博客、论坛、组和 wikis，并与其他 developerWorks 用户交流。
