ストアドプロシージャとビューとユーザー定義関数
課題
ストアドプロシージャとビューとユーザ定義関数について調べる。
一文で済ますには複雑で、定期的に繰り返すSELECT文を書くことになった。取り敢えずガリガリ書いてファイル保存して定期実行でも良いが、そのSELECT文の結果をほかの人も欲しがるとなると手間がかかる。そこで、ストアドプロシージャかユーザ定義関数にしてしまおうと思った。そのためにまずストアドプロシージャとユーザ定義関数のことを調べる。ついでにビューのことも復習する。
2015.11.18追記
実際にストアドプロシージャとユーザ定義関数を使ってわかったことがあるので追加した。追加部分はオレンジ色にしている（参考以外）。
調査
調査対象環境
- SQL Server 2012
調査結果
ストアドプロシージャ
データベースに対する一連の処理を一つのプログラムにまとめてRDBMSに保存し、実行できるようにしたもの
特徴
- 複数のSQL文を含められる
- 引数がとれる
- 出力用の引数もとれる
- ただし、テーブル変数は読み取り専用で出力は出来ない
- 戻り値はプロシージャの実行状態を表す整数値だけ
- 定義内で一時テーブルを使うことが出来る
- DBへの変更処理も書ける
長所
ユーザ定義関数
データベースからデータを抽出する一連の処理を一つのプログラムにまとめてRDBMSに保存し、実行できるようにしたもの
特徴
メリット
- SQLを一文ずつ順番に発行する手間が省ける
- ネットワークトラフィックが減る
- 一連の処理をRDBMSで保存するため、構文解析や機械語変換がされて処理が早くなる
- 関数をSQL文に含めることができる
- テーブル値を返すユーザ定義関数ならSELECT * FROM [Function](hoge, piyo)みたいなことができる
ビュー
実テーブルから作られる仮想テーブル
特徴
- 実データを持たず、実行すると定義時に書いたSELECT文の結果が返る
- 引数・戻り値はない
- DBへの変更処理は制限があるけれど、UPDATEやINSERT、DELETEを使える
メリット
- 何度も必要となるSELECT文を簡単に実行できるようになる
- 見せたくないデータを除いたビューを用いることでセキュリティも考慮できる
評価
DB変更を伴う処理ならストアドプロシージャ、複雑なデータ抽出処理ならユーザ定義関数という使い分けが良さそう。 特にテーブル値を返すユーザ定義関数がFROM句にそのまま使えるのはSQL文の記述が簡単になって見やすくなりそう。
関数の引数によって返すテーブルが違う、とか気をつけないといけないかなって思ったけど、それはユーザ定義関数のせいじゃないか。
テーブル値を返す場合、テーブル行数が多いと処理に長時間かかるのが注意点だと知った。
ビューを使ったDBへの変更処理はうーん、どうなんだろう。アプリケーションからデータ編集する際に使うことで、編集されちゃ困る部分を隠すことができる、とかかな。自分自身は当分SELECT文の簡略化を主な目的としてビューを使う。
参考
ストアドプロシージャ
- Tumbling Dice — [SQL Server]ストアドプロシージャを作成するための基礎知識
- 初心者のためのSQL入門
- [SQL] 13. ストアドプロシージャ (SQL 非標準) 1 | TECHSCORE(テックスコア)
- さらっと覚えるSQL＆T-SQL入門（9）：Transact-SQLでストアドプロシージャを作成する (1/2) - ＠IT
ビュー
- https://msdn.microsoft.com/ja-jp/library/ms187956(v=sql.110).aspx
- [SQL] 9. ビュー 1 | TECHSCORE(テックスコア)
