什麼是預存程序？

SQL（結構化查詢語言）是管理和操作關係型資料庫的標準語言。其強大且常用的功能之一是預存程序。預存程序是一組預先編譯並儲存在資料庫中的SQL語句，可以接受輸入參數、執行操作並傳回結果。讓我們探討一下什麼是預存程序以及如何建立預存程序。

預存程序簡介

預存程序聽起來可能是個複雜的術語，但它們是高效資料庫管理的基礎。讓我們從它的定義開始。

什麼是預存程序？

預存程序是一系列預先定義並儲存在資料庫伺服器上的 SQL 語句。當需要執行這些操作時，可以透過呼叫預存程序的名稱來執行，而不用發送多個單獨的查詢命令。

這是一個簡化的範例，展示如何在 SQL Server 中建立簡單的預存程序：

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

以下是一些儲存過程的關鍵元件：

• 輸入參數：這些是從外部傳遞給預存程序的值，用於自訂預存程序的行為。輸入參數允許預存程序根據不同的條件執行不同的操作。
• 輸出參數：與輸入參數類似，輸出參數也是預存程序的一部分，但它們的作用是向呼叫者傳回值而不是接收值。
• 局部變數：這些是在預存程序中聲明的變量，用於儲存執行期間的中間結果或計算值。局部變數僅在預存程序的上下文中可見，並且可以在其生命週期內多次分配。
• SQL語句：它們構成了預存程序的核心邏輯，包括但不限於查詢、插入、更新和刪除資料。

這些元件協同工作，使預存程序成為執行資料庫操作的可重複使用且高效的方式。透過將常見的資料庫任務封裝在預存程序中，您可以簡化應用程式開發，同時提高效能和安全性。

儲存過程如何運作

預存程序在資料庫伺服器內部執行，這意味著它們可以比從客戶端相繼發送多個查詢更有效地完成操作並且執行速度更快。此外，使用預存程序可以顯著減少網路流量，因為只需將最終結果集從伺服器傳輸到用戶端，而不需要來回傳輸每個單獨查詢的結果。這樣既提高了資料處理的速度，也減少了網路頻寬的使用。

資料庫管理中的角色

預存程序在資料庫管理中起著核心作用，因為它們將業務邏輯集中儲存在資料庫伺服器上。這樣做可以確保關鍵操作始終以一致、安全和高效的方式執行。具體來說，儲存過程可以幫助：

• 維護資料完整性：透過確保所有資料操作都遵循預定的規則和約束，預存程序有助於維護資料的完整性和一致性。
• 執行業務邏輯：將複雜的業務規則封裝在預存程序中，確保這些規則得到嚴格執行，並且不會受到客戶端程式碼變更的影響。
• 簡化資料庫互動：預存程序透過提供封裝複雜操作的接口，降低了應用程式與資料庫互動的複雜性，使開發和維護變得更加容易。

使用預存程序的好處

使用預存程序有幾個關鍵優點：

1. 增強的性能：

• 預編譯的預存程序執行速度較快。
• 提高回應速度並更有效地利用伺服器資源。

1. 可重複使用性和可維護性：

• 預存程序可以多次調用，減少程式碼重複。
• 預存程序的更新將在所有使用它們的地方生效，確保一致性並減少錯誤。

1. 資料安全：

• 控制資料庫存取並限制直接操作表的能力。
• 透過預存程序提供安全層，防止未經授權的存取和惡意攻擊。

與預存程序一起使用的常用命令

現在讓我們來看看與預存程序配對的有用指令。

創建程式

如前所述，該指令用於在資料庫中定義一個新的預存程序。以下是使用此函數的預存程序的範例：

假設我們有一個名為「Employees」的表，其中包含以下列：

• 員工ID
• 名字
• 姓氏
• 部門ID
• 薪水

我們想要建立一個預存程序來檢索屬於特定部門的所有員工。

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

執行

此指令用於執行預存程序。它也可用於傳遞輸入和輸出參數。對於我們先前的範例，「EXEC」指令如下所示：

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

改變程式

此命令可讓您修改現有的預存程序，而無需刪除並重新建立它。繼續前面的例子，如果我們想修改名為「GetEmployeesByDepartment」的儲存過程，新增一個額外的薪資過濾器，即我們想要檢索特定部門中薪資大於一定金額的員工資訊。

這是一個範例：

EXEC GetEmployeesByDepartment @DepartmentID = 1;

下降程序

如果不再需要某個預存程序，可以使用 DROP PROCEDURE 指令將其從資料庫中刪除。

ALTER PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT,
   @MinSalary DECIMAL(10, 2)
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID AND Salary > @MinSalary;
END;

如何建立和使用預存程序

我們將在三個領域研究創建和使用預存程序：

• MySQL
• SQL Server
• 甲骨文

MySQL

在 MySQL 中建立預存程序相當簡單。您可以使用「CREATE PROCEDURE」語句定義流程、指定參數並編寫 SQL 程式碼。

你可以這樣做：

第1步：建立員工表

首先，讓我們建立一個範例員工表來填入我們將要使用的資料。

DROP PROCEDURE GetEmployeesByDepartment

第 2 步：插入範例數據

將一些範例資料插入員工表中。

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

第 3 步：建立預存程序

讓我們建立一個預存程序來根據部門檢索員工。

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

第四步：呼叫儲存過程

要呼叫預存程序並檢索特定部門的員工，請使用 CALL 語句。

CREATE PROCEDURE GetEmployeesByDepartment(IN depID INT)
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = depID;
END;

SQL伺服器

在 SQL Server 中，預存程序的建立和執行略有不同，但沒有發生很大的變化。這是一個例子：

第 1 步：建立員工表

首先，讓我們建立一個範例員工表。

CALL GetEmployeesByDepartment(1);

第 2 步：插入範例數據

接下來，我們將向員工表中插入一些範例資料。

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

第 3 步：建立預存程序

讓我們建立一個預存程序來根據部門檢索員工。

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

第四步：執行儲存程序

要執行預存程序並檢索特定部門的員工，請使用 EXEC 語句。

EXEC GetEmployeesByDepartment @DepartmentID = 1;

甲骨文

Oracle 也支援預存程序。以下是有關如何使用 SQL 在 Oracle 中實現它們的逐步指南。

第1步：建立員工表

首先，讓我們建立一個範例員工表。

ALTER PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT,
   @MinSalary DECIMAL(10, 2)
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID AND Salary > @MinSalary;
END;

第 2 步：插入範例數據

接下來，我們將一些範例資料插入員工表中以建立資料集。

DROP PROCEDURE GetEmployeesByDepartment

第 3 步：建立預存程序

讓我們建立一個預存程序來根據部門檢索員工。

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

設計儲存流程：最佳實踐

結束本次實作介紹後，讓我們來看看設計預存程序的一些最佳實務。

使用參數化查詢

預存程序中的參數化查詢有助於防止 SQL 注入攻擊。始終使用參數，而不是將使用者輸入直接連接到 SQL 語句中。

例如，不要使用這個：

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

用這個：

CREATE PROCEDURE GetEmployeesByDepartment(IN depID INT)
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = depID;
END;

限制對基礎表的訪問

如前所述，預存程序可以透過限制對基礎表的直接存取來充當安全層。這降低了敏感資料被揭露的風險。

最佳化SQL程式碼

為了確保預存程序高效運行，應該對它們進行效能最佳化。這意味著減少不必要的計算並充分利用索引。您可以透過分析查詢執行計劃來識別並解決效能瓶頸，從而提高查詢效率。

例如，您應該避免使用「SELECT *」來檢索表中的所有字段，因為這會增加傳輸的資料量並降低效率。相反，您應該僅選擇需要的字段，以縮小資料檢索的範圍以提高效能。

記錄您的預存程序

記錄程式碼也適用於預存程序的編寫。這對於其他開發人員了解每個流程的作用和功能至關重要。它還促進一致的命名約定和編碼風格。

這個過程可以透過在預存程序中新增註解或維護單獨的文件來實現。例如：

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

維護版本控制

版本控制對於管理和追蹤預存程序的變更至關重要​​。維護一個包含預存程序腳本及其文件的完整變更歷史記錄的儲存庫很有幫助。這不僅可以更輕鬆地追蹤所有修改，還可以確保不同部署環境之間的一致性。

最後的想法

預存程序是一種高效率且安全的資料庫管理方式。它們具有許多優點，如果與正確的最佳實踐結合使用，可以顯著提高組織內資料分析的效率和有效性。

---

社群

前往 Chat2DB 網站
？加入 Chat2DB 社群
？在 X 上關注我們
？在 Discord 上找到我們

以上是什麼是預存程序？的詳細內容。更多資訊請關注PHP中文網其他相關文章！