從存儲(chǔ)過程返回?cái)?shù)據(jù) - SQL Server | Microsoft 官方文檔?

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

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

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

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

在語法中，PROC是PROCEDURE的縮寫，兩個(gè)選項(xiàng)的意思一樣。在對(duì)存儲(chǔ)過程命名完之后，接著是參數(shù)列表。參數(shù)是可選的。關(guān)鍵字AS其后就是實(shí)際的代碼。

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>

簡單的存儲(chǔ)過程示例：

CREATE PROC spPerson
　　AS
    SELECT * FROM Person

執(zhí)行存儲(chǔ)過程：

EXEC spPerson

1、聲明參數(shù)

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

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

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

其語法如下所示:

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

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

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

執(zhí)行存儲(chǔ)過程：

EXEC spName '酒';

2、提供默認(rèn)值

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

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

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

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ù)

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

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

執(zhí)行存儲(chǔ)過程：

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

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

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

SQL Server默認(rèn)會(huì)在完成存儲(chǔ)過程時(shí)自動(dòng)返回一個(gè)0值。

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

RETURN []

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

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

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

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

　　SELECT @MyMessage = '第一個(gè)RETURN';
　　PRINT @MyMessage;    
　　RETURN 100;        --將這里改成返回100

　　SELECT @MyOtherMessage = '第二個(gè)RETURN';
　　PRINT @MyOtherMessage;
　　RETURN;

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

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

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

對(duì)于調(diào)用存儲(chǔ)過程需要注意以下幾點(diǎn)：

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

6、WITH RECOMPILE選項(xiàng)

可以利用存儲(chǔ)過程提供的安全性代碼和代碼封裝方面的好處，但還是忽略了預(yù)編譯代碼方面的影響。可以回避未使用正確的查詢計(jì)劃的問題，因?yàn)榭梢源_保為特定一次運(yùn)行創(chuàng)建新的計(jì)劃。方法就是使用WITH RECOMPILE選項(xiàng)。 
使用該選項(xiàng)的方式有兩種：

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

EXEC spMySproc '1/1/2004'
　　WITH RECOMPILE

2、也可以通過在存儲(chǔ)過程中包含WITH RECOMPILE選項(xiàng)來使之變得更持久。

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

二、修改存儲(chǔ)過程:ALTER PROC

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

三、刪除存儲(chǔ)過程:DROP PROC

DROP PROC|PROCEDURE <sproc name>[;]

四、常用存儲(chǔ)過程

1、sp_help： 查詢表的信息

sp_help Person

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

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

sp_helpdb TestDataCenter

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

這張圖幾乎包含了數(shù)據(jù)庫的所有信息了。有了這張圖，想了解一個(gè)數(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ù)，輸出當(dāng)前數(shù)據(jù)庫的所有文件信息。

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

sp_helpfilegroup 'PRIMARY'

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

顯示結(jié)果如下：

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

sp_helptext spName

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

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

最新評(píng)論