实验11 存储过程

实验11 存储过程

1．实验目的

（1）掌握使用SQL Server管理平台和Transact-SQL语句创建存储过程、执行存储过程、修改存储过程、删除存储过程的用法。

（2）理解使用SQL Server管理平台和Transact-SQL语句查看存储过程定义、重命名存储过程的用法。

2．实验内容及步骤

请先附加studentsdb数据库，然后完成以下实验。

（1）CREATE PROCEDURE st_g

AS

BEGIN

SELECT x.学号,x.姓名,y.分数

FROM student_info x ,grade y

Where x.学号=y.学号

END

该程序完成的功能是创建查询学生所选课程的分数的存储过程。

（2）设计一个存储过程proc_grade完成这样的功能：查询grade表中课程编号为’k002’的学号、分数信息，并使存储过程不能使用sp_helptext查看（即加密），请编写程序实现。

答：

Create proc proc_grade

with encryption

As

select学号,分数

from grade

where课程编号='k002'

-----

exec proc_grade

sp_helptext proc_grade

（3）修改存储过程proc_grade，查询grade表中课程编号为’k001’的学号、分数信息，去掉proc_grade加密性，使其在运行时重新编译。

答：

Alter proc proc_grade

As

select学号,分数

from grade

where课程编号='k001'

-----

exec proc_grade

sp_helptext proc_grade

（4）在student_info 表中增加一列名为“手机号”，数据类型char(11)。设计一个存储过程proc_tel，查询student_info表中末尾数字为5的手机号码。

答：

Alter table student_info

add手机号char(11)

default'13515125'

Create proc proc_tel

as

select手机号

from student_info

where手机号like'%5'

-----

exec proc_tel

（5）设计一个存储过程proc_avg，输出grade表中每个学生的平均成绩。

答：

Create proc proc_avg

as

select学号,AVG(分数)平均成绩

from grade

group by学号

-----

exec proc_avg

（6）设计一个存储过程proc_list，查询grade表中指定课程(参数@cid char(4))的成绩排名前3的学生成绩信息。编写程序并调用该存储过程。

答：

Create proc proc_list

@cid char(4)

as

select top 3 *

from grade

where课程编号=@cid

ORDER BY分数DESC

------

exec proc_list'k001'

（7）设计一个存储过程proc_stu，查询某门课程某个分数段的学生信息，显示学号、姓名、分数，要求课程名称和分数段均为输入参数，请编写程序并调用该存储过程。

答：

Create proc proc_stu

@cid char(50),

@min int,

@max int

as

select a.学号,姓名,b.分数

from student_info a join grade b on a.学号=b.学号

join curriculum c on b.课程编号=c.课程编号

where c.课程名称=@cid and b.分数between@min and@max

----

exec proc_stu'大学计算机基础',85,100

（8）设计一个存储过程proc_course，查询某门课程的平均成绩并将其输出给用户。课程名称为输入参数，平均成绩为输出参数，编写程序并调用该存储过程。

答：

Create proc proc_course

@cid char(50),

@avg float output

as

select@avg=AVG(a.分数)

from grade a join curriculum b on a.课程编号=b.课程编号

where b.课程名称=@cid

--------

declare@avg float

exec proc_course'大学计算机基础',@avg output

select@avg平均成绩

（9）设计一个存储过程，用于修改指定学生(参数@sid char(4))指定课程(参数为@cid char(4))的分数(@score decimal(3,1))，并输出该生的平均成绩（输出参数@avgs），要求输出格式为：“该生的最新平均成绩为：”。编写并调用该存储过程(修改学号’0002’的学生的修读课程‘K003’的成绩改为96)。

答：

Create proc A

@sid char(4),

@cid char(4),

@score decimal(3,1),

@avgs float output

as

update grade

set分数=@score

where学号=@sid and课程编号=@cid

select@avgs=AVG(分数)

from grade

where学号=@sid

----

declare@avgs float

exec A'0002','K003',96,@avgs output

select@avgs'该生的最新平均成绩为：'

（10）设计一个存储过程proc_credit完成这样的功能：输入学号@sid、课程名称@cname参数值，将查询curriculumgrade表，并从输出参数@score、@credit 获取该学生该课程的成绩和学分，如果分数大于等于60，则返回对应课程的学分，否则返回学分值0，请编写程序并调用该存储过程。

答：

Create proc proc_credit

@sic char(4),

@cname char(50),

@score int output,

@credit int output

as

IF NOT EXISTS(select a.分数,b.学分

from grade a join curriculum b on a.课程编号=b.课程编号

WHERE a.学号=@sic and b.课程名称=@cname and a.分数>=60)

RETURN 0

select@score=a.分数,@credit=b.学分

from grade a join curriculum b on a.课程编号=b.课程编号

WHERE a.学号=@sic and b.课程名称=@cname and a.分数>=60

return@score

------

DECLARE@credit int

DECLARE@score int

DECLARE@a int

EXEC@a=proc_credit'0001','大学计算机基础',@score output,@credit output IF@a=0

SELECT'返回学分值为0'

else

select@credit学分