為代碼減負之<二>預存程序（SQL）

在上篇部落格中介紹到了觸發器的使用，並且其中也提到了觸發器是個特殊的預存程序，那麼什麼是預存程序呢？他們

兩個又到底有什麼區別呢？

其實最主要的區別就是，觸發器是當滿足條件時系統自動執行的，而預存程序是手動調用的。

 

簡單介紹

 

什麼是預存程序？

定義：將常用的或很複雜的工作，預先用SQL語句寫好並用一個指定的名稱儲存起來，使用者通過指定預存程序的名字

並給出參數（如果該預存程序帶有參數）來調用它。

講到這裡,可能有人要問：這麼說預存程序不就是一堆SQL語句而已嗎？那麼預存程序與一般的SQL語句有什麼區別

呢？

預存程序有它獨到的優點不單單只是把一堆SQL語句堆積到一塊：

      1. 預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般SQL語句每執行一次就編譯

一次,所以使用預存程序可提高資料庫執行速度。

      2. 當對資料庫進行複雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此複雜操作用預存程序封

裝起來與資料庫提供的交易處理結合一起使用。

      3. 預存程序可以重複使用,可減少資料庫開發人員的工作量

      4. 安全性高,可設定只有某此使用者才具有對指定預存程序的使用權

 

預存程序的種類：

 

   1. 系統預存程序：以sp_開頭,用來進行系統的各項設定.取得資訊.相關管理工作,如sp_help就是取得指定對象的相關

資訊

   2. 擴充預存程序  以XP_開頭,用來叫用作業系統提供的功能

   3. 使用者自訂的預存程序,這是我們所指的預存程序

 

如何使用

 

 

這麼好的一個東西，該怎麼應用在系統中呢？

以機房收費系統的退卡功能為例。

1. 第一步也是思考，思考這個功能涉及到了幾條SQL語句（如果就一條就不必建立預存程序了），

把SQL語句都列出來：

delete Card_info  where CID = @CID                                                                  刪除卡表中對應卡號的資訊

delete Student_info   where SID = @SID                                                             根據卡號對應的學號刪除學生表對應學號資訊

delete Line_info    where CID =@CID                                                                  刪除上機記錄中對應卡號的資訊

delete Recharge_info   where CID = @CID                                                         刪除充值記錄中對應卡號的資訊

insert into ReturnCard_info (CID,ReturnCash ,UID ,Rdate ,Rtime ) values(@CID ,@ReturnCash ,@UID ,@Rdate,@Rtime)                                               增加退卡資訊到退卡記錄表

 

1. 建立預存程序  在相應資料庫下開啟可程式化性，建立預存程序

                                    

1. 添加相應預存程序的代碼

<span style="font-family:Microsoft YaHei;">--=============================================--Author:                牛遷遷--Create date: 2014年6月11日 --Description:        退卡,刪除Card_info,Line_info,Student_info,Recharge_info,同時添加ReturnCard_info--=============================================ALTER PROCEDURE [dbo].[PROC_ReturnCard]                             -- 命名規範   PROC_ + 名稱 --Add the parameters for the stored procedure here@CID varchar(20),@ReturnCashvarchar(20),@UID varchar(20),           --  定義用到的參數@Rdatevarchar(20),@Rtime varchar(20),@SID varchar(20)ASBEGIN--SET NOCOUNT ON added to prevent extra result sets from--interfering with SELECT statements.SETNOCOUNT ON;     -- Insert statements forprocedure here                         -- 添加涉及到的SQL語句deleteCard_info  where CID = @CIDdeleteStudent_info   where SID = @SIDdeleteLine_info    where CID =@CID deleteRecharge_info   where CID = @CIDinsertinto ReturnCard_info (CID ,ReturnCash ,UID ,Rdate ,Rtime ) values(@CID,@ReturnCash ,@UID ,@Rdate ,@Rtime)END</span>

1. 調用預存程序 （D層）

<span style="font-family:Microsoft YaHei;">    Public Function ReturnCard(ENCardinfo AsEN_Card_info, ENStudentinfo As EN_Student_info, ENLineinfo As EN_Line_info,ENReturnCardinfo As EN_ReturnCard_info) As Integer ImplementsIReturnCard.ReturnCard        Dim strSql As String ="PROC_ReturnCard"            '這裡的strSql不再存放單條SQL語句，而是預存程序        '定義所需的參數        Dim sqlParams As SqlParameter() = {NewSqlParameter("@CID", ENCardinfo.CID),                                           NewSqlParameter("@SID", ENStudentinfo.SID),                                           NewSqlParameter("@ReturnCash", ENReturnCardinfo.ReturnCash),                                           NewSqlParameter("@UID", ENReturnCardinfo.UID),                                           NewSqlParameter("@Rdate", ENReturnCardinfo.Rdate),                                           NewSqlParameter("@Rtime", ENReturnCardinfo.Rtime)}        '注意這裡的CommandType.StoredProcedure        ReturnclsSqlHelper.ExecAddDelUpdate(strSql, CommandType.StoredProcedure, sqlParams)     End Function</span>

 

        如果沒有預存程序的話，完成這個過程，需要編寫五個函數：刪除卡資訊的函數、刪除學生資訊的函數、刪除

上級記錄的函數、刪除充值記錄的函數、增加退卡記錄的函數；然後再一個個調用，不僅代碼繁多，而且關係亂；使

用預存程序只需要編寫一個函數就可以完成五個函數的工作，使代碼邏輯變得簡單化。

預存程序和觸發器，其實就是把SQL語句封裝到了資料庫中，觸發器能完成的工作，預存程序一般也能完成，但是選

擇的時候要優先使用預存程序。

 

 

資料引用:http://www.knowsky.com/2951.html