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

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

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

?????????????????????? 創(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 必須是當前用戶的名稱或當前用戶所屬的角色的名稱。

procedure_name

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

; number

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

@ parameter

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

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

data_type

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

VARYING

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

default

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

OUTPUT

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

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ā)布。

FOR REPLICATION

??? 指定不能在訂閱服務器上執(zhí)行為復制創(chuàng)建的存儲過程。.使用 FOR REPLICATION 選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復制過程中執(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ù)進行查詢并返回一個數(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.在存儲過程中使用游標

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

??

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

?

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

set @Result=''
declare @Field int //聲明臨時存放CityID的變量
open city_cursor //打開游標
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 //關閉游標
deallocate city_cursor //釋放游標引用
GO

?

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

??????

?

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

?

SQL SERVER 存儲過程學習筆記