Oracle 10g第2版新特性之SQL和PL/SQL

在 SQL 中查询 XML

　　长期以来，对于很多包含大量字符内容的应用程序的数据类型而言，XML 已成为事实上的标准。最近它也已成为其他应用程序的的存储方法，而不仅仅限于大量的内容。

　　Oracle 从 Oracle9i 数据库开始就提供 XML 与数据库的集成。在该版本中，您可以使用很多不同的方法来查询 XML 内容。在 Oracle 数据库 10g 第 2 版中，新的 XQuery 和 XMLTable 函数使查询 XML 内容变得更容易。

　　XQuery

　　首先，让我们来看这两种方法中较简单的一种：XQuery。请看下面的示例：

SQL＞ xquery 2 for $var1 in (1,2,3,4,5,6,7,8,9) 3 let $var2 := $var1 + 1 4 where $var2 ＜ 6 5 order by $var2 descending 6 return $var2 7 / Result Sequence ------------------ 5 4 3 2

　　新的 SQL 命令 xquery 表示一个 XQuery 命令。请注意该命令：新语法模仿了 FOR ...IN ... 内嵌视图，该视图是在 Oracle9i 数据库中推出的。

　　XQuery 的一般结构由缩略语 FLOWR（发音为“flower”）来描述，它代表 FOR、LET、ORDER BY、WHERE 和 RETURN。在以上的示例中，我们看到第 2 行定义了数据的来源，即从 1 到 9 的一系列数字。它可以是任何来源 — 一组标量值或者 XML 数据的一个元素，由 FOR 子句指定。该行还指定一个变量来存取这些值 (var1)。在第 3 行中，另一个变量 var2 拥有的值是 var1 加 1，由 LET 子句指定。

　　对于所有这些返回值，我们只关心 6 以下的值，这是由子句 WHERE 指定的。然后我们根据 var2 的值以降序方式对结果集排序，如第 6 行中的 ORDER BY 子句所示。最后，利用 RETURN 子句将值返回给用户。

　　如果将该语法与常规 SQL 语法相比较，则 RETURN、FOR、WHERE 和 ORDER BY 类似于 SELECT、FROM、WHERE 和 ORDER BY。LET 子句没有对应的 SQL 语句，但它可以在其他子句中指定。

　　让我们来看这种功能强大的新工具的一个实际应用示例。首先，创建一个表，用于保存与一个帐户持有者间的详细通信信息。

create table acc_comm_log ( acc_no number, comm_details xmltype );

　　现在，向其中插入一些记录。

insert into acc_comm_log values ( 1, xmltype( '＜CommRecord＞ ＜CommType＞EMAIL＜/CommType＞ ＜CommDate＞3/11/2005＜/CommDate＞ ＜CommText＞Dear Mr Smith＜/CommText＞ ＜/CommRecord＞') ) / insert into acc_comm_log values ( 2, xmltype( '＜CommRecord＞ ＜CommType＞LETTER＜/CommType＞ ＜CommDate＞3/12/2005＜/CommDate＞ ＜CommText＞Dear Mr Jackson＜/CommText＞ ＜/CommRecord＞') ); insert into acc_comm_log values ( 3, xmltype( '＜CommRecord＞ ＜CommType＞PHONE＜/CommType＞ ＜CommDate＞3/10/2005＜/CommDate＞ ＜CommText＞Dear Ms Potter＜/CommText＞ ＜/CommRecord＞') );

　　现在您可以看到表中的记录：

SQL＞ l 1 select acc_no, 2 XMLQuery( 3 'for $i in /CommRecord 4 where $i/CommType != "EMAIL" 5 order by $i/CommType 6 return $i/CommDate' 7 passing by value COMM_DETAILS 8 returning content) XDetails 9 from acc_comm_log 10 / ACC_NO XDETAILS ---------- ------------------------------ 1 2 ＜CommDate＞3/12/2005＜/CommDate＞ 3 ＜CommDate＞3/10/2005＜/CommDate＞

　　XMLTable

　　另一个函数 XMLTable 用于类似的目的，但是它象常规的 SQL 查询一样返回列。以下是其运行情况。

1 select t.column_value 2 from acc_comm_log a, 3 xmltable ( 4 'for $root in $date 5 where $root/CommRecord/CommType!="EMAIL" 6 return $root/CommRecord/CommDate/text()' 7 passing a.comm_details as "date" 8* ) t SQL＞ / COLUMN_VALUE --------------------- 3/12/2005 3/10/2005

　　此示例演示了如何将常规的 SQL 语句用于 XML 查询所返回的 XML 表。查询按照非常结构化的 FLOWR 模式来指定命令。
XQuery 与 XMLTable 的对比

　　既然您已经了解了在常规 SQL 查询中使用 XML 的两种方法，就让我们来看这二种方法适用的情形。

　　第一种方法 XQuery 允许您获取 XMLType 形式的数据，在任何支持它的程序或应用程序中都可以将其作为 XML 来处理。在您所看到的示例中，帐户数据的结果输出是 XML 格式，而您可以使用任何工具（不必是关系型工具）来处理和显示这些数据。第二种方法 XMLTable 结合了常规 SQL 和 XML 的功能。帐户数据的结果输出不是 XML 格式，而是关系型数据。

　　注意两个案例中的源代码都是 XML，但是 XQuery 使用 XMLType 来表示 XML 格式的数据，而 XMLTable 将其表示为关系表，可以像常规表一样进行处理。这种功能非常适用于要输出表的现有程序，它引入了 XML 的特性。

　　XML 在预先不太了解确切的数据结构的场合中非常有用。在以上示例中，根据不同模式，通信记录也不相同。如果是电子邮件，则属性可能是接收方的电子邮件地址、回复地址、任何复本（cc:、bcc: 等等）、消息的文本等等。如果是电话呼叫，则属性是所呼叫的电话号码、号码的类型（家庭、工作、移动电话等等）、应答者、留下的语音邮件等等。如果您要设计一个包含所有可能的属性类型的表，则它会包括很多列，并且极其冗长，造成读取困难。但是，如果您只有一个 XMLType 列，则可以将所有内容填在那里,但仍然保持通信类型的独特属性。查询仍然可以使用简单的 SQL 接口，使应用程序的开发变得轻而易举。