全面解析SQL存儲過程

　存儲過程(Stored Procedure),是一組為了完成特定功能的SQL 語句，類似一門程序設(shè)計語言，也包括了數(shù)據(jù)類型、流程控制、輸入和輸出和它自己的函數(shù)庫。存儲過程可以說是一個記錄集，它是由一些T-SQL語句組成的代碼塊，這些T-SQL語句代碼像一個方法一樣實現(xiàn)一些功能（對單表或多表的增刪改查），然后再給這個代碼塊取一個名字，在用到這個功能的時候調(diào)用他就行了。不過SQL存儲過程對于一些初學者來說還是比較抽象難理解的，因此本文將由淺至深地剖析SQL存儲過程，幫助你學習它。

存儲過程的優(yōu)點

1. 存儲過程只在創(chuàng)造時進行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度，效率要比T-SQL語句高。
2. 當對數(shù)據(jù)庫進行復雜操作時，可將此復雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務處理結(jié)合一起使用。
3. 一個存儲過程在程序在網(wǎng)絡中交互時可以替代大堆的T-SQL語句，所以也能降低網(wǎng)絡的通信量，提高通信速率。
4. 存儲過程可以重復使用,可減少數(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ù)

1. procedure_name ：存儲過程的名稱，在前面加#為局部臨時存儲過程，加##為全局臨時存儲過程。
   
2. number：是可選的整數(shù)，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數(shù)字不應包含在標識符中，只應在 procedure_name 前后使用適當?shù)亩ń绶?nbsp;
   
3. @parameter：存儲過程的參數(shù)?？梢杂幸粋€或多個。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認值）。存儲過程最多可以有 2100 個參數(shù)。
   使用 @ 符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。有關(guān)更多信息，請參見 EXECUTE。 
4. 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ù)目的限制。 
5. VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標參數(shù)。 
   
6. default： 參數(shù)的默認值。如果定義了默認值，不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或 NULL。如果過程將對該參數(shù)使用 LIKE 關(guān)鍵字，那么默認值中可以包含通配符（%、_、[] 和 [^]）。
   
7. OUTPUT：表明參數(shù)是返回參數(shù)。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標占位符。 
   
8. RECOMPILE: 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，請使用 RECOMPILE 選項。
   
9. ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復制的一部分發(fā)布。 說明在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創(chuàng)建加密過程。 
   
10. FOR REPLICATION:指定不能在訂閱服務器上執(zhí)行為復制創(chuàng)建的存儲過程。.使用 FOR REPLICATION 選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復制過程中執(zhí)行。本選項不能和 WITH RECOMPILE 選項一起使用。 
    
11. AS:指定過程要執(zhí)行的操作。
    
12. sql_statement:過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。但有一些限制。
    

實例操作學習

　下面通過表Student來具體了解一下存儲過程，因為是要了解存儲過程的簡單用法，所以例子很簡單。

無參數(shù)存儲過程

　選出Student表中的所有信息

create proc StuProc
as      //此處 as 不可以省略不寫
begin   //begin 和 end 是一對，不可以只寫其中一個，但可以都不寫
select S#,Sname,Sage,Ssex from student
end
go

有參數(shù)存儲過程

　全局變量

　全局變量也稱為外部變量，是在函數(shù)的外部定義的，它的作用域為從變量定義處開始，到本程序文件的末尾。

　選出指定姓名的學生信息:

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è)置默認值

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

更詳細的實例操作學習

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 

存儲過程的修改

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ù)庫下選擇“可編程性”。  

?。?）打開“存儲過程”文件夾，右鍵單擊要刪除的存儲過程（如：PROC_SEINFO），在彈出的快捷菜單中選擇“刪除”命令。

?。?）單擊“確定”按鈕，即可刪除所選定的存儲過程。  

注意：刪除數(shù)據(jù)表后，并不會刪除相關(guān)聯(lián)的存儲過程，只是其存儲過程無法執(zhí)行。

　使用T-SQL刪除存儲過程：

　DROP PROCEDURE語句用于從當前數(shù)據(jù)庫中刪除一個或多個存儲過程或過程組。 

　語法：

DROP PROCEDURE { procedure } [ ,...n ]

　參數(shù)說明：   

• Procedure：是要刪除的存儲過程或存儲過程組的名稱。過程名稱必須符合標識符規(guī)則?？梢赃x擇是否指定過程所有者名稱，但不能指定服務器名稱和數(shù)據(jù)庫名稱。   
  
• n：是表示可以指定多個過程的占位符。
  

　例如刪除PROC_SEINFO存儲過程的SQL語句如下。

DROP PROCEDURE PROC_SEINFO

DROP PROCEDURE proc10, proc20, proc30

DROP PROCEDURE procs

注意：SQL語句DROP不能刪除存儲過程組中的單個存儲過程。

應用存儲過程驗證用戶登錄身份：

　目前，驗證用戶登錄身份的方法有多種，而通過調(diào)用存儲過程來實現(xiàn)用戶身份驗證是目前最好的解決方案之一。因為存儲過程在創(chuàng)建時即在服務器上進行編譯，所以執(zhí)行起來比單個SQL語句要快得多。

　本例是通過調(diào)用存儲過程來驗證用戶登錄的用戶名和密碼是否正確。運行本實例，在“用戶名”和“密碼”文本框中輸入相應的用戶名和密碼，單擊“登錄”按鈕即可。

　程序開發(fā)步驟：

?。?）新建一個網(wǎng)站，將其命名為"index"，默認主頁名為Default.aspx。    

?。?）Default.aspx頁面涉及到的控件如表1所示。

?。?）主要程序代碼如下。
　打開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(); 
        }}

免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：[email protected]進行舉報，并提供相關(guān)證據(jù)，查實之后，將立刻刪除涉嫌侵權(quán)內(nèi)容。

猜你喜歡

• MSSQL分頁存儲過程的實現(xiàn)及方法是什么

  這篇文章主要介紹了MSSQL分頁存儲過程的實現(xiàn)與使用方法,并支持多表分頁存儲的功能,具有一定參考借鑒價值,需要的朋友可以參考下

• SQL數(shù)據(jù)庫如何設(shè)置主鍵，方法是什么

  數(shù)據(jù)庫設(shè)置主鍵的方法：首先打開【SQL Server Management Studio】管理工具，并連接數(shù)據(jù)庫；然后右鍵【表】菜單，并依次選擇【新建】->【表】；最后按住【ctrl】鍵，并右鍵選擇【設(shè)置主鍵】即可。

• sql創(chuàng)建數(shù)據(jù)庫有中文亂碼是啥原因？如何解決？

  有一些朋友在使用SqlServer創(chuàng)建新數(shù)據(jù)庫的時候，有中文亂碼的情況，這是什么原因呢？應該如何解決？下面就和大家聊聊SqlServer數(shù)據(jù)庫中文亂碼問題的解決辦法。

• SQLserver中組合累加排序怎么實現(xiàn)，SQL語句是什么

  本文分享SQL語句實現(xiàn)表中字段的組合累加排序的實例代碼，希望能給大家做一個參考。

• SQL?Server查詢分組記錄的實現(xiàn)方法是什么

  這篇文章介紹了SQL?Server實現(xiàn)查詢每個分組的前N條記錄，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下