SQL Server預存程序

  一,定義:Sql Server的預存程序是一個被命名的儲存在伺服器上的Transacation-Sql語句集合,是封裝重複性工作的一種方法.

  二,預存程序的優點:

  　1,重複使用。預存程序可以重複使用，從而可以減少資料庫開發人員的工作量。

　  2,提高效能。預存程序在建立的時候就進行了編譯，將來使用的時候不用再重新編譯。一般的SQL語句每執行一次就需要編譯一次，所以使用預存程序提高了效率。

     3,減少網路流量。預存程序位於伺服器上，調用的時候只需要傳遞預存程序的名稱以及參數就可以了，因此降低了網路傳輸的資料量。

　  4,安全性。參數化的預存程序可以防止SQL注入式的攻擊，而且可以將Grant、Deny以及Revoke許可權應用於預存程序。

  三,文法,建立預存程序:　　

文法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 ]

參數

owner

    擁有預存程序的使用者識別碼 的名稱。owner 必須是目前使用者的名稱或目前使用者所屬的角色的名稱。

procedure_name

    新預存程序的名稱。過程名必須符合標識符規則，且對於資料庫及其所有者必須唯一。

;number

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

@parameter

    過程中的參數。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數。使用者必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的預設值，或者該值設定為等於另一個參數）。預存程序最多可以有 2.100 個參數。

使用 @ 符號作為第一個字元來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用於該過程本身；相同的參數名稱可以用在其它過程中。預設情況下，參數只能代替常量，而不能用於代替表名、列名或其它資料庫物件的名稱。

data_type

    參數的資料類型。除 table 之外的其他所有資料類型均可以用作預存程序的參數。但是，cursor 資料類型只能用於 OUTPUT 參數。如果指定 cursor 資料類型，則還必須指定 VARYING 和 OUTPUT 關鍵字。對於可以是 cursor 資料類型的輸出參數，沒有最大數目的限制。

VARYING

    指定作為輸出參數支援的結果集（由預存程序動態構造，內容可以變化）。僅適用於遊標參數。

default

    參數的預設值。如果定義了預設值，不必指定該參數的值即可執行過程。預設值必須是常量或 NULL。如果過程將對該參數使用 LIKE 關鍵字，那麼預設值中可以包含萬用字元（%、_、[] 和 [^]）。

OUTPUT

    表明參數是返回參數。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數可將資訊返回給調用過程。Text、ntext 和 image 參數可用作 OUTPUT 參數。使用 OUTPUT 關鍵字的輸出參數可以是遊標預留位置。

n

    表示最多可以指定 2.100 個參數的預留位置。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

    RECOMPILE 表明 SQL Server 不會緩衝該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩衝在記憶體中的執行計畫時，請使用 RECOMPILE 選項。

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

FOR REPLICATION

    指定不能在訂閱伺服器上執行為複製建立的預存程序。.使用 FOR REPLICATION 選項建立的預存程序可用作預存程序篩選，且只能在複製過程中執行。本選項不能和 WITH RECOMPILE 選項一起使用。

AS

   指定過程要執行的操作。

sql_statement

   過程中要包含的任意數目和類型的 Transact-SQL 陳述式。但有一些限制。

n

   是表示此過程可以包含多條 Transact-SQL 陳述式的預留位置。

  四,使用方法:

　　

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

注:*所包圍部分來自MS的聯機叢書.

 

                           幾個執行個體

                        (AjaxCity表中內容)

　　　　　ID        CityName   Short

             1         蘇州市     SZ　　

             2　　　　 無錫市     WX

             3         常州市     CZ

1.選擇表中所有內容並返回一個資料集

        CREATE PROCEDURE mysp_All
        AS
           select * from AjaxCity
        GO

執行結果

       

2.根據傳入的參數進行查詢並返回一個資料集

       CREATE PROCEDURE mysp_para
            @CityName varchar(255),

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

執行結果

       

3.帶有輸出參數的預存程序(返回前兩條記錄的ID的和)

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

執行結果

        

4.在預存程序中使用遊標

  有這樣一個表,儲存的是各超階級市下面的縣級市的資訊.:

  

   現在想統計出各個地級市下面的縣級市的個數,並組成一個字串.結果應該是"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)//迴圈開始
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

 

執行結果

      

 

    好了,關於預存程序先寫到這裡.以上幾個例子基本上實現了平常所用到的大部分功能.至於複雜的預存程序,所用到的知道主要是SQL的文法,以及SQL中內建函數的使用.已不屬於本文所要討論的範圍了.