軟件開(kāi)發(fā)學(xué)習(xí)之—實(shí)例解析SQL存儲(chǔ)過(guò)程

來(lái)源：荊州網(wǎng)站建設(shè) 時(shí)間：2017-05-19

存儲(chǔ)過(guò)程(Stored Procedure),是一組為了完成特定功能的SQL 語(yǔ)句，類似一門(mén)程序設(shè)計(jì)語(yǔ)言，也包括了數(shù)據(jù)類型、流程控制、輸入和輸出和它自己的函數(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è)定只有某些用戶才具有對(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ò)程

創(chuàng)建存儲(chǔ)過(guò)程的參數(shù)

1.procedure_name ：存儲(chǔ)過(guò)程的名稱，在前面加#為局部臨時(shí)存儲(chǔ)過(guò)程，加##為全局臨時(shí)存儲(chǔ)過(guò)程。

2.; number：是可選的整數(shù)，用來(lái)對(duì)同名的過(guò)程分組，以便用一條 DROP PROCEDURE 語(yǔ)句即可將同組的過(guò)程一起除去。例如，名為 orders 的應(yīng)用程序使用的過(guò)程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語(yǔ)句將除去整個(gè)組。如果名稱中包含定界標(biāo)識(shí)符，則數(shù)字不應(yīng)包含在標(biāo)識(shí)符中，只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)亩ń绶?/span>

3.@parameter：存儲(chǔ)過(guò)程的參數(shù)?？梢杂幸粋€(gè)或多個(gè)。用戶必須在執(zhí)行過(guò)程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲(chǔ)過(guò)程最多可以有 2.100 個(gè)參數(shù)。

使用 @ 符號(hào)作為第一個(gè)字符來(lái)指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識(shí)符的規(guī)則。每個(gè)過(guò)程的參數(shù)僅用于該過(guò)程本身；相同的參數(shù)名稱可以用在其它過(guò)程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫(kù)對(duì)象的名稱。有關(guān)更多信息，請(qǐng)參見(jiàn) EXECUTE。

4.data_type：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括text、ntext 和 image）均可以用作存儲(chǔ)過(guò)程的參數(shù)。不過(guò)，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定的數(shù)據(jù)類型為 cursor，也必須同時(shí)指定 VARYING 和 OUTPUT 關(guān)鍵字。有關(guān) SQL Server 提供的數(shù)據(jù)類型及其語(yǔ)法的更多信息，請(qǐng)參見(jiàn)數(shù)據(jù)類型。

說(shuō)明對(duì)于可以是 cursor 數(shù)據(jù)類型的輸出參數(shù)，沒(méi)有最大數(shù)目的限制。

5.VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲(chǔ)過(guò)程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。

6.default：參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過(guò)程。默認(rèn)值必須是常量或 NULL。如果過(guò)程將對(duì)該參數(shù)使用 LIKE 關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、[] 和 [^]）。

7.OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過(guò)程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。

8.RECOMPILE: 表明 SQL Server 不會(huì)緩存該過(guò)程的計(jì)劃，該過(guò)程將在運(yùn)行時(shí)重新編譯。在使用非典型值或臨時(shí)值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計(jì)劃時(shí)，請(qǐng)使用 RECOMPILE 選項(xiàng)。

9.ENCRYPTION: 表示 SQL Server加密 syscomments 表中包含 CREATE PROCEDURE 語(yǔ)句文本的條目。使用 ENCRYPTION 可防止將過(guò)程作為 SQL Server 復(fù)制的一部分發(fā)布。說(shuō)明在升級(jí)過(guò)程中，SQL Server 利用存儲(chǔ)在 syscomments 中的加密注釋來(lái)重新創(chuàng)建加密過(guò)程。

10.FOR REPLICATION：指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過(guò)程。.使用 FOR REPLICATION 選項(xiàng)創(chuàng)建的存儲(chǔ)過(guò)程可用作存儲(chǔ)過(guò)程篩選，且只能在復(fù)制過(guò)程中執(zhí)行。本選項(xiàng)不能和 WITH RECOMPILE 選項(xiàng)一起使用。

11.AS:指定過(guò)程要執(zhí)行的操作。

12.sql_statement:過(guò)程中要包含的任意數(shù)目和類型的 Transact-SQL 語(yǔ)句。但有一些限制。

實(shí)例操作學(xué)習(xí)

下面通過(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ò)程：

全局變量

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

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

create proc StuProc

@sname varchar(100)  

as

begin

select S#,Sname,Sage,Ssex from studentwhere 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 studentwhere 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 fromstudent where sname=@sname)

set @IsRight =1

else

set @IsRight=0

go

declare @IsRight int

exec StuProc '趙雷' ,@IsRight output

select @IsRight

以上是全局變量，下面來(lái)了解局部變量

局部變量：

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

局部變量的定義：

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

局部變量的賦值方法：

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

局部變量的顯示：

create proc StuProc

as

declare @sname varchar(100)

set @sname='趙雷'

select S#,Sname,Sage,Ssex from studentwhere sname=@sname

go

exec StuProc

那如果是要把局部變量的數(shù)據(jù)顯示出來(lái)怎么辦呢？

create proc StuProc

as

declare @sname varchar(100)

set @sname=(select Sname from student whereS#=01)

select @sname

go

exec StuProc

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

比如，在SQL Server查詢編輯器窗口中用CREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程PROC_InsertEmployee，用于實(shí)現(xiàn)向員工信息表（tb_Employee）中添加信息，同時(shí)生成自動(dòng)編號(hào)。其SQL語(yǔ)句如下：

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ò)程的修改：

創(chuàng)建完存儲(chǔ)過(guò)程之后，如果需要重新修改存儲(chǔ)過(guò)程的功能及參數(shù)，可以在SQL Server 2005中通過(guò)以下兩種方法進(jìn)行修改：一種是用Microsoft SQL Server Mangement修改存儲(chǔ)過(guò)程；另外一種是用T-SQL語(yǔ)句修改存儲(chǔ)過(guò)程。

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

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

（2）打開(kāi)“存儲(chǔ)過(guò)程”文件夾，右鍵單擊要修改的存儲(chǔ)過(guò)程（如：PROC_SEINFO），在彈出的快捷菜單中選擇“修改”命令，將會(huì)出現(xiàn)查詢編輯器窗口。用戶可以在此窗口中編輯T-SQL代碼，完成編輯后，單擊工具欄中的“執(zhí)行（X）”按鈕，執(zhí)行修改代碼。用戶可以在查詢編輯器下方的Message窗口中看到執(zhí)行結(jié)果信息。

使用Transact-SQL修改存儲(chǔ)過(guò)程：

使用ALTER PROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程，它不會(huì)影響存儲(chǔ)過(guò)程的權(quán)限設(shè)定，也不會(huì)更改存儲(chǔ)過(guò)程的名稱。

語(yǔ)法：

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ò)程的名稱。

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

例如，修改存儲(chǔ)過(guò)程PROC_SEINFO，用于查詢年齡大于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ò)程，步驟如下：

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

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

（3）單擊“確定”按鈕，即可刪除所選定的存儲(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ò)程組的名稱。過(guò)程名稱必須符合標(biāo)識(shí)符規(guī)則?？梢赃x擇是否指定過(guò)程所有者名稱，但不能指定服務(wù)器名稱和數(shù)據(jù)庫(kù)名稱。

n：是表示可以指定多個(gè)過(guò)程的占位符。

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

DROP PROCEDURE PROC_SEINFO

例如，刪除多個(gè)存儲(chǔ)過(guò)程proc10、proc20和proc30。

DROP PROCEDURE proc10, proc20, proc30

例如，刪除存儲(chǔ)過(guò)程組procs（其中包含存儲(chǔ)過(guò)程proc1、proc2、proc3）。

DROP PROCEDURE procs

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

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

目前，驗(yàn)證用戶登錄身份的方法有多種，而通過(guò)調(diào)用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)用戶身份驗(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)證用戶登錄的用戶名和密碼是否正確。運(yùn)行本實(shí)例，在“用戶名”和“密碼”文本框中輸入相應(yīng)的用戶名和密碼，單擊“登錄”按鈕即可。

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

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

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

（3）主要程序代碼如下。

打開(kāi)SQL Server Management Studio，并連接到SQL Server2005中的數(shù)據(jù)庫(kù)。單擊工具欄中“”按鈕，新建查詢編輯器。在該查詢編輯器中，創(chuàng)建驗(yàn)證登錄用戶身份的存儲(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 whereuserName=@UserName AND passWord=@PassWord)

     set @ReturnValue= 100

ELSE

     set @ReturnValue= -100

GO

在"登錄"按鈕的Click事件下，執(zhí)行驗(yàn)證登錄用戶身份的存儲(chǔ)過(guò)程，如果輸入的用戶名和密碼正確，則彈出對(duì)話框提示用戶登錄成功，代碼如下：

protected void btnLogin_Click(objectsender, EventArgs e)

   {

      //連接數(shù)據(jù)庫(kù)

      myConn = newSqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());

      myCmd = new SqlCommand("PROC_EXISTS", myConn);   //調(diào)用存儲(chǔ)過(guò)程，判斷用戶是否存在

      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 = newSqlParameter("@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('您輸入的用戶名和密碼不正確，請(qǐng)重新輸入！')</script>");

               return;

          }

      }

      catch(Exception ex)

      {

          Response.Write(ex.Message.ToString());

      }

      finally

      {

          myConn.Close();

          myConn.Dispose();

          myCmd.Dispose();

      }}

以上由湖北國(guó)菱荊州軟件開(kāi)發(fā)、荊州網(wǎng)站建設(shè)工程師整理發(fā)布。喜歡程序開(kāi)發(fā)的伙伴們歡迎一起交流探討。Email:business@gl-ns.com，電話：0716-6666211.