但也不是說學(xué)不會，今天就給大家好好惡補一下存儲過程的一些常規(guī)用法，一些非常規(guī)用法實在是太多了，這里就不一一列舉了。

作者：丶平凡世界

來源： SQL數(shù)據(jù)庫開發(fā)

創(chuàng)建存儲過程

create proc | procedure procedure_name

[{@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [output],

{@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [output],

....

]

as

SQL_statements

go

示例

create proc sp_test
@param1 int,
@param2 varchar(16) as select * from test  where id=@param1 and t_no=@param2;
go

上面就是一個簡單的示例。

存儲過程在創(chuàng)建階段可以帶參數(shù)或不帶參數(shù)，不帶參數(shù)的一般是執(zhí)行一些不需要傳遞參數(shù)的語句就可以完成的功能，帶參數(shù)那就是需要傳遞參數(shù)的SQL語句，就像上面的示例，傳遞了兩個參數(shù)給SQL語句。帶參數(shù)的一定要定義參數(shù)類型，是字符型的還要定義長度，給參數(shù)加默認(rèn)值是可選的。

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

優(yōu)點：

1、提高性能

SQL語句在創(chuàng)建過程時進行分析和編譯。存儲過程是預(yù)編譯的，在首次運行一個存儲過程時，查詢優(yōu)化器對其進行分析、優(yōu)化，并給出最終被存在系統(tǒng)表中的存儲計劃，這樣，在執(zhí)行過程時便可節(jié)省此開銷。

2、降低網(wǎng)絡(luò)開銷

存儲過程調(diào)用時只需用提供存儲過程名和必要的參數(shù)信息，從而可降低網(wǎng)絡(luò)的流量。

3、便于進行代碼移植

數(shù)據(jù)庫專業(yè)人員可以隨時對存儲過程進行修改，但對應(yīng)用程序源代碼卻毫無影響，從而極大的提高了程序的可移植性。

4、更強的安全性

1）系統(tǒng)管理員可以對執(zhí)行的某一個存儲過程進行權(quán)限限制，避免非授權(quán)用戶對數(shù)據(jù)的訪問

2）在通過網(wǎng)絡(luò)調(diào)用過程時，只有對執(zhí)行過程的調(diào)用是可見的。因此，惡意用戶無法看到表和數(shù)據(jù)庫對象名稱、嵌入自己的 Transact-SQL 語句或搜索關(guān)鍵數(shù)據(jù)。

3）使用過程參數(shù)有助于避免 SQL 注入攻擊。因為參數(shù)輸入被視作文字值而非可執(zhí)行代碼，所以，攻擊者將命令插入過程內(nèi)的 Transact-SQL 語句并損害安全性將更為困難。

4）可以對過程進行加密，這有助于對源代碼進行模糊處理。

劣勢：

1、邏輯處理吃力

SQL本身是一種結(jié)構(gòu)化查詢語言，但不是面向?qū)ο蟮牡模举|(zhì)上還是過程化的語言，面對復(fù)雜的業(yè)務(wù)邏輯，過程化的處理會很吃力。同時SQL擅長的是數(shù)據(jù)查詢而非業(yè)務(wù)邏輯的處理，如果如果把業(yè)務(wù)邏輯全放在存儲過程里面，違背了這一原則。

2、修改參數(shù)復(fù)雜

如果需要對輸入存儲過程的參數(shù)進行更改，或者要更改由其返回的數(shù)據(jù)，則您仍需要更新程序集中的代碼以添加參數(shù)、更新調(diào)用，等等，這時候估計會比較繁瑣了。

3、開發(fā)調(diào)試復(fù)雜

由于IDE的問題，存儲過程的開發(fā)調(diào)試要比一般程序困難。

4、無法應(yīng)用緩存

雖然有全局臨時表之類的方法可以做緩存，但同樣加重了數(shù)據(jù)庫的負(fù)擔(dān)。如果緩存并發(fā)嚴(yán)重，經(jīng)常要加鎖，那效率實在堪憂。

5、不支持群集

數(shù)據(jù)庫服務(wù)器無法水平擴展，或者數(shù)據(jù)庫的切割（水平或垂直切割）。數(shù)據(jù)庫切割之后，存儲過程并不清楚數(shù)據(jù)存儲在哪個數(shù)據(jù)庫中。

存儲過程的具體應(yīng)用

基礎(chǔ)應(yīng)用

1、創(chuàng)建不帶參數(shù)的存儲過程

示例：查詢訂單表中訂單總數(shù)

--查詢存儲過程 IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_COUNT;
GO CREATE procedure PROC_ORDER_COUNT AS 
   SELECT COUNT(OrderID) FROM Orders;
GO --執(zhí)行上述存儲過程： EXEC PROC_ORDER_COUNT;

2、創(chuàng)建帶參數(shù)的存儲過程

示例：根據(jù)城市查詢訂單數(shù)量

--查詢存儲過程，根據(jù)城市查詢總數(shù) IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_COUNT;
GO CREATE procedure PROC_ORDER_COUNT(@city nvarchar(50)) AS    SELECT COUNT(OrderID) FROM Orders WHERE City=@city GO --執(zhí)行上述存儲過程： EXEC PROC_ORDER_COUNT  N'GuangZhou';

進階應(yīng)用

3、參數(shù)帶通配符

--查詢訂單編號頭兩位是LJ的訂單信息，含通配符 IF OBJECT_ID (N'PROC_ORDER_INFO', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_INFO;
GO CREATE procedure PROC_ORDER_INFO
   @OrderID nvarchar(50)='LJ%' --默認(rèn)值 AS 
   SELECT OrderID,City,OrderDate,Price FROM Orders
   WHERE OrderID like @OrderID;
GO --執(zhí)行上述存儲過程： EXEC PROC_ORDER_INFO;
EXEC PROC_ORDER_INFO N'LJ%';
EXEC PROC_ORDER_INFO N'%LJ%';

4、帶輸出參數(shù)

--根據(jù)訂單查詢的信息，返回訂單的城市及單價 IF OBJECT_ID (N'PROC_ORDER_INFO ', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_INFO ;
GO CREATE procedure PROC_ORDER_INFO
   @orderid nvarchar(50),     --輸入?yún)?shù)    @city nvarchar(20) out,    --輸出參數(shù)    @price  float output        --輸入輸出參數(shù) AS 
   SELECT @city=City,@price=Price FROM Orders
   WHERE OrderID=@orderid AND Price=@price;
GO --執(zhí)行上述存儲過程： declare @orderid nvarchar(50),
       @city nvarchar(20),
       @price  int; set @orderid= N'LJ0001'; set @price = 35.21;
exec PROC_ORDER_INFO @orderid,@city out, @price output; select @city, @price;

上面兩個在平時工作中遇到的較少，需要的時候知道怎么用即可，1，2個是必須掌握的操作。

存儲過程進行增刪改

1、新增

--新增訂單信息 IF OBJECT_ID (N'PROC_INSERT_ORDER', N'P') IS NOT NULL
   DROP procedure PROC_INSERT_ORDER;
GO CREATE procedure PROC_INSERT_ORDER
   @orderid  nvarchar(50),
   @city nvarchar(20),
   @price float AS 
   INSERT INTO Orders(OrderID,City,Price)
   VALUES(@orderid,@city,@price) GO --執(zhí)行 EXEC PROC_INSERT_ORDER N'LJ0001',N'GuangZhou',35.21;

2、修改

--修改訂單信息
IF OBJECT_ID (N'PROC_UPDATE_ORDER', N'P') IS NOT NULL
DROP procedure PROC_UPDATE_ORDER;
GO
CREATE procedure PROC_UPDATE_ORDER
@orderid nvarchar(50),
@city nvarchar(20),
@price float
AS
UPDATE Orders SET OrderID=@orderid,City=@city,Price=@price;
GO
--執(zhí)行
EXEC PROC_UPDATE_ORDER N'LJ0001',N'ShangHai',37.21;

3、刪除

--修改訂單信息 IF OBJECT_ID (N'PROC_DELETE_ORDER', N'P') IS NOT NULL
   DROP procedure PROC_DELETE_ORDER;
GO CREATE procedure PROC_DELETE_ORDER
   @orderid  nvarchar(50), AS 
   DELETE  FROM Orders WHERE OrderID=@orderid;
GO --執(zhí)行 EXEC PROC_DELETE_ORDER N'LJ0001';

存儲過程其他功能

這部分是選修內(nèi)容，有興趣的可以了解一下

1、重復(fù)編譯存儲過程

--重復(fù)編譯 IF OBJECT_ID (N'PROC_ORDER_WITH_RECOMPILE', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_WITH_RECOMPILE;
GO CREATE procedure PROC_ORDER_WITH_RECOMPILE with recompile --重復(fù)編譯 AS 
   SELECT * FROM Orders;
GO

2、加密存儲過程

--查詢存儲過程，進行加密，加密后不能查看和修改源腳本 IF OBJECT_ID (N'PROC_ORDER_WITH_ENCRYPTION', N'P') IS NOT NULL
   DROP procedure PROC_ORDER_WITH_ENCRYPTION;
GO CREATE procedure PROC_ORDER_WITH_ENCRYPTION with encryption --加密 AS 
   SELECT * FROM Orders;
GO --執(zhí)行上述存儲過程： EXEC PROC_ORDER_WITH_ENCRYPTION

執(zhí)行完的效果如圖：

以上就是存儲過程的詳細(xì)用法了。還有不明白或有疑問的地方，歡迎留言討論。