目錄

1 存儲過程簡介

2 存儲過程使用

2.1?創建存儲過程

2.2 in，out以及inout

---

1 存儲過程簡介

?SQL語句需要先編譯然后執行，而存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯后存儲在數據庫中，用戶通過指定存儲過程的名字并給定參數（如果該存儲過程帶有參數）來調用執行它。

????存儲過程是可編程的函數，在數據庫中創建并保存，可以由SQL語句和控制結構組成。當想要在不同的應用程序或平臺上執行相同的函數，或者封裝特定功能時，存儲過程是非常有用的。數據庫中的存儲過程可以看做是對編程中面向對象方法的模擬，它允許控制數據的訪問方式。

優點

• 存儲過程可封裝，并隱藏復雜的商業邏輯。
• 存儲過程可以回傳值，并可以接受參數。
• 存儲過程無法使用 SELECT 指令來運行，因為它是子程序，與查看表，數據表或用戶定義函數不同。
• 存儲過程可以用在數據檢驗，強制實行商業邏輯等。

缺點

• 存儲過程，往往定制化于特定的數據庫上，因為支持的編程語言不同。當切換到其他廠商的數據庫系統時，需要重寫原有的存儲過程。
• 存儲過程的性能調校與撰寫，受限于各種數據庫系統。

2 存儲過程使用

存儲過程的一些基本語法：

--------------創建存儲過程-----------------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 --在存儲過程中能調用另外一個存儲過程，而不能刪除另外一個存儲過程

創建存儲過程的參數：
1.procedure_name?：存儲過程的名稱，在前面加#為局部臨時存儲過程，加##為全局臨時存儲過程。

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

3.@parameter： 存儲過程的參數。可以有一個或多個。用戶必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的默認值）。存儲過程最多可以有 2.100 個參數。?
使用 @ 符號作為第一個字符來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用于該過程本身；相同的參數名稱可以用在其它過程中。默認情況下，參數只能代替常量，而不能用于代替表名、列名或其它數據庫對象的名稱。有關更多信息，請參見 EXECUTE。?

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

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

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

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

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

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

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

11.AS?:指定過程要執行的操作。

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

2.1?創建存儲過程

UserAccount
UserID	UserName	PassWord	RegisterTime	RegisterIP
12	6 ??????????????????	6 ??????????????????	2012-12-31	6
18	5 ??????????????????	5 ??????????????????	2013-01-01	5
19	1 ??????????????????	1 ??????????????????	2013-01-01	1
20	2 ??????????????????	2 ??????????????????	2013-01-01	2
21	3 ??????????????????	3 ??????????????????	2013-01-01	3
22	4 ??????????????????	4 ??????????????????	2013-01-01	4
23	5 ??????????????????	5 ??????????????????	2013-01-01	5
25	7 ??????????????????	7 ??????????????????	2013-01-01	7
26	8 ??????????????????	8 ??????????????????	2013-01-01	8
NULL	NULL	NULL	NULL	NULL

針對上面的表，我使用存儲過程對它做一些操作：

1.?只返回單一記錄集的存儲過程?

-------------創建名為GetUserAccount的存儲過程---------------- create Procedure GetUserAccount as select * from UserAccount go-------------執行上面的存儲過程---------------- exec GetUserAccount

?結果：相當于運行 select * from?UserAccount?這行代碼，結果為整個表的數據。

2.沒有輸入輸出的存儲過程?

-------------創建名為GetUserAccount的存儲過程----------------create Procedure inUserAccount as insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9) go-------------執行上面的存儲過程----------------exec inUserAccount

結果：相當于運行?insert?into?UserAccount (UserName,[PassWord],RegisterTime,RegisterIP)?values(9,9,'2013-01-02',9)?這行代碼。

3.有返回值的存儲過程?

-------------創建名為GetUserAccount的存儲過程----------------create Procedure inUserAccountRe as insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10) return @@rowcount go-------------執行上面的存儲過程----------------exec inUserAccountRe

?解釋：這里的@@rowcount為執行存儲過程影響的行數，執行的結果是不僅插入了一條數據，還返回了一個值即 return value =1 ?，這個可以在程序中獲取，稍后在c#調用存儲過程中會有說到。

4.有輸入參數和輸出參數的存儲過程?

-------------創建名為GetUserAccount的存儲過程----------------create Procedure GetUserAccountRe @UserName nchar(20), @UserID int output as if(@UserName>5) select @UserID=COUNT(*) from UserAccount where UserID>25 else set @UserID=1000 go-------------執行上面的存儲過程----------------exec GetUserAccountRe '7',null

解釋：@UserName為輸入參數，@UserID為輸出參數。?運行結果為@userID為COOUT（*）即 =1。

5. 同時具有返回值、輸入參數、輸出參數的存儲過程?

-------------創建名為GetUserAccount的存儲過程----------------create Procedure GetUserAccountRe1 @UserName nchar(20), @UserID int output as if(@UserName>5) select @UserID=COUNT(*) from UserAccount where UserID>25 else set @UserID=1000 return @@rowcount go-------------執行上面的存儲過程----------------exec GetUserAccountRe1 '7',null

結果：@userID為COOUT（*）即 =1，Retun Value=1。

6.同時返回參數和記錄集的存儲過程?

-------------創建名為GetUserAccount的存儲過程----------------create Procedure GetUserAccountRe2 @UserName nchar(20), @UserID int output as if(@UserName>5) select @UserID=COUNT(*) from UserAccount where UserID>25 else set @UserID=1000 select * from UserAccount return @@rowcount go-------------執行上面的存儲過程----------------exec GetUserAccountRe2 '7',null

結果：返回執行?select?*?from?UserAccount 這句代碼的結果集，同時@userID為COOUT（*）即 =1，Retun Value=9。?

7.返回多個記錄集的存儲過程?

-------------創建名為GetUserAccount的存儲過程----------------create Procedure GetUserAccountRe3 as select * from UserAccount select * from UserAccount where UserID>5 go-------------執行上面的存儲過程----------------exec GetUserAccountRe3

結果：返回兩個結果集，一個為?select?*?from?UserAccount，另一個為?select?*?from?UserAccount?where?UserID>5 。

2.2 in，out以及inout

??MySQL存儲過程的參數用在存儲過程的定義，共有三種參數類型

????IN，OUT，INOUT
????格式為：Create procedure|function([[IN |OUT |INOUT ] 參數名 數據類形...])

????IN 輸入參數
????????表示該參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被返回，為默認值

????OUT 輸出參數
????????該值可在存儲過程內部被改變，并可返回

????INOUT 輸入輸出參數
????????調用時指定，并且可被改變和返回

1.參數in的使用(代表輸入，意思說你的參數要傳到存過過程的過程里面去)

//為了避免存儲過程中分號(";")結束語句，我們使用分隔符告訴mysql解釋器,該段命令是否已經結束了。

/** 案例功能：求1-n的和 */delimiter $ create procedure p1(in n int) begin declare total int default 0; declare num int default 0; while num < n do set num:=num+1; set total:=total+num; end while; select total; end$call p1(10)$

創建并執行完存儲過程，運行結果如下：

2.參數out的使用(代表往外輸出)

//這里還要注意一點的就是我們的輸出參數一定要設置相應類型的初始，否則不管你怎么計算得出的結果都為NULL值

/** 案例功能：求1-n的和 */create procedure p2(in n int,out total int) begin declare num int default 0; set total:=0; while num < n do set num:=num+1; set total:=total+num; end while; end$

注意：對于第一個輸入參數我們可以理解，但是第二個輸出參數我們到底應該怎么輸？

這里我們需要對第二個參數定義一個變量名(更形象點就是你輸入一個輸入類型的參數n，由輸出參數total往外發射輸出我們只需要定義一個變量名來接收這個輸出值即可)

call p2(100,@sum)$//這里的@sum就是我定義用來接收處處total的值

select @sum$

創建并執行完存儲過程(查詢定義的變量值)，運行結果如下：

總結in、out區別：

in:表示輸入一個值，你需要一個值，我給你一個值

out:你往外輸出一個值，你輸出的那個值我就拿一個變量來接收你給我輸出的那個值

3.參數inout的使用(既能輸入一個值又能傳出來一個值)

/** 功能：傳一個年齡，自動讓年齡增長10歲 */ create procedure p3(inout age int) begin set age:=age+10; end$

注意：調用的時候，我這里需要和大家聲明一下，inout型的參數值既是輸入類型又是輸出類型，你給它一個值，值不是變量，不是變量那out的時候它怎么賦給這個值是不是？

因此我們需要先設置一個變量并初始化這個值，調用的時候直接傳這個變量即可。

set @currentAge=8$

call p3(@currentAge)$

select @currentAge$

創建并執行完存儲過程，運行結果如下：

使用參數的存儲過程（備注：decimal(8,2)意思就是總共有8位，小數點后留兩位）

create?procedure?procedureName(?out?min?decimal(8,2),?out?avg?decimal(8,2),?out?max?decimal(8,2)? )? BEGIN?select?MIN(price)?INTO?min?from?order;?select?AVG(price)?into?avg?from?order;?select?MAX(price)?into?max?from?order;? END;??

此過程接受三個參數， 分別用于獲取訂單表的最小、平均、最大價格。每個參數必須具有指定的類

型，這里使用十進制值(decimal(8,2))， 關鍵字OUT指出相應的參數用來從存儲過程傳出

一個值(返回給調用者)

MySQL支持IN(傳遞給存儲過程)、OUT(從存儲過程傳出，如這里所用)和INOUT(對存儲過程傳入和傳出)類型的參數。存儲過程的代碼位于BEGIN和END語句內，如前所見，它們是一系列SELECT語句，用來檢索值，然后保存到相應的變量(通過指定INTO關鍵字)

為調用此修改過的存儲過程，必須指定3個變量名，如下所示：(所有MySQL變量都必須以@開始。)

?

--?由于過程指定三個參數，?故調用必須要參數匹配? call?procedureName(@min,?@avg,?@max);??

該調用并沒有任何輸出， 只是把調用的結果賦給了調用時傳入的變量(@min, @avg, @max)。然后即可調用顯示該變量的值。

select?@min,?@avg,?@max;?

結果如下

@min	@avg	@max
42.00	601.00	2222.00

使用in參數, 輸入一個用戶id， 返回該用戶所有訂單的總價格。

create?procedure?getTotalById?(?in?userId?int,?out?total?decimal(8,2)? )? BEGIN?select?SUM(r.price)?from?order?r?where?r.u_id?=?userId?into?total;? END;??

調用存儲過程

?

call?getTotalById(1,?@total);?select?@total;??

結果將返回該用戶所有訂單的合計價格。

復雜一點的過程， 根據用戶id獲取該用戶的所有訂單價格， 并動態的選擇是否加稅。代碼設計如下

?

create?procedure?getTotalByUser2(?in?userId?int,?in?flag?boolean,?--?是否加稅標記?out?total?decimal(8,2)? )? begin?DECLARE?tmptotal?DECIMAL(8,2);?DECLARE?taxrate?int?DEFAULT?6;--?默認的加稅的利率?select?SUM(r.price)?from?order?r?where?r.u_id?=?userId?into?tmptotal;?if?flag?then?select?tmptotal?+?(tmptotal/1000*taxrate)?into?tmptotal;?end?if;?select?tmptotal?into?total;? END;??

該過程傳入三個參數， 用戶id， 是否加稅以及返回的總價格，在過程內部， 定義兩個局部變量tmptotal和taxrate，把查詢出來的結果賦給臨時變量， 在判斷是否加稅。最后把局部變量的值賦給輸出參數。

?

call?getTotalByUser2(1,?false,?@total);?--?不加稅? call?getTotalByUser2(1,?true,?@total);??--?加稅? select?@total;?

?

?

總結

以上是生活随笔為你收集整理的数据库存储过程讲解与实例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。