SQL Server存儲(chǔ)過(guò)程入門學(xué)習(xí)

存儲(chǔ)過(guò)程的定義,

存儲(chǔ)過(guò)程(Stored Procedure),是一組為了完成特定功能的SQL 語(yǔ)句，集經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)，如果該存儲(chǔ)過(guò)程帶有參數(shù)來(lái)執(zhí)行。

在sql server 的系列版本中，存儲(chǔ)過(guò)程分為兩類：系統(tǒng)提供的存儲(chǔ)過(guò)程和用戶自定義存儲(chǔ)過(guò)程。系統(tǒng)SP，主要存儲(chǔ)master 數(shù)據(jù)庫(kù)中，并以sp_為前綴并且系統(tǒng)存儲(chǔ)過(guò)程主要是從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQL Server。

用戶自定義存儲(chǔ)過(guò)程是由用戶創(chuàng)建，并能完成某一特定功能，如：查詢用戶所需數(shù)據(jù)信息的存儲(chǔ)過(guò)程。

這里我們看看存儲(chǔ)過(guò)程的好處；

(1)重復(fù)使用。存儲(chǔ)過(guò)程可以重復(fù)使用，從而可以減少數(shù)據(jù)庫(kù)開發(fā)人員的工作量。

(2)提高性能。存儲(chǔ)過(guò)程在創(chuàng)建的時(shí)候就進(jìn)行了編譯，將來(lái)使用的時(shí)候不用再重新編譯。一般的SQL語(yǔ)句每執(zhí)行一次就需要編譯一次，所以使用存儲(chǔ)過(guò)程提高了效率。

(3)減少網(wǎng)絡(luò)流量。存儲(chǔ)過(guò)程位于服務(wù)器上，調(diào)用的時(shí)候只需要傳遞存儲(chǔ)過(guò)程的名稱以及參數(shù)就可以了，因此降低了網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。

(4)安全性。參數(shù)化的存儲(chǔ)過(guò)程可以防止SQL注入式的攻擊，而且可以將Grant、Deny以及Revoke權(quán)限應(yīng)用于存儲(chǔ)過(guò)程。

好了，我們來(lái)看看創(chuàng)建的基本語(yǔ)法

定義存儲(chǔ)過(guò)程的語(yǔ)法

CREATEPROC[EDURE]存儲(chǔ)過(guò)程名

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

…… ,

@參數(shù)n數(shù)據(jù)類型OUTPUT

AS

SQL語(yǔ)句

GO

參數(shù)是可選的

參數(shù)分為輸入?yún)��?shù)、輸出參數(shù)

輸入?yún)��?shù)允許有默認(rèn)值

這里來(lái)創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程

CREATE PROCEDURE UserLogin@name varchar(20),@password varchar(20)

AS

-- 定義一個(gè)臨時(shí)用來(lái)保存密碼的變量--DECLARE @strPwd NVARCHAR(20) 這里先不介紹變量。稍后的文章會(huì)詳細(xì)講到BEGINselect * from userinfo where userName=@name and userPass=@passwordENDGO

首先我們用簡(jiǎn)單的sql查詢

select * from userinfo where userName='admin'

查詢結(jié)果：

---------------------

UserName UserPass

Admin Admin

現(xiàn)在我們來(lái)執(zhí)行我們的存儲(chǔ)過(guò)程exec UserLogin admin,admin

--或這樣調(diào)用：EXEC UserLogin @name='admin',@password='admin'

查詢結(jié)果：

---------------------

UserName UserPass

Admin Admin

注意，在SQL SERVER中，所有用戶定義的變量都以“@”開頭，OUTPUT關(guān)鍵字表示這個(gè)參數(shù)是用來(lái)輸出的，AS之后就是存儲(chǔ)過(guò)程內(nèi)容了。只要將以上代碼在“查詢分析器”里執(zhí)行一次，SQL SERVER就會(huì)在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名為“UserLogin”的存儲(chǔ)過(guò)程。你可以打開“企業(yè)管理器”，選擇當(dāng)前操作的數(shù)據(jù)庫(kù)，然后在左邊的樹型列表中選擇"可編程性->存儲(chǔ)過(guò)程"，此時(shí)就可以在右邊的列表中看到你剛剛創(chuàng)建的存儲(chǔ)過(guò)程了（如果沒(méi)有，刷新一下即可）。

看到了在數(shù)據(jù)中調(diào)用存儲(chǔ)過(guò)程的方法有兩種（這里EXEC與EXECUTE等效）；

EXEC過(guò)程名 參數(shù)值1，參數(shù)值2，....

或者

EXEC 參數(shù)1=參數(shù)值1，參數(shù)2=參數(shù)值2....

上面我們也看到了。

創(chuàng)建這樣的存儲(chǔ)過(guò)程有什么用呢，不是只能在數(shù)據(jù)中查看賽。

我們?cè)谧鰓eb或者winform 程序，假設(shè)需要個(gè)登錄，好，我們可以調(diào)用用這個(gè)存儲(chǔ)過(guò)程來(lái)登錄，根據(jù)傳入的參數(shù)，如果查詢出來(lái)有記錄，那么這條記錄在數(shù)據(jù)庫(kù)中存在，表示登錄成功，否則失敗。

這樣做的目的更加安全，可以防止sql注入。