实训项目 存储过程的创建和使用.doc

网络数据库 实训报告一、实训目的和要求1、了解存储过程的作用；2、掌握创建、修改及删除存储过程的方法；3、掌握执行存储过程的方法。二、实训所需仪器、设备硬件：计算机软件：操作系统Windows XP、SQL Server 2005三、实训内容（一）不带参数的存储过程的创建和修改1、在student数据库中创建一个名为myp1的存储过程，该存储过程的作用是显示t_student中的全部记录。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = mpy1 AND type = P) DROP PROCEDURE mpy1GO CREATE PROCEDURE myp1 ASSelect * FROM T_STUDENTGO2、运行myp1，检查是否实现功能。use studentexec myp13、修改myp1，使其功能为显示t_student中班级为05541班的学生记录，然后测试是否实现其功能。set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE dbo.myp1 ASSelect * FROM T_STUDENTwhere left(s_number,5)=05541use studentexec myp14、创建一个存储过程myp2，完成的功能是在表t_student、表t_course和表t_score中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = myp2 AND type = P) DROP PROCEDURE myp2GO CREATE PROCEDURE myp2ASSelect 班级=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_SCOREWHERE T_STUDENT.S_NUMBER=t_SCORE.S_NUMBERAND T_COURSE.C_NUMBER=t_SCORE.C_NUMBERGO（二）带输入参数的存储过程的创建1、创建一个带有一个输入参数的存储过程stu_info，该存储过程根据传入的学生编号，在t_student中查询此学生的信息。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = Stu_Info AND type = P) DROP PROCEDURE Stu_InfoGO USE STUDENTGOCREATE 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_STUDENTWHERE S_NUMBER=S_NUMBERGO2、创建一个带有一个输入参数的存储过程stu_info2，该存储过程根据传入的学生编号，在t_student中查询此学生班级、学号、姓名和年龄。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = Stu_Info2 AND type = P) DROP PROCEDURE Stu_Info2GO USE STUDENTGOCREATE 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_STUDENTWHERE S_NUMBER=S_NUMBERGO3、创建一个带有一个输入参数的存储过程stu_info3，该存储过程根据传入的班级编号，查询该班所有同学的所有科目的成绩和。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = Stu_Info3 AND type = P) DROP PROCEDURE Stu_Info3GO CREATE PROCEDURE Stu_Info3 class_number varchar(10)AS Select sum(score) as 成绩和FROM t_scoreWHERE class_number=SUBSTRING(t_score.S_NUMBER,1, LEN(t_score.S_NUMBER)-2)4、创建一个带有两个输入参数的存储过程stu_info4，该存储过程根据传入的班级编号和课程号，查询该班所有同学的该课程的平均分。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = Stu_Info4 AND type = P) DROP PROCEDURE Stu_Info4GO CREATE PROCEDURE Stu_Info4 class_number varchar(10), c_number varchar(10)AS Select avg(t_score.score) as 平均分FROM t_scoreWHERE class_number=SUBSTRING(t_score.S_NUMBER,1, LEN(t_score.S_NUMBER)-2)and c_number=c_number5、创建一个带有两个输入参数的存储过程stu_info5，该存储过程根据传入的学号和课程号，在t_score中查询此学号的学生此课程号的成绩。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = Stu_Info5 AND type = P) DROP PROCEDURE Stu_Info5GO 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_scoreWHERE s_number=s_numberand c_number=c_number（三）带输入和输出参数的存储过程的创建1、创建一个带有参数的存储过程stu_age，该存储过程根据传入的学生编号，在t_student中算出此学生的年龄，并根据程序执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = Stu_age AND type = P) DROP PROCEDURE Stu_ageGO CREATE PROCEDURE Stu_Age S_NUMBER varchar(10), Age int OUTPUTAS DECLARE ErrorValue intSET ErrorValue=0SELECT Age=YEAR(GETDATE()-YEAR(BIRTHDAY) FROM T_STUDENTWHERE S_NUMBER=S_NUMBERIF (ERROR0) SET ErrorValue=ERRORRETURN ErrorValueGO2、在查询分析器中执行如下语句：use studentGODECLARE stuAge int, ReturnValue intEXECUTE ReturnValue=stu_age 0554103, stuAge OUTPUTPRINT 本程序的执行结果:PRINT 程序的返回值= + CAST(ReturnValue AS char(6)PRINT 学号为0554103的学生年龄是： + CAST(stuAge AS char(6) 执行上述程序，观察运行结果是否正确。正确 3、创建一个带参数的存储过程，输入参数为课程名称，查询有哪些班级的哪些学生参加了这门课程的考试及学生的考试成绩，存储过程返回0表示程序执行成功，返回其他整数，表示程序在执行时出现错误。USE STUDENTIF EXISTS (SELECT name FROM sysobjects WHERE name = Stu_age AND type = P) DROP PROCEDURE Stu_ageGO CREATE PROCEDURE Stu_Age c_name varchar(10)AS DECLARE ErrorValue intSET ErrorValue=0SELECT 班级=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_scoreWHERE t_course.c_number=t_score.c_numberand t_student.s_number=t_score.s_numberand c_name=c_nameIF (ERROR0) SET ErrorValue=ERRORRETURN ErrorValueGO-验证declare c_name varchar(30), ReturnValue intset c_name=高等数学print 本程序的执行结果：execute ReturnValue=stu_Score c_nameprint 程序的返回值=+ CAST(ReturnValue as char(2)四、实训步骤1、大致的步骤：【打开数据库】-【打开student】-【可编程性】-【存储过程】-【新建存储过程】-【输入代码】-【验证】2、源代码附在题目的后面了。五、总结与分析六、思考题1、简述存储过程和自定义函数的区别。答：存储过程：1. 功能强大，限制少2. 不能直接引用返回值3. 用select语句返回记录集自定义函数：1. 诸多限制，有许多语句不能使用，许多功能不能实现