给存储过程传递一个表(XML运用)

http://www.sqlservercentral.com/articles/Stored+Procedures/2977/

By Jacob Sebastian, 2007/05/30 
Translated by 挖土（Digjim），2009年1月9日,  http://digjim.cnblogs.com/

 

介绍

我们大部分人都会遇到特殊的情况去设计可重用、可管理的数据库对象（存储过程，函数，试图等），当一个应用程序很大的时候尤为重要。通用的功能性代码需要可识别，并在定义成函数以后能被程序的不同部分调用。试图、存储过程也是这样。按照这样的方式设计出来的代码提高了可管理性，又极大的提高了可重用性，当然也提高了生产率，减少了错误。

问题

让我们假设，我们在做一个库存管理系统。当一个交易（销售订购，发货，收获，库存调整等）发生，我们需要根据交易来调整相应商品的库存量。我们已经有针对每一个交易的存储过程来保存或更新数据，所有这些存储过程都要去更新相应商品的库存量。

既然库存量需要在不同的地方被更新，那么把这一部分单独拿出来做成一个存储过程就非常有必要了。这样这个存储过程就可以在任何需要更新库存量的地方被调用。这个看上去很简单，但是困难的部分是我们要一次更新多个商品的库存量。

表变量似乎是一个好主意。如果我们可以传输一个包含商品更新信息的表变量，那么可以极大地减少复杂度。但是SQL Server不允许给存储过程传输表变量。那我们怎么办呢？

在这里，我想尝试提供一种通过使用XML来传输一个表到存储过程的解决方法。调用者可以把一个表中的数据转换成XML变量来传输给存储过程。被调用者可以把XML变量转换成表或直接用XQuery来查询数据。

调用者

调用者需要把一个表数据转换成XML变量，数据可能来源于一个表（Table）或查询（Query），下面是一个把查询出来的数据转换成XML变量的例子。

    1 /*

    2     先建一个示例表.

    3 */

    4 

    5 CREATE TABLE [dbo].[OrderDetails](

    6     [OrderDetailID] [int] IDENTITY(1,1) NOT NULL,

    7     [ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    8     [Qty] [int] NULL

    9 ) ON [PRIMARY]

   10 

   11 /*

   12     加入一些样例数据

   13 */

   14 INSERT INTO OrderDetails(ItemNumber, Qty)

   15     SELECT 'A001', 10

   16     UNION SELECT 'A002', 20

   17     UNION SELECT 'A003', 30

   18 /*

   19     下面的查询以XML的格式返回查询结果.

   20 */

   21 

   22 SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW('item'), ROOT('items')

   23 

   24 /*

   25 OUTPUT:

   26 

   27 <items>

   28   <item ItemNumber="A001" Qty="10" />

   29   <item ItemNumber="A002" Qty="20" />

   30   <item ItemNumber="A003" Qty="30" />

   31 </items>

   32 */

 

下面，把查询结果赋给一个XML变量。

    1 -- 定义变量

    2 DECLARE @x XML

    3 

    4 -- 把查询结果保存到XML变量中

    5 SET @x = (SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW('item'), ROOT('items'), TYPE)

    6 

    7 -- 从XML变量中查询数据（验证一下，我们是不是作对了）

    8 SELECT

    9     x.item.value('@ItemNumber[1]', 'VARCHAR(20)') AS ItemNumber,

   10     x.item.value('@Qty[1]', 'INT') AS Qty

   11 FROM @x.nodes('//items/item') AS x(item)

 

 

这个阶段，我们准备好了一个XML变量，它可以被传到子存储过程或函数中。这个XML变量中包含了我们需要子存储过程或函数来帮我们执行或更新的数据。子存储过程既可以把这个XML变量转换成一个表，也可以直接从XMl变量了都数据。

被调用者

到目前为止，我们看到了怎么样把一个查询结果转换成XML变量。这个XML变量可以传输给根据货物信息来更新货物库存量的存储过程。最简单的方法就是把这个XML里的数据包装成一个试图，然后把他当作一个表来使用。

我们再建另一个表，库存量表，他将被XML传输过来的数据库更新。用下面的脚本来创建这个示例表和数据。 

    1 CREATE TABLE [dbo].[Inventory](

    2     [InventoryID] [int] IDENTITY(1,1) NOT NULL,

    3     [ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    4     [Stock] [int] NULL

    5 ) ON [PRIMARY]

    6 

    7 INSERT INTO Inventory (ItemNumber, Stock)

    8     SELECT 'A001', 0

    9     UNION SELECT 'A002', 0

   10     UNION SELECT 'A003', 0

 

下面是被调用者这边需要实现的设计。 

    1 CREATE PROCEDURE [dbo].[UpdateInventory1]

    2 (

    3     @x XML

    4 )

    5 AS

    6 

    7 SET NOCOUNT ON

    8 

    9 /*

   10     下面的代码把XML的数据包装成一个视图

   11     然后更新库存量表"inventory".

   12 */

   13 

   14 UPDATE Inventory SET

   15     stock = stock + v.Qty

   16 FROM Inventory inv

   17 INNER JOIN (

   18     SELECT

   19         x.item.value('@ItemNumber[1]','varchar(20)') AS ItemNumber,

   20         x.item.value('@Qty[1]','INT') AS Qty

   21     FROM @x.nodes('//items/item') AS x(item)

   22 ) v ON (v.ItemNumber = inv.ItemNumber)

   23 

   24 RETURN

 

执行
现在我们来执行这个存储过程，执行下面的代码。 

    1 -- 定义XML变量

    2 DECLARE @x XML

    3 

    4 -- 把结果保存在XML变量中

    5 SET @x = (SELECT ItemNumber, Qty FROM OrderDetails FOR XML RAW('item'), ROOT('items'), TYPE)

    6 

    7 -- 执行存储过程

    8 EXECUTE UpdateInventory1 @x

    9 

   10 -- 查看更新后的数据

   11 SELECT * FROM inventory

 

更新存储过程

上面这些脚本中，把XMl变量包装成视图。这是一个非常简单直接的方法。你也可以直接访问这些数据就像她直接来自一个表或着试图。在内部视图执行XML查询的复杂度就没有了。

下面的例子，给出了另外一种根据XML变量里的数据库来直接更新数据库的方法。

    1 CREATE PROCEDURE [dbo].[UpdateInventory2]

    2 (

    3     @x XML

    4 )

    5 AS

    6 

    7 SET NOCOUNT ON

    8 

    9 /*

   10     这个存储过程用的TSQL代码更少

   11     它根据XML里的数据来直接更新数据库，

   12     而不是把XMl数据转换成试图。

   13 */

   14 

   15 UPDATE Inventory SET

   16     stock = stock + x.item.value('@Qty[1]','INT')

   17 FROM Inventory inv

   18 INNER JOIN @x.nodes('//items/item') x(item) ON

   19     (x.item.value('@ItemNumber[1]','varchar(20)') = inv.ItemNumber)

   20 

   21 RETURN