实训项目-存储过程创建及使用.doc

网络数据库 实训报告
一、实训目的和要求
1、了解存储过程的作用；
2、掌握创建、修改及删除存储过程的方法；
3、掌握执行存储过程的方法。
二、实训所需仪器、设备
硬件：计算机
软件：操作系统Windows XP、SQL Server 2005
三、实训内容
（一）不带参数的存储过程的创建和修改
1、在student数据库中创建一个名为myp1的存储过程，该存储过程的作用是显示t_student中的全部记录。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'mpy1' AND type = 'P')
DROP PROCEDURE mpy1
GO
CREATE PROCEDURE myp1
AS
Select * FROM T_STUDENT
GO

2、运行myp1，检查是否实现功能。
use student
exec myp1

3、修改myp1，使其功能为显示t_student中班级为05541班的学生记录，然后测试是否实现其功能。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[myp1]
AS
Select * FROM T_STUDENT
where left(s_number,5)='05541'

use student
exec myp1

4、创建一个存储过程myp2，完成的功能是在表t_student、表t_course和表t_score中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'myp2' AND type = 'P')
DROP PROCEDURE myp2
GO
CREATE PROCEDURE myp2
AS
Select 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,
LEN(T_STUDENT.S_NUMBER)-2),
学号=SUBSTRING(T_STUDENT.S_NUMBER,
LEN(T_STUDENT.S_NUMBER)-1,2),
S_NAME AS 姓名,
SEX AS 性别,
T_COURSE.C_NAME AS 课程名称,
t_SCORE.SCORE AS 考试分数
FROM T_STUDENT,T_COURSE,t_SCORE
WHERE T_STUDENT.S_NUMBER=t_SCORE.S_NUMBER
AND T_COURSE.C_NUMBER=t_SCORE.C_NUMBER
GO

（二）带输入参数的存储过程的创建
1、创建一个带有一个输入参数的存储过程stu_info，该存储过程根据传入的学生编号，在t_student中查询此学生的信息。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stu_Info' AND type = 'P')
DROP PROCEDURE Stu_Info
GO
USE STUDENT
GO
CREATE PROCEDURE Stu_Info
@S_NUMBER varchar(10)
AS
Select 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,
LEN(T_STUDENT.S_NUMBER)-2),
学号=SUBSTRING(T_STUDENT.S_NUMBER,
LEN(T_STUDENT.S_NUMBER)-1,2),
S_NAME AS 姓名,
SEX AS 性别,
BIRTHDAY AS 出生日期,
POLITY AS 政治面貌
FROM T_STUDENT
WHERE S_NUMBER=@S_NUMBER
GO

2、创建一个带有一个输入参数的存储过程stu_info2，该存储过程根据传入的学生编号，在t_student中查询此学生班级、学号、姓名和年龄。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stu_Info2' AND type = 'P')
DROP PROCEDURE Stu_Info2
GO
USE STUDENT
GO
CREATE PROCEDURE Stu_Info2
@S_NUMBER varchar(10)
AS
Select 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,
LEN(T_STUDENT.S_NUMBER)-2),
学号=SUBSTRING(T_STUDENT.S_NUMBER,
LEN(T_STUDENT.S_NUMBER)-1,2),
S_NAME AS 姓名,
YEAR(GETDATE())-YEAR(BIRTHDAY) AS 年龄
FROM T_STUDENT
WHERE S_NUMBER=@S_NUMBER
GO

3、创建一个带有一个输入参数的存储过程stu_info3，该存储过程根据传入的班级编号，查询该班所有同学的所有科目的成绩和。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stu_Info3' AND type = 'P')
DROP PROCEDURE Stu_Info3
GO
CREATE PROCEDURE Stu_Info3
@class_number varchar(10)
AS
Select sum(score) as 成绩和
FROM t_score
WHERE @class_number=SUBSTRING(t_score.S_NUMBER,1,
LEN(t_score.S_NUMBER)-2)

4、创建一个带有两个输入参数的存储过程stu_info4，该存储过程根据传入的班级编号和课程号，查询该班所有同学的该课程的平均分。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stu_Info4' AND type = 'P')
DROP PROCEDURE Stu_Info4
GO
CREATE PROCEDURE Stu_Info4
@class_number varchar(10),
@c_number varchar(10)
AS
Select avg(t_score.score) as 平均分
FROM t_score
WHERE @class_number=SUBSTRING(t_score.S_NUMBER,1,
LEN(t_score.S_NUMBER)-2)
and
@c_number=c_number

5、创建一个带有两个输入参数的存储过程stu_info5，该存储过程根据传入的学号和课程号，在t_score中查询此学号的学生此课程号的成绩。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stu_Info5' AND type = 'P')
DROP PROCEDURE Stu_Info5
GO
CREATE PROCEDURE Stu_Info5
@s_number varchar(10),
@c_number varchar(10)
AS
Select
学号=t_score.s_number,
课程编号=t_score.c_number,
score AS 成绩
FROM t_score
WHERE @s_number=s_number
and
@c_number=c_number

（三）带输入和输出参数的存储过程的创建
1、创建一个带有参数的存储过程stu_age，该存储过程根据传入的学生编号，在t_student中算出此学生的年龄，并根据程序执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stu_age' AND type = 'P')
DROP PROCEDURE Stu_age
GO
CREATE PROCEDURE Stu_Age
@S_NUMBER varchar(10),
@Age int OUTPUT
AS
DECLARE @ErrorValue int
SET @ErrorValue=0
SELECT @Age=YEAR(GETDATE())-YEAR(BIRTHDAY)
FROM T_STUDENT
WHERE S_NUMBER=@S_NUMBER
IF (@@ERROR<>0)
SET @ErrorValue=@@ERROR
RETURN @ErrorValue
GO

2、在查询分析器中执行如下语句：
use student
GO
DECLARE @stuAge int,
@ReturnValue int
EXECUTE @ReturnValue=stu_age ‘0554103’, @stuAge OUTPUT
PRINT ‘本程序的执行结果:’
PRINT ‘程序的返回值=’ + CAST(@ReturnValue AS char(6))
PRINT ‘学号为‘0554103的学生年龄是：’ + CAST(@stuAge AS char(6))
执行上述程序，观察运行结果是否正确。
正确

3、创建一个带参数的存储过程，输入参数为课程名称，查询有哪些班级的哪些学生参加了这门课程的考试及学生的考试成绩，存储过程返回0表示程序执行成功，返回其他整数，表示程序在执行时出现错误。
USE STUDENT
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stu_age' AND type = 'P')
DROP PROCEDURE Stu_age
GO
CREATE PROCEDURE Stu_Age
@c_name varchar(10)
AS
DECLARE @ErrorValue int
SET @ErrorValue=0
SELECT 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,
LEN(T_STUDENT.S_NUMBER)-2),
t_student.s_name as 学生,
score as 成绩
FROM T_STUDENT,t_course,t_score
WHERE t_course.c_number=t_score.c_number
and t_student.s_number=t_score.s_number
and c_name=@c_name
IF (@@ERROR<>0)
SET @ErrorValue=@@ERROR
RETURN @ErrorValue
GO
--验证
declare @c_name varchar(30),
@ReturnValue int
set @c_name='高等数学'
print '本程序的执行结果：'
execute @ReturnValue=stu_Score @c_name
print '程序的返回值='+ CAST(@ReturnValue as char(2))

四、实训步骤

1、大致的步骤：【打开数据库】--【打开student】--【可编程性】--【存储过程】--【新建存储过程】--【输入代码】--【验证】
2、源代码附在题目的后面了。
五、总结与分析
六、思考题
1、简述存储过程和自定义函数的区别。
答：存储过程：1. 功能强大，限制少
2. 不能直接引用返回值
3. 用select语句返回记录集
自定义函数：1. 诸多限制，有许多语句不能使用，许多功能不能实现
2. 可以直接引用返回值
3. 用表变量返回记录集
2、简述存储过程的优点。
答：存储过程的优点：
（1）实现了模块化编程，一个存储过程可以被多个用户共享和重用。
（2）存储过程具有对数据库立即访问的功能。
（3）使用存储过程可以加快程序的运行速度。
（4）使用存储过程可以减少网络流量。
（5）使用存储过程可以提高数据库的安全性。
3、系统存储过程和自定义存储过程有何区别？
答：最简单的区别就是系统存储过程是系统自带的。我们不可更改删除 ；而自定义存储过程是我们自己编辑的。在自定义存储过程中可以调用系统存储过程。
10