原文: SQL——存儲(chǔ)過(guò)程

1. 為什么使用存儲(chǔ)過(guò)程

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

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

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

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

?

2. 什么是存儲(chǔ)過(guò)程

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

?

3. 存儲(chǔ)過(guò)程的優(yōu)點(diǎn)

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

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

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

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

4. 存儲(chǔ)過(guò)程的分類(lèi)

? ? ? 1）系統(tǒng)存儲(chǔ)過(guò)程

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

? ? ? 3）用戶(hù)自定義存儲(chǔ)過(guò)程

?

5. 系統(tǒng)存儲(chǔ)過(guò)程

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

? ? ?常見(jiàn)的系統(tǒng)存儲(chǔ)過(guò)程，見(jiàn)下一篇文章

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

?

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

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

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

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

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

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

? ? ?方法一：?

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

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

? ? ?方法二：

? ? 關(guān)閉xp_cmdshell

? ? EXEC sp_configure 'show advanced options', 1;

? ? RECONFIGURE;

? ? EXEC sp_configure 'xp_cmdshell', 1;

? ? RECONFIGURE;

? ? 開(kāi)啟xp_cmdshell

? ? EXEC sp_configure 'show advanced options', 1;

? ? RECONFIGURE;

? ? EXEC sp_configure 'xp_cmdshell', 0;

? ? ?RECONFIGURE;

?

7. 用戶(hù)自定義存儲(chǔ)過(guò)程

? ?語(yǔ)法：

? ?create ?proc[edure] 存儲(chǔ)過(guò)程名

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

? ? ? ? ? ? ……

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

? as ?

? ? ? ? ? ? ? <SQL 語(yǔ)句>

? go

?

?一個(gè)完成的存儲(chǔ)過(guò)程包含以下3部分：

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

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

? ?3） 存儲(chǔ)過(guò)程的返回值

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

? ? 刪除存儲(chǔ)過(guò)程

? ? drop proc ?存儲(chǔ)過(guò)程名

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

? ? ? ? ? ? ?drop proc ?存儲(chǔ)過(guò)程名

? ? go

?

8. ?注意事項(xiàng)

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

? ? ? create proc ?usp_name

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

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

? ? ? ?as?

? ? ? ? ? ?……

? ? ? ?go

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

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

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

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

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

?

? ? ? ?帶輸出參數(shù)的存儲(chǔ)過(guò)程

? ? ? ?create proc usp_name

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

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

? ? ? ?as

? ? ? ? ? ? ?<SQL語(yǔ)句>

? ? ? ?go?

? ? ?調(diào)用存儲(chǔ)過(guò)程?

? ? ? declare @sum int?

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

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

?

9. 處理存儲(chǔ)過(guò)程中的錯(cuò)誤

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

? ? 其中:

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

? ? msg_str: 用戶(hù)定義的特定信息，最長(zhǎng)為255個(gè)字符

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

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

? ? ?option: 錯(cuò)誤的自定義選項(xiàng)，可以使一下任意一值

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

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

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

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

SQL——存儲(chǔ)過(guò)程