数据库技术三（存储过程）

存储过程

基本内容:(1)编写简单存储过程;(2)编写和使用带参数的存储过程;(3)编写和使用带输出参数的存储过程;(4)编写带状态返回值的存储过程。

1. 编写一个查询学生表中学生姓名和籍贯的存储过程，然后执行该存储过程。

CREATE PROCEDURE P1
AS
BEGIN
SELECT 姓名，籍贯 FROM dbo.学生表
END
GO

EXEC P1

2. 编写一个让所有教材的定价打8折的存储过程。然后执行该存储过程，要求执行存储过程前后查询课程表的教材名称，定价。

CREATE PROCEDURE P2
AS
BEGIN
UPDATE dbo.课程表 SET 定价=定价*0.8
WHERE 定价 IS NOT NULL
END
GO

SELECT 教材名称，定价 FROM dbo.课程表
WHERE 定价 IS NOT NULL
EXEC P2
SELECT 教材名称，定价 FROM dbo.课程表
WHERE 定价 IS NOT NULL

3. 编写一个查询平均成绩大于指定成绩的学生信息的存储过程。然后执行该存储过程，传入参数为“90”。

CREATE PROCEDURE P3(@CHENGJI INT)
AS
BEGIN
SELECT * FROM dbo.学生表
WHERE 学号 IN(
SELECT 学号 FROM dbo.选课表 GROUP BY 学号
HAVING AVG(成绩) > @CHENGJI)
END

GO
EXEC P3 85  -- 执行时参数无括号

4. 编写一个查询有课程考试成绩低于给定值的学生信息的存储过程，如果存储过程执行时没有给定参数，则默认查询有课程考试成绩低于60的学生信息。然后执行该存储过程，分别显示提供参数和不提供参数的存储过程执行结果。

CREATE PROCEDURE P4(@CHENGJI INT = 60)
AS
BEGIN
SELECT * FROM dbo.学生表
WHERE 学号 IN(
SELECT 学号 FROM dbo.选课表 WHERE 成绩<@CHENGJI)
END

GO
EXEC P4 DEFAULT
EXEC P4 90

5. 编写一个存储过程，传入参数为一字符串，用于查找学生姓名中包含给定字符串的学生的信息，如果不提供参数值，则默认查询姓名含’王’的学生信息。分别显示提供参数和不提供参数的存储过程执行结果。

CREATE PROCEDURE P5(@NAME VARCHAR(20)='王')
AS
BEGIN
SELECT * FROM dbo.学生表
WHERE 姓名 LIKE '%'+@NAME+'%'
END

GO
EXEC P5 DEFAULT
EXEC P5 '李'

6. 编写一个用OUTPUT参数返回指定课（用开课号指定）的平均成绩，最高成绩和最低成绩的存储过程。

CREATE PROCEDURE P6(@KAIKE VARCHAR(20), @AVG FLOAT OUTPUT,@MAX INT OUTPUT,@MIN INT OUTPUT)
AS
BEGIN
SELECT @AVG=AVG(成绩），@MAX=MAX(成绩），@MIN=MIN(成绩）
FROM dbo.选课表 WHERE 开课号=@KAIKE
END

GO
DECLARE @KAIKE VARCHAR(20),@AVG FLOAT,@MAX INT,@MIN INT
SET @KAIKE='010101'
EXEC P6 @KAIKE,@AVG OUTPUT,@MAX OUTPUT,@MIN OUTPUT
SELECT @KAIKE AS 开课号，@AVG AS 平均分，@MAX AS 最高分，@MIN AS 最低分

7. 编写一个查找是否存在学生籍贯包含给定字符串(字符串长度不超过20个字符)的学生的存储过程。如果存在多个符合条件的学生则返回1，存在一个返回0，否则返回-1。要求用return实现返回状态值。分别显示三种情况的存储过程执行结果(返回1输出“存在多人”，返回0输出“存在一人”，返回-1输出“不存在”)。

CREATE PROCEDURE P7 (@JIGUAN VARCHAR(20))
AS
BEGIN
DECLARE @NUM INT
SELECT @NUM=COUNT(*) FROM dbo.学生表
WHERE 籍贯 LIKE '%'+@JIGUAN+"%"
IF (@NUM=1)
RETURN 0
ELSE IF(@NUM>1)
RETURN 1
ELSE
RETURN -1
END

GO
DECLARE @N INT
EXEC @N=P7 '都'
IF(@N=0)
PRINT('存在一人')
ELSE IF(@N=1)
PRINT('存在多人')
ELSE
PRINT('不存在')

8. 编写一个修改指定学号的学生的籍贯的存储过程，参数包括学号和籍贯。然后执行该存储过程，将学号“S060101”学生的籍贯修改为“成都”。

CREATE PROCEDURE P8(@XUEHAO VARCHAR(20),@JIGUAN VARCHAR(20))
AS
BEGIN
UPDATE dbo.学生表 SET 籍贯=@JIGUAN WHERE 学号=@学号
END

GO
EXEC P8 's060101','成都'
SELECT * FROM dbo.学生表 WHERE 学号='s060101'

总结

1. 最需要主要的地方一个是参数的问题，比如执行存储过程时，一定没有括号，但创建存储过程的时候可以有括号，以及OUTPUT参数在执行时需要注意。
2. 另外关于赋值，存在的特殊情况，用exec执行存储过程，并将存储过程的返回值赋值给变量，而并不是用SET。
3. 存储过程只需经过一次编译，下一次是使用时，无需再次编译。