原文: 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——存儲過程