存儲過程優(yōu)點

運行T-SQL語句進行編程有兩種辦法道逗，一種是把T-SQL語句全部寫在應用程序中，并存儲在本地；另一種是把部分T-SQL語句編寫的程序作為存儲過程存儲在SQL Server中，只有本地的應用程序調用存儲過程树叽。大多數程序員偏向使用后者，原因在于存儲過程具有以下優(yōu)點：

• 一次編譯曼追，多次執(zhí)行玄糟。第一次執(zhí)行某個過程時，將編譯該過程以確定檢索數據的最優(yōu)訪問計劃。 如果已經生成的計劃仍保留在數據庫引擎計劃緩存中，則該過程隨后執(zhí)行的操作可能重新使用該計劃。
• 可在應用程序中多次調用预侯；修改存儲過程不會影響應用程序源代碼。
• 存儲過程存儲在服務中峰锁，能夠減少網絡流量萎馅。比如一個需要數百行T-SQL代碼的操作可以通過一條執(zhí)行存儲過程代碼的語句來調用，而不需要在網絡中發(fā)送數百行代碼虹蒋。
• 存儲過程可被作為一種安全機制來充分利用糜芳。可以只授予用戶執(zhí)行存儲過程的權限魄衅，而不授予用戶直接訪問存儲過程中涉及的表的權限峭竣。這樣，用戶只能通過存儲過程來訪問表晃虫，并進行有限的操作皆撩，從而保證了表中數據的安全。使用授權操作設置各個用戶的權限哲银。

存儲過程分類

(1)系統(tǒng)存儲過程
??SQL Server提供的存儲過程扛吞，用于執(zhí)行與系統(tǒng)相關的任務，主要存儲在master數據庫并以sp_為前綴荆责，例如sp_addtype滥比、sp_rename等。

(2)擴展存儲過程
??擴展存儲過程是以在SQL Server環(huán)境之外執(zhí)行的動態(tài)鏈接庫(Dymatic-Link)Libraries,DDL)來實現的做院，執(zhí)行系統(tǒng)存儲過程不能勝任的任務盲泛，如發(fā)郵件、文件處理等键耕，通常以前綴xp_開頭寺滚。執(zhí)行擴展存儲過程的方式與存儲過程的相似。

(3)臨時存儲過程
??臨時存儲過程首先是本地存儲過程屈雄。SQL Server支持兩種臨時存儲過程：局部臨時過程和全局臨時過程玛迄。
??如果存儲過程的前面有一個符號“#”，那么它就是局部臨時過程棚亩，只能在一個用戶會話中使用蓖议，在當前會話結束時就會被除去。
??如果存儲過程的前面有兩個符號“##”讥蟆，那么把該存儲過程稱為全局臨時存儲過程勒虾，可以在所有用戶會話中使用，在使用該過程的最后一個會話結束時除去瘸彤。

(4)用戶定義的存儲過程
??用戶自定義的存儲過程由用戶創(chuàng)建的一組T-SQL語句集合組成修然，可以接收和返回用戶提供的參數，完成某些特定功能质况。
??存儲過程創(chuàng)建好且語法正確后愕宋，系統(tǒng)將存儲過程的名稱存儲在當前數據庫的系統(tǒng)表sysobject中；將存儲過程的文本存儲在當前數據庫的系統(tǒng)表syscomments中结榄。

創(chuàng)建存儲過程

存儲過程語法如下：

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：該過程所屬的架構的名稱中贝。如果在創(chuàng)建過程時未指定架構名稱，則自動分配正在創(chuàng)建過程的用戶的默認架構臼朗。
• 可以通過使用一個#符號在procedure_name之前創(chuàng)建本地臨時過程(#procedure_name)或兩個#符號創(chuàng)建全局臨時過程(## procedure_name) 邻寿。局部臨時程序僅對創(chuàng)建了它的連接可見，并且在關閉該連接后將被刪除视哑。 全局臨時程序可用于所有連接绣否，并且在使用該過程的最后一個會話結束時將被刪除。
• @parameter：指定過程中的參數挡毅，是局部的蒜撮，可以聲明一個或多個。
• 如果指定了FOR REPLICATION跪呈，則無法聲明參數段磨。
• parameter可以是輸入參數or輸出參數，若為輸入參數IN可以不寫庆械，系統(tǒng)默認薇溃；若為輸出參數則要加上OUTPUT。
• 表值參數只能是 INPUT 參數缭乘，并且這些參數必須帶有 READONLY 關鍵字沐序。
• 光標數據類型只能是輸出參數和必須附帶由 VARYING 關鍵字。
• OUT | OUTPUT指示參數是輸出參數堕绩，使用 OUTPUT 參數將值返回給過程的調用方策幼。
• [ =default ]：參數的默認值。 如果默認定義值奴紧，該函數可以執(zhí)行而無需指定該參數的值特姐。
• WITH ENCRYPTION：SQL Server加密syscomments表中包含CREATE PROCEDURE語句文本的條目，即對用戶隱藏存儲過程的文本黍氮，不能從syscomments表中獲取該存儲過程的信息唐含。
• WITH RECOMPILE：指示數據庫引擎不緩存該過程的計劃浅浮，該過程將在每次運行時重新編譯。如果指定了FOR REPLICATION捷枯，則不能使用此選項滚秩。
• EXECUTE AS子句：指定在其中執(zhí)行過程的安全上下文。

關于參數

• 存儲過程參數也可以帶有默認值淮捆，如：

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

• 存儲過程參數可以帶有通配符郁油，如：

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

關于輸出
①OUTPUT參數
??如果在過程定義中為參數指定 OUTPUT 關鍵字，則存儲過程在退出時可將該參數的當前值返回至調用程序攀痊。若要用變量保存參數值以便在調用程序中使用桐腌，則調用程序必須在執(zhí)行存儲過程時使用 OUTPUT 關鍵字。
??也可以在執(zhí)行過程時為 OUTPUT 參數指定輸入值苟径。 這將允許過程從調用程序接收值案站，使用該值更改或執(zhí)行操作，然后將新值返回給調用程序涩笤。
②使用返回代碼返回數據
??過程可以返回一個整數值（稱為“返回代碼”）嚼吞，以指示過程的執(zhí)行狀態(tài)。 使用 RETURN 語句指定過程的返回代碼蹬碧。 與 OUTPUT 參數一樣舱禽，執(zhí)行過程時必須將返回代碼保存到變量中，才能在調用程序中使用返回代碼值恩沽。
??RETURN是從查詢或過程中無條件退出誊稚，不執(zhí)行位于 RETURN 之后的語句。RETURN返回的不能是空值罗心，如果過程試圖返回空值里伯，將生成警告信息并返回 0 值。用輸出參數OUTPUT可以輸出任意類型的結果（不包括表類型）渤闷，而RETURN只能返回整型并且總能返回一個整型值疾瓮。一般的RETURN用來返回返回代碼(如0表示執(zhí)行成功，1表示未指定所需參數值)飒箭。
??RETURN和OUTPUT還可以出現在同一存儲過程中狼电，詳見示例(3)。

局限與限制

①在單個批處理中弦蹂，CREATE PROCEDURE 語句不能與其他 Transact-SQL 語句組合使用肩碟。
②以下語句不能用于存儲過程主體中的任何地方。

③過程可以引用尚不存在的表凸椿。 在創(chuàng)建時削祈，只進行語法檢查。 直到第一次執(zhí)行該過程時才對其進行編譯。 只有在編譯過程中才解析過程中引用的所有對象髓抑。 因此咙崎，如果語法正確的過程引用了不存在的表，則仍可以成功創(chuàng)建启昧；但如果被引用的表不存在叙凡，則過程將在執(zhí)行時將失敗。
④不能將某一函數名稱指定為參數默認值或者在執(zhí)行過程時傳遞給參數的值密末。 但是，您可以將函數作為變量傳遞跛璧，如以下示例中所示：

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

⑤如果該過程對 SQL Server 的遠程實例進行更改严里，將無法回滾這些更改。 遠程過程不參與事務追城。

執(zhí)行存儲過程

調用存儲過程使用Execute|Exec關鍵字刹碾，不能省略。

Execute|Exec
{
  [@整形變量=]
  存儲過程名[,n]|@存儲過程變量名
  [[@過程參數=]參數值|@可變參數名 [OUTPUT]|[DEFAULT]]
  [,..,n]
  [WITH RECOMPILE]
}

• @整形變量：可選座柱，代表存儲過程的返回狀態(tài)迷帜。
• n：可選，用于對同名的過程分組色洞。
• @過程參數：為存儲過程的參數賦值戏锹。

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

• OUTPUT：指定該參數為輸出參數奈搜。
• DEFAULT：指明該參數使用默認值。如果該參數定義時沒有指定默認值盯荤，則不能使用DEFAULT選項馋吗。
• WITH RECOMPILE：強制在執(zhí)行存儲過程時重新對其進行編譯。

【示例】
(1)帶OUTPUT參數的存儲過程——最后的返回值存儲在調用程序聲明的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

調用過程如下：

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參數的存儲過程

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

(3)同時帶Return和output參數的存儲過程

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

調用過程如下：

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

存儲過程傳遞集合參數以及返回秋秤、接收結果集

（1）傳遞集合參數

A宏粤、傳遞多個形參

B、使用表值參數
??使用表值參數類型將多個行插入表中航缀。 一下示例將創(chuàng)建參數類型商架，聲明表變量來引用它，填充參數列表芥玉，然后將值傳遞給存儲過程蛇摸。 存儲過程使用這些值將多個行插入表中。

/* 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）返回結果集

A灿巧、使用 OUTPUT 游標參數
??以下示例使用 OUTPUT 游標參數將過程的局部游標傳遞回執(zhí)行調用的批處理赶袄、過程或觸發(fā)器揽涮。
??首先，創(chuàng)建在 Currency表上聲明并打開一個游標的過程：

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  

接下來饿肺，運行以下批處理：聲明一個局部游標變量蒋困，執(zhí)行上述過程以將游標賦值給局部變量，然后從該游標提取行敬辣。

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返回多個輸出參數
??這種方法缺點在于如果結果集中幾百個元素，那么在存儲過程就要聲明幾百個變量溉跃，十分麻煩村刨。

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

調用段：

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

C、SELECT返回結果集
??在存儲過程中寫一段返回一個結果集的SELECT語句撰茎，如果在調用段中僅僅EXEC procedure_name [parameter1...parametern]嵌牺，那么該SELECT語句的結果僅僅只會輸出到屏幕上，而不能用這個結果集做后續(xù)處理龄糊。如果要保存此結果集逆粹，只有一種方法，即通過使用 INSERT/EXEC 將其存儲到永久表炫惩、臨時表或表變量中僻弹，從而將結果流式處理到磁盤。

①把結果集存儲在臨時表
創(chuàng)建存儲過程：

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

調用段：

-- 創(chuàng)建一個臨時表诡必，和存儲過程的結果集結構一致
CREATE TABLE #t1
(
  id int,
  name varchar(50)
)

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

②把結果集存儲在表變量
??但這種方法在查詢的數據量較大的情況下比較影響性能奢方，查詢速度較慢，在數據量較小的情況下這種差異并不明顯爸舒。

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

管理存儲過程

①查看存儲過程信息

②修改存儲過程

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 ]

③刪除存儲過程

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