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

作者：丶平凡世界

來源： SQL數據庫開發

創建存儲過程

create proc | procedure procedure_name

[{@參數數據類型} [=默認值] [output],

{@參數數據類型} [=默認值] [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

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

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

存儲過程的優點和缺點

優點：

1、提高性能

SQL語句在創建過程時進行分析和編譯。存儲過程是預編譯的，在首次運行一個存儲過程時，查詢優化器對其進行分析、優化，并給出最終被存在系統表中的存儲計劃，這樣，在執行過程時便可節省此開銷。

2、降低網絡開銷

存儲過程調用時只需用提供存儲過程名和必要的參數信息，從而可降低網絡的流量。

3、便于進行代碼移植

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

4、更強的安全性

1）系統管理員可以對執行的某一個存儲過程進行權限限制，避免非授權用戶對數據的訪問

2）在通過網絡調用過程時，只有對執行過程的調用是可見的。因此，惡意用戶無法看到表和數據庫對象名稱、嵌入自己的 Transact-SQL 語句或搜索關鍵數據。

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

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

劣勢：

1、邏輯處理吃力

SQL本身是一種結構化查詢語言，但不是面向對象的的，本質上還是過程化的語言，面對復雜的業務邏輯，過程化的處理會很吃力。同時SQL擅長的是數據查詢而非業務邏輯的處理，如果如果把業務邏輯全放在存儲過程里面，違背了這一原則。

2、修改參數復雜

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

3、開發調試復雜

由于IDE的問題，存儲過程的開發調試要比一般程序困難。

4、無法應用緩存

雖然有全局臨時表之類的方法可以做緩存，但同樣加重了數據庫的負擔。如果緩存并發嚴重，經常要加鎖，那效率實在堪憂。

5、不支持群集

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

存儲過程的具體應用

基礎應用

1、創建不帶參數的存儲過程

示例：查詢訂單表中訂單總數

--查詢存儲過程 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 --執行上述存儲過程： EXEC PROC_ORDER_COUNT;

2、創建帶參數的存儲過程

示例：根據城市查詢訂單數量

--查詢存儲過程，根據城市查詢總數 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 --執行上述存儲過程： EXEC PROC_ORDER_COUNT  N'GuangZhou';

進階應用

3、參數帶通配符

--查詢訂單編號頭兩位是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%' --默認值 AS 
   SELECT OrderID,City,OrderDate,Price FROM Orders
   WHERE OrderID like @OrderID;
GO --執行上述存儲過程： EXEC PROC_ORDER_INFO;
EXEC PROC_ORDER_INFO N'LJ%';
EXEC PROC_ORDER_INFO N'%LJ%';

4、帶輸出參數

--根據訂單查詢的信息，返回訂單的城市及單價 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),     --輸入參數    @city nvarchar(20) out,    --輸出參數    @price  float output        --輸入輸出參數 AS 
   SELECT @city=City,@price=Price FROM Orders
   WHERE OrderID=@orderid AND Price=@price;
GO --執行上述存儲過程： 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 --執行 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
--執行
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 --執行 EXEC PROC_DELETE_ORDER N'LJ0001';

存儲過程其他功能

這部分是選修內容，有興趣的可以了解一下

1、重復編譯存儲過程

--重復編譯 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 --重復編譯 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 --執行上述存儲過程： EXEC PROC_ORDER_WITH_ENCRYPTION

執行完的效果如圖：

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