Sql?Server存儲過程實例分析

今天小編給大家分享一下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存儲過程實例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。