コンピューターシステム株式会社

技術BLOG

Microsoft SQL Serverの勉強をしてみよう・その2

データベース 2021/12/15 松山担当

こんにちは。松山本社のWANPOです。

前回紹介しておりました、自社の講習会でMicrosoft SQL Serverについて紹介したいと思います。
第2回目としては「Microsoft SQL Server」のストアドプロシージャ・トリガーについて学んだので紹介致します。

ストアドプロシージャの説明

ストアドプロシージャ(stored procedure)とは

データベース管理システム(DBMS)の機能の1つで、DBに対する連続した複数の処理を1つのプログラムにまとめ、データとともに保存できるようにしたものです。
さまざまな SQL文に加え、通常の SQL では扱えない入出力のパラメータや、条件分岐や繰り返しといったプログラミング機能を含めることが可能です。
クライアントは作成されたストアドプロシージャを呼び出すだけで、事前に登録された一連の処理を何度でも確実に実行することができます。

<作成方法>

オブジェクトエクスプローラーから、目的のデータベースを展開し、 [プログラミング] を展開します。
次に、 [ストアドプロシージャ] を右クリックし、「ストアドプロシージャ」を選択することで作成できます。

 


ストアドプロシージャのメリット、デメリット
  1. メリット

一度コンパイルされて実行可能な形式で格納されているため、SQL文を1つずつ呼び出す場合と比べ処理が高速になります。
自動的にキャッシュされ、ユーザ間で共有される結果、メモリー要件や起動時のオーバーヘッドが低減されます。
DBに対する処理をアプリケーションから隔離できます。
データベーストリガーと組み合わせることで、複雑なルールによるデータの参照整合性保持が可能になります。
 ⇒業務システムに関する処理内容を変更したい場合、関連するストアドプロシージャのみを更新するだけで対応可能なため、
  アプリケーションの保守性が向上します。

  1. デメリット

SQLで実現できる全ての処理を定義できますが、ストアドプロシージャの保存場所がDBサーバであるため、ネットワーク上で複数の処理が一斉に実行されると、DBサーバへの負荷、およびネットワークのトラフィックも増加することになります。
汎用のプログラミング言語に比べ機能や拡張性、開発ツールなどの環境は劣るため、過度に処理内容をストアドプロシージャ側で動かせることで、かえってシステム全体の生産性や保守性を下げてしまう場合もあります。
 ⇒管理が煩雑になります。

ストアドプロシージャの作成例

ストアドプロシージャを作成する構文は以下の通りとなります。

CREATE PROCEDURE [ ストアドプロシージャ名 ]
[ @入力パラメーター名1 ] [ 入力パラメータ名1のデータ型 ]  
[ @入力パラメーター名2 ] [ 入力パラメータ名2のデータ型 ]
[ @入力パラメーター名3 ] [ 入力パラメータ名3のデータ型 ]
・・・・・
AS
BEGIN

[ 実行したいスクリプト ]

END

SQLの実行環境

 SQLの実行方法について説明します。

 まずは、「新しいクエリ」をクリックしてください。

SQLエディターが開きました。

このSQLエディター内に、SQL文を記述してください。

SQLを実行する際は、データベースが「課題用データベース」となっていることを確認し「実行」をクリックしてください。

※以降のSQL実行は、このSQLエディターで行います。

SELECTを実行するストアドプロシージャ

まずは、簡単なストアドプロシージャを作ってみましょう。

作成するのは、在庫マスタテーブルから「数量」が30未満のデータを抽出するストアドプロシージャです。

CREATE PROCEDURE sp30未満在庫
AS
BEGIN

  SELECT 商品コード,数量,更新日 FROM 在庫マスタ
  WHERE 数量 < 30
END

上記のSQL文を実行すると「sp30未満在庫」という名前の

ストアドプロシージャが作成されます。

では、実行してみましょう。ストアドプロシージャを実行する

には、「EXEC」を使用します。

EXEC sp30未満在庫

SELECT文を実行したのと同じ結果を返します。

引数を用いたストアドプロシージャ

引数を用いたストアドプロシージャを作ってみましょう。

作成するのは、在庫マスタテーブルから「数量」が「引数で渡された値」未満のデータを抽出するストアドプロシージャです。

CREATE PROCEDURE sp指定数未満在庫
@在庫 int
AS
BEGIN

  SELECT 商品コード,数量,更新日 FROM 在庫マスタ
  WHERE 数量 < @在庫
END

では、引数に「30」を指定して実行してみましょう。

EXEC sp指定数未満在庫 30

上で実行した引数無しのストアドプロシージャ「EXEC sp30未満在庫」と同じ結果が返されます。

繰り返し処理を行うストアドプロシージャ

繰り返し処理を行うストアドプロシージャを作ってみましょう。

作成するのは、売上データより商品ごとの売上合計を求め、売上集計テーブルに結果を更新するストアドプロシージャです。

CREATE PROCEDURE sp売上集計データ作成
AS
BEGIN 

-- 変数とカーソルの宣言
DECLARE @商品コード nvarchar(4)
DECLARE @数量合計 int

DECLARE cursor売上データ CURSOR FOR
SELECT 商品コード, SUM(数量) AS 数量合計
FROM 売上データ
GROUP BY 商品コード

-- 売上集計テーブルを初期化
DELETE FROM 売上集計

-- メイン処理
OPEN cursor売上データ

FETCH NEXT FROM cursor売上データ
INTO @商品コード, @数量合計

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO 売上集計 (商品コード, 数量合計)
VALUES (@商品コード, @数量合計)

FETCH NEXT FROM cursor売上データ
INTO @商品コード, @数量合計
END

-- 終了処理
CLOSE cursor売上データ
DEALLOCATE cursor売上データ

END

「DECLARE ~ CURSOR FOR」というSQL文ですが、これはカーソルを宣言しています。カーソルは、SELECT文の実行結果を1行ずつ取り出すことができます。

DECLARE [ カーソル名 ] CURSOR FOR [ カーソルを使用するSELECT文 ]

「OPEN」でカーソルを利用できるようにします。

OPEN [ カーソル名 ]  

「FETCH」というSQL文で、カーソルから1行を読み込み、ローカル変数へ値を格納します。

FETCH [ FETCHタイプ ] FROM [ カーソル名 ] INTO [ 結果を格納するローカル変数 ]

FETCHタイプは、どの行を読み込むのかを指定します。

今回の例では「NEXT」を使用しましたが、これは現在行を読み込むということです。このほかに最初の行を読み込む「FIRST」、最後の行を読み込む「LAST」があります。

「@@FETCH_STATUS」というグローバルシステム変数が 利用されています。「FETCH」で正常に値が読み込めた場合は、この変数に「0」がセットされます。最終の行を超えて値が読み込めなくなった場合は「-2」がセットされます。

今回の例では、「@@FETCH_STATUS」が「0」の間、処理を繰り返す構文になっています。つまりデータが取得できる間、処理を繰り返す。ということになります。

利用が終了したら「CLOSE」を行います。

CLOSE [ カーソル名 ]

それでは、説明が長くなりましたが、実行してみましょう。

EXEC sp売上集計データ作成

結果は、売上集計テーブルに格納させているのでデータを確認してみましょう。

SELECT 商品コード,数量合計 FROM 売上集計

トリガーの説明

トリガー(trigger)とは

データベース管理システム(DBMS)の機能の1つで、テーブルに何かの操作(追加・更新・削除)が加えられた時に、
あらかじめ定義した処理を自動的に実行する機能のことで、ストアドプロシージャの一種となります。
アプリケーションから呼び出されるものではなく、アプリケーションには全く依存しないものとなります。
例えば、トリガーを使用して以下の事ができます。

  • テーブルの更新時に別のテーブルを更新
  • 更新内容が不適切な場合に強制的にロールバックを実施

<作成方法>

オブジェクトエクスプローラーから、目的のデータベースを展開し、 [テーブル] から目的のテーブルを展開します。
次に「トリガー] を右クリックし、「新しいトリガー」を選択することで作成できます。


トリガーのメリット、デメリット
  1. メリット

複数のアプリケーションで共通に実行する処理をトリガーに定義することで、アプリケーションを簡略化することができます。
 ⇒個別のアプリケーションをシンプルに記述できます。
 ⇒多様しなければ保守が容易になる。

  1. デメリット

トリガーはテーブルにデータ操作文が実行されるたびに実行されるため、一つずつ順番にしか処理できない。
そのため、トリガの実行数が増えると処理が遅くなる。
 ⇒処理全体の見通しが悪くなり、意図しない動作を引き起こす危険性がある。

アプリケーション自体はトリガーによってどのような処理が行われるのかを知ることができないため、トリガーを多用するとアプリケーションによる処理とトリガーによる処理の関係が不明確となり、全体としての処理内容が把握しずらい。
 ⇒保守性が低下する可能性あり。

トリガーの種類
  1. ログオントリガー

ログオン時に、ユーザーセッションが確立される時に実行されます。
例えば、ログオントリガーを使って 「 既にセッションが確立されているユーザーが、他のセッションでログオンしようとした場合は拒否する 」 というようなことができます。

  1. DDL トリガー

CREATE・ALTER・DROP ステートメントのような、DDL イベントと呼ばれるデータベースのスキーマを変更するようなイベントをきっかけに実行されます。
例えば DDL トリガーを使うと、データベースのスキーマ変更を防いだり、データベーススキーマの変更記録を残したりすることができます。

  1. DML トリガー

テーブルやビューに対して、INSERT・UPDATE・DELETEステートメントのような、データの挿入・変更・削除等が起こった時に実行されます。
トリガーを起こしたステートメントと同じトランザクションとして扱われるので、トリガー内でエラーを起こすことによって、トランザクションをロールバックさせることもできます。

DML トリガーの種類
  1. BEFORE トリガー

テーブルに対する操作の前に、トリガーを実行します。
トリガー動作時期にBEFOREを指定したトリガーを、BEFOREトリガーといいます。
なお、SQL Serverでは、BEFOREトリガーは実装されていません。

  1. AFTER トリガー

テーブルに対するINSERT・UPDATE・DELETEステートメントの動作の後に、トリガーを実行します。
トリガー動作時期にAFTERを指定したトリガーを、AFTERトリガーといいます。

  1. INSTEAD OF トリガー

トリガーを起こしたINSERT・UPDATE・DELETEステートメントの動作の代わりに実行されます。
つまり、トリガーを起こした INSERT・UPDATE・DELETEステートメントは実行されません。
※実行処理にて明示的にデータ操作を記載しないと、テーブルへ反映はされません。
トリガー動作時期にINSTEAD OFを指定したトリガーを、INSTEAD OFトリガーといいます。

※inserted テーブルと deleted テーブルについて

DMLトリガーでは、insertedテーブル および deletedテーブルという2つの特殊なテーブルが使用されます。
これらのテーブルを使用して、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
ON 売上データ
AFTER INSERT
AS
BEGIN

--商品コード、数量の格納先
DECLARE @SHOCODE nvarchar(10)
DECLARE @NO int

--追加された商品の商品コード、数量を取得する。
SELECT @SHOCODE = 商品コード, @NO = 数量 FROM inserted;

--在庫マスタにある追加された商品の数量を更新する。
UPDATE 在庫マスタ SET 数量 = 数量 - @NO WHERE 商品コード = @SHOCODE;

END

■実行確認

売上データにレコードを追加して、AFTER トリガーの動きを確認してみましょう。

下記、INSERT文を実行してみてください。

INSERT INTO 売上データ (商品コード,数量,売上日) VALUES ('YS01',5,'2021-07-28');

INSERT文の実行前は在庫マスタの商品コードがYS01の数量は65でしたが、実行後は売上データに追加した分の数量が引かれ、60に減っていることが確認できます。

■実行前

■実行後

②UPDATE

次に下記、トリガーのスクリプトを実行してレコード更新(UPDATE)の後に実施するトリガーを作成してみましょう。

このトリガーは仕入先マスタのレコードが更新された際に変更前の仕入先コードと同じ仕入先コードが商品マスタに存在する場合に変更後の仕入先コードに更新するものとなります。

CREATE TRIGGER 仕入先マスタ更新_trigger
ON 仕入先マスタ
AFTER UPDATE
AS
BEGIN

--変更前仕入先コード、変更後仕入先コードの格納先
DECLARE @BEFORECODE nvarchar(4)
DECLARE @AFTERCODE nvarchar(4)

--変更前仕入先コードを取得する。
SELECT @BEFORECODE = 仕入先コード FROM deleted;
--変更後仕入先コードを取得する。
SELECT @AFTERCODE = 仕入先コード FROM inserted;

--商品マスタにある仕入先コードを更新する。
UPDATE 商品マスタ SET 仕入先コード = @AFTERCODE WHERE 仕入先コード = @BEFORECODE;

END

■実行確認

仕入先マスタのレコードを更新して、AFTER トリガーの動きを確認してみましょう。

下記、UPDATE文を実行してみてください。

UPDATE 仕入先マスタ SET 仕入先コード = 'Y002' WHERE 仕入先名 = '米久';

UPDATE文の実行前は商品マスタの仕入先コードがY001のレコードがありますが、実行後は仕入先マスタで更新した仕入先コードに変更されていることが確認できます。

■実行前

■実行後

③DELETE

次に下記、トリガーのスクリプトを実行してレコード削除(DELETE)の後に実施するトリガーを作成してみましょう。

このトリガーは売上データのレコードが削除された際に在庫マスタの対応する商品の数量を増やすものとなります。

CREATE TRIGGER 売上データ削除_trigger
ON 売上データ
AFTER DELETE
AS
BEGIN

--商品コード、数量の格納先
DECLARE @SHOCODE nvarchar(10)
DECLARE @NO int

--削除された商品の商品コード、数量を取得する。
SELECT @SHOCODE = 商品コード, @NO = 数量 FROM deleted;

--在庫マスタにある削除された商品の数量を更新する。
UPDATE 在庫マスタ SET 数量 = 数量 + @NO WHERE 商品コード = @SHOCODE;

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 [ トリガー名 ]
ON [ テーブル名 ]
INSTEAD OF { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
BEGIN

[ 実行したいスクリプト ]

END

INSTEAD OFの後に、

レコード追加の時に実行させる場合:INSERT

レコード更新の時に実行させる場合:UPDATE

レコード削除の時に実行させる場合:DELETE

を入力します。「,」で区切って複数入力してもかまいません。

AFTER トリガーでINSERT、UPDATE、DELETE、をそれぞれ確認しましあので、ここではINSERTのみを例にトリガーを作成します。

次に下記、トリガーのスクリプトを実行してレコード追加(INSERT)の時に実施するトリガーを作成してみましょう。

このトリガーは仕入先マスタにレコードが追加される際に既に登録されている仕入先名の場合はエラーを出力し、レコード追加をしないようにするものです。

CREATE TRIGGER 仕入先マスタ追加_trigger
ON 仕入先マスタ
INSTEAD OF INSERT
AS
BEGIN

--既に登録されている仕入先名を別の仕入先コードで追加しようとした場合
--エラーメッセージを出力する。

IF EXISTS
(SELECT *
FROM 仕入先マスタ
INNER JOIN inserted ON 仕入先マスタ.仕入先名 = inserted.仕入先名
WHERE 仕入先マスタ.仕入先コード <> inserted.仕入先コード)
BEGIN

RAISERROR (N'同じ仕入先名が存在しています。', 16, 10);

END
--初めて登録する仕入先の場合、仕入先マスタに追加する。
ELSE
BEGIN

INSERT INTO 仕入先マスタ SELECT * FROM inserted;

END
END

■実行確認

仕入先マスタにレコードを追加して、INSTEAD OF トリガーの動きを確認してみましょう。

下記、INSERT文を実行してみてください。

INSERT INTO 仕入先マスタ (仕入先コード,仕入先名,読み仮名,登録日,更新日)

VALUES ('Y999','米久','よねきゅう',NULL,'2021-07-28');

既に登録されている仕入先名のため、実行するとエラーメッセージが出力され、仕入先マスタにレコードが追加されていないことが確認できます。

■実行前

■実行後

初めて登録する仕入先名のレコードを追加して、INSTEAD OF トリガーの動きを確認してみましょう。

下記、INSERT文を実行してみてください。

INSERT INTO 仕入先マスタ (仕入先コード,仕入先名,読み仮名,登録日,更新日)
VALUES ('Y999','日本食研','にほんしょっけん',NULL,'2021-07-28');

初めて登録する仕入先名のため、実行するとレコードが追加されていることが確認できます。

■実行前

■実行後


以上で、「Microsoft SQL Server」の勉強紹介は終了となります。

学習した『ストアドプロシージャ』『トリガー』を使うとAPの修正等無く運用対処的にDB保存データの加工などができますのでアプリケーションの保守性が向上しますが多用すると全体としての処理内容が把握しずらいなど、システム全体に影響してくるので注意して使い分けをしていきたいと思います。

最後に、講習で使った演習問題と解答例を紹介しておきますので、チャレンジして頂ければと思います。

最後までお付き合いいただき、ありがとうございました。