SQL SERVER 預存程序複習

將常用的或很複雜的工作，預先用SQL語句寫好並用一個指定的名稱儲存起來, 那麼以後要叫資料庫提供與已定義好的預存程序的功能相同的服務時,只需調用execute,即可自動完成命令。

                  預存程序的優點

    1.預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。     2.當對資料庫進行複雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。     3.預存程序可以重複使用,可減少資料庫開發人員的工作量     4.安全性高,可設定只有某此使用者才具有對指定預存程序的使用權

                      建立預存程序 *************************************************

文法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中內建函數的使用.已不屬於本文所要討論的範圍了.

將常用的或很複雜的工作，預先用SQL語句寫好並用一個指定的名稱儲存起來, 那麼以後要叫資料庫提供與已定義好的預存程序的功能相同的服務時,只需調用execute,即可自動完成命令。

                  預存程序的優點

    1.預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。     2.當對資料庫進行複雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。     3.預存程序可以重複使用,可減少資料庫開發人員的工作量     4.安全性高,可設定只有某此使用者才具有對指定預存程序的使用權

                      建立預存程序 *************************************************

文法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中內建函數的使用.已不屬於本文所要討論的範圍了.