SqlServer：存储过程
存储过程
存储过程(Stored Procedure)简称过程，由一系列SQL语句构成完成一定的功能的程序段，通过过程名调用并执行。在 SqlServer 中存放在数据库的“可编程性”组件中，与表和视图的级别相同。存储过程是由流程控制和 SQL 语句组成的，允许用户声明变量，可以调用系统函数，经编译后存储在数据库服务器中。存储过程可以接收输入参数，也可以将运行结果带出过程，返回执行存储过程的状态值，还可以嵌套调用。由于存储过程在服务器运行，且运行后会保存在缓存中，因此可以提高运行效率。
存储过程分为三类，分别是系统存储过程、用户自定义存储过程和扩展存储过程。
| 存储过程类型 | 说明 |
|---|---|
| 系统存储过程 | 系统自动创建的系统存储过程，用于显示各种参数等，以 “sp_” 为前缀 |
| 自定义存储过程 | 由用户为完成某一特定功能而编写的存储过程，名称不能以 “sp_” 为前缀 |
| 扩展存储过程 | 是对动态链接库(DLL)函数的调用，前缀名是 “XP_” |
创建存储过程的步骤是：
- 检验存储过程名是否存在；
- 编写存储过程中的 SQL 语句；
- 用创建存储过程的语法创建存储过程。
SQL 语句
建立存储过程前需要确定其功能，存储过程的调用可以是无参数调用或有参数调用，可以有返回值或者没有参数返回。建立存储过程的命令格式是:
CREATE PROCEDURE 存储过程名
[WITH ENCRYPTION]
[@参数名 类型 [ = 默认值][OUTPUT]][,-n ]
AS SQL语句
| 参数 | 说明 |
|---|---|
| PROCEDURE | 存储过程名 |
| WITH ENCRYPTION | 将存储过程的代码加密 |
| @参数名 类型 | 接收指定的实际参数及类型 |
| OUTPUT | 表示参数是输出参数，若无此项则参数是输入参数 |
| SQL 语句 | 是构造存储过程的 SQL 语句，如果包括多条命令可以用 BEGIN…END 代码块 |
调用存储过程的方式有以下 2 种：
存储过程名 [参数值] --存储过程名是批处理的第 1 条语句
EXECUTE 存储过程名 [参数值] --存储过程名不是批处理的第 1 条语句
存储过程样例
样例一
此时有成绩表 Score 和课程表 Course，表中具有以下字段和记录。
用户输入某一门课程的名称，就可统计出该课程各分数段分布的人数。首先建立排名表：
CREATE TABLE Rank(
division char(20),
sub_sum int
);
INSERT INTO Rank(division) VALUES('[0，60)'),('[60,70)'),('[70,80)'),('[80,90)'),('[90,100]');
接着创建存储过程。
CREATE PROCEDURE printcourse @pcname char(20)
AS
DECLARE @pcno char(20), @pcount int, @count int
set nocount on
SELECT @count = COUNT(*) FROM Course WHERE Cname = @pcname
IF(@count = 0)
BEGIN
RAISERROR('您输入的课程不存在，请重新输入！', 16, 1)
RETURN
END
--查找 @pcname 对应的课程号
SELECT @pcno = Score.cno FROM Course, Score WHERE Course.Cno = Score.Cno AND Course.Cname = @pcname
--统计不及格人数，并更新 Rank 表
SELECT @pcount = COUNT(*) FROM Score WHERE Degree < 60 AND Cno = @pcno
UPDATE Rank SET sub_sum = @pcount WHERE division = '[0，60)'
SELECT @pcount = COUNT(*) FROM Score WHERE Degree >= 60 AND Degree < 70 AND Cno = @pcno
UPDATE Rank SET sub_sum = @pcount WHERE division = '[60,70)'
SELECT @pcount = COUNT(*) FROM Score WHERE Degree >= 70 AND Degree < 80 AND Cno = @pcno
UPDATE Rank SET sub_sum = @pcount WHERE division='[70,80)'
SELECT @pcount = COUNT(*) FROM Score WHERE Degree >= 80 AND Degree < 90 AND Cno = @pcno
UPDATE Rank SET sub_sum = @pcount WHERE division='[80,90)'
SELECT @pcount = COUNT(*) FROM Score WHERE Degree >= 90 AND Degree <= 100 AND Cno = @pcno
UPDATE Rank SET sub_sum = @pcount WHERE division = '[90,100]'
最后尝试执行该存储过程。
EXEC printcourse '计算机导论'
SELECT * FROM Rank
样例二
统计某一门课的平均成绩，存储过程可带有一个字符型参数值，接受用户输入的课程名称。一个输出参数(用 output 声明)用于存放返回给调用者的这门课程的平均成绩)。
CREATE PROCEDURE printavg_course @pcname char(20), @pavg int output
AS
DECLARE @pcno char(20)
SET NOCOUNT ON
SELECT @count = COUNT(*) FROM Course WHERE Cname = @pcname
IF(@count = 0)
BEGIN
RAISERROR('您输入的课程不存在，请重新输入！', 16, 1)
RETURN
END
--最关键的 2 行
SELECT @pcno = Score.cno FROM Course, Score WHERE Course.Cno = Score.Cno AND Course.Cname = @pcname
SELECT @pavg = AVG(Score.Degree) FROM Score WHERE Cno = @pcno
PRINT RTrim(@pcname) + '的平均成绩为：' + CAST(@pavg AS char(5))
运行该存储过程。
DECLARE @pavg int
EXEC printavg_course '操作系统', @pavg output
Select @pavg int
样例三
在 Course 表中查询学生的学号、课程号和成绩，但是要将学生选课成绩从百分制改为等级制，即 A、B、C、D、E 五级。
CREATE PROCEDURE printclass_degree
AS
SELECT Sno, Cno, Degree,
CASE
WHEN Degree < 60 THEN '不及格'
WHEN Degree >= 60 AND Degree < 70 THEN '及格'
WHEN Degree >= 70 AND Degree < 80 THEN '中'
WHEN Degree >= 80 AND Degree < 90 THEN '良'
WHEN Degree >= 90 AND Degree <= 100 THEN '优'
ELSE '成绩为空！'
END AS 'Degree Classified'
FROM Score
运行该存储过程。
EXEC printclass_degree
样例四
表 Course 增加两列，分别是课程选修最大人数（mn ，默认50）和当前人数（cn ，默认0）。
某学生没有选过某课程，要选某课程且没有超过课程最大人数时，可以选择该课程，当前选课人数加1，并在成绩 Score 表中增加对应的该生该课程的成绩为 0 记录。否则，提示该课程选课人数已满，不能选课。
CREATE PROCEDURE cs @sno char(3), @cno char(5)
AS
DECLARE @mn INT, @cn INT, @count INT
SET @count = (SELECT count(Sno) FROM Score WHERE Sno = @sno AND Cno = @cno)
IF(@count = 1)
BEGIN
RAISERROR('该学生已经选过这门课',16,1)
END
ELSE
BEGIN
SELECT @mn = mn FROM Course WHERE Cno = @Cno
SET @count = (SELECT count(Sno) FROM Score WHERE Cno = @Cno)
IF(@count >= @mn)
BEGIN
RAISERROR('该课程已达到最大选课人数',16,1)
END
ELSE
BEGIN
UPDATE Course SET cn = @count + 1 WHERE Cno = @cno
INSERT INTO Score(Sno, Cno, Degree) VALUES(@sno, @cno, 0)
RAISERROR('学生选课成功',16,1)
END
END
运行该存储过程。
EXEC cs '101', '3-245'
SELECT * FROM Score
SELECT * FROM Course
EXEC cs '101', '3-235'
某学生选了某课程后要退选，该课程的当前选课人数减 1 并删除其对应的成绩。
CREATE PROCEDURE csd @sno char(3), @cno char(5)
AS
DECLARE @mn INT, @cn INT, @count INT
SET @count = (SELECT count(Sno) FROM Score WHERE Sno = @sno AND Cno = @cno)
IF(@count = 0)
BEGIN
RAISERROR('该学生没有选过这门课',16,1)
END
ELSE
BEGIN
SELECT @cn = cn FROM Course010 WHERE Cno = @Cno
UPDATE Course SET cn = @cn - 1 WHERE Cno = @cno
DELETE Score WHERE Sno = @sno AND Cno = @cno
RAISERROR('学生退课成功',16,1)
END
运行该存储过程。
EXEC csd '101', '3-245'
SELECT * FROM Score
SELECT * FROM Course
EXEC csd '101', '3-245'
某学生选了某课程后，可以查询其成绩（out）。
CREATE PROCEDURE cx @sno char(3), @cno char(5)
AS
DECLARE @mn INT, @cn INT, @count INT
SET @count = (SELECT count(Sno) FROM Score WHERE Sno = @sno AND Cno = @cno)
IF(@count = 0)
BEGIN
RAISERROR('该学生没有选过这门课',16,1)
END
ELSE
BEGIN
SELECT * FROM Score WHERE Cno = @cno AND Sno = @sno
END
运行该存储过程。
EXEC cx '101', '3-245'
如果学生没有选课，则看到如下信息。
参考资料
《SqlServer 2014 数据库技术实用教程》，胡伏湘、肖玉朝 主编，清华大学出版社
