存储过程

 

存储过程

存储过程是一组预先写好的能实现某种功能的T-SQL程序，也是一种数据库对象，是在数据库应用中运用得十分广泛的一种数据对象。

1． 存储过程简介

存储过程的运用情况比较广，可以包含几乎所有的T-SQL语句，如数据存取语句、流程控制语句、错误处理语句等，使用起来十分有弹性。

1） 存储过程（Stored Procedure）是一组预先写好的能实现某种功能的T-SQL程序，指定一个程序名并由SQLServer编译后将其存在SQLServer中，以后要实现该功能，则可以调用这个程序来完成。用户可以通过存储过程的名字并给出参数（如果该存储过程有参数的话）来执行它。

2） 使用存储过程的优点

使用存储过程有以下几个优点：

A. 执行速度快效率高

因为SQLServer2005会实现将存储过程编译成二进制可执行代码，那么在运行存储过程时，SQLServer2005不需要再对存储过程进行编译，可以加快执行的速度。

B. 模块式编程

存储过程在创建完毕之后，可以在程序中多次被调用，而不必重新编写该T-SQL语句。在创建存储过程后，也可以对存储过程进行修改，而且一次修改之后，所有调用该存储过程的程序所得到的结果都会被修改，提高了程序的可移植性。

C. 减少网络流量

由于存储过程是存在数据库服务器端上的一组T-SQL，在客户端调用时，只需要使用一个存储过程名及参数即可，那么在网络上传送的流量比传送这一组完整的T-SQL程序要小得多，所以可以减少网络流量，提高运行速度。

D. 安全性

存储过程可以作为一种安全机制来使用，当用户要访问一个或多个数据表，但没有存取权限时，可以设计一个存储过程来存取这些数据表中的数据。而当一个数据表没有设权限，而对该数据表操作又需要进行权限控制时，也可以使用存储过程来作为一个存取通道，对不同权限的用户使用不同的存储过程。

3） 存储过程的分类

在SQLServer2005中，存储过程可以分为3大类：

A. 系统存储过程（System Stored Procedures）:一般是以“sp_”为前缀的，是由SQLServer2005自己创建、管理和使用的一种特殊的存储过程，不要对其进行修改或删除。从物理意义上讲，系统存储过程存储在Resource数据库中，但从逻辑意义上来说，系统存储过程出现在系统数据库和用户自定义数据库的sys架构中。

B. 扩展存储过程（Extended Stored Procedures）:通常是以“xp_”为前缀。扩展存储过程允许使用其他编辑语言（如C#等）创建自己的外部存储过程，其内容并不存在SQLServer2005中，而是以DLL形式单独存在。不过该功能在以后的SQLServer版本中可能会被废除，所以尽量不要使用。

C. 用户自定义存储过程（User-defined Stored Procedures）:由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出参数，在SQLServer2005中，用户自定义存储过程又分为T-SQL存储过程和CLR存储过程两种。

l T-SQL存储过程：保存T-SQL语句的集合，可以接受和返回用户提供的参数。

l CLR存储过程：该存储过程是针对微软的.NET Framework公共语言运行时（CLR）方法的引用，可以接受和返回用户提供的参数。CLR存储过程在.NET Framework程序中是作为公共静态方法实现的。

2． 创建存储过程

在SQLServer2005中，可以用SQLServer Management Studio和T-SQL语言来创建存储过程，在创建存储过程时，要确定存储过程的以下3个组成部分。

1） 输入参数和输出参数

2） 在存储过程中执行的T-SQL语句

3） 返回的状态值，指明执行存储过程是成功还是失败

一个存储过程就是一个批处理，在遇到go时，查询编辑器就会认为该存储过程的代码已经结束。运行存储过程也是用EXEC语句。运行存储过程与运行存储过程中的Select语句的结果是一样的。

3． 修改存储过程

存储过程是一段T-SQL代码，在使用过程中，一旦发现存储过程不能完成需要的功能或功能需求有所改变，则需要修改原有的存储过程。

4． 执行存储过程

对于一个有输入参数的存储过程，要如何执行它呢？

有以下几种方法：

1） 对于存储过程：

create procedure example1

@p1 int = 1

as

begin

set nocount on;

select * fronm product where categroyID=@p1

order by proName

end

 

执行代码：exec example1，所有的查询结果都是ID为1的记录，这是因为存储过程中指定了@p1 int = 1。即：党没有指定@p1参数值的时候，默认@p1的值为1。所以查询出来的结果都是categroyID为1的记录。

2） 如果在创建example1存储过程时，没有指定@p1的默认值，如：

create procedure example1

@p1 int

as

begin

set nocount on;

select * fronm product where categroyID=@p1

order by proName

end

此时，如果再运行exec example1，将会出现错误提示。正确的方法代码是：exec example1 3或者exec example1 @p1=3

 

Exec是execute的简写，execute命令可以用来执行存储过程或函数。

5． 设计存储过程的高级技巧

在设计存储过程中有很多的技巧，下面介绍几种常用的。

1） 设计存储过程时要注意的一些事项

在设计存储过程时，必须注意以下事项：

A. 虽然在设计存储过程时，可以包含任意数据和类型的T-SQL语句，但是表1所列的语句在设计存储过程时不能使用。

表1：不能再存储过程中使用的语句

Create aggregate

Create function

Create procedure

Create rule

Create trigger

Create view

Set parsonly

Set showplan_text

Use database_name

Create default

Alter function

Alter procedure

Create schema

Alter trigger

Alter view

Set showplan_all

Set showplan_xml

 

B. 除了表1中所列出的语句之外的所有其他建立对象的语句都可以在存储过程中使用，包括创建数据表等语句，也可以创建和使用临时表。如果是在存储过程中创建的本地临时表，那么只要存储过程退出后，临时表也就会被删除。

C. 在存储过程中，不但可以创建对象，还可以引用在该存储过程中已经定义过的对象。

D. 如果再一个存储过程中调用另一个存储过程，那么被调用的存储过程可以使用调用的存储过程里创建的对象，包括临时表。

E. 如果再存储过程中包含对远程SQLServer2005实例进行更改的T-SQL语句，一旦该语句执行后不能回滚。

F. 存储过程中的参数的最大数目为2100个，但存储过程中的局部变量的最大数目受内存的限制。

G. 存储过程最大可达128M。

H. 在存储过程里，如果T-SQL语句没有指定对象的架构名，则将存储过程的架构默认为该T-SQL语句里的对象的架构名。例如有一个名为“test”的存储过程，它所属的架构是“guest”，该存储过程里有个T-SQL语句“select * from products”，那么在执行“guest.test”存储过程时，会运行“select * from products”语句，当“guest.products”表找不着时，系统会去查找“dbo.products”表，再找不到时，才会报错。

2） 参数传递的方式

存储过程可以包含参数，在执行存储过程时，如果不指明参数名称，则按存储过程所定义的参数次序传递。如果在存储过程里定义了参数的默认值，并且放在最后，则可以不指定该参数。

例：创建一个存储过程，输入类别名称、单价、库存量和订购量后，可以查看该类型中大于该单价、库存量和订购量的产品，其代码如下：

create proc example2

@类别名称 varchar(15),

@单价 money=$10,

@库存量 smallint,

@订购量 smallint=5

AS

Begin

Select * from 产品 Join 类别

On 产品.类别ID=类别.类别ID

Where (类别.类别名称=@类别名称)

And (产品.单价>@单价)

And (产品.库存量>@库存量)

And (产品.订购量>@订购量)

End

Go

执行该存储过程的方法如下：在这种执行方法中，是将存储过程中所需的参数依次传递给存储过程，即列类别名称为“饮料”、单价大于1、库存量大于10、订购量大于5的所有产品。

Exec example2 饮料,1,10,20

如果不按参数顺序传递参数，则要指定参数名，如以下代码：

exec example2 @单价=1,@订购量=20,@库存量=10,@类别名称=饮料

或者

exec example2 @单价=1,@订购量=20,@库存量=10,@类别名称=’饮料’

在传递参数时，也可以用default来代表默认值，如以下代码是可以正确执行的：

Exec example2 饮料,default,10,default

由于example2存储过程的最后一个参数定义了默认值，所以可以将其省略，例如：

Exec example2 饮料,default,10

由于example2存储过程中有一个参数定义了默认值，但并不是排在最后的参数，如果要省略这两个有默认值的参数，就必须要指定参数名，如：

exec example2 @库存量=10,@类别名称=’饮料’

如果在设计example2存储过程时，将两个有默认值的参数都放在最后，则不指定参数明也可以省略那两个有默认值的参数，如：

create proc example3

@类别名称 varchar(15),

@库存量 smallint,

@单价 money=$10,

@订购量 smallint=5

AS

Begin

Select * from 产品 Join 类别

On 产品.类别ID=类别.类别ID

Where (类别.类别名称=@类别名称)

And (产品.单价>@单价)

And (产品.库存量>@库存量)

And (产品.订购量>@订购量)

End

Go

省略参数的运行代码为：exec example3饮料,10

3） 存储过程的返回值

存储过程与其他的编程语言中的过程十分类似，其可以接受输入参数并以输出参数的形式向调用它的过程返回多个值，也可以向调用它的过程返回状态值，以说明该存储过程运行成功或失败。在执行存储过程时，可以有3种不同的返回值：

A. 在存储过程中，以“return n”的形式返回一个整数值

B. 在存储过程中，指定一个output的返回参数以返回值

C. 在存储过程中执行T-SQL语句返回数据集，如select语句

例：创建一个存储过程，返回产品表中的所有产品的库存量。

create proc example4

AS

Begin

Declare @返回值 int

Select @返回值=sum(库存量) from 产品

return @返回值

End

Go

接受这个返回值必须要用变量来接收，如：

declare @接收值 int

exec @接收值 = example4

print @接收值

上面例子中返回的值也可以用output参数来返回，可以改为：

create proc example5

@返回值 int output

AS

Begin

Select @返回值=sum(库存量) from 产品

End

Go

接受output的返回值也必须要用变量，如：

declare @接收值 int

exec  example5  @接收值 output

print @接收值

注意：return 返回的是整数，output可以返回任何数据。

如果让return返回非整数值，在创建存储过程时不会出错，但是运行存储过程时将会出错，如将上面的例子改为：

create proc example6

AS

Begin

Declare @库存 int

Select @库存=sum(库存量) from 产品

return ‘库存量为：’+cast(@库存 as varchar(10))

End

Go

再执行以下代码：

declare @接收值 int

exec   @接收值=example6

print @接收值

将会出现错误信息。

第三种返回值是直接返回到执行存储过程的应用程序中，无法在批程序中使用，只能在批程序的结果中将其显示出来。

例：建立一个存储过程，用于查看某个类别的所有产品，其代码如下：

create proc example7

@类别名称 nvarchar(15)

AS

Begin

Select 产品ID, 产品名称 from 产品

Join 类别 On 产品.类别ID=类别.类别ID

Where类别.类别名称=@类别名称

End

Go

Exec example7 N’饮料’

4） 不显示影响行数的通知

在查询编辑器里执行查询或修改得T-SQL语句或执行的存储过程里含有查询或修改得T-SQL语句时，都会返回影响了多少行记录的语句。然而有些时候，并不希望返回这些信息，以免干扰应用程序的运行，也可以减少带宽，此时可以将影响的行数信息关闭，关闭方法为：

set nocount on

使用了该命令后，再运行查询或修改得T-SQL语句，都不会在显示影响了多个记录的语句了。如果要再次显示影响记录行数的语句，将ON改为OFF即可。

5） 临时存储过程

临时存储过程与临时数据表的功能相同，都是因为需要而创建的数据库对象。临时存储过程也是存放在tempdb数据库中，当使用临时存储过程的用户都断开连接后，临时存储过程也会被自动删除。

与临时数据表相同，临时存储过程也可以分为局部临时存储过程和全局临时存储过程两种。局部临时存储过程的命名是以#开头，只有建立它的用户可以使用，当建立它的用户断开连接后，将会自动删除。全部临时存储过程的命名是以##开头，所有用户都可以使用，但当建立它的用户断开连接后，其他用户就不能再执行该存储过程，已经在执行中而未执行完的存储过程可以继续执行，只有当所有的用户的执行都结束后，系统才会将全部临时存储过程删除。

创建临时存储过程的方法与创建正常的存储过程方法一样，只是存储过程的命名不同。

Create proc #临时存储过程

As

Select * from 产品

Go

Exec #临时存储过程

 

6） 嵌套调用存储过程

在一个存储过程中还可以执行另一个存储过程，称之为嵌套。存储过程可以多次嵌套，最多可以嵌套到32层。如果超过32层的嵌套将会导致整个调用链失败。正在执行的存储过程的当前嵌套层数可以使用@@NESTLEVEL来查看。

 

7） 查看存储过程的依赖关系

8） 加密存储过程

存储过程与视图一样，可以通过加密的方式来保护其代码的安全，加密后存储过程可以正常使用，但是无法查看该存储过程的内容。在SQLServer Management Studio中也不能修改该存储过程的内容，只能通过Alter语句来修改。

create proc example8

@类别名称 nvarchar(15)

with excryption

AS

Select * from类别 

Where 类别名称=@类别名称

Go

9） 查看存储过程的源代码

在SQLServer2005里，可以通过系统存储过程“sp_helptext”来查看存储过程的源代码，其使用方法为：

sp_helptext 存储过程名

需要注意的事，使用sp_helptext也不能查看加密的存储过程源代码。如：exec example8，会显示“对象example8的文本已加密”

10） 设计存储过程组

在SQLServer2005里，允许将存储过程分组，将多个存储过程放在同一个组中，可移植性相关或不相关的数据库操作，当然删除存储过程组时，将会把存储过程组中的所有存储过程都删除。

例：创建两个存储过程，一个是查询类别表中所有的数据，一个是根据类别名称来查询类别表里的数据，由于两个存储过程都是对类别表进行查询操作，所以将其放在他同一个过程组中，其代码如下：

create proc example9;1

AS

Select * from类别 

Go

 

create proc example9;2

@类别名称 nvarchar(15)

AS

Select * from类别 

Where 类别名称=@类别名称

Go

 

创建完存储过程组后，在SQLServer Management Studio里只能看到一个名为example9的存储过程组，而看不到里面包含了多少个存储过程。存储过程组和存储过程的图标也是一样的。

如果要运行example9中的第一个存储过程，可以使用以下代码：

exec example9;1

或

exec example9

存储过程组编号为1的存储过程的编号可以省略，而该存储过程组中的其他存储过程的编号不能省略。若要运行存储过程组的第二个存储过程，则要用以下代码：

exec example9;2 饮料