SQL (Structured Query Language) は、リレーショナル データベースを管理および操作するための標準言語です。その強力で一般的に使用される機能の 1 つはストアド プロシージャです。ストアド プロシージャは、プリコンパイルされてデータベースに保存される一連の SQL ステートメントであり、入力パラメータを受け入れ、操作を実行し、結果を返すことができます。ストアド プロシージャとは何か、またストアド プロシージャの作成方法を見てみましょう。
ストアド プロシージャは複雑な用語のように聞こえるかもしれませんが、効率的なデータベース管理の基礎です。その定義から始めましょう。
ストアド プロシージャは、データベース サーバーに事前定義されて保存されている一連の SQL ステートメントです。これらの操作を実行する必要がある場合、複数の個別のクエリ コマンドを送信する代わりに、ストアド プロシージャの名前を呼び出すことで操作を実行できます。
SQL Server で単純なストアド プロシージャを作成する方法を示す簡略化された例を次に示します。
CREATE PROCEDURE procedure_name AS BEGIN -- SQL statements END
ストアド プロシージャの主要なコンポーネントの一部を次に示します。
これらのコンポーネントは連携して、ストアド プロシージャを再利用可能で効率的にデータベース操作を実行できるようにします。一般的なデータベース タスクをストアド プロシージャにカプセル化することで、パフォーマンスとセキュリティを向上させながらアプリケーション開発を簡素化できます。
ストアド プロシージャはデータベース サーバー内で実行されるため、複数のクエリがクライアントから次々に送信される場合よりも効率的に操作を完了し、より速く実行できます。さらに、ストアド プロシージャを使用すると、個々のクエリの結果をやり取りするのではなく、最終的な結果セットのみをサーバーからクライアントに転送する必要があるため、ネットワーク トラフィックを大幅に削減できます。このようにして、データ処理の速度が向上するだけでなく、ネットワーク帯域幅の使用も削減されます。
ストアド プロシージャはビジネス ロジックをデータベース サーバーに一元的に保存するため、データベース管理において中心的な役割を果たします。そうすることで、重要な操作が常に一貫して安全かつ効率的な方法で実行されるようになります。具体的には、ストアド プロシージャは次の場合に役立ちます。
ストアド プロシージャを使用することには、いくつかの重要な利点があります。
次に、ストアド プロシージャと組み合わせる便利なコマンドを見てみましょう。
前述したように、このコマンドはデータベースに新しいストアド プロシージャを定義するために使用されます。この関数を使用したストアド プロシージャの例を次に示します。
次の列を持つ「Employees」というテーブルがあるとします。
特定の部門に所属するすべての従業員を取得するストアド プロシージャを作成したいと考えています。
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 でストアド プロシージャを作成するのは非常に簡単です。 「CREATE PROCEDURE」ステートメントを使用して、プロシージャを定義し、パラメータを指定し、SQL コードを記述します。
次のことができます:
まず、使用するデータを入力するサンプルの従業員テーブルを作成しましょう。
DROP PROCEDURE GetEmployeesByDepartment
いくつかのサンプル データを Employees テーブルに挿入します。
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY AUTO_INCREMENT, FirstName VARCHAR(50), LastName VARCHAR(50), DepartmentID INT, Salary DECIMAL(10, 2) );
部門に基づいて従業員を取得するストアド プロシージャを作成しましょう。
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 Server では、ストアド プロシージャの作成と実行が若干異なりますが、大幅に変更されるわけではありません。以下に例を示します:
まず、サンプルの Employees テーブルを作成しましょう。
CALL GetEmployeesByDepartment(1);
次に、いくつかのサンプル データを Employees テーブルに挿入します。
CREATE PROCEDURE procedure_name AS BEGIN -- SQL statements END
部門に基づいて従業員を取得するストアド プロシージャを作成しましょう。
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 にこれらを実装する方法についてのステップバイステップのガイドを示します。
まず、サンプルの 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;
次に、サンプル データを従業員テーブルに挿入してデータセットを作成します。
DROP PROCEDURE GetEmployeesByDepartment
部門に基づいて従業員を取得するストアド プロシージャを作成しましょう。
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;
前述したように、ストアド プロシージャは、基になるテーブルへの直接アクセスを制限することでセキュリティ層として機能できます。これにより、機密データが漏洩するリスクが軽減されます。
ストアド プロシージャを効率的に実行するには、パフォーマンスを最適化する必要があります。これは、不必要な計算を減らし、インデックスを有効に活用することを意味します。クエリ実行プランを分析してパフォーマンスのボトルネックを特定して解決することで、クエリの効率を向上させることができます。
たとえば、テーブル内のすべてのフィールドを取得するために「SELECT *」を使用することは避けるべきです。これは、転送されるデータ量が増加し、効率が低下するためです。代わりに、必要なフィールドのみを選択してデータ取得の範囲を絞り、パフォーマンスを向上させる必要があります。
コードの文書化はストアド プロシージャの作成にも適用されます。これは、他の開発者が各プロシージャの役割と機能を理解するために不可欠です。また、一貫した命名規則とコーディング スタイルも促進します。
このプロセスは、ストアド プロシージャにコメントを追加するか、別のドキュメントを維持することで実現できます。例:
CREATE PROCEDURE procedure_name AS BEGIN -- SQL statements END
ストアド プロシージャへの変更を管理および追跡するには、バージョン管理が重要です。ストアド プロシージャ スクリプトとそのドキュメントに対する変更の完全な履歴を含むリポジトリを維持すると便利です。これにより、すべての変更の追跡が容易になるだけでなく、さまざまなデプロイメント環境間での一貫性も確保されます。
ストアド プロシージャは、データベース管理の効率的かつ安全な手段です。これらは、適切なベスト プラクティスとともに使用すると、組織内のデータ分析の効率と有効性を大幅に向上させることができる多くの利点を提供します。
Chat2DB Web サイトにアクセスします
? Chat2DB コミュニティに参加してください
? X でフォローしてください
? Discord で私たちを見つけてください
以上がストアド プロシージャとは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
