1. 概要
最近業務でデータ移行ツールを作成することがあった。
私が担当する業務の中の、移行対象のテーブル数は約20くらい。
データ数もはっきりとは数えないけど10万件は軽く越すので、手作業での以降はまず不可能()
対象業務が同じで、別システムからの乗り換えとなり、異なるシステムのテーブルからの移行が発生するので、それをSQLで行う必要があるということになる。
最初は何も考えず以下のようにしていた。
- 対象のテーブル毎でストアドを組む
- 別システムであれば業務に対するテーブルの仕様が異なることがあるので、整合性を取るための処理をする(別テーブルの移行後データを参照して別テーブルの値を整形するなど)
-
1,2までで作成しているストアドを順次実行するストアドを作成する
この時、3で作成しているストアドを流しても途中でエラーが返ることがあった。
このときはその都度3で作成していたストアドからエラーになるストアドまでを切り取って実行し、エラーの原因を特定するなどしていた。
。。。すごい手間
ということで、今回は上記の1,2までのストアドを順次実行するためのストアドを作り替えることにしました。
2. 前提
- 1,2までに作成しているストアドは、第一引数としてデバッグ実行を制御する真偽値を有していること。
これでそのストアドをデバッグ実行する場合は最終的にストアドを実行した際の移行後データを返す作りにする。
- 第１引数以降にそれぞれのストアドで扱うパラメータを有していること
第１引数以降は任意のものとする。(なくてもOK)
これ以降の内容の前提知識
テーブル作成やストアドの中のリソースをSELECT〜で指定するまでは割とSQL標準なのですが、それ以降のカーソルを使用した反復処理がSQLServerの独自拡張であるT-SQLを使用しているのでもしかしたら理解しづらいかもです。
ですがそんなに難しい話じゃないので、読んでみてください。
3. 仕様
まず、動的SQLで実行するために考えたことは実行するストアドを補完するテーブルを作ることです。
このテーブルをリソースにして、カーソルで処理させることで順次実行制御を行います。
3.1. テーブル仕様
- テーブルには、実行順連番とストアド名、オプションパラメータを文字列で格納するようにしました。
create table sample_stored_execsequence (
exec_id int IS NOT NULL,
stored_name nvarchar(max) IS NOT NULL,
some_params nvarchar(max) IS NOT NULL
);
exec_idで実行順序を指定し
stored_nameに動的SQLで読み込むストアド名
some_paramsにストアドで使用する第一引数以降のパラメータを文字列で持たせる
ようにしました。
また、some_paramsに格納するパラメータは後述するストアドの使用上カンマから始めて、パラメータの間にパラメータを挟んで格納することが必要です
時間があれば外部キーを挟んでsome_paramsを一つずつ指定できる構成でもよかったんですが、めんどくさかったので今回はこれで。
3.2. ストアドの実装
後は上記のテーブルにストアドやパラメータ等を挿入すれば、それをリソースにストアドを組むだけです。
下記にサンプルを載せておきます。
create procedure sample_sequential_exec
--実行時のパラメータ
@debug_flg int = 0, --本来はbit型にするのが正なのですがbitを文字列変換の方法がなぜかわからなかったのでintにしました
@debug_id int = 0,
--カーソルで使用するパラメータ
@exec_id int = 0,
@stored_name nvarchar(max) = N'',
@some_params nvarchar(max) = N''
as
begin transaction
--データソースを取得
declare sample_cursor cursor for
select
exec_id
, stored_name
, some_params
from
sample_stored_execsequence
order by exec_id --保険
--カーソルで１行ずつ処理
open sample_cursor;
fetch next from sample_cursor
into
@exec_id
, @stored_name
, @some_params
while @@fetch_status = 0
begin
if @exec_id = @debug_id
begin
exec(@stored_name + str(@debug_flg) + @some_params)
goto endloop
end
else
exec(@stored_name + '0' + @some_params)
fetch next from sample_cursor
into
@exec_id
, @stored_name
, @some_params
end
endloop:
close sample_cursor
deallocate sample_cursor
commit transaction
end
4. どうなったか
-
@debug_flgと@debug_idで任意の位置で処理を止めて、実行結果を確認できる
元来の実装では、手続き型のような感じでただストアドを並べていただけだったので、それを任意の位置で実行するとすると、いちいち切り取って実行したりして面倒だったけど、その手間がなくなった。
- 作成したテーブルの中でレコードの
exec_idを入れ替えれば実行順をすぐに変更できる
ストアドの実行順を変更するのに順次実行するストアドの中身をイジる必要がなくなった。
5. まとめ
今回はSQLServerでの実装であり、その中でカーソルなどは T-SQLというSQLServerの独自拡張のため、そのまま転用はできないと思うが、少しは効率化できたのではないかと思う。