ストアド プロシージャとは何ですか?

SQL (Structured Query Language) は、リレーショナル データベースを管理および操作するための標準言語です。その強力で一般的に使用される機能の 1 つはストアド プロシージャです。ストアド プロシージャは、プリコンパイルされてデータベースに保存される一連の 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;

ストアド プロシージャを作成および使用する方法

ストアド プロシージャの作成と使用について、次の 3 つの領域で見ていきます。

• MySQL
• SQL サーバー
• オラクル

MySQL

MySQL でストアド プロシージャを作成するのは非常に簡単です。 「CREATE PROCEDURE」ステートメントを使用して、プロシージャを定義し、パラメータを指定し、SQL コードを記述します。

次のことができます:

ステップ 1: 従業員テーブルを作成する

まず、使用するデータを入力するサンプルの従業員テーブルを作成しましょう。

DROP PROCEDURE GetEmployeesByDepartment

ステップ 2: サンプル データを挿入する

いくつかのサンプル データを Employees テーブルに挿入します。

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);

ステップ 4: ストアド プロシージャを呼び出す

ストアド プロシージャを呼び出して特定の部門の従業員を取得するには、CALL ステートメントを使用します。

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

SQLサーバー

SQL Server では、ストアド プロシージャの作成と実行が若干異なりますが、大幅に変更されるわけではありません。以下に例を示します:

ステップ 1: Employees テーブルを作成する

まず、サンプルの Employees テーブルを作成しましょう。

CALL GetEmployeesByDepartment(1);

ステップ 2: サンプル データを挿入する

次に、いくつかのサンプル データを Employees テーブルに挿入します。

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;

ステップ 4: ストアド プロシージャを実行する

ストアド プロシージャを実行して特定の部門の従業員を取得するには、EXEC ステートメントを使用します。

EXEC GetEmployeesByDepartment @DepartmentID = 1;

オラクル

Oracle はストアド プロシージャもサポートしています。ここでは、SQL を使用して Oracle にこれらを実装する方法についてのステップバイステップのガイドを示します。

ステップ 1: 従業員テーブルを作成する

まず、サンプルの Employees テーブルを作成しましょう。

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 Web サイトにアクセスします
? Chat2DB コミュニティに参加してください
? X でフォローしてください
? Discord で私たちを見つけてください

以上がストアド プロシージャとは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。