ストアドプロシージャの概念

ストアドプロシージャは、論理処理ユニットのグループに集められ、データベースサーバーに格納された1つ以上のT-SQLステートメントのコレクションです。ストアドプロシージャが実行されると、最初の呼び出しでSQL Serverがそれを実行し、プランキャッシュと呼ばれるキャッシュに格納します。次回、SQL Serverはプランキャッシュを再利用するため、処理が高速化されます。最適な管理。これは、高性能アプリケーションで大規模なデータベースを最適化するのに非常に効果的です。

さらに、ストアドプロシージャは、データベース管理者（DBA）にとって非常に便利であり、DBAがコマンドの名前付きブロックを作成し、ストアドプロシージャの内容を気にせずに、それらを開発者に送信するのに役立ちます。入力パラメーターと出力パラメーターに注意してください。たとえば、その月の注文のリストを取得し、それを開発者に渡してアプリケーションに統合するストアドプロシージャを作成すると、開発者はデータベースに干渉して新しいコードを作成する必要がなく、ストアドを呼び出すだけです。宣言したプロシージャ。それを使用するだけです。

たとえば、日ごとに最も売れている製品のリストを取得するストアドプロシージャを作成し、これをユーザーマニュアルとともに開発部門に送信すると、開発部門は保存されているコンテンツに関心がなく、渡されたパラメータとストアドの戻り結果に関する情報が必要です。

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

• セキュリティの強化：データベース管理者は、特権をストアドプロシージャに関連付けることで、セキュリティを強化できます。ユーザーは、テーブルまたはビューにアクセスできない場合でも、ストアドプロシージャを実行するためのアクセス許可を取得できます。
• プリコンパイル済み実行：ストアドプロシージャは、最初の実行時にコンパイルされます。後続の実行ごとに、SQL Serverはプリコンパイルされたバージョンを再利用します。これにより、実行時間が短縮され、パフォーマンスが向上します。
• クライアント/サーバートラフィックの削減：ストアドプロシージャはシステムトラフィックの削減に役立ちます。T-SQLステートメントが実行されると、ネットワークは実行ごとに個別のリソースを使用します。ストアドプロシージャが実行されると、SQLステートメントがグループ化されて1つの単位として実行されるため、ネットワークトラフィックが削減されます。
• コードの再利用：ストアドプロシージャは何度も再利用できます。これにより、同様のタスクを実行するたびに何百ものTransact-SQLステートメントを繰り返し入力する必要がなくなります。

ストアドプロシージャの種類

ユーザー定義のストアドプロシージャ：

カスタムストアドプロシージャと呼ぶことができます。これらのプロシージャは、lapwjタスクの処理にT-SQLステートメントを再利用するために使用されます。ユーザー定義のストアドプロシージャには、T-SQLストアドプロシージャと共通言語ランタイム（CLR）ストアドプロシージャの2種類があります。 CLRストアドプロシージャは、.NET Frameworkのいくつかのメソッドに基づいており、どちらもパラメータを取得して返すことができます。

ストアドプロシージャの拡張：

ストアドプロシージャの拡張は、SQLServerがオペレーティングシステムと対話するのに役立ちます。拡張ストアドプロシージャは、SQLServerのオブジェクトには含まれていません。これらは、SQLServer環境の外部で実行されるDinamicLink Libraries（DDL）として開発されたストアドプロシージャです。アプリケーションはSQLServerと対話し、実行中にDLLを呼び出します。SQLServerは、拡張ストアドプロシージャを実行するためのスペースを割り当てます。拡張ストアドプロシージャは「xp」プレフィックスを使用します

システムストアドプロシージャ：

システムストアドプロシージャは通常、システムテーブルと対話し、システムテーブルの更新、プレフィックス「sp_」が付いたシステムストアドプロシージャなどの管理タスクを処理するために使用されます。これらのプロシージャは、データベースResourceにあります。手順は、各システムのsysスキーマまたはユーザー定義データベースにあります。システムストアドプロシージャでは、 GRANT、DENY、REVOKEのアクセス許可が許可されます。

システムストアドプロシージャは、1つの単位として実行されるコンパイル済みのT-SQLステートメントのセットです。システムプロシージャは、データベースの管理およびシステムの操作と情報の管理に使用されます。これらのプロシージャは、システムテーブル、ユーザー定義テーブル、ビュー、インデックスなどのデータベースオブジェクトに関するメタデータ情報に簡単にアクセスするためのソリューションを提供します。

システムストアドプロシージャは、システムのsysスキーマとユーザー定義データベースに論理的に表示されます。システムストアドプロシージャを参照する場合、 sysスキーマ識別子が使用されます。システム内のシステムストアドプロシージャは、 sp_で始まるデータベースリソース内の非表示のデータベースに物理的に格納されます。システムストアドプロシージャは、データベース管理者（データベースシステムの最高レベルの管理）が所有します。

注：システムテーブルは、新しいデータベースの作成時にデフォルトで作成されます。これらのテーブルには、テーブルやビューなどのユーザー定義オブジェクトに関するメタデータ情報が格納されます。データベース管理者から権限が付与されていない限り、ユーザーはシステムストアドプロシージャを使用してシステムテーブルにアクセスしたり、システムテーブルを更新したりすることはできません。

システムストアドプロシージャの分類

• カタログストアドプロシージャ：データベース内のテーブルに関するすべての情報は、システムカタログと呼ばれる一連のテーブルに格納されます。システムカタログからの情報は、カタログ手順を使用して取得できます。たとえば、L sp_tablesは、現在のデータベースのテーブルのリストを表示するカタログストアドプロシージャです。
• セキュリティストアドプロシージャ：セキュリティストアドプロシージャは、データベースのセキュリティを管理するために使用されます。たとえば、 sp_changedbownerは、現在のデータベースの所有者を変更するために使用されるセキュリティストアドプロシージャです。
• カーソルストアドプロシージャ：カーソルプロシージャは、ポインタ機能を実装するために使用されます。たとえば、 sp_cursor_listは、接続によって開かれたすべてのポインタを取得し、それらのプロパティを記述するストアドプロシージャです。
• 分散クエリストアドプロシージャ：分散ストアドプロシージャは、分散クエリを管理するために使用されます。たとえば、 sp_indexesは、特定のテーブルのインデックス情報を返す分散クエリストアドプロシージャです。
• データベースメールおよびSQLメールストアドプロシージャ： SQLサーバーで電子メールを処理するタスクを処理するために使用されます。たとえば、sp_send_dbmailは、指定された受信者に電子メールを送信するデータベースメールストアドプロシージャです。電子メールの内容は、結果セット、添付ファイル、またはその両方にすることができます。

一時的なストアドプロシージャ

セッション内で一時的に使用するために作成されたストアドプロシージャは、一時ストアドプロシージャと呼ばれます。これらのプロシージャはtempdbテーブルに保存されます。 tempdbシステムテーブルは、SQLServerインスタンスを介して接続するすべてのユーザーが利用できるグローバルリソースです。これには、すべての一時テーブルと一時的に保存されたプロシージャが含まれます。

SQL Serverは、ローカルとグローバルの2種類の一時ストアドプロシージャをサポートしていますが、次の違いがあります。

注：セッションは、ユーザーがデータベースに接続すると確立され、ユーザーが切断すると終了します。 ##プレフィックスを含むグローバル一時ストアドプロシージャの完全な名前は、128文字を超えることはできません。 ＃プレフィックスを含むローカル一時ストアード・プロシージャーの最終名は、116文字を超えることはできません。

リモートストアプロシージャ

リモートSQLServerで実行できるストアドプロシージャは、リモートストアドプロシージャと呼ばれます。リモート接続手順は、サーバーがリモートアクセス（ローカルアクセスではなくリモートアクセス）を許可している場合にのみ使用できます。リモートストアドプロシージャがローカルSQLServerインスタンスからクライアントに対して実行されると、ステートメントで中止エラーが発生する場合があります。エラーが発生すると、エラーの原因となったコマンドは終了しますが、リモートストアドプロシージャは引き続き実行されます。

拡張ストアドプロシージャ

拡張ストアドプロシージャは、通常のT-SQL構文では処理できないタスクを処理するために使用されます。拡張ストアドプロシージャは、 「xp_」プレフィックスを使用します。これらのストアドプロシージャは、データベースマスターのdboスキーマに格納されます。

構文：

 EXECUTE <procedure_name>

拡張ストアドプロシージャxp_fileexistを使用して、ファイルMytext.txtが存在するかどうかを確認する例：

 EXECUTE xp_fileexist 'CMyTest.txt'

ユーザー定義のストアドプロシージャ

SQL Serverでは、ユーザーはさまざまなタスクを実行するためのカスタムストアドプロシージャを作成できます。これらのストアドプロシージャは、ユーザー定義またはカスタムストアドプロシージャと呼ばれます。

たとえば、 Customer_Detailsテーブルに顧客のすべてのデータが格納されている場合、顧客に関する詳細データを表示するたびにT-SQLステートメントを作成する必要があります。代わりに、プロシージャが再利用のために実行されるたびに顧客の詳細を表示するコストストアドプロシージャを作成できます。

カスタムストアドプロシージャを作成するには、データベースでのCREATE PROCEDURE権限と、作成されたプロシージャのALTERスキーマ権限が必要です。

構文：

 CREATE {PROC|PROCEDURE} proc_ame [{@parameter data_type}] AS <sql_statement>

顧客の詳細を表示するために使用されるuspGetCustTerritoryという名前のカスタムストアドプロシージャを初期化する例：

 use AdventureWorks2019 CREATE PROCEDURE uspGetCustTerritory AS SELECT TOP 10 CustomerID,Customer.TerritoryID,Sales.SalesTerritory.Name FROM Sales.Customer JOIN Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID go exec uspGetCustTerritory

パラメータの使用（パラメータ）

ストアドプロシージャの威力は、その中にパラメータを配置できることから得られます。データは、呼び出し側プログラムを介してストアドプロシージャに渡されます。パラメータには、次の2種類があります。

• 入力パラメータを使用すると、呼び出し側プログラムはストアドプロシージャに値を渡すことができます。これらの値は、ストアドプロシージャで定義された変数にキャプチャされます。
• 出力パラメータを使用すると、ストアドプロシージャは値を呼び出し元のプログラムに戻すことができます。これらの値は、呼び出し元のプログラムの変数で取得されます。

入力パラメータ

値は呼び出し側プログラムからストアドプロシージャに渡され、これらの値はストアドプロシージャで定義された変数にキャプチャされます。入力パラメーターは、ストアード・プロシージャーの作成時に定義されます。入力パラメータに渡される値は変数または定数である可能性があり、値はプロシージャの呼び出し時にストアドプロシージャに渡されます。ストアドプロシージャは、これらの値を使用して特定のタスクを処理します。

構文：

 CREATE PROCEDURE <procedure_name> @parameter <data_type> AS <sql_statement> EXEC| EXECUTE <procedure_name> <parameters>

たとえば、territoryパラメータを使用してuspGetSalesストアドプロシージャを作成し、テリトリーの名前を取得して、このテリトリーの販売の詳細と営業担当者IDを表示します。次に、値が「Northwest」のストアドプロシージャを実行するコードが、入力パラメータとして渡されます。

 use AdventureWorks2019; GO CREATE PROCEDURE uspGetSales @territory varchar(40) AS SELECT BusinessEntityID, B.SalesYTD, B.SalesLastYear FROM Sales.SalesPerson A JOIN sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name=@territory; GO exec uspGetSales 'Northwest'

出力パラメータ

ストアード・プロシージャーは、出力を呼び出し側プログラムに返す必要がある場合があります。ストアドプロシージャから呼び出し側プログラムにデータを渡し、出力パラメータを介して処理されます。出力パラメータは、ストアドプロシージャ時に定義されます。出力パラメーターを指定するには、パラメーターを宣言するときにOUTPUTキーワードを使用します。また、ステートメントを呼び出すには、OUTPUTキーワードを使用して変数を定義する必要もあります。

構文：

 CREATE PROCEDURE <procedure_name> @parameter <data_type> OUTPUT AS <sql_statement> EXEC| EXECUTE <procedure_name> <parameters>

たとえば、 upsGetTotalSalesストアドプロシージャでは、入力パラメータ@territoryを使用してテリトリーの名前を取得し、出力パラメータ@sumを使用して現在までの販売年の合計を取得します。

 use AdventureWorks2019; GO CREATE PROCEDURE uspGetTotalSales @territory varchar(40), @sum int OUTPUT AS SELECT @sum= SUM(B.SalesYTD) FROM Sales.SalesPerson A JOIN Sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name = @territory GO

次のステップでは、出力値を受け取る@sumsale変数を宣言することにより、上記のストアドプロシージャを実行します。

 DECLARE @sumsale int; exec uspGetTotalSales 'NorthWest', @sumsale OUTPUT; SELECT @sumsale AS 'Total sales northWest';

OUTPUTパラメータには次のプロパティがあります。

• テキストまたは画像のデータ型を伝送できません
• 呼び出しステートメントには、出力値を受け取るための変数が含まれている必要があります。
• 次のT-SQLステートメントコールバックでユーザーに返すために使用できる変数

OUTPUT句は、INSERT、UPDATE、およびDELETEステートメントが実行された各行の情報を返します。この句は、INSERTまたはUPDATE操作を実行した後にIDまたは計算された列の値を取得するのに役立ちます。

もちろん、SSMSを使用してストアドプロシージャを作成することもできます。

ALTER（変更）ストアドプロシージャ

ストアード・プロシージャーが再初期化されると、ストアード・プロシージャーに関連付けられているアクセス許可は失われます。ただし、ALTERステートメントを介して変更された場合、プロシージャーに定義された許可は状態のままになります。

構文：

 ALTER PROCEDURE <procedure_name> @parameter <data_type> [OUTPUT] [WITH {ENCRYPTION|RECOMPILE}] AS <sql_statement>

uspGetTotalsという名前のストアドプロシージャの定義を変更して、 Sales.SalesTerritoryテーブルにCostYTD列を追加します。

 ALTER PROCEDURE [dbo].[uspGetTotal] @territory varchar = 40 AS SELECT BusinessEntityID, B.SalesYTD, B.CostYTD, B.SalesLastYear FROM Sales.SalesPerson A JOIN Sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name = @territory GO

注：ストアード・プロシージャーの定義を変更すると、従属オブジェクトに実行エラーが発生する場合があります。この問題は、ストアード・プロシージャーの変更を反映するように従属オブジェクトが更新されていない場合に発生します。

DROPストアドプロシージャ

ストアドプロシージャは、不要になったときに削除できます。プログラムが削除されたプロシージャを呼び出すと、エラーが返されます。

ドロップされたプロシージャと同じ名前と同じパラメータで新しいプロシージャが作成された場合、削除されたプロシージャと同じ名前と同じパラメータを持つ新しいプロシージャを参照するため、古いプロシージャへのすべての呼び出しは正常に実行されます。

ストアドプロシージャを削除する前に、 sp_dependsという名前のシステムストアドプロシージャを実行して、プロシージャに依存するオブジェクトを特定できます。

構文：

 DROP PROCEDURE <procedure_name>

 DROP PROCEDURE uspGetTotals

ネストされたストアドプロシージャ

SQL Server 2019では、ストアドプロシージャを他のストアドプロシージャ内で呼び出すことができます。このストアドプロシージャアーキテクチャは、ネストされたストアドプロシージャと呼ばれる別のストアドプロシージャを呼び出します。

ストアドプロシージャが別のストアドプロシージャを呼び出すと、ネストレイヤーが1つ増えると言われます。同様に、ストアドプロシージャが実行を完了し、呼び出し元のストアドプロシージャに制御フローを渡すと、ネストレイヤーが1つ減ります。 SQLServer2019でサポートされるネストレイヤーの最大数は32です。

例えば：

 CREATE PROCEDURE NestedProcedure AS BEGIN EXEC uspGetCustTerritory EXEC uspGetSales 'France' END