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

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

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

【IT168 专稿】

    SQL语言一直以来被认为在最近三十年中是发展最慢的语言之一。但SQL却可以在专家手中得心应手，发挥出更大的作用。而有很多专家认为，把SQL和其他的语言相比是完全没有必要的。虽然SQL没有现在语言中的局部变量，语言结构、以及缺少一些基本的功能。然而，我们却可以使用存储过程来扩展这种语言。在本文中将描述如何使用存储过程来增强SQL语言的力量，这其中包括增加数组功能、矩阵以及列表处理任务等。例如，我们可以做到如下的事情：

1. 在一个结果集中列中任意多个表中每个表的行数。
2. 使用一个INSERT表达式模板来为每个表执行这个INSERT表达式。
3. 使用不同的字段将一组表连接起来，并显示它们的行数。即使字段的名子不同。
4. 可以为不同组的用户分配不同的权限。或是为每个用户分配所有的权限。

    上面所显示的对于任何SQL库来说都是强大的功能，有了这些功能，我们可以用几分钟完成以前需要几小时才能做完的工作。
    首先、我们需要了解一些关于数学映射方法的概念。在Perl和C#中这些映射是很常见的，由于在这些语言中的这些功能和SQL有机地结合，才使SQL可以发挥出更强大的功能。在本文中，我们将看到一些使用SP_map存储过程的例子。这些功能不仅仅是例子，它们完全可以当作程序中的关键特性被使用。最后，我们将学习如何在本机上安装这些存储过程。

一、映射概述

    映射特性在大多数的功能型编程语言中都可以找到，这种特性一般很少在过程或命令型语言中使用。一个关键的区别是“命令型语言的目的是强调一系列执行步骤来完成每项操作，而功能型语言的目的是对逻辑和功能的安排，而一般不需要清楚地指定步骤”。

    Perl是一种典型的命令型语言，但是它可以很好的被当作功能型语言来使用。在实际工作中，它提供一种非常实用的映射能力。下面是一个非常简单的例子，这个例子将一个整数列表映射成和它们等效的ASCII值，我们只使用三行就可以完成上面的功能，代码如下：

   @chars = map(chr, @nums);
   @chars = map { chr } @nums;
   @chars = map { chr($_) } @nums;

图1 基本的数组映射事例：使用Perl的chr()函数将一个输入数组映射到另一个输出数组中（两个数组相同）
上面代码的map操作符不仅限于当作函数使用，它还可以被当作处理操作符使用，如下面的代码所示：

%hash = map { getkey($_) => $_ } @array;

换句话说，上面一行代码的作用是通过代码块(getkey($_)=>$_)来映射输入数组（@array），并将其保存在哈希表中（%hash）。这行代码和下面的的过程型表达形式等效。

%hash = ();

foreach $element (@array) {

$hash{getkey($element)} = $element;

}

如果我们不了解Perl也没关系。从上面的代码片段中可以猜测到是通过一个循环来扫描数组，并将其逐个填入哈希表。

在Perl中的函数映射表达式显得很复杂，如下面的代码通过一个特殊的被包含在每个记录中的字段来分类结构：

@ordered = map { $_->[1] }

sort { $a->[0] <=> $b->[0] }

map { [compute(), $_] }

@unordered;

现在让我们看一看在C#中与之等效的例子。C#（版本为2.0或更高）提高了可以转换任何数据(ConvertAll)的泛型方法，这个方法和Perl中的map等效。一个C#2.0函数列表处理的代码如下所示：

List outputList =

list.ConvertAll(delegate(string s) { return s.ToLower(); });

在上面的代码中将一个匿名的函数定义成了一个代理(delegate)，并将这个匿名函数传入List对象中的映射方法（ConvertAll），然后返回一个新的List对象，这个新的对象包含了所有原List中被转换为小写字符的元素。而Perl中与之等效的是如下代码所示：

@outputList = map { tr/A-Z/a-z/;$_} @list;

正象Perl可以通过链锁产生复杂的转换，C#也可以。下面这个例子产生了所有比被给定尺寸大的文件列表：

List paths = new List(

Directory.GetFiles(directory));

return paths.ConvertAll( File.OpenRead )

.FindAll( delegate(FileStream f) { return f.Length >= bigLength; } )

.ConvertAll( delegate(FileStream f) { return f.Name; } );

上面的一些代码给出了如何在Perl和C#中使用映射，那么在本文的下面部分将介绍如何在数据库中进行映射处理。

二、在SQL中的基本映射

让我们先看看图2显示的结构图：

图2. 在SQL中的数组映射：首先，我们使用一个模板将一个输入数组值映射到一个输出数组中，然后执行这个输出数组。

上面的转换图可用以下几点描述：
1. 输入是一个表名列表。列表可以是常量或是动态的。

2. 在本例子中的映射函数是一个SELECT模板，这个模板只不过是一个标准的带占位符的SELECT表达式。这些占位符表示要从这个列表中插入的元素。

3. 输出是一个DML表达式集合，这些DML表达式将使用被替换的输入值。

在读本文的下面部分之前，先看一下图3，图3显示了存储过程在操作微软的标准事例数据库AdventureWorks的实际执行结果。

图3 SQL映射执行效果：SP_map将列表中的每一个表名映射到了SELECT模板表达式中，并使用分开的结果集返回

下面是我们需要做实现的SQL代码：

EXEC SP_map

@StmtTemplate = 'select count(*) as Rows from <MAIN_ARG>',

@MainArray = 'Person.Address, Person.Contact,

Production.Culture, Sales.Customer', @Verbose = 1

在上面代码中加入@Verbose标志是为了这个存储过程也可以使用如下的表达式：

select count(*) as Rows from Person.Address

select count(*) as Rows from Person.Contact

select count(*) as Rows from Production.Culture

select count(*) as Rows from Sales.Customer

注意：如果我们没有AdventureWorks数据库，可以使用从任何数据库获得的表名。

@StmtTemplate除了使用标号< MAIN_ARG>是一个标准的DML表达式，这个标号是一个占位符来替换我们的输入列表中的每一个元素。
我们大家都知道，SQL并没有数组列表的概念。因此，SP-map通过了一个单独的字符串参（中间由逗号将元素分开）来模拟一个数组。由于这些代码是直接写在程序中的，因此，属于常量列表。

三、在SQL中的聚合映射

也许我们会有这样的疑问，SP_map对大的列表的处理分怎么样呢。对于上面的例子，假设我们提供了一个包含100个元素的列表。SP_map为每一个被执行的表达式返回一个结果集（也就是对每一个输入元素）。因此，会有很多可滚动的窗口。那么对于数据更多的列表，SP_map提供了一个@Accumulate标志，来将众多小的结果集合成一个。虽然从概念上非常简单，但所不同的是SP_map所做的主要功能将发生改变。这将重写我们的查询代码。在其中将一个新的包含输入元素的列。否则，我们将没有办法将输入和输出相连，如图4所示：
打开@Accumulate标志后，将利用我们的SELECT表达式上的一个约束（就是所有被返回的字段必须被命名）。换句话说，'select count(*) from <MAIN_ARG>'将不再有效，而我们将使用'select count(*) as Count from <MAIN_ARG>'来取代原来的语句，如图4所示：

图4 聚合映射：打开@Accumulate标志后，将合并多个结果集为一个更便利的结果集

如果我们忘记了，SQL Server将会出现一个类似下面的错误：

An object or column name is missing or empty.

For SELECT INTO statements, verify each column

has a name. For other statements, look for

empty alias names.

Aliases defined as "" or [] are not allowed.

Add a name or single space as the alias name.

我们可以通过设置@Verbose标志来查看代码。代码如下所示：

 SELECT Convert(varchar(128),'Person.Address') as ItemName,
count(*) as Rows
INTO #Map_Temp_Table from Person.Address
INSERT INTO #Map_Temp_Table
SELECT 'Person.Contact', count(*) as Rows
from Person.Contact
INSERT INTO #Map_Temp_Table
SELECT 'Production.Culture', count(*) as Rows
from Production.Culture
INSERT INTO #Map_Temp_Table
SELECT 'Sales.Customer', count(*) as Rows
from Sales.Customer
select * from #Map_Temp_Table
drop table #Map_Temp_Table

我们从上面的代码可以看到，SP_map使用了一个中间临时表秋收集独立的结果集。如果我们将verbosity标志从1设置到2，我们会看到更多更清晰的代码（这将显示除了实际执行的代码块外的中间CREATE和UPDATE模板）。SP_map直接从我们的表达式模板产生这些中间模板，然后使用这些中间模板从输入列表中映射数据。CREATE和UPDATE模板的代码如下：

-- TEMPLATE (create):

SELECT Convert(varchar(128),

'<MAIN_ARG>') as ItemName, count(*) as Rows

INTO #Map_Temp_Table from <MAIN_ARG>

-- TEMPLATE (update):

INSERT INTO #Map_Temp_Table

SELECT '<MAIN_ARG>', count(*) as Rows from <MAIN_ARG>

对于我们的输入列表的第一个元素，SP_map使用了CREATE模板动态地定义了一个临时表，并存储第一个结果集。对于后面的部分使用了UPDATE模板来向临时表追加行。当完成时，使用一条SELECT语句从临时表中查出所有的数据。并生成一个单独的结果集，最后删除临时表。

关注我们