【DB】ストアドプロシージャとは？新卒エンジニアの学び - エムティーアイエンジニアリングブログ

こんにちは、エムティーアイ Advent Calendar 2024 の12月24日分の執筆を担当させていただくスマートコンテンツ事業部で開発を担当している、新卒の長澤です。

今回は、私が業務でソースコード調査をしている際に、始めて触れたデータベース技術の１つである「ストアドプロシージャ」について、その学びをアウトプットさせていただきます。

ストアドプロシージャについて勉強してみた結果、いくつかの利点や課題があることに気づきましたので、その内容を記載しております。

1. ストアドプロシージャとは？

ストアドプロシージャ とは、データベースに保存される一連のSQL文をまとめたプログラムのことです。通常、SQLクエリはその都度送信して実行されますが、ストアドプロシージャは事前にデータベース内に保存され、名前を指定して実行されます。

主な特徴

データベース内で保存・実行
- クエリがデータベースサーバー内で直接実行されるため、クライアントとサーバー間の通信量を減らすことができます。
再利用可能
- 一度作成すれば何度でも呼び出せるため、同じ処理を繰り返し利用する場面に最適です。
動的なパラメータ対応:
- パラメータを渡すことで、柔軟な処理を実現することができます。

　　　　　　　　　　　　↓ SQL Server であれば SSMS からアクセス可能です

2. ストアドプロシージャの利点と課題

利点

処理が高速:

ストアドプロシージャは、事前にコンパイルされているため、クエリの解析や最適化があらかじめ済んでいます。そのため、データベース内で効率的に処理を実行することができます。　また、1回の呼び出しで複数のクエリをまとめて実行できるので、アプリケーションとの通信量が減り、全体のパフォーマンス向上につながります。
セキュリティの強化:

SQLインジェクション対策として有効です。パラメータ化されたクエリを使用することで、入力データの安全性を確保します。
保守性の向上:

アプリケーションコードとデータベース処理を分離し、ロジックをデータベース内に一元化できます。

課題

デバッグの難しさ:

実行時のエラー箇所の特定が難しく、一般的なプログラミング言語ほどデバッグが容易ではありません。実際にSSMSにアクセスして、クエリの内容を確認する必要があります。
バージョン管理の課題:

通常のアプリケーションのソースコードと異なり、ストアドプロシージャはデータベース内に保存されるため、Gitなどのバージョン管理ツールと連携がしづらいです。

3. ストアドプロシージャの基本構文と例

ストアドプロシージャを作成するためには、データベースの種類に応じた構文を理解する必要がありますが、以下は、SQL Serverを例にした基本構文とサンプルコードです。

基本構文（SQL Server）

CREATE PROCEDURE スキーマ名.プロシージャ名
    @パラメータ名 データ型 = デフォルト値
AS
BEGIN
    -- メイン処理
    SELECT * FROM テーブル名 WHERE カラム名 = @パラメータ名;
END;

例: 商品在庫を確認するプロシージャ

以下は、指定された商品IDの在庫数を取得するストアドプロシージャの例です。

CREATE PROCEDURE dbo.GetStock
    @ProductID INT
AS
BEGIN
    SELECT ProductName, StockQuantity
    FROM Products
    WHERE ProductID = @ProductID;
END;

実行方法

作成したプロシージャを呼び出すには、以下のように EXEC または CALL を使用します。

EXEC dbo.GetStock @ProductID = 123;

4. 実際の活用例

バッチ処理の自動化

例えば、月次で売上データを集計し、特定のテーブルに保存する処理を自動化できます。

CREATE PROCEDURE dbo.MonthlySalesSummary
AS
BEGIN
    INSERT INTO SalesSummary (Month, TotalSales)
    SELECT MONTH(SaleDate), SUM(Amount)
    FROM Sales
    GROUP BY MONTH(SaleDate);
END;

トランザクションの利用

複数のテーブルにまたがる処理を安全に実行できます。

CREATE PROCEDURE dbo.UpdateOrderStatus
    @OrderID INT, @Status NVARCHAR(50)
AS
BEGIN
    BEGIN TRANSACTION;

    UPDATE Orders
    SET Status = @Status
    WHERE OrderID = @OrderID;

    INSERT INTO OrderLogs (OrderID, Status, UpdatedAt)
    VALUES (@OrderID, @Status, GETDATE());

    COMMIT TRANSACTION;
END;