萬盛學電腦網

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

SQL Server 儲存程序完整指南:建立、參數、加密與分頁實戰

什麼是儲存程序?

儲存程序是一組為完成特定功能而編寫、經編譯後儲存在資料庫中的 SQL 陳述式集合。使用者透過呼叫程序名稱並傳遞參數即可執行。它類似程式語言中的方法,支援邏輯控制、資料操作、輸入/輸出參數、結果集與回傳值。

儲存程序的四大優勢

  • 模組化程式設計:一次建立、多處呼叫,修改程序不需動應用程式碼,提升可攜性。
  • 執行速度快:首次執行經查詢最佳化器分析並快取執行計畫,後續直接重用,優於每次皆需編譯的批次處理。
  • 降低網路流量:客戶端只傳送呼叫指令,而非大量 SQL 陳述式。
  • 安全性機制:可針對程序授權,間接控制底層資料存取,防止未授權查詢。

系統儲存程序

儲存在 master 資料庫,名稱以 sp_ 開頭,可跨資料庫呼叫。常用範例:

EXEC sp_databases;        -- 檢視資料庫
EXEC sp_tables;           -- 檢視資料表
EXEC sp_columns student;  -- 檢視欄位
EXEC sp_helpindex student;-- 檢視索引
EXEC sp_rename 'student', 'stuInfo'; -- 重命名資料表
EXEC sp_helptext 'sp_stored_procedures'; -- 檢視程序定義

使用者自訂儲存程序

基本語法

CREATE PROC | PROCEDURE proc_name
    [{@param data_type} [= default] [OUTPUT], ...]
AS
    SQL_statements

1. 無參數程序

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'proc_get_student')
    DROP PROC proc_get_student;
GO
CREATE PROC proc_get_student
AS
    SELECT * FROM student;
GO
EXEC proc_get_student;

2. 修改程序

ALTER PROC proc_get_student
AS
    SELECT * FROM student;

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;

4. 含預設值與萬用字元參數

CREATE PROC proc_findStudentByName
    @name VARCHAR(20) = '%j%',
    @nextName VARCHAR(20) = '%'
AS
    SELECT * FROM student WHERE name LIKE @name AND name LIKE @nextName;
GO
EXEC proc_findStudentByName;              -- 使用預設值
EXEC proc_findStudentByName '%o%', 't%';  -- 自訂參數

5. 輸出參數

CREATE PROC proc_getStudentRecord
    @id INT,
    @name VARCHAR(20) OUT,
    @age VARCHAR(20) OUTPUT
AS
    SELECT @name = name, @age = age FROM student WHERE id = @id;
GO
-- 呼叫範例
DECLARE @id INT = 7, @name VARCHAR(20), @temp VARCHAR(20) = '1';
EXEC proc_getStudentRecord @id, @name OUT, @temp OUTPUT;
SELECT @name, @temp;

6. 不快取執行計畫

CREATE PROC proc_temp WITH RECOMPILE
AS SELECT * FROM student;

7. 加密程序

CREATE PROC proc_temp_encryption WITH ENCRYPTION
AS SELECT * FROM student;

8. 遊標參數

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 @exec_cur CURSOR, @id INT, @name VARCHAR(20), @age INT;
EXEC proc_cursor @cur = @exec_cur OUTPUT;
FETCH NEXT FROM @exec_cur INTO @id, @name, @age;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT 'id: ' + CAST(@id AS VARCHAR) + ', name: ' + @name + ', age: ' + CAST(@age AS CHAR);
    FETCH NEXT FROM @exec_cur INTO @id, @name, @age;
END
CLOSE @exec_cur; DEALLOCATE @exec_cur;

9. 分頁儲存程序

-- 方式一:ROW_NUMBER + 範圍
CREATE PROC pro_page
    @startIndex INT, @endIndex INT
AS
    SELECT COUNT(*) FROM product;
    SELECT * FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY pid) AS rowId, *
        FROM product
    ) temp WHERE temp.rowId BETWEEN @startIndex AND @endIndex;

-- 方式二:頁碼/頁大小
CREATE PROC pro_stu
    @pageIndex INT, @pageSize INT
AS
    DECLARE @startRow INT, @endRow INT;
    SET @startRow = (@pageIndex - 1) * @pageSize + 1;
    SET @endRow = @startRow + @pageSize - 1;
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER(ORDER BY id ASC) AS number FROM student
    ) t WHERE t.number BETWEEN @startRow AND @endRow;

RAISERROR 錯誤處理

RAISERROR(
    {msg_id | msg_str | @local_variable},
    severity, state,
    [argument [, ...n]]
    [WITH option [, ...n]]
);
  • msg_str:自訂訊息,最長 2047 字元
  • severity:0-18 為使用者可指定,19+ 需系統管理員權限
  • state:0-255,協助追蹤錯誤來源

總結

儲存程序是 SQL Server 開發的核心利器,善用參數化、加密、分頁與錯誤處理,能大幅提升效能、安全性與維護性。建議在專案中建立統一的程式碼範本,規範命名與錯誤處理模式。

copyright © 萬盛學電腦網 all rights reserved