学号-姓名-实验XX实验报告(模板)

2011-2012学年第二学期课程实验报告课程名称：数据库系统原理

实验名称：

姓名周东涛班级软件2班学号1001030181 实验台编号同组同学

实验课表现出勤、表现得分25% 25 实验报告

得分50%

实验总分操作结果得分25% 25

实验目的

1.掌握存储过程的概念及优点

2.掌握存储过程的创建和使用方法

3.掌握游标的概念及优点

4.掌握游标的创建和使用方法

实验内容（实验步骤和实验结果的简单描述，方便同学自己以后阅读）

以下题目均在STUxxxx（学生管理）数据库中完成。

1．使用游标实现如下功能：显示每个学生的学号、姓名、各门功课的成绩与平均成绩。格式如下：学号姓名课程名称成绩

……………………

use stu1001030181

declare cursor1 cursor

for select学号from xs

open cursor1

if@@error= 0

begin

declare @stuNo varchar(50)

declare @average int

fetch next from cursor1 into @stuNo

while@@fetch_status=0

begin

select @average=A VG(成绩)from xs_kc

where学号=@stuNo

select xs.学号,姓名,课程名,成绩,@average as平均成绩

from xs,kc,xs_kc

where xs.学号=xs_kc.学号and kc.课程号=xs_kc.课程号and xs.学号=@stuNo

fetch next from cursor1 into @stuNo

end

end

close cursor1

deallocate cursor1

2．使用游标实现如下功能：修改XS表中“总学分”字段的值，使其为该学生所学各门功课的学分之和。

declare cursor2 cursor

for select学号,总学分from xs

open cursor2

if@@error= 0

begin

declare @sno char(10)

declare @myscore int

fetch next from cursor2 into @sno,@myscore

while@@fetch_status=0

begin

update xs set总学分=(select sum(kc.学分)

from kc,xs_kc

where xs_kc.学号=@sno and kc.课程号=xs_kc.课程号)

where学号=@sno

fetch next from cursor2 into @sno,@myscore

end

end

close cursor2

deallocate cursor2

3．创建一存储过程proc1，显示指定学生指定课程的成绩，然后执行该存储过程。

CREATE PROCEDURE proc1

@newsno char(10),

@newsub char(10),

@newscore int OUTPUT

AS

SELECT @newscore=xs_kc.成绩

FROM xs,xs_kc

WHERE xs.学号=@newsno and xs.学号= xs_kc.学号and xs_kc.课程号= @newsub

DECLARE @score int

EXEC proc1 4102101,101,@score OUTPUT

SELECT @score

4．创建一存储过程proc2，显示其指定课程的成绩在指定范围内的学生学号、姓名、课程名和成绩，并返回其人数，然后执行该存储过程。

CREATE PROCEDURE proc2

@newsno char(10),

@newname char(10),

@newsubname char(10),

@newscoresmall int,

@newscorelarge int,

@newsum int OUTPUT

AS

SELECT @newsum =sum(xs_kc.成绩)

FROM xs,xs_kc,kc

WHERE xs.学号=@newsno and xs.学号=xs_kc.学号and xs_kc.课程号=kc.课程号and kc.课程名= @newsubname and xs_kc.成绩BETWEEN @newscoresmall AND @newscorelarge

DECLARE @sum int

EXEC proc2 4102101,101,计算机基础,60,100,@sum OUTPUT

SELECT @sum

5．创建一存储过程xs_insert,用于实现往xs表中插入一条记录，并返回执行状态码信息：如果插入成功，则返回0；否则，则返回1。

CREATE PROCEDURE XS_INSERT

@SNO CHAR(10),

@NAME CHAR(10),

@SEX BIT,

@DATE SMALLDATETIME,

@SCORE INT,

@CLASS CHAR(10),

@TEMP TEXT,

@RESULT INT OUTPUT

AS

INSERT INTO XS V ALUES

(@SNO,@NAME,@SEX,@DATE,@SCORE,@CLASS,@TEMP)

SET @RESULT=@@ERROR

DECLARE @ERROR INT

EXEC XS_INSERT '4102104','小明',1,'1991-1-1',100,'计算机',NULL,@ERROR OUTPUT

SELECT@ERROR

6．创建一存储过程xs_update,用于对指定学号指定课程名称的学生成绩进行修改。

CREATE PROCEDURE XS_UPDATE

@SNO CHAR(10),

@SUB CHAR(10),

@SCORE INT

AS

UPDA TE XS_KC SET成绩= @SCORE WHERE XS_KC.学号=@SNO AND XS_KC.课程号=KC.课程号AND KC.课程名= @SUB

7．创建一存储过程，用于统计每个专业计算机基础成绩在各个分数段的学生人数。分数段划分为：低于60分，60～70分，70 ～80分，80～90分，90～100分。

专业低于60 60～70 70～80 80～90 90～100

计算机0 3 7 5 4

………………………………

ALTER PROC KC_SUM

@SCORE1 INT,

@SCORE2 INT,

@SCORE3 INT,

@SCORE4 INT,

@SCORE5 INT

AS

SELECT @SCORE1 =COUNT(*)

FROM XS_KC,KC,XS

WHERE XS_KC.成绩BETWEEN 0 AND 60 AND XS_KC.课程号=KC.课程号AND XS.学号= XS_KC.学号AND KC.课程名='计算机基础'GROUP BY XS.专业

SELECT @SCORE2 =COUNT(*)

FROM XS_KC,KC,XS

WHERE XS_KC.成绩BETWEEN 60 AND 70 AND XS_KC.课程号=KC.课程号AND XS.学号= XS_KC.学号AND KC.课程名='计算机基础'GROUP BY XS.专业

SELECT @SCORE3 =COUNT(*)

FROM XS_KC,KC,XS

WHERE XS_KC.成绩BETWEEN 70 AND 80 AND XS_KC.课程号=KC.课程号AND XS.学号= XS_KC.学号AND KC.课程名='计算机基础'GROUP BY XS.专业

SELECT @SCORE4 =COUNT(*)

FROM XS_KC,KC,XS

WHERE XS_KC.成绩BETWEEN 80 AND 90 AND XS_KC.课程号=KC.课程号AND XS.学号= XS_KC.学号AND KC.课程名='计算机基础'GROUP BY XS.专业

SELECT @SCORE5 =COUNT(*)

FROM XS_KC,KC,XS

WHERE XS_KC.成绩BETWEEN 90 AND 100 AND XS_KC.课程号=KC.课程号AND XS.学号= XS_KC.学号AND KC.课程名='计算机基础'GROUP BY XS.专业

实验过程中遇到的问题以及如何解决的？（可以写多条，是否认真填写将影响实验成绩）

1、在实验过程中我遇到了不知道如何使用游标的问题，通过老师上课讲的如何声明游标、打开游标、提取数据、关闭游标的方式，我解决了这个问题。

2、在实验过程中我遇到了Cursorfetch: INTO 列表中声明的变量数目必须与所选列的数目相同的问题，通过加入所缺的变量的方式，我解决了这个问题。

3、在实验过程中我遇到了不知道如何创建存储过程的问题，通过看书找到语句CREATE PROCEDURE PROC........AS.........的方式，我解决了这个问题

没有问题的情况下，是否有创新思路（或多做哪些工作）。

本次实验的体会（可以写多条，是否认真填写将影响实验成绩）

通过本实验，我理解/了解/熟悉了.......

1.我熟悉了如何声明游标、打开游标、提取数据、关闭游标

2.我熟悉了通过create procedure proc as创建存储过程对表进行增删查改数据,然后执行存储过程

3.我了解了使用游标的优点和存储过程的优点

思考题

1．总结一下SQL Server游标的使用方法。

答：使用游标有四种基本的步骤:声明游标、打开游标、提取数据、关闭游标.

声明游标:

象使用其它类型的变量一样,使用一个游标之前,首先应当声明它。游标的声明包括两个部分:游标的名称;这个游标所用到的SQL语句。如要声明一个叫作Cus-tomerCursor的游标用以查询地址在北京的客户的姓名、帐号及其余额,您可以编写如下代码:

DECLARE CustomerCursor CURSOR FOR

SELECT acct_no,name,balance

FROM customer

WHERE province="北京";

打开游标:

声明了游标后在作其它操作之前,必须打开它。打开游标是执行与其相关的一段SQL语句,例如打开上例声明的一个游标,我们只需键入:

OPEN CustomerCursor;

提取数据:

当用OPEN语句打开了游标并在数据库中执行了查询后,您不能立即利用在查询结果集中的数据。您必须用FETCH语句来取得数据。一条FETCH语句一次可以将一条记录放入程序员指定的变量中。事实上,FETCH语句是游标使用的核心。在DataWindow和DataStore中,执行了Retrieve()函数以后,查询的所有结果全部可以得到;而使用游标,我们只能逐条记录地得到查询结果。

已经声明并打开一个游标后,我们就可以将数据放入任意的变量中。在FETCH语句中您可以指定游标的名称和目标变量的名称。如下例:

FETCH CustmerCur-sor

INTO:ls_acct_no,

:ls_name,

:ll_balance;

从语法上讲,上面所述的就是一条合法的取数据的语句,但是一般我们使用游标却还应当包括其它的部分。正如我们前面所谈到的,游标只能一次从后台数据库中取一条记录,而在多数情况下,我们所想要作的是在数据库中从第一条记录开始提取,一直到结束。所以我们一般要将游标提取数据的语句放在一个循环体内,直至将结果集中的全部数据提取后,跳出循环圈。通过检测SQLCA.SQL-CODE的值,可以得知最后一条FETCH语句是否成功。一般,当SQLCODE值为0时表明一切正常,100表示已经取到了结果集的末尾,而其它值均表明操作出了问题,这样我们可以编写以下的代码:

lb_continue=True

ll_total=0

DO WHILE lb_continue

FETCH CustomerCur-sor

INTO:ls_acct_no,

:ls_name,

:ll_balance;

If sqlca.sqlcode=0 Then

ll_total+=ll_balance

Else

lb_continue=False

End If

LOOP

循环体的结构有多种,这里提到的是最常见的一种。也有的程序员喜爱将一条FETCH语句放在循环体的前面,循环体内再放置另外一条FETCH语句,并检测SQLCA.SQLCODE是否为100（见以下实例）。但是这样做,维护时需同时修改两条FETCH语句,稍麻烦了些。

关闭游标

在游标操作的最后请不要忘记关闭游标,这是一个好的编程习惯,以使系统释放游标占用的资源。关闭游标的语句很简单:

CLOSE CustomerCursor;

使用Where子句

我们可以动态地定义游标中的Where子句的参数,例如在本例中我们是直接定义了查询省份是北京的记录,但也许在应用中我们要使用一个下拉式列表框,由用户来选择要查询的省份,我们该怎样做呢?

我们在前面曾经提到过,DECLARE语句的作用只是定义一个游标,在OPEN语句中这个游标才会真正地被执行。了解了这些,我们就可以很方便地实现这样的功能,在DECLARE的Where子句中加入变量作参数,如下所示:

DECLARE CustomerCursor CURSOR FOR

SELCECT acct_no,name,balance

FROM customer

WHERE province=:ls_province;

// 定义ls_province的值

OPEN CustomerCursor;

游标的类型

同其它变量一样,我们也可以定义游标的访问类型:全局、共享、实例或局部,游标变量的命名规范建议也同其它变量一样。

2．总结一下使用存储过程的好处。

答：使用存储过程的好处

相对于直接使用SQL语句，在应用程序中直接调用存储过程有以下好处：

(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。

(2)执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

(3)更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

(4) 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

教师评价

实验态度 A. 很认真 B. 认真 C. 比较认真 D. 不认真独立完成情况 A. 很好 B. 好 C. 比较好 D. 不好收获和体会 A. 多 B. 比较多 C. 少 D. 很少思考题回答情况 A. 很好 B. 好 C. 比较好 D. 不好

评阅教师：陈红顺

日期：