預存程序是封裝商業邏輯、減少網路往返、提升安全性的核心工具。本文以銀行存款表 bankMoney 為例,循序漸進示範五種常見模式。
Id userID Sex Money
001 Zhangsan 男 30
002 Wangwu 男 50
003 Zhangsan 男 40
CREATE PROCEDURE sp_query_bankMoney
AS
SELECT * FROM bankMoney;
GO
EXEC sp_query_bankMoney;
應用端只需呼叫程序名稱,SQL 細節完全封裝。
CREATE PROCEDURE insert_bank
@param1 CHAR(10), -- id
@param2 VARCHAR(20), -- userID
@param3 VARCHAR(20), -- sex
@param4 INT, -- Money
@param5 INT OUTPUT -- 回傳該 user 總存款
WITH ENCRYPTION
AS
BEGIN
INSERT INTO bankMoney (id, userID, sex, Money)
VALUES (@param1, @param2, @param3, @param4);
SELECT @param5 = SUM(Money)
FROM bankMoney
WHERE userID = 'Zhangsan';
END
GO
-- 呼叫範例
DECLARE @total_price INT;
EXEC insert_bank '004', 'Zhangsan', '男', 100, @total_price OUTPUT;
PRINT '總餘額為 ' + CAST(@total_price AS VARCHAR);
| 型態 | 用法 | 適用場景 |
|---|---|---|
| RETURN | 整數狀態碼 | 成功/失敗代碼 |
| OUTPUT 參數 | 標量值(數值、字串、日期) | 單一計算結果 |
| RESULT SET | SELECT 回傳資料集 | 多筆資料、報表 |
USE pubs;
GO
IF EXISTS (SELECT 1 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
EXECUTE au_info_all;
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
-- 三種呼叫方式皆可
EXECUTE au_info 'Dull', 'Ann';
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann';
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull';
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
-- 多種呼叫組合
EXECUTE au_info2; -- 預設 D% / %
EXECUTE au_info2 'Wh%'; -- 姓氏 Wh%
EXECUTE au_info2 @firstname = 'A%'; -- 名字 A%
EXECUTE au_info2 '[CK]ars[OE]n'; -- 正則式範圍
EXECUTE au_info2 'Hunter', 'Sheryl'; -- 精確匹配
EXECUTE au_info2 'H%', 'S%'; -- 雙模糊
-- 動態 WHERE 子句範例
CREATE PROCEDURE proc_query_like
@like VARCHAR(20)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM dept WHERE 1=1';
IF @like <> ''
SET @sql += N' AND (deptname LIKE ''%'' + @like + ''%'' OR remark LIKE ''%'' + @like + ''%'')';
EXEC sp_executesql @sql, N'@like VARCHAR(20)', @like;
END
善用預存程序參數化、輸出參數、動態 SQL 與預設值,能大幅提升資料庫程式的彈性與可維護性。