詳細(xì)實(shí)例全面解析SQL存儲(chǔ)過(guò)程

全面解析SQL存儲(chǔ)過(guò)程

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

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

1. 存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯，而一般SQL語(yǔ)句每執(zhí)行一次就編譯一次,所以使用存儲(chǔ)過(guò)程可提高數(shù)據(jù)庫(kù)執(zhí)行速度，效率要比T-SQL語(yǔ)句高。
2. 當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)，可將此復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用。
3. 一個(gè)存儲(chǔ)過(guò)程在程序在網(wǎng)絡(luò)中交互時(shí)可以替代大堆的T-SQL語(yǔ)句，所以也能降低網(wǎng)絡(luò)的通信量，提高通信速率。
4. 存儲(chǔ)過(guò)程可以重復(fù)使用,可減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。
5. 安全性高,可設(shè)定只有某些用戶(hù)才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán)
   

存儲(chǔ)過(guò)程基本語(yǔ)法

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

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)用存儲(chǔ)過(guò)程-----------------

EXECUTE Procedure_name '' --存儲(chǔ)過(guò)程如果有參數(shù)，后面加參數(shù)格式為：@參數(shù)名=value，也可直接為參數(shù)值value

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

drop procedure procedure_name    --在存儲(chǔ)過(guò)程中能調(diào)用另外一個(gè)存儲(chǔ)過(guò)程，而不能刪除另外一個(gè)存儲(chǔ)過(guò)程

　下面通過(guò)表Student來(lái)具體了解一下存儲(chǔ)過(guò)程，因?yàn)槭且私獯鎯?chǔ)過(guò)程的簡(jiǎn)單用法，所以例子很簡(jiǎn)單。

無(wú)參數(shù)存儲(chǔ)過(guò)程

　選出Student表中的所有信息

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

有參數(shù)存儲(chǔ)過(guò)程

　全局變量

　全局變量也稱(chēng)為外部變量，是在函數(shù)的外部定義的，它的作用域?yàn)閺淖兞慷x處開(kāi)始，到本程序文件的末尾。

　選出指定姓名的學(xué)生信息:

create proc StuProc
@sname varchar(100)   
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
end
go

exec StuProc '趙雷'   //執(zhí)行語(yǔ)句

　上面是在外部給變量賦值，也可以在內(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

　局部變量

　局部變量也稱(chēng)為內(nèi)部變量。局部變量是在函數(shù)內(nèi)作定義說(shuō)明的。其作用域僅限于函數(shù)內(nèi)部，離開(kāi)該函數(shù)后再使用這種變量是非法的。

　局部變量的定義

　必須先用Declare命令定以后才可以使用，declare{@變量名 數(shù)據(jù)類(lèi)型}

　局部變量的賦值方法

　set{@變量名=表達(dá)式}或者select{@變量名=表達(dá)式}

　局部變量的顯示

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ù)顯示出來(lái)怎么辦呢？

create proc StuProc
as 
declare @sname varchar(100)
set @sname=(select Sname from student where S#=01)
select @sname
go

exec StuProc

更詳細(xì)的實(shí)例操作學(xué)習(xí)

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(員工編號(hào)) 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 

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

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ù)說(shuō)明

　procedure_name：是要更改的存儲(chǔ)過(guò)程的名稱(chēng)。

　交叉鏈接：關(guān)于ALTER PROCEDURE語(yǔ)句的其他參數(shù)與CREATE PROCEDURE語(yǔ)句相同，可參見(jiàn)上面的“創(chuàng)建存儲(chǔ)過(guò)程的參數(shù)”。

　例如，修改存儲(chǔ)過(guò)程PROC_SEINFO，用于查詢(xún)年齡大于35的員工信息。SQL語(yǔ)句如下：

ALTER PROCEDURE [dbo].[PROC_SEINFO] 
AS 
BEGIN 
SELECT * FROM tb_Employee where 員工年齡>35 
END

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

　使用Microsoft SQL Server Mangement刪除存儲(chǔ)過(guò)程，步驟如下：

?。?）在SQL Server Management Studio的“對(duì)象資源管理器”中，選擇要?jiǎng)h除存儲(chǔ)過(guò)程所在的數(shù)據(jù)庫(kù)（如：db_student），然后在該數(shù)據(jù)庫(kù)下選擇“可編程性”。  

?。?）打開(kāi)“存儲(chǔ)過(guò)程”文件夾，右鍵單擊要?jiǎng)h除的存儲(chǔ)過(guò)程（如：PROC_SEINFO），在彈出的快捷菜單中選擇“刪除”命令。

?。?）單擊“確定”按鈕，即可刪除所選定的存儲(chǔ)過(guò)程。  

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

　使用T-SQL刪除存儲(chǔ)過(guò)程：

　DROP PROCEDURE語(yǔ)句用于從當(dāng)前數(shù)據(jù)庫(kù)中刪除一個(gè)或多個(gè)存儲(chǔ)過(guò)程或過(guò)程組。 

　語(yǔ)法：

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

　參數(shù)說(shuō)明：   

• Procedure：是要?jiǎng)h除的存儲(chǔ)過(guò)程或存儲(chǔ)過(guò)程組的名稱(chēng)。過(guò)程名稱(chēng)必須符合標(biāo)識(shí)符規(guī)則。可以選擇是否指定過(guò)程所有者名稱(chēng)，但不能指定服務(wù)器名稱(chēng)和數(shù)據(jù)庫(kù)名稱(chēng)。   
  
• n：是表示可以指定多個(gè)過(guò)程的占位符。
  

　例如刪除PROC_SEINFO存儲(chǔ)過(guò)程的SQL語(yǔ)句如下。

DROP PROCEDURE PROC_SEINFO

DROP PROCEDURE proc10, proc20, proc30

DROP PROCEDURE procs

注意：SQL語(yǔ)句DROP不能刪除存儲(chǔ)過(guò)程組中的單個(gè)存儲(chǔ)過(guò)程。

應(yīng)用存儲(chǔ)過(guò)程驗(yàn)證用戶(hù)登錄身份：

　目前，驗(yàn)證用戶(hù)登錄身份的方法有多種，而通過(guò)調(diào)用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)用戶(hù)身份驗(yàn)證是目前最好的解決方案之一。因?yàn)榇鎯?chǔ)過(guò)程在創(chuàng)建時(shí)即在服務(wù)器上進(jìn)行編譯，所以執(zhí)行起來(lái)比單個(gè)SQL語(yǔ)句要快得多。

　本例是通過(guò)調(diào)用存儲(chǔ)過(guò)程來(lái)驗(yàn)證用戶(hù)登錄的用戶(hù)名和密碼是否正確。運(yùn)行本實(shí)例，在“用戶(hù)名”和“密碼”文本框中輸入相應(yīng)的用戶(hù)名和密碼，單擊“登錄”按鈕即可。

　程序開(kāi)發(fā)步驟：

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

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

?。?）主要程序代碼如下。
　打開(kāi)SQL Server Management Studio，并連接到SQL Server2005中的數(shù)據(jù)庫(kù)。單擊工具欄中“ ”按鈕，新建查詢(xún)編輯器。在該查詢(xún)編輯器中，創(chuàng)建驗(yàn)證登錄用戶(hù)身份的存儲(chǔ)過(guò)程PROC_EXISTS，具體的SQL語(yǔ)句如下：

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í)行驗(yàn)證登錄用戶(hù)身份的存儲(chǔ)過(guò)程，如果輸入的用戶(hù)名和密碼正確，則彈出對(duì)話(huà)框提示用戶(hù)登錄成功，代碼如下：

protected void btnLogin_Click(object sender, EventArgs e) 
    { 
        //連接數(shù)據(jù)庫(kù) 
        myConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); 
        myCmd = new SqlCommand("PROC_EXISTS", myConn);   //調(diào)用存儲(chǔ)過(guò)程，判斷用戶(hù)是否存在
        myCmd.CommandType = CommandType.StoredProcedure; 
        //為存儲(chǔ)過(guò)程的參數(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ù)是存儲(chǔ)過(guò)程的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('您是合法用戶(hù)，登錄成功！')</script>"); 
                return; 
            } 
            else 
            { 
                Response.Write("<script>alert('您輸入的用戶(hù)名和密碼不正確，請(qǐng)重新輸入！')</script>"); 
                return; 
            } 
        } 
        catch(Exception ex) 
        { 
            Response.Write(ex.Message.ToString()); 
        } 
        finally 
        { 
            myConn.Close(); 
            myConn.Dispose(); 
            myCmd.Dispose(); 
        }}