Haijd's Blog

存储过程


1. 什么是存储过程

　　调用百度百科的解释:存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。 　　简单来说，存储过程就像是c#中的方法，该方法实现某一特定的功能，在需要的地方直接调用就行了。而且该方法还是单例的。 　　存储过程不仅仅是一组SQL语句的组合，存储过程和一般的SQL语句还是有区别的。 　　

当我们使用SQL Server数据库帮助我们建立网站或应用软件时，Transaction-SQL是一种主要的编程语言，使用SQL语句(Transaction-SQL)编程有两种方法： 　　① 在本地存储SQL语句编写的代码，通过程序连接数据库，对SQL Server进行操作。 　　② 把部分复杂或重用的SQL语句封装成存储过程，存储在SQL Server数据库中，然后通过程序去调用存储过程即可。而且还可以在存储过程中调用另一个存储过程。

2. 存储过程的优点

　　1.存储过程只在创建时进行编译，以后每次执行都不需要重新编译，而一般SQL语句每执行一次就编译一次，所以存储过程可以提高数据库的执行速度。 　　2.对数据库进行复杂操作时(比如对多张表进行SELECT、INSERT、UPDATE、DELETE)，可将此复杂操作封装成存储过程，然后结合数据库中的事物处理一起使用。 　　3.存储过程可以复用，从而减少开发人员的工作量。 　　4.安全性高，可以设定操作此存储过程的用户权限。 　　5.使用时只需要传输调用语句，而非多条SQL语句，减少网络流量，降低网络负载。

3. 存储过程的种类

　　1.系统存储过程：以SP_开头，用来进行系统的各项设定、取得信息、相关管理，比如SP_help就是取得指定对象的相关信息。 　　2.扩展存储过程：以XP_开头，用来调用操作系统提供的功能，exec master ..XP_cmdshell 'ping 120.110.119:12306' 　　3.用户自定的存储过程，也就是这篇博客所说的存储过程。

4. 创建存储过程

创建语法： 　　create proc usp_name 　　形参列表(形参名称以@符号开头，参数类型在后) 　　as 　　 方法体 　　go

调用语法： 　　exec 存储过程名称 实参,实参,实参…

　　在调用的时候要三个对应：类型对应、数量对应、顺序对应。

4.1. 代码示例：

获取所有员工信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

go
if exists(select * from sysobjects where name='usp_getAllStaffInfo')
    drop proc usp_getAllStaffInfo
go

create procedure usp_getAllStuInfo
as
    select * from Staff
go

--调用存储过程
execute usp_getAllStaffInfo

查询指定性别的员工信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

go
if exists(select * from sysobjects where name='usp_GetStaffInfoBySex')
    drop proc usp_GetStaffInfoBySex
go

create PROC usp_GetStaffInfoBySex
@sex nchar(1)   
as
    select * from Staff where Sex=@sex
go

--调用存储过程
EXEC usp_GetStaffInfoBySex '男'

获取指定部门和性别的员工信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

go
if exists(select * from sysobjects where name='usp_GetStaffInfoBySexForClass')
    drop PROC usp_GetStaffInfoBySexForClass
go

create PROC usp_GetStaffInfoBySexForClass
@className nvarchar(20)
@sex ncahr(1)='男'   --有默认值的形参
as
    declare @classId int   --声明一个存储部门ID的变量
    set @classId=(select classId from Company where className=@className)
    select * from Staff where Sex=@sex and classId=@classId
go

--调用存储过程
EXEC usp_GetStaffInfoBySexForClass @classId='.NET部',@sex='男'
EXEC usp_GetStaffInfoBySexForClass '.NET部','男'
EXEC usp_GetStaffInfoBySexForClass default,'.NET部'    --不需要改变默认值就是用default关键字

返回指定性别员工人数和总人数 　　output参数相当于c#中的out参数，用于将参数返回，所以在调用的时候需要声明变量去接收。注意在接收的时候，接收的变量后也要有output参数。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

go
if exists(select * from sysobjects where name='usp_GetStaffManAndStaffCount')
create proc usp_GetStaffManAndStaffCount
    @sex nchar(1),
    @SexCount int output,
    @count int output
as
    set @SexCount=(select count(*) from Staff where Sex=@sex)
    set @count=(select count(*) from Staff)


--调用存储过程
declare @manCount int,@sumCount int
exec usp_GetStaffManAndStaffCount '男',@manCount output,@sumCount output

🎫 一个分页存储过程

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

if exists(select * from sysobjects where name='usp_pageing')
    drop proc usp_pageing

create proc usp_pageing
    @totalPage int output,   --总页数
    @pageIndex int=1,   --当前的页码数，默认从1开始
    @pageCount int=10   --每页显示的条目数
as
    select * from (select ROW_NUMBER() over(order by newsCreateDate) id, * from News) item where item.Id>(@pageIndex-1)*@pageCount and item.Id<=(@pageIndex*@pageCount)
    set @totalPage=ceiling((select count(*) from News)*1.0/@pageCount)   --ceiling函数为向上四舍五入，如：ceiling(1.45,0) = 2;ceiling(1.55,0)=2