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

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

1.存储过程

1 存储过程的概念

定义

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

类别

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

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

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

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

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

2 存储过程的使用

创建存储过程

一般形式：

CREATE PROC 过程名

@形参1 类型,

@形参2 类型…

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

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

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

数据库脚本

数据表脚本