SQL Server 系统存储过程的应用-技术开发专区

SQL Server 系统存储过程的应用

作者：IT168 PandaRen 编辑：胡铭娅 2007-08-22 00:00

【IT168 技术文档】SQL Server系统存储过程数量众多，不下数百个，本文只是根据自身使用的经验，对一些常用的存储过程进行讲解。我将这些存储过程根据功能进行了一定的分类，对重要的存储过程的功能、性能和特点等进行说明，并且结合实例（SQL脚本）进行讲解，文中少量示例脚本摘自MSDN。
本文旨在对SQL Server的存储过程进行由浅入深的讲解，以便对不同水平的用户提供帮助。对于SQL Server系统存储过程尚不甚了解的用户，通过文章的介绍，能够对SQL Server的存储能够为我们做什么有一个大致的了解，以便日后遇到问题时候能够寻求到合适的解决方案；而对于系统存储过程有一定了解的用户，本文将通过大量实例，让用户能够很快的理解以及掌握这些存储过程的用法。
本文的结构首先将若干功能分类列出，然后在各功能中对相关的存储过程进行讲解，重要的存储过程进行详细阐述并进行举例示范。
注意：这些系统存储过过程，未经注明的都是存放于Master数据库，在SQL2000中，其Owner是“dbo”, 而在SQL2005中，owner改为了“sys”, 并且在用户数据库中也可以直接调用，但是SQL Server 2005依然支持使用Master.dbo. [procedureName]或者Master.. [procedureName]的方式来调用。
1. 重命名数据库对象
我们知道，一般的ALTER语句并不支持对目标或者子对象进行更名，当然ALTER DATABASE除外，ALTER DATABASE支持改名，如下语句所示。

ALTER DATABASE oldDB Modify Name=newDB

用户表，行列，存储过程等对象只能通过先Drop后重建的方法来实现改名，并且还可能涉及到要复制数据的问题。但是通过使用sp_rename，我们可以对所有的数据库对象都进行重命名。需要注意的是，改名可能会破坏一些相关存储过程或者触发器的完整性，这个时候你就需要手动更新他们了。
重命名对象sp_rename
定义

示例
下面的脚本演示了重命名一列和一个数据表的过程。

-创建一个测试表 
Create table tbl_Test(a int) 

--重命名列 
sp_rename 'tbl_Test.a', 'b','COLUMN' 

--重命名表 
sp_rename 'tbl_test', 'tbl_test2','object'

2. 外部命令与文件操作
（1）外壳命令xp_cmdshell
使用xp_cmdshell，我们可以像执行DOS命令那样采用命令行方式来执行外部命令，包括象dir、mkdir和copy之类的系统命令，也能够执行普通的exe命令。通过xp_cmdshell执行命令的权限和运行SQL Server服务的用户权限一样大。通过xp_cmdshell, 我们一下就把数据库的功能进行了极大的延展，由于command命令的灵活性，我们可以用它来创建目录，拷贝，压缩文件，甚至调用自己写的一些控制台程序或者脚本。
注意：这里使用SQL2005的用户需要注意，出于安全的考虑，SQL2005安装以后默认并不启用xp_cmdshell，也就是说它默认是被禁制使用的。但是我们可以通过如下方法打开启用这个功能强大又危险的存储过程：
从菜单所有程序, 通过点击”Microsoft SQL Server 2005 => Configuration Tools（配置工具） => SQL Server Surface Area Configuration（外围配置管理器）”，打开Surface Area Configuration 的管理程序，点击”Surface Area Configuration for features”, 将xp_cmdshell的选激活即可。
定义

示例
显示当前文件夹的的文本文件。

EXEC master..xp_cmdshell 'dir c:\*.txt'

存储过程会将命令的文字输出通过多行记录集的方式进行返回，如下。
驱动器 C 中的卷是 SYSTEM
卷的序列号是 0482-xxxx
NULL
c:\ 的目录
NULL
2006-08-05 20:32 13,649 cmd.txt
2006-08-05 20:32 4 response.txt
2004-06-25 09:31 97,355 debug.txt
….
2005-12-03 19:26 27,005 cdnlog.txt
9 个文件 147,173 字节
0 个目录 3,316,301,824 可用字节
NULL
当然，我们还可以用它做更多的事情，如下代码演示了一个利用net命令来关闭SQL Server服务的过程，它向域中的SQL用户发送通知消息，并且暂停和停止数据库。

CREATE PROC shutdownSQLServer 
AS 
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server 
shutting down in 10 minutes. No more connections 
allowed.', no_output 
EXEC xp_cmdshell 'net pause sqlserver' 
WAITFOR DELAY '00:05:00' 
EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server 
shutting down in 5 minutes.', no_output 
WAITFOR DELAY '00:04:00' 
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server 
shutting down in 1 minute. Log off now.', no_output 
WAITFOR DELAY '00:01:00' 
EXEC xp_cmdshell 'net stop sqlserver', no_output

（2）文件访问xp_fileexist
文件访问的相关存储过程众多，比如xp_fileexist, xp_create_subdir, xp_delete_file, xp_delete_file等等。由于xp_cmdshell可以执行一些文件相关的创建，删除的操作，这里主要对xp_fileexist进行详述。
xp_fileexist是用来检查文件或者文件夹是否存在的，显然分析由xp_fileexist返回的结果比分析由执行Exec xp_cmdshell ‘dir filename’返回的文本信息来得轻松并且可靠得多。Xp_fileexist支持两种方式返回结果，一个是通过输出参数@File_Exists OUT来返回结果，另外一种方式就是不指定输出参数，那么它将返回一个有三列结果集, 分别代表文件是否存在文件是一个存在的目录，父目录是否已存在。
? 定义

示例
这里的"C:\bt"是一个存在的目录，我们来看看执行后会有什么结果。

exec xp_fileexist 'C:\bt\'

返回的结果是0, 1, 1。这表示"C:\bt"是一个存在的目录，并且父目录也存在。
这里的"C:\test.txt"是一个普通文件，我们再来看看执行这句脚本有什么结果。

exec xp_fileexist 'c:\test.txt

返回的结果是1, 0, 1。这表示"C:\test.txt"是一个存在的文件，并且父目录也存在。
当然我们也可以通过output参数的方式来获取结果，让我们看看下面的脚本运行会有什么结果。

declare @File_Exists int 
exec xp_fileexist 'C:\bt\', @File_Exists OUT 
print @File_Exists 
exec xp_fileexist 'c:\test.txt', @File_Exists OUT 
print @File_Exists

结果是:

(1 行受影响) 
0 
(1 行受影响) 
1

我们发现通过output参数，只能够检查文件是否存在，并不能检查目录是否存在，所以具体使用哪种输出方式，要根据你的需要而定。
3. 发送电子邮件
一个使用数据库的系统，无论是一个web服务还是一个其他的应用系统，都非常有机会需要通过发送邮件的方式和用户交互。如果能够在我们的trigger或者存储过程中直接发送邮件，那么将会是非常的方便。SQL Server给我们提供了很多选择:
1) 自己写一个XP（扩展存储过程）来发送邮件，这将是一件非常令人头疼的事情，你需要了解邮件通信的机制和协议，或者利用一些现成的API和接口来实现你的存储过程。最后写出来的XP也许是一个功能不够强大又非常不稳定的东西。所以对于普通用户，不推荐使用此方法。
2) SQL Mail: 基于MAPI，特点是能够将查询结果和邮件一起发送。但是如果你希望你的程序能够移植到SQL Server 2005以后的版本，那么建议你不要使用它。因为微软已经宣称要从以后的版本中移除这个功能了。
3) Database Mail: SQL Server 2005中的新特性，功能强大，支持通用的SMTP协议，如果你使用的是SQL Server 2005, 那么推荐你使用它。
4) OLE Automation: 利用OLE Automation创建CDO.Message来发送邮件，如果你的数据库版本不支持Database Mail，而且也想通过SMTP灵活的发送邮件，那他它也是一个不错的选择。
SQL Mail
SQL Mail 是基于MAPI的，需要数据库服务器安装有Outlook应用程序。SQL Mail的最大特点就是能够将查询结果作为邮件内容或者附件进行发送。发送SQL Mail使用的存储过程是xp_sendmail。
根据微软的资料，SQL Mail的功能将会在未来的版本中被移除，而推荐使用的是SQL Server 2005 的Database Mail功能；另外SQL Mail还需要配置Outlook邮件账户，加上本身功能受一定的局限性，因此不推荐使用。
注意：SQL2005中需要从Surface Area Configuration中将SQL Mail选项打开。
定义

• 示例
下面的代码演示如何创建一个账户和一个档案文件，并且降账户分配给Profile(档案文件)。

-- 创建邮件账户 
EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = 'MyMailAccount', 
@description = 'Mail account for use by all database users.', 
@email_address = 'test@sina.com', 
@replyto_address = 'test@sina.com', 
@display_name = 'Mail Tester', 
@mailserver_name = 'stmp.sina.com', 
@username = 'test', 
@password = 'password' ; 

-- 创建邮件用户档案文件 
EXECUTE msdb.dbo.sysmail_add_profile_sp 
@profile_name = 'MyProfile', 
@description = 'Profile used for administrative mail.' ; 

-- 将账户加入到档案文件中 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
@profile_name = 'MyProfile', 
@account_name = 'AdventureWorks Public Account', 
@sequence_number =1 ; 

-- 将访用户档案的权限赋予给msdb数据库的所有用户 
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
@profile_name = 'MyProfile', 
@principal_name = 'public', 
@is_default = 1 ;

Profile创建好了，我们就可以利用它来发邮件了。

-- 账户配置好以后，我们就可以发送邮件了 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'MyProfile', 
@recipients = 'target@hotmail.com', 
@body = 'The stored procedure finished successfully.', 
@subject = 'Hello!' ;

 利用OLE Automation 存储过程创建并发送邮件
注意：SQL2005中需要从Surface Area Configuration中将OLE Automation选项打开
OLE Automation功能使得我们能够象asp一样创建很多基于COM的对象，使SQL的功能得到了很大的扩展。但是在它本身也存在一些缺陷，在SQL Server中对这些对象的属性赋值以及方法调用，都需要通过存储过程间接实现，效率相对低下，并且比较麻烦，不过偶尔用用还是不错的
利用OLE Automation来发送邮件的步骤同样比较繁琐，首先创建一个CDO.Message对象，通过sp_OASetProperty 设置Message对象的各种属性，包括SMTP服务器的信息，还有邮件本身的信息包括标题、正文和收件人等。最后利用sp_OAMethod添加附件，进行发送操作；最后发送完毕后还需要将创建的对象进行删除。总之是非常的麻烦，不过如果将其封装为一个自定义存储过程的话，用起来还是能方便很多的。
• 示例
下面的代码演示了如何通过OLE Automation来发送邮件，为了简化代码，这里没有进行任何错误处理。

declare @iMsg int 
-- 创建CDO.Message 对象 
EXEC sys.sp_OACreate 'CDO.Message', @iMsg OUT 

-- 配置消息对象 
--这里我们配置一个远程邮件服务器 
EXEC sys.sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2' 

-- 配置smpt服务器地址和端口. 
EXEC sys.sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '127.0.0.1' 
EXEC sys.sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', 25 

-- Save the configurations to the message object. 
EXEC sys.sp_OAMethod @iMsg, 'Configuration.Fields.Update', NULL 
-- Set the e-mail parameters. 
EXEC sys.sp_OASetProperty @iMsg, 'MimeFormatted', FALSE 
EXEC sys.sp_OASetProperty @iMsg, 'To', 'receiver@hotmail.com' 
EXEC sys.sp_OASetProperty @iMsg, 'Subject', 'Hello!' 
EXEC sys.sp_OASetProperty @iMsg, 'TextBody', 'This is an Email from SQL Server!' 

--send the mail 
EXEC sys.sp_OAMethod @iMsg, 'Send', NULL 
-- 清除创建的对象. 
EXEC sys.sp_OADestroy @iMsg

4. 执行动态SQL语句
这里首先解释一下什么叫做动态SQL语句，这里说的动态SQL语句指的就是在SQL脚本比如存储过程中动态组装的SQL语句。比如一条查询语句，根据条件的不同，我们选择的表名、列或者where条件都可能不同。
SQL Server中有两个可以执行动态SQL语句的存储过程，sp_sqlExec和sp_executesql。sp_executesql 与sp_sqlExec一个明显区别是，sp_executesq可用通过参数方式来执行SQL，并且能够返回动态SQL语句的输出参数。
 sp_sqlExec
这个存储过程在作用其实是和使用Execute/Exec表达式执行动态SQL的作用是一样的。也就是说Sp_sqlExec(@statement) 和 Exec(@statement) 的作用一样，这个通过查看Sp_sqlExec的创建脚本能够得到验证。
需要注意的是，Sp_sqlExec和Exec所执行的sql脚本的上下文和调用Sp_sqlExec的上下文是不同的，也就是说在当前调用的Sp_sqlExec的程序中声明的变量并不能从Sp_sqlExec执行的SQL语句中访问。
• 示例
如下的代码演示了一个动态SQL语句被组装的过程。这个存储过程的功能是返回tbl_Test中的数据，并且根据提供的字段进行排序。

create procedure sp_GetTestData 
(@orderByField as varchar(20)=null) 
as 
begin 

declare @statement varchar(1024) 
set @statement = 'select * from tbl_Test' 

if @orderByField<>null 
begin 
set @statement = @statement+' order by ' + @orderByField 
end 
exec sp_sqlexec @statement 
end

 sp_executesql
这个以“sp”开头的存储过程实际上是一个“xp”(extended stored procedure)，有点挂羊头卖狗肉的味道。Microsoft还有很多其他这样的以”sp”开头的“xp”，想必自有他们的道理，不过我暂时还没有弄清楚。
sp_executesq通过参数替换的方式，也可以用来执行一些查询结构固定而仅仅是参数不同的SQL语句，相对于sp_sqlExec/Execute执行一个组装好的sql字符串来说，通过参数替换方式执行sp_executesql还有如下优势:
1) 多次执行的性能得到优化。如果多次调用SQL语句，我们只需要改变输入参数的取值，而传入的T-SQL脚本是和上一次完全一样的，查询优化器就能够那第二次执行的T-SQL脚本和第一次执行的SQL脚本进行匹配，并且执行上一次产生的执行计划，这样SQL Server就不需要再对这条语句进行重新编译，从而获得更高的效率。
2) T-SQL 语句只需要一次就能够组装好，也就是不需要将一些固定的SQL表达式和一些变量进行组装。
3) 非字符串类型的变量比如整形变量能够保持本来的形式。另外普通字符串也不需要进行象Unicode字符串的转化了。
• 定义

• 示例
下面的脚本演示了如何通过参数的方式来调用存储过程，并且获取输出参数的结果。

DECLARE @IntVariable int; 
DECLARE @SQLString nvarchar(500); 
DECLARE @ParmDefinition nvarchar(500); 
DECLARE @max_title varchar(30); 

SET @IntVariable = 197; 
SET @SQLString = N'SELECT @max_titleOUT = max(Title) 
FROM AdventureWorks.HumanResources.Employee 
WHERE ManagerID = @level'; 
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT'; 

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT; 
SELECT @max_title;

5. 数据库用户进程管理
在使用数据库的过程中，我们经常会发现数据库中某个数据表被锁定导致更新挂起，或者对数据库进行更名或者恢复操作的时候失败，其原因往往是有其他的用户正在对数据库进行操作。我们利用sp_who就可以查看某个数据库实例的当前的用户和进程。
• 定义

• 例子
我们现在要对TestDB进行恢复操作，却发现恢复失败了。于是我们使用下面的语句可以用于显示当前数据库中所有的用户进程。

Exec sp_who

结果如下：

从上面的结果我们可以发现51号进程的user1正在client1上正在对TestDB进行更新操作。于是我们可以通知使用者停止使用testDB或者直接使用用命令强行杀掉此用户进程。

kill 51.

6. 后记
掌握对SQL Server的系统存储过程的应用，对我们完成一些功能有着很大帮助，并且提供了一个解决问题的思路：一些功能如果不使用这些系统存储过程，可能实现起来会非常麻烦，但是如果能够通过恰当的系统存储过程并且通过恰当的方式来实现，往往会达到事半功倍的效果。对这些知识的掌握也在于平时的积累，等到需要的时候，就能够信手拈来。

关注我们