SQL Server的存儲過程

148次閱讀

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

本篇內(nèi)容主要講解“SQL Server 的存儲過程”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“SQL Server 的存儲過程”吧!

首先介紹一下什么是存儲過程：存儲過程就是將常用的或很復雜的工作，預先用 SQL 語句寫好并用一個指定的名稱存儲起來，并且這樣的語句是放在中的，還可以根據(jù)條件執(zhí)行不同 SQL 語句，那么以后要叫數(shù)據(jù)庫提供與已定義好的存儲過程的功能相同的服務時，只需調(diào)用 execute, 即可自動完成命令。

語法：

Sql 代碼
CREATE PROC [EDURE] procedure_name [; number]
[{ @parameter data_type}
[VARYING] [= default] [OUTPUT]
] [,…n]

[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION} ]

[FOR REPLICATION]

AS sql_statement […n]

——————————————————————————-

create proc [edure] procedure_name [;number]

[{@parameter date_type}

[varying] [=default] [output]

] [,…n]

[with {recompile | encryption | recompile , encryption}]

[for repilcation]

as sql_statement [..n]
參數(shù)：
procedure_name
新存儲過程的名稱。過程名必須符合標識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須唯一。
要創(chuàng)建局部臨時過程，可以在 procedure_name 前面加一個編號符 (#procedure_name)，要創(chuàng)建全局臨時過程，可以在 procedure_name 前面加兩個編號符 (##procedure_name)。完整的名稱（包括 # 或 ##）不能超過 128 個字符。指定過程所有者的名稱是可選的。
;number
是可選的整數(shù)，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數(shù)字不應包含在標識符中，只應在 procedure_name 前后使用適當?shù)亩ń绶?br /> @parameter
過程中的參數(shù)。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認值）。存儲過程最多可以有 2100 個參數(shù)。
使用 @符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。
data_type
參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括 text、ntext 和 image）均可以用作存儲過程的參數(shù)。不過，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定的數(shù)據(jù)類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關鍵字。
說明：對于可以是 cursor 數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。
VARYING
指定作為輸出參數(shù)支持的結果集（由存儲過程動態(tài)構造，內(nèi)容可以變化）。僅適用于游標參數(shù)。
default
參數(shù)的默認值。如果定義了默認值，不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或 NULL。如果過程將對該參數(shù)使用 LIKE 關鍵字，那么默認值中可以包含通配符（%、_、[] 和 [^]）。
OUTPUT
表明參數(shù)是返回參數(shù)。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關鍵字的輸出參數(shù)可以是游標占位符。
n
表示最多可以指定 2100 個參數(shù)的占位符。
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(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
過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。但有一些限制。
n
是表示此過程可以包含多條 Transact-SQL 語句的占位符。
注釋
存儲過程的最大大小為 128 MB。

存儲過程的優(yōu)點都有哪些呢？
1. 存儲過程只在創(chuàng)造時進行編譯即可，以后每次執(zhí)行存儲過程都不需再重新編譯，而我們通常使用的 SQL 語句每執(zhí)行一次就編譯一次, 所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。
2. 經(jīng)常會遇到復雜的業(yè)務邏輯和對數(shù)據(jù)庫的操作，這個時候就會用 SP 來封裝數(shù)據(jù)庫操作。當對數(shù)據(jù)庫進行復雜操作時 (如對多個表進行 Update,Insert,Query,Delete 時），可將此復雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務處理結合一起使用。可以極大的提高數(shù)據(jù) 庫的使用效率，減少程序的執(zhí)行時間，這一點在較大數(shù)據(jù)量的數(shù)據(jù)庫的操作中是非常重要的。在代碼上看，SQL 語句和程序代碼語句的分離，可以提高程序代碼的可讀性。
3. 存儲過程可以設置參數(shù)，可以根據(jù)傳入?yún)?shù)的不同重復使用同一個存儲過程，從而高效的提高代碼的優(yōu)化率和可讀性。
4. 安全性高, 可設定只有某此用戶才具有對指定存儲過程的使用權存儲過程的種類：
（1）系統(tǒng)存儲過程：以 sp_開頭, 用來進行系統(tǒng)的各項設定. 取得信息. 相關管理工作, 如 sp_help 就是取得指定對象的相關信息。
（2）擴展存儲過程以 XP_開頭, 用來調(diào)用操作系統(tǒng)提供的功能
exec master..xp_cmdshell ping 10.8.16.1
（3）用戶自定義的存儲過程, 這是我們所指的存儲過程常用格式
模版：Create procedure procedue_name [@parameter data_type][output]
[with]{recompile|encryption} as sql_statement
解釋：output：表示此參數(shù)是可傳回的
with {recompile|encryption} recompile: 表示每次執(zhí)行此存儲過程時都重新編譯一次;encryption: 所創(chuàng)建的存儲過程的內(nèi)容會被加密。

實例 1：只返回單一記錄集的存儲過程。
表銀行存款表（bankMoney）的內(nèi)容如下

要求 1：查詢表 bankMoney 的內(nèi)容的存儲過程

Sql 代碼
create procedure sp_query_bankMoney
as
select * from bankMoney
go
exec sp_query_bankMoney
注 * 在使用過程中只需要把中的 SQL 語句替換為存儲過程名，就可以了很方便吧！

實例 2（向存儲過程中傳遞參數(shù)）：
加入一筆記錄到表 bankMoney，并查詢此表中 userID= Zhangsan 的所有存款的總金額。
Sql 代碼
Create proc insert_bank
@param1 char(10),
@param2 varchar(20),
@param3 varchar(20),
@param4 int,
@param5 int output
with encryption ——— 加密
as
insert bankMoney (id,userID,sex,Money)
Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID= Zhangsan
go
在 SQL Server 查詢分析器中執(zhí)行該存儲過程的方法是：
Sql 代碼
declare @total_price int
exec insert_bank 004 , Zhangsan , 男 ,100,@total_price output
print 總余額為 +convert(varchar,@total_price)
go

在這里再啰嗦一下存儲過程的 3 種傳回值（方便正在看這個例子的朋友不用再去查看語法內(nèi)容）:
1. 以 Return 傳回整數(shù)
2. 以 output 格式傳回參數(shù)
3.Recordset
傳回值的區(qū)別:
output 和 return 都可在批次程式中用變量接收, 而 recordset 則傳回到執(zhí)行批次的客戶端中

實例 3：使用帶有復雜 SELECT 語句的簡單過程
下面的存儲過程從四個表的聯(lián)接中返回所有作者（提供了姓名）、出版的書籍以及出版社。該存儲過程不使用任何參數(shù)。
Sql 代碼
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = au_info_all AND type = P )
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
au_info_all 存儲過程可以通過以下方法執(zhí)行：
EXECUTE au_info_all
— Or
EXEC au_info_all
如果該過程是批處理中的第一條語句，則可使用：
au_info_all

實例 4：使用帶有參數(shù)的簡單過程
Sql 代碼
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
au_info 存儲過程可以通過以下方法執(zhí)行：
Sql 代碼
EXECUTE au_info Dull , Ann
— Or
EXECUTE au_info @lastname = Dull , @firstname = Ann
— Or
EXECUTE au_info @firstname = Ann , @lastname = Dull
— Or
EXEC au_info Dull , Ann
— Or
EXEC au_info @lastname = Dull , @firstname = Ann
— Or
EXEC au_info @firstname = Ann , @lastname = Dull
如果該過程是批處理中的第一條語句，則可使用：
Sql 代碼
au_info Dull , Ann
— Or
au_info @lastname = Dull , @firstname = Ann
— Or
au_info @firstname = Ann , @lastname = Dull
實例 5：使用帶有通配符參數(shù)的簡單過程
Sql 代碼
CREATE PROCEDURE au_info2
@lastname varchar(30) = D% ,
@firstname varchar(18) = %
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
au_info2 存儲過程可以用多種組合執(zhí)行。下面只列出了部分組合：
Sql 代碼
EXECUTE au_info2
— Or
EXECUTE au_info2 Wh%
— Or
EXECUTE au_info2 @firstname = A%
— Or
EXECUTE au_info2 [CK]ars[OE]n
— Or
EXECUTE au_info2 Hunter , Sheryl
— Or
EXECUTE au_info2 H% , S%

= proc2