在SQL Server2005中实现数组映射(二)-技术开发专区

在SQL Server2005中实现数组映射(二)

作者：IT168 极地圣火编辑：李宁 2007-12-29 00:00

【IT168 专稿】

在SQL Server2005中实现数组映射（一）

四、重新访问聚合映射

到现在为止我们已经学会了如何通过一系列参数来聚合一条简单的SELECT语句。但是这样仅仅在与如sp_who这样的存储过程联合时才有用。sp_who可以列出当前的用户，或使用sp_spaceused，这个存储过程可以返回关于表、视图或查询。行数和磁盘信息。如下面的代码使用SP_map对两个表调用了sp_spaceused：

EXEC SP_map

@StmtTemplate = "execute sp_spaceused <MAIN_ARG>",

@MainArray = 'Users, Permissions',

@Accumulate = 0

我们看到的@Accumulate标志，将多个结果集合为了一个。但使用存储过程做这件事和使用简单的SELECT语句是有很大差异的。如果我们编辑上面的代码，将@Accumulate打开，并设置@Verbose为1，SP_map将返回如下的代码：

-- TEMPLATE (create): SELECT * INTO #Map_Temp_Table 
       FROM OPENROWSET ('SQLOLEDB',
'Server=(local)\SqlExpress;TRUSTED_CONNECTION=YES;',
'set fmtonly off execute dbo.sp_spaceused <MAIN_ARG>')
-- TEMPLATE (update): INSERT INTO #Map_Temp_Table SELECT * 
       FROM OPENROWSET ('SQLOLEDB',
'Server=(local)\SqlExpress;TRUSTED_CONNECTION=YES;',
'set fmtonly off execute dbo.sp_spaceused <MAIN_ARG>')
SELECT * INTO #Map_Temp_Table FROM OPENROWSET
('SQLOLEDB','Server=(local)\SqlExpress;TRUSTED_CONNECTION=YES;',
'set fmtonly off execute dbo.sp_spaceused Users')
INSERT INTO #Map_Temp_Table SELECT * FROM OPENROWSET
('SQLOLEDB','Server=(local)\SqlExpress;TRUSTED_CONNECTION=YES;',
'set fmtonly off execute dbo.sp_spaceused Permissions')
select * from #Map_Temp_Table
drop table #Map_Temp_Table

当按通常方法调用上面的语句时（不使用SP_map包装），存储过程将返回单独的结果集。但是一个特殊的存储过程—sp_msForEachTable-可以返回多个结果集（每一个表是一个结果集）。为了显示所有表的结果集，可以使用下面的标准调用方式，代码如下：

EXEC sp_msForEachTable "EXEC sp_spaceused '?'"

SP_map还只要在语句上稍加修改，就可以将输出结果转换为一个结果集。代码如下：

EXEC SP_map

@StmtTemplate = 'EXEC sp_msForEachTable

"EXEC sp_spaceused ''?''"', @Accumulate = 1

要注意的是，只有我们不用支持@MainArray作为参数时，还可以在SP_map中包装返回多个结果集的存储过程。而@MainArray，在这种情况下，已经在表名集合中被隐含定义了。上面调用SP_map后将产生如下的代码：

- TEMPLATE (create):
SELECT * INTO #Map_Temp_Table FROM OPENROWSET (
'SQLOLEDB',
'Server=(local)\SqlExpress;TRUSTED_CONNECTION=YES;',
'set fmtonly off EXEC dbo.sp_msForEachTable
"select convert(varchar(128), ''?'') as Name"')
TRUNCATE TABLE #Map_Temp_Table
INSERT INTO #Map_Temp_Table EXEC dbo.sp_msForEachTable
"select convert(varchar(128), '?') as Name"
SELECT * INTO #Map_Temp_Table FROM OPENROWSET (
'SQLOLEDB',
'Server=(local)\SqlExpress;TRUSTED_CONNECTION=YES;',
'set fmtonly off EXEC dbo.sp_msForEachTable
"select convert(varchar(128), ''?'') as Name" ')
TRUNCATE TABLE #Map_Temp_Table
INSERT INTO #Map_Temp_Table EXEC dbo.sp_msForEachTable
"select convert(varchar(128), '?') as Name"
select * from #Map_Temp_Table
drop table #Map_Temp_Table

这时，代码仅仅产生了一个CREATE模板，并没有UPDATE模板。还有，这个CREATE模板没有占位符。SP_map并没有扫描一个按着正常方式调用的存储过程返回的列表。而是由强大的sp_msForEachTable存储过程通过自身进行的扫描。

五、可改变序列的映射

为了进行下面的例子，先建立一个简单的表Permissions。DDL代码如下：

CREATE TABLE [dbo].[Permissions](

[IDPermission] [int] IDENTITY(1,1) NOT NULL,

[IDUser] [int] NOT NULL,

[IDPermissionType] [int] NOT NULL,

[PermGranted] [bit] NOT NULL

)

让我们先考虑如下的场景：我们刚刚建立的Permission表包含了系统中每个用户的许可信息。而在这个表中的每一行指定了一个用户的ID，一个许可类型和这个用户是否被授权这个许可，还是没有被授权。我们有六个新用户ID的列表，并且我们想授予每一个用户的所有的权限（共8个许可类型）。为了完成这个任务，我们必须在Permissions表中插入48行记录，每一个用户和每一个许可是一行。但是我们只调用一次SP_map就可以完成这项任务。下面的解决方案只显示了五行代码（为了清楚起见，使用了变量），然后，如果我们直接将其直接放这些列表在过程调用中，它仅仅需要一行代码。这个例子是二维的，要求两个占位符。与上一个例子相比较，这个例子在模板中使用了一个单独的列表和一个单独的占位符。解决方案的代码如下：

 DECLARE @USERS VARCHAR(50)
SET @USERS = '913,1425,11223,739,1234,1001'
   DECLARE @PERMS VARCHAR(50)
SET @PERMS = '2,3,5,7,11,13,17,19'

EXEC SP_map
@StmtTemplate='INSERT INTO Permissions VALUES(<MAIN_ARG>, <AUX_ARG>, 1)',
@MainArray=@USERS, @AuxArray=@PERMS, @Permute=1, @Verbose=1, @Process=0

将@Verbos设为1，SP_map产生的代码：
-- INSERT INTO Permissions VALUES(913, <AUX_ARG>, 1)
   -- INSERT INTO Permissions VALUES(1425, <AUX_ARG>, 1)
   -- INSERT INTO Permissions VALUES(11223, <AUX_ARG>, 1)
   -- INSERT INTO Permissions VALUES(739, <AUX_ARG>, 1)
   -- INSERT INTO Permissions VALUES(1234, <AUX_ARG>, 1)
   -- INSERT INTO Permissions VALUES(1001, <AUX_ARG>, 1)

要注意的是每一行SQL在开始都使用了注释符号，那么这是为什么呢？是由于这些都是不可执行的SQL表达式！SP_map现在已经替换了使用用户列表值的@StmtTemplate中的占位符<MAIN_ARG>。SP_map很明显在二维的组合方式上操作。对于每一个用户和每一个许可，都做了一些事情。换句话说，这仅仅是两个嵌套的循环。因此，设置@Verbose为1将显示外部循环的细节，当设置@Verboss为2时，将显示内部循环的细节。内部循环的最终形式是一个完整的DML表达式集合。因此，这些在开始并没有使用注释符号。代码如下：

 -- INSERT INTO Permissions VALUES(913, <AUX_ARG>, 1)
      INSERT INTO Permissions VALUES(913, 2, 1)
INSERT INTO Permissions VALUES(913, 3, 1)
INSERT INTO Permissions VALUES(913, 5, 1)
INSERT INTO Permissions VALUES(913, 7, 1)
INSERT INTO Permissions VALUES(913, 11, 1)
INSERT INTO Permissions VALUES(913, 13, 1)
INSERT INTO Permissions VALUES(913, 17, 1)
INSERT INTO Permissions VALUES(913, 19, 1)
-- INSERT INTO Permissions VALUES(1425, <AUX_ARG>, 1)
      INSERT INTO Permissions VALUES(1425, 2, 1)
INSERT INTO Permissions VALUES(1425, 3, 1)
INSERT INTO Permissions VALUES(1425, 5, 1)
INSERT INTO Permissions VALUES(1425, 7, 1)
INSERT INTO Permissions VALUES(1425, 11, 1)
INSERT INTO Permissions VALUES(1425, 13, 1)
INSERT INTO Permissions VALUES(1425, 17, 1)
INSERT INTO Permissions VALUES(1425, 19, 1)
-- INSERT INTO Permissions VALUES(11223, <AUX_ARG>, 1)
      INSERT INTO Permissions VALUES(11223, 2, 1)
INSERT INTO Permissions VALUES(11223, 3, 1)
INSERT INTO Permissions VALUES(11223, 5, 1)
INSERT INTO Permissions VALUES(11223, 7, 1)
INSERT INTO Permissions VALUES(11223, 11, 1)

图5显示了上面代码执行后Permissions表的结果。

图5 可改变序列的映射结果集。所有用户和许可的组合被手稿了Permissions表

这个例子到现在为止已经通过打开@Permute标志处理了一个二维的场景，那么如果我们关闭这个标志会发生什么情况呢？

当@Permute关闭，这两个列表将被约束为同一个集合，因此，我们需要使用户和许可列表拥有同样的元素个数，如图6所示：

图6

我们可以从图6看出，这个过各在两个模式下使用@MainArray和@AuxArray。一个关键的地方是，当我们将@Permute设置为0时，会出现次序改变。使用上面的改变次序的例子，元素的次序在两个列表中并没有意义。（要注意的是这个次序和我们是否打开@Permute有关，因此数据库操作几乎总是产生能引起持久化数据库改变的副作用）。

关注我们