第 4 章 「ビジネスロジックのプログラミング」～ MCDBA をめざそう!! MCP 70-229 SQL Server 2000 開発編 ～

NEC

Eラーニング事業部

鈴木 智行

2002 年 7 月 22 日

目次

1. 前回の復習問題の解答と解説
2. データ処理の管理
3. 手続き型ビジネスロジックの強制
4. プログラミングオブジェクトのトラブルシューティング
5. 復習問題

1. 前回の復習問題の解答と解説

問 1 の解答　ウ

（解説）ACCESS からのデータコピーは BCP や BULK INSERT では実現できません。また、SQL Server でスナップショットレプリケーションを定期的にスケジューリングする場合、ACCESS はサブスクライバとしてしか定義できないため、エも不適格です。したがってウが正解となります。

問 2 の解答　エ

（解説）選択リストに社員 ID と社員名があり、GROUP BY 句を利用して集計に使用している場合には、必ず社員 ID と社員名が GROUP BY 句にも必要になります。したがって GROUP BY 句が適切に指定されているエが正解になります。

問 3 の解答　ウ

（解説）WHERE 句の条件が間違っているイは不正解ですが、他の選択肢は論理的に間違っていません。しかしこの問題はカーソルやサブクエリを使用しなくても実現できるため、リソースを他より使用せず、コストが低いウが最も適切な解となります。

2. データ処理の管理

2.1 エラー処理の実装

@@ERROR は最後に実行した Transact-SQL ステートメントのエラー番号を返すシステム関数です。したがって Transact-SQL ステートメントの直後で @@ERROR を検査してエラー処理を実装することができます。

（例）売上テーブルに1件挿入し、その成否によって出力するメッセージを変更する。

insert 売上
values(N'さとう','N03',50,50)
if @@error <> 0 
 　　print 'FAILURE'
else　--@@error=0は正常終了を意味します
 print 'SUCCESS'

また SET XACT_ABORT ON によって Transact-SQL が実行時エラーを発生したときに SQL Server が現在のトランザクションをロールバックするように指定できます。

2.2 パラメータの引渡しと応答

SQL Server のストアドプロシージャでは入力パラメータと出力パラメータを使用できます。入力パラメータは CREATE PROCEDURE ステートメントで1つ以上の変数を宣言します。出力パラメータは CREATE PROCEDURE ステートメントと EXECUTE ステートメントの両方において OUTPUT キーワードを指定します。

（例）社員名を入力パラメータとし、売上高を出力パラメータとする、本日売上確認ストアドプロシージャを作成する。

CREATE PROCEDURE dbo.本日売上確認
@name1 nvarchar(10),
@result int output
AS
select @result = SUM(製品単価*売上個数)  from 売上
where 社員名 = @name1
group by 社員名

（例）社員名を入力パラメータとし、本日売上確認ストアドプロシージャを実行する。　　　その後、出力パラメータから結果を得る。

declare @uriage int
execute dbo.本日売上確認 N'すずき',@uriage output
select @uriage　

また、ユーザー定義関数でも入力パラメータを使用できますが、スカラ関数ではスカラ値が、複数ステートメントテーブル値関数とインラインテーブル値関数ではテーブルが出力されます。

（例）ユーザー毎の本日売上をテーブルで返すユーザー毎本日売上ユーザー定義関数を作成する。

            CREATE FUNCTION dbo.ユーザー毎本日売上
(@name1 nchar(10))
returns table
AS
return(select 社員名, SUM(製品単価*売上個数)  as 売上高 from 売上
where 社員名 = @name1
group by 社員名)

（例）ユーザー毎本日売上ユーザー定義関数に社員名をパラメータとして与えると結果がテーブルとして戻る。

select * from dbo.ユーザー毎本日売上(N'すずき')

3. 手続き型ビジネスロジックの強制

3.1 トリガ動作の指定

SQL Server 7.0 からは 3 つの AFTER トリガ（INSERT トリガ、DELETE トリガ、UPDATE トリガ）をそれぞれ複数指定できますが、それに加え SQL Server 2000 からは同種の複数の AFTER トリガの中で、最初と最後に実行するトリガを sp_settriggerorder システムストアドプロシージャで指定できるようになりました。

（例）受注テーブルの在庫確認トリガを最後に起動するように設定する

            sp_settriggerorder
@triggername = '在庫確認',
@order = 'LAST',
@stmttype = 'INSERT'

また現在のトリガの設定は OBJECTPROPERTY 関数で調べることができます（画面 3-1-1）。画面 3-1-1 では、結果が 1（True）で返ってくるため最後に起動するトリガであることがわかります。

画面 3-1-1 在庫確認トリガが最後に起動するトリガかどうかを確認する

3.2 トランザクションの設計と管理

トランザクションはできるだけ短くすることが必要です。トランザクションが長いとロックされたデータにユーザーがアクセスする可能性が高くなり、同時実行性が減少します。トランザクションを短くするには以下の方法を使用します。

• トランザクション中にユーザーからの入力を要求しない

• 更新系のステートメントは最小限の行を操作する

• WHILE ステートメントなどの特定のステートメントの使用に注意する。

また必要に応じてトランザクション分離レベルを設定できます。データの一貫性をどれくらいのレベルで保つためにも使用されますが、分離レベルの設定によって同時実行性も変化します（READ UNCOMMITTED→READ COMMITTED →REPEATABLE READ →SERIALIZABLEの順に高くなります）。SQL Server の既定の分離レベルは READ COMMITTED です。

表 3-2-1 SQL Server の 4 つのトランザクション分離レベル

3.3 フローの制御の管理

SQL Server の明示的なトランザクションではトランザクションのネストが可能です。

図 3-3-1 トランザクションのネスト

ただし、トランザクションは最も外側のトランザクションのみが適用されます。図 3-3-1 の場合では、トランザクションＡ（TranA）の Commit ステートメント（ア）によって TableA、TableB、TableC に対する Update がコミットされます。トランザクションＢ（TranB）の Commit ステートメント（イ）では TableB に対する Update はコミットされません。

このような場合では @@TRANCOUNT システム関数を使い、開いているトランザクションが存在するかどうか、またそれらのトランザクションがどれだけ深くネストしているかを判断できます。@@TRANCOUNT は開いているトランザクションが存在しない場合は 0 の値をとります。また Begin Transaction ステートメントによって1ずつ増加し、Rollback Transaction ステートメントや Commit Transaction ステートメントによって 1 ずつ減少します。

また、トリガもトランザクションであり 32 レベルまでネスト可能ですが、ネストした一連のトリガ内のいずれかのレベルで失敗があると、トランザクション全体が取り消され、データに対して加えられた変更はすべてロールバックされますので注意してください。このような場合には sp_configure 'nested triggers', 0 でトリガのネストを無効にすることもできます。

4. プログラミングオブジェクトのトラブルシューティング

4-1 Transact-SQL デバッガ

Transact-SQL デバッガは SQL クエリアナライザのオブジェクトブラウザから実行でき（画面 4-1-1）、ストアドプロシージャをデバッグすることができます（画面 4-1-2）。

画面 4-1-1 オブジェクトブラウザからの Transact-SQL デバッガの起動

画面 4-1-2 Transact-SQL デバッガのインタフェース

4-2 WITH CHECK OPTION

SQL Server のデフォルトではビューを経由するデータ修正ステートメントについて、修正の影響を受ける行がビューの範囲内に含まれているかのチェックがされません。例えば以下の例のように社員名さとうが取り扱った製品の一覧を出力するビューを作成します。

（例）社員名さとうが取り扱った製品の一覧を出力するビューを作成

CREATE VIEW さとう本日取扱製品_view AS
SELECT DISTINCT 社員名, 製品ID
FROM            dbo.売上
WHERE           (社員名 = N'さとう')

このビューに対し insert さとう本日取扱製品_view values(N'すずき',N'N05')というステートメントでビューの範囲外のデータを入力します。すると画面 4-2-1 のように正常終了します。

画面 4-2-1 ビューに範囲外データを入力

こうしたビューの範囲外の修正をチェックしたい場合にはビューの作成時に WITH CHECK OPTION を指定する。

（例）上記のビューを WITH CHECK OPTION 付きで作成する

CREATE VIEW さとう本日取扱製品_view AS
SELECT  社員名, 製品ID
FROM            dbo.売上
WHERE           (社員名 = N'さとう')
WITH CHECK OPTION

このビューに対し insert さとう本日取扱製品_view values(N'すずき',N'N06')というステートメントでビューの範囲外のデータを入力します。すると画面 4-2-2のようにエラーが発生し、ビューの範囲外で行われる可能性がある更新をチェックできます。

画面 4-2-2 WITH CHECK OPTION 付きビューに範囲外データを入力

5. 復習問題

解答、解説は次回に記述します、復習として考えてみてください。

問 1 SQLServer 2000 のある一つのテーブルに INSERT トリガを４つ (TrigerA、TrigerB、TrigerC、TrigerD) 定義し、TrigerA→TrigerB→TrigerC→TrigerD の順番で実行するようにしたい。どうすればいいですか？もっとも正しい選択肢を選択しなさい。

ア．sp_settriggerorder ストアドプロシージャでトリガの順番を指定する。

イ．nested triggers 環境設定オプションでトリガの順番を指定する。

ウ．同じテーブルに INSERT トリガを４つ指定することはできない。

エ．このような順番でトリガを指定することはできない。

問 2 2 つのアプリケーションが同時に同じデータを更新するときには更新の喪失（ロストアップデート）が起こり、最初に更新した情報が喪失してしまう場合があります。SQL Server でこの更新の喪失を防ぐためにはどうすればよいですか？もっとも正しい選択肢を選びなさい。

ア．トランザクション分離レベルを、最低限 REPEATABLE READ にする必要がある。
イ．トランザクション分離レベルを、SERIALIZABLE にする必要がある。

ウ．特に設定しなくても防ぐことは可能。

エ．更新の喪失を防ぐことはできない。

問3　売上表に対し、下記の SQL 文を実行しました。本日売上_view ビューについて正しいものを選択してください。

表名: 売上

※社員名は NOT NULL、他は NULL を設定している

（SQL文）

CREATE VIEW 本日売上_view AS
SELECT  社員名, 製品ID  SUM(製品単価*売上個数)  as 売上高
FROM    dbo.売上
GROUP BY 社員名, 製品ID
WITH CHECK OPTION

ア．CREATE VIEW 文はエラーとなり本日売上_viewビューは作成できない。

イ．NULL を許可しない社員名を指定すれば本日売上_viewビューからデータ更新可能である。

ウ．NULL を許可している製品IDを指定すれば本日売上_viewビューからデータ更新可能である。

エ．WITH CHECK OPTION が指定されているため、ビューの範囲外のデータは更新できない。

鈴木   智行 : NEC Eラーニング事業部に所属。 入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。Windows 2000 および SQL Server 2000 での MCSA, MCSE, MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア （データベース） も取得済。最近は MCA の 3 科目 （データベース、OS／ネットワーク、アプリケーション構築） 全てに合格し、C# を勉強中。