SQLストアドプロシージャ: クエリの自動化と最適化

SQL

SQLストアドプロシージャは、データベースに保存および格納されたSQL文のセットです。これらは必要に応じて実行され、データ操作や検証タスクを実行し、共通の操作に対して反復的なSQLコードを記述する必要を減らします。ストアドプロシージャは、効率性と再利用性を促進することでデータベース管理を支援します。さらに、拡張されたデータベースセキュリティと保守性をサポートします。この記事では、SQLストアドプロシージャの作成と実行方法、一般的なユースケース、およびベストプラクティスについて説明します。

始めるにあたり、DataCampの SQL入門と SQLを学ぶコースを受講することを強くお勧めします。これにより、SQLを使用してデータを抽出し分析するための基礎知識を学ぶことができます。また、ダウンロード可能な SQL基本チートシートは、最も一般的なSQL関数がすべて含まれているため、便利な参照資料です。

SQLにおけるストアドプロシージャとは何ですか？

SQLにおけるストアドプロシージャは、データベース内に保存されているSQLステートメントのコレクションです。SQLストアドプロシージャの目的は、データベースに対してクエリ処理、挿入、更新、または削除などの一連の操作を実行することです。

通常のSQLクエリとは異なり、別々のコマンドとして実行される代わりに、ストアドプロシージャは一連のSQLステートメントをカプセル化し、SQLコマンドを繰り返し記述する必要なくコードを再利用できるようにします。

SQLストアドプロシージャの利点は次のとおりです:

コードの再利用性: 一度ストアドプロシージャが作成されると、必要な回数だけ呼び出すことができ、SQLコードの冗長性を排除します。
パフォーマンスの向上: ストアドプロシージャは通常、事前にコンパイルされてデータベースサーバーに保存されているため、ネットワークの遅延やコンパイル時間を減らすことで高速に実行されることが多いです。
セキュリティ：ストアドプロシージャを使用することで、ユーザーにテーブルへの直接アクセス権を与えることなく、データセキュリティと機密データアクセスの制御を向上させることができます。

基本的な構文と構造

ストアドプロシージャを作成するための構文は、データベースシステム（例：MySQL、SQL Server、Oracle）によってわずかに異なる場合があります。以下は、SQL Server構文を使用した一般的な例です：

-- ProcedureNameという名前のストアドプロシージャを作成
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQLステートメントをここに記述
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

上記の構文では；

ストアドプロシージャの作成: このコマンドは新しいストアドプロシージャを定義するために使用されます。
プロシージャ名: ストアドプロシージャに付けられた名前です。データベース内で一意である必要があります。
@パラメータ1, @パラメータ2: パラメータはオプションであり、手続きがデータ入力を受け取ることを可能にします。各パラメータは@記号とデータ型（例：INT、VARCHAR(50)）で定義されます。
AS BEGIN…END: BEGIN と END の間のSQL文は、手続きの本文を形成し、主なロジックが実行されます。この例では、手続きは特定の条件に基づいてテーブルからレコードを取得します。

入力および出力パラメータ

入力および出力パラメータを使用すると、値をストアドプロシージャに渡したり、ストアドプロシージャから値を取得したりできます。

たとえば、@Parameter1 が入力パラメータとして定義されている場合、手続きが呼び出されるときに任意の値を割り当てることができ、SQLロジックや出力に影響を与えることができます。以下の例では、@UserID パラメータが指定された UserID に特化したデータを取得します。

-- 指定されたユーザーのデータを取得する手続きを作成
CREATE PROCEDURE GetUserData 
 -- 入力パラメータ: 取得するユーザーのID
    @UserID INT 
AS
BEGIN
    -- UserID が入力パラメータと一致する Users テーブルのすべての列を選択
    SELECT * FROM Users WHERE UserID = @UserID;
END;

出力パラメータは、OUTPUT キーワードで定義され、ストアドプロシージャが呼び出し元に値を返すことを可能にします。たとえば、手続きが割引を計算する場合、出力パラメータを使用してそれを呼び出しプログラムに渡すことができます。

-- 割引価格を計算する手続きを作成
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- 出力: 割引後の最終価格
AS
BEGIN
    -- 割引率を元の価格に適用して最終価格を計算します
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

この手順を呼び出すには、次のようにします;

-- 割引後の最終価格を保存するための変数を宣言します
DECLARE @FinalPrice DECIMAL(10, 2);
-- 価格100と10%の割引でCalculateDiscount手順を実行します 
-- 出力を@FinalPrice変数に格納します
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- 最終的な割引後の価格を選択して表示します
SELECT @FinalPrice AS FinalPrice;

MySQLのストアドプロシージャ

前述のように、SQLでストアドプロシージャを実行する方法は、データベースシステムや使用するツールによって異なります。

MySQLでのストアドプロシージャの作成

MySQLでストアドプロシージャを作成するには、プロシージャの名前、パラメータ、およびその本体を構成するSQLステートメントを定義します。以下の例では、GetEmployeeDetailsという名前のプロシージャを作成し、EmployeeIDを入力パラメータとして受け取り、その特定の従業員の詳細を取得します。

DELIMITER $
-- 特定のEmployeeIDを使用して従業員の詳細を取得する手順を作成します
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- EmployeeIDが入力パラメータと一致するEmployeesからすべての列を選択
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

MySQLでストアドプロシージャを実行する

ストアドプロシージャを実行する最も一般的な方法は、SQLコマンドを使用することです。MySQLでは、ストアドプロシージャを実行するためにCALLコマンドを使用します。

CALL ProcedureName();

定義されたGetEmployeeDetailsプロシージャを使用した実行クエリは、次のようになります：

-- EmployeeID 101の詳細を取得するためにストアドプロシージャを実行
CALL GetEmployeeDetails(101);

SQL Serverのストアドプロシージャ

SQL Serverは、ストアドプロシージャの作成、実行、および管理のための特定の構文とコマンドを提供しています。このアプローチにより、効率的で再利用可能なSQLルーチンを構築し、最小限の繰り返しで複雑なタスクを処理できます。

SQL Serverでストアドプロシージャを作成

SQL Serverでストアドプロシージャを作成するには、プロシージャの名前、パラメータ、および本体を構成するSQLステートメントを定義します。以下の例では、GetEmployeeDetailsという名前のプロシージャを作成し、@EmployeeIDを入力パラメータとして受け取り、その特定の従業員の詳細を取得します。

-- EmployeeIDによって特定の従業員の詳細を取得するプロシージャを作成
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- 入力パラメータ: 取得する従業員のID
AS
BEGIN
    -- 入力パラメータに一致するEmployeeIDを持つEmployeesからすべての列を選択
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

SQL Serverでのストアドプロシージャの実行

SQL Serverでは、EXECまたはEXECUTEコマンドがストアドプロシージャを呼び出します。以下の例では、特定の入力パラメータを使用してストアドプロシージャGetEmployeeDetailsを実行する方法を示します。

-- EmployeeIDを102に設定してGetEmployeeDetailsプロシージャを実行
EXEC GetEmployeeDetails @EmployeeID = 102;

出力パラメーターを実行するには、コマンドで変数を宣言することもできます。次の例では、@TotalSalesがCalculateTotalSalesからの出力を受け取る変数として宣言されています。

-- 変数を宣言して総売上額を保存します
DECLARE @TotalSales DECIMAL(10, 2);
-- SalespersonID 5のCalculateTotalSalesを実行し、結果を@TotalSalesに保存します
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- 総売上額を表示します
SELECT @TotalSales AS TotalSales;

SQL Serverの異なるデータクエリ機能を理解するために、SQL Server入門コースを受講することをお勧めします。また、ストアドプロシージャの作成、更新、実行スキルを身につけるだけでなく、集計関数、結合、テーブルの挿入および削除など、さまざまな機能を備えたSQL Server開発者キャリアトラックも検討してください。

ストアドプロシージャの一般的な使用方法

SQLストアドプロシージャは、繰り返し複雑なタスクが必要なシナリオで役立ちます。以下は、データ管理やビジネスオペレーションにおけるストアドプロシージャの実世界の応用例です。

データ検証と整合性の強制

ストアドプロシージャを使用して、更新または挿入前にデータを検証することができます。以下の例では、ストアドプロシージャが新しいレコードをCustomersテーブルに挿入する前に顧客のメールが一意であるかをチェックし、データの一貫性を確保します。これにより、検証ロジックがデータベース内で集約され、冗長性が減少し、異なるアプリケーション間で均一な強制が確保されます。

-- 新しい顧客を追加する手続きを作成し、重複したメールをチェックします
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Customersテーブルにメールがすでに存在するかをチェック
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- メールがすでに使用されている場合はエラーをスロー
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- メールが一意であれば新しい顧客の詳細を挿入
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

自動データ処理とレポート作成

ストアドプロシージャを使用して定期的なレポートを生成したり、大規模なデータセットを処理することもできます。たとえば、ストアドプロシージャを使用して、電子商取引プラットフォームからの日次売上データを集計し、レポートテーブルに保存することができます。これにより、チームが複雑なクエリを実行せずに売上の洞察を取得しやすくなります。

-- 日次売上レポートを生成する手順を作成
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- 今日の日付と総売上をSalesReportテーブルに挿入
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Salesテーブルから今日の日付と売上の合計を選択
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

トランザクション管理

ストアドプロシージャを使用することで、複数の操作を1つのトランザクションとして実行できます。たとえば、銀行システムでは、ストアドプロシージャが資金移動における借方および貸し方の両方を処理し、両方が成功するか失敗するかを確認できます。

-- アカウント間で資金を移動する手続きを作成する
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- 原子性を確保するためにトランザクションを開始する
    -- 送信者のアカウント残高から指定された金額を差し引く
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- 受信者のアカウント残高に指定された金額を加える
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- エラーを確認し、エラーが発生した場合はロールバックする; それ以外の場合はトランザクションをコミットする
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- エラーが発生した場合はすべての変更を元に戻す
    ELSE
        COMMIT TRANSACTION;    -- エラーがない場合は変更を確認する
END;

アクセス制御とデータセキュリティ

SQLストアドプロシージャを使用して、機密情報へのデータアクセスを制御することもできます。たとえば、ストアドプロシージャを使用すると、トランザクションの詳細なしにアカウント残高などの関連フィールドのみを取得する手続きを呼び出すことを許可することで、直接テーブルアクセスを制限できます。

-- 認証チェック付きでアカウント残高を取得する手続きを作成する
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- アカウントが存在し、指定されたユーザーが所有しているかを確認する
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- 認証されている場合、アカウント残高を選択して返す
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- 認証されていない場合、エラーをスローする
        THROW 50000, 'Unauthorized access.', 1;
END;

データ移行とETLプロセス

ストアドプロシージャは、システム間でデータをロード、変換、移行するためにも使用されます。ストアドプロシージャは、ソースデータベースからのデータ抽出を自動化し、必要に応じて変換し、ターゲットテーブルに挿入することで、レポートや分析のためのデータ統合を簡素化します。

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- 抽出
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- 変換
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- ロード
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

ストアドプロシージャのベストプラクティス

効率的で保守可能なストアドプロシージャを書くことは、データベースの最適なパフォーマンスを確保します。以下は、SQLデータベースのためのストアドプロシージャを書くためのヒントです。

一貫した命名規則を使用する: ストアドプロシージャを識別しやすく理解しやすくするために、一貫性があり記述的な命名形式を使用してください。また、SQL Serverでシステムプロシージャ用に予約されているsp_プレフィックスを避け、潜在的な競合やパフォーマンスの問題を防ぐようにしてください。
エラーハンドリングの実装: SQLステートメントをTRY...CATCHブロックでラップして、エラーをキャッチして処理し、データの整合性を維持します。
パフォーマンスの最適化: カーソルの使用を最小限に抑えることで、遅延やリソースの消費を軽減します。代わりに、一般的に効率的な集合ベースの操作を使用してください。頻繁に使用される列にインデックスを作成し、大きなテーブルでの複雑な結合を避けてメモリのオーバーヘッドを減らし、効率を向上させます。
ストアドプロシージャをパラメータ化する: ハードコードされた値の代わりにパラメータを使用して、プロシージャに動的な値を渡すことができるようにしてください。これにより、柔軟性が高まり再利用性が向上します。

詳細な集計関数や結合を使用してデータをフィルタリングする方法について学ぶには、当社の中級SQLコースをご覧ください。また、結合テーブルやデータ分析スキルを向上させるために、SQL Server FundamentalsおよびSQL Fundamentalsスキルトラックも試してみてください。

結論

SQLストアドプロシージャは、データベース管理におけるコードの再利用性とパフォーマンスの最適化を向上させます。ストアドプロシージャは、コントロールされたアクセスによるデータベースのセキュリティを強化し、データの整合性を確保します。データプラクティショナーとして、最適なデータベース管理の手法をマスターするためにストアドプロシージャの作成と実行を練習することをお勧めします。

データアナリストとして熟練したいのであれば、必要なスキルを学ぶために私たちのSQLにおけるアソシエイトデータアナリストキャリアトラックをチェックしてください。プロフェッショナルなダッシュボードをSQLを使用して構築する方法を学びたい場合は、SQLにおけるレポーティングコースも適しています。最後に、データ分析のためのSQLの使い方を習得したことを示し、他のデータ専門家の中で目立つために、SQLアソシエイト認証を取得することをお勧めします。

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure