こんにちは。ERPパッケージシステム導入コンサルタントのafroriansymです。SQLを書く楽しみが減ってきて少々退屈していますので、ブログに書き残しておきます。
本記事は、私と同じくERPパッケージシステム導入コンサルタントやDBエンジニア向けの内容とご認識ください。（システム開発担当者の方には、初歩的すぎて微妙かと思いますが、よろしければお付き合いください。）
とはいえ、SQLに興味をお持ちの方にも、こんな風に使うことがあるのか、といった情報提供になるかと考えております。よろしければご覧ください。
システム運用上でSQLを利用するケース
前回の記事では、トラブル対応（システムの不具合で緊急対応が必要な場合）で利用するケースのSQL作業お作法を書きました。
www.afroriansym100life-shift.net
今回は、システムに機能が不足している場合に、やむを得ずDB作業で対応を行うケースのSQLを書いてみます。
（とはいえ最近のシステムはSQLなど書かずとも標準機能が充実してきているように感じますが、そういう話はここでは置いておきます）
システムの機能不足を補う具体的な事例
勤怠管理システムや、交通費などの経費精算システムでは、申請を提出して、上長（権限者）が承認するプロセスがあります。
承認ができる上長は、特定の人に限られます。たとえば、役職でいうと係長以上とか。役職はなくとも、実質チームリーダーとしての業務上の役割を持つ人とか。
こうした、申請の承認を行う人のことを、システム上の”権限”を持つ人。権限を与えるべき人、と定義します。こうした権限を持つ人は、定期的にメンテナンスをする必要があります。たとえば、チーム編成が変わったときや所属組織の編成があったときに、役職者やチームリーダーも変わります。それに伴って、権限を誰に与えるか、権限を無くす（はずす、と表現することもあります）ことが発生します。
こうしたメンテナンスが簡単にできれば良いのですが、簡単に設定することが難しいケースがあります。今回紹介するケースは、システムがまだ古い時代だったため、「CSVデータ取込み機能」がありませんでした。そのため、一回のオペレーションで権限者を登録・削除することができず、設定画面上から、１人１人削除や登録する必要がありました。（複数回の画面操作オペレーションが必要でした）
この場合、多くの人数の権限をメンテナンスする必要があるほど、大変です。例えば、大企業で所属やチームの数が多いほど、単純にシステム上の権限を与えるべき人が多くなる、と考えればイメージしやすいかと思います。
そこで、SQLを利用して時間短縮というわけです。
実際の作業手順（バックアップ⇒ワークテーブル作成⇒差分調査⇒削除・挿入⇒バックアップ削除（後日））
1,000名の権限登録が必要な場合、１名分の権限登録をする時間が1分であれば、1,000分、つまり16時間以上はかかることになります。2人で登録するなら丸1日かかります。（2人日ですね）
今回、SQL実行前に、あらかじめ誰に権限を登録するのか、というデータをCSVファイルで作成しておいて頂くことを前提としました。
権限テーブル名称を、KENGEN、としています。SQL作業の手順は以下の通りです。
１．バックアップ
２．ワークテーブル作成
３．ワークテーブル内データDELETE
４．受領した取込み対象データをワークテーブルにDBツールでCSVデータ取込み
５．権限テーブルで不要なデータを削除する
６．権限テーブルに追加が必要なデータをワークテーブルからINSERTする
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
１．バックアップ
create table BK_20180216_KENGEN as SELECT * FROM KENGEN
（KENGENテーブルを、BK_20180216_KENGENテーブルという名前でバックアップする）
※ select count(*) from KENGEN で、バックアップ元のテーブルのデータ件数（レコード行数）と、バックアップ後のテーブルの件数も念のため確認しておくとよいかも。
確認したほうが良い理由：
本番環境が稼働している最中の作業を強行する場合だと差分が出てしまう可能性があるため。（たまたま、SQLでバックアップする前後に、システム利用者が権限者の登録をするケースが考えられる。ただし、作業前後にユーザー様へ連絡をして、担当者が操作することをストップするなど準備しておけば不要かもしれない。とはいえ情報連携不足で不慮の事件は発生するもの。件数チェックはしたほうが良い）
２．ワークテーブル作成
create table WORK_20180216_KENGEN as SELECT * FROM KENGEN
（KENGENテーブルを、WORK_20180216_KENGENテーブルという名前でワークテーブル、つまり作業用のテーブルとする）
３．ワークテーブル内のデータをDELETE(※truncateでもよい)
delete from WORK_20180216_KENGEN
（ワークテーブル内のデータを全て削除して、新規に権限登録すべきデータ取込みの準備をする）
４．システムのユーザー様が準備した取込対象データをワークテーブルにDBツールでCSVデータ取込み
※ 取込対象データは、新規に登録すべき人、現状のとおり権限登録したままにすべき人が含まれる。現在は権限持っているが、今後は権限登録不要な人は含まれない。
５．権限テーブルで不要なデータを削除する
delete FROM KENGEN k
where not exists(select * from WORK_20180216_KENGEN w
where w.id = k.id
and w.syozoku_cd = k.sr_syz_cd)
（ワークテーブル上には存在しないが、KENGENテーブルに存在するデータは、今後は不要な権限データのため、削除する）
６．権限テーブルに追加が必要なデータをワークテーブルからINSERTする
insert into KENGEN
SELECT * FROM WORK_20180216_KENGEN w
where not exists(select * from KENGEN k
where k.id = w.id
and k.syozoku_cd = w.syozoku_cd)
（KENGENテーブルに、現状存在しないが、今後必要となるデータのみをワークテーブルから挿入する）
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ここまでが一連の作業となります。ただし、DBの表領域の関係上、バックアップテーブルは、作業が問題無く完了した後、可能であれば早めに削除してしまうようにしたいところです。
さて、ここまで書いて、何か突っ込まれそうなことに気づいたので書いておきます。
そもそも、ワークテーブルなんか不要じゃなかろうか？ ということですが、わざわざ上記の作業手順にした理由は１つです。すでにシステムが稼働しているためです。
システムをメンテナンスの名目でストップしてやれば、確かにワークテーブルを作らなくても、バックアップテーブル作っておいて、KENGENテーブルの中身のデータをDELETEして、CSVファイルをKENGENテーブルに取り込めば終わりです。
しかし、システムの稼働をストップするための社内プロセスが面倒であるケースもあります。そうした利害関係をもとに、ではこういう流れでやりましょうか。として対応したケースです。
補足（KENGENテーブルに”履歴”がある場合の操作例と注意事項）
こちらは自分向けのメモですが、記載しておきます。
とある社員（Aさん）だけ、2018/12/31までで権限を削除したい場合
update KENGEN set end_date = 2018/12/31 where id in (select id from EMPLOYEE where id = ‘A100101’)
（社員番号と社員IDを管理するテーブル”EMPLOYEE”で、社員番号がA100101である社員Aは、2018/12/31でKENGENの有効期間をストップするという意味合いで、KENGENテーブルの有効期間end_dateを2018/12/31と更新する）
注）ただし、これはどのようなシステム上であっても、このように操作すれば、Aさんが、2018/12/31以前の勤怠期間であれば権限保持する対象所属に属する人の勤怠を参照する権限があるとは限らない。
某システムではとあるコントロール設定が別にあり、
そのコントロールがONになっていなければ、履歴を切れば、ログイン日が対象期間を過ぎていれば、参照することはできない。ONになっていれば、参照することは可能である。
こちらも突っ込みどころのある内容だが、自分向けなのでいったんここまででご容赦願います。
まとめ
結構長くなってしまいましたが、ポイントは２点です。
１．システムの機能不足に対応するSQLは、ユーザーの運用状況にあった対応を考えてSQL作業手順を作るべし
２．今回、テーブル間のデータ差分をとるには、where not exists(select … という感じのを使いました
次は group by を使った例を出してみようかと。
参考書籍： DBに関する業務について知りたい場合に、ご一読ください。
新卒して就職したての時によく読んでいました。
ベテランが丁寧に教えてくれる データベースの知識と実務 (IT ENGINEER’S Basic)
- 作者: 金宏和實
- 出版社/メーカー: 翔泳社
- 発売日: 2007/12/19
- メディア: 単行本（ソフトカバー）
- 購入: 2人 クリック: 11回
- この商品を含むブログ (4件) を見る
以上。
コメント