sql存儲過程實例詳解
存儲過程(Stored Procedure),是一組為了完成特定功能的SQL 語句，類似一門程序設(shè)計語言，也包括了數(shù)據(jù)類型、流程控制、輸入和輸出和它自己的函數(shù)庫。
存儲過程可以說是一個記錄集，它是由一些T-SQL語句組成的代碼塊，這些T-SQL語句代碼像一個方法一樣實現(xiàn)一些功能（對單表或多表的增刪改查），然后再給這個代碼塊取一個名字，在用到這個功能的時候調(diào)用他就行了。不過SQL存儲過程對于一些初學(xué)者來說還是比較抽象難理解的，因此本文將由淺至深地剖析SQL存儲過程，幫助你學(xué)習(xí)它。
推薦：《SQL視頻教程》
存儲過程的優(yōu)點
1.存儲過程只在創(chuàng)造時進行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度，效率要比T-SQL語句高。
2.當(dāng)對數(shù)據(jù)庫進行復(fù)雜操作時，可將此復(fù)雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。
3.一個存儲過程在程序在網(wǎng)絡(luò)中交互時可以替代大堆的T-SQL語句，所以也能降低網(wǎng)絡(luò)的通信量，提高通信速率。
4.存儲過程可以重復(fù)使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量。
5.安全性高,可設(shè)定只有某些用戶才具有對指定存儲過程的使用權(quán)?
存儲過程基本語法
--------------創(chuàng)建存儲過程----------------- CREATE?PROC?[?EDURE?]?procedure_name?[?;?number?] ????[?{?@parameter?data_type?} ????????[?VARYING?]?[?=?default?]?[?OUTPUT?] ????]?[?,...n?] [?WITH ????{?RECOMPILE?|?ENCRYPTION?|?RECOMPILE?,?ENCRYPTION?}?] [?FOR?REPLICATION?] AS?sql_statement?[?...n?] --------------調(diào)用存儲過程----------------- EXECUTE?Procedure_name?''?--存儲過程如果有參數(shù)，后面加參數(shù)格式為：@參數(shù)名=value，也可直接為參數(shù)值value --------------刪除存儲過程----------------- drop?procedure?procedure_name????--在存儲過程中能調(diào)用另外一個存儲過程，而不能刪除另外一個存儲過程
創(chuàng)建存儲過程的參數(shù)
● procedure_name ：存儲過程的名稱，在前面加#為局部臨時存儲過程，加##為全局臨時存儲過程。?
●?number：是可選的整數(shù)，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應(yīng)用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標(biāo)識符，則數(shù)字不應(yīng)包含在標(biāo)識符中，只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)亩ń绶??
●?@parameter：存儲過程的參數(shù)。可以有一個或多個。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲過程最多可以有 2100 個參數(shù)。
●?使用 @ 符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。有關(guān)更多信息，請參見 EXECUTE。?
●?data_type：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括 text、ntext 和 image）均可以用作存儲過程的參數(shù)。不過，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定的數(shù)據(jù)類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關(guān)鍵字。有關(guān) SQL Server 提供的數(shù)據(jù)類型及其語法的更多信息，請參見數(shù)據(jù)類型。
說明對于可以是 cursor 數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。?
●?VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。??
●?default： 參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或 NULL。如果過程將對該參數(shù)使用 LIKE 關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、[] 和 [^]）。?
●?OUTPUT：表明參數(shù)是返回參數(shù)。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。??
●?RECOMPILE: 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，請使用 RECOMPILE 選項。?
●?ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復(fù)制的一部分發(fā)布。 說明在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創(chuàng)建加密過程。??
●?FOR REPLICATION:指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。.使用 FOR REPLICATION 選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復(fù)制過程中執(zhí)行。本選項不能和 WITH RECOMPILE 選項一起使用。??
●?AS:指定過程要執(zhí)行的操作。?
●?sql_statement:過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。但有一些限制。?
實例操作學(xué)習(xí)
下面通過表Student來具體了解一下存儲過程，因為是要了解存儲過程的簡單用法，所以例子很簡單。?
無參數(shù)存儲過程
選出Student表中的所有信息?
create?proc?StuProc as??????//此處?as?不可以省略不寫 begin???//begin?和?end?是一對，不可以只寫其中一個，但可以都不寫 select?S#,Sname,Sage,Ssex?from?student end go
有參數(shù)存儲過程
全局變量
全局變量也稱為外部變量，是在函數(shù)的外部定義的，它的作用域為從變量定義處開始，到本程序文件的末尾。
選出指定姓名的學(xué)生信息:?
create?proc?StuProc @sname?varchar(100)??? as? begin select?S#,Sname,Sage,Ssex?from?student?where?sname=@sname end go exec?StuProc?'趙雷'???//執(zhí)行語句
上面是在外部給變量賦值，也可以在內(nèi)部直接給變量設(shè)置默認(rèn)值?
create?proc?StuProc @sname?varchar(100)='趙雷' as? begin select?S#,Sname,Sage,Ssex?from?student?where?sname=@sname end go exec?StuProc
也可以把變量的內(nèi)容輸出，使用output?
create?proc?StuProc @sname?varchar(100), @IsRight?int??output?//傳出參數(shù) as? if?exists?(select?S#,Sname,Sage,Ssex?from?student?where?sname=@sname) set?@IsRight?=1 else set?@IsRight=0 go declare?@IsRight?int? exec?StuProc?'趙雷'?,?@IsRight?output select?@IsRight
以上是全局變量，下面來了解局部變量?
局部變量
局部變量也稱為內(nèi)部變量。局部變量是在函數(shù)內(nèi)作定義說明的。其作用域僅限于函數(shù)內(nèi)部，離開該函數(shù)后再使用這種變量是非法的。
局部變量的定義
必須先用Declare命令定以后才可以使用，declare{@變量名 數(shù)據(jù)類型}
局部變量的賦值方法
set{@變量名=表達式}或者select{@變量名=表達式}
局部變量的顯示
create?proc?StuProc as? declare?@sname?varchar(100) set?@sname='趙雷' select?S#,Sname,Sage,Ssex?from?student?where?sname=@sname go exec?StuProc
那如果是要把局部變量的數(shù)據(jù)顯示出來怎么辦呢？?
create?proc?StuProc as? declare?@sname?varchar(100) set?@sname=(select?Sname?from?student?where?S#=01) select?@sname go exec?StuProc
更詳細的實例操作學(xué)習(xí)
比如，在SQL Server查詢編輯器窗口中用CREATE PROCEDURE語句創(chuàng)建存儲過程PROC_InsertEmployee，用于實現(xiàn)向員工信息表（tb_Employee）中添加信息，同時生成自動編號。其SQL語句如下：?
IF?EXISTS?(SELECT?name?? ???FROM???sysobjects?? ???WHERE??name?=?'Proc_InsertEmployee'?? ???AND??????????type?=?'P')? DROP?PROCEDURE?Proc_InsertEmployee? GO? CREATE?PROCEDURE?Proc_InsertEmployee? @PName?nvarchar(50),? @PSex?nvarchar(4),? @PAge?int,? @PWage?money? AS? begin? ???declare?@PID?nvarchar(50)? ???select?@PID=Max(員工編號)?from?tb_Employee? ???if(@PID?is?null)? ???????set?@PID='P1001'? ???else? ???????set?@PID='P'+cast(cast(substring(@PID,2,4)?as?int)+1?as?nvarchar(50))? ???begin? ???????insert?into?tb_Employee?values(@PID,@PName,@PSex,@PAge,@PWage)? ???end? end? go
存儲過程的修改
創(chuàng)建完存儲過程之后，如果需要重新修改存儲過程的功能及參數(shù)，可以在SQL Server 2005中通過以下兩種方法進行修改：一種是用Microsoft SQL Server Mangement修改存儲過程；另外一種是用T-SQL語句修改存儲過程。?
使用Microsoft SQL Server Mangement修改存儲過程，步驟如下：?
（1）在SQL Server Management Studio的“對象資源管理器”中，選擇要修改存儲過程所在的數(shù)據(jù)庫（如：db_18），然后在該數(shù)據(jù)庫下，選擇“可編程性”。?
（2）打開“存儲過程”文件夾，右鍵單擊要修改的存儲過程（如：PROC_SEINFO），在彈出的快捷菜單中選擇“修改”命令，將會出現(xiàn)查詢編輯器窗口。用戶可以在此窗口中編輯T-SQL代碼，完成編輯后，單擊工具欄中的“執(zhí)行（X）”按鈕，執(zhí)行修改代碼。用戶可以在查詢編輯器下方的Message窗口中看到執(zhí)行結(jié)果信息。?
使用Transact-SQL修改存儲過程：?
使用ALTER PROCEDURE語句修改存儲過程，它不會影響存儲過程的權(quán)限設(shè)定，也不會更改存儲過程的名稱。?
語法：
ALTER?PROC?[?EDURE?]?procedure_name?[?;?number?]? ????[?{?@parameter?data_type?}?? ?????????[?VARYING?]?[?=?default?]?[?OUTPUT?]? ????]?[?,...n?]?? [?WITH? ????{?RECOMPILE?|?ENCRYPTION? ????????|?RECOMPILE?,?ENCRYPTION???}?? ]? [?FOR?REPLICATION?]?? AS? ????sql_statement?[?...n?]
參數(shù)說明
procedure_name：是要更改的存儲過程的名稱。
交叉鏈接：關(guān)于ALTER PROCEDURE語句的其他參數(shù)與CREATE PROCEDURE語句相同，可參見上面的“創(chuàng)建存儲過程的參數(shù)”。
例如，修改存儲過程PROC_SEINFO，用于查詢年齡大于35的員工信息。SQL語句如下：
ALTER?PROCEDURE?[dbo].[PROC_SEINFO]? AS? BEGIN? SELECT?*?FROM?tb_Employee?where?員工年齡>35? END
存儲過程的刪除
使用Microsoft SQL Server Mangement刪除存儲過程，步驟如下：
（1）在SQL Server Management Studio的“對象資源管理器”中，選擇要刪除存儲過程所在的數(shù)據(jù)庫（如：db_student），然后在該數(shù)據(jù)庫下選擇“可編程性”。??
（2）打開“存儲過程”文件夾，右鍵單擊要刪除的存儲過程（如：PROC_SEINFO），在彈出的快捷菜單中選擇“刪除”命令。?
（3）單擊“確定”按鈕，即可刪除所選定的存儲過程。??
注意：刪除數(shù)據(jù)表后，并不會刪除相關(guān)聯(lián)的存儲過程，只是其存儲過程無法執(zhí)行。
使用T-SQL刪除存儲過程：?
DROP PROCEDURE語句用于從當(dāng)前數(shù)據(jù)庫中刪除一個或多個存儲過程或過程組。?
語法：
DROP?PROCEDURE?{?procedure?}?[?,...n?]
參數(shù)說明：? ?
Procedure：是要刪除的存儲過程或存儲過程組的名稱。過程名稱必須符合標(biāo)識符規(guī)則。可以選擇是否指定過程所有者名稱，但不能指定服務(wù)器名稱和數(shù)據(jù)庫名稱。? ??
n：是表示可以指定多個過程的占位符。?
? ??
例如刪除PROC_SEINFO存儲過程的SQL語句如下。?
DROP?PROCEDURE?PROC_SEINFO
例如，刪除多個存儲過程proc10、proc20和proc30。?
DROP?PROCEDURE?proc10,?proc20,?proc30
例如，刪除存儲過程組procs（其中包含存儲過程proc1、proc2、proc3）。?
DROP?PROCEDURE?procs
注意：
SQL語句DROP不能刪除存儲過程組中的單個存儲過程。
應(yīng)用存儲過程驗證用戶登錄身份：
目前，驗證用戶登錄身份的方法有多種，而通過調(diào)用存儲過程來實現(xiàn)用戶身份驗證是目前最好的解決方案之一。因為存儲過程在創(chuàng)建時即在服務(wù)器上進行編譯，所以執(zhí)行起來比單個SQL語句要快得多。?
本例是通過調(diào)用存儲過程來驗證用戶登錄的用戶名和密碼是否正確。運行本實例，在“用戶名”和“密碼”文本框中輸入相應(yīng)的用戶名和密碼，單擊“登錄”按鈕即可。?
程序開發(fā)步驟：?
（1）新建一個網(wǎng)站，將其命名為”index”，默認(rèn)主頁名為Default.aspx。? ??
（2）Default.aspx頁面涉及到的控件如表1所示。?
（3）主要程序代碼如下。?
打開SQL Server Management Studio，并連接到SQL Server2005中的數(shù)據(jù)庫。單擊工具欄中“ ”按鈕，新建查詢編輯器。
在該查詢編輯器中，創(chuàng)建驗證登錄用戶身份的存儲過程PROC_EXISTS，具體的SQL語句如下：?
CREATE?PROC?PROC_EXISTS? (? @UserName?NVARCHAR(20),? @PassWord?NVARCHAR(20),? @ReturnValue?int?OUTPUT? )? AS? IF?EXISTS(select?*?from?tb_member?where?userName=@UserName?AND?passWord=@PassWord)? ???????set?@ReturnValue=?100? ELSE? ???????set?@ReturnValue=?-100? GO
在”登錄”按鈕的Click事件下，執(zhí)行驗證登錄用戶身份的存儲過程，如果輸入的用戶名和密碼正確，則彈出對話框提示用戶登錄成功，代碼如下：?
protected?void?btnLogin_Click(object?sender,?EventArgs?e)? ????{? ????????//連接數(shù)據(jù)庫? ????????myConn?=?new?SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());? ????????myCmd?=?new?SqlCommand("PROC_EXISTS",?myConn);???//調(diào)用存儲過程，判斷用戶是否存在 ????????myCmd.CommandType?=?CommandType.StoredProcedure;? ????????//為存儲過程的參數(shù)賦值? ????????SqlParameter?userName=new?SqlParameter("@UserName",?SqlDbType.NVarChar,?20);? ????????userName.Value=this.txtName.Text.Trim();? ????????myCmd.Parameters.Add(userName);? ????????SqlParameter?passWord=new?SqlParameter("@PassWord",?SqlDbType.NVarChar,?20);? ????????passWord.Value?=?this.txtPassword.Text.Trim();? ????????myCmd.Parameters.Add(passWord);? ????????//指出該參數(shù)是存儲過程的OUTPUT參數(shù)? ????????SqlParameter?ReturnValue?=?new?SqlParameter("@ReturnValue",SqlDbType.Int?,4);? ????????ReturnValue.Direction?=?ParameterDirection.Output;? ????????myCmd.Parameters.Add(ReturnValue);? ????????try? ????????{? ????????????myConn.Open();? ????????????myCmd.ExecuteNonQuery();? ????????????if?(int.Parse(ReturnValue.Value.ToString())?==?100)? ????????????{? ????????????????Response.Write("<script>alert('您是合法用戶，登錄成功！')</script>");? ????????????????return;? ????????????}? ????????????else? ????????????{? ????????????????Response.Write("<script>alert('您輸入的用戶名和密碼不正確，請重新輸入！')</script>");? ????????????????return;? ????????????}? ????????}? ????????catch(Exception?ex)? ????????{? ????????????Response.Write(ex.Message.ToString());? ????????}? ????????finally? ????????{? ????????????myConn.Close();? ????????????myConn.Dispose();? ????????????myCmd.Dispose();? ????????}}
