萬盛學電腦網

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

SQL Server 預存程序實戰教學:從基本建立到參數、回傳值、輸出參數與結果集

為什麼需要預存程序?

批次指令碼有兩大缺點:無法傳參數、無法模組化重複呼叫。預存程序解決這兩點,並將邏輯下沉至資料庫層,減少網路傳輸、提升效能與資料完整性。

預存程序分類

  • 系統預存程序:sp_ 開頭 (如 sp_help),儲於 master。
  • 擴展預存程序:xp_ 開頭 (如 xp_cmdshell)。
  • 使用者自訂預存程序:開發者建立,本文重點。

建立、修改、刪除語法


-- 建立
CREATE PROC proc_name
AS
BEGIN
    -- 批次陳述式
END
GO

-- 修改 (CREATE → ALTER)
ALTER PROC proc_name
AS
BEGIN ... END
GO

-- 刪除
DROP PROC proc_name;

注意:預存程序本體不可包含 GO (批次分隔符),否則會提前結束建立。若需執行另一批次,請另建 SP 並呼叫。

簡單範例:插入以時間為名的使用者


CREATE PROC insUser
AS
BEGIN
    BEGIN TRAN;
    DECLARE @username VARCHAR(20);
    SET @username = CONVERT(VARCHAR(8), GETDATE(), 112) 
                  + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 8), ':', '');
    IF NOT EXISTS (SELECT * FROM yonghu WHERE yonghuming = @username)
        INSERT INTO yonghu VALUES (@username, '111111', '@163.com', '新用戶');
    COMMIT TRAN;
END
GO

EXEC insUser;  -- 同一秒內重複執行只會插入一筆 (主鍵/唯一約束)

參數傳遞與回傳機制

1. 輸入參數


CREATE PROC queryItem
    @tablename NVARCHAR(10),
    @id VARCHAR(20),
    @idvalue INT
AS
DECLARE @sql VARCHAR(100);
SET @sql = 'SELECT * FROM ' + @tablename + ' WHERE ' + @id + ' = ' + CAST(@idvalue AS VARCHAR);
EXEC(@sql);
GO

-- 呼叫
EXEC queryItem 'yiren', 'yirenid', 10;
EXEC queryItem @tablename='jingjiren', @id='jingjirenid', @idvalue=1;

2. 回傳值 (RETURN) - 僅限 INT


CREATE PROC returnProc
AS
BEGIN
    BEGIN TRAN;
    DECLARE @err INT = 0;
    INSERT INTO yiren (xingming) VALUES ('王美麗');
    SET @err += @@ERROR;
    INSERT INTO yiren (yirenid) VALUES (1);  -- 故意違反 PK
    SET @err += @@ERROR;
    IF @err > 0 ROLLBACK TRAN ELSE COMMIT TRAN;
    RETURN @err;  -- 0=成功, 非0=失敗
END
GO

-- 呼叫捕捉回傳碼
DECLARE @rc INT;
EXEC @rc = returnProc;
PRINT @rc;

3. 輸出參數 (OUTPUT) - 任意型別


CREATE PROC queryProfile
    @id INT,
    @xingming VARCHAR(50) OUTPUT,
    @nianling INT OUTPUT
AS
SELECT @xingming = xingming, @nianling = nianling FROM yiren WHERE yirenid = @id;
GO

-- 呼叫端
DECLARE @name VARCHAR(50), @age INT;
EXEC queryProfile 1, @name OUTPUT, @age OUTPUT;
PRINT '姓名:' + @name + ', 年齡:' + CAST(@age AS VARCHAR);

原理:呼叫端變數傳址給 SP,SP 修改即直接反映呼叫端變數。

4. 結果集 (SELECT) - 供用戶端程式消費


CREATE PROC selectProc
AS
SELECT * FROM yiren;
GO

EXEC selectProc;  -- SSMS 顯示網格 / ADO.NET 填充 DataTable

此模式實現「帶參數的檢視表」。

進階選項

選項語法用途
WITH RECOMPILECREATE PROC ... WITH RECOMPILE每次執行重新編譯,解決參數嗅探導致計畫不穩
WITH ENCRYPTIONCREATE PROC ... WITH ENCRYPTION隱藏定義原始碼 (sp_helptext 顯示加密)

管理工具操作

SSMS 物件總管 → 可程式性 → 預存程序 → 右鍵「新增預存程序」 → 選單「查詢」→「指定範本參數值」 (Ctrl+Shift+M) 填寫 → 編輯 → F5 執行建立。

總結

預存程序是 T-SQL 程式設計的核心單元。掌握 輸入參數 實現通用邏輯、OUTPUT 參數 回傳多型別資料、RETURN 回傳執行狀態碼、結果集 供上層消費、WITH RECOMPILE/ENCRYPTION 控制編譯與安全性,即能構建健壯的資料存取層。

copyright © 萬盛學電腦網 all rights reserved