存儲過程在網上商城系統開發中的應用

摘 要： 存儲過程是數據庫端執行的一組T-SQL語句的集合。網上商城系統具有大量的用戶交互的特點，系統性能十分重要。文章介紹了網上商城系統的主要功能及數據庫設計，具體闡述了存儲過程在該系統開發中的應用。實踐結果表明，使用存儲過程能夠提高系統運行的效率，增強系統的安全性。

關鍵詞： 存儲過程； 網上商城； 安全性； SQL Server

中圖分類號：TP311 文獻標志碼：A 文章編號：1006-8228（2013）09-31-03

0 引言

目前國內B2C行業正處于高速成長期，市場的進一步擴張為傳統企業電子商務化提供了一個發展的平臺，建立自己的B2C網上商城正成為未來中小企業進行網上開拓市場的渠道選擇[1]。網上商城系統與一般網站的主要區別之一是系統要求更多的交互，更安全地進行數據存儲和交換，因此網站的運行效率及安全性是網站設計的關鍵。存儲過程是數據庫管理系統中一類常用的數據庫對象，具有可模塊化編程、執行效率高、可減輕網絡負載及訪問安全可靠等特點。在網上商城系統中使用該對象，可以提高網站的訪問速度和安全性，為用戶提供更好的購物體驗。

1 網上商城系統

1.1 網上商城系統的基本功能

網上商城系統要為用戶建立一個動態的、便捷的、交互性的網絡購物環境，實際上是一個B/S結構的信息系統[2]。它主要有兩類用戶，即普通用戶和管理員。整個系統可以分為前臺和后臺兩個部分，前臺主要是面向普通用戶的，主要由商品瀏覽、商品查詢、購物車、會員管理等模塊組成，后臺是面向管理員的，主要包括登錄管理、商品管理、會員管理、訂單管理、公告管理等模塊構成。后臺功能模塊如圖1所示。

1.2 網上商城系統的邏輯體系設計

網上商城系統采用Microsoft Visual Studio 2010開發，后臺數據庫采用SQL Server 2008。該系統使用三層體系結構，整個系統分為表示層、業務邏輯層、數據訪問層，如圖2所示。該系統的數據訪問層實現時調用了事先在數據庫后臺編制的各類存儲過程，從而提高了系統的運行效率，保證了系統的安全性。

1.3 網上商城系統的數據庫設計

根據系統的功能需求，設計出了以下數據庫關系模式：

⑴ 商品信息（商品編號，類別編號，商品名稱，商品介紹，進貨價格，商品圖片，進貨日期，備注）；

⑵ 商品類別（類別編號，類別名稱）；

⑶ 訂單信息（訂單編號，用戶名，付款方式，運送方式，訂貨日期，是否執行，備注）；

⑷ 訂單明細（訂單明細編號，訂單編號，商品編號，價格，數量）；

⑸ 會員信息（會員編號，用戶名，密碼，真實姓名，地址，郵政編碼，電話，電子郵件）；

⑹ 管理員（用戶編號，用戶名，密碼）。

2 存儲過程及其優點

存儲過程是一種重要的數據庫對象，是為了實現某種特定的功能，將一組預編譯的SQL語句以存儲單元的形式存儲在服務器上，供用戶調用[3]。存儲過程可以帶輸入參數、輸出參數，具有返回值。SQL Server支持五種類型的存儲過程，分別是：系統存儲過程、本地存儲過程、臨時存儲過程、遠程存儲過程和擴展存儲過程。

存儲過程具有以下優點。

⑴ 執行效率高 存儲過程在創建時就經過了語法檢查和性能優化，因此在執行時不必再重復這些步驟。存儲過程第一次調用后，就駐留在內存中，不必再經過編譯和優化，所以執行速度很快。

⑵ 提高軟件系統的可維護性 存儲過程將相關業務邏輯封裝在一起，只要創建一次，便可多次調用。用戶可以獨立于應用程序而對存儲過程進行修改[4]。

⑶ 降低網絡流量 存儲過程位于服務器上，調用的時候只需要傳遞存儲過程的名稱和參數，因此降低了網絡傳輸的數據量[5]。

⑷ 保證系統的安全 存儲過程可以避免用戶在應用程序中直接嵌入SQL語句，防止SQL注入攻擊；可以在數據庫內分別設置各個存儲過程的安全保護[6]，即為存儲過程設置一定的調用權限，從而避免用戶越權訪問，保證信息系統的安全。

3 存儲過程在網上商城系統中的應用

3.1 網上商城系統中的部分存儲過程

SQL Server中創建存儲過程時需要指定存儲過程的名稱、輸入參數、輸出參數等，在網上商城系統中，服務器端創建了實現登錄驗證、數據操作（增、刪、改、查）、數據分頁等存儲過程，大大提高了應用程序訪問數據庫的效率，具體如下。

3.1.1 實現登錄驗證的存儲過程

登錄存儲過程proc_Login主要用來驗證用戶名和密碼的合法性。基于存儲過程的優點，可以運用它實現登錄功能從而防御SQL的注入攻擊，提高系統的安全性。另外，可以使用“WITH ENCRYPTION”語句對存儲過程的創建過程進行加密，具體代碼如下。

CREATE PROC proc_Login

@UserName VARCHAR（20）， --輸入參數用戶名

@Pwd VARCHAR（20）， --輸入參數密碼

@Return BIT OUTPUT --輸出參數，登錄結果

WITH ENCRYPTION

AS

BEGIN

IF EXISTS（SELECT COUNT（*）

FROM Admin

WHERE UserName=@UserName AND Pwd=@Pwd）

SET @Return=1 --驗證成功

ELSE

SET @Return=0 --驗證失敗

END

3.1.2 增加商品的存儲過程

網上商城系統中包括許多對數據表進行增加、刪除、修改或查詢的操作，這些都可以使用存儲過程來實現，從而提高系統的可維護性。這里以增加商品來展示此類存儲過程的實現，其他功能只需將存儲過程中的INSERT語句改成相應功能的語句即可。下面的存儲過程共包括七個輸入參數，主要功能是實現商品信息的添加，具體代碼如下。

CREATE PROCEDURE proc_InsertProduct

@typeID INT， --類別編號

@goodsName VARCHAR（200）， --商品名稱

@introduce TEXT， --商品介紹

@price FLOAT， --進貨價格

@picture VARCHAR（100）， --商品圖片

@INTime DATETIME， --進貨日期

@remarks TEXT --備注

AS

BEGIN

INSERT INTO product

（typeID，goodsName，introduce，price，picture，INTime，remarks）

VALUES

（@typeID，@goodsName，@introduce，@price，@picture，

@INTime，@remarks）

END

3.1.3 實現分頁的存儲過程

經典的數據分頁方法是ADO記錄集分頁法，也就是利用ADO自帶的分頁功能來實現分頁，但這種分頁方法僅適用于數據量較小的情形，而不適合網上商城這類系統。由于存儲過程是事先編譯好的SQL語句，它的執行效率要比通過Web頁面傳來的SQL語句的執行效率要高，因此在數據庫端編寫實現分頁的存儲過程很有必要，可以使用戶有較好的訪問體驗。實現通用的分頁功能，存儲過程設計時可以包括數據表名、需要返回的列、排序的字段名、頁尺寸、頁碼、返回的記錄總數等多個輸入參數，由于本文篇幅有限，代碼此處就不列出了。

3.2 存儲過程在.NET程序中的調用

.NET程序中要訪問數據庫及數據庫對象，首先，要定義ADO.NET的SqlConnection對象，并利用該對象打開與數據庫的連接；其次，需要創建一個SqlCommand對象，并指定該對象的類型為存儲過程；如果該存儲過程有輸入參數或者輸出參數，還需要依次在command對象的Parameters集合中通過Add（）方法逐個添加參數，并將參數的值傳入或傳出；然后，通過Command對象的ExecuteNonQurey（）、ExecuteReader（）、ExecuteScalar（）等方法調用存儲過程，將增加、刪除、修改的數據插入到數據庫中，或者是將數據返回到.NET程序中；最后，關閉數據庫連接。下面以增加商品的存儲過程proc_InsertProduct為例，列出其在.NET中編寫的關鍵代碼：

//創建與數據庫的連接

string connString="Data Source=.；Initial Catalog=test；

User Id=Admin；Pwd=admin"；

SqlConnection conn=new SqlConnection（connString）；

try

{ string sql="proc_InsertProduct"；

//打開數據庫連接

conn.Open（）；

//創建SqlCommand對象，并指定其類型是存儲過程

SqlCommand command=new SqlCommand（sql， conn）；

command.CommandType=CommandType.StoredProcedure；

//逐一添加輸入參數，商品類別、商品名稱等

command.Parameters.Add（"@typeID"， SqlDbType.Int）；

command.Parameters["@typeID"].Value

=Convert.ToInt16（txtTypeID.Text）；

command.Parameters.Add（"@goodsName"，SqlDbType.VarChar）；

command.Parameters["@goodsName"].Value

=txtgoodsName.Text；

……字段較多，此處不一一列出

command.Parameters.Add（"@remarks"， SqlDbType.Text）；

command.Parameters["@remarks"].Value=txtremarks.Text；

//可調用Sqlcommand的不同方法來執行查詢或者是增、刪、改的動作

command.ExecuteNonQuery（）；

string message=""；

……根據返回值判斷操作是否成功，此處略

MessageBox.Show（message）；

}

catch （Exception ex）

{ MessageBox.Show（"系統發生錯誤"）；

}

finally

{ //關閉數據庫連接

conn.Close（）；

}

4 結束語

數據庫應用系統開發中，將易于變化或進行數據庫處理的功能用存儲過程編寫，可以給開發及后期的軟件維護帶來諸多方便。但是存儲過程的使用不當也會增加服務器的負擔，因此需要結合實際工作合理使用。筆者在網上商城系統的開發過程中充分利用了SQL Server存儲過程，不僅大大減少了程序開發的工作量，而且提高了系統的性能和穩定性。

參考文獻：

[1] 李俊.基于Asp.net的B2C電子商務系統設計與實現[D].重慶大學碩士學位論文，2012.

[2] 歐陽昉.網上商城購物系統研究與開發[D].電子科技大學碩士學位論文，2007.

[3] 吳伶琳，楊正校.SQL Server 2005數據庫基礎[M].大連理工大學出版社，2010.

[4] 李春芬，吳英斌.存儲過程在勘探設備管理系統中的應用[J].物探裝備，2011.21（5）：327-330

[5] 陳曉丹，王娟.存儲過程在信息發布系統中的研究與應用[J].武漢工程職業技術學院學報，2010.22（2）：36-38

[6] 張小波，成良玉.vs.net中存儲過程使用方法研究[J].計算機應用，2004.24（2）：138-140