了解預存程序：高效 SQL 程式設計指南-mysql教程-PHP中文網

了解預存程序：高效 SQL 程式設計指南

Patricia Arquette

發布： 2024-12-28 08:52:11

原創

337 人瀏覽過

Understanding Stored Procedures: A Guide to Efficient SQL Programming

什麼是預存程序？

預存程序是儲存在資料庫伺服器上的一個或多個SQL語句的預編譯集合。它的作用就像一個可重複使用的腳本，可以透過呼叫其名稱、可選地傳遞輸入參數以及檢索輸出參數或結果來執行。預存程序用於封裝複雜的業務邏輯、強制資料完整性並提高查詢效能。

預存程序的主要特點

可重複使用性：

預存程序可以編寫一次並在多個應用程式中重複使用，確保業務邏輯的一致性。
性能：

由於它們是預先編譯的，因此預存程序的執行速度比即席查詢更快。
安全：

它們允許開發人員透過授予執行權限來限制對資料庫的直接訪問，而無需暴露表結構。
維護性：

只需要在預存程序中更改業務邏輯或 SQL 程式碼，減少重複。
網路流量減少:

使用預存程序，可以在一次呼叫中執行多個 SQL 語句，從而減少客戶端與伺服器的通訊。

預存程序的語法

建立預存程序：

CREATE PROCEDURE ProcedureName
    @Parameter1 DataType,
    @Parameter2 DataType OUTPUT
AS
BEGIN
    -- SQL Statements
    SELECT @Parameter2 = COUNT(*) FROM TableName WHERE ColumnName = @Parameter1;
END;

登入後複製

執行預存程序：

DECLARE @OutputParam INT;
EXEC ProcedureName 'InputValue', @OutputParam OUTPUT;
PRINT @OutputParam;

登入後複製

預存程序的類型

系統預存程序:

資料庫系統為管理任務提供的預先定義程序（例如 SQL Server 中的 sp_help、sp_rename）。
使用者定義的預存程序:

由使用者為特定任務創建，例如取得資料、執行計算或修改記錄。
暫存程序:

暫時儲存在資料庫中以供會話使用或直到伺服器重新啟動。以 # 前綴命名。
擴充預存程序:

允許從 SQL Server 內部執行外部程式（在最新版本中已棄用）。

預存程序的範例用例

資料擷取：

CREATE PROCEDURE ProcedureName
    @Parameter1 DataType,
    @Parameter2 DataType OUTPUT
AS
BEGIN
    -- SQL Statements
    SELECT @Parameter2 = COUNT(*) FROM TableName WHERE ColumnName = @Parameter1;
END;

登入後複製

插入資料：

DECLARE @OutputParam INT;
EXEC ProcedureName 'InputValue', @OutputParam OUTPUT;
PRINT @OutputParam;

登入後複製

更新資料：

   CREATE PROCEDURE GetEmployeeDetails
       @DepartmentID INT
   AS
   BEGIN
       SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
   END;

登入後複製

執行計算：

   CREATE PROCEDURE AddNewEmployee
       @Name NVARCHAR(50),
       @Position NVARCHAR(50),
       @Salary DECIMAL(10,2)
   AS
   BEGIN
       INSERT INTO Employees (Name, Position, Salary) VALUES (@Name, @Position, @Salary);
   END;

登入後複製