实验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 课程信息表