存储过程在考试系统中的应用

摘要:介绍了SQL Server存储过程及其优点,简单分析了考试系统需求,给出了核心的数据库结构,结合系统需求,使用存储过程完成考试系统中核心功能的算法实现。

关键词: 考试系统;存储过程;数据库;算法;

中图分类号:TP311.133.1文献标示码:A

Application of Stored Procedures in Exam System

SUN Yue-hong1,Pan Li2

(1. School of Software Engineering, Tongji University, Shanghai 201804, China)

(2. School of software,Nanyang Institute of Technology, Nanyang 473004, Henan, China)

Abstract:This paper describes the SQL Server stored procedure and its advantages, A simple analysis of the exam system requirements, Gives the core database structure, Combination of system requirements, Using stored procedures to achieve the core algorithm of the exam system.

Key Word:Exam System;Stored Procedures;Database;Algorithm

1 问题的提出

在SQL Server中存储过程是一组存储在服务器上的,能够执行复杂功能的预编译的Transact- SQL代码。

存储过程具有下列优点:

1)存储过程允许标准组件式编程(模块化设计),可以大大提高系统可移植性。

存储过程被创建以后,即可保存在数据库中,可以在程序中多次调用,而且数据库专业人员可随时对存储过程进行修改,而不影响应用程序代码,从而极大地提高了程序的可移植性。

2) 存储过程创建时预编译,调用时只需在网上传递少量代码和结果集,可以大大提高效率

存储过程可以把包含大量操作的Transaction-SQL 代码进行封装、预编译,以后需要时直接调用,效率更高;客户端调用存储过程时,通过网络发送该过程名和少量入口参数,数据库服务器就可以执行该过程,在执行完成后只返回结果集给客户端应用程序,而无须在网上传送大量的命令和中间结果数据,这样可以减轻网络负担,提高效率。

3) 存储过程支持变量、程序控制结构等语法,可以增强标准SQL 语言的功能和灵活性

标准SQL 语言受到自身的限制,它不能声明变量,不能使用分支、循环等控制结构。存储过程利用流控制语句和内部函数可以实现标准SQL 语言所不能实现的操作,从而提高了标准SQL 语言的功能和灵活性。

4) 存储过程保存成对象,可以进行授权等操作,增强系统的安全性。

存储过程和表、视图等对象一样,可以进行授权管理,由系统管理员对用户执行存储过程的权限进行限制,从而能够实现对相应的数据访问权限的限制,增强数据访问的安全性。

随着计算机和网络的普及,考试系统软件得到了长远发展,目前, 考试系统正成为越来越多的知识水平测评的主要方式。而考试系统中好多核心的功能需要执行大量的SQL语句来实现,如果直接在应用程序端编写、执行大量的SQL语句,执行效率低,难以维护和移植,安全性也难以保障。本文提出用存储过程实现考试系统中一些核心的业务逻辑,从而利用存储过程的优点提高程序效率,方便管理和维护。

2考试系统核心功能和主要数据结构设计

1)考试系统的核心功能

考试系统要完成的核心功能是组卷、改卷、成绩生成和数据迁移,这几个功能都用存储过程来实现。

(1) 组卷功能

要想通过考试系统进行考试,必须有试卷,试卷的生成―即组卷就变得非常重要,本文提出一种动态随机抽题组卷方法,考试前老师根据情况设置本次考试题型、知识点、难度与题目数量,考试时根据系统设置调用存储过程随机生成试卷。

(2) 改卷功能

考试结束后需要进行改卷工作,试卷的批改应尽量自动化,可以大大节省教师改卷的工作量。本文使用存储过程实现客观题目和部分主观题目的自动改卷。

(3)成绩生成功能

试卷批改过程中,成绩记录到试题表中每个试题上,为了方便进行成绩统计分析和试卷分析,需要把最终总成绩和各个题型成绩进行汇总保存到单独的表中,成绩的生成使用存储过程来实现。

2)考试系统主要数据结构

根据考试系统要实现的主要功能,设计如下基本表:

(1) 考生相关信息表

Studentinfo表记录考生基本信息,基本结构如下:(STUID,STUNAME,SEX,CLASS,MAJOR,Year,photo,Password)

Students表记录某场考试考生,除了上述字段外,还有ksid(考试id),kname(课程名称),stustatus(考生状态),online(是否在线),kssj(剩余时间)

(2) 题库相关信息表

Xzt表记录选择题题目,基本结构如下:

(ID,KNAME,TX,QUESTION,A,B,C,D,ANSWER,CHAPTER,TMND)

Zhgt表记录主观题题目,在xzt表基础上去掉A、B、C、D、E、F列,answer列长度增加即可。

(3) 试卷相关信息表

Xztpaper表记录抽取的选择题试题,在xzt表结构基础上添加如下内容:

(stuID,Ksid,UANSWER,Score,Uscore)

Zhgpaper表记录主观题试题,在zhgt表基础上加上相应字段即可

3核心功能的存储过程实现

(1) 组卷功能存储过程实现

CREATE PROCEDURE [dbo].[up_GetPaper]

@a_ksidVarchar(8),--试卷编号

@a_class nvarchar(50),--班级

@Rtn char(1) output

AS

BEGIN

--清除原有试题记录

DELETE FROM xztpaper WHERE stuid IN (SELECT stuid FROM studentINFO

WHERE class=@a_class) AND KSID=@a_ksid;

--获取班级学生学号

DECLARE Student_Cursor CURSOR FOR

SELECT stuidFROM StudentInfoWHERE class=@a_class ORDER BY stuid;

OPEN student_cursor;

FETCH NEXT FROM Student_Cursor INTO @stuid;

WHILE( @@FETCH_STATUS = 0)

BEGIN

--获取试题参数

DECLARE Paper_cursor CURSOR FOR

SELECT kmmc,tx,tmzj,tmmd,tmsl,tmfz FROM PaperPara WHERE sid=@a_ksid;

OPENPaper_Cursor;

FETCH NEXT FROM Paper_Cursor INTO @a_kmmc,@a_tx,@a_tmzj,@a_tmnd,@a_tmsl,@a_tmfz;

WHILE (@@FETCH_STATUS=0)

BEGIN

--抽取试题

SET @sql='INSERT INTO XztPaper(ksid,stuid,id,Kname,tx,Chapter,tmnd,question,A,B,C,D,E,F,Answer,Score) '

SET @sql=@sql+' SELECT TOP '+CAST(@a_tmsl AS VARCHAR(5))+' '+@a_ksid+','+@stuid+',ID,Kname,TX,Chapter,tmnd,Question,A,B,C,D,E,F,Answer,'+CAST(@a_tmfz AS VARCHAR(10))+' FROM XZT'

SET @sql=@sql+' WHERE (kname='''+@a_kmmc+''') AND (TX='''+@a_tx+''') AND (Chapter='''+@a_tmzj+''') AND (tmnd='''+@a_tmnd+''')'

SET @sql=@sql+' ORDER BY newid()'

EXEC(@sql)

commit;

--抽取主观题

FETCH NEXT FROM Student_Cursor INTO @stuid;

END

CLOSE Student_Cursor

DEALLOCATE Student_Cursor

END

(2) 改卷功能存储过程实现

CREATEproc [dbo].[proc_autopaper](@ksid varchar(10))

AS

begin

--对该ksid对应考试的单选题和多选题目答案完全正确的进行自动批改

update xztpaper_his set score= uscore

where tx='单选题' and answer=uanswer and ksid=@ksid

update xztpaper_his set score= uscore

where tx='多选题' and answer=uanswer and ksid=@ksid

--多选题目中答案未选全的进行处理

DECLARE match_cur CURSOR FOR

SELECT answer,uanswer,stuid,id

FROM xztpaper_his

WHERE tx='多选题' and ksid=@ksid--定义游标

OPEN match_cur

FETCH NEXT FROM match_cur INTO @answer,@uanswer,@stuid,@id

WHILE @@FETCH_STATUS = 0

BEGIN

if @answer@uanswer

begin

set @sql= dbo.match(@answer,@uanswer)

if @sql='1'--返回1说明漏选,得分在该题目分数基础上减1,否则说明选错,0分

update xztpaper_his set score=uscore -1

where tx='多选题' and stuid=@stuid and id=@id and ksid=@ksid

else

update xztpaper_his set score=0

where tx='多选题' and stuid=@stuid and id=@id and ksid=@ksid

end

FETCH NEXT FROM match_cur INTO @answer,@uanswer,@stuid,@id

END

CLOSE match_cur

DEALLOCATE match_cur

--填空题目成绩类似多选题进行处理

end

4 结束语

由于存储过程的几个优点,在能用SQL语句实现复杂业务逻辑的情况下,尽量把这些语句封装到存储过程中。本文利用SQL Server存储过程实现了考试系统中的几个核心功能,提高了系统效率,方便了SQL代码的管理和维护,收到了很好的效果。

参考文献:

[1]赵杰,李涛,朱慧.SQL Server 数据库设计与实现教程[M].北京:北京大学出版社,2003.

[2]杜军平,黄杰.SQL Server 2000 数据库开发[M].北京:机械工业出版社,2001.

[3]温春明.SQL Server 存储过程研究[J].福建电脑,2007.

[4]曹耀辉.SQL Server 存储过程在系统开发中的应用[J].