利用SQL Server存儲過程提高在線考試系統(tǒng)的性能

丁革媛,宋 揚(yáng),袁中臣,魏寶武

（沈陽工業(yè)大學(xué) 工程學(xué)院,遼寧 遼陽111003）

隨著計(jì)算機(jī)網(wǎng)絡(luò)技術(shù)和數(shù)據(jù)庫技術(shù)的飛速發(fā)展，高校的數(shù)字化校園建設(shè)進(jìn)程日益深入，其中數(shù)字化教學(xué)與學(xué)習(xí)環(huán)境建設(shè)成為其核心內(nèi)容。為推動數(shù)字化校園建設(shè)進(jìn)程，創(chuàng)建數(shù)字化教學(xué)環(huán)境,利用Delphi7和SQL Server2000開發(fā)出適用于多學(xué)科的高校通用計(jì)算機(jī)在線考試軟件系統(tǒng)。Delphi在基于C/S模式的數(shù)據(jù)庫應(yīng)用開發(fā)領(lǐng)域具有很強(qiáng)的數(shù)據(jù)庫支持能力，因?yàn)樗峁┝素S富的數(shù)據(jù)庫操作組件，常用的有 TQuery、TTable、TADOQuery和TADOTable等[1]。通過這些組件，程序員可以在短時間內(nèi)完成一個應(yīng)用程序模塊的設(shè)計(jì)，大大減少了開發(fā)人員的工作量。但是，這些組件在操作數(shù)據(jù)庫時卻存在局限性，即必須將數(shù)據(jù)庫中的記錄從服務(wù)器端取出放入到客戶端，再根據(jù)查詢條件進(jìn)行選擇。這種實(shí)現(xiàn)方法，如果待訪問的數(shù)據(jù)量較大，勢必因?yàn)椴樵儠r間較長而降低程序執(zhí)行的速度，從而降低應(yīng)用程序的性能。解決這一問題利用了SQL Server2000的存儲過程來實(shí)現(xiàn)[2]。

1 存儲過程及其優(yōu)點(diǎn)

1.1 存儲過程的定義

存儲過程是存儲在服務(wù)器上的一組預(yù)先定義并編譯好的用來實(shí)現(xiàn)某種特定功能的SQL語句。它可以接受參數(shù)、返回狀態(tài)值或參數(shù)值。存儲過程在服務(wù)器端執(zhí)行，通過應(yīng)用程序調(diào)用的方法實(shí)現(xiàn)，執(zhí)行結(jié)束后再把結(jié)果返回到客戶端。

1.2 存儲過程的優(yōu)點(diǎn)

（1）存儲過程執(zhí)行速度快，便于提高系統(tǒng)的性能。由于存儲過程在第一次執(zhí)行之后，其執(zhí)行規(guī)劃就儲存在高速緩沖存儲區(qū)中，在以后的操作中。只需從高速緩沖存儲區(qū)中調(diào)用編譯好的二進(jìn)制形式存儲過程來執(zhí)行[3]。

（2）減少網(wǎng)絡(luò)流量，提高系統(tǒng)執(zhí)行效率?？蛻舳藨?yīng)用程序調(diào)用一個存儲過程，只需通過網(wǎng)絡(luò)發(fā)送該過程名和少量入口參數(shù)，數(shù)據(jù)庫服務(wù)器就可執(zhí)行該過程，執(zhí)行完成后，只返回結(jié)果狀態(tài)或?qū)⒆罱K結(jié)果集給客戶端應(yīng)用程序，無需通過網(wǎng)絡(luò)傳送大量的SQL操作命令和中間結(jié)果，最大限度地減少網(wǎng)絡(luò)通信負(fù)擔(dān)，提高了執(zhí)行效率。

（3）維護(hù)了數(shù)據(jù)庫的安全性。如果用戶被授予執(zhí)行存儲過程的權(quán)限，即使該用戶沒有在執(zhí)行該存儲過程中可參考的表或視圖的訪問權(quán)限，該用戶也可以完全執(zhí)行該存儲過程而不受影響。因此，可以通過創(chuàng)建存儲過程來完成數(shù)據(jù)的插入、更新和刪除等操作，并且可以通過編程方式控制在操作中對信息的訪問權(quán)限[4]。

（4）提高軟件系統(tǒng)的可維護(hù)性。存儲過程在創(chuàng)建時就在服務(wù)器上經(jīng)過調(diào)試和編譯，可以避免不必要的錯誤出現(xiàn)。另外，存儲過程將相關(guān)業(yè)務(wù)邏輯封裝在一起，可以大大提高整個軟件系統(tǒng)的可維護(hù)性。因?yàn)楫?dāng)相關(guān)業(yè)務(wù)邏輯發(fā)生變化時，不需要修改并編譯客戶端應(yīng)用程序，只需要修改位于服務(wù)器端的實(shí)現(xiàn)相應(yīng)業(yè)務(wù)邏輯的存儲過程即可。

（5）充分增強(qiáng)SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和運(yùn)算。

基于以上優(yōu)點(diǎn)，在進(jìn)行C/S模式數(shù)據(jù)庫應(yīng)用系統(tǒng)開發(fā)時，通常把比較浪費(fèi)時間、影響網(wǎng)絡(luò)傳送的相關(guān)業(yè)務(wù)邏輯編寫成存儲過程由服務(wù)器執(zhí)行，同時利用服務(wù)器的SQL語法進(jìn)行優(yōu)化處理，只將少量的結(jié)果顯示在客戶端，充分提高軟件系統(tǒng)的性能[5]。

2 存儲過程的創(chuàng)建方法

以SQL Server 2000數(shù)據(jù)庫管理系統(tǒng)為背景，創(chuàng)建方法如下：

下面對語法中各選項(xiàng)進(jìn)行說明：

（1）選項(xiàng)“procedure_name”指存儲過程名，不能超過128個字符，“[;number]”指程序編號，用于區(qū)別同名的存儲過程。

（2）選項(xiàng)“@parameter”表示參數(shù)，每個存儲過程中最多可以設(shè)定1 024個參數(shù)，要求在每個參數(shù)名前加上符號“@”。

（3）選項(xiàng)“[=default]”指為對應(yīng)參數(shù)設(shè)定默認(rèn)值。

（4）選項(xiàng)“OUTPUT”用來指定參數(shù)既有輸入又有輸出。即在調(diào)用這個存儲過程時，如果所指定的參數(shù)是要輸入的參數(shù)，同時也需要在結(jié)果中輸出，則該項(xiàng)必須為“OUTPUT”。

（5）選項(xiàng)“WITH”用來指定存儲過程的處理方式。

（6）選項(xiàng)“FOR REPLICATION”表示只能由復(fù)制方式執(zhí)行[6]。

3 存儲過程在考試軟件系統(tǒng)中的應(yīng)用

（1）考試系統(tǒng)在運(yùn)行時要經(jīng)過三個步驟，考生抽取套題編號、抽取套題內(nèi)容和將套題內(nèi)容插入答題表進(jìn)行答題。如果不采用存儲過程，則需用下面方法實(shí)現(xiàn)。

①隨機(jī)抽取套題編號

通過執(zhí)行存儲過程，將題庫中與考生抽取套題編號相匹配的記錄提取出來，并批量插入到考試表中，同時對考生分?jǐn)?shù)進(jìn)行了初始化為0的動作。而本考試系統(tǒng)就是將考生的學(xué)號和考生抽取的套題編號作為參數(shù)傳遞給存儲過程，因此，大幅度提高了考試軟件的效率。

（2）在考試系統(tǒng)進(jìn)行不同類型題目抽取時，如果在Delphi中直接應(yīng)用SQL語句，應(yīng)使用下面代碼：

此時，程序是在運(yùn)行時才編譯的。這樣就增加了運(yùn)行的時間，同時還要把完整的SQL語句傳遞給Sql Server服務(wù)器執(zhí)行，這樣考試過程中勢必增加網(wǎng)絡(luò)流量，降低軟件的運(yùn)行效率。

如果采用存儲過程，可用下面方法實(shí)現(xiàn)：

Select Select*from tblKS Where學(xué)生學(xué)號=@strNO and試題類型=′單項(xiàng)選擇題′order by 試題序號

GO

在客戶端調(diào)用方法如下：

此時，存儲過程是預(yù)先編譯的，自然節(jié)省了編譯的時間，同時只向服務(wù)器傳遞了一個參數(shù)DM.strNO，大大減少了網(wǎng)絡(luò)流量，提高了考試系統(tǒng)的性能。

（3）在考試系統(tǒng)中添加考生信息時，首先要判斷是否存在該考生學(xué)號信息，然后再進(jìn)行添加操作。如果采用普通SQL語句，代碼如下：

通過if流控制語句實(shí)現(xiàn)了相同學(xué)號不能插入的功能，增強(qiáng)了SQL語言的功能和靈活性，避免了普通SQL語句的繁瑣與低效，提高了考試系統(tǒng)的性能。

在開發(fā)數(shù)據(jù)庫應(yīng)用系統(tǒng)時，合理使用存儲過程來完成相關(guān)的業(yè)務(wù)邏輯操作，能夠顯著提高系統(tǒng)的性能、運(yùn)行效率和可維護(hù)性。當(dāng)然，并非所有的業(yè)務(wù)邏輯都要使用存儲過程來實(shí)現(xiàn)。如果都使用存儲過程的方法實(shí)現(xiàn)，一定會增加服務(wù)器的負(fù)擔(dān)；同時，客戶端的資源也不能得到充分的利用[8]。因此，要根據(jù)具體情況，將那些使用頻繁、比較復(fù)雜的業(yè)務(wù)邏輯用存儲過程的方法實(shí)現(xiàn)，合理規(guī)劃服務(wù)器端和客戶端程序，使相關(guān)事務(wù)能夠均衡處理，以此提高軟件系統(tǒng)的性能和效率。

[1]吳小林，蔣先剛，高艷錦.基于 Delphi的多層數(shù)據(jù)庫應(yīng)用系統(tǒng)連接技術(shù)的研究[J].華東交通大學(xué)學(xué)報(bào)，2005，22（1）：68-70.

[2]史創(chuàng)明.SQL Server 2000中文版數(shù)據(jù)庫管理與應(yīng)用標(biāo)準(zhǔn)教程[M].北京：清華大學(xué)出版社，2006：264-278.

[3]張莉，陳雷，孫龍清,等.SQL Server數(shù)據(jù)庫原理及應(yīng)用（第 2版）[M].北京：清華大學(xué)出版社，2009：265-275.

[4]李舒，陳麗君.高校學(xué)生成績管理系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn)[J].遼寧大學(xué)學(xué)報(bào).自然科學(xué)版，2006，33（3）：284-285.

[5]曾毅，王玉萍.SQL Server數(shù)據(jù)庫中存儲過程的實(shí)現(xiàn)[J].科技信息，2008(25)：88-89.

[6]肖輝輝，段艷明.SQL Server 2000存儲過程在VB中的應(yīng)用[J].福建電腦，2007(12)：189-190.

[7]馬曉梅.SQL Server 2000實(shí)驗(yàn)指導(dǎo)（第2版）[M].北京：清華大學(xué)出版社，2008：105-112.

[8]孫曉楓，范正翹，袁海文.存儲過程在SQL Server數(shù)據(jù)庫自我管理中的高級應(yīng)用[J].計(jì)算機(jī)應(yīng)用，2002，22（4）：92-93.