Sql Server 存储过程实例讲解

一、遇到的问题

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

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

        在调用处，我们先定义了两个变量，然后我们以output的方式把两个变量传递给了存储过程。于是存储过程就可以看到这两个来自外部的变量了。那么存储过程中对这两个变量的一切修改都可以立刻体现到调用处的代码中，因为它修改的实际上就是调用处的两个变量。

3.select语句的结果集

        如果在存储过程中执行了select语句并显示结果集（并不是使用select语句给变量赋值），那么这个结果集也可以看做是一种返回值（不能被批处理语句用，但是作为结果集可以被C#等编程语言使用）。

create proc selectProc
as
select * from yiren
go
exec selectProc

        这种存储过程可以用来实现“带有参数的视图”，在上面【传递参数】中举出的例子就是。

【存储过程的常用功能】

        从存储过程参数和返回值的用法我们可以看出，存储过程通常用来处理一些对数据库的更新操作、或者是按照特定的需要从数据库中查询信息，并以变量的形式（而不是结果集的形式）返回给调用处、或者是以结果集的形式返回，但并不能被调用处的语句所使用。我们可以使用return的数值来监控存储过程执行得是否顺利。

五、存储过程的嵌套调用

        在一个存储过程中还可以执行另一个存储过程，即嵌套调用。可以多次嵌套，但最多32层。可以用@@NESTLEVEL来查看嵌套当前层数：

create proc proc_1 --获取艺人id
@yid int output
as
    select top 1 @yid=yirenid from yiren
       where nicheng='芙蓉姐姐'
    print '存储过程1在第'+cast(@@NESTLEVEL as varchar)+'层'
go

create proc proc_1_1 --获取粉丝id
@fid int output
as
    declare @yid int
    exec proc_1 @yid output
    select @fid=yonghuid from fensi
       where yirenid=@yid
       order by yonghuid desc
    print '存储过程1_1在第'+cast(@@NESTLEVEL as varchar)+'层'
go

create proc proc_1_1_1 --获得芙蓉姐姐的粉丝
as
    declare @fid int
    exec proc_1_1 @fid output
    select * from yonghu
       where yonghuid=@fid
    print '存储过程1_1_1在第'+cast(@@NESTLEVEL as varchar)+'层'
go

exec proc_1_1_1
go

六、系统存储过程

        sp_databases             列出服务器上的所有数据库（无参数）

        sp_server_info            列出服务器信息（可以有参数）

        sp_stored_procedures              列出当前环境中的所有存储过程（可以有参数）

        sp_tables              返回当前环境下可查询的对象的列表（无参数）

        sp_configure              显示或更改当前服务器的全局配置设置

        sp_help         显示有关数据库对象的信息（可以有参数）如sp_help yiren

        sp_helpdb          显示有关数据库的信息（可以有参数）如sp_helpdb SuperStar

        sp_helptext       显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本（有参数）如sp_helptext proc_1

七、存储过程的注意事项

【存储过程的优点】

Ø  只在创建时编译，执行速度快效率高

Ø  减少网络传输流量

Ø  提高安全性

Ø  模块式编程，可以重复使用

Ø  统一每次的操作流程

【偷偷说一句】

        前面不管学什么都要提一下缺点，但是存储过程这里却没提。这并不意味着存储过程没有任何缺点，毕竟任何东西都不能滥用，但是可以看出来，存储过程的确是T-SQL编程的核心内容，是最重要的部分。而它本身又是如此地容易掌握，相信你现在的心情不错吧？

【在存储过程中使用事务】

        存储过程中是可以使用事务的，这毫无疑问。在存储过程中使用事务不必用goto语句，在rollback或者commit语句之后直接return即可终止存储过程的执行。前面我们也提到过：在普通批处理中实际上也是可以使用return语句的。

八、使用存储过程实现分页查询

<strong>create proc queryPage
    @tablename nvarchar(50), --用于传入表名
    @idname nvarchar(50), --用于传入字段名
    @pagesize int, --用于传入每页记录数
    @currentpage int, --用于传入希望查看的页面编号
    @totalpages int output --用于传出页面总数
as
--声明保存查询语句的局部变量：
declare @sql as nvarchar(1000)
--声明保存记录总数的局部变量：
declare @rowcount as int
--获得记录总数：
set @sql='select @rc=count(*) from '+@tablename
--不要直接执行select @rowcount=count(*) from @tablename
--将参数传入语句：
exec sp_executesql @sql,N'@rc int output',@rc=@rowcount output
--将根据每页的行数得到的总页数保存到输出参数中：
set @totalpages = ceiling(cast(@rowcount as float)/cast(@pagesize as float))
if @currentpage >1
begin
    if @currentpage>@totalpages
    begin
       set @currentpage = @totalpages --则显示最后一页
    end
    set @sql = 'select top '+cast(@pagesize as varchar)
    +' * from '+@tablename+' where '+@idname+' not in (select top '
    +cast(@pagesize*(@currentpage-1) as varchar)
    +' '+@idname+' from '+@tablename+' order by '+@idname+')
order by '+@idname
end
else --只选第一页就不必使用子查询了，提高性能
begin
    set @sql = 'select top '+cast(@pagesize as varchar)
    +' * from '+@tablename+' order by '+@idname
end
exec(@sql) --执行查询语句
go</strong>

Ø  sp_executesql这个系统存储过程是另一种执行sql语句的方法。它比exec(@sql)的功能高级一些。在这里我们为它传入3个参数，分别是等待处理的@sql（必须是nvarchar类型的）、使用字符串为@sql语句定义新的变量名、为字符串中定义的新变量赋值（@rc是字符串中定义的新变量，把它赋为@rowcount，又因为希望通过@rc为@rowcount返回值，所以指定为output）。

Ø  ceiling函数得到大于某小数的最小整数，如ceiling(3.5)会得到4。这里把@rowcount和@pagesize相除，得到的数字是个小数。小数部分无法组成一个完整的分页但是不代表没有记录，所以使用ceiling函数，让@totalpages的值能够正确赋值。

Ø  当传入的currentpage并不在正确的分页编号范围内时，我们的代码做出了处理，给@currentpage赋了一个正确范围内的数值。

调用上面的存储过程：

declare @totalpages int
exec queryPage 'yiren','yirenid',5,13,@totalpages output
print '一共'+cast(@totalpages as varchar)+'行'