存储过程返回结果集_存储过程

在开发SQL Server时，为了修改和扩充方便，经常会将负责不同功能的语句集中起来并且按照用途分别独立存储，以便能够反复调用，这些独立存储且拥有不同功能的语句即是“存储过程”。

存储过程属于数据库对象，是一种高效的、安全的访问数据库的方法。主要用于提高数据库中检索数据的速度，也经常被用来访问数据或管理被修改的数据。存储过程包含一些 T-SQL 语句并以特定的名称存储在数据库中。用户在编程过程中只需要给出存储过程的名称和必需的参数，就可以方便地调用它们。

存储过程处理灵活，允许用户使用声明的变量，还可以有输入输出参数，返回单个或多个结果集以及处理后的结果值。

存储过程的优点：

存储过程可以接受输入参数、 返回表格或标量结果和消息，调用“数据定义语言（DDL）”和“数据操作语言（DML）”语句， 然后返回输出参数。使用存储过程的优点如下：

（1）执行速度快
（2）模块化的程序设计
（3）减少网络通信量
（4）保证系统的安全性
（5）自动完成需要预先执行的任务

存储过程的分类：

（1）系统存储过程
系统存储过程定义在系统数据库 master 中，其前缀是“sp_”，例如，常用的显示系统对象信息的“sp_help”系统存储过程，为检索系统表的信息提供了方便快捷的方法。系统存储过程允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。
（2）扩展存储过程
是在SQL Server之外，使用编程语言创建的外部例程形成的动态链接库（DLL）。使用时，先将 DLL 加载到 SQL Server系统中，并且按照使用系统存储过程的方法执行。扩展存储过程在 SQL Server 实例地址空间中运行。但因为扩展存储过程不易撰写，而且可能会引发安全性问题。所以不在讲解
（3）用户存储过程
用户存储过程可以使用 T-SQL 语言 编写，也可以使用 CLR 方式编写。在本书中，T-SQL 存储过程就称为存储过程。
1.存储过程：存储过程保存 T-SQL 语句集合，可以接受和返回用户提供的参数。存储过程中可以包含根据客户端应用程序提供的信息，在一个或多个表中插入新行所需的语句。 存储过程也可以从数据库向客户端应用程序返回数据。
2.CLR存储过程：CLR存储过程是对 Microsoft .NET Framework公共语言运行时（CLR） 方法的引用，可以接受和返回用户提供的参数。它们在“.NET Framework 程序集”中是作为 类的公共静态方法实现的。简单地说，CLR 存储过程就是可以使用 Microsoft Visual Studio 2005 环境下的语言作为脚本编写的、可以对 Microsoft .NET Framework 公共语言运行时 （CLR）方法进行引用的存储过程。

存储过程的创建与执行

使用T-SQL命令创建存储过程

语法格式如下：

create proc[edure] procedure_name
@<参数名><数据类型>[,..n] [output]
with
{recompile | encryption | recompile, encryption}]
as SQL_statement[...n]

参数说明：recompile：表明不保存该存储过程的执行计划，该存储过程将在执行时重新编译

encryption：指定SQL Server对syscomments表中包含本create procedure语句文本的条目进行加密

SQL_statement：存储过程中要包含的T-SQL语句

注意：存储过程名不要使用“sp_”作为名称前缀，“sp_”前缀用于标识系统存储过程。

存储过程的调用执行：如果是批处理的第一句，则可以直接使用存储过程的名字进行调用；如果不是第一句则使用execute（exec）关键字来完成

如果是带参数的存储过程，必须给出具体的参数值，传递给输入参数，语法格式为：

exec <存储过程名>[<参数值>[，..]]

若带输出参数，则必须接受输出参数的值，一般定义变量来接受，也就是要将存储过程输出参数的值，传递给变量，语法格式为：

例题1：创建一个查询各班人数的存储过程，将结果插入到一个新表中class_num（班级编号 nchar(10),总人数 smallint）并调用它。

源代码如下：

use SCC
go
create table class_num
(
	班级编号 nchar(10),
	总人数 smallint
)
go
create proc 各班人数
as
	begin
		truncate table class_num
		insert into class_num(班级编号,总人数)
		(select classno,count(classno)
		from student
		group by ClassNo)
	end
go
各班人数
go

例题2：创建存储过程p_班号找学生,该存储过程能够根据给定的班级编号，返回该班级编号对应的所有学生信息，并查询0004班的所有学生信息

源代码如下：

use SCC
go
create proc p_班号找学生 @bjbh nvarchar(10)
as
	begin
		select *
		from student
		where classno=@bjbh
	end
go
exec p_班号找学生 '0004'
go

结果如下：

例题3：创建存储过程p_各班人数，能够通过给定的班级编号，将人数返回，并以0004班为例

源代码如下：

use SCC
go
create proc p_各班人数
@classno nvarchar(10),@renshu smallint output
as
begin
	set @renshu=
	(
		select count(classno)
		from student
		group by classno
		having classno=@classno
	)
	print @renshu
end
go
declare @classno nvarchar(10),@renshu smallint
set @classno='0004'
exec p_各班人数 @classno,@renshu output
select @renshu
go

结果如下：

通过系统存储过程sp_helptext可显示规则，默认值，未加密的存储过程，用户定义函数，触发器或视图的文本

执行如下语句：exec sp_helptext 对象名

修改和删除存储过程

（1）修改存储过程的方法与之前我们学习的修改数据库对象的方法一致，在创建的语法基础上将create改为alter，进而对存储过程的定义进行修改

（2）删除存储过程使用的是drop关键字，格式为：drop 存储过程名

例题4：删除SCC数据库中的student_info1存储过程

源代码如下：

use SCC
go
if exists(select name from sysobjects where name='student_info1')
  drop procedure student_info1

界面方式操作存储过程（与界面方式操作用户定义函数相同，不在进行讲解）

自定义函数和存储过程的区别

（1）自定义函数

可以返回表变量；限制颇多，包括不能使用 output 参数；不能用临时表；函数内部的操作不能影响到外部环境；不能通过 select 返回结果集；不能 update，delete，数据库表；必须 return 一个标量值或表变量。自定义函数一般用在复用度高，功能简单单一，争对性强的地方。

（2）存储过程

不能返回表变量；限制少，可以执行对数据库表的操作，可以返回数据集；可以 return 一个标量值，也可以省略 return；存储过程一般用在实现复杂的功能，数据操纵方面。