数据库系统概论实验4——存储过程.docVIP

数据库系统概论 实验四 存储过程 （4课时） 实验目的： 理解存储过程的作用和优点。 掌握SQL Server中存储过程的定义语法。 实验方法： 由于教材上存储过程部分内容较少，且实验环境SQL Server的存储过程方面语法与教材上有差异，因此本次实验课的内容包括两部分，先由老师结合实例进行补充讲解，然后布置实验内容、给出提示，学生参照实例进行实验。 实验要求： 本次实验要求撰写实验报告，要求同前。 实验内容： 第一部分 讲解 存储过程创建语法： CREATE PROCEDURE 过程名 (参数名1 类型，参数名2 类型，……) AS DECLARE 变量名1 类型 , //变量名以@开头 …… , 变量名n 类型 BEGIN //可缺省 T-SQL语句序列 END //可缺省 一、无参数的存储过程 1．创建 create proc p1 as select * from student where ssex=女 注：创建存储过程的语句要作为一个事务单独执行 2．执行 exec p1 注：exec后不要加procedure 3．修改 alter proc p1 as select * from student where ssex=女 select * from student where ssex=男 执行并查看结果 4．删除 drop proc p1 二、使用参数的存储过程 （一）使用输入参数 1．创建（功能：查询某同学选修的课程名称和成绩，直接显示） create proc p2 (@name char(8)) as select cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and sname=@name 注：参数名以@开头，多个参数间用逗号分隔，参数表可不用（）括起 2．执行 （1）按位置传递参数 exec p2 张三 （设有‘张三’这个学生名） 注：当有多个参数时顺序不能错。 （2）按参数名传递参数 exec p2 @name=张三 （二）使用输出参数返回值 调用语句要包含一个变量来接受返回值，所以要先声明一个变量。 在创建存储过程语句和调用语句中，每个输出参数都必须使用OUTPUT关键字。 1．创建（功能：查询某门课的平均成绩，作为返回值用于后续处理） create proc p3 (@cno char(4),@avgs numeric output) as select @avgs=avg(grade) from sc where cno=@cno 2．执行 declare @avg numeric exec p3 0001,@avg output （设有‘0001’这个课程号） select 平均成绩=@avg 注：此三条语句必须一起执行，否则不能识别变量@avg。 第二部分 实验 创建一个存储过程，查看‘0002’号课程的选修学生姓名和成绩，并执行。 创建一个存储过程，根据课程号查看某门课程的选修学生姓名和成绩。要求使用两种参数传递方法执行该存储过程。 创建一个存储过程，根据课程号获取选修某门课程的总人数作为返回值；执行该存储过程，显示总人数。 编写存储过程实现功能：根据课程名统计某课程的成绩分布情况，即按照各分数段统计人数。执行该存储过程，显示结果。（可采用作业提示方法，也可使用输出参数获取结果）  4．CREATE PROCEDURE statistic_mark (@name CHAR(50)) AS DECLARE @less60 INT, @b60a70 INT, @b70a80 INT, @b80a90 INT, @more90 INT, @curcno CHAR(4) BEGIN //可缺省 SELECT @curcno=cno FROM Course WHERE cname=@name; IF @curcno IS NULL begin ROLLBACK; RAISERROR (输入错误，没有该课程,16,1); END; SELECT @less60=count(*) FROM sc WHERE cno=@curcno AND grade60; SELECT @b60a70=count(*) FROM sc WHERE cno=@curcno AND grade=60