基于存儲過程的簡易教學(xué)系統(tǒng)設(shè)計與實現(xiàn)

杜環(huán)環(huán)　欒靜

摘 要：基于存儲過程的執(zhí)行速度快、安全性高等優(yōu)勢，利用存儲過程分析設(shè)計一種簡易教學(xué)系統(tǒng)。在分布式應(yīng)用環(huán)境下將對數(shù)據(jù)庫的訪問做在業(yè)務(wù)邏輯層上，通過存儲過程實現(xiàn)對數(shù)據(jù)的所有操作處理，這樣既可以降低整個應(yīng)用程序的復(fù)雜性，也可以提高數(shù)據(jù)處理速度，方便用戶操作。針對存儲過程和MVC三層架構(gòu)模式的優(yōu)點，設(shè)計實現(xiàn)了簡易教學(xué)系統(tǒng)，并給出了設(shè)計原理及實現(xiàn)方案。

關(guān)鍵詞：存儲過程；MVC三層架構(gòu)模式；簡易教學(xué)系統(tǒng)

中圖分類號：TP311 文獻(xiàn)標(biāo)志碼：A 文章編號：1673-8454（2016）23-0086-04

一、引言

隨著社會的發(fā)展，學(xué)校中教師和學(xué)生的規(guī)模不斷擴(kuò)大，使教師和學(xué)生信息數(shù)據(jù)量急劇增長，從而導(dǎo)致管理教師和學(xué)生信息的工作變得越來越繁雜，如何利用計算機(jī)技術(shù)幫助教師更快、更好地完成此類工作是非常有必要的。[1]通常情況下，對于基本信息的添加、刪除、修改和查詢等基本操作，使用Excel軟件來完成也是可行的，但安全性不能保證，共享性體現(xiàn)不了，并且查詢很不方便。尤其當(dāng)涉及多個數(shù)據(jù)表格聯(lián)合查詢時，使用Excel軟件進(jìn)行數(shù)據(jù)處理會增加很大的工作量，在對其中一個數(shù)據(jù)表格進(jìn)行處理時，也不能確保其它相關(guān)聯(lián)的數(shù)據(jù)表格中數(shù)據(jù)的一致性。[2]因此，需要借助軟件開發(fā)技術(shù)，編寫小的應(yīng)用程序，利用數(shù)據(jù)庫技術(shù)對大量的數(shù)據(jù)進(jìn)行保存、添加、刪除、修改等操作，以便能夠更好地利用數(shù)據(jù)資源。

Microsoft SQL Server是Microsoft研制開發(fā)的一款數(shù)據(jù)庫管理系統(tǒng)，具有操作簡單易學(xué)的特點，用戶可以以很高的安全性、可靠性和可擴(kuò)展性運行關(guān)鍵任務(wù)，具有很強的安全保密性。作為數(shù)據(jù)存儲，SQL Server性能可靠，數(shù)據(jù)管理功能強大。[3]以其低價位、功能多、用戶界面友好而被廣泛地應(yīng)用在大多數(shù)分布式Web應(yīng)用程序中，是目前最常用的數(shù)據(jù)庫系統(tǒng)之一。[4]

二、SQL應(yīng)用與設(shè)計環(huán)境選擇

SQL（Structured Query Language）語言是一個通用的、功能很強的關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，是一種介于關(guān)系代數(shù)與關(guān)系演算之間的結(jié)構(gòu)化查詢語言，其功能不僅僅是查詢，用于存取數(shù)據(jù)以及更新、統(tǒng)計和管理關(guān)系數(shù)據(jù)庫系統(tǒng)。

1.SQL語言對數(shù)據(jù)操作具有的優(yōu)點[5]

（1）非過程化語言；

（2）統(tǒng)一語言，是所有關(guān)系數(shù)據(jù)庫的公共語言。

SQL Server主要功能及其命令動詞如表1所示：

數(shù)據(jù)定義是通過CREATE、DROP、ALTER命令，實現(xiàn)對基本表、視圖以及索引文件的定義、修改和刪除操作，還可以定義存儲過程、觸發(fā)器等模塊。數(shù)據(jù)操縱是通過SELETE、INSERT、UPDATE、DELETE命令，實現(xiàn)數(shù)據(jù)查詢和數(shù)據(jù)更新兩種操作語句。其中，數(shù)據(jù)查詢是指對數(shù)據(jù)庫中的數(shù)據(jù)查詢、統(tǒng)計、排序、檢索、分組等操作，數(shù)據(jù)更新是指數(shù)據(jù)的插入、刪除、修改等操作。數(shù)據(jù)控制是指對數(shù)據(jù)的操作權(quán)限控制。通過GRANT、REVOKE命令實現(xiàn)對數(shù)據(jù)庫用戶的授權(quán)和收權(quán)的存取控制，以保證數(shù)據(jù)的安全性。

對數(shù)據(jù)庫的操作主要是通過以上SQL命令動詞實現(xiàn)，但編寫實現(xiàn)系統(tǒng)的程序代碼時，考慮應(yīng)用在分布式環(huán)境中，使用獨立的SQL命令實現(xiàn)對數(shù)據(jù)庫的訪問，不利于數(shù)據(jù)的安全保護(hù)，因此會采用MVC框架結(jié)構(gòu)和存儲過程實現(xiàn)數(shù)據(jù)庫訪問。

MVC框架結(jié)構(gòu)由模型（Model）、視圖（View）和控制器（Controller）三部分組成。MVC結(jié)構(gòu)將傳統(tǒng)的輸入、處理和輸出功能映射到一個邏輯的圖形化用戶界面結(jié)構(gòu)中。[6-9]MVC框架結(jié)構(gòu)如圖1所示。

模型（Model）表示業(yè)務(wù)邏輯操作等，在MVC中擁有最多的處理業(yè)務(wù)，負(fù)責(zé)在數(shù)據(jù)庫中存取數(shù)據(jù)，能為多個視圖提供數(shù)據(jù)操作。視圖（View）是用于屏幕上的顯示。模型進(jìn)行操作之后，其結(jié)果通過視圖來顯示?？刂破鳎–ontroller）用于管理用戶與視圖發(fā)生的交互。用戶發(fā)出請求，控制器將相應(yīng)的信息傳遞給模型進(jìn)行處理，模型與視圖匹配在一起，共同完成用戶的請求。[6]他們各自處理任務(wù)。使應(yīng)用程序的輸入、處理和輸出分開。

當(dāng)前臺頁面發(fā)送數(shù)據(jù)請求時，控制層接受請求并調(diào)用相應(yīng)模型去處理請求，由模型調(diào)用存儲過程，在服務(wù)器端得到處理結(jié)果后，再將返回的數(shù)據(jù)在前臺顯示，模型調(diào)用的是存儲過程，而不是SQL基本語句，屏蔽了數(shù)據(jù)庫中表的結(jié)構(gòu)、數(shù)據(jù)關(guān)聯(lián)、用戶使用權(quán)限等重要信息，提高了系統(tǒng)安全性。將業(yè)務(wù)邏輯封裝在數(shù)據(jù)庫系統(tǒng)的存儲過程中，可以大大提高整個軟件系統(tǒng)的可維護(hù)性，提高系統(tǒng)的共享性。合理編寫存儲過程，可以最大限度地利用SQL Server的各種資源。

2.SQL存儲過程具有的優(yōu)點

（1）執(zhí)行速度快，改善系統(tǒng)性能

存儲過程在服務(wù)器端運行，可以利用服務(wù)器強大的計算能力和速度提高執(zhí)行速度。而且存儲過程是預(yù)編譯的，第一次執(zhí)行后的存儲過程會駐留在高速緩存中，以后直接調(diào)用，執(zhí)行速度很快，如果某個操作需要大量的T-SQL語句或重復(fù)執(zhí)行，那么使用存儲過程比直接使用T-SQL語句執(zhí)行得更快。

（2）減少網(wǎng)絡(luò)流量

用戶可以通過發(fā)送一條執(zhí)行存儲過程的語句實現(xiàn)一個復(fù)雜的操作，而不需要寫上百條T-SQL語句，這樣可以減少在服務(wù)器和客戶端之間傳遞語句的數(shù)量，減輕了服務(wù)器的負(fù)擔(dān)。

（3）增強代碼的重用性和共享性

存儲過程在被創(chuàng)建后，可以在程序中被多次調(diào)用，而不必重新編寫。所有的客戶端都可以使用相同的存儲過程來確保數(shù)據(jù)訪問和修改的一致性。而且存儲過程可以獨立于應(yīng)用程序而進(jìn)行修改，大大提高了程序的可移植性。

（4）提供了安全機(jī)制

如果存儲過程支持用戶需要執(zhí)行的所有業(yè)務(wù)功能，SQL Server可以不授予用戶直接訪問表、視圖的權(quán)限，而是授權(quán)用戶執(zhí)行該存儲過程，這樣可以防止把數(shù)據(jù)庫中表的細(xì)節(jié)暴露給用戶，保證表中數(shù)據(jù)的安全性。

鑒于SQL存儲過程的以上優(yōu)點，本文設(shè)計系統(tǒng)應(yīng)用在網(wǎng)絡(luò)環(huán)境中，采用存儲過程處理數(shù)據(jù)，存儲過程封裝數(shù)據(jù)庫操作，可以有效提高程序的性能。[8]調(diào)用存儲過程時，通過指定存儲過程的名字并給出參數(shù)（如果存儲過程帶有參數(shù)）來執(zhí)行，避免了代碼冗余，從而提高系統(tǒng)開發(fā)效率。

三、簡易教學(xué)系統(tǒng)的設(shè)計

針對中學(xué)生的教學(xué)管理工作，在數(shù)據(jù)量比較大的情況下，使用存儲過程將簡單的查詢組合在一起，將數(shù)據(jù)的處理轉(zhuǎn)移到后臺數(shù)據(jù)庫中，基于存儲過程和MVC三層架構(gòu)模式設(shè)計實現(xiàn)簡易教學(xué)系統(tǒng)，實現(xiàn)了學(xué)生、教師和課程信息的管理。本系統(tǒng)采用了Microsoft Windows 7操作系統(tǒng)、SQL Server 2008作為后臺數(shù)據(jù)庫，采用MyEclipse10開發(fā)環(huán)境，設(shè)計并實現(xiàn)了一套完善的簡易教學(xué)系統(tǒng)。

1.系統(tǒng)功能分析

簡易教學(xué)系統(tǒng)針對的是學(xué)校里的教師、學(xué)生以及課程模塊的管理。[10-17]主要功能包括：學(xué)生基本信息管理、教師基本信息管理、課程信息管理、學(xué)生成績管理、授課信息管理。其功能結(jié)構(gòu)如圖2所示：

學(xué)生基本信息管理：該模塊實現(xiàn)了增加學(xué)生基本信息、修改學(xué)生基本信息、刪除學(xué)生基本信息以及查詢學(xué)生基本信息等。

教師基本信息管理：該模塊實現(xiàn)了增加教師信息、刪除教師信息、修改教師信息以及查詢教師信息等。可以統(tǒng)計某一地區(qū)教師信息，教師性別統(tǒng)計及某年齡段的統(tǒng)計?？梢愿鶕?jù)身份證的前四位查詢該地區(qū)教師的信息等。

課程信息管理：該模塊實現(xiàn)了增加課程信息、修改課程信息、刪除課程信息以及查詢課程信息。

學(xué)生成績管理：該模塊實現(xiàn)了增加學(xué)生成績、修改學(xué)生成績、刪除學(xué)生成績、查詢學(xué)生成績、查詢學(xué)生排名、統(tǒng)計選修科目等。學(xué)生也可以查詢某門課程的成績，不及格門數(shù)統(tǒng)計以及總評成績等。

授課課程信息管理：該模塊實現(xiàn)了增加授課信息、修改授課信息、刪除授課信息以及查詢授課信息等。

2.系統(tǒng)數(shù)據(jù)庫設(shè)計

（1）數(shù)據(jù)庫表設(shè)計

本研究中，系統(tǒng)后臺數(shù)據(jù)庫主要設(shè)計了5個數(shù)據(jù)表——學(xué)生信息表、教師信息表、課程信息表、學(xué)生成績表和授課信息表。各數(shù)據(jù)表及其主要字段如下所示（下畫線代表主鍵）：

學(xué)生信息表（學(xué)號、姓名、性別、身份證號、民族、班級號、入學(xué)時間、家庭地址、政治面貌）

教師信息表（工號、身份證、姓名、性別、民族、年齡、出生地址、專業(yè)、職稱）

課程信息表（課程號、課程名、開課學(xué)期、學(xué)分）

學(xué)生成績表（學(xué)號、課程號、成績、學(xué)期、學(xué)年）

授課信息表（工號、課程號、班級號、課程名、授課地點、授課學(xué)期、周學(xué)時）

（2）存儲過程設(shè)計

存儲過程是一組為了完成特定功能的SQL語句集，[9-11]存儲在服務(wù)器上的一組預(yù)編譯的Transact-SQL語句，是一種封裝重復(fù)任務(wù)操作的方法。存儲過程因其執(zhí)行效率高、與事務(wù)處理結(jié)合、運行更安全等優(yōu)點，在數(shù)據(jù)庫應(yīng)用程序中被廣泛采用。創(chuàng)建存儲過程的語法為：[12]

CREATE PROCEDURE <過程名> [；<版本號>]

[@<參數(shù)名><參數(shù)類型>[=<默認(rèn)值>][OUTPUT]…]

[WITH RECOMPILE | ENCRYPTION | RECOMPILE，ENCRYPTION]

AS

在上面的CREATE PROCEDURE語句中，尖括號“< >”里的內(nèi)容是必須要有的。方括號“[ ]”里的內(nèi)容是可選的，參數(shù)的命名必須加“@”符號。方括號里符號“|”是或的關(guān)系，兩邊的選項可以任選一項。

存儲過程創(chuàng)建后，以編譯的形式存儲于數(shù)據(jù)庫服務(wù)器端，供應(yīng)用程序調(diào)用。如果不調(diào)用，存儲過程是不會執(zhí)行的。通過調(diào)用存儲過程時，實參的數(shù)量、順序、類型要與定義存儲過程時的形參的數(shù)量、順序、類型相匹配。

存儲過程按返回的數(shù)據(jù)類型，可以分為兩類：[13-14]一類簡單存儲過程相當(dāng)于SELECT語句，用于查詢數(shù)據(jù)，查詢到的數(shù)據(jù)以結(jié)果集的形式返回；另一類存儲過程是通過輸出參數(shù)返回信息，或不返回信息只執(zhí)行一個動作。

根據(jù)不同的功能需求，本研究系統(tǒng)設(shè)計了如下存儲過程。

簡單存儲過程主要包括：

> 查詢所有學(xué)生的信息。

> 統(tǒng)計不同性別的學(xué)生人數(shù)。

> 統(tǒng)計所有課程都合格的學(xué)生信息。

> 查詢所有教師的基本信息。

帶參數(shù)的存儲過程，向存儲過程指定輸入、輸出參數(shù)的主要目的是通過參數(shù)向存儲過程輸入和輸出信息來擴(kuò)展存儲過程的功能。通過使用參數(shù)，可以多次使用同一存儲過程并按用戶要求查找所需要的結(jié)果。

學(xué)生基本信息管理：

> 查詢某學(xué)生的基本信息。

> 根據(jù)身份證，計算某學(xué)生的年齡。

> 修改某學(xué)生的家庭住址。

> 刪除某學(xué)生的信息。

教師基本信息管理：

> 統(tǒng)計某教師的基本信息。

> 統(tǒng)計教師信息表中某地區(qū)的教師信息和人數(shù)。

> 統(tǒng)計某年齡段的教師人數(shù)。

對于課程信息管理：

> 查詢某課程的基本信息。

> 修改某課程的學(xué)分。

> 刪除某課程的基本信息。

對學(xué)生成績表：

> 查詢某學(xué)生的所有成績以及該學(xué)生所選修課程門數(shù)。

> 查詢某學(xué)生所有不及格成績，并統(tǒng)計不及格課程門數(shù)。

> 查詢某學(xué)生所學(xué)課程的總評成績及班級排名。

> 查詢某學(xué)生某門課程的成績。

> 查詢某門課程的統(tǒng)計信息（最大值、最小值、平均值、總?cè)藬?shù)）。

> 查詢某課程所有比平均成績高的學(xué)生姓名及成績。

> 統(tǒng)計某門課程不及格學(xué)生人數(shù)。

> 統(tǒng)計某課程在某分?jǐn)?shù)區(qū)間的學(xué)生成績。

四、系統(tǒng)實現(xiàn)

通過以上分析設(shè)計，完成了簡易教學(xué)系統(tǒng)。系統(tǒng)界面主要由3部分組成：一是顯示系統(tǒng)的名稱界面，功能是顯示系統(tǒng)的標(biāo)題；二是功能的導(dǎo)航，目的是鏈接到各個具體功能界面、對系統(tǒng)的各個功能進(jìn)行的管理操作；三是結(jié)果顯示，目的是能夠顯示所有功能的內(nèi)容，是對教學(xué)信息系統(tǒng)模塊進(jìn)行具體操作。

系統(tǒng)存儲過程示例如下：

（1）查詢某學(xué)生某門課程的成績。功能是根據(jù)學(xué)生姓名和課程名查詢學(xué)生這門課程的成績。

（2）查詢某學(xué)生所有不及格的成績，并統(tǒng)計不及格課程門數(shù)。功能是根據(jù)學(xué)生姓名查詢不及格的科目成績，并統(tǒng)計不及格科目。

系統(tǒng)界面如圖3所示：

五、總結(jié)與展望

本文討論了SQL基本數(shù)據(jù)處理與存儲過程數(shù)據(jù)處理的特點，并在介紹存儲過程優(yōu)點的基礎(chǔ)上，結(jié)合MVC三層架構(gòu)模式，分析、設(shè)計并實現(xiàn)了基于存儲過程的簡易教學(xué)系統(tǒng)，可以方便管理教師和學(xué)生信息，為類似教學(xué)信息系統(tǒng)的設(shè)計與實現(xiàn)提供借鑒。但是，該系統(tǒng)在界面優(yōu)化、細(xì)節(jié)設(shè)計等方面還存在一些不足，比如增加管理員和學(xué)生登錄界面，而且可以增加考勤模塊，通過管理學(xué)生的日常考勤，可以統(tǒng)計學(xué)生的最終成績。下一步將在本文研究的基礎(chǔ)上，展開考勤方面的研究，以進(jìn)一步優(yōu)化和完善系統(tǒng)功能。

參考文獻(xiàn)：

[1]楊開城.教學(xué)系統(tǒng)分析技術(shù)的初步研究[J].中國電化教育，2007（8）：1-5.

[2]鄭少蘭.電子表格與SQL Server的結(jié)合應(yīng)用[J].電腦知識與技術(shù)，2006（35）：17-18.

[3]石道生，張士軍，孫山.SQL Server數(shù)據(jù)庫觸發(fā)器與存儲過程技術(shù)在自定義數(shù)據(jù)表中的應(yīng)用[J].計算機(jī)與現(xiàn)代化，2004（3）：22-24，27.

[4]陳一明.SQL Server數(shù)據(jù)庫應(yīng)用技巧探討[J].科學(xué)技術(shù)與工程，2008（12）：3334-3337.

[5]苗雪蘭，劉瑞新，宋歌.數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用教程（第3版）[M].北京：機(jī)械工業(yè)出版社，2011：1-325.

[6]康建萍.基于MVC模式的Struts框架研究與應(yīng)用[J].電腦知識與技術(shù)（學(xué)術(shù)交流），2007（2）：49-51.

[7]張計龍，張成洪，張凱，宓詠，郭超，郭家銀.基于改進(jìn)MVC的高校人事管理系統(tǒng)[J].計算機(jī)工程，2004（8）：191-194.

[8]白楊.探究SQL SERVER存儲過程[J].電腦知識與技術(shù)，2011（34）：8769-8770，8776.

[9]王鵬，王曉亮，張喆，吳仁彪.基于存儲過程的通航飛行數(shù)據(jù)處理系統(tǒng)[J].計算機(jī)工程與設(shè)計，2015（4）：1084-1089.

[10]王翠娥.基于WEB的教學(xué)系統(tǒng)的設(shè)計與實現(xiàn)[D].電子科技大學(xué)，2013.

[11]常紅梅.基于存儲過程的數(shù)據(jù)庫安全性實踐初論[J].網(wǎng)絡(luò)安全技術(shù)與應(yīng)用，2015（4）：112-114.

[12]季剛.基于存儲過程的主從服務(wù)器數(shù)據(jù)庫同步的實現(xiàn)[J].計算技術(shù)與自動化，2012（3）：114-116.

[13]陳瀟.SQL Server2008數(shù)據(jù)庫存儲過程的應(yīng)用[J].軟件工程師，2015（6）：18-19.

[14]張申勇，蔡培茂，廖繼勇，樂小燕，孫細(xì)斌.淺析SQL Server中視圖、存儲過程和函數(shù)的異與同[J].電腦知識與技術(shù)，2011（23）：5570-5573，5577.

[15]李衛(wèi)華，顏建軍.基于MVC模式的人事管理系統(tǒng)的設(shè)計與實現(xiàn)[J].三明學(xué)院學(xué)報，2009（4）：416-419.

[16]徐紅，李睿，李進(jìn)，曾珂.基于存儲過程的計費查詢統(tǒng)計系統(tǒng)設(shè)計與實現(xiàn)[J].現(xiàn)代電子技術(shù)，2005（13）：76-78.

[17]沈黎.基于網(wǎng)絡(luò)數(shù)據(jù)庫的存儲過程和觸發(fā)器應(yīng)用研究[J].西南師范大學(xué)學(xué)報（自然科學(xué)版），2016（3）：51-55.

（編輯：王天鵬）