数据库系统概论实验4——存储过程
数据库系统概论
实验四 存储过程
(4课时)
, 实验目的:
, 理解存储过程的作用和优点。
, 掌握SQL Server中存储过程的定义语法。 , 实验
方法
快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载
:
由于教材上存储过程部分内容较少,且实验环境SQL Server的存储过程方面语法与教
材上有差异,因此本次实验课的内容包括两部分,先由老师结合实例进行补充讲解,然后布
置实验内容、给出提示,学生参照实例进行实验。 , 实验要求:
本次实验要求撰写实验
报告
软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载
,要求同前。
, 实验内容:
第一部分 讲解
存储过程创建语法:
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。
第二部分 实验
1( 创建一个存储过程,查看‘0002’号课程的选修学生姓名和成绩,并执行。 2( 创建一个存储过程,根据课程号查看某门课程的选修学生姓名和成绩。要求使用两种参
数传递方法执行该存储过程。
3( 创建一个存储过程,根据课程号获取选修某门课程的总人数作为返回值;执行该存储过
程,显示总人数。
4( 编写存储过程实现功能:根据课程名统计某课程的成绩分布情况,即按照各分数段统计
人数。执行该存储过程,显示结果。(可采用作业提示方法,也可使用输出参数获取结
果)
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 grade<60;
SELECT @b60a70=count(*)
FROM sc
WHERE cno=@curcno AND grade>=60 AND grade<70;
SELECT @b70a80=count(*)
FROM sc
WHERE cno=@curcno AND grade>=70 AND grade<80;
SELECT @b80a90=count(*)
FROM sc
WHERE cno=@curcno AND grade>=80 AND grade<90;
SELECT @more90=count(*)
FROM sc
WHERE cno=@curcno AND grade>=90;
INSERT INTO Rank VALUES('[0,60)', @less60);
INSERT INTO Rank VALUES('[60,70)', @b60a70);
INSERT INTO Rank VALUES('[70,80)', @b70a80);
INSERT INTO Rank VALUES('[80,90)', @b80a90);
INSERT INTO Rank VALUES('[90,100]', @more90); END
GO
CREATE TABLE Rank(
division CHAR(20),
number INT);
exec statistic_mark '数据库'
select * from Rank
或使用输出参数:
create PROCEDURE statistic_mark (@name CHAR(50),@less60 INT output, @b60a70 INT
output,@b70a80 INT output,@b80a90 INT output,@more90 INT output)
AS
DECLARE
@curcno CHAR(4)
SELECT @curcno=cno
FROM Course
WHERE cname=@name;
IF @curcno IS NULL
RAISERROR ('输入错误,没有该课程',16,1);
SELECT @less60=count(*)
FROM sc
WHERE cno=@curcno AND grade<60;
SELECT @b60a70=count(*)
FROM sc
WHERE cno=@curcno AND grade between 60 AND 69;
SELECT @b70a80=count(*)
FROM sc
WHERE cno=@curcno AND grade>=70 AND grade<80;
SELECT @b80a90=count(*)
FROM sc
WHERE cno=@curcno AND grade>=80 AND grade<90;
SELECT @more90=count(*)
FROM sc
WHERE cno=@curcno AND grade>=90;
GO
declare @e INT , @d INT ,@c INT ,@b INT ,@a INT exec statistic_mark '数据库',@e output,@d output,@c output,@b output,@a output
select 'a'=@a,'b'=@b,'c'=@c,'d'=@d,'e'=@e