SQL 中的函數、過程、遊標和觸發器

完整指南：SQL 中的函數、流程、遊標和觸發器

在關聯式資料庫管理系統（RDBMS）中，函數、過程、遊標和觸發器等各種元件至關重要增強資料庫系統的靈活性和功能性的作用。它們允許開發人員實現自訂業務邏輯、自動執行重複任務並更有效地管理資料。

本指南將提供這些組件的全面解釋，以及每個組件的範例程式碼片段。

---

1. SQL 中的函數

SQL 中的

函數

是一個儲存程序，可以接受輸入、執行操作並傳回值。它類似於過程，但函數必須傳回一個值，並且可以像任何其他表達式一樣在查詢中使用。 要點：

可以從查詢呼叫函數。

• 函數傳回單一值。
• 它們可以接受輸入參數。
• 它們通常用於計算、轉換和資料檢索。
函數範例（SQL Server 語法）

讓我們寫一個簡單的函數來計算數字的平方。

CREATE FUNCTION dbo.SquareNumber (@Number INT)
RETURNS INT
AS
BEGIN
    RETURN @Number * @Number
END

用法：

此函數接受一個整數作為輸入，計算其平方並傳回結果。

SELECT dbo.SquareNumber(4); -- Output: 16

2. SQL 中的過程

---

過程

（也稱為預存程序）是一組可以作為一個單元執行的SQL 語句。過程可以接受參數，執行插入、更新、刪除和選擇等操作，並傳回多個結果（但不能像函數那樣直接傳回單一值）。 要點：

過程不一定會傳回值，但它們可能會傳回多個結果集。

• 它們可以執行多種操作。
• 可以使用 EXEC 指令明確呼叫過程。
過程範例（SQL Server 語法）

讓我們寫一個程式來更新員工的薪水。

CREATE PROCEDURE dbo.UpdateSalary 
    @EmployeeID INT, 
    @NewSalary DECIMAL
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmployeeID;
END

用法：

此流程將 EmployeeID 和 NewSalary 作為輸入，更新員工的薪資，並且不傳回任何值。

EXEC dbo.UpdateSalary @EmployeeID = 101, @NewSalary = 75000;

3. SQL中的遊標

---

SQL 中的

遊標 是一種資料庫對象，可讓您一次檢索和處理查詢返回的每一行。當您需要執行逐行操作（例如更新或刪除）時，這特別有用，而這些操作在單一基於集合的操作中不容易處理。

要點：

• 遊標可用來迭代查詢結果集。
• 它們通常在基於集合的運算不夠用時使用。
• 遊標可以分為不同類型（靜態、動態、僅向前等）。

遊標範例（SQL Server 語法）

我們來寫一個例子，使用遊標將所有員工的薪水更新 10%。

CREATE FUNCTION dbo.SquareNumber (@Number INT)
RETURNS INT
AS
BEGIN
    RETURN @Number * @Number
END

說明：

1. 我們宣告一個遊標SalaryCursor，它從Employees 表中選擇EmployeeID 和Salary。
2. 我們打開遊標並將第一行取得到變數中。
3. 在 WHILE 循環內，我們將每位員工的薪水乘以 1.1（增加 10%）來更新。
4. 處理完所有行後，我們關閉並釋放遊標。

---

4. SQL中的觸發器

觸發器是一種特殊類型的預存程序，當發生特定資料庫事件（例如表上的 INSERT、UPDATE 或 DELETE）時，它會自動執行（或「觸發」）。觸發器對於執行業務規則、維護資料完整性或在發生變更時自動更新相關表非常有用。

要點：

• 觸發器可以是之前或之後事件（插入、更新、刪除）。
• 觸發器可以為每個語句觸發一次或每行觸發一次（取決於類型）。
• 它們通常用於執行完整性規則或追蹤變更。

觸發器範例（SQL Server 語法）

讓我們建立一個觸發器，每當員工的薪資更新時，該觸發器都會自動更新 LastModified 欄位。

SELECT dbo.SquareNumber(4); -- Output: 16

說明：

1. 觸發器 trg_UpdateSalary 在員工表上執行 UPDATE 操作後觸發。
2. 在觸發器內，我們檢查 Salary 欄位是否已使用 UPDATE() 函數更新。
3. 如果薪水已更新，我們將使用目前日期和時間 (GETDATE()) 修改 LastModified 欄位。
4. 插入表是一個特殊的表，其中包含更新操作後的新值，我們用它來更新已修改員工的 LastModified 欄位。

---

SQL 元件總結

Component	Description	Example Use Case
Function	A stored program that returns a single value and can be used in queries.	Calculate the square of a number.
Procedure	A stored program that can perform multiple actions (insert, update, delete) but does not return a value.	Update an employee’s salary.
Cursor	A mechanism for iterating over a result set row-by-row, used for operations that cannot be easily expressed in set-based SQL.	Update all employees’ salaries by a fixed percentage.
Trigger	A stored program that automatically executes when specific database events (INSERT, UPDATE, DELETE) occur.	Automatically update a timestamp column when a record is modified.

---

結論

• 函數和過程對於模組化資料庫中的業務邏輯和可重複使用操作至關重要。函數更注重返回值，而過程可以處理多個任務，但不直接傳回值。
• 遊標 當您需要逐行處理資料時使用，儘管基於集合的操作通常更有效。
• 觸發器允許自動回應資料庫事件，確保資料完整性並執行規則，無需手動幹預。

每個元件都有獨特的用途，使您的資料庫更加靈活、可維護和高效，特別是在複雜的資料庫環境中。

以上是SQL 中的函數、過程、遊標和觸發器的詳細內容。更多資訊請關注PHP中文網其他相關文章！