利用SQL Server存儲過(guò)程提高在線(xiàn)考試系統的性能

隨著(zhù)計算機網(wǎng)絡(luò )技術(shù)和數據庫技術(shù)的飛速發(fā)展，高校的數字化校園建設進(jìn)程日益深入，其中數字化教學(xué)與學(xué)習環(huán)境建設成為其核心內容。為推動(dòng)數字化校園建設進(jìn)程，創(chuàng )建數字化教學(xué)環(huán)境,利用Delphi7和SQL Server2000開(kāi)發(fā)出適用于多學(xué)科的高校通用計算機在線(xiàn)考試軟件系統。Delphi在基于C/S模式的數據庫應用開(kāi)發(fā)領(lǐng)域具有很強的數據庫支持能力，因為它提供了豐富的數據庫操作組件，常用的有TQuery、TTable、TADOQuery和TADOTable等[1]。通過(guò)這些組件，程序員可以在短時(shí)間內完成一個(gè)應用程序模塊的設計，大大減少了開(kāi)發(fā)人員的工作量。但是，這些組件在操作數據庫時(shí)卻存在局限性，即必須將數據庫中的記錄從服務(wù)器端取出放入到客戶(hù)端，再根據查詢(xún)條件進(jìn)行選擇。這種實(shí)現方法，如果待訪(fǎng)問(wèn)的數據量較大，勢必因為查詢(xún)時(shí)間較長(cháng)而降低程序執行的速度，從而降低應用程序的性能。解決這一問(wèn)題利用了SQL Server2000的存儲過(guò)程來(lái)實(shí)現[2]。
1 存儲過(guò)程及其優(yōu)點(diǎn)
1.1 存儲過(guò)程的定義
存儲過(guò)程是存儲在服務(wù)器上的一組預先定義并編譯好的用來(lái)實(shí)現某種特定功能的SQL語(yǔ)句。它可以接受參數、返回狀態(tài)值或參數值。存儲過(guò)程在服務(wù)器端執行，通過(guò)應用程序調用的方法實(shí)現，執行結束后再把結果返回到客戶(hù)端。
1.2 存儲過(guò)程的優(yōu)點(diǎn)
（1）存儲過(guò)程執行速度快，便于提高系統的性能。由于存儲過(guò)程在第一次執行之后，其執行規劃就儲存在高速緩沖存儲區中，在以后的操作中。只需從高速緩沖存儲區中調用編譯好的二進(jìn)制形式存儲過(guò)程來(lái)執行[3]。
（2）減少網(wǎng)絡(luò )流量，提高系統執行效率?？蛻?hù)端應用程序調用一個(gè)存儲過(guò)程，只需通過(guò)網(wǎng)絡(luò )發(fā)送該過(guò)程名和少量入口參數，數據庫服務(wù)器就可執行該過(guò)程，執行完成后，只返回結果狀態(tài)或將最終結果集給客戶(hù)端應用程序，無(wú)需通過(guò)網(wǎng)絡(luò )傳送大量的SQL操作命令和中間結果，最大限度地減少網(wǎng)絡(luò )通信負擔，提高了執行效率。
（3）維護了數據庫的安全性。如果用戶(hù)被授予執行存儲過(guò)程的權限，即使該用戶(hù)沒(méi)有在執行該存儲過(guò)程中可參考的表或視圖的訪(fǎng)問(wèn)權限，該用戶(hù)也可以完全執行該存儲過(guò)程而不受影響。因此，可以通過(guò)創(chuàng )建存儲過(guò)程來(lái)完成數據的插入、更新和刪除等操作，并且可以通過(guò)編程方式控制在操作中對信息的訪(fǎng)問(wèn)權限[4]。
（4）提高軟件系統的可維護性。存儲過(guò)程在創(chuàng )建時(shí)就在服務(wù)器上經(jīng)過(guò)調試和編譯，可以避免不必要的錯誤出現。另外，存儲過(guò)程將相關(guān)業(yè)務(wù)邏輯封裝在一起，可以大大提高整個(gè)軟件系統的可維護性。因為當相關(guān)業(yè)務(wù)邏輯發(fā)生變化時(shí)，不需要修改并編譯客戶(hù)端應用程序，只需要修改位于服務(wù)器端的實(shí)現相應業(yè)務(wù)邏輯的存儲過(guò)程即可。
（5）充分增強SQL語(yǔ)言的功能和靈活性。存儲過(guò)程可以用流控制語(yǔ)句編寫(xiě)，有很強的靈活性，可以完成復雜的判斷和運算。
基于以上優(yōu)點(diǎn)，在進(jìn)行C/S模式數據庫應用系統開(kāi)發(fā)時(shí)，通常把比較浪費時(shí)間、影響網(wǎng)絡(luò )傳送的相關(guān)業(yè)務(wù)邏輯編寫(xiě)成存儲過(guò)程由服務(wù)器執行，同時(shí)利用服務(wù)器的SQL語(yǔ)法進(jìn)行優(yōu)化處理，只將少量的結果顯示在客戶(hù)端，充分提高軟件系統的性能[5]。
2 存儲過(guò)程的創(chuàng )建方法
以SQL Server 2000數據庫管理系統為背景，創(chuàng )建方法如下：
CREATE PROC[EDURE]
procedure_name[;number]
[{@parameter data_type}[VARYING][=default] [OUTPUT] ][,...n]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement [...n ]
下面對語(yǔ)法中各選項進(jìn)行說(shuō)明：
（1）選項“procedure_name”指存儲過(guò)程名，不能超過(guò)128個(gè)字符，“[;number]”指程序編號，用于區別同名的存儲過(guò)程。
（2）選項“@parameter”表示參數，每個(gè)存儲過(guò)程中最多可以設定1 024個(gè)參數，要求在每個(gè)參數名前加上符號“@”。
（3）選項“[=default]”指為對應參數設定默認值。
（4）選項“OUTPUT”用來(lái)指定參數既有輸入又有輸出。即在調用這個(gè)存儲過(guò)程時(shí)，如果所指定的參數是要輸入的參數，同時(shí)也需要在結果中輸出，則該項必須為“OUTPUT”。
（5）選項“WITH”用來(lái)指定存儲過(guò)程的處理方式。
（6）選項“FOR REPLICATION”表示只能由復制方式執行[6]。
3 存儲過(guò)程在考試軟件系統中的應用
（1）考試系統在運行時(shí)要經(jīng)過(guò)三個(gè)步驟，考生抽取套題編號、抽取套題內容和將套題內容插入答題表進(jìn)行答題。如果不采用存儲過(guò)程，則需用下面方法實(shí)現。
①隨機抽取套題編號
select 套題編號from tblcrecord order by newid()
②抽取對應套題內容,套題編號存儲在dm.strNum中
with qry1 do
Begin
Close ;
SQL.Clear ;
SQL.Add(′Select * from tblTK Where 套題編號
='''+dm.strNum +''');
Open ;
End;
③插入考試題到考試表tblKS中
qry1.First;
While not qry1.Eof Do
begin
qry2.Append;
qry2.FieldByName(‘考生學(xué)號’).Value:= qry1.Field-
ByName(‘考生學(xué)號’).Value;
…
qry2.Post;
end;
顯然，此種方法步驟繁瑣，耗時(shí)較多，在考試過(guò)程中會(huì )產(chǎn)生抽題緩慢的現象，系統性能?chē)乐亟档?。為避免此種情況的發(fā)生，考試系統采用了存儲過(guò)程的方法，代碼如下[7]：
CREATE PROCEDURE cp_Paper_Add
@MyNo char(10),
@Mynum char(20)
AS
set nocount on
exec(′insert into tblKS (學(xué)生學(xué)號、套題編號、試題序號、試題內容、試題答案、試題分數、是否答題、套題名稱(chēng)、考試科目、試題類(lèi)型、學(xué)生分數、類(lèi)別序號) select''''+@MyNo+'''',''''+@Mynum+''',試題序號,試題內容,試題答案,試題分數,“否”,套題名稱(chēng),科目名稱(chēng),試題類(lèi)型,0,類(lèi)別序號 from tblTK where 套題編號='''+@Mynum+''' )
GO
通過(guò)執行存儲過(guò)程，將題庫中與考生抽取套題編號相匹配的記錄提取出來(lái)，并批量插入到考試表中，同時(shí)對考生分數進(jìn)行了初始化為0的動(dòng)作。而本考試系統就是將考生的學(xué)號和考生抽取的套題編號作為參數傳遞給存儲過(guò)程，因此，大幅度提高了考試軟件的效率。
（2）在考試系統進(jìn)行不同類(lèi)型題目抽取時(shí)，如果在Delphi中直接應用SQL語(yǔ)句，應使用下面代碼：
with cds1 do
Begin
Close ;
SQL.Clear ;
SQL.Add(′Select * from tblKS Where 學(xué)生學(xué)號=′);
SQL.Add(‘ ?蓯+dm.strNo +?蓯 and 試題類(lèi)型=′′單項選擇題′′ order by 試題序號′);
Open ;
End;
此時(shí)，程序是在運行時(shí)才編譯的。這樣就增加了運行的時(shí)間，同時(shí)還要把完整的SQL語(yǔ)句傳遞給Sql Server服務(wù)器執行，這樣考試過(guò)程中勢必增加網(wǎng)絡(luò )流量，降低軟件的運行效率。
如果采用存儲過(guò)程，可用下面方法實(shí)現：
CREATE PROCEDURE sp_Query_All
@strNo char(10)
AS
set nocount on
Select Select * from tblKS Where 學(xué)生學(xué)號=@strNO and 試題類(lèi)型=′′單項選擇題′′ order by 試題序號
GO
在客戶(hù)端調用方法如下：
with cds1 do
Begin
Close ;
SQL.Clear ;
SQL.Add('exec sp_Query_All :strNO');

Params.ParamByName('strNO').Value:=DM.strNO;
　 Open ;
　 End;
此時(shí)，存儲過(guò)程是預先編譯的，自然節省了編譯的時(shí)間，同時(shí)只向服務(wù)器傳遞了一個(gè)參數DM.strNO，大大減少了網(wǎng)絡(luò )流量，提高了考試系統的性能。
（3）在考試系統中添加考生信息時(shí)，首先要判斷是否存在該考生學(xué)號信息，然后再進(jìn)行添加操作。如果采用普通SQL語(yǔ)句，代碼如下：
with qry1 do
Begin
Close ;
SQL.Clear ;
SQL.Add(′select * from tblstudents where考生學(xué)號=
:strNO′);
Params.ParamByName('strNO').Value :=DM.strNO;
Open ;
if recordcount=0 then
begin
qry2.Append;
qry2.FieldByName(‘考生學(xué)號’).Value:= DM.strNO;
…
qry2.Post;
End;

采用存儲過(guò)程，可以用流控制語(yǔ)句編寫(xiě)。采用存儲過(guò)程代碼如下：
CREATE PROCEDURE sp_Stud_Add
@No char(10),
@Name char(20),
@Sex char(2),
@Class char(30),
@XueYuan char(20),
@XH smallint
AS
if not exists (Select * from tblStudents where 學(xué)生學(xué)號=
@No)
begin
insert into tblStudents values(@No,@Name,@Sex,@Class,
@XueYuan,@XH)
end
GO
通過(guò)if 流控制語(yǔ)句實(shí)現了相同學(xué)號不能插入的功能，增強了SQL語(yǔ)言的功能和靈活性，避免了普通SQL語(yǔ)句的繁瑣與低效，提高了考試系統的性能。
在開(kāi)發(fā)數據庫應用系統時(shí)，合理使用存儲過(guò)程來(lái)完成相關(guān)的業(yè)務(wù)邏輯操作，能夠顯著(zhù)提高系統的性能、運行效率和可維護性。當然，并非所有的業(yè)務(wù)邏輯都要使用存儲過(guò)程來(lái)實(shí)現。如果都使用存儲過(guò)程的方法實(shí)現，一定會(huì )增加服務(wù)器的負擔；同時(shí)，客戶(hù)端的資源也不能得到充分的利用[8]。因此，要根據具體情況，將那些使用頻繁、比較復雜的業(yè)務(wù)邏輯用存儲過(guò)程的方法實(shí)現，合理規劃服務(wù)器端和客戶(hù)端程序，使相關(guān)事務(wù)能夠均衡處理，以此提高軟件系統的性能和效率。
參考文獻
[1] 吳小林，蔣先剛，高艷錦.基于Delphi的多層數據庫應用系統連接技術(shù)的研究[J].華東交通大學(xué)學(xué)報，2005，22（1）：68-70.
[2] 史創(chuàng )明.SQL Server 2000中文版數據庫管理與應用標準教程[M].北京：清華大學(xué)出版社，2006：264-278.
[3] 張莉，陳雷，孫龍清,等.SQL Server數據庫原理及應用（第2版）[M]. 北京：清華大學(xué)出版社，2009：265-275.
[4] 李舒，陳麗君.高校學(xué)生成績(jì)管理系統的設計與實(shí)現[J].遼寧大學(xué)學(xué)報.自然科學(xué)版，2006，33（3）：284-285.
[5] 曾毅，王玉萍.SQL Server數據庫中存儲過(guò)程的實(shí)現[J].科技信息，2008(25)：88-89.
[6] 肖輝輝，段艷明.SQL Server 2000存儲過(guò)程在VB中的應用[J].福建電腦，2007(12)：189-190.
[7] 馬曉梅.SQL Server 2000實(shí)驗指導（第2版）[M].北京：清華大學(xué)出版社，2008：105-112.
[8] 孫曉楓，范正翹，袁海文.存儲過(guò)程在SQL Server數據庫自我管理中的高級應用[J].計算機應用，2002，22（4）：92-93.