從存儲過程返回數(shù)據(jù) - SQL Server | Microsoft 官方文檔?

存儲過程它是真正的腳本，更準確地說，它是批處理(batch)，但都不是很確切，它存儲與數(shù)據(jù)庫而不是單獨的文件中。

存儲過程中有輸入?yún)?shù)，輸出參數(shù)以及返回值等。

一、創(chuàng)建存儲過程:CREATE PROC

創(chuàng)建存儲過程的方法除了他使用AS關(guān)鍵字外，和創(chuàng)建數(shù)據(jù)庫中任何其他對象一樣。存儲過程的基本語法如下：

在語法中，PROC是PROCEDURE的縮寫，兩個選項的意思一樣。在對存儲過程命名完之后，接著是參數(shù)列表。參數(shù)是可選的。關(guān)鍵字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

執(zhí)行存儲過程：

EXEC spPerson

1、聲明參數(shù)

聲明參數(shù)需要以下幾部分的信息:名稱、數(shù)據(jù)類型 、默認值 、方向、

對于名稱，有一組簡單的規(guī)則。

• 它必須以@符號(和變量一樣)開始。此外，除了不能內(nèi)嵌空格外，其規(guī)則與普通變量規(guī)則相同。
• 數(shù)據(jù)類型和名稱一樣，必須像變量那樣聲明，采用SQL Server內(nèi)置的或用戶自定義的數(shù)據(jù)類型。
• 聲明需要類型時需要注意，當聲明CURSOR類型參數(shù)時，必須也使用VARYING和OUTPUT選項。同時，OUTPUT可以簡寫為OUT。

其語法如下所示:

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

一個需要傳入?yún)?shù)的存儲過程示例：

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

執(zhí)行存儲過程：

EXEC spName '酒';

2、提供默認值

在默認值方面，參數(shù)與變量不同。對于同樣的情況，變量一般初始化為NULL值，而參數(shù)不是。事實上，如果不提供默認值，則會假設(shè)參數(shù)是必須的，并且當調(diào)用存儲過程時需要提供一個初始值。

為了使參數(shù)是可選的，必須提供默認值。方法是在數(shù)據(jù)類型后在逗號之前添加"="符號和作為默認值的值。這樣，存儲過程的用戶可以有決定對此參數(shù)不提供值或是提供他們自己的值。

創(chuàng)建一個存儲過程如下：

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

執(zhí)行如下語句：

EXEC spName 
EXEC spName '如意刀狼'

輸出結(jié)果如下：

3、輸出參數(shù)

一個獲得OUTPUT參數(shù)的存儲過程：

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

執(zhí)行存儲過程：

DECLARE @Id int　　--實際上，調(diào)用時名稱可以不同，例如也可以為@Num,@i等等。
EXEC InsertPerson @Id OUTPUT    --注意此處也要有OUTPUT
SELECT @Id

4、返回值。返回值必須是整數(shù)。

返回值可用來確定存儲過程執(zhí)行的狀態(tài)。

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

為了從存儲過程向調(diào)用代碼傳遞返回值，只需要使用RETURN語句。

RETURN []

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

關(guān)于RETURN語句，最重要的是知道它是無條件地從存儲過程中退出的。無論運行到存儲過程的哪個位置，在調(diào)用RETURN語句之后將不會執(zhí)行任何一行代碼。

下面的存儲過程，讓其返回一個指定的值，以指示執(zhí)行狀態(tài)。

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;

執(zhí)行之后，顯示結(jié)果如下：

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

5、執(zhí)行存儲過程：

對于調(diào)用存儲過程需要注意以下幾點：

• 對于存儲過程聲明中的輸出參數(shù)，需要使用OUTPUT關(guān)鍵字。
• 和聲明存儲過程時一樣，調(diào)用存儲過程時，必須使用OUTPUT關(guān)鍵字。這樣就對SQL Server作了提前通知，告訴它參數(shù)所需要的特殊處理。但需要注意的是，如果忘記包含OUTPUT關(guān)鍵字，不會產(chǎn)生運行時錯誤，但是輸出的值不會傳入變量中(變量很可能是NULL)。
• 賦值給輸出結(jié)果的變量不需要和存儲過程中的內(nèi)部參數(shù)擁有相同的名稱。
• EXEC(或EXECUTE)關(guān)鍵字是必須的，因為對存儲過程的調(diào)用并不是批處理要做的第一件事(如果存儲過程的調(diào)用是批處理的第一件事，則可以不使用EXEC)。

6、WITH RECOMPILE選項

可以利用存儲過程提供的安全性代碼和代碼封裝方面的好處，但還是忽略了預(yù)編譯代碼方面的影響?？梢曰乇芪词褂谜_的查詢計劃的問題，因為可以確保為特定一次運行創(chuàng)建新的計劃。方法就是使用WITH RECOMPILE選項。 
使用該選項的方式有兩種：

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

EXEC spMySproc '1/1/2004'
　　WITH RECOMPILE

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

如果使用這種方式，則在CREATE PROC或ALTER PROC語句中的AS語句前添加WITH RECOMPILE選項即可。如果通過該選項創(chuàng)建存儲過程，那么無論在運行時選擇了其他什么選項，每次運行存儲過程都會重新編譯它。

二、修改存儲過程: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

看一張表有那些信息，有約束，存儲過程，自定義函數(shù)等等信息。

2、sp_helpdb： 查看數(shù)據(jù)庫信息

sp_helpdb TestDataCenter

當然也可以不帶參數(shù)，顯示當前數(shù)據(jù)庫連接下的所有數(shù)據(jù)庫信息。

這張圖幾乎包含了數(shù)據(jù)庫的所有信息了。有了這張圖，想了解一個數(shù)據(jù)庫的信息就簡單了。

3、sp_helpindex： 查看有關(guān)表或視圖上的索引的信息

sp_helpindex Person

注意參數(shù)中是表名，上面的Person就是表名，而不是索引名稱。

4、sp_helpconstraint： 查看表上的約束信息

sp_helpconstraint Person

注意參數(shù)是表名。

5、sp_helpfile： 根據(jù)文件邏輯名稱， 查看文件的信息

sp_helpfile TestDataCenter

注意參數(shù)是文件的邏輯名稱。也可以不帶參數(shù)，輸出當前數(shù)據(jù)庫的所有文件信息。

6、sp_helpfilegroup： 根據(jù)文件組名稱，查看文件組信息

sp_helpfilegroup 'PRIMARY'

參數(shù)名中是文件組的邏輯名稱，當然也可以不帶參數(shù)，這樣就僅僅輸出當前數(shù)據(jù)庫的文件組信息。

顯示結(jié)果如下：

7、sp_helptext：顯示默認值、未加密的 Transact-SQL 存儲過程、用戶定義 Transact-SQL 函數(shù)、觸發(fā)器、計算列、CHECK 約束燈等的定義。

sp_helptext spName

返回的是什么？就是定義的代碼。

到此這篇關(guān)于Sql Server存儲過程的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評論