Microsoft SQL Serverの勉強をしてみよう・その２
データベース
こんにちは。松山本社のWANPOです。
前回紹介しておりました、自社の講習会でMicrosoft SQL Serverについて紹介したいと思います。
第２回目としては「Microsoft SQL Server」のストアドプロシージャ・トリガーについて学んだので紹介致します。
ストアドプロシージャの説明
ストアドプロシージャ（stored procedure）とは
データベース管理システム（DBMS）の機能の１つで、DBに対する連続した複数の処理を１つのプログラムにまとめ、データとともに保存できるようにしたものです。
さまざまな SQL文に加え、通常の SQL では扱えない入出力のパラメータや、条件分岐や繰り返しといったプログラミング機能を含めることが可能です。
クライアントは作成されたストアドプロシージャを呼び出すだけで、事前に登録された一連の処理を何度でも確実に実行することができます。
＜作成方法＞
オブジェクトエクスプローラーから、目的のデータベースを展開し、 [プログラミング] を展開します。
次に、 [ストアドプロシージャ] を右クリックし、「ストアドプロシージャ」を選択することで作成できます。
ストアドプロシージャのメリット、デメリット
- メリット
一度コンパイルされて実行可能な形式で格納されているため、SQL文を１つずつ呼び出す場合と比べ処理が高速になります。
自動的にキャッシュされ、ユーザ間で共有される結果、メモリー要件や起動時のオーバーヘッドが低減されます。
DBに対する処理をアプリケーションから隔離できます。
データベーストリガーと組み合わせることで、複雑なルールによるデータの参照整合性保持が可能になります。
⇒業務システムに関する処理内容を変更したい場合、関連するストアドプロシージャのみを更新するだけで対応可能なため、
アプリケーションの保守性が向上します。
- デメリット
SQLで実現できる全ての処理を定義できますが、ストアドプロシージャの保存場所がDBサーバであるため、ネットワーク上で複数の処理が一斉に実行されると、DBサーバへの負荷、およびネットワークのトラフィックも増加することになります。
汎用のプログラミング言語に比べ機能や拡張性、開発ツールなどの環境は劣るため、過度に処理内容をストアドプロシージャ側で動かせることで、かえってシステム全体の生産性や保守性を下げてしまう場合もあります。
⇒管理が煩雑になります。
ストアドプロシージャの作成例
ストアドプロシージャを作成する構文は以下の通りとなります。
|
CREATE PROCEDURE [ ストアドプロシージャ名 ] [ 実行したいスクリプト ] |
SQLの実行環境
SQLの実行方法について説明します。
まずは、「新しいクエリ」をクリックしてください。
SQLエディターが開きました。
このSQLエディター内に、SQL文を記述してください。
SQLを実行する際は、データベースが「課題用データベース」となっていることを確認し「実行」をクリックしてください。
※以降のSQL実行は、このSQLエディターで行います。
SELECTを実行するストアドプロシージャ
まずは、簡単なストアドプロシージャを作ってみましょう。
作成するのは、在庫マスタテーブルから「数量」が30未満のデータを抽出するストアドプロシージャです。
|
CREATE PROCEDURE sp３０未満在庫 |
上記のSQL文を実行すると「sp３０未満在庫」という名前の
ストアドプロシージャが作成されます。
では、実行してみましょう。ストアドプロシージャを実行する
には、「EXEC」を使用します。
| EXEC sp３０未満在庫 |
SELECT文を実行したのと同じ結果を返します。
引数を用いたストアドプロシージャ
引数を用いたストアドプロシージャを作ってみましょう。
作成するのは、在庫マスタテーブルから「数量」が「引数で渡された値」未満のデータを抽出するストアドプロシージャです。
| CREATE PROCEDURE sp指定数未満在庫 @在庫 int AS BEGIN SELECT 商品コード,数量,更新日 FROM 在庫マスタ WHERE 数量 < @在庫 END |
では、引数に「30」を指定して実行してみましょう。
| EXEC sp指定数未満在庫 30 |
上で実行した引数無しのストアドプロシージャ「EXEC sp３０未満在庫」と同じ結果が返されます。
繰り返し処理を行うストアドプロシージャ
繰り返し処理を行うストアドプロシージャを作ってみましょう。
作成するのは、売上データより商品ごとの売上合計を求め、売上集計テーブルに結果を更新するストアドプロシージャです。
|
CREATE PROCEDURE sp売上集計データ作成 DECLARE cursor売上データ CURSOR FOR -- 売上集計テーブルを初期化 -- メイン処理 FETCH NEXT FROM cursor売上データ WHILE @@FETCH_STATUS = 0 FETCH NEXT FROM cursor売上データ -- 終了処理 END |
「DECLARE ～ CURSOR FOR」というSQL文ですが、これはカーソルを宣言しています。カーソルは、SELECT文の実行結果を1行ずつ取り出すことができます。
| DECLARE [ カーソル名 ] CURSOR FOR [ カーソルを使用するSELECT文 ］ |
「OPEN」でカーソルを利用できるようにします。
| OPEN [ カーソル名 ] |
「FETCH」というSQL文で、カーソルから１行を読み込み、ローカル変数へ値を格納します。
| FETCH [ FETCHタイプ ] FROM [ カーソル名 ］ INTO [ 結果を格納するローカル変数 ］ |
FETCHタイプは、どの行を読み込むのかを指定します。
今回の例では「NEXT」を使用しましたが、これは現在行を読み込むということです。このほかに最初の行を読み込む「FIRST」、最後の行を読み込む「LAST」があります。
「@@FETCH_STATUS」というグローバルシステム変数が 利用されています。「FETCH」で正常に値が読み込めた場合は、この変数に「0」がセットされます。最終の行を超えて値が読み込めなくなった場合は「-2」がセットされます。
今回の例では、「@@FETCH_STATUS」が「0」の間、処理を繰り返す構文になっています。つまりデータが取得できる間、処理を繰り返す。ということになります。
利用が終了したら「CLOSE」を行います。
| CLOSE [ カーソル名 ] |
それでは、説明が長くなりましたが、実行してみましょう。
| EXEC sp売上集計データ作成 |
結果は、売上集計テーブルに格納させているのでデータを確認してみましょう。
| SELECT 商品コード,数量合計 FROM 売上集計 |
トリガーの説明
トリガー（trigger）とは
データベース管理システム（DBMS）の機能の１つで、テーブルに何かの操作（追加・更新・削除）が加えられた時に、
あらかじめ定義した処理を自動的に実行する機能のことで、ストアドプロシージャの一種となります。
アプリケーションから呼び出されるものではなく、アプリケーションには全く依存しないものとなります。
例えば、トリガーを使用して以下の事ができます。
- テーブルの更新時に別のテーブルを更新
- 更新内容が不適切な場合に強制的にロールバックを実施
<作成方法＞
オブジェクトエクスプローラーから、目的のデータベースを展開し、 [テーブル] から目的のテーブルを展開します。
次に「トリガー] を右クリックし、「新しいトリガー」を選択することで作成できます。
トリガーのメリット、デメリット
- メリット
複数のアプリケーションで共通に実行する処理をトリガーに定義することで、アプリケーションを簡略化することができます。
⇒個別のアプリケーションをシンプルに記述できます。
⇒多様しなければ保守が容易になる。
- デメリット
トリガーはテーブルにデータ操作文が実行されるたびに実行されるため、一つずつ順番にしか処理できない。
そのため、トリガの実行数が増えると処理が遅くなる。
⇒処理全体の見通しが悪くなり、意図しない動作を引き起こす危険性がある。
アプリケーション自体はトリガーによってどのような処理が行われるのかを知ることができないため、トリガーを多用するとアプリケーションによる処理とトリガーによる処理の関係が不明確となり、全体としての処理内容が把握しずらい。
⇒保守性が低下する可能性あり。
トリガーの種類
- ログオントリガー
ログオン時に、ユーザーセッションが確立される時に実行されます。
例えば、ログオントリガーを使って 「 既にセッションが確立されているユーザーが、他のセッションでログオンしようとした場合は拒否する 」 というようなことができます。
- DDL トリガー
CREATE・ALTER・DROP ステートメントのような、DDL イベントと呼ばれるデータベースのスキーマを変更するようなイベントをきっかけに実行されます。
例えば DDL トリガーを使うと、データベースのスキーマ変更を防いだり、データベーススキーマの変更記録を残したりすることができます。
- DML トリガー
テーブルやビューに対して、INSERT・UPDATE・DELETEステートメントのような、データの挿入・変更・削除等が起こった時に実行されます。
トリガーを起こしたステートメントと同じトランザクションとして扱われるので、トリガー内でエラーを起こすことによって、トランザクションをロールバックさせることもできます。
DML トリガーの種類
- BEFORE トリガー
テーブルに対する操作の前に、トリガーを実行します。
トリガー動作時期にBEFOREを指定したトリガーを、BEFOREトリガーといいます。
なお、SQL Serverでは、BEFOREトリガーは実装されていません。
- AFTER トリガー
テーブルに対するINSERT・UPDATE・DELETEステートメントの動作の後に、トリガーを実行します。
トリガー動作時期にAFTERを指定したトリガーを、AFTERトリガーといいます。
- INSTEAD OF トリガー
トリガーを起こしたINSERT・UPDATE・DELETEステートメントの動作の代わりに実行されます。
つまり、トリガーを起こした INSERT・UPDATE・DELETEステートメントは実行されません。
※実行処理にて明示的にデータ操作を記載しないと、テーブルへ反映はされません。
トリガー動作時期にINSTEAD OFを指定したトリガーを、INSTEAD OFトリガーといいます。
※inserted テーブルと deleted テーブルについて
DMLトリガーでは、insertedテーブル および deletedテーブルという２つの特殊なテーブルが使用されます。
これらのテーブルを使用して、DMLトリガーに条件を設定することができます。
insertedテーブル には、INSERT および UPDATE の実行で影響を受けたデータのコピーが格納されます。
deletedテーブル には、UPDATE および DELETE の実行で影響を受けたデータデータのコピーが格納されます。
UPDATE は、DELETE と、それに続く INSERT の組み合わせとなりますので、deletedテーブル に更新前のデータがコピーされ、更新後のデータが insertedテーブル にコピーされることになります。
AFTER トリガーの作成例
AFTER トリガーを作成する構文は以下の通りとなります。
| CREATE TRIGGER [ トリガー名 ] ON [ テーブル名 ] AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS BEGIN [ 実行したいスクリプト ] END |
AFTERの後に、
レコード追加の後に実行させる場合：INSERT
レコード更新の後に実行させる場合：UPDATE
レコード削除の後に実行させる場合：DELETE
を入力します。「,」で区切って複数入力してもかまいませんが、これから説明する作成例では動きを確認するためにINSERT、UPDATE、DELETEそれぞれ別々にトリガーを作成します。
①INSERT
まずは下記、トリガーのスクリプトを実行して、レコード追加（INSERT）の後に実施するトリガーを作成してみましょう。
このトリガーは売上データにレコードが追加された際に在庫マスタの対応する商品の数量を減らすものとなります。
|
CREATE TRIGGER 売上データ追加_trigger --商品コード、数量の格納先 --追加された商品の商品コード、数量を取得する。 --在庫マスタにある追加された商品の数量を更新する。 END |
■実行確認
売上データにレコードを追加して、AFTER トリガーの動きを確認してみましょう。
下記、INSERT文を実行してみてください。
| INSERT INTO 売上データ (商品コード,数量,売上日) VALUES ('YS01',5,'2021-07-28'); |
INSERT文の実行前は在庫マスタの商品コードがYS01の数量は65でしたが、実行後は売上データに追加した分の数量が引かれ、60に減っていることが確認できます。
■実行前
■実行後
②UPDATE
次に下記、トリガーのスクリプトを実行してレコード更新（UPDATE）の後に実施するトリガーを作成してみましょう。
このトリガーは仕入先マスタのレコードが更新された際に変更前の仕入先コードと同じ仕入先コードが商品マスタに存在する場合に変更後の仕入先コードに更新するものとなります。
|
CREATE TRIGGER 仕入先マスタ更新_trigger --変更前仕入先コード、変更後仕入先コードの格納先 --変更前仕入先コードを取得する。 --商品マスタにある仕入先コードを更新する。 END |
■実行確認
仕入先マスタのレコードを更新して、AFTER トリガーの動きを確認してみましょう。
下記、UPDATE文を実行してみてください。
| UPDATE 仕入先マスタ SET 仕入先コード = 'Y002' WHERE 仕入先名 = '米久'; |
UPDATE文の実行前は商品マスタの仕入先コードがY001のレコードがありますが、実行後は仕入先マスタで更新した仕入先コードに変更されていることが確認できます。
■実行前
■実行後
③DELETE
次に下記、トリガーのスクリプトを実行してレコード削除（DELETE）の後に実施するトリガーを作成してみましょう。
このトリガーは売上データのレコードが削除された際に在庫マスタの対応する商品の数量を増やすものとなります。
|
CREATE TRIGGER 売上データ削除_trigger --商品コード、数量の格納先 --削除された商品の商品コード、数量を取得する。 --在庫マスタにある削除された商品の数量を更新する。 END |
■実行確認
売上データのレコードを削除して、AFTER トリガーの動きを確認してみましょう。
「AFTER トリガーの作成例① 実行確認」で追加したレコードを削除する下記、DELETE文を実行してみてください。
| DELETE FROM 売上データ WHERE 商品コード = 'YS01' AND 売上日 = '2021-07-28 |
DELETE文の実行前は在庫マスタの商品コードがYS01の数量は60でしたが、実行後は売上データを削除した分の数量が足され、65に増えていることが確認できます。
■実行前
■実行後
INSTEAD OF トリガーの作成例
DMLトリガーのINSTEAD OF トリガーを実際に作ってみましょう。
INSTEAD OF トリガーを作成する構文は以下の通りとなります。
|
CREATE TRIGGER [ トリガー名 ] [ 実行したいスクリプト ] |
INSTEAD OFの後に、
レコード追加の時に実行させる場合：INSERT
レコード更新の時に実行させる場合：UPDATE
レコード削除の時に実行させる場合：DELETE
を入力します。「,」で区切って複数入力してもかまいません。
AFTER トリガーでINSERT、UPDATE、DELETE、をそれぞれ確認しましあので、ここではINSERTのみを例にトリガーを作成します。
次に下記、トリガーのスクリプトを実行してレコード追加（INSERT）の時に実施するトリガーを作成してみましょう。
このトリガーは仕入先マスタにレコードが追加される際に既に登録されている仕入先名の場合はエラーを出力し、レコード追加をしないようにするものです。
|
CREATE TRIGGER 仕入先マスタ追加_trigger --既に登録されている仕入先名を別の仕入先コードで追加しようとした場合 RAISERROR (N'同じ仕入先名が存在しています。', 16, 10); END INSERT INTO 仕入先マスタ SELECT * FROM inserted; END |
■実行確認
仕入先マスタにレコードを追加して、INSTEAD OF トリガーの動きを確認してみましょう。
下記、INSERT文を実行してみてください。
|
INSERT INTO 仕入先マスタ (仕入先コード,仕入先名,読み仮名,登録日,更新日) VALUES ('Y999','米久','よねきゅう',NULL,'2021-07-28'); |
既に登録されている仕入先名のため、実行するとエラーメッセージが出力され、仕入先マスタにレコードが追加されていないことが確認できます。
■実行前
■実行後
初めて登録する仕入先名のレコードを追加して、INSTEAD OF トリガーの動きを確認してみましょう。
下記、INSERT文を実行してみてください。
|
INSERT INTO 仕入先マスタ (仕入先コード,仕入先名,読み仮名,登録日,更新日) |
初めて登録する仕入先名のため、実行するとレコードが追加されていることが確認できます。
■実行前
■実行後
以上で、「Microsoft SQL Server」の勉強紹介は終了となります。
学習した『ストアドプロシージャ』『トリガー』を使うとＡＰの修正等無く運用対処的にDB保存データの加工などができますのでアプリケーションの保守性が向上しますが多用すると全体としての処理内容が把握しずらいなど、システム全体に影響してくるので注意して使い分けをしていきたいと思います。
最後に、講習で使った演習問題と解答例を紹介しておきますので、チャレンジして頂ければと思います。
最後までお付き合いいただき、ありがとうございました。