实验8：游标和存储过程

【实验目的】

1、了解游标、存储过程的作用

2、熟悉游标和存储过程的特点

3、掌握游标的使用步骤和存储过程的创建和管理

【实验设备及器材】

1、硬件：PC机；

2、软件：(1)Windows7; (2)Microsoft SQL Server 2012。

【主要内容】

游标的使用步骤；当前游标集的修改；存储过程的创建和管理。

【实验内容及要求】/【实验步骤及结果】

下面所有的操作在“学生管理数据库”中进行。（附加数据库，T-SQL语句）

一、存储过程操作

1.设计简单存储过程AVG_GRADE，求出计算机原理这门课程的平均成绩；并执行验证。

USE?学生管理数据库

GO

CREATE?PROCEDURE?AVG_GRADE

AS

SELECT?成绩信息表.课程号,Round?(AVG(成绩信息表.成绩),1)?

AS?'平均成绩'

FROM?学生信息表 join?成绩信息表 ON?学生信息表.学号=成绩信息表.学号 join?课程信息表 ON?课程信息表.课程号=成绩信息表.课程号 and?课程信息表.课程名='计算机原理'

GROUP?BY??成绩信息表.课程号

EXEC??AVG_GRADE

2.设计存储过程S_C_SC，求出某课程的考试学生姓名和成绩；并执行验证。（请注意：请考虑是否已经存在该存储过程的情况，如果已有该存储过程，请先删除，再创建）

USE?学生管理数据库

GO

IF?EXISTS?(SELECT?*?FROM?SYS.OBJECTS?WHERE?NAME='S_C_SC'?AND?TYPE='P')

??DROP?PROCEDURE?S_C_SC

GO

CREATE?PROCEDURE?S_C_SC

??@course varchar(50)

AS

??SELECT?课程信息表.课程名,学生信息表.姓名,成绩信息表.成绩

??FROM?学生信息表 JOIN?成绩信息表 ?ON?学生信息表.学号=成绩信息表.学号

???????JOIN?课程信息表 ?ON?成绩信息表.课程号=课程信息表.课程号 AND?课程名=@course

GO

EXEC S_C_SC '计算机原理'

1. 修改2建立的存储过程，求出某课程的考试人数和平均分；并执行验证。

USE?学生管理数据库

GO

ALTER?PROCEDURE?S_C_SC

??@course varchar(50)

AS

??SELECT?AVG(成绩信息表.成绩)?AS?平均分,COUNT(?@course)?AS?人数

??FROM?课程信息表 ?JOIN?成绩信息表 ?ON?课程信息表.课程号=成绩信息表.课程号 AND?课程名=@course

GO

EXEC?S_C_SC?'计算机原理'

4.建立存储过程SNAME_S，查询出所有姓名中有军字的同学的学号、姓名、性别和专业；并执行验证。

USE?学生管理数据库

GO

CREATE?PROCEDURE?SNAME_S

@course varchar(50)

AS

??SELECT?学生信息表.学号,学生信息表.姓名,学生信息表.性别,学生信息表.专业

??FROM?学生信息表

??WHERE?学生信息表.姓名 like?@course

GO?

EXEC?SNAME_S?'%军%';

5.建立存储过程I_II，利用输出参数计算阶乘；并执行验证。

USE?学生管理数据库

GO

CREATE?PROCEDURE?I_II

??@SUM_I INT,

??@SUM_II FLOAT?OUTPUT

AS

??DECLARE?@X int,@Y float

??SET?@X=1

??SET?@Y=1

??WHILE?@X<=@SUM_I

??BEGIN

????SET?@Y=@Y*@X

????SET?@X=@X+1

??END

??SET??@SUM_II=@Y

GO

DECLARE?@OUTPUT FLOAT

EXEC?I_II?5,@OUTPUT OUTPUT

PRINT?STR(@OUTPUT)

二、游标操作

1.使用游标逐行提取学生信息表的记录。

USE?学生管理数据库

GO

DECLARE?SUM_I CURSOR

FOR?

??SELECT?*?FROM?学生信息表

OPEN?SUM_I

FETCH?NEXT?FROM?SUM_I

WHILE?@@FETCH_STATUS=0

????FETCH?NEXT?FROM?SUM_I

CLOSE?SUM_I

DEALLOCATE?SUM_I

1. 使用游标更改学生信息表的第2行的姓名，姓名随意改；并查询表验证。

USE?学生管理数据库

GO

DECLARE?SUM_I SCROLL?CURSOR

FOR?

??SELECT?*?FROM?学生信息表

OPEN?SUM_I

FETCH?ABSOLUTE?2 FROM?SUM_I

UPDATE?学生信息表

SET?姓名='刘雄狄'

WHERE?CURRENT?OF?SUM_I

CLOSE?SUM_I

DEALLOCATE?SUM_I

GO

SELECT?*?FROM?学生信息表

3.?创建一个课程信息表的临时表，删除该表的第3行记录；并查询表验证。

USE?学生管理数据库

GO

IF?OBJECT_ID('tempdb..#课程临时表')?is?not?null?

??DROP?TABLE?#课程临时表

SELECT?*?INTO?#课程临时表 FROM?课程信息表

SELECT?*?FROM?#课程临时表

SELECT?*?FROM?课程信息表

GO

DECLARE?SUM_I INSENSITIVE?SCROLL?CURSOR

FOR?

??SELECT?课程号 FROM?课程信息表

OPEN?SUM_I

FETCH?ABSOLUTE?3 FROM?SUM_I

DECLARE?@course varchar(50)

FETCH?ABSOLUTE?3 FROM?SUM_I INTO?@course

PRINT?@course

DELETE?FROM?#课程临时表

WHERE?课程号=@course

CLOSE?SUM_I

DEALLOCATE?SUM_I

GO

SELECT?*?FROM?#课程临时表

SELECT?*?FROM?课程信息表