將常用的或很復(fù)雜的工作，預(yù)先用SQL語句寫好并用一個指定的名稱存儲起來, 那么以后要叫數(shù)據(jù)庫提供與已定義好的存儲過程的功能相同的服務(wù)時,只需調(diào)用execute,即可自動完成命令。

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

??? 1.存儲過程只在創(chuàng)造時進(jìn)行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。
??? 2.當(dāng)對數(shù)據(jù)庫進(jìn)行復(fù)雜操作時(如對多個表進(jìn)行Update,Insert,Query,Delete時），可將此復(fù)雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。
??? 3.存儲過程可以重復(fù)使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量
????4.安全性高,可設(shè)定只有某此用戶才具有對指定存儲過程的使用權(quán)

?????????????????????? 創(chuàng)建存儲過程
*************************************************

語法

CREATE PROC[ EDURE ] [ owner . ] procedure_name [ ; number ]
????[ { @ parameter data_type }
???????? [ VARYING ] [ = default ] [ OUTPUT ]
???? ] [ , ... n ]

[ WITH
????{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ... n ]

參數(shù)

owner

??? 擁有存儲過程的用戶 ID 的名稱。 owner 必須是當(dāng)前用戶的名稱或當(dāng)前用戶所屬的角色的名稱。

procedure_name

??? 新存儲過程的名稱。過程名必須符合標(biāo)識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須唯一。

; number

??? 是可選的整數(shù)，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應(yīng)用程序使用的過程可以命名為 orderproc ;1、 orderproc ;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標(biāo)識符，則數(shù)字不應(yīng)包含在標(biāo)識符中，只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)亩ń绶?

@ parameter

??? 過程中的參數(shù)。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值，或者該值設(shè)置為等于另一個參數(shù)）。存儲過程最多可以有 2.100 個參數(shù)。

使用 @ 符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。

data_type

??? 參數(shù)的數(shù)據(jù)類型。除 table 之外的其他所有數(shù)據(jù)類型均可以用作存儲過程的參數(shù)。但是， cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定 cursor 數(shù)據(jù)類型，則還必須指定 VARYING 和 OUTPUT 關(guān)鍵字。對于可以是 cursor 數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。

VARYING

??? 指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。

default

??? 參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或 NULL。如果過程將對該參數(shù)使用 LIKE 關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、[] 和 [^]）。

OUTPUT

??? 表明參數(shù)是返回參數(shù)。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過程。 Text 、 ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。

n

??? 表示最多可以指定 2.100 個參數(shù)的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION}

????RECOMPILE 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，請使用 RECOMPILE 選項。

ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復(fù)制的一部分發(fā)布。

FOR REPLICATION

??? 指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。.使用 FOR REPLICATION 選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復(fù)制過程中執(zhí)行。本選項不能和 WITH RECOMPILE 選項一起使用。

AS

?? 指定過程要執(zhí)行的操作。

sql_statement

?? 過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。但有一些限制。

n

?? 是表示此過程可以包含多條 Transact-SQL 語句的占位符。

**********************************************

注:*所包圍部分來自MS的聯(lián)機叢書.

?

?????????????????????????? 幾個實例

??????????????????????? (AjaxCity表中內(nèi)容)

　　　　　ID??????? CityName?? Short

?????????????? 1?????????? 蘇州市 ??????? SZ　　

????????????? ?2　　　? 無錫市?????? WX

?????????????? 3?????????? 常州市 ??????? CZ

1.選擇表中所有內(nèi)容并返回一個數(shù)據(jù)集

??????? CREATE PROCEDURE mysp_All
??????? AS
?????????? select * from AjaxCity
????????GO

執(zhí)行結(jié)果

???????

2.根據(jù)傳入的參數(shù)進(jìn)行查詢并返回一個數(shù)據(jù)集

???????CREATE PROCEDURE mysp_para
??????????? @CityName varchar(255),

??????????? @Short??? varchar(255)
?????? AS
???????? select * from AjaxCity where CityName=@CityName ?And Short=@Short
?????? GO

執(zhí)行結(jié)果

???????

3.帶有輸出參數(shù)的存儲過程(返回前兩條記錄的ID的和)

CREATE PROCEDURE mysp_output
?????? @SUM int? output
?AS
?????? select @SUM=sum([ID]) from (select top 2 * from AjaxCity) as tmpTable
GO

執(zhí)行結(jié)果

????????

4.在存儲過程中使用游標(biāo)

??有這樣一個表,存儲的是各超階級市下面的縣級市的信息.如圖:

??

?? 現(xiàn)在想統(tǒng)計出各個地級市下面的縣級市的個數(shù),并組成一個字符串.結(jié)果應(yīng)該是"5,2,2".

?

CREATE PROCEDURE mysp_Cursor
??? @Result varchar(255) output //聲明輸出變量
AS
??? declare city_cursor cursor for //聲明游標(biāo)變量
??? select [ID] from AjaxCity

set @Result=''
declare @Field int //聲明臨時存放CityID的變量
open city_cursor //打開游標(biāo)
fetch next from city_cursor into @Field //將實際ID賦給變量
while(@@fetch_status=0) //循環(huán)開始
begin
?????? if @Result = ''
?????????? select @Result = convert(nvarchar(2),count(*))? from AjaxCounty where CityID=@Field
?????? else
?????????? select @Result = @Result + ',' + convert(nvarchar(2),count(*)) from AjaxCounty where CityID=@Field
??????
?????? fetch next from city_cursor into @Field //下一個CityID
end
close city_cursor //關(guān)閉游標(biāo)
deallocate city_cursor //釋放游標(biāo)引用
GO

?

執(zhí)行結(jié)果

??????

?

??? 好了,關(guān)于存儲過程先寫到這里.以上幾個例子基本上實現(xiàn)了平常所用到的大部分功能.至于復(fù)雜的存儲過程,所用到的知道主要是SQL的語法,以及SQL中內(nèi)置函數(shù)的使用.已不屬于本文所要討論的范圍了.

?

SQL SERVER 存儲過程學(xué)習(xí)筆記