【機房重構】——預存程序（Stored Procedure）

標籤：

【前言】

       在學習資料庫知識時，就曾經接觸過預存程序，當時只是如蜻蜓點水一般，短暫的停留，並沒有留下什麼印象，在進行機房重構學習過程中，又重新認識了儲存結構，從開始的抵觸，不想用到後來逐漸瞭解，學著使用，思路越來越清晰，真正去做了才發現了其中的樂趣；常常說學習需要多總結，現在就是在積累，每一次的收穫都要整理記錄，才能留下深刻的印象，下面就來學習一下預存程序的知識吧。

【預存程序的概念】

      在採用客戶機/伺服器（C/S）計算模式的資料庫系統中，很多工作可以在用戶端完成，也可以在伺服器端完成，資料庫除了存放資料，還存放程式，由於這種程式以資料庫物件的形式儲存在資料庫中，因此稱為預存程序。

      預存程序是使用SQL語句和流程式控制制語句編寫的模組，預存程序經編譯和最佳化後儲存在資料庫伺服器端的資料庫中，使用時調用即可。

      預存程序中可以包含邏輯控制語句和資料操縱語句，它可以接受參數、輸出參數、返回單個或多個結果集以及返回值。

     我的理解：預存程序是為了完成某個特定功能的一組SQL語句，將多條相關聯的SQL語句集合在一起，存放在資料庫中，第一次編譯之後再次調用直接調用，不需要再進行第二次編譯，使用者通過指定預存程序的名字並給出參數（如果該預存程序帶有參數）來執行它。

【預存程序的使用】 1、預先瞭解——建立預存程序

        ① 開啟資料庫，進行如下操作：

        

       ② 參數說明：

<span style="font-family:KaiTi_GB2312;font-size:18px;">CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>  （名字：指定要建立的預存程序的名稱，命名必須符合命名規則，在一個資料庫中或對其所有者而言，預存程序的名字必須唯一）-- Add the parameters for the stored procedure here （參數:在CREATE PROCEDURE語句中，可以聲明一個或多個參數，當調用該預存程序時，使用者必須給出所有的參數值，除非定義了參數的預設值）<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;    -- Insert statements for procedure hereSELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>（動作陳述式：預存程序中要包含的任意數目和類型的Transact-SQL語句）END</span>

2、具體實戰——結合機房收費系統不使用預存程序

       機房收費系統的充值功能實現需要執行兩條SQL語句，將充值記錄插入到充值表中，並且更新卡表中的卡內金額——相關代碼（D層）：

        

<span style="font-family:KaiTi_GB2312;font-size:18px;">Public Function InsertRecharge(card As ECard, recharge As Erecharge) As Boolean Implements IRecharge.InsertRecharge        Dim flag1 As Boolean        Dim flag2 As Boolean        Dim SqlParams As SqlParameter() = {New SqlParameter("@cardno", recharge.cardno),                                           New SqlParameter("@addmoney", recharge.addmoney),                                           New SqlParameter("@gdate", recharge.gdate),                                           New SqlParameter("@gtime", recharge.gtime),                                           New SqlParameter("@userid", recharge.userid)}        '向ReCharge_Info中插入儲值記錄        <span style="background-color: rgb(51, 255, 255);">sql = "Insert into ReCharge_Info(CardNo,addmoney,gdate,gtime,UserID) Values(@cardno,@addmoney,@gdate,@gtime,@userid)"</span>        flag1 = myHelper.AddDeleUpdate(sql, CommandType.Text, SqlParams)        If flag1 = False Then            flag = False        Else            '儲值記錄添加成功，更新Card_info中卡內金額            Dim SqlParams1 As SqlParameter() = {New SqlParameter("@cardno", card.cardno),                                          New SqlParameter("@cash", card.cash)}            <span style="background-color: rgb(51, 255, 255);">sql = "update Card_info set [email protected] where [email protected]"</span>            flag2 = myHelper.AddDeleUpdate(sql, CommandType.Text, SqlParams1)            If flag2 = False Then                flag = False            Else                flag = True            End If        End If</span>

使用預存程序

 機房收費系統的下機功能（部分）實現需要執行兩條SQL語句，下機成功後需要更新上下機記錄表和卡表——相關代碼（D層）：

<span style="font-family:KaiTi_GB2312;font-size:18px;">Public Function offinsert(onoff As EOnoff) As Boolean Implements IOffline.offinsert        Dim sql As String        Dim card As New Entity.ECard        Dim sqlParams As SqlParameter() = {New SqlParameter("@cardno", onoff.cardno),                                           New SqlParameter("@offdate", onoff.offlinedate),                                           New SqlParameter("@offtime", onoff.offlinetime),                                           New SqlParameter("@consumetime", onoff.consumetime),                                           New SqlParameter("@consumecash", onoff.consumecash),                                           New SqlParameter("@ischeckout", "未結賬"),                                           New SqlParameter("@onoffstate", "下機")}       <span style="background-color: rgb(51, 255, 255);"> sql = "PRO__offline"</span>        Dim flag As Boolean        flag = mysqlhelper.AddDeleUpdate(sql, <span style="background-color: rgb(51, 255, 255);">CommandType.StoredProcedur</span>e, sqlParams)               Return flag    End Function</span>

預存程序：PRO_offline

<span style="font-family:KaiTi_GB2312;font-size:18px;">USE [charge_sys]GO/****** Object:  StoredProcedure [dbo].[PRO__offline]    Script Date: 2016/5/27 17:07:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Tracy>-- Create date: <2016/5/24>-- Description:<建立下機預存程序，更新上下機記錄表和卡表>-- =============================================ALTER PROCEDURE [dbo].[PRO__offline]-- Add the parameters for the stored procedure here@cardno nvarchar(50),@offdate nvarchar(50),@offtime nvarchar(50),@consumetime nvarchar(50),@consumecash  numeric(18,1),@onoffstate nvarchar(50),@ischeckout nvarchar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;    -- Insert statements for procedure here<span style="background-color: rgb(51, 255, 255);">update</span> OnOff_Info  set [email protected] ,offlinetime [email protected],consumetime [email protected],consumecash [email protected] ,onoffstate  [email protected],ischeckout [email protected] where cardno [email protected] <span style="background-color: rgb(51, 255, 255);">update</span> Card_Info  set [email protected] where [email protected] END</span>

【預存程序的優點】

   ⑴能實現模組化程式設計。預存程序是根據實際功能的需要建立的一個程式模組，並被儲存在資料庫中。以後使用者要完成該功能，只要在程式中直接調用該預存程序即可，無需再編寫重複的程式碼。預存程序可由資料庫編程方面的專門人員建立，並可獨立於程式原始碼而進行修改和擴充。

   ⑵ 提高執行效率。當客戶程式需要訪問伺服器上的資料時，一般要經過5個步驟：查詢語句被發送到伺服器→伺服器編譯T-SQL語句→最佳化產生查詢執行計畫→資料庫引擎執行查詢→執行結果發回客戶程式，如果執行儲存在用戶端本地的T-SQL程式，那麼每次執行該程式是，對於程式中的每一條語句都要經過以上5個步驟；而預存程序在建立時就被編譯和最佳化，當預存程序第一次被執行時，SQL Server為其產生查詢計劃並將其儲存在記憶體中，這樣以後再調用該預存程序是就不必再進行編譯，即以上5個步驟中的第2步和第3步就被省略了，這能大大改善系統的效能。

   ⑶增強了SQL的功能和靈活性。預存程序可以用流程式控制制語句編寫，有很強的靈活性，能完成複雜的邏輯判斷和複雜的運算。

   ⑷降低網路的通訊量。一個需要數百行T-SQL代碼的操作，如果將其建立成預存程序，那麼使用一條調用預存程序的語句就可完成該操作，這樣就避免了在網路上發送數百行代碼，從而減少了網路負荷。

   ⑸減輕了程式編寫的工作量。預存程序可以反覆調用，並可供其他前端應用程式共用應用邏輯。

   ⑹間接實現安全控制功能。管理員可以不授予使用者訪問預存程序中涉及的表的許可權，而只授予執行預存程序的許可權，這樣，既可以保證使用者通過預存程序操縱資料庫中的資料，由可以保證使用者不能直接存取預存程序中涉及的表。使用者通過預存程序來訪問表，所能進行的操作是有限制的，從而保證了表中資料的安全性。

【預存程序的缺點】

    ⑴可移植性差

     如果一個系統過多的使用了預存程序，那系統的商務邏輯過於依賴資料庫，這樣就會給系統額外的增加一層資料庫中的商務邏輯層，如果開發的時候用的sql server，後來探索資料量過大，需要提高效能移植到oracle或者mysql，這樣就會很麻煩，相當於把預存程序重寫一遍，這是不能忍受的。

     ⑵效能擴充性問題
    隨著系統訪問量的增長，系統必須進行不斷地升級擴充，特別對於大型系統而言，更重要的是效能可擴充性而不是局部的效能。
    處理邏輯如果全部放在預存程序裡，所有的處理都在資料庫伺服器上進行，消耗的就是資料庫伺服器的CPU資源，大家知道資料庫伺服器由於需要較高的可靠性，通常選用的都是價格昂貴的伺服器，對資料庫伺服器升級通常都花費很大。
      如果把處理邏輯放在中介層伺服器上進行，中介層伺服器一般都是小型的機器，價格便宜，而且中介層伺服器的CPU通常主頻比資料庫伺服器的速度還快（比如現在8CPU的資料庫伺服器主頻只有800M，而雙CPU的刀片式伺服器CPU主頻已經到2.8G了），而且對於多層架構，支援中介層伺服器可以增加多台機器進行負載平衡，用中介層伺服器即價格便宜，擴充空間也更大。
     ⑶代碼可複用性差
     預存程序還是過程型語言，兩個很相似的功能在也需要兩個預存程序，因為他們是互相獨立的，可以互相調用，但是不能繼承等物件導向的操作，這也就增加了代碼量。

【小結】

    1、預存程序的使用有利有弊，我們應該根據實際情況來確定是否用預存程序，預存程序應該有選擇地使用，牽涉到批量資料操作的，用預存程序較好。對於小資料量處理的事務操作，應放到中介層處理，這樣系統的移植性和擴充性更好。

    2、不要陷入“業務應該跟著技術走”的誤區，剛開始不會用預存程序，基本資料設定表中更新記錄的時候只是一條Update語句就使用了預存程序，有點本末倒置，應該是為了最佳化系統，實現業務而運用技術，僅僅一條sql語句其實沒有必要用預存程序。

    3、之前跟師傅交流提到過預存程序，我瞭解到的全部都是預存程序的優點，師傅強調了一下它的缺點，還有觸發器（特殊的預存程序），忽然覺得對於知識的學習應該由點及面，一點點拓展，一點點深入，對知識的學習不滿足，保持恒久的求知慾。

       不足之處，請大家多多提意見~~

   

【機房重構】——預存程序（Stored Procedure）