Sql Server儲存過程詳解

從儲存過程返回資料 - SQL Server | Microsoft 官方檔案

儲存過程它是真正的指令碼，更準確地說，它是批次處理(batch)，但都不是很確切，它儲存與資料庫而不是單獨的檔案中。

儲存過程中有輸入引數，輸出引數以及返回值等。

一、建立儲存過程:CREATE PROC

建立儲存過程的方法除了他使用AS關鍵字外，和建立資料庫中任何其他物件一樣。儲存過程的基本語法如下：

在語法中，PROC是PROCEDURE的縮寫，兩個選項的意思一樣。在對儲存過程命名完之後，接著是參數列。引數是可選的。關鍵字AS其後就是實際的程式碼。

CREATE PROCEDURE|PROC <sproc name>
　　[ [schema.] <data type> [VARYING] [=] [OUT[PUT]] [READONLY]
　　[, [schema.] <data type> [VARYING] [=] [OUT[PUT]] [READONLY]
　　[,...
　　...    
　　]]
　　[WITH 
　　RECOMPILE | ENCRYPTION | [EXECUTE AS { CALLER | SELF | OWNER | <'user name'>}]
　　AS
　　<code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>

簡單的儲存過程範例：

CREATE PROC spPerson
　　AS
    SELECT * FROM Person

執行儲存過程：

EXEC spPerson

1、宣告引數

宣告引數需要以下幾部分的資訊:名稱、資料型別 、預設值 、方向、

對於名稱，有一組簡單的規則。

• 它必須以@符號(和變數一樣)開始。此外，除了不能內嵌空格外，其規則與普通變數規則相同。
• 資料型別和名稱一樣，必須像變數那樣宣告，採用SQL Server內建的或使用者自定義的資料型別。
• 宣告需要型別時需要注意，當宣告CURSOR型別引數時，必須也使用VARYING和OUTPUT選項。同時，OUTPUT可以簡寫為OUT。

其語法如下所示:

@parameter_name [AS] datatype [=default|NULL] [VARYING] [OUTPUT | OUT]

一個需要傳入引數的儲存過程範例：

CREATE PROC spName
　　　　@Name nvarchar(50)
　　AS
　　SELECT Name FROM Person WHERE Name LIKE @Name + '%';

執行儲存過程：

EXEC spName '酒';

2、提供預設值

在預設值方面，引數與變數不同。對於同樣的情況，變數一般初始化為NULL值，而引數不是。事實上，如果不提供預設值，則會假設引數是必須的，並且當呼叫儲存過程時需要提供一個初始值。

為了使引數是可選的，必須提供預設值。方法是在資料型別後在逗號之前新增"="符號和作為預設值的值。這樣，儲存過程的使用者可以有決定對此引數不提供值或是提供他們自己的值。

建立一個儲存過程如下：

CREATE PROC spName
　　@Name nvarchar(50) = NULL
　　AS
　　IF @Name IS NOT NULL
    　　SELECT * FROM Person WHERE NAME = @Name
　　ELSE
    　　SELECT * FROM Person WHERE Id = 45

執行如下語句：

EXEC spName 
EXEC spName '如意刀狼'

輸出結果如下：

3、輸出引數

一個獲得OUTPUT引數的儲存過程：

CREATE PROC InsertPerson
    　　@Id int OUTPUT　　--必須註明為OUTPUT
　　AS
　　INSERT INTO Person　VALUES('劉備',22,190,'不詳','未婚','幼兒園','不詳',4999999)
　　SET @Id = @@IDENTITY

執行儲存過程：

DECLARE @Id int　　--實際上，呼叫時名稱可以不同，例如也可以為@Num,@i等等。
EXEC InsertPerson @Id OUTPUT    --注意此處也要有OUTPUT
SELECT @Id

4、返回值。返回值必須是整數。

返回值可用來確定儲存過程執行的狀態。

SQL Server預設會在完成儲存過程時自動返回一個0值。

為了從儲存過程向呼叫程式碼傳遞返回值，只需要使用RETURN語句。

RETURN []

要特別注意的是：返回值必須是整數。

關於RETURN語句，最重要的是知道它是無條件地從儲存過程中退出的。無論執行到儲存過程的哪個位置，在呼叫RETURN語句之後將不會執行任何一行程式碼。

下面的儲存過程，讓其返回一個指定的值，以指示執行狀態。

CREATE PROC spTestReturns
　　AS
　　DECLARE @MyMessage nvarchar(50);
　　DECLARE @MyOtherMessage nvarchar(50);

　　SELECT @MyMessage = '第一個RETURN';
　　PRINT @MyMessage;    
　　RETURN 100;        --將這裡改成返回100

　　SELECT @MyOtherMessage = '第二個RETURN';
　　PRINT @MyOtherMessage;
　　RETURN;

執行之後，顯示結果如下：

DECLARE @Return int
EXEC @Return = spTestReturns  //第一個RETURN
SELECT @Return   //返回100

5、執行儲存過程：

對於呼叫儲存過程需要注意以下幾點：

• 對於儲存過程宣告中的輸出引數，需要使用OUTPUT關鍵字。
• 和宣告儲存過程時一樣，呼叫儲存過程時，必須使用OUTPUT關鍵字。這樣就對SQL Server作了提前通知，告訴它引數所需要的特殊處理。但需要注意的是，如果忘記包含OUTPUT關鍵字，不會產生執行時錯誤，但是輸出的值不會傳入變數中(變數很可能是NULL)。
• 賦值給輸出結果的變數不需要和儲存過程中的內部引數擁有相同的名稱。
• EXEC(或EXECUTE)關鍵字是必須的，因為對儲存過程的呼叫並不是批次處理要做的第一件事(如果儲存過程的呼叫是批次處理的第一件事，則可以不使用EXEC)。

6、WITH RECOMPILE選項

可以利用儲存過程提供的安全性程式碼和程式碼封裝方面的好處，但還是忽略了預編譯程式碼方面的影響。可以迴避未使用正確的查詢計劃的問題，因為可以確保為特定一次執行建立新的計劃。方法就是使用WITH RECOMPILE選項。 
使用該選項的方式有兩種：

1、可以在執行時包含WITH RECOMPILE。這告訴SQL Server拋棄已有的執行計劃並且建立一個新的計劃-但只是這一次。也就是說，只是這次使用WITH RECOMPILE選項來執行儲存過程。

EXEC spMySproc '1/1/2004'
　　WITH RECOMPILE

2、也可以通過在儲存過程中包含WITH RECOMPILE選項來使之變得更持久。

如果使用這種方式，則在CREATE PROC或ALTER PROC語句中的AS語句前新增WITH RECOMPILE選項即可。如果通過該選項建立儲存過程，那麼無論在執行時選擇了其他什麼選項，每次執行儲存過程都會重新編譯它。

二、修改儲存過程:ALTER PROC

ALTER PROC spPerson
　　AS
　　SELECT * FROM Person WHERE Id = 45

三、刪除儲存過程:DROP PROC

DROP PROC|PROCEDURE <sproc name>[;]

四、常用儲存過程

1、sp_help： 查詢表的資訊

sp_help Person

看一張表有那些資訊，有約束，儲存過程，自定義函數等等資訊。

2、sp_helpdb： 檢視資料庫資訊

sp_helpdb TestDataCenter

當然也可以不帶引數，顯示當前資料庫連線下的所有資料庫資訊。

這張圖幾乎包含了資料庫的所有資訊了。有了這張圖，想了解一個資料庫的資訊就簡單了。

3、sp_helpindex： 檢視有關表或檢視上的索引的資訊

sp_helpindex Person

注意引數中是表名，上面的Person就是表名，而不是索引名稱。

4、sp_helpconstraint： 檢視表上的約束資訊

sp_helpconstraint Person

注意引數是表名。

5、sp_helpfile： 根據檔案邏輯名稱， 檢視檔案的資訊

sp_helpfile TestDataCenter

注意引數是檔案的邏輯名稱。也可以不帶引數，輸出當前資料庫的所有檔案資訊。

6、sp_helpfilegroup： 根據檔案組名稱，檢視檔案組資訊

sp_helpfilegroup 'PRIMARY'

引數名中是檔案組的邏輯名稱，當然也可以不帶引數，這樣就僅僅輸出當前資料庫的檔案組資訊。

顯示結果如下：

7、sp_helptext：顯示預設值、未加密的 Transact-SQL 儲存過程、使用者定義 Transact-SQL 函數、觸發器、計算列、CHECK 約束燈等的定義。

sp_helptext spName

返回的是什麼？就是定義的程式碼。

到此這篇關於Sql Server儲存過程的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。