SqlServer：存储过程

目录

• 存储过程
• SQL 语句
• 存储过程样例
  • 样例一
  • 样例二
  • 样例三
  • 样例四
• 参考资料

存储过程#

存储过程(Stored Procedure)简称过程，由一系列SQL语句构成完成一定的功能的程序段，通过过程名调用并执行。在 SqlServer 中存放在数据库的“可编程性”组件中，与表和视图的级别相同。存储过程是由流程控制和 SQL 语句组成的，允许用户声明变量，可以调用系统函数，经编译后存储在数据库服务器中。存储过程可以接收输入参数，也可以将运行结果带出过程，返回执行存储过程的状态值，还可以嵌套调用。由于存储过程在服务器运行，且运行后会保存在缓存中，因此可以提高运行效率。
存储过程分为三类，分别是系统存储过程、用户自定义存储过程和扩展存储过程。

存储过程类型	说明
系统存储过程	系统自动创建的系统存储过程，用于显示各种参数等，以 “sp_” 为前缀
自定义存储过程	由用户为完成某一特定功能而编写的存储过程，名称不能以 “sp_” 为前缀
扩展存储过程	是对动态链接库(DLL)函数的调用，前缀名是 “XP_”

创建存储过程的步骤是：

1. 检验存储过程名是否存在；
2. 编写存储过程中的 SQL 语句；
3. 用创建存储过程的语法创建存储过程。

SQL 语句#

建立存储过程前需要确定其功能，存储过程的调用可以是无参数调用或有参数调用，可以有返回值或者没有参数返回。建立存储过程的命令格式是:

Copy Highlighter-hljs
CREATE PROCEDURE 存储过程名
[WITH ENCRYPTION]
[@参数名 类型 [ = 默认值][OUTPUT]][,-n ]
AS SQL语句

参数	说明
PROCEDURE	存储过程名
WITH ENCRYPTION	将存储过程的代码加密
@参数名 类型	接收指定的实际参数及类型
OUTPUT	表示参数是输出参数，若无此项则参数是输入参数
SQL 语句	是构造存储过程的 SQL 语句，如果包括多条命令可以用 BEGIN…END 代码块

调用存储过程的方式有以下 2 种：

Copy Highlighter-hljs
存储过程名 [参数值]    --存储过程名是批处理的第 1 条语句
EXECUTE 存储过程名 [参数值]    --存储过程名不是批处理的第 1 条语句

存储过程样例#

样例一#

此时有成绩表 Score 和课程表 Course，表中具有以下字段和记录。


用户输入某一门课程的名称，就可统计出该课程各分数段分布的人数。首先建立排名表：

Copy Highlighter-hljs
CREATE TABLE Rank(
    division char(20),
    sub_sum int
);

INSERT INTO Rank(division) VALUES('[0，60)'),('[60,70)'),('[70,80)'),('[80,90)'),('[90,100]');

接着创建存储过程。

Copy Highlighter-hljs
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]'

最后尝试执行该存储过程。

Copy Highlighter-hljs
EXEC printcourse '计算机导论'

SELECT * FROM Rank

样例二#

统计某一门课的平均成绩，存储过程可带有一个字符型参数值，接受用户输入的课程名称。一个输出参数(用 output 声明)用于存放返回给调用者的这门课程的平均成绩)。

Copy Highlighter-hljs
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))

运行该存储过程。

Copy Highlighter-hljs
DECLARE @pavg int

EXEC printavg_course '操作系统', @pavg output

Select @pavg int

样例三#

在 Course 表中查询学生的学号、课程号和成绩，但是要将学生选课成绩从百分制改为等级制，即 A、B、C、D、E 五级。

Copy Highlighter-hljs
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

运行该存储过程。

Copy Highlighter-hljs
EXEC printclass_degree 

样例四#

表 Course 增加两列，分别是课程选修最大人数（mn ，默认50）和当前人数（cn ，默认0）。

某学生没有选过某课程，要选某课程且没有超过课程最大人数时，可以选择该课程，当前选课人数加1，并在成绩 Score 表中增加对应的该生该课程的成绩为 0 记录。否则，提示该课程选课人数已满，不能选课。

Copy Highlighter-hljs
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

运行该存储过程。

Copy Highlighter-hljs
EXEC cs '101', '3-245'
SELECT * FROM Score
SELECT * FROM Course

Copy Highlighter-hljs
EXEC cs '101', '3-235'

某学生选了某课程后要退选，该课程的当前选课人数减 1 并删除其对应的成绩。

Copy Highlighter-hljs
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

运行该存储过程。

Copy Highlighter-hljs
EXEC csd '101', '3-245'
SELECT * FROM Score
SELECT * FROM Course

Copy Highlighter-hljs
EXEC csd '101', '3-245'

某学生选了某课程后，可以查询其成绩（out）。

Copy Highlighter-hljs
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

运行该存储过程。

Copy Highlighter-hljs
EXEC cx '101', '3-245'

如果学生没有选课，则看到如下信息。

参考资料#

《SqlServer 2014 数据库技术实用教程》，胡伏湘、肖玉朝 主编，清华大学出版社