数据库系统概论实验5——存储过程.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(3),@avgs numeric(5,1) output) as select @avgs=avg(grade) from sc where cno=@cno 2．执行 declare @avg numeric(5,1) exec p3 001,@avg output （设有‘001’这个课程号） select 平均成绩=@avg 注：此三条语句必须一起执行，否则不能识别变量@avg。 第二部分 实验 创建一个存储过程，查看‘002’号课程的选修学生姓名和成绩，并执行。 创建一个存储过程，根据课程号查看某门课程的选修学生姓名和成绩。要求使用两种参数传递方法执行该存储过程。 创建一个存储过程，根据课程号获取选修某门课程的总人数作为返回值；执行该存储过程，显示总人数。 编写存储过程实现功能：根据课程名统计某课程的成绩分布情况，即按照各分数段（60,60~70,70~80,80~90,90~100）统计人数。执行该存储过程，显示结果。（可采用提示方法，也可使用输出参数获取结果；执行前先增添一些成绩数据。） （提示步骤：1.创建一表用来存放统计结果； 2.编写存储过程: ①根据课程名查出课程号；②查询该课程各分数段的人数，存入相应变量；③将上步统计的各分数段及人数插入新表中； 3.执行存储过程，并查看新表内容。） *首先输入并执行以下语句： create database stu GO use stu create table student( sno char(4), sname char(8), ssex char(2)) insert into student values(0001,李好,男) insert into student values(0002,张灵,女) insert into student values(0003,刘文刚,男) insert into student values(0004,孟蕾,女) create table course( cno char(3), cname char(20))