MS_StoredProcedure - NetDevInfraWGinOSSConsortium/NetDevInfraWiki GitHub Wiki

ストアド プロシージャ

概要

補足（「速い」の中身）: ストアド プロシージャが速いのは、
主にサーバ・クライアント間の往復（ラウンドトリップ）が減るためである。
「実行プランがコンパイル済みだから速い」と説明されることがあるが、
SQL Server ではアドホック クエリでも実行プランはキャッシュ・再利用されるため、
現在この差は決定的ではない
（SQL Server アドホック クエリ問題の監視）。

一方で、以下のような副作用もある。

論点 内容
パラメータ スニッフィング 初回実行時のパラメータ値でプランが固定され、他の値で極端に遅くなることがある
バージョン管理 DB 内のオブジェクトのため、アプリのソース管理から外れやすい
テスト 単体テストが書きにくい
移植性 T-SQL / PL/SQL は方言差が大きく、クロスDB対応が困難

パラメータ スニッフィングの対処は OPTIMIZE FOR / RECOMPILE /
ローカル変数への退避など。詳細は
SQL Server のオプティマイザを参照。

DBMS ネイティブなストアド プロシージャ

DBMS ごとの、ネイティブなストアド プロシージャ。

Transact-SQL (SQL Server)

PL/SQL (Oracle)

CLR ストアド プロシージャ

DBMS ごとの、CLR ストアド プロシージャ。

SQL CLR (SQL Server)

ODE.NET の .NETストアド プロシージャ (Oracle)

移行メモ（正誤）: 「ODE.NET」は ODP.NET（Oracle Data Provider for .NET）の誤記。
以降の見出しも同様に読み替える。

詳細

ストアドの定義と実行。

共通（ADO.NET）

SqlParameterのプロパティ

  • SqlParameter.Direction プロパティに、
    ParameterDirection 列挙型の 2、3、4 を設定した際の動作を確認する。
# メンバー名 説明
1 Input このパラメーターは入力パラメーターです。
2 InputOutput このパラメーターは入力と出力の両方の機能です。
3 Output このパラメーターは出力パラメーターです。
4 ReturnValue パラメーターは、ストアド プロシージャ、組み込み関数、ユーザー定義関数などの操作からの戻り値を表します。
  • SqlParameter.SqlDbType プロパティに
    以下の SqlDbType 列挙型を設定した際の動作を確認する。
# メンバー名 説明
1 Structured テーブル値パラメーターに含まれている構造化データを指定するための特別なデータ型。

補足（Output / ReturnValue を読むタイミング）: 出力パラメータと戻り値は、
DataReader を閉じるまで値が入らない。
ExecuteReader を使う場合は、読み切って Close/Dispose した後に参照する。

await using (var r = await cmd.ExecuteReaderAsync())
{
    while (await r.ReadAsync()) { /* ... */ }
}   // ← ここで閉じてから
int rc = (int)cmd.Parameters["@ReturnValue"].Value;

なお、ReturnValue は T-SQL の RETURN 文の値（int のみ）で、
業務上のデータを返す用途には使わない（慣例として 0 = 成功）。
値を返すなら出力パラメータか結果セットを使う。

複数の結果セット

SELECT 文を発行した回数分の結果セットが返る。

補足: DataReader では NextResult() で次の結果セットへ進む。
Dapperでは QueryMultiple を使うと簡潔に書ける。

using var multi = await cn.QueryMultipleAsync("usp_GetAll", ...);
var users = await multi.ReadAsync<User>();
var depts = await multi.ReadAsync<Dept>();

DBMS ネイティブなストアド プロシージャ

Transact-SQL (SQL Server)

補足（先頭に置く定番）: T-SQL のストアド プロシージャでは、
冒頭に SET NOCOUNT ON; を置くのが定番。
「n 件処理されました」というメッセージ（DONE_IN_PROC）の送出を抑止し、
往復のオーバーヘッドを減らせる。
ただし @@ROWCOUNT を参照する場合は順序に注意が必要
（SQL Server のトリガの補足を参照）。

エラー処理は TRY...CATCH と THROW（SQL Server 2012 以降）を使う。
トランザクションを扱う場合は SET XACT_ABORT ON; を併用すると、
エラー時にトランザクションが確実にアボートされる。

PL/SQL (Oracle)

CLR ストアド プロシージャ

SQL CLR (SQL Server)

ODP.NET の .NETストアド プロシージャ (Oracle)

補足（最新化：SQL CLR の現状）: SQL CLR は現在も利用可能だが、
以下の理由で採用のハードルが上がっている。

  • clr enabled は既定で 0（無効）（SQL Server の基本的な設定）
  • SQL Server 2017 以降は clr strict security が既定で有効となり、
    SAFE アセンブリでも署名または信頼登録（sp_add_trusted_assembly）が必要
  • Azure SQL Database では SQL CLR が利用できない

正規表現などの用途は、SQL Server 2016 以降の
STRING_SPLIT / JSON_VALUE、SQL Server 2025 の正規表現関数、
あるいはアプリ側での処理で代替できることが多い。

参考

DBMS ネイティブなストアド プロシージャ

マニュアル

ストアド ファンクションとの違い

以下を参考にすると、

  • SQL Server では機能の違い
  • Oracle では戻り値の有・無

に依るらしい。

補足（SQL Server での使い分け）: SQL Server では
ストアド プロシージャとユーザー定義関数（UDF）で
できることが明確に違う。

ストアド プロシージャ ユーザー定義関数
SELECT 内での呼び出し 不可 可能
データの更新 可能 不可（副作用を持てない）
出力パラメータ 可能 不可（戻り値のみ）
動的 SQL 可能 不可
トランザクション制御 可能 不可

なお、スカラー UDF は行ごとに評価されるため性能上の罠になりやすい。
SQL Server 2019 以降は「スカラー UDF のインライン化」で
自動的に改善されるケースがあるが、
可能ならインライン テーブル値関数（RETURNS TABLE AS RETURN (...)）
で書くほうが確実に速い。

無名SQLブロック（BEGIN～ENDブロック）

BEGIN...END で囲まれた手続き型と非手続き型の SQL ステートメントを実行する。

補足（「クエリの条件句で変数を参照する」がなぜ良くないか）:
ローカル変数の値はコンパイル時にはオプティマイザから見えないため、
統計情報のヒストグラムが使われず、
一律の推定値（密度ベース）で件数が見積もられる。
結果として不適切な実行プランが選ばれることがある。

ストアド プロシージャのパラメータであれば値が見える
（＝パラメータ スニッフィングが働く）ため、
「変数に退避してから使う」テクニックは
スニッフィングを意図的に無効化したいときの手段でもある。
つまり、状況によって良し悪しが逆転する。

大量データの処理方式3

大量データの処理方式3

CLR ストアド プロシージャ

各 DBMS ごとに用意されている。

共通（ADO.NET）

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

SQL CLR

  • 特集 SQL Server 2005 の新機能「SQL CLR」（前編）（1/3） - ＠IT
    http://www.atmarkit.co.jp/fdotnet/special/sqlclr01/sqlclr01_01.html

    • ［特集］SQL Server 2005 の新機能「SQL CLR」（前編）
      SQL Server プログラミングを革新する SQL CLR とは？
    • ［特集］SQL Server 2005 の新機能「SQL CLR」（後編）
      Visual Studio 2005 で SQL CLR を実装してみる

  • SQL Server 2005 を使いこなそう

  • SQL CLR を極める 3 つのコーディング・テクニック － ＠IT
    http://www.atmarkit.co.jp/fdb/rensai/sqls05try06/sqls05try06_3.html

    • Transact-SQL では困難だった複雑な計算処理や文字列操作などでは大きく力を発揮します。
      そこで、今回は .NET Framework で提供される正規表現ライブラリの利用を確認してみましょう。

    • 正規表現とは文字列のパターンを表現する表記法です。
      文字列の検索や置換といったシーンで利用されています。
      ここでは正規表現による入力値チェックを SQL CLR で実装します。

OracleのCLR ストアド プロシージャ

.NET ストアド プロシージャ

DB2のCLR ストアド プロシージャ

.NET CLR ルーチン

・・・

その他

OSS の DB では、N/A であるもよう。

# DBMS サポート
1 PostgreSQL N/A
2 MySQL N/A

補足（正確には）: 上表は「**CLR（.NET）**ストアド プロシージャ」の
サポート有無を指しており、ストアド プロシージャ自体が無いという意味ではない。

  • PostgreSQL: PL/pgSQL のほか、
    PL/Python・PL/Perl・PL/v8 などの手続き型言語を追加できる
    （PL/CLR に相当するものは無い）
  • MySQL: SQL/PSM 準拠のストアド プロシージャを持つ

Tags: 移行, データアクセス, SQL Server

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

⚠️ **GitHub.com Fallback** ⚠️