SQL Server存儲(chǔ)過(guò)程總結(jié)

存儲(chǔ)過(guò)程簡(jiǎn)介：

存儲(chǔ)過(guò)程（Stored Procedure）是在大型數(shù)據(jù)庫(kù)中，一組為了完成特定功能的SQL 語(yǔ)句集，它存儲(chǔ)在數(shù)據(jù)庫(kù)中，一次編譯后永久有效，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象。在數(shù)據(jù)量特別龐大的情況下利用存儲(chǔ)過(guò)程能達(dá)到倍速的效率提升

? ------來(lái)源于百度百科

存儲(chǔ)過(guò)程的種類：

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

以sp_開頭,用來(lái)進(jìn)行系統(tǒng)的各項(xiàng)設(shè)定.取得信息.相關(guān)管理工作。

2本地存儲(chǔ)過(guò)程

用戶創(chuàng)建的存儲(chǔ)過(guò)程是由用戶創(chuàng)建并完成某一特定功能的存儲(chǔ)過(guò)程，事實(shí)上一般所說(shuō)的存儲(chǔ)過(guò)程就是指本地存儲(chǔ)過(guò)程。

3臨時(shí)存儲(chǔ)過(guò)程

分為兩種存儲(chǔ)過(guò)程：

一是本地臨時(shí)存儲(chǔ)過(guò)程，以井字號(hào)(#)作為其名稱的第一個(gè)字符，則該存儲(chǔ)過(guò)程將成為一個(gè)存放在tempdb數(shù)據(jù)庫(kù)中的本地臨時(shí)存儲(chǔ)過(guò)程，且只有創(chuàng)建它的用戶才能執(zhí)行它;

二是全局臨時(shí)存儲(chǔ)過(guò)程，以兩個(gè)井字號(hào)(##)號(hào)開始，則該存儲(chǔ)過(guò)程將成為一個(gè)存儲(chǔ)在tempdb數(shù)據(jù)庫(kù)中的全局臨時(shí)存儲(chǔ)過(guò)程，全局臨時(shí)存儲(chǔ)過(guò)程一旦創(chuàng)建，以后連接到服務(wù)器的任意用戶都可以執(zhí)行它，而且不需要特定的權(quán)限。

4遠(yuǎn)程存儲(chǔ)過(guò)程

在SQL Server2005中，遠(yuǎn)程存儲(chǔ)過(guò)程(Remote Stored Procedures)是位于遠(yuǎn)程服務(wù)器上的存儲(chǔ)過(guò)程，通?？梢允褂梅植际讲樵兒虴XECUTE命令執(zhí)行一個(gè)遠(yuǎn)程存儲(chǔ)過(guò)程。

5擴(kuò)展存儲(chǔ)過(guò)程

擴(kuò)展存儲(chǔ)過(guò)程(Extended Stored Procedures)是用戶可以使用外部程序語(yǔ)言編寫的存儲(chǔ)過(guò)程，而且擴(kuò)展存儲(chǔ)過(guò)程的名稱通常以xp_開頭。

? ------來(lái)源于百度百科

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

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

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

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

簡(jiǎn)單講：

1.存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，而一般SQL語(yǔ)句每執(zhí)行一次就編譯一次,所以使用存儲(chǔ)過(guò)程可提高數(shù)據(jù)庫(kù)執(zhí)行速度。

2.當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)(如對(duì)多個(gè)表進(jìn)行Update,Insert,Query,Delete時(shí))，可將此復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用。

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

4.安全性高,可設(shè)定只有某些用戶才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán)

有一點(diǎn)需要注意的是，一些網(wǎng)上盛傳的所謂的存儲(chǔ)過(guò)程要比sql語(yǔ)句執(zhí)行更快的說(shuō)法，實(shí)際上是個(gè)誤解，并沒有根據(jù)，包括微軟內(nèi)部的人也不認(rèn)可這一點(diǎn)，所以不能作為正式的優(yōu)點(diǎn)，希望大家能夠認(rèn)識(shí)到這一點(diǎn)。

存儲(chǔ)過(guò)程的缺點(diǎn)：

1：調(diào)試麻煩。

2：數(shù)據(jù)庫(kù)移植不方便，存儲(chǔ)過(guò)程依賴與數(shù)據(jù)庫(kù)管理系統(tǒng)， SQL Server 存儲(chǔ)過(guò)程中封裝的操作代碼不能直接移植到其他的數(shù)據(jù)庫(kù)管理系統(tǒng)中。

3：重新編譯問(wèn)題，因?yàn)楹蠖舜a是運(yùn)行前編譯的，如果帶有引用關(guān)系的對(duì)象發(fā)生改變時(shí)，受影響的存儲(chǔ)過(guò)程、包將需要重新編譯（不過(guò)也可以設(shè)置成運(yùn)行時(shí)刻自動(dòng)編譯）。

4： 如果在一個(gè)程序系統(tǒng)中大量的使用存儲(chǔ)過(guò)程，到程序交付使用的時(shí)候隨著用戶需求的增加會(huì)導(dǎo)致數(shù)據(jù)結(jié)構(gòu)的變化，接著就是系統(tǒng)的相關(guān)問(wèn)題了，最后如果用戶想維護(hù)該系統(tǒng)可以說(shuō)是很難很難、而且代價(jià)是空前的，維護(hù)起來(lái)更麻煩。

5：不支持面向?qū)ο蟮脑O(shè)計(jì)，無(wú)法采用面向?qū)ο蟮姆绞綄⑦壿嫎I(yè)務(wù)進(jìn)行封裝，甚至形成通用的可支持服務(wù)的業(yè)務(wù)邏輯框架。

存儲(chǔ)過(guò)程實(shí)例：

創(chuàng)建books表

use Test;
--創(chuàng)建測(cè)試books表
create table Money (
    id int identity(1,1) primary key,
    money int
);

無(wú)參存儲(chǔ)過(guò)程

--創(chuàng)建一個(gè)無(wú)參存儲(chǔ)過(guò)程
if exists(select * from sysobjects where name='usp_getAllMoneyInfo')
drop proc usp_getAllMoneyInfo
go

創(chuàng)建存儲(chǔ)過(guò)程

--存儲(chǔ)過(guò)程相當(dāng)于一個(gè)函數(shù) 可以有參數(shù)，輸出參數(shù) as后為方法體 使用return進(jìn)行返回--
create proc usp_getAllMoneyInfo
--as前面為定義參數(shù) 相當(dāng)于函數(shù)中的參數(shù)， 不用進(jìn)行聲明--
as
select * from Money
go

執(zhí)行存儲(chǔ)過(guò)程

--執(zhí)行存儲(chǔ)過(guò)程

exec usp_getAllMoneyInfo;

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

--刪除存儲(chǔ)過(guò)程
drop proc usp_getAllMoneyInfo;
go

修改存儲(chǔ)過(guò)程

alter proc usp_getAllMoneyInfo
as
select Money.money from Money
go

創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過(guò)程

if exists(select * from sysobjects where name='usp_getMoneyInfo')
drop proc usp_getMoneyInfo
go
--創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過(guò)程 并執(zhí)行
create proc usp_getMoneyInfo
--as前面為定義參數(shù) 相當(dāng)于函數(shù)中的參數(shù) 不用進(jìn)行聲明
@id int
as
select * from Money where id=@id
go
exec usp_getMoneyInfo 2 --在調(diào)用后面添加參數(shù)
go

創(chuàng)建一個(gè)帶output參數(shù)的存儲(chǔ)過(guò)程

--創(chuàng)建一個(gè)帶output參數(shù)的存儲(chǔ)過(guò)程 并執(zhí)行
if exists(select * from sysobjects where name='usp_getAllMoneyCount')
drop proc usp_getAllMoneyCount
go
create proc usp_getAllMoneyCount
@count int output --外部參數(shù)在后面加output類似于c#的out
as
set @count = (select count(*) from Money)
go

declare @cnt int;
exec usp_getAllMoneyCount @count=@cnt output --在調(diào)用的時(shí)候 要加output的關(guān)鍵字
print @cnt

創(chuàng)建一個(gè)帶有返回值的存儲(chǔ)過(guò)程

--創(chuàng)建一個(gè)帶有返回值的存儲(chǔ)過(guò)程 并執(zhí)行
--帶有返回值的存儲(chǔ)過(guò)程--
--return只能返回整型的數(shù)據(jù)，如果需要返回其他類型的數(shù)據(jù)，需要使用output輸出參數(shù)--
if exists(select * from sysobjects where name='usp_getCount')
drop proc usp_getCount
go
create proc usp_getCount
as
declare @count int 
set @count = (select count(*) from Money)
return @count
go
declare @totalCount int
exec @totalCount = usp_getCount
print @totalCount

文章持續(xù)更新，如果喜歡，請(qǐng)拿起你們可愛的小手，給我點(diǎn)個(gè)贊吧！

點(diǎn)贊是一種積極的生活態(tài)度，贊一個(gè)吧！