| MSDE FunClub |
|
最終更新日 : 2000/08/18 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.18
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第７章４２３ｐ 〜 ４２８ｐ掲載】 |
-- [SQL07_06.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、キーセットドリブンカーソル
-- を使った現在行の修正です
--
-- カレントデータベースを MySampleTest にする
USE MySampleTest
GO
-- (件処理されました)のメッセージを抑止する
SET NOCOUNT ON
GO
-- *********************************************************
-- 【事前確認チェック】
-- [社員]表の[社員コード]に主キーが設定されていますか？
-- Accessのテーブルを単純に転送コピー（Export）しただけでは
-- 主キーは設定されません
-- 主キーが設定されていないテーブルに対して、キーセットドリブン
-- カーソルは使うことができません
-- ［注意］ストアドプロシージャ sp_pkeys を使えばもっと簡単に
-- 調査することができます(SQL08_01を参照)
-- *********************************************************
DECLARE @pk_name varchar(80) -- 主キーに付けられた制約名
DECLARE @msg varchar(256)
DECLARE @cnt int
DECLARE @cl_name varchar(80)
-- 社員表の主キーに付けられた制約名を、システムビュー表から取得する
SELECT @pk_name = CONSTRAINT_NAME FROM MySampleTest.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = '社員' AND CONSTRAINT_TYPE = 'PRIMARY KEY')
IF( @pk_name is NULL )
BEGIN
PRINT ''
PRINT '社員表に主キーが設定されていません。'
PRINT 'キーセットドリブンカーソルを使う時は、主キーが必要です。'
PRINT 'ACCESSのテーブルを転送しただけでは、主キーは設定されません。'
PRINT '社員コードに主キーを設定してください'
PRINT 'SQL07_06B.SQL プログラムを先に実行しましょう'
PRINT ''
RAISERROR('主キーがありません。中止します',16,127)
RETURN
END
-- 主キーがあります。その主キーは、社員コードに付けられたものでしょうか？
-- 連結主キーの場合は、エラーとする
SELECT @cnt = count(*) FROM MySampleTest.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE (TABLE_NAME = '社員' AND CONSTRAINT_NAME = @pk_name )
IF( @cnt <> 1 )
BEGIN
RAISERROR('列数(%d)個 に振られた連結主キーです。単一列に設定してください',16,127,@cnt)
RETURN
END
-- 主キーが付けられた列の名前を取得する
SELECT @cl_name = COLUMN_NAME FROM MySampleTest.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE (TABLE_NAME = '社員' AND CONSTRAINT_NAME = @pk_name )
IF( @cl_name <> '社員コード' )
BEGIN
RAISERROR('主キーが列名(%s)に付けられていますが、''社員コード'' 列に変更してください'
,16,127,@cl_name)
RETURN
END
PRINT ''
PRINT '社員コード列に主キーが設定されています（事前確認OK!）'
PRINT 'キーセットドリブンカーソルを使った修正を行ないます'
GO
-- *********************************************************
-- キーセットドリブンカーソルを使った修正を行ないます
-- *********************************************************
-- 作業用変数の定義
DECLARE @total int , @cnt int , @code int
DECLARE @社員コード int , @社員コード2 int
DECLARE @氏名 varchar(40) , @氏名2 varchar(40)
DECLARE @FETCH int
DECLARE @OPTIMISTIC int
-- 次の値を =1 にすると、エラーが起こります
SET @FETCH = 0 -- FETCHエラーの発生
SET @OPTIMISTIC = 1 -- OPTIMISTICロック違反の発生
PRINT ''
PRINT '【@FETCH=' + CAST(@FETCH AS varchar) +
' @OPTIMISTIC=' + CAST(@OPTIMISTIC AS varchar) + ' 】で実行中'
--////////////////////////////////////////////////////////////////
-- 【キーセットドリブンカーソルを定義】
-- キーセット内容は固定されますので、レコード総数の増減はありません
-- OPTIMISTICロックのため、更新エラーが起こる可能性があります
--////////////////////////////////////////////////////////////////
DECLARE My_cur CURSOR LOCAL SCROLL KEYSET
OPTIMISTIC TYPE_WARNING
FOR SELECT 社員コード,氏名 FROM MySampleTest..社員
WHERE 社員コード BETWEEN 100 AND 299
ORDER BY 社員コード
FOR UPDATE of 氏名
-- ローカルカーソルを開く
OPEN My_cur
-- 結果セットの行数を取得する
SET @total = @@CURSOR_ROWS
PRINT ''
RAISERROR('結果セット内のレコード総数 = %d',0,1,@total) WITH NOWAIT
IF( @total = 0 )
BEGIN
PRINT 'レコードの取得に失敗しました'
PRINT 'もう１度、［社員］テーブルをAccessから転送してください'
RAISERROR('中止します',0,127)
RETURN
END
-- ************************************************
-- 静的キーセットの管理内容と矛盾を起こさせます
-- レコードを削除したり主キーの値を変更するとカーソルの
-- 管理内容と現実が異なり、FETCHエラーが起こります
-- ************************************************
--** 待機している間に手動で変更してもよい
--** RAISERROR('30秒間待機する間に、他接続からレコードを削除や主キーの内容変更しましょう'
--** , 0 , 1 ) WITH NOWAIT
--** WAITFOR DELAY '00:00:30'
--** RAISERROR('待機終了' , 0 , 1 ) WITH NOWAIT
-- 自分から故意に変更した方が早いです
IF( @FETCH = 1 )
BEGIN
-- 一番小さい社員コードの主キーの値を変更する
UPDATE 社員 SET 社員コード = 社員コード * 10
WHERE 社員コード = (SELECT MIN(社員コード) FROM 社員 )
END
-- 正の値のときは、結果セットがあります
IF( @total > 0 )
BEGIN
SET @cnt = 0
-- 結果セットを１行づつ取得する
WHILE( @cnt <> @total )
BEGIN
-- １行進めて、その内容を取得
FETCH NEXT FROM My_cur INTO @社員コード,@氏名
--/////////////////////////////////////////////////////////
-- FETCHが正しくできましたか？
-- キーセットドリブンカーソルでは以下のFETCHエラーが起こる
-- 外部の接続から
-- (1)レコードが削除された場合
-- (2)主キーの値が変更された場合
-- この事象が起こると、@@FETCH_STATUS は -2 を返します
--////////////////////////////////////////////////////////
IF( @@FETCH_STATUS <> 0 )
BEGIN
PRINT ''
PRINT '******************************'
PRINT '**** FETCH ERROR 発生!! ****'
PRINT '******************************'
PRINT ''
PRINT '外部変数@@FETCH_STATUS = ' + CAST(@@FETCH_STATUS as varchar)
IF( @@FETCH_STATUS = -2 )
BEGIN
PRINT ''
PRINT 'FETCH命令で取得しようとしたレコードが、'
PRINT '実際のテーブルにありません'
PRINT '外部の者によってそのレコードが変更・削除されました'
END
--終了します
CLOSE My_cur
DEALLOCATE My_cur
RAISERROR('中止します',0,127)
RETURN
END
SET @cnt = @cnt + 1
RAISERROR('%2d:%4d %s',0,1,@cnt,@社員コード,@氏名) WITH NOWAIT
-- ******************************************
-- OPTIMISTICロック違反を起こします
-- 部外者によって、現在行の内容を変更します
-- もちろん、自分から故意に変更することもできます
-- ******************************************
IF( @OPTIMISTIC = 1 )
BEGIN
--**RAISERROR('15秒間待機する間に、レコードの氏名の値を変更しましょう'
--** , 0 , 1 ) WITH NOWAIT
--**WAITFOR DELAY '00:00:15'
--**RAISERROR('待機終了' , 0 , 1 ) WITH NOWAIT
-- 自分から故意に変更した方が早いです
UPDATE 社員 SET 氏名 = '直接更新しました'
WHERE 社員コード = @社員コード
END
--**************************************
-- カーソル現在行の内容を修正する
--**************************************
UPDATE 社員
SET 氏名 = CAST(@社員コード AS varchar )
WHERE CURRENT OF My_cur
--/////////////////////////////////////////////////////
-- 更新が失敗しましたか？
-- その時は、
-- サーバー : メッセージ 16947、レベル 10、状態 1
-- 行は更新または削除されませんでした。
-- エラーが起こります
--/////////////////////////////////////////////////////
SET @code = @@ERROR -- エラー番号の取得
IF( @code <> 0 )
BEGIN
PRINT ''
PRINT '****************************************'
PRINT '**** Optimistic更新エラーの発生!! ****'
PRINT '****************************************'
PRINT ''
--更新エラーが発生しました
RAISERROR( 'エラーコードは 16947 ですか? ErrCode=%d' ,0,127,@code)
--終了します
CLOSE My_cur
DEALLOCATE My_cur
RETURN
END
-- 現在行の内容を取得する
FETCH RELATIVE 0 FROM My_cur INTO @社員コード2 , @氏名2
-- 確認します
IF( @氏名2 <> CAST(@社員コード AS varchar ) )
BEGIN
RAISERROR( 'あれ？ 更新が反映されていないよ？' , 16 , 127 )
RETURN
END
-- 元の氏名に戻す
UPDATE 社員 SET 氏名 = @氏名
WHERE CURRENT OF My_cur
-- 上記の更新命令ではエラーは起きないでしょう
-- エラーチェックは省略します
END
END
-- カーソルを閉じる
CLOSE My_cur
-- 参照関係を解除する
DEALLOCATE My_cur
GO
