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)+'岁'