Sql Server 存储过程实例讲解

一、遇到的问题 我们在之前学习的课程写了不少的批处理语句，这些批处理语句存在两个问题： 1.没法像函数那样传参数运行(自定义化) 2.没法像函数那样可以反复地调用(功能化模块化) 说到这里，我们可以猜到，数据库中应该可以建立函数形式的数据库对象来解决

 　　一、遇到的问题

　　我们在之前学习的课程写了不少的批处理语句，这些批处理语句存在两个问题：

　　1.没法像函数那样传参数运行(自定义化)

　　2.没法像函数那样可以反复地调用(功能化模块化)

　　说到这里，我们可以猜到，数据库中应该可以建立函数形式的数据库对象来解决这样的问题。但是介绍这种数据库对象之前，我们再来看几个需要解决的问题：

　　我们已经学会把一条select语句封装在视图中，但是它只能用来查询，如果我们希望进行其它操作，比如增删改记录、建删库表等，是不能用视图来完成的。

　　另一方面，如果把一个带有更改操作的批处理整个存储成数据库对象，还可以把处理数据的程序移动到离数据尽可能近的地方，而不是总把这些操作写在客户端程序(如Java、C#)中。通过将处理数据的程序从客户应用程序移动到服务器，可以降低网络流量，并提高性能和数据的完整性。

　　二、存储过程的概念

　　解决上面的问题，我们可以使用一种叫做“存储过程”的数据库对象。

　　存储过程(Stored Procedure) 把我们经常用到的一串复杂sql语句保存成一个数据库对象，并给它起一个名字。每次使用存储过程只需要使用如下的形式即可：

　　exec proc 存储过程名

　　存储过程还可以带参数运行：

　　exec proc 存储过程名 参数值[, 参数值...]

　　? 存储过程并不神秘，它就是批处理。之前提到视图是保存在服务器上的命名select语句，与之类似，存储过程是保存在服务器上的命名批处理，，系统将预先对它进行编译。

　　? 存储过程可以包含几乎所有的T-SQL语句，如数据存取语句、流程控制语句、错误处理语句等，使用起来弹性很大。

　　? 数据库中也存在着系统函数和用户定义函数 这两种对象，用户定义函数的功能和存储过程很像，但是有一定的区别。

　　【存储过程的分类】

　　- 系统存储过程 system stored procedure 前缀sp_ 例如sp_help sp、helpdb

　　- 扩展存储过程 extended stored procedure 前缀xp_ 例如xp_cmdshell

　　- 用户自定义存储过程 user-defined stored procedure 也就是我们自己创建的

　　三、用户自定义存储过程的创建、修改、删除

　　【创建存储过程】

　　create proc 存储过程名 --无参数的存储过程

　　as

　　批处理语句

　　go

　　【修改存储过程】

　　alter proc 存储过程名 --无参数的存储过程

　　as

　　批处理语句

　　go

　　可以看到，修改存储过程的语法和创建的语法只差一个单词，把create换成alt即可。

　　【删除存储过程】

　　drop proc 存储过程名

　　我们还可使用management studio来管理存储过程，展开菜单树中的“可编程性”，在“存储过程”的子节点中可以进行各种操作。这里要说一下创建：当点击“新建存储过程”之后，会出现一个基于模板的创建语句。这时点击菜单中的“查询→指定模板参数的值”，即可弹出对话框来对模板进行设置，从而建立我们想要的存储过程。另外，点击菜单中的“视图→模板资源管理器”，可以看到SQL SERVER为我们提供的各种SQL语句模板。

　　【一个简单的例子】

　　--插入一个以时间为用户名的用户

　　create proc insUser

　　as

　　begin tran

　　declare @username varchar(20)

　　set @username=convert(varchar(8),getdate(),112)

　　+replace(convert(varchar(10),getdate(),8),':','')

　　if not exists(select * from yonghu where yonghuming=@username)

　　insert into yonghu values

　　(@username,'111111','@163.com','新用户')

　　commit tran --也可以写commit，但是建议不要去掉tran

　　go

　　然后使用exec执行这个存储过程：

　　exec insUser

　　选中exec这一行，然后按F5快速地反复执行，你会发现在同一秒内只能插入一个用户。

　　这个存储过程一旦建立就不能再次执行这段代码了，可以把create改成alter来修改。

　　注意存储过程的代码中不能go语句，因为go是用来提交批的，一旦遇到go系统会认为这个存储过程的代码已经书写完毕，会提交create或者alter的批处理。如果希望在存储过程中执行另一个批处理，请把该批处理写成另一个存储过程并调用。

　　四、用户自定义存储过程的参数传递和返回值

　　【传递参数】

　　create|alter proc 存储过程名

　　@参数名参数类型[,

　　@参数名 参数类型...]

　　as

　　批处理语句

　　go

　　还记得我们前面做过的一个案例吗?

　　declare @tablename nvarchar(10),@id varchar(10),@idvalue int

　　declare @sql varchar(100)

　　set @tablename='yiren'

　　set @id='yirenid'

　　set @idvalue=10

　　set @sql='select * from '+@tablename+' where '+@id+' = '+cast(@idvalue as varchar)

　　print @sql

　　exec(@sql)

　　现在我们把它写成存储过程。这样我们每次都可以从一个指定表中提取我们想要的记录了

　　create proc queryItem

　　@tablename nvarchar(10),

　　@id varchar(20),

　　@idvalue int --参数外面还可以套上圆括号，看起来更加清晰

　　as

　　declare @sql varchar(100)

　　set @sql='select * from '+@tablename+'

　　where '+@id+' = '+cast(@idvalue as varchar)

　　exec(@sql)

　　go

　　调用的方法：

　　exec queryItem 'yiren','yirenid',@idvalue=10

　　queryItem 'jingjiren','jingjirenid',1

　　存储过程不使用exec也可以调用，但是不推荐这么做。存储过程参数的名字可以在调用时写出来，但是这是完全没必要的，所以@idvalue=10直接写成10就可以了。

　　exec加不加括号效果不一样。加括号是执行sql语句，不加括号是执行存储过程。

　　【返回值】

　　1.以retrun返回，始终是整数值

　　return只能返回整数，即使不显式写出“return 整数值”这样的语句，存储过程也会自动返回一个数值0表示成功。我们可以在发生错误时返回非0值，表示有错误发生。不要试图使用return返回一个在存储过程中处理的结果，比如姓名、生日之类的内容，因为它是整数，功能极为有限。我们只用它返回存储过程执行的状态就足够了。请看例子：

　　create proc returnProc

　　as

　　begin tran

　　declare @error int

　　insert into yiren (xingming) values ('王美丽')

　　set @error=@@error

　　insert into yiren (yirenid) values (1)

　　set @error=@error+@@error

　　if @error>0

　　rollback tran

　　else

　　commit tran

　　return @error

　　go

　　调用的方法：

　　declare @error int

　　--set @error=exec returnProc --这样写是错误的……

　　exec @error=returnProc

　　select '返回值'=@error

　　如果returnProc有个参数@xingming希望传入'王美丽'，可以这样调用：

　　exec @error=returnProc '王美丽'

　　--或者：

　　exec @error=returnProc @xingming='王美丽'

　　2.以output参数返回数据

　　output可以用来返回任何类型的数据，严格来说，它并不是一个“返回值”，而是一个能够被存储过程调用代码处看到的“外部变量”。这样说的原因看下面的例子就明白了：

　　--通过id查询艺人的姓名和年龄

　　create proc queryProfile

　　@id int,

　　@xingming varchar(50) output, --必须有output

　　@nianling int output

　　as

　　select @xingming=xingming,@nianling=nianling

　　from yiren where yirenid=@id

　　go

　　调用的方法：

　　declare @xingming varchar(50),@nianling int

　　exec queryProfile 1,@xingming output,@nianling output --必须有output

　　print '1号艺人的姓名是'+@xingming

　　+'，年龄是'+cast(@nianling as varchar)+'岁'