ExcelからOracleを更新できれば大助かり：Excel‐Oracle連携（3）（2/4 ページ）
企業の部門内に蓄積された大量のExcelデータをデータベースで管理したい。あるいは、Excelで作成した業務書類からシステムに手作業でデータ入力する工数を削減したい。このようなニーズに応えるべく、OracleデータベースにはExcelからOracleへのさまざまな連携機能が備わっている。（編集部）
Excelで変更行を判別する方法
まず1点目の、Excelシート上のどの行が、どのように変更されたか（更新か追加か削除か）を判別する方法を紹介します。ここで紹介する例は、図1のように左端の列（A列）をフラグ列とし、シート上のデータに変更が加えられると、自動的に変更行のA列に更新種別が記入される、というものです。「更新」ボタンを押すと、シート上での変更行のみを対象としてOracleデータベースの表が更新されるようになっています。
このExcelシートで使用しているマクロはリスト3です。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
このマクロはワークシートのChangeイベントを使用しているため、シート上のセルの値が変更された場合に自動的に呼び出されます。結果的に、セルの値を変えるだけでフラグ列に更新種別が入ります。後はシート上の「更新」ボタンがクリックされたときに、フラグ列の値に従って更新／追加／削除の処理を行うだけです。
更新操作における同時実行性の制御
次に、ほかのユーザーが同じデータを変更していた場合にどうするかですが、一般的な制御としては
- ほかのユーザーが変更していたかどうかにかかわらず更新する
- 更新するかどうかを選べるようにする
- ほかのユーザーが更新していたら必ず失敗させる
のいずれかを行うことになります。
（1）の方法は簡単で、何も気にせず更新をすれば、ほかのユーザーがすでに確定させた変更を上書きして更新することになります。
（2）の制御を行うには、ほかのユーザーが更新しているかどうかを判別する必要があります。大まかな実装としては、Excel側に検索時のデータを持っておいて、それを更新時のOracleデータベースの表データと比較し、同じかどうかでほかのユーザーが更新をしたか判別するのが一般的でしょう。
実際にどのように比較するかについては、Excelに検索結果を出力する際に、2つのシートに出力しておき、そのうち1つのシートは比較用として変更不可にする、あるいはデータベースの表の各行に、変更のたびに増加する変更番号のような列を持っておき、Excelから更新する際に変更番号をExcel側とデータベース側で比較する、などの方法があります。
前者の方法では、1つ1つの列についてExcelシートとOracleデータベースの表を比較することになるので、列数が増えると処理時間も増えます。後者の場合には、変更番号列のみを比較すればよいので、負荷の低い処理で済みますが、Oracleデータベースの表に変更番号列がない場合は、表定義の変更が発生します。どちらも長所・短所がありますが、一般的には後者の方法を使う方が多いかもしれません。
（3）に関しては、今回紹介しているようなマスタ表メンテナンスのExcelアプリケーションではあまり採用しない方法ですが、フォーム画面から1件のデータを編集するような場合に見られます。（2）と同じ判別方法でも実装できますが、ExcelからOracleデータベースに処理を行う際のトランザクション・レベルを変更することでも可能です（トランザクションおよびトランザクション・レベルについては、マニュアル「Oracle Database概要」13−7ページを参照してください）。
具体的には、トランザクションを開始する前（検索を開始する前）に、トランザクション・レベルを「SERIALIZABLE」にしておくと、commitするまでの1つのトランザクションの間で、更新対象の表のデータがほかのトランザクションにより変更された場合に、更新処理が失敗するようになります。マクロのイメージとしてはリスト4、リスト5のような形です。赤字部分がトランザクション・レベルを設定する部分です。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
このマクロで更新処理をしようとしたときに、検索処理後にほかのトランザクションがデータを変更していた場合はエラーが返され、更新処理は失敗します。一度commitまたはrollbackしてトランザクションを終了させ、新たにトランザクションを開始して更新することになります。実際にはこれらの処理は複数のプロシージャに分かれると思いますが、その際はトランザクションの開始と終了の制御についても考慮する必要があります。
なお、本記事で使用したマクロ（oo4oのみ、トランザクション・レベルの変更は除く）を含んだExcelファイルを以下のリンクからダウンロードすることが可能です。
（次ページに続く）
Copyright © ITmedia, Inc. All Rights Reserved.