SQL存儲(chǔ)過(guò)程是提前編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一組SQL語(yǔ)句，可被重復(fù)調(diào)用。存儲(chǔ)過(guò)程有助于提高數(shù)據(jù)庫(kù)性能和安全性，并提供了一種有效的方式來(lái)組織和管理數(shù)據(jù)庫(kù)邏輯。下面我們將詳細(xì)介紹SQL存儲(chǔ)過(guò)程定義、語(yǔ)法與示例，并附上存儲(chǔ)過(guò)程的優(yōu)缺點(diǎn)，以及創(chuàng)建、刪除、加密存儲(chǔ)過(guò)程的示例。

存儲(chǔ)過(guò)程的定義

存儲(chǔ)過(guò)程其實(shí)就是已預(yù)編譯為可執(zhí)行過(guò)程的一個(gè)或多個(gè)SQL語(yǔ)句。通過(guò)調(diào)用和傳遞參數(shù)即可完成該存儲(chǔ)過(guò)程的功能。

創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)法

CREATE PROC | PROCEDURE  procedure_name
[{@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [OUTPUT],
{@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [OUTPUT],
....
]
AS
sql_statements
GO

簡(jiǎn)單示例

CREATE PROC sp_test
@param1 INT,
@param2 VARCHAR(16)
AS
SELECT * FROM test 
WHERE id=@param1
AND t_no=@param2;
GO

上面就是一個(gè)簡(jiǎn)單的示例。

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

存儲(chǔ)過(guò)程的優(yōu)點(diǎn)

1、提高性能

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

2、降低網(wǎng)絡(luò)開(kāi)銷(xiāo)

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

3、便于進(jìn)行代碼移植

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

4、更強(qiáng)的安全性

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

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

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

可以對(duì)過(guò)程進(jìn)行加密，這有助于對(duì)源代碼進(jìn)行模糊處理。

存儲(chǔ)過(guò)程的缺點(diǎn)

1、邏輯處理吃力

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

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

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

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

由于IDE的問(wèn)題，存儲(chǔ)過(guò)程的開(kāi)發(fā)調(diào)試要比一般程序困難。

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

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

5、不支持群集

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

創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程

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

--查詢存儲(chǔ)過(guò)程
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í)行上述存儲(chǔ)過(guò)程：

EXEC PROC_ORDER_COUNT;

創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程

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

--查詢存儲(chǔ)過(guò)程，根據(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í)行上述存儲(chǔ)過(guò)程：

EXEC PROC_ORDER_COUNT  N'廣州';

參數(shù)帶通配符

--查詢訂單編號(hào)頭兩位是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í)行上述存儲(chǔ)過(guò)程：

EXEC PROC_ORDER_INFO; EXEC PROC_ORDER_INFO N'LJ%'; EXEC PROC_ORDER_INFO N'%LJ%';

帶輸出參數(shù)

--根據(jù)訂單查詢的信息，返回訂單的城市及單價(jià)
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í)行上述存儲(chǔ)過(guò)程：

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;

存儲(chǔ)過(guò)程中的插入

--新增訂單信息
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í)行上述存儲(chǔ)過(guò)程：

EXEC PROC_INSERT_ORDER N'LJ0001',N'廣州',35.21;

存儲(chǔ)過(guò)程中的更新

--修改訂單信息
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í)行上述存儲(chǔ)過(guò)程：

EXEC PROC_UPDATE_ORDER N'LJ0001',N'上海',37.21;

存儲(chǔ)過(guò)程中的刪除

--刪除訂單信息
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í)行上述存儲(chǔ)過(guò)程：

EXEC PROC_DELETE_ORDER N'LJ0001';

重復(fù)編譯存儲(chǔ)過(guò)程

重復(fù)編譯存儲(chǔ)過(guò)程的目的是為了提高存儲(chǔ)過(guò)程的執(zhí)行效率。

--重復(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

加密存儲(chǔ)過(guò)程

--查詢存儲(chǔ)過(guò)程，進(jìn)行加密，加密后不能查看和修改源腳本
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í)行上述存儲(chǔ)過(guò)程：

EXEC PROC_ORDER_WITH_ENCRYPTION

希望通過(guò)了解SQL存儲(chǔ)過(guò)程的定義、優(yōu)點(diǎn)、語(yǔ)法和示例，深入了解對(duì)SQL存儲(chǔ)過(guò)程，以便能夠更好地運(yùn)用它來(lái)優(yōu)化數(shù)據(jù)庫(kù)操作。

重要聲明：本文來(lái)自SQL數(shù)據(jù)庫(kù)開(kāi)發(fā)，經(jīng)授權(quán)轉(zhuǎn)載，版權(quán)歸原作者所有，不代表銳成觀點(diǎn)，轉(zhuǎn)載的目的在于傳遞更多知識(shí)和信息。