The first time that a stored procedure is accessed, SQL Server generates compile and execution plans that are stored in the query cache and reused for subsequent executions. Therefore, you can receive a slight performance benefit when using a stored procedure by avoiding the need to parse, compile, and generate a query plan on subsequent executions of a stored procedure.
簡單範例
建立預存程序
CREATE PROCEDURE uspEmp
@EmpNo int,
@DepNo int
AS
SELECT EmpNo,EmpName,E.DepNo,DepName
FROM Emp E
INNER JOIN Dep D On E.DepNo=D.DepNo
執行預存程序
Exec uspEmp 1,1
刪除預存程序
DROP PROCEDURE uspEmp
修改預存程序
ALTER PROCEDURE uspEmp
@EmpNo int,
@DepNo int
AS
SELECT EmpNo,EmpName,E.DepNo,DepName
FROM Emp E
INNER JOIN Dep D On E.DepNo=D.DepNo
查看預存程序內容
EXEC sp_helptext uspEmp;
檢測預存程序是否存在
IF OBJECT_ID('dbo.uspAddEmp', 'P') IS NOT NULL
--DROP PROC dbo.uspAddEmp;
PRINT 'EXIST'
BEGIN/END
BEGIN/END 這二個關鍵字用來包住程式碼的區塊，可用可不用，只是讓程式碼看起來更明確。
SET NOCOUNT ON/OFF
當執行預存程序結束時，預設都會回傳受影響的資料列數，你可以使用 SET NOCOUNT OFF 關閉這個功能。ON 則啟用這個功能。
Variables, Parameters, and Return Codes
使用 CREATE PROCEDURE 陳述式建立預存程序，基本語法定義如下：
CREATE PROCEDURE <procedure name> [參數列] [WITH ENCRYPTION | RECOMPILE | EXECUTE AS Clause] AS 程式碼區塊
Variables
SQL Server has two types of variables: local and global . A local variable is designated by a single at sign (@) while a global variable is designated by a double at sign (@@). In addition, you can create, read, and write local variables, but you can only read the values from global variables.
Local Variables
宣告
要定義變數必須使用 DECLARE 子句，變數之間可以用逗號間隔。 但是如果為 TABLE 型別，必須獨立定義。
DECLARE @intvariable INT, @datevariable DATE,
@spatialvar GEOGRAPHY,
@levelvar HIERARCHYID
DECLARE @tablevar TABLE
(
ID INT NOT NULL,
Customer VARCHAR(50) NOT NULL
)
配置
要 assign 值給變數，可以使用 SET 或 SELECT 子句。 但是，若要由查詢指令中取得，就只能使用 SELECT 子句。
declare @EmpName nvarchar(50) select @EmpName = 'abc' set @EmpName = '123' select @EmpName=EmpName FROM Emp WHERE EmpNo=1 --若回傳多列，變數值會是最後一筆的值
針對四則運算式，SQL2008 增加了以下方法：
declare @int int set @int=0 set @int += 1 set @int *= 2 set @int /= 2 declare @str varchar(100) ='' set @str += 'abc' set @str += '123'
Global Variables
- @@ERROR ：Error code from the last statement executed
- @@IDENTITY ：Value of the last identity value inserted within the connection
- @@ROWCOUNT ：The number of rows affected by the last statement
- @@TRANCOUNT ：The number of open transactions within the connection
- @@VERSION ：The version of SQL Server
@@IDENTITY
這個變數要小心取用，它是全域變數，所以任何 Insert 行為都有可能影響它的值。 例如，若某個 SP 會新增一筆 TableA 的資料，如果 TableA 的 trigger 還會去新增 TableB, TableC ， 而且這三個資料表都包含識別欄位，此時如果在 SP 使用 @@IDENTITY 取得的值，將會是最後一次 Insert 指令返回的識別碼。 若僅想要取得該 SP 範圍中的變數值，可以改用 SCOPE_IDENTITY() 函式。
CREATE PROCEDURE [dbo].[uspAddEmp]
@EmpName nvarchar(50)
AS
INSERT Emp(EmpName, DepNo) Values (@EmpName, 1)
--SELECT @@IDENTITY;
SELECT SCOPE_IDENTITY();
其實取得插入的識別值共有三個方法，每個函數定義的範圍(Scope)各有不同：
- IDENT_CURRENT ：會傳回在任何工作階段和任何範圍中，產生給特定資料表 (叫用函數時指定) 的最後一個識別值。
- SCOPE_IDENTITY ：會傳回在目前工作階段以及目前範圍中，任何資料表產生的最後一個識別值。
- @@IDENTITY ：會傳回所有範圍的目前工作階段中，任何資料表所產生的最後一個識別值。
Parameters
Stored Procedure 的參數都是 Local 變數，依性質可分成二類： INPUT 和 OUTPUT 。 OUTPUT 參數是用來回傳一個值給應用程式時使用。
CREATE PROCEDURE [dbo].[uspAddEmp2]
@EmpName nvarchar(50),
@EmpNo int OUTPUT
AS
INSERT Emp(EmpName, DepNo) Values (@EmpName, 1)
SELECT @EmpNo = @@IDENTITY;
你也可以使用帶預設值的參數，若叫用時該參數沒有傳入值就會使用預設值。
CREATE PROCEDURE [dbo].[uspAddEmp3]
@EmpName nvarchar(50) ,
@DepNo int = 1
AS
INSERT Emp(EmpName, DepNo) Values (@EmpName, @DepNo)
SELECT @@IDENTITY;
Return Codes
要回傳資料給叫用端，有幾下幾種方式：
CREATE PROCEDURE [dbo].[uspEmp]
@EmpNo int
AS
SELECT *
FROM Emp E INNER JOIN Dep D On E.DepNo=D.DepNo
WHERE E.EmpNo= @EmpNo
CREATE PROCEDURE [dbo].[uspAddEmp_O]
@EmpName nvarchar(50),
@EmpNo int OUTPUT
AS
INSERT Emp(EmpName, DepNo) Values (@EmpName, 1)
SET @EmpNo = @@IDENTITY;
--叫用時
declare @No int
exec uspAddEmp_O 'vito', @No out
print @No
CREATE PROCEDURE uspAddEmp4
@EmpName nvarchar(50)
AS
INSERT Emp(EmpName, DepNo) Values (@EmpName, 1)
RETURN @@IDENTITY; --return 只能回傳整數型別，不能回傳其他型別。
--叫用時
DECLARE @No int
EXEC @No = uspAddEmp4 'vito'
PRINT @No
若由 ADO.NET 叫用預存程序，相關的配套方法可參考保哥的文章。
預存程序群組
CREATE PROCEDURE sp_GetUser;1 AS SELECT UserId,UserName,AliasName FROM tblUser WHERE Sex='0001' CREATE PROCEDURE sp_GetUser;2 AS SELECT UserId,UserName,AliasName FROM tblUser WHERE Sex='0002' GO EXEC sp_GetUser;1 EXEC sp_GetUser;2
Control Flow Constructs
在預存程序中，有以下的流程語法可以使用：
- IF ... ELSE
- BEGIN. . .END
- WHILE
- BREAK / CONTINUE
- WAITFOR
- GOTO
- RETURN
IF ... ELSE
IF (@EmpName='VITO') INSERT Emp(EmpName, DepNo) Values (@EmpName, 1) ELSE INSERT Emp(EmpName, DepNo) Values (@EmpName, 2) IF (@EmpName='VITO') BEGIN INSERT Emp(EmpName, DepNo) Values (@EmpName, 1) END ELSE BEGIN INSERT Emp(EmpName, DepNo) Values (@EmpName, 1) END
WHILE
DECLARE @var1 int = 1 WHILE( @var1<5 ) BEGIN PRINT @var1 SET @var1 += 1 END
BREAK 與 CONTINUE
DECLARE @var1 int = 0 WHILE( @var1<4 ) BEGIN SET @var1 += 1 PRINT 'loop:top ' + cast(@var1 as char(1)) if @var1=3 CONTINUE; PRINT 'loop:bottum ' + cast(@var1 as char(1)) END --loop:top 1 --loop:bottum 1 --loop:top 2 --loop:bottum 2 --loop:top 3 --loop:top 4 --loop:bottum 4
WAITFOR
WAITFOR 用來暫停執行，有以下三種使用方法：
WAITFOR DELAY
WAITFOR DELAY 用來延遲一段時間再繼續執行
DECLARE @var1 int = 0 WHILE( @var1<3 ) BEGIN SET @var1 += 1 PRINT @var1 WAITFOR DELAY '00:00:05'; --暫停5秒 PRINT SYSDATETIME() END --1 --2013-05-15 11:39:41.4185893 --2 --2013-05-15 11:39:46.4261981 --3 --2013-05-15 11:39:51.43380
WAITFOR TIME
WAITFOR DELAY 用來暫停執行，直到指定的時間到了才繼續執行
PRINT SYSDATETIME() WAITFOR TIME '11:48:30'; --等到 11:48:30 才執行 PRINT SYSDATETIME() --2013-05-15 11:48:19.2525309 --2013-05-15 11:48:30.0321498
WAITFOR RECEIVE
含 receive_statement 的 WAITFOR 只適用於 Service Broker 訊息。如需詳細資訊，請參閱＜RECEIVE (Transact-SQL)＞
RETURN
RETURN 可用來停止預存程式，並返回一個整數型別的狀態值給叫用端的程式。
CREATE PROCEDURE [dbo].[uspCheckEmp] AS DECLARE @Num INT; SELECT @Num = COUNT(*) FROM Emp IF (@Num % 2) = 0 RETURN '0' ELSE RETURN '1' -- 叫用 DECLARE @result char(1) EXEC @result = uspCheckEmp PRINT @result
GOTO
Executing Stored Procedures
使用 Input 參數
執行預儲程序有以下幾個方法：
--無參數 EXEC <stored procedure> --按位置順序 EXEC <stored procedure> @parm1=<value>, @parm2=<value>,... --按參數名稱 EXEC <stored procedure> <value>, <value>,...
按位置順序較簡單；按參數名稱可避免預存程序的參數因變換順序產生的錯誤。
按位置順序的叫用，預存程序中含預設值的參數必須放在最後面；按參數名稱就沒這個限制。
沒有預設值的參數都必須要傳入。
使用 Output 參數
建立預存程序時，參數後面必須使用 OUTPUT 關鍵字
CREATE PROCEDURE [dbo].[uspAddEmp_O]
@EmpName nvarchar(50),
@EmpNo int OUTPUT
AS
INSERT Emp(EmpName, DepNo) Values (@EmpName, 1)
SET @EmpNo = @@IDENTITY;
叫用預存程序時，參數值後面也必須使用 OUTPUT 關鍵字
declare @No int exec uspAddEmp_O 'vito', @No OUTPUT print @No
PS. 此處的 OUTPUT 可以使用縮寫 OUT 。
Dynamic Execution
有時候陳述式必須到了執行階段才有辨法組成，這時候就必須採用動態執行的技巧。
執行語法：
EXEC(<command>) --或 sp_executesql <command>
範例：
EXEC('SELECT OrderID, CustomerID FROM Sales.SalesOrderHeader WHERE OrderID = 1')
GO
DECLARE @var VARCHAR(MAX)
SET @var = 'SELECT OrderID, CustomerID FROM Sales.SalesOrderHeader WHERE OrderID = 1'
EXEC(@var)
模組執行內容（Modules Execution Context）
「模組」（modules）是指 function, procedures, queues, trigger 這些功能。 「執行內容」則是指目前的工作階段（session），它是由一對安全性 Token 來表示：Login Token 和 User Token。 若一個 Login Token 有多個資料庫存取權，則這個 Login Token 會有多個 User Token 。
自訂模組的執行內容
EXECUTE AS 子句可用來設定下列模組的執行內容：函數、程序、佇列和觸發程序。
CREATE PROCEDURE [ProcA]([參數列])
WITH EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }
AS
BEGIN
.....
END
- CALLER：指定模組內的陳述式，以模組的呼叫者為執行內容。
- SELF：指定模組內的陳述式，以模組的建立者為執行內容。
- OWNER：指定模組內的陳述式，以模組的擁有者為執行內容。
- user_name：指定模組內的陳述式，以指定的使用者為執行內容。
在舊版的 SQL Server 中，在這些模組中一律只能在模組的呼叫者的內容中執行。 例如，若 User1 對預存程序 procedureA 有執行權，在過去，在這個預存程序中的指令就不能超越 User1 的權限，否則就會發生錯誤。
從 SQL 2005 開始，透過 EXECUTE AS 指定執行模組，執行內容可以從模組的呼叫者切換到模組的擁有者，或切換到指定的使用者。 例如，上例中，若在 procedureA 執行了某個動態檢視查詢(DMV)，雖然 User1 沒有這個權限，但是 User2 有，那麼就可以使用 EXECUTE AS 來切換到 User2 。
您可以指定執行模組的內容，來控制 Database Engine 是使用哪一個使用者帳戶，以驗證該模組參考之物件的權限。 此舉可以在使用者自訂模組以及其所參考物件之間的物件鏈結管理權限時，提供更大的彈性和控制權。 您只需要授予模組本身的權限給使用者即可，不必授予使用者參考物件的明確權限。 只有執行模組的使用者，必須具有模組所存取之物件的權限。
模擬其他執行內容
當使用者（user）或登入者（login）連接到工作階段（session）時，SQL Server 會依據這個資料建立執行內容（Execution Context），直到使用者登出時結束。 工作內容期間的所有作業都是依據該使用者或登入者的權限檢查而定。
你可以使用 EXECUTE AS 來模擬不同工作階段的執行內容。（PS.這裡的EXECUTE AS陳述式和上面的EXECUTE AS子句是不同的。）
{ EXEC | EXECUTE } AS <context_specification>
<context_specification>::=
{ LOGIN | USER } = 'name' [ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ]
| CALLER
- LOGIN ：以特定的登入（Login）來模擬工作內容。
- USER ：以特定的資料庫使用者（USER）來模擬工作內容。
- CALLER ：用於模組內部時，指定模組內部的陳述式是在模組呼叫者的內容中執行。 。
- NO REVERT ：設定工作內容切換後不能還原回先前的工作內容。
- COOKIE INTO @var ：設定工作內容切換後，若要還原，只能以特定的工作內容還原
備註
執行內容中的變更會一直持續有效，直到發生下列項目之一為止：
- 執行另一個 EXECUTE AS 陳述式。
- 執行 REVERT 陳述式。
- 卸除工作階段。(session is dropped)
權限
若要執行 EXECUTE AS 命令，則呼叫者本身要具有 IMPERSONATE 權限。
範例一：
下面這個簡單例子便是使用 EXECUTE AS 命令來切換到不同的使用者內容。
--檢查目前的工作內容 SELECT SUSER_NAME(), USER_NAME(); --sa DELETE Emp WHERE EmpNo=80; --sa的權限可以刪除成功 --切換成 vxxxxxxo 工作內容 EXECUTE AS LOGIN = 'vxxxxxxo'; DELETE Emp WHERE EmpNo=81 --由於 vxxxxxxo 對物件 'Emp' 沒有 DELETE 權限，所以失敗。 --還原工作內容 REVERT; DELETE Emp WHERE EmpNo=82 --刪除成功
範例二：
若有個預存程序 ProcA ，程式裡面包含了叫用 sys.sql_logins 系統檢視表，而 sys.sql_logins 檢視表必須具有 VIEW SERVER STATE 權限才可使用。 若 UserA 只有 ProcA 執行權，沒有 VIEW SERVER STATE 權限，那麼該如何在不變更 UserA 權限下，讓他可以成功執行 ProcA 。
以下二種方法都可以用來解決這個狀況：
方法一：
- 將 ProcA 加入 EXECUTE AS OWNER 陳述式：
- 變更 ProcA 的 OWNER 為 UserB （UserB 必須有 VIEW SERVER STATE 權限）
ALTER PROCEDURE ProcA WITH EXECUTE AS Owner --以模組的Owner的工作內容執行 AS BEGIN ...... END
方法二：
- 找一個(或新增一個)有 VIEW SERVER STATE 權限的使用者 UserB
- 在 ProcA 中加入 EXECUTE AS UserB
ALTER PROCEDURE ProcA
AS
BEGIN
EXECUTE AS UserB --模擬 UserB 的工作內容
......
REVERT; --還原工作內容
END
在方法一中，因為 ProcA 的 OWNER 有 VIEW SERVER STATE 權限，所以在 ProcA 中透過 EXECUTE AS OWNER 指定模組執行時使用模組的 OWNER 執行內容。
在方法二中，因為 UserB 有 VIEW SERVER STATE 權限，所以在 ProcA 中使用 EXECUTE AS UserB 模擬 UserB 身份執行。
RECOMPILE
當 Stores Procedure 首次被執行時，SQL Server 會先進行編譯，並建立最佳化的執行計畫，同時將編譯過的命令存放在查詢快取(query cache)。 等下次執行時，就直接執行快取中的程式碼，以提升效率。
可是如果與該查詢的相關資料結構有被調整過，之前編譯過的查詢就不一定是最適合的，所以必須重新編譯。 要命令 Stores Procedure 進行重新編譯有以下三種方式。
在預儲程序中使用 WITH RECOMPILE
這個方法，預儲程序在每次執行時都會重新編譯，與直接執行 TSQL 沒有差別，無法提升執行效率。
CREATE PROCEDURE uspAddEmp_RECOMPILE @Name varchar(30)
WITH RECOMPILE
AS
...
執行預儲程序時使用 WITH RECOMPILE
這個方法只會在該次執行預存程序時進行重新編譯
exec uspAddEmp_RECOMPILE 'vito2' WITH RECOMPILE
使用 sp_recompile 指令
這個方法使用指令直接對預存程序進行重新編譯
sp_recompile uspAddEmp_RECOMPILE
Encryption
WITH ENCRYPTION 是用來加密物件用的選項，可以針對 Stored Procedure, Function, Trigger, View 等物件。 若建立預存程序時使用了 WITH ENCRYPTION 選項，則 SQL Server 會將預存程序的原始文字轉換為混亂格式(obfuscates)。 而加密後的 SP 雖然可以正常執行，但是無法被修改，也無法被檢視，既使以 SA 的權限也不行。
要反解這個加密選項，若不想刪除重建，就必須使用專用管理員連接（Dedicated admin connection，DAC），並執行特定的指令。 網路上可以找到 sp__windbi$decrypt 這個前人寫好的 SP ，就是用來清除加密的功能。 另外也有現成的 SQL Decryptor 解密軟體可用，就請自訂 Google 一下。
若使用 DAC 有問題產生，可參考以下幾篇文章
了解參數探測行為
前面提過，使用 SP 時，SQL Server會 reuse 查詢計畫，以提升執行效率。 但是，在某些情況下，reuse 查詢計畫，有時反而會來一些效能低落的問題。 底下說明就是要介紹這些狀況。
什麼是參數探測行為
最佳化工具會在重新啟動 MicrosoftSQL Server 後、首次執行預存程序時自動發生， 這時會將有使用的參數值一同包含在查詢計畫的一部份中， 這樣的作法對大多情況下來說是比較好的，只要SP後續執行都是使用常值， 那基本上都能夠重用執行計畫，但有可能會誤用執行計畫(造成查詢效能成本過高)。 換句話說，假設第一次返回資料量相當龐大(已快取執行計畫)， 執行第二次返回資料量很小但查詢成本(效能)反而不如預期(因為使用了第一次執行計畫)， 這樣就是標準的參數探測行為。
範例說明
底下為測試的環境：
--create Stored Procedure CREATE procedure [dbo].[uspGetUser](@Sex varchar(10)) as SELECT UserID,UserName,Sex,AliasName FROM tblUser WHERE Sex=@Sex --create index CREATE NONCLUSTERED INDEX [IX_tblUser_Sex] ON [dbo].[tblUser] ( [Sex] ASC )
資料筆數
第一次執行SP
第一次執行SP時，SQL Server 會為SP建立執行計畫快取
實際資料列數目：2712
估計資料列數目：2712
查詢成本：8.83809
第二次執行SP
第二次執行時，由於該SP執行計畫已經快取，所以參數探測行為會直接採用快取中的執行計畫。
實際資料列數目：102
估計資料列數目：2712
查詢成本：8.83809
第三次執行SP
第二次執行因為參數探測行為導致誤用了執行計畫(因為快取了第一次執行計畫)，所以本次執行前，先刪除執行計畫快取才再次執行SP。
實際資料列數目：102
估計資料列數目：102
查詢成本：0.335455
如果要避免參數探測行為，請參考了解參數探測行為(2)
沒有留言:
張貼留言