SQL——預存程序

標籤：style   blog   http   io   ar   os   使用   sp   資料   

原文:SQL——預存程序

1. 為什麼使用預存程序

     應用程式通過T-SQL語句到伺服器的過程是不安全的。

     1） 資料不安全

      2）每次提交SQL代碼都要經過文法編譯後在執行，影響應用程式的運行效能

      3） 網路流量大

 

2. 什麼是預存程序

        預存程序是SQL語句和控制語句的先行編譯集合，儲存在資料庫裡，可由應用程式調用執行，而且允許使用者聲明變數、邏輯控制語句及其他強大的編程功能。儲存在SQLServer中，通過名稱和參數執行，也可一返回結果。對於預存程序我更傾向於把他理解成方法。它裡面可以只有一條查詢語句，也可以包含一系列使用控制流程的SQL語句。

 

3. 預存程序的優點

     1） 模組化呈現設計

     2） 執行速度快，效率高

     3） 減少網路流量

     4） 具有良好的安全性

4. 預存程序的分類

      1）系統預存程序

      2）擴充預存程序（屬於系統預存程序的一種）

      3）使用者自訂預存程序

 

5. 系統預存程序

      它一般以"sp_"開頭，是由SQL Server建立、管理和使用，它存放在Resource資料庫中。類似C#語言類庫中的方法，暫時先不考慮它是如何編寫的，先瞭解常用的系統預存程序及調用方法。

     常見的系統預存程序，見下一篇文章

     調用方法：exec[ute]  預存程序名  [參數值]

 

6.  常用的擴充預存程序   xp_cmdshell

     xp_cmdshell  它可以完成DOS命令下的一些操作。

     exec  xp_cmdshell  DOS命令  [no_output]

     說明  no_output是選擇性參數，表示設定執行DOS命令後是否輸出返回資訊。

     樣本： exec xp_cmdshell  ‘mkdir  D:\newdir‘  output

     強調： 因為使用者可以通過xp_cmdshell對作業系統做一些操作，如果該預存程序被駭客使用對作業系統做操作就麻煩了，所以通常會把xp_cmdshell 關閉掉：

     方法一： 

     SQL Server 2008版本及以上， 通過資料庫右擊  選擇“方面”   ，在下拉式清單中選擇 “伺服器安全‘ ， 下面的清單項目中可以看到xmcmdshellEnable 設定。

     SQL Server2005版本及以下，通過開始- SQLServer- 外圍裝置尋找

     方法二：

    關閉xp_cmdshell

    EXEC sp_configure ‘show advanced options‘, 1;

    RECONFIGURE;

    EXEC sp_configure ‘xp_cmdshell‘, 1;

    RECONFIGURE;

    開啟xp_cmdshell

    EXEC sp_configure ‘show advanced options‘, 1;

    RECONFIGURE;

    EXEC sp_configure ‘xp_cmdshell‘, 0;

     RECONFIGURE;

 

7. 使用者自訂預存程序

   文法：

   create  proc[edure] 預存程序名

            @參數1  資料類型 = 預設值 output, 

            ……

            @參數n  資料類型 = 預設值 output

  as  

              <SQL 陳述式>

  go

 

 一個完成的預存程序包含以下3部分：

   1） 輸入參數、輸出參數

   2） 在預存程序中執行的T-SQL語句

   3） 預存程序的傳回值

其中輸入參數允許有預設值。

    刪除預存程序

    drop proc  預存程序名

    if  exists (select * from sysobject where name = 預存程序名)

             drop proc  預存程序名

    go

 

8.  注意事項

       預存程序的聲明： 輸入參數可以有預設值，輸出參數也可以有預設值

      create proc  usp_name

                  @age int = 5,

　　　　　　 @name varchar(10)        

       as 

           ……

       go

        執行語句：  

           exec  pr_name  18 , ‘zm‘

            exec  default  , ‘zm‘

            exec  @name = ‘zm‘

       說明： 為了調用方便，最好將有預設值的預存程序參數列表放到最後。

 

       帶輸出參數的預存程序

       create proc usp_name

               @num1  int,

               @sum int output

       as

             <SQL語句>

       go 

     調用預存程序 

      declare @sum int 

      exec  usp_name  5, @sum  output

      注意， 調用帶有輸出參數的預存程序參數後面必須帶output關鍵字

 

9. 處理預存程序中的錯誤

    raiserror  （ {msg_id  | msg_str} {, serverity, state } [with option [,……]]）

    其中:

    msg_id: 在sysmessage系統資料表中指定使用者定義錯誤資訊

    msg_str: 使用者定義的特定資訊，最長為255個字元

    serverity: 與特定資訊相關聯，表示使用者定義的嚴重性層級。使用者可選用的層級是0~18。數字越大，表示越嚴重。

     state : 表示錯誤的狀態， 1~255中的值

     option: 錯誤的自訂選項，可以使一下任意一值

　　　　LOG: 在Microsoft SQl Server 資料庫引擎樣本的錯誤記錄檔和應用程式記錄檔中記錄錯誤

          NOWAIT:將訊息立即發送給用戶端

 　　    SETERROR:將@@error值和 ERROR_NUMBER 值設定為msg_id 或5000， 不用考慮嚴重層級。

          例如： raiserror (‘錯誤資訊‘, 16,1)

SQL——預存程序