sql存儲過程的優(yōu)點有哪些

137次閱讀

共計 7811 個字符，預計需要花費 20 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章主要介紹 sql 存儲過程的優(yōu)點有哪些，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

sql 存儲過程實例詳解

存儲過程 (Stored Procedure), 是一組為了完成特定功能的 SQL 語句，類似一門程序設計語言，也包括了數據類型、流程控制、輸入和輸出和它自己的函數庫。

存儲過程可以說是一個記錄集，它是由一些 T -SQL 語句組成的代碼塊，這些 T -SQL 語句代碼像一個方法一樣實現一些功能（對單表或多表的增刪改查），然后再給這個代碼塊取一個名字，在用到這個功能的時候調用他就行了。不過 SQL 存儲過程對于一些初學者來說還是比較抽象難理解的，因此本文將由淺至深地剖析 SQL 存儲過程，幫助你學習它。

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

1. 存儲過程只在創(chuàng)造時進行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般 SQL 語句每執(zhí)行一次就編譯一次, 所以使用存儲過程可提高數據庫執(zhí)行速度，效率要比 T -SQL 語句高。

2. 當對數據庫進行復雜操作時，可將此復雜操作用存儲過程封裝起來與數據庫提供的事務處理結合一起使用。

3. 一個存儲過程在程序在網絡中交互時可以替代大堆的 T -SQL 語句，所以也能降低網絡的通信量，提高通信速率。

4. 存儲過程可以重復使用, 可減少數據庫開發(fā)人員的工作量。

5. 安全性高, 可設定只有某些用戶才具有對指定存儲過程的使用權

存儲過程基本語法

-------------- 創(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 ]
-------------- 調用存儲過程 -----------------
EXECUTE Procedure_name   -- 存儲過程如果有參數，后面加參數格式為：@參數名 =value，也可直接為參數值 value
-------------- 刪除存儲過程 -----------------
drop procedure procedure_name -- 在存儲過程中能調用另外一個存儲過程，而不能刪除另外一個存儲過程

創(chuàng)建存儲過程的參數

● procedure_name：存儲過程的名稱，在前面加 #為局部臨時存儲過程，加## 為全局臨時存儲過程。

● number：是可選的整數，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在標識符中，只應在 procedure_name 前后使用適當的定界符。

● @parameter：存儲過程的參數?？梢杂幸粋€或多個。用戶必須在執(zhí)行過程時提供每個所聲明參數的值（除非定義了該參數的默認值）。存儲過程最多可以有 2100 個參數。

● 使用 @ 符號作為第一個字符來指定參數名稱。參數名稱必須符合標識符的規(guī)則。每個過程的參數僅用于該過程本身；相同的參數名稱可以用在其它過程中。默認情況下，參數只能代替常量，而不能用于代替表名、列名或其它數據庫對象的名稱。有關更多信息，請參見 EXECUTE。

● data_type：參數的數據類型。所有數據類型（包括 text、ntext 和 image）均可以用作存儲過程的參數。不過，cursor 數據類型只能用于 OUTPUT 參數。如果指定的數據類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關鍵字。有關 SQL Server 提供的數據類型及其語法的更多信息，請參見數據類型。

說明對于可以是 cursor 數據類型的輸出參數，沒有最大數目的限制。

● VARYING：指定作為輸出參數支持的結果集（由存儲過程動態(tài)構造，內容可以變化）。僅適用于游標參數。

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

● OUTPUT：表明參數是返回參數。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數可將信息返回給調用過程。Text、ntext 和 image 參數可用作 OUTPUT 參數。使用 OUTPUT 關鍵字的輸出參數可以是游標占位符。

● RECOMPILE: 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內存中的執(zhí)行計劃時，請使用 RECOMPILE 選項。

● ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復制的一部分發(fā)布。說明在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創(chuàng)建加密過程。

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

● AS: 指定過程要執(zhí)行的操作。

● sql_statement: 過程中要包含的任意數目和類型的 Transact-SQL 語句。但有一些限制。

實例操作學習

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

無參數存儲過程

選出 Student 表中的所有信息

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

有參數存儲過程

全局變量

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

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

create proc StuProc
@sname varchar(100) 
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
exec StuProc  趙雷  // 執(zhí)行語句

上面是在外部給變量賦值，也可以在內部直接給變量設置默認值

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

也可以把變量的內容輸出，使用 output

create proc StuProc
@sname varchar(100),
@IsRight int output // 傳出參數
as 
if exists (select S#,Sname,Sage,Ssex from student where sname=@sname)
set @IsRight =1
set @IsRight=0
declare @IsRight int 
exec StuProc  趙雷  , @IsRight output
select @IsRight

以上是全局變量，下面來了解局部變量

局部變量

局部變量也稱為內部變量。局部變量是在函數內作定義說明的。其作用域僅限于函數內部，離開該函數后再使用這種變量是非法的。

局部變量的定義

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

局部變量的賦值方法

set{@變量名 = 表達式} 或者 select{@變量名 = 表達式}

局部變量的顯示

create proc StuProc
as 
declare @sname varchar(100)
set @sname= 趙雷 
select S#,Sname,Sage,Ssex from student where sname=@sname
exec StuProc

那如果是要把局部變量的數據顯示出來怎么辦呢？

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

更詳細的實例操作學習

比如，在 SQL Server 查詢編輯器窗口中用 CREATE PROCEDURE 語句創(chuàng)建存儲過程 PROC_InsertEmployee，用于實現向員工信息表（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)建完存儲過程之后，如果需要重新修改存儲過程的功能及參數，可以在 SQL Server 2005 中通過以下兩種方法進行修改：一種是用 Microsoft SQL Server Mangement 修改存儲過程；另外一種是用 T -SQL 語句修改存儲過程。

使用 Microsoft SQL Server Mangement 修改存儲過程，步驟如下：

（1）在 SQL Server Management Studio 的“對象資源管理器”中，選擇要修改存儲過程所在的數據庫（如：db_18），然后在該數據庫下，選擇“可編程性”。

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

使用 Transact-SQL 修改存儲過程：

使用 ALTER PROCEDURE 語句修改存儲過程，它不會影響存儲過程的權限設定，也不會更改存儲過程的名稱。

語法：

ALTER PROC [ EDURE ] procedure_name [ ; number ] 
 [ { @parameter data_type } 
 [ VARYING ] [ = default ] [ OUTPUT ] 
 ] [ ,...n ] 
[ WITH 
 { RECOMPILE | ENCRYPTION 
 | RECOMPILE , ENCRYPTION } 
] 
[ FOR REPLICATION ] 
AS 
 sql_statement [ ...n ]

參數說明

procedure_name：是要更改的存儲過程的名稱。

交叉鏈接：關于 ALTER PROCEDURE 語句的其他參數與 CREATE PROCEDURE 語句相同，可參見上面的“創(chuàng)建存儲過程的參數”。

例如，修改存儲過程 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 的“對象資源管理器”中，選擇要刪除存儲過程所在的數據庫（如：db_student），然后在該數據庫下選擇“可編程性”。

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

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

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

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

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

語法：

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

參數說明：

Procedure：是要刪除的存儲過程或存儲過程組的名稱。過程名稱必須符合標識符規(guī)則?？梢赃x擇是否指定過程所有者名稱，但不能指定服務器名稱和數據庫名稱。

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 不能刪除存儲過程組中的單個存儲過程。

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

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

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

程序開發(fā)步驟：

（1）新建一個網站，將其命名為 index，默認主頁名為 Default.aspx。

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

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

打開 SQL Server Management Studio，并連接到 SQL Server2005 中的數據庫。單擊工具欄中“”按鈕，新建查詢編輯器。

在該查詢編輯器中，創(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) 
 { 
 // 連接數據庫  
 myConn = new SqlConnection(ConfigurationManager.AppSettings[ ConnectionString].ToString()); 
 myCmd = new SqlCommand(PROC_EXISTS , myConn); // 調用存儲過程，判斷用戶是否存在
 myCmd.CommandType = CommandType.StoredProcedure; 
 // 為存儲過程的參數賦值  
 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); 
 // 指出該參數是存儲過程的 OUTPUT 參數  
 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(); 
 }}

以上是“sql 存儲過程的優(yōu)點有哪些”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業(yè)資訊頻道！

向 AI 問一下細節(jié)

正文完