組み込み型分析ツールでのストアド プロシージャの使用

ストアド プロシージャを使用すると、同じコード行を頻繁に記述する手間を省き、必要なクエリの呼び出しと実行を迅速化する簡単な方法です。

しかし、ストアド プロシージャ、その仕組み、そしてストアド プロシージャがユーザーにもたらす利点について何を知っていますか?

この記事では、ストアド プロシージャとは何か、その利点、関数と比較した場合の違い、ストアド プロシージャの作成方法、そして最後に、組み込み分析でストアド プロシージャを使用する方法について学びます。

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

ストアド プロシージャは、リレーショナル データベース管理システム (RDBMS) にグループとして保存され、繰り返し保存して再利用できる一連の SQL 文およびその他の PL/SQL 構成要素です。ストアド プロシージャは、SELECT、INSERT、UPDATE、DELETE などの複数の SQL ステートメントで構成されます。これらは 1 つのユニットとして実行され、特定の問題を解決したり、一連の関連タスクを実行したりするために使用されます。つまり、SQL クエリが必要で繰り返し記述する場合は、それをストアド プロシージャとして保存し、それを呼び出してクエリを実行できます。

Microsoft SQL Server を使用している場合は、SQL Server Management Studio のオブジェクト エクスプローラーでデータベースの下の [プログラム] ノードを展開すると、ストアド プロシージャを見つけることができます。

ストアド プロシージャを使用する利点

ストアド プロシージャは、最もよく使用され、人気のあるデータベース サブジェクトの1 つであるため、いくつかの利点があります。ストアド プロシージャを使用すると、SQL クエリの実行を簡素化し、高速化できます。

たとえば、プロシージャを保存すると、サーバーとクライアント間のネットワーク トラフィックを削減できます。これは、コマンドが単一のコード バッチとして実行された結果です。そのため、コードの各行が個別に実行のために送信されるのではなく、ストアド プロシージャを実行する呼び出しのみがサーバーに送信されます。

ストアド プロシージャを使用する他の利点をいくつか示します。

保守性

多数のクライアント マシン上でコピーを維持するのとは対照的に、サーバー上でストアド プロシージャを維持するのは非常に簡単です。

また、プロシージャのスクリプトは 1 つの場所に保存されますが、ストアド プロシージャは他のアプリケーションでも自由に使用できます。定義が変更された場合、影響を受けるのはストアド プロシージャのみであり、それを呼び出すアプリケーションには影響しないため、まったく問題ありません。

パフォーマンスと効率

ストアド プロシージャは 1 回コンパイルするだけで、その後は何度でも使用できます。これにより、アドホック クエリを実行する場合に同じ内容を複数回記述する手間が大幅に節約され、ストアド プロシージャの最初の実行時とアドホック クエリの最初の実行時のパフォーマンスがわずかに向上します。

実際のRevealを参照

複雑なビジネスロジックを含む

複雑なビジネス ロジックには最適な場所ではありませんが、ストアド プロシージャは基本的にデータベース上の関数呼び出しです。 IF ステートメント、LOOPS、セキュリティ チェックなどを含めて、ストアド プロシージャの呼び出しに正しいデータが確実に送り返されるようにすることができます。

チームのスキルセットに基づいて多少の議論の余地はありますが、ベスト プラクティスは、アプリケーションのビジネス ロジックをアプリケーション側に置くことに傾いています。ただし、ストアド プロシージャと T-SQL は、ロジックを実行するための非常に強力な構文です。

変更が簡単

ALTER PROCEDURE コマンドを使用すると、ストア プロシージャ内の 1 つ以上のステートメントをすばやく変更できます。これにより、呼び出し側アプリに返される結果を非常に迅速かつ簡単に更新できます。ビジネス ロジックを含む中間層コンポーネントはコストのかかる再デプロイが必要になる可能性があるのとは対照的に、ストアド プロシージャへの迅速な変更には数秒かかります。

@Ord Year 値がハードコーディングされているこのストアド プロシージャを考えてみましょう。開発者またはデータベース管理者は、ALTER PROCEDURE コマンドを使用して @Ord Year 変数を簡単に更新できます。

ALTER PROCEDURE [dbo].[SalesByCategory] @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998' AS IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' BEGIN SELECT @OrdYear = '1998' END SELECT ProductName, TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0) FROM [Order Details] OD, Orders O, Products P, Categories C WHERE OD.OrderID = O.OrderID AND OD.ProductID = P.ProductID AND P.CategoryID = C.CategoryID AND C.CategoryName = @CategoryName AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear GROUP BY ProductName ORDER BY ProductName

セキュリティ

ストアド プロシージャを使用するもう 1 つの大きな利点は、データ ソースにセキュリティが提供されることです。ストアド プロシージャを使用して不正アクセスを制限し、アプリケーションのセキュリティを強化できます。たとえば、テーブル自体へのアクセスを許可せずに、データベース テーブルを更新するプロシージャへのアクセスを誰かに許可できます。ストアド プロシージャを使用して、ストアド プロシージャのエンド ユーザーに対して基になるテーブル名を「隠す」こともできます。ストアド プロシージャの呼び出し元は、ストアド プロシージャで使用されるテーブルの名前ではなく、ストアド プロシージャの名前だけを持っているため、悪意のある方法でデータが含まれるテーブルに直接アクセスすることはできません。

ストアド プロシージャと関数の違い

ストアド プロシージャとストアド関数は、タスクを完了するための SQL ステートメントのセットを含むデータベース オブジェクトです。そして、一見同じもののように見えるかもしれませんが、それらはまったく異なります。

ストアド プロシージャとストアド関数を区別する点は次のとおりです。

• 関数はストアド プロシージャから呼び出すことができますが、ストアド プロシージャを関数から呼び出すことはできません。
• 関数では出力パラメーターを使用できませんが、ストアド プロシージャでは出力パラメーターと入力パラメーターの両方が使用できます。
• 関数内でトランザクションを管理することはできませんが、ストアド プロシージャ内で実行できます。
• select ステートメントを使用して関数を呼び出すことはできますが、select ステートメントを使用してストアド プロシージャを呼び出すことはできません。
• 関数には戻り値の型があり、値を返しますが、ストアド プロシージャには戻り値の型がなく、値を返すかどうかはオプションです。
• 関数では SELECT ステートメントのみが許可されますが、ストアド プロシージャでは SELECT、INSERT、UPDATE、AND DELETE が許可されます。
• ストアド プロシージャはデータの読み取りと変更に使用できますが、関数は読み取りのみが可能です。
• 関数は SELECT ステートメントに埋め込むことができますが、ストアド プロシージャは SELECT ステートメントで利用できません。
• 関数は複数の結果セットを返すことができませんが、ストアド プロシージャは返すことができます。
• トランザクションはストアド プロシージャで使用できますが、関数では使用できません。

ストアド プロシージャを作成するにはどうすればよいですか?

新しいストアド プロシージャの作成は簡単です。基本的な手順は 3 つあります。

1. SQL Server オブジェクト エクスプローラーで、[プログラム] の下の[ストアド プロシージャ] ノードを右クリックします。

2. 新しいストアド プロシージャ テンプレートで、ストアド プロシージャから必要なデータを返すテーブル、パラメータ、およびクエリ オプションを含むようにCREATE PROCEDURE ステートメントを変更します。

3. ツールバーの「実行」ボタンをクリックして、ストアド プロシージャをコンパイルして保存します。

ストアド プロシージャを作成するツールは複数ありますが、対話型の SQL Server Management Studio を使用するのが最も簡単です。これを使用すると、Azure や SQL Server Analysis Services など、さまざまな種類のデータベース サーバーへの接続を簡単に開始できます。

組み込み型分析ツールでストア プロシージャを使用するにはどうすればよいですか?

​Reveal Embed SDKを使用する場合、ストアド プロシージャを使用する方法が 2 つあります。

1. ストアド プロシージャをサポートするデータベース上で新しいビジュアライゼーションを作成している場合、使用可能なストアド プロシージャのリストが表示されます。選択したストアド プロダクトはRevealの他のデータ ソースと同じように扱います。唯一の違いはパラメーター化されたストアド プロシージャであり、ストアド プロシージャに変数を渡すことでフィルターで使用できます。

Revealユーザー インターフェイスを介してストアド プロシージャを使用する方法に関する 6 分間の短いビデオをここで見ることができます。

2. Reveal SDK であるため、コードを介してストアド プロシージャとパラメータをプログラムで完全に制御することもできます。ストアド プロシージャ名と必要なパラメーターを渡すには、ChangeVisualizationDataSourceItemAsync 関数を使用します。この例では、CustOrderHist という名前のストアド プロシージャを渡し、CustomerID パラメーターを渡す方法を示します。

public async override Task<RVDataSourceItem> ChangeVisualizationDataSourceItemAsync (string userId, string dashboardId, RVVisualization visualization, RVDataSourceItem dataSourceItem) { if (dashboardId == "StoredProcNorthWind") { var msSqlDsi = dataSourceItem as RVSqlServerDataSourceItem; if (msSqlDsi?.Procedure == "CustOrderHist") { msSqlDsi.ProcedureParameters["@CustomerID"] = "AROUT"; } else { msSqlDsi.Procedure = "MyProcedure"; msSqlDsi.ProcedureParameters["@SampleParam"] = "SampleParamValue"; } return dataSourceItem; } }

要約

ストアド プロシージャは、データベースにクエリを実行し、呼び出し元のアプリに結果を返すための強力なツールです。Reveal SDK ユーザーにデータを提示する方法を簡素化し、アプリケーション コード内のパラメーターの処理方法を簡素化するには、エラーが発生しやすく長いアドホック クエリを使用するのではなく、ストアド プロシージャの使用を検討する必要があります。

​SDK をダウンロードするか、簡単なデモをスケジュールすることで、Reveal埋め込み分析とその仕組みについて詳しく知ることができます。