存儲(chǔ)過(guò)程優(yōu)點(diǎn)

運(yùn)行T-SQL語(yǔ)句進(jìn)行編程有兩種辦法，一種是把T-SQL語(yǔ)句全部寫(xiě)在應(yīng)用程序中，并存儲(chǔ)在本地；另一種是把部分T-SQL語(yǔ)句編寫(xiě)的程序作為存儲(chǔ)過(guò)程存儲(chǔ)在SQL Server中，只有本地的應(yīng)用程序調(diào)用存儲(chǔ)過(guò)程。大多數(shù)程序員偏向使用后者，原因在于存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)：

• 一次編譯，多次執(zhí)行。第一次執(zhí)行某個(gè)過(guò)程時(shí)，將編譯該過(guò)程以確定檢索數(shù)據(jù)的最優(yōu)訪問(wèn)計(jì)劃。 如果已經(jīng)生成的計(jì)劃仍保留在數(shù)據(jù)庫(kù)引擎計(jì)劃緩存中，則該過(guò)程隨后執(zhí)行的操作可能重新使用該計(jì)劃。
• 可在應(yīng)用程序中多次調(diào)用；修改存儲(chǔ)過(guò)程不會(huì)影響應(yīng)用程序源代碼。
• 存儲(chǔ)過(guò)程存儲(chǔ)在服務(wù)中，能夠減少網(wǎng)絡(luò)流量。比如一個(gè)需要數(shù)百行T-SQL代碼的操作可以通過(guò)一條執(zhí)行存儲(chǔ)過(guò)程代碼的語(yǔ)句來(lái)調(diào)用，而不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼。
• 存儲(chǔ)過(guò)程可被作為一種安全機(jī)制來(lái)充分利用?？梢灾皇谟栌脩魣?zhí)行存儲(chǔ)過(guò)程的權(quán)限，而不授予用戶直接訪問(wèn)存儲(chǔ)過(guò)程中涉及的表的權(quán)限。這樣，用戶只能通過(guò)存儲(chǔ)過(guò)程來(lái)訪問(wèn)表，并進(jìn)行有限的操作，從而保證了表中數(shù)據(jù)的安全。使用授權(quán)操作設(shè)置各個(gè)用戶的權(quán)限。

存儲(chǔ)過(guò)程分類

(1)系統(tǒng)存儲(chǔ)過(guò)程
??SQL Server提供的存儲(chǔ)過(guò)程，用于執(zhí)行與系統(tǒng)相關(guān)的任務(wù)，主要存儲(chǔ)在master數(shù)據(jù)庫(kù)并以sp_為前綴，例如sp_addtype、sp_rename等。

(2)擴(kuò)展存儲(chǔ)過(guò)程
??擴(kuò)展存儲(chǔ)過(guò)程是以在SQL Server環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(kù)(Dymatic-Link)Libraries,DDL)來(lái)實(shí)現(xiàn)的，執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程不能勝任的任務(wù)，如發(fā)郵件、文件處理等，通常以前綴xp_開(kāi)頭。執(zhí)行擴(kuò)展存儲(chǔ)過(guò)程的方式與存儲(chǔ)過(guò)程的相似。

(3)臨時(shí)存儲(chǔ)過(guò)程
??臨時(shí)存儲(chǔ)過(guò)程首先是本地存儲(chǔ)過(guò)程。SQL Server支持兩種臨時(shí)存儲(chǔ)過(guò)程：局部臨時(shí)過(guò)程和全局臨時(shí)過(guò)程。
??如果存儲(chǔ)過(guò)程的前面有一個(gè)符號(hào)“#”，那么它就是局部臨時(shí)過(guò)程，只能在一個(gè)用戶會(huì)話中使用，在當(dāng)前會(huì)話結(jié)束時(shí)就會(huì)被除去。
??如果存儲(chǔ)過(guò)程的前面有兩個(gè)符號(hào)“##”，那么把該存儲(chǔ)過(guò)程稱為全局臨時(shí)存儲(chǔ)過(guò)程，可以在所有用戶會(huì)話中使用，在使用該過(guò)程的最后一個(gè)會(huì)話結(jié)束時(shí)除去。

(4)用戶定義的存儲(chǔ)過(guò)程
??用戶自定義的存儲(chǔ)過(guò)程由用戶創(chuàng)建的一組T-SQL語(yǔ)句集合組成，可以接收和返回用戶提供的參數(shù)，完成某些特定功能。
??存儲(chǔ)過(guò)程創(chuàng)建好且語(yǔ)法正確后，系統(tǒng)將存儲(chǔ)過(guò)程的名稱存儲(chǔ)在當(dāng)前數(shù)據(jù)庫(kù)的系統(tǒng)表sysobject中；將存儲(chǔ)過(guò)程的文本存儲(chǔ)在當(dāng)前數(shù)據(jù)庫(kù)的系統(tǒng)表syscomments中。

創(chuàng)建存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程語(yǔ)法如下：

CREATE PROCEDURE|PROC [schema_name.] procedure_name
    -- Add the parameters for the stored procedure here
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
    [WITH <procedure_option> [ ,...n ]]
    [FOR REPLICATION]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]  

• schema_name：該過(guò)程所屬的架構(gòu)的名稱。如果在創(chuàng)建過(guò)程時(shí)未指定架構(gòu)名稱，則自動(dòng)分配正在創(chuàng)建過(guò)程的用戶的默認(rèn)架構(gòu)。
• 可以通過(guò)使用一個(gè)#符號(hào)在procedure_name之前創(chuàng)建本地臨時(shí)過(guò)程(#procedure_name)或兩個(gè)#符號(hào)創(chuàng)建全局臨時(shí)過(guò)程(## procedure_name) 。局部臨時(shí)程序僅對(duì)創(chuàng)建了它的連接可見(jiàn)，并且在關(guān)閉該連接后將被刪除。 全局臨時(shí)程序可用于所有連接，并且在使用該過(guò)程的最后一個(gè)會(huì)話結(jié)束時(shí)將被刪除。
• @parameter：指定過(guò)程中的參數(shù)，是局部的，可以聲明一個(gè)或多個(gè)。
• 如果指定了FOR REPLICATION，則無(wú)法聲明參數(shù)。
• parameter可以是輸入?yún)?shù)or輸出參數(shù)，若為輸入?yún)?shù)IN可以不寫(xiě)，系統(tǒng)默認(rèn)；若為輸出參數(shù)則要加上OUTPUT。
• 表值參數(shù)只能是 INPUT 參數(shù)，并且這些參數(shù)必須帶有 READONLY 關(guān)鍵字。
• 光標(biāo)數(shù)據(jù)類型只能是輸出參數(shù)和必須附帶由 VARYING 關(guān)鍵字。
• OUT | OUTPUT指示參數(shù)是輸出參數(shù)，使用 OUTPUT 參數(shù)將值返回給過(guò)程的調(diào)用方。
• [ =default ]：參數(shù)的默認(rèn)值。 如果默認(rèn)定義值，該函數(shù)可以執(zhí)行而無(wú)需指定該參數(shù)的值。
• WITH ENCRYPTION：SQL Server加密syscomments表中包含CREATE PROCEDURE語(yǔ)句文本的條目，即對(duì)用戶隱藏存儲(chǔ)過(guò)程的文本，不能從syscomments表中獲取該存儲(chǔ)過(guò)程的信息。
• WITH RECOMPILE：指示數(shù)據(jù)庫(kù)引擎不緩存該過(guò)程的計(jì)劃，該過(guò)程將在每次運(yùn)行時(shí)重新編譯。如果指定了FOR REPLICATION，則不能使用此選項(xiàng)。
• EXECUTE AS子句：指定在其中執(zhí)行過(guò)程的安全上下文。

關(guān)于參數(shù)

• 存儲(chǔ)過(guò)程參數(shù)也可以帶有默認(rèn)值，如：

create procedure pun_info @pubname varchar(20)='ALGOdata'

• 存儲(chǔ)過(guò)程參數(shù)可以帶有通配符，如：

create procedure pun_info 
   @name varchar(20)='D%'
as
  select name from authors where name like @name

關(guān)于輸出
①OUTPUT參數(shù)
??如果在過(guò)程定義中為參數(shù)指定 OUTPUT 關(guān)鍵字，則存儲(chǔ)過(guò)程在退出時(shí)可將該參數(shù)的當(dāng)前值返回至調(diào)用程序。若要用變量保存參數(shù)值以便在調(diào)用程序中使用，則調(diào)用程序必須在執(zhí)行存儲(chǔ)過(guò)程時(shí)使用 OUTPUT 關(guān)鍵字。
??也可以在執(zhí)行過(guò)程時(shí)為 OUTPUT 參數(shù)指定輸入值。 這將允許過(guò)程從調(diào)用程序接收值，使用該值更改或執(zhí)行操作，然后將新值返回給調(diào)用程序。
②使用返回代碼返回?cái)?shù)據(jù)
??過(guò)程可以返回一個(gè)整數(shù)值（稱為“返回代碼”），以指示過(guò)程的執(zhí)行狀態(tài)。 使用 RETURN 語(yǔ)句指定過(guò)程的返回代碼。 與 OUTPUT 參數(shù)一樣，執(zhí)行過(guò)程時(shí)必須將返回代碼保存到變量中，才能在調(diào)用程序中使用返回代碼值。
??RETURN是從查詢或過(guò)程中無(wú)條件退出，不執(zhí)行位于 RETURN 之后的語(yǔ)句。RETURN返回的不能是空值，如果過(guò)程試圖返回空值，將生成警告信息并返回 0 值。用輸出參數(shù)OUTPUT可以輸出任意類型的結(jié)果（不包括表類型），而RETURN只能返回整型并且總能返回一個(gè)整型值。一般的RETURN用來(lái)返回返回代碼(如0表示執(zhí)行成功，1表示未指定所需參數(shù)值)。
??RETURN和OUTPUT還可以出現(xiàn)在同一存儲(chǔ)過(guò)程中，詳見(jiàn)示例(3)。

局限與限制

①在單個(gè)批處理中，CREATE PROCEDURE 語(yǔ)句不能與其他 Transact-SQL 語(yǔ)句組合使用。
②以下語(yǔ)句不能用于存儲(chǔ)過(guò)程主體中的任何地方。

③過(guò)程可以引用尚不存在的表。 在創(chuàng)建時(shí)，只進(jìn)行語(yǔ)法檢查。 直到第一次執(zhí)行該過(guò)程時(shí)才對(duì)其進(jìn)行編譯。 只有在編譯過(guò)程中才解析過(guò)程中引用的所有對(duì)象。 因此，如果語(yǔ)法正確的過(guò)程引用了不存在的表，則仍可以成功創(chuàng)建；但如果被引用的表不存在，則過(guò)程將在執(zhí)行時(shí)將失敗。
④不能將某一函數(shù)名稱指定為參數(shù)默認(rèn)值或者在執(zhí)行過(guò)程時(shí)傳遞給參數(shù)的值。 但是，您可以將函數(shù)作為變量傳遞，如以下示例中所示：

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;   

⑤如果該過(guò)程對(duì) SQL Server 的遠(yuǎn)程實(shí)例進(jìn)行更改，將無(wú)法回滾這些更改。 遠(yuǎn)程過(guò)程不參與事務(wù)。

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

調(diào)用存儲(chǔ)過(guò)程使用Execute|Exec關(guān)鍵字，不能省略。

Execute|Exec
{
  [@整形變量=]
  存儲(chǔ)過(guò)程名[,n]|@存儲(chǔ)過(guò)程變量名
  [[@過(guò)程參數(shù)=]參數(shù)值|@可變參數(shù)名 [OUTPUT]|[DEFAULT]]
  [,..,n]
  [WITH RECOMPILE]
}

• @整形變量：可選，代表存儲(chǔ)過(guò)程的返回狀態(tài)。
• n：可選，用于對(duì)同名的過(guò)程分組。
• @過(guò)程參數(shù)：為存儲(chǔ)過(guò)程的參數(shù)賦值。

SQL Server提供了兩種傳遞參數(shù)的辦法：
(1)按位置傳遞參數(shù)，即傳遞的參數(shù)和定義時(shí)的參數(shù)順序一致，如：
execute au_info 'Dull','Ann'
(2)通過(guò)參數(shù)名傳遞，采用“參數(shù)=值”的形式，此時(shí)各個(gè)參數(shù)可以任意排序，如：
execute au_info @firstName='Dull',@lastName='Ann' 或
execute au_info @lastName='Ann',@firstName='Dull'

• OUTPUT：指定該參數(shù)為輸出參數(shù)。
• DEFAULT：指明該參數(shù)使用默認(rèn)值。如果該參數(shù)定義時(shí)沒(méi)有指定默認(rèn)值，則不能使用DEFAULT選項(xiàng)。
• WITH RECOMPILE：強(qiáng)制在執(zhí)行存儲(chǔ)過(guò)程時(shí)重新對(duì)其進(jìn)行編譯。

【示例】
(1)帶OUTPUT參數(shù)的存儲(chǔ)過(guò)程——最后的返回值存儲(chǔ)在調(diào)用程序聲明的OUTPUT變量中

create procedure Query_Relationer
   @QueryCID int,                   -- 輸入的形參
   @QueryRName varchar(20) OUTPUT   -- 輸出的形參
as
begin
  if exists(select rid from Customer where cid = @QueryCID)
    select @QueryRName = RName from Relationer
    where rid = (select rid from Customer where cid = @QueryCID and cStatus = 1)
  else
    set @QueryRName = '不存在'
end
go

調(diào)用過(guò)程如下：

declare @Relationer_name varchar(20),@Cust_ID int
execute Query_Relationer @Cust_ID=20103530,@Relationer_name OUTPUT
print '客戶ID為'+convert(char(8),@Cust_ID)+'的聯(lián)系人是：'+@Relationer_name

(2)帶Return參數(shù)的存儲(chǔ)過(guò)程

create proc up_user
as
delcare @age int
begin
  select @age=uage from user
  return @age
end

(3)同時(shí)帶Return和output參數(shù)的存儲(chǔ)過(guò)程

create proc up_user
@id int,
@name varchar(20) output
as
 declare @age int
 begin
  select @age=stuage,@name=stuname from stuinfo where uid=@id
  return @age
 end

調(diào)用過(guò)程如下：

declare @age int
declare @name varchar(20)
exec @age=up_user 2,@name output
-- 輸出age和name
select @age,@name

存儲(chǔ)過(guò)程傳遞集合參數(shù)以及返回、接收結(jié)果集

（1）傳遞集合參數(shù)

A、傳遞多個(gè)形參

B、使用表值參數(shù)
??使用表值參數(shù)類型將多個(gè)行插入表中。 一下示例將創(chuàng)建參數(shù)類型，聲明表變量來(lái)引用它，填充參數(shù)列表，然后將值傳遞給存儲(chǔ)過(guò)程。 存儲(chǔ)過(guò)程使用這些值將多個(gè)行插入表中。

/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  
  
/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO [AdventureWorks2012].[Production].[Location]  
           ([Name]  
           ,[CostRate]  
           ,[Availability]  
           ,[ModifiedDate])  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
GO  
  
/* Declare a variable that references the type. */  
DECLARE @LocationTVP   
AS LocationTableType;  
  
/* Add data to the table variable. */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT [Name], 0.00  
    FROM   
    [AdventureWorks2012].[Person].[StateProvince];  
  
/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  

（2）返回結(jié)果集

A、使用 OUTPUT 游標(biāo)參數(shù)
??以下示例使用 OUTPUT 游標(biāo)參數(shù)將過(guò)程的局部游標(biāo)傳遞回執(zhí)行調(diào)用的批處理、過(guò)程或觸發(fā)器。
??首先，創(chuàng)建在 Currency表上聲明并打開(kāi)一個(gè)游標(biāo)的過(guò)程：

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL  
    DROP PROCEDURE dbo.uspCurrencyCursor;  
GO  
CREATE PROCEDURE dbo.uspCurrencyCursor   
    @CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    SET NOCOUNT ON;  
    SET @CurrencyCursor = CURSOR  
    FORWARD_ONLY STATIC FOR  
      SELECT CurrencyCode, Name  
      FROM Sales.Currency;  
    OPEN @CurrencyCursor;  
GO  

接下來(lái)，運(yùn)行以下批處理：聲明一個(gè)局部游標(biāo)變量，執(zhí)行上述過(guò)程以將游標(biāo)賦值給局部變量，然后從該游標(biāo)提取行。

DECLARE @MyCursor CURSOR;  
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  
WHILE (@@FETCH_STATUS = 0)  
BEGIN;  
     FETCH NEXT FROM @MyCursor;  
END;  
CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO  

B、使用OUTPUT返回多個(gè)輸出參數(shù)
??這種方法缺點(diǎn)在于如果結(jié)果集中幾百個(gè)元素，那么在存儲(chǔ)過(guò)程就要聲明幾百個(gè)變量，十分麻煩。

CREATE PROCEDURE Student.singS
      @id int,
      @name varchar(20) OUTPUT,
      @age int OUTPUT
AS
   select name,age from Student where id=@id
GO

調(diào)用段：

DECLARE @name varchar(20),@age int
EXECUTE Student.singS 28, @name OUTPUT,@age OUTPUT
print '學(xué)生的姓名為：'+@name+',年齡為：'+@age

C、SELECT返回結(jié)果集
??在存儲(chǔ)過(guò)程中寫(xiě)一段返回一個(gè)結(jié)果集的SELECT語(yǔ)句，如果在調(diào)用段中僅僅EXEC procedure_name [parameter1...parametern]，那么該SELECT語(yǔ)句的結(jié)果僅僅只會(huì)輸出到屏幕上，而不能用這個(gè)結(jié)果集做后續(xù)處理。如果要保存此結(jié)果集，只有一種方法，即通過(guò)使用 INSERT/EXEC 將其存儲(chǔ)到永久表、臨時(shí)表或表變量中，從而將結(jié)果流式處理到磁盤(pán)。

①把結(jié)果集存儲(chǔ)在臨時(shí)表
創(chuàng)建存儲(chǔ)過(guò)程：

CREATE PROCEDURE Proc1
 @a varchar(50)
AS
 SELECT id,name FROM Table1 WHERE name=@a

調(diào)用段：

-- 創(chuàng)建一個(gè)臨時(shí)表，和存儲(chǔ)過(guò)程的結(jié)果集結(jié)構(gòu)一致
CREATE TABLE #t1
(
  id int,
  name varchar(50)
)

-- 把結(jié)果集插入臨時(shí)表中
INSERT INTO #t1 EXEC Proc1 'Ada'
-- do something with results
--用完之后要把臨時(shí)表清空
DROP TABLE #t1

②把結(jié)果集存儲(chǔ)在表變量
??但這種方法在查詢的數(shù)據(jù)量較大的情況下比較影響性能，查詢速度較慢，在數(shù)據(jù)量較小的情況下這種差異并不明顯。

create proc proc1 as
   select col1 from dbo.table1;

create proc proc2 as
   declare @t table(col1 int);
   insert @t (col1) exec proc1;
   -- do something with results

管理存儲(chǔ)過(guò)程

①查看存儲(chǔ)過(guò)程信息

②修改存儲(chǔ)過(guò)程

ALTER PROCEDURE|PROC [schema_name.] procedure_name
    -- Add the parameters for the stored procedure here
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
    [WITH <procedure_option> [ ,...n ]]
    [FOR REPLICATION]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]

③刪除存儲(chǔ)過(guò)程

DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]