| MSDE FunClub |
|
最終更新日 : 2000/08/21 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.21
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第８章４７５ｐ 〜 ４７９ｐ掲載】 |
-- [SQL08_05.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- トランザクションの入れ子構造
-- を理解します
--
-- この［社員］テーブルは、AccessのNorthwindデモデータベース
-- の［社員］テーブルをSQLServerに転送したものです
--
-- カレントデータベースを MySampleTest にする
USE MySampleTest
GO
-- (件処理されました)のメッセージを抑止する
SET NOCOUNT ON
GO
-- *******************************************************************************
-- Transact-SQLスクリプトでは、バッチ間で情報伝達を行う手段が限られている
-- ここでは、空白のエラーメッセージを登録し、そのエラーメッセージ番号を使った
-- 情報伝達方法を紹介します
-- RAISERROR命令を使うと、エラーメッセージ番号が@@ERRORに設定されることを利用する
-- *******************************************************************************
--ユーザ定義のエラーメッセージを登録する
--先に英語版を登録し、次に日本語版メッセージを登録する
--メッセージ番号は sysmessagesテーブルの error列の最大値の次にしてもよい
--ここでは、適当な番号としています
IF EXISTS( SELECT error FROM master..sysmessages WHERE error = 60001 ) EXEC sp_dropmessage 60001,'all' --既存メッセージの削除
IF EXISTS( SELECT error FROM master..sysmessages WHERE error = 60002 ) EXEC sp_dropmessage 60002,'all'
IF EXISTS( SELECT error FROM master..sysmessages WHERE error = 60003 ) EXEC sp_dropmessage 60003,'all'
-- メッセージの内容を空白１個にすること
EXEC sp_addmessage 60001,1,' ','us_english'
EXEC sp_addmessage 60001,1,' ','日本語'
EXEC sp_addmessage 60002,1,' ','us_english'
EXEC sp_addmessage 60002,1,' ','日本語'
EXEC sp_addmessage 60003,1,' ','us_english'
EXEC sp_addmessage 60003,1,' ','日本語'
GO
-- レコード挿入位置にレコードがあれば削除します
DELETE FROM 社員 WHERE( 社員コード BETWEEN 10 AND 20 )
GO
-- *********************************************************
-- 【トランザクションの３重入れ子構造を理解します】
-- *********************************************************
DECLARE @OutFlag int , @MidFlag int , @InFlag int
-- フラグが１のとき、ROLLBACKが行われます
SELECT @OutFlag = 0 ,
@MidFlag = 0 ,
@InFlag = 0
PRINT ''
PRINT ' @OutFlag=' + CAST(@OutFlag as varchar) +
' @midFlag=' + CAST(@MidFlag as varchar) +
' @InFlag =' + CAST(@InFlag as varchar)
IF(@InFlag=1) PRINT '一番内側のトランザクション内でROLLBACKします'
ELSE IF(@MidFlag=1) PRINT ' 中間 のトランザクション内でROLLBACKします'
ELSE IF(@OutFlag=1) PRINT ' 外側 のトランザクション内でROLLBACKします'
PRINT ''
PRINT 'プログラム開始直後トランザクションネストレベル=' + CAST(@@TRANCOUNT AS varchar)
-- 一番外側のトランザクション開始
BEGIN TRANSACTION OutTran
PRINT '外側トランザクション開始 ネストレベル=' + CAST(@@TRANCOUNT AS varchar)
INSERT INTO 社員(社員コード,フリガナ,氏名) VALUES(10,'ヤマダ イチロウ' , '山田 一郎')
-- 中間のトランザクション開始
BEGIN TRANSACTION MidTran
PRINT '中間トランザクション開始 ネストレベル=' + CAST(@@TRANCOUNT AS varchar)
INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(11,'ヤマダ ジロウ' , '山田 二郎')
-- 内側のトランザクション開始
BEGIN TRANSACTION InTran
PRINT '内側トランザクション開始 ネストレベル=' + CAST(@@TRANCOUNT AS varchar)
INSERT INTO 社員(社員コード,フリガナ,氏名)
VALUES(12,'ヤマダ サブロウ', '山田 三郎')
IF( @InFlag = 1 )
BEGIN
-- ロールバックを行う
-- 名前を付けるときは一番外側のトランザクション名
-- 名前は省略することができる
ROLLBACK TRANSACTION OutTran
PRINT '内側でROLLBACK 実行直後ネストレベル=' + CAST(@@TRANCOUNT AS varchar)
-- エラー番号を@@ERROR外部変数にセットする
-- WITH SETERRORオプションを忘れずに!
RAISERROR(60001,0,1) WITH SETERROR
RETURN
END
-- 内側のトランザクションをコミットする
COMMIT TRANSACTION InTran
PRINT '内側トランザクション終了後ネストレベル=' + CAST(@@TRANCOUNT AS varchar)
IF( @MidFlag = 1 )
BEGIN
-- ロールバックを行う
-- 名前を付けるときは一番外側のトランザクション名
ROLLBACK TRANSACTION
PRINT '中間でROLLBACK 実行直後ネストレベル=' + CAST(@@TRANCOUNT AS varchar)
-- エラー番号を@@ERROR外部変数にセットする
-- WITH SETERRORオプションを忘れずに!
RAISERROR(60002,0,1) WITH SETERROR
RETURN
END
-- 中間のトランザクションをコミットする
COMMIT TRANSACTION MidTran
PRINT '中間トランザクション終了後ネストレベル=' + CAST(@@TRANCOUNT AS varchar)
IF( @OutFlag = 1 )
BEGIN
-- ロールバックを行う
-- 名前を付けるときは一番外側のトランザクション名
ROLLBACK TRANSACTION
PRINT '外側でROLLBACK 実行直後ネストレベル=' + CAST(@@TRANCOUNT AS varchar)
-- エラー番号を@@ERROR外部変数にセットする
-- WITH SETERRORオプションを忘れずに!
RAISERROR(60003,0,1) WITH SETERROR
RETURN
END
-- 外側のトランザクションをコミットする
COMMIT TRANSACTION OutTran
PRINT '外側トランザクション終了後ネストレベル=' + CAST(@@TRANCOUNT AS varchar)
GO
-- *******************************
-- 直前のバッチの実行状態を調べる
-- *******************************
DECLARE @Code int
SELECT @Code = @@ERROR
IF( @Code <> 0 )
BEGIN
PRINT '直前のバッチは、エラーメッセージ番号 ' + CAST(@Code as varchar) +
' で終了しました'
PRINT CASE @Code WHEN 60001 THEN '内側'
WHEN 60002 THEN '中間'
WHEN 60003 THEN '外側'
END + 'トランザクションで ROLLBACK を実行しました'
END
GO
-- *******************************
-- レコードの登録結果を表示
-- *******************************
DECLARE @社員コード int , @フリガナ varchar(20) , @氏名 varchar(20)
DECLARE @Str社員コード varchar(4) , @strout varchar(80)
DECLARE hC INSENSITIVE CURSOR FOR
SELECT 社員コード,フリガナ,氏名 FROM 社員
WHERE( 社員コード BETWEEN 10 AND 20 )
-- カーソルを開いてレコードを表示する
OPEN hC
-- レコードはありますか？
IF( @@CURSOR_ROWS = 0 )
BEGIN
PRINT '*** レコードは存在しません ***'
PRINT ' レコード登録に失敗!! '
GOTO L9999
END
-- タイトル行の出力
EXEC master..xp_sprintf @strout OUTPUT , '[%.2s][%12s][%12s]' ,
'NO' , 'フリガナ' , ' 氏 名 '
PRINT ''
PRINT '**** 登録結果 ****'
PRINT @strout
-- 先頭行の取り出し
FETCH NEXT FROM hC INTO @社員コード,@フリガナ,@氏名
-- 0の時は、正常に取得できました
WHILE( @@FETCH_STATUS = 0 )
BEGIN
-- 取り出し内容の編集とその出力
SELECT @Str社員コード = CONVERT(char(4),@社員コード)
EXEC master..xp_sprintf @strout OUTPUT , '[%.2s][%12s][%12s]' ,
@Str社員コード , @フリガナ , @氏名
PRINT @strout
-- 次行の取り出し
FETCH NEXT FROM hC INTO @社員コード,@フリガナ,@氏名
END
L9999:
-- カーソルを閉じて破棄する
CLOSE hC
DEALLOCATE hC
GO
