存儲過程和函數(shù)的區(qū)別
前兩天面試時被面試官問到這個問題,搜腸刮肚也沒有說出個所以然來.看來對數(shù)據(jù)庫知識還是要時時總結(jié)歸納一下啊！
在網(wǎng)上找了些資料,歸納如下:
先上個圖片:
Oracle中差別
SQL SERVER中：
本質(zhì)上沒區(qū)別。只是函數(shù)有如：只能返回一個變量的限制。而存儲過程可以返回多個。而函數(shù)是可以嵌入在sql中使用的,可以在select中調(diào)用，而存儲過程不行。執(zhí)行的本質(zhì)都一樣。
函數(shù)限制比較多，比如不能用臨時表，只能用表變量．還有一些函數(shù)都不可用等等．而存儲過程的限制相對就比較少
1. 一般來說，存儲過程實(shí)現(xiàn)的功能要復(fù)雜一點(diǎn)，而函數(shù)的實(shí)現(xiàn)的功能針對性比較強(qiáng)。
2. 對于存儲過程來說可以返回參數(shù)，而函數(shù)只能返回值或者表對象。
3. 存儲過程一般是作為一個獨(dú)立的部分來執(zhí)行（EXEC執(zhí)行），而函數(shù)可以作為查詢語句的一個部分來調(diào)用（SELECT調(diào)用），由于函數(shù)可以返回一個表對象，因此它可以在查詢語句中位于FROM關(guān)鍵字的后面。
4. 當(dāng)存儲過程和函數(shù)被執(zhí)行的時候，SQL Manager會到procedure cache中去取相應(yīng)的查詢語句，如果在procedure cache里沒有相應(yīng)的查詢語句，SQL Manager就會對存儲過程和函數(shù)進(jìn)行編譯。
Procedure cache中保存的是執(zhí)行計(jì)劃 (execution plan) ，當(dāng)編譯好之后就執(zhí)行procedure cache中的execution plan，之后SQL SERVER會根據(jù)每個execution plan的實(shí)際情況來考慮是否要在cache中保存這個plan，評判的標(biāo)準(zhǔn)一個是這個execution plan可能被使用的頻率；其次是生成這個plan的代價(jià)，也就是編譯的耗時。保存在cache中的plan在下次執(zhí)行時就不用再編譯了。
函數(shù)限制比較多，如不能用臨時表，只能用表變量等，而存儲過程的限制相對就比較少。
1. 一般來說，存儲過程實(shí)現(xiàn)的功能要復(fù)雜一點(diǎn)，而函數(shù)的實(shí)現(xiàn)的功能針對性比較強(qiáng)。
2. 對于存儲過程來說可以返回參數(shù)，而函數(shù)只能返回值或者表對象。
3. 存儲過程一般是作為一個獨(dú)立的部分來執(zhí)行，而函數(shù)可以作為查詢語句的一個部分來調(diào)用，由于函數(shù)
可以返回一個表對象，因此它可以在查詢語句中位于FROM關(guān)鍵字的后面。
4. 當(dāng)存儲過程和函數(shù)被執(zhí)行的時候，SQL Manager會到procedure cache中去取相應(yīng)的查詢語句，如果在
procedure cache里沒有相應(yīng)的查詢語句，SQL Manager就會對存儲過程和函數(shù)進(jìn)行編譯。
Procedure cache：中保存的是執(zhí)行計(jì)劃，當(dāng)編譯好之后就執(zhí)行procedure cache中的execution plan，之后SQL SERVER會根據(jù)每個execution plan的實(shí)際情況來考慮是否要在cache中保存這個plan，評判的標(biāo)準(zhǔn)一個是這個execution plan可能被使用的頻率；其次是生成這個plan的代價(jià)，也就是編譯的耗時。保存在cache中的plan在下次執(zhí)行時就不用再編譯了。
存儲過程和函數(shù)具體的區(qū)別：
存儲過程：可以使得對的管理、以及顯示關(guān)于及其用戶信息的工作容易得多。存儲過程是 SQL 語句和可選控制流語句的預(yù)編譯集合，以一個名稱存儲并作為一個單元處理。存儲過程存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強(qiáng)大的編程功能。存儲過程可包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集以及返回值。
可以出于任何使用 SQL 語句的目的來使用存儲過程，它具有以下優(yōu)點(diǎn)：
(1)功能強(qiáng)大，限制少。
(2)可以在單個存儲過程中執(zhí)行一系列 SQL 語句。
(3)可以從自己的存儲過程內(nèi)引用其它存儲過程，這可以簡化一系列復(fù)雜語句。
(4)存儲過程在創(chuàng)建時即在上進(jìn)行編譯，所以執(zhí)行起來比單個 SQL 語句快。
(5)可以有多個返回值，即多個輸出參數(shù)，并且可以使用SELECT返回結(jié)果集。
函數(shù)：是由一個或多個 SQL 語句組成的子程序，可用于封裝代碼以便重新使用。自定義函數(shù)諸多限制，有許多語句不能使用，許多功能不能實(shí)現(xiàn)。函數(shù)可以直接引用返回值，用表變量返回記錄集。但是，用戶定義函數(shù)不能用于執(zhí)行一組修改全局?jǐn)?shù)據(jù)庫狀態(tài)的操作。
補(bǔ)充：
前面有一句，“可以處于任何使用SQL語句的目的來使用存儲過程”。這里想說的是，有些時候有些地方使用函數(shù)或許會更方便些。例如，存在這樣一個表：Temperature(Year, Month, Day, T02, T08, T14, T20)，其中Year,Month,Day是時間字段，T02, T08, T14, T20是指2時、8時、14時、20時四個時刻對應(yīng)的溫度值，這些溫度值可為空。現(xiàn)在，要求統(tǒng)計(jì)2008年5月份的平均溫度。
或許大家會這樣寫：
SELECT (AVG(T02)+AVG(T08)+AVG(T14)+AVG(T20))/4 FROM Temperature WHERE Year=2008 AND Month=5
如果不考慮空值的話，這樣完全正確，但是考慮空值的話，如果根本沒有統(tǒng)計(jì)02時的溫度，那么AVG(T02)將為NULL，然后進(jìn)行所有運(yùn)算的結(jié)果都將為 NULL。這顯然是不正確的。
這里可以創(chuàng)建一個自定義函數(shù)，然后使用一個SELECT語句即可查詢：
SELECT AVG(user.Average(T02,T08,T14,T20)) FROM Temperature WHERE Year=2008 AND Month=5
總結(jié)：
用戶自定義函數(shù)在處理同一數(shù)據(jù)行中的各個字段時，特別方便有用。雖然這里使用存儲過程也能達(dá)到查詢目的，但是顯然沒有使用函數(shù)方便。而且，即使使用存儲過程也無法處理SELECT查詢中的同一數(shù)據(jù)行中的各個字段的運(yùn)算。因?yàn)榇鎯^程不返回值，使用時只能單獨(dú)調(diào)用；而函數(shù)卻能出現(xiàn)在能放置表達(dá)式的任何位置。
CREATE FUNCTION user.Average
(
@T02 float,
@T08 float,
@T14 float,
@T20 float
)
RETURNS float AS
BEGIN
DECLARE @sum float
DECLARE @num int
DECLARE @Ret float
SET @sum=0
SET @num=0
IF @T02 IS NOT NULL
BEGIN
SET @sum = @sum + @T02
SET @num = @num + 1
END
IF @T08 IS NOT NULL
BEGIN
SET @sum = @sum + @T08
SET @num = @num + 1
END
IF @T14 IS NOT NULL
BEGIN
SET @sum = @sum + @T14
SET @num = @num + 1
END
IF @T20 IS NOT NULL
BEGIN
SET @sum = @sum + @T20
SET @num = @num + 1
END
IF @num>0 SET @Ret = @sum / @num
Return @Ret
END
GO
