原文: SQL——存儲過程

1. 為什么使用存儲過程

? ? ?應(yīng)用程序通過T-SQL語句到服務(wù)器的過程是不安全的。

? ? ?1） 數(shù)據(jù)不安全

? ? ? 2）每次提交SQL代碼都要經(jīng)過語法編譯后在執(zhí)行，影響應(yīng)用程序的運行性能

? ? ? 3） 網(wǎng)絡(luò)流量大

?

2. 什么是存儲過程

? ? ? ? 存儲過程是SQL語句和控制語句的預(yù)編譯集合，保存在數(shù)據(jù)庫里，可由應(yīng)用程序調(diào)用執(zhí)行，而且允許用戶聲明變量、邏輯控制語句及其他強(qiáng)大的編程功能。保存在SQLServer中，通過名稱和參數(shù)執(zhí)行，也可一返回結(jié)果。對于存儲過程我更傾向于把他理解成方法。它里面可以只有一條查詢語句，也可以包含一系列使用控制流的SQL語句。

?

3. 存儲過程的優(yōu)點

? ? ?1） 模塊化呈現(xiàn)設(shè)計

? ? ?2） 執(zhí)行速度快，效率高

? ? ?3） 減少網(wǎng)絡(luò)流量

? ? ?4） 具有良好的安全性

4. 存儲過程的分類

? ? ? 1）系統(tǒng)存儲過程

? ? ? 2）擴(kuò)展存儲過程（屬于系統(tǒng)存儲過程的一種）

? ? ? 3）用戶自定義存儲過程

?

5. 系統(tǒng)存儲過程

? ? ? 它一般以"sp_"開頭，是由SQL Server創(chuàng)建、管理和使用，它存放在Resource數(shù)據(jù)庫中。類似C#語言類庫中的方法，暫時先不考慮它是如何編寫的，先了解常用的系統(tǒng)存儲過程及調(diào)用方法。

? ? ?常見的系統(tǒng)存儲過程，見下一篇文章

? ? ?調(diào)用方法：exec[ute] ?存儲過程名 ?[參數(shù)值]

?

6. ?常用的擴(kuò)展存儲過程 ? xp_cmdshell

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

? ? ?exec ?xp_cmdshell ?DOS命令 ?[no_output]

? ? ?說明 ?no_output是可選參數(shù)，表示設(shè)置執(zhí)行DOS命令后是否輸出返回信息。

? ? ?示例： exec xp_cmdshell ?'mkdir ?D:\newdir' ?output

? ? ?強(qiáng)調(diào)： 因為用戶可以通過xp_cmdshell對操作系統(tǒng)做一些操作，如果該存儲過程被黑客使用對操作系統(tǒng)做操作就麻煩了，所以通常會把xp_cmdshell 關(guān)閉掉：

? ? ?方法一：?

? ? ?SQL Server 2008版本及以上， 通過數(shù)據(jù)庫右擊 ?選擇“方面” ? ，在下拉列表中選擇 “服務(wù)器安全‘ ， 下面的列表項中可以看到xmcmdshellEnable 設(shè)置。

? ? ?SQL Server2005版本及以下，通過開始- SQLServer- 外圍設(shè)備查找

? ? ?方法二：

? ? 關(guān)閉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] 存儲過程名

? ? ? ? ? ? @參數(shù)1 ?數(shù)據(jù)類型 = 默認(rèn)值 output,?

? ? ? ? ? ? ……

? ? ? ? ? ? @參數(shù)n ?數(shù)據(jù)類型 = 默認(rèn)值 output

? as ?

? ? ? ? ? ? ? <SQL 語句>

? go

?

?一個完成的存儲過程包含以下3部分：

? ?1） 輸入?yún)?shù)、輸出參數(shù)

? ?2） 在存儲過程中執(zhí)行的T-SQL語句

? ?3） 存儲過程的返回值

其中輸入?yún)?shù)允許有默認(rèn)值。

? ? 刪除存儲過程

? ? drop proc ?存儲過程名

? ? if ?exists (select * from sysobject where name = 存儲過程名)

? ? ? ? ? ? ?drop proc ?存儲過程名

? ? go

?

8. ?注意事項

? ? ? ?存儲過程的聲明： 輸入?yún)?shù)可以有默認(rèn)值，輸出參數(shù)也可以有默認(rèn)值

? ? ? create proc ?usp_name

? ? ? ? ? ? ?? ? ?@age int = 5,

　　　　　　?@name varchar(10) ? ? ? ??

? ? ? ?as?

? ? ? ? ? ?……

? ? ? ?go

? ? ? ? 執(zhí)行語句： ?

? ? ? ? ? ?exec ?pr_name ?18 , 'zm'

? ? ? ? ? ? exec ?default ?, 'zm'

? ? ? ? ? ? exec ?@name = 'zm'

? ? ? ?說明： 為了調(diào)用方便，最好將有默認(rèn)值的存儲過程參數(shù)列表放到最后。

?

? ? ? ?帶輸出參數(shù)的存儲過程

? ? ? ?create proc usp_name

? ? ? ? ? ? ? ?@num1 ?int,

? ? ? ? ? ? ? ?@sum int output

? ? ? ?as

? ? ? ? ? ? ?<SQL語句>

? ? ? ?go?

? ? ?調(diào)用存儲過程?

? ? ? declare @sum int?

? ? ? exec ?usp_name ?5, @sum ?output

? ? ? 注意， 調(diào)用帶有輸出參數(shù)的存儲過程參數(shù)后面必須帶output關(guān)鍵字

?

9. 處理存儲過程中的錯誤

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

? ? 其中:

? ? msg_id: 在sysmessage系統(tǒng)表中指定用戶定義錯誤信息

? ? msg_str: 用戶定義的特定信息，最長為255個字符

? ? serverity: 與特定信息相關(guān)聯(lián)，表示用戶定義的嚴(yán)重性級別。用戶可選用的級別是0~18。數(shù)字越大，表示越嚴(yán)重。

? ? ?state : 表示錯誤的狀態(tài)， 1~255中的值

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

　　　　LOG: 在Microsoft SQl Server 數(shù)據(jù)庫引擎示例的錯誤日志和應(yīng)用程序日志中記錄錯誤

? ? ? ? ? NOWAIT:將消息立即發(fā)送給客戶端

?　　 ? ?SETERROR:將@@error值和 ERROR_NUMBER 值設(shè)置為msg_id 或5000， 不用考慮嚴(yán)重級別。

? ? ? ? ? 例如： raiserror ('錯誤信息', 16,1)

SQL——存儲過程