| MSDE FunClub |
|
最終更新日 : 2000/08/21 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.21
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第８章４５６ｐ 〜 ４５９ｐ掲載】 |
-- [SQL08_01.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- 明示的なトランザクションでよく誤る例
-- です
-- トランザクション内の整合性違反によって全体がロールバック
-- されると勘違いする例です
--
-- この［社員］テーブルは、AccessのNorthwindデモデータベース
-- の［社員］テーブルをSQLServerに転送したものです
--
-- カレントデータベースを MySampleTest にする
USE MySampleTest
GO
-- (件処理されました)のメッセージを抑止する
SET NOCOUNT ON
GO
-- *********************************************************
-- 【事前確認チェック】
-- [社員]テーブルの[社員コード]に主キーが設定されていますか？
-- Accessのテーブルを単純に転送コピー（Export）しただけでは
-- 主キーは設定されません
-- ［参照］
-- SQL07_06でも主キー検査を行っていますが、ここでは
-- ストアドプロシージャ sp_pkeys を使った方法を使います
-- *********************************************************
DECLARE @CNT int , @CL_NAME sysname
SELECT @CNT = 0 ,
@CL_NAME = ''
-- sp_pkeysの結果受け取り用一時テーブルの作成
CREATE TABLE #TMP_PKEYS(
TABLE_QUALIFIER sysname , -- テーブル識別子の名前
TABLE_OWNER sysname , -- テーブル所有者の名前
TABLE_NAME sysname , -- テーブルの名前
COLUMN_NAME sysname , -- 返される TABLE_NAME の各列の列名
KEY_SEQ smallint , -- 連結主キーのシーケンス番号
PK_NAME sysname -- 主キー識別子
)
-- sp_pkeysを実行する
-- 社員テーブルの主キー情報を一時テーブルに格納する
INSERT INTO #TMP_PKEYS EXECUTE sp_pkeys '社員'
-- 主キーがありますか？（得られた行数は１行でないとダメ）
SELECT @CNT = COUNT(*) FROM #TMP_PKEYS
IF( @CNT <> 1 )
BEGIN
DROP TABLE #TMP_PKEYS -- 一時テーブル削除
IF( @CNT = 0 )
BEGIN
PRINT '社員表に主キーが設定されていません。'
PRINT 'ACCESSのテーブルを転送しただけでは、主キーは設定されません。'
PRINT '社員コードに主キーを設定してください'
RAISERROR('主キーがありません。中止します',16,127)
END
ELSE
BEGIN
RAISERROR('列数(%d)個に振られた連結主キーです。単一列にしてください',16,127,@CNT)
END
RETURN
END
-- その主キーは、社員コードに付けられたものでしょうか？
SELECT @CL_NAME = COLUMN_NAME FROM #TMP_PKEYS
IF( @CL_NAME <> '社員コード' )
BEGIN
DROP TABLE #TMP_PKEYS -- 一時テーブル削除
RAISERROR('主キーが列名(%s)に付けられています。社員コード列にしてください'
,16,127, @CL_NAME)
RETURN
END
DROP TABLE #TMP_PKEYS -- 一時テーブル削除
PRINT ''
-- PRINT '社員コード列に主キーが設定されています（事前確認OK!）'
GO
-- レコード挿入位置にレコードがあれば削除します
DELETE FROM 社員 WHERE( 社員コード BETWEEN 10 AND 20 )
GO
-- *********************************************************
-- 【トランザクションを使ったレコード挿入処理】
-- トランザクション内で主キーの重複登録整合性違反を発生させます
-- *********************************************************
BEGIN TRANSACTION -- トランザクション開始
--（注）実際は、半角カナ文字です
INSERT INTO 社員(社員コード,フリガナ,氏名) VALUES(10,'ヤマダ イチロウ' , '山田 一郎')
INSERT INTO 社員(社員コード,フリガナ,氏名) VALUES(11,'ヤマダ ジロウ' , '山田 二郎')
INSERT INTO 社員(社員コード,フリガナ,氏名) VALUES(12,'ヤマダ サブロウ', '山田 三郎')
-- 社員コード11番は、整合性違反。主キーがすでに登録済み
INSERT INTO 社員(社員コード,フリガナ,氏名) VALUES(11,'カトウ イチロウ' , '加藤 一郎')
COMMIT TRANSACTION
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 '*** レコードは存在しません ***'
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
