SQLserver数据库之存储过程与自定义函数-存储过程

SQLserver数据库之存储过程与自定义函数

1 存储过程的概念

• 定义

存储过程（Stored Procedure）是数据库中为了完成特定功能而编写的一组SQL 语句集，经编译后存储在数据库中，当需要使用这些功能时，用户通过存储过程的名字并给出必要的参数来执行它。

• 类别

（1）系统存储过程（以sp_开头）

（2）本地存储过程（用户创建）

（3）远程存储过程（常用于分布式查询）

（4）扩展存储过程 （一般以xp_开头，由外部语言程序编写）

（5）临时存储过程（放在TempDB中）

2 存储过程的使用

• 创建存储过程

一般形式：

CREATE PROC 过程名

@形参1 类型,

@形参2 类型…

AS

SQL语句

• 调用存储过程

一般形式：

EXEC PROC 过程名@实参1,@实参2

• 修改和删除存储过程

使用Alter修改存储过程，用新的语句替换以前的存储过程语句；

使用Drop PROC 删除存储过程名。

【例1】创建无参数存储过程，查询学校人数，并调用该存储过程。

SQLserver数据库之存储过程与自定义函数

创建：

 create proc pro_getStuCounts
 as
 select schID,人数=count(*)
 from stuInfo group by schID

调用：exec pro_getStuCounts

注：使用存储过程，可以使用select

以表结果形式返回查询数据，或使用

return以值方式返回单个查询结果。

【例2】创建带参数存储过程，创建获取指定学校人数的存储过程并调用。

SQLserver数据库之存储过程与自定义函数

创建：

 create proc proc_countSpecialSchool
 @schId char(6)
 as
select count(*) as 人数
from stuInfo
where schID=@schID

调用：

exec proc_countSpecialSchool ‘580101’

【例3】创建学生登录存储过程，检验输入的学生身份证号和密码是否匹配，分别返回1或0。

SQLserver数据库之存储过程与自定义函数

创建：

 create proc proc_login
@userId char(18),@userPass char(6)
as
begin
if exists(select * from stuInfo where stuIdentity=@userId and stuPwd=@userPass)
 select 1
else
 select 0
end

调用： exec proc_login ‘51090219960829264x’,’29264x’

【例4】创建存储过程，改进第12讲例9，查询指定分页的数据。

创建：

 create proc proc_pagingByNtile
 @pageIndex int,@pageCounts int
 as
 begin
 select stuIdentity,stuName,stuSex,schId,examNum
 from (select *,页码=ntile(@pageCounts) over (order by autoId)
 from stuInfo) a
  where 页码=@pageIndex
 end

调用： exec proc_pagingByNtile 50,100

本文所用到的有创建数据库与插入数据表的脚本

大家可以点击下方文字链接进行下载

数据库脚本

数据表脚本

IT技术-IT技术网站-IT技术学习-SQLserver数据库教程-自学编程就到志在指尖