萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 預存程序實戰教學:從基本查詢到參數、輸出、動態 SQL 與分頁應用

預存程序是封裝商業邏輯、減少網路往返、提升安全性的核心工具。本文以銀行存款表 bankMoney 為例,循序漸進示範五種常見模式。

範例資料

Id   userID   Sex   Money
001  Zhangsan 男     30
002  Wangwu   男     50
003  Zhangsan 男     40

1. 單一結果集查詢

CREATE PROCEDURE sp_query_bankMoney
AS
    SELECT * FROM bankMoney;
GO

EXEC sp_query_bankMoney;

應用端只需呼叫程序名稱,SQL 細節完全封裝。

2. 輸入參數 + 輸出參數(新增並回傳總額)

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 SETSELECT 回傳資料集多筆資料、報表

3. 複雜聯查(無參數)

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;

4. 帶參數查詢(作者姓名)

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';

5. 通配符參數(模糊查詢)

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%';         -- 雙模糊

進階補充:動態 SQL 與分頁

-- 動態 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 與預設值,能大幅提升資料庫程式的彈性與可維護性。

copyright © 萬盛學電腦網 all rights reserved