萬盛學電腦網

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

SQL Server 預存程序完整開發指南:建立、參數、輸出、加密、游標與分頁

為什麼使用預存程序?

  • 模組化重用:一次編寫,多處呼叫,修改不影響應用程式碼。
  • 預先編譯:首次執行產生執行計畫並快取,後續執行省去編譯優化開銷。
  • 減少網路流量:呼叫端僅傳送 EXEC proc_name 參數,而非完整 T-SQL 批次。
  • 安全性:可只授予 EXECUTE 權限,不直接授予資料表權限。

系統預存程序速查

儲存於 master 資料庫,sp_ 開頭,任何資料庫均可呼叫。


EXEC sp_databases;           -- 列出資料庫
EXEC sp_tables;              -- 列出資料表
EXEC sp_columns 'student';   -- 列出欄位
EXEC sp_helpindex 'student'; -- 列出索引
EXEC sp_helptext 'sp_who';   -- 查看系統 SP 定義
EXEC sp_rename 'stu', 'stud';       -- 重命名表
EXEC sp_rename 'stud.name', 'sName', 'COLUMN'; -- 重命名欄位

使用者自訂預存程序

基本語法


CREATE PROC[EDURE] 程序名
    [@參數名 資料型別 [= 預設值] [OUTPUT], ...]
AS
BEGIN
    -- T-SQL 陳述式
END

1. 無參數、無回傳


CREATE PROC proc_get_student
AS
SELECT * FROM student;
GO

EXEC proc_get_student;

2. 修改預存程序


ALTER PROC proc_get_student
AS
SELECT id, name FROM student;  -- 變更欄位
GO

3. 帶輸入參數


CREATE PROC proc_find_stu
    @startId INT, @endId INT
AS
SELECT * FROM student WHERE id BETWEEN @startId AND @endId;
GO

EXEC proc_find_stu 2, 4;
EXEC proc_find_stu @startId=2, @endId=4;  -- 命名參數,順序可互換

4. 帶預設值參數


CREATE PROC proc_find_by_name
    @name VARCHAR(20) = '%j%', @nextName VARCHAR(20) = '%'
AS
SELECT * FROM student WHERE name LIKE @name AND name LIKE @nextName;
GO

EXEC proc_find_by_name;              -- 使用預設值
EXEC proc_find_by_name '%o%', 't%';  -- 傳遞值

5. 輸出參數 (OUTPUT)


CREATE PROC proc_get_profile
    @id INT,
    @name VARCHAR(20) OUTPUT,
    @age INT OUTPUT
AS
SELECT @name = name, @age = age FROM student WHERE id = @id;
GO

-- 呼叫端必須宣告變數並加 OUTPUT
DECLARE @n VARCHAR(20), @a INT;
EXEC proc_get_profile 1, @n OUTPUT, @a OUTPUT;
PRINT @n + ' ' + CAST(@a AS VARCHAR);

6. 不快取執行計畫 (WITH RECOMPILE)


CREATE PROC proc_temp WITH RECOMPILE
AS SELECT * FROM student;  -- 每次重新編譯,適用參數嗅探嚴重場景
GO

7. 加密預存程序 (WITH ENCRYPTION)


CREATE PROC proc_secret WITH ENCRYPTION
AS SELECT * FROM student;
GO

EXEC sp_helptext 'proc_secret';  -- 回傳「文字已加密」

注意:加密後無法檢視定義,也無法透過複寫發佈,請妥善保存原始碼。

8. 游標參數 (OUTPUT CURSOR)


CREATE PROC proc_cursor @cur CURSOR VARYING OUTPUT
AS
SET @cur = CURSOR FORWARD_ONLY STATIC FOR
    SELECT id, name, age FROM student;
OPEN @cur;
GO

-- 呼叫端
DECLARE @c CURSOR, @id INT, @n VARCHAR(20), @a INT;
EXEC proc_cursor @cur = @c OUTPUT;
FETCH NEXT FROM @c INTO @id, @n, @a;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @id + ' ' + @n + ' ' + CAST(@a AS VARCHAR);
    FETCH NEXT FROM @c INTO @id, @n, @a;
END
CLOSE @c; DEALLOCATE @c;

9. 分頁預存程序 (ROW_NUMBER 實作)


CREATE PROC pro_page
    @pageIndex INT, @pageSize INT
AS
DECLARE @start INT = (@pageIndex - 1) * @pageSize + 1;
DECLARE @end INT = @start + @pageSize - 1;

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM student
) t WHERE t.rn BETWEEN @start AND @end;
GO

EXEC pro_page 2, 10;  -- 第 2 頁,每頁 10 筆

RAISERROR 自訂錯誤


RAISERROR('自訂錯誤訊息', 16, 1);  -- 嚴重等級 16 (使用者可更正), 狀態 1
RAISERROR(50001, 16, 1);           -- 使用 sys.messages 定義的訊息 ID
  • 嚴重等級 0-18:任何使用者可指定。
  • 19-25:需 sysadmin 或 ALTER TRACE 權限,且必須加 WITH LOG。

呼叫預存程序的完整模式


-- 1. 簡單呼叫
EXEC proc_name;

-- 2. 位置參數
EXEC proc_name 'value1', 100;

-- 3. 命名參數 (建議)
EXEC proc_name @param1 = 'value1', @param2 = 100;

-- 4. 捕捉回傳碼 (INT)
DECLARE @rc INT;
EXEC @rc = proc_name @param1 = 'v1';
PRINT @rc;

-- 5. 捕捉 OUTPUT 參數
DECLARE @out VARCHAR(50);
EXEC proc_name @param1 = 'v1', @outParam = @out OUTPUT;
PRINT @out;

總結

預存程序是 SQL Server 伺服器端程式設計的核心。掌握參數傳遞 (輸入/輸出/預設值)、回傳值與結果集、加密/重編譯選項、游標與分頁模式,能構建高效、安全、可維護的資料存取層。

copyright © 萬盛學電腦網 all rights reserved