SQL のストアド プロシージャ | 概要

このブログでは、コード セットを保存し、必要なときにいつでも繰り返し使用できる SQL のストアド プロシージャについて説明します。ストアド プロシージャはビューに似ています。ただし、ストアド プロシージャを使用すると、ビューでは実行できない DROP、TRUNCATE、DELETE などの一般的なテーブル操作を実行できます。

ストアド プロシージャもプリコンパイルされているため、ビューよりも高速に実行されます。また、データベース サーバーに送信されるデータの量も最小限に抑えられます。

目次

• SQL のストアド プロシージャの構文: パラメーターなし
• SQL のストアド プロシージャの構文: パラメーターあり
• SSMS のストアド プロシージャのサンプル シナリオ
• 2 番目のサンプル シナリオ
• 3 番目のサンプル シナリオ
• SQL での保存されたストアド プロシージャの場所
• パラメータを使用した SQL でのストアド プロシージャの作成
• デフォルト値を使用したスト​​アド プロシージャの作成
• 結論

SQL のストアド プロシージャの構文: パラメーターなし

パラメーターなしでストアド プロシージャを作成するには、CREATE関数から始める必要があります。次に、PROC関数を追加し、その直後にプロシージャ名を入力し、AS関数を追加します。

でストアド プロシージャを作成することは、テーブルやビューを作成することと似ています。ただし、違いはデータの取得プロセスにあります。

たとえば、ビューからデータを取得する場合は、「SELECT * FROM view_name」を使用します。一方、ストアド プロシージャの場合は、「実行」を意味する EXEC を使用し、その後にストアド プロシージャ名を入力します。

ストアド プロシージャを実行すると、追加した命令やコマンドに基づいた情報も表示されます。

SQL のストアド プロシージャの構文: パラメーターあり

に移りましょう。このプロセスは、前に示した例と似ています。唯一の違いは、 AS関数の前にパラメーターを含める必要があり、直後にデータ型も含める必要があることです。

ストアド プロシージャの作成時に宣言したパラメーターをコマンドで利用することもできます。これは、複数の値を入力する時間を節約するためです。たとえば、customerID1、customerID2などを手動で入力する代わりに、 @custidまたはパラメーターを利用して、コードのセットが長くならないようにしました。

パラメーターを使用または含める場合は、@記号を使用することが重要であることに注意してください。パラメーターを 1 つだけではなく、必要な数だけ含めることもできます。これは、パラメーターを使用してストアド プロシージャを実行する方法です。

ご覧のとおり、ストアド プロシージャの実行時に値1のパラメーターも使用しました。これにより、 custid1の情報のみが取得されます。

SSMS のストアド プロシージャのサンプル シナリオ

SQL のストアド プロシージャの例をさらに見てみましょう。最初の例では、以下で強調表示されているコマンドを使用してサンプル ストアド プロシージャを作成します。

ストアド プロシージャを作成する際の命名規則では、通常、これがストアド プロシージャであることを示すために「usp」または「sp」を使用します。「usp」が何を意味するのか疑問に思っている場合は、単にユーザー定義ストアド プロシージャを表します。 

次に、ストアド プロシージャの実行時に処理するクエリを作成します。

前の例のクエリでは、単にdbo.stageOrdersという名前のテーブルが削除されます。次に、 Sales.SalesOrderHeaderテーブルのデータを使用してdbo.stageOrdersを再作成します。 

次のコードを強調表示し、[実行]ボタンをクリックして、このストアド プロシージャを作成しましょう。

その後、このようなメッセージが表示されるはずです。

次に、 EXEC usp_TESTを実行してストアド プロシージャを実行します。

usp_TESTストアド プロシージャを実行すると、dbo.stageOrdersにSales.SalesOrderHeaderからのデータが含まれるようになります。以下のコマンドを実行して、dbo.stageOrdersの内容を確認してみましょう。

その結果、ストアド プロシージャに追加したコマンドに基づいてdbo.stageOrdersに作成したデータがこれらになります。

2 番目のサンプル シナリオ

別の例に移りましょう。今回は、 dbo.stageOrdersテーブルを削除し、 Sales.SalesOrderHeaderテーブルからのデータを使用して同じテーブルを再作成する必要はありません。

代わりに、「 usp_TEST」ストアド プロシージャを実行するたびに Sales.SalesOrderHeader テーブルのデータが表示されるようにしたいだけです。

これを実行するには、ALTERステートメントを使用して、最近作成したストアド プロシージャに変更を加える必要があります。

この例では、ストアド プロシージャを変更するために使用されるALTERステートメントを利用しました。これをビューやテーブルに適用することもできます。上のスクリーンショットのコードを実行すると、ストアド プロシージャによって処理されるクエリが変更されるはずです。

今回は、以前のようにテーブルを再作成しません。「 usp_TEST 」を実行すると、 Sales.SalesOrderHeaderテーブルからデータが取得されるだけです。

3 番目のサンプル シナリオ

別の例を見てみましょう。今回は、 SSMSでストアド プロシージャを作成する最初の例で使用した dbo.stageOrders テーブルを再利用します。この例では、dbo.stageOrders テーブルを選択することから始めます 。 

dbo.stageOrders からすべてのデータを選択した結果は次のようになります。

次に、 usp_TESTを実行して dbo.stageOrders テーブルを空にしましょう。次のコマンドを追加することでこれを行うことができます。

TRUNCATE TABLEコマンドを追加した後、 usp_TESTを更新する必要があります。

その後、コマンドが正常に完了したことを示すメッセージが表示されます。次に、 usp_TESTを再度実行します。

usp_TESTを実行すると、 Sales.SalesOrderHeaderのすべてのレコードが表示され、 dbo.stageOrdersテーブルも空になります。 

dbo.stageOrders テーブルが空かどうかを確認するには、次のコマンドを使用してテーブルを選択し、実行する必要があります。

上記のコードを実行すると、dbo.stageOrders テーブルが空になっていることがわかります。これは、 usp_TESTの更新に使用したTRUNCATE TABLEコマンドが原因です。

SQL での保存されたストアド プロシージャの場所

SQL のストアド プロシージャが保存されている場所を確認したい場合は、左側の[オブジェクト エクスプローラー]パネルに移動し、作業しているデータベースの前にある[ + ] アイコンをクリックします。

次に、「Programmability」を右クリックし、「Refresh」を選択します。

「 + 」アイコンをクリックして、 Programmabilityフォルダまたはグループを展開します。次に、同じ手順を実行して「ストアド プロシージャ」グループを展開します。ストアド プロシージャ グループ内にdbo.usp_TESTが表示されるはずです。

SQL の特定のストアド プロシージャが実行しているコマンドやクエリを確認したい場合は、ストアド プロシージャを右クリックし 、以下のスクリーンショットの手順に従ってください。

その後、別のタブでストアド プロシージャが開き、そこに提供されているコマンドが表示されます。dbo.usp_TESTを開くと次のようになります。

ご覧のとおり、CREATEステートメントの前にデフォルトのコマンドがいくつかあります。必要に応じて単に削除することもできます。 

これで、 SQL のストアド プロシージャが実行しているコマンドを確認する方法がわかりました。

パラメータを使用した SQL でのストアド プロシージャの作成

次に、パラメーターを含むストアド プロシージャを作成します。たとえば、次のコードを使用して新しいストアド プロシージャを作成します。

コード例では、usp_GetCustomerという名前のストアド プロシージャを作成する同じプロセスを使用しました。次に、入力タイプがINTの@CustomerIDというパラメーターを追加しました。

ストアド プロシージャの作成時にパラメータを追加した後は、コマンドを実行するときは常にパラメータを指定する必要があることに注意してください。

パラメーターを指定せずにusp_GetCustomerを実行するとどうなるかを見てみましょう。

パラメータを指定せずにusp_GetCustomerを実行すると、エラー メッセージが表示されました。パラメーターを指定してusp_GetCustomerを実行すると、次のようになります。

パラメータを指定すると、ストアド プロシージャの実行で適切な結果を得ることができます。

デフォルト値を使用したスト​​アド プロシージャの作成

パラメーターを使用してストアド プロシージャを実行する際にエラー メッセージが表示されることを回避したい場合は、デフォルト パラメーターとして機能するデフォルト値を設定できます。

たとえば、usp_GetOrdersBy Yearという名前のストアド プロシージャを作成します。

次に、入力タイプが「INT 」、デフォルト値が2011であるパラメーター@Order Yearを追加します。

パラメーターを指定せずにusp_GetOrdersBy Yearを実行すると、 2011 年のレコードが表示されます。

一方、指定されたパラメーターとして2014を指定してusp_GetOrdersBy Yearを実行すると、 2014 年のレコードが表示されるはずです。

これは、日常のデータ管理タスクでストアド プロシージャを利用する方法です。

SQL 集計関数の HAVING 句
ISNULL SQL 関数 In Case ステートメント
OFFSET および FETCH を使用した SQL データ抽出

結論

すべてを考慮すると、SQL のストアド プロシージャとは何か、そしてその目的は理解できました。現在のストアド プロシージャを変更または更新するために使用されるALTERステートメントについても説明しました。

さらに、SQL でストアド プロシージャを作成するさまざまな方法があること、およびデフォルト値を指定することでストアド プロシージャの実行時にエラーが発生しないようにする方法を学びました。

最も重要なことは、長いコード セットを繰り返し実行することを避けるために、ストアド プロシージャを利用してコマンド セットを保存する方法を学習したことです。最後に注意してください。パラメーターを指定するときは、「 @ 」記号を使用することを忘れないでください。

ではごきげんよう、

ハーフィズ