SQL の関数、プロシージャ、カーソル、トリガー

フルガイド: SQL の関数、プロシージャ、カーソル、トリガー

リレーショナル データベース管理システム (RDBMS) では、関数、プロシージャ、カーソル、トリガーなどのさまざまなコンポーネントが重要な役割を果たします。データベース システムの柔軟性と機能性を強化する役割を果たします。これらにより、開発者はカスタム ビジネス ロジックを実装し、反復的なタスクを自動化し、より効果的にデータを管理できるようになります。

このガイドでは、これらのコンポーネントの包括的な説明と、各コンポーネントのサンプル コード スニペットを提供します。

---

1. SQL の関数

SQL の

関数 は、入力を受け入れ、操作を実行し、値を返すことができるストアド プログラムです。これはプロシージャに似ていますが、関数は値を返す必要があり、他の式と同様にクエリ内で使用できます。

重要なポイント:

関数はクエリから呼び出すことができます。
関数は単一の値を返します。
入力パラメータを受け取ることができます。
これらは通常、計算、変換、データ取得に使用されます。

関数の例 (SQL Server 構文)

数値の 2 乗を計算する簡単な関数を書いてみましょう。

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

使用法:

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

この関数は、整数を入力として受け取り、その二乗を計算し、結果を返します。

---

2. SQLでの手続き

プロシージャ (ストアド プロシージャ とも呼ばれる) は、1 つの単位として実行できる SQL ステートメントのセットです。プロシージャはパラメータを受け取り、挿入、更新、削除、選択などの操作を実行し、複数の結果を返すことができます (ただし、関数のように単一の値を直接返すことはできません)。

重要なポイント:

プロシージャは必ずしも値を返すとは限りませんが、複数の結果セットを返す場合があります。
複数の操作を実行できます。
プロシージャは、EXEC コマンドを使用して明示的に呼び出すことができます。

手順例（SQL Server構文）

従業員の給与を更新する手順を書いてみましょう。

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

使用法:

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

このプロシージャは、EmployeeID と NewSalary を入力として受け取り、従業員の給与を更新しますが、値は返しません。

---

3. SQL のカーソル

SQL の

カーソル

は、クエリによって返された各行を一度に 1 つずつ取得して処理できるデータベース オブジェクトです。これは、更新や削除など、単一のセットベースの操作では簡単に処理できない行ごとの操作を実行する必要がある場合に特に便利です。

重要なポイント:

• カーソルを使用して、クエリ結果セットを反復処理できます。
• これらは通常、セットベースの操作では不十分な場合に使用されます。
• カーソルはさまざまなタイプ (静的、動的、前方専用など) に分類できます。

カーソルの例 (SQL Server 構文)

カーソルを使用して、全従業員の給与を 10% 更新する例を書いてみましょう。

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

説明:

1. Employees テーブルから EmployeeID と Salary を選択するカーソル SalaryCursor を宣言します。
2. カーソルを開いて最初の行を変数にフェッチします。
3. WHILE ループ内で、各従業員の給与を 1.1 (10% 増加) で乗算して更新します。
4. すべての行を処理した後、カーソルを閉じて割り当てを解除します。

---

4. SQL のトリガー

トリガーは、テーブルに対する INSERT、UPDATE、DELETE などの特定のデータベース イベントが発生したときに自動的に実行 (または「起動」) する特別な種類のストアド プロシージャです。トリガーは、ビジネス ルールの適用、データの整合性の維持、または変更が発生した場合の関連テーブルの自動更新に役立ちます。

重要なポイント:

• トリガーは、イベント (挿入、更新、削除) の BEFORE または AFTER にすることができます。
• トリガーは、ステートメントごとに 1 回、または行ごとに 1 回 (タイプに応じて) 起動できます。
• 整合性ルールを強制したり、変更を追跡したりするためによく使用されます。

トリガーの例 (SQL Server 構文)

従業員の給与が更新されるたびに LastModified 列を自動的に更新するトリガーを作成してみましょう。

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

説明:

1. トリガー trg_UpdateSalary は、Employees テーブルに対する UPDATE 操作の後に起動されます。
2. トリガー内で、UPDATE() 関数を使用して Salary 列が更新されたかどうかを確認します。
3. 給与が更新された場合は、LastModified 列を現在の日時で変更します (GETDATE())。
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 中国語 Web サイトの他の関連記事を参照してください。