連載
» 2005年09月09日 00時00分 公開

Excel‐Oracle連携(3):ExcelからOracleを更新できれば大助かり (2/4)

[宮本尚志,@IT]

Excelで変更行を判別する方法

 まず1点目の、Excelシート上のどの行が、どのように変更されたか(更新か追加か削除か)を判別する方法を紹介します。ここで紹介する例は、図1のように左端の列(A列)をフラグ列とし、シート上のデータに変更が加えられると、自動的に変更行のA列に更新種別が記入される、というものです。「更新」ボタンを押すと、シート上での変更行のみを対象としてOracleデータベースの表が更新されるようになっています。

図1 Excelシート上に変更を判別するフラグ列を作成する(画像をクリックすると拡大します) 図1 Excelシート上に変更を判別するフラグ列を作成する(画像をクリックすると拡大します)

 このExcelシートで使用しているマクロはリスト3です。

Private Sub Worksheet_Change(ByVal Target As Range)
    
  Dim iCountArea As Integer    '変更されたレンジ個数
  Dim iCount     As Long       'レンジの行数
  Dim i          As Long       'カウンタ
  Dim j          As Long       'カウンタ
  Dim iRow       As Long       '行

  '変更個所がデータビュー外の場合は何もしない
  If Target.Column > 10 Or Target.Column < 3 _
  Or Target.Row < 9 Then
    Exit Sub
  End If

  ' 変更されたレンジ個数を取得
  iCountArea = Target.Areas.Count

  ' 変更されたレンジごとに、各行の状態をセットする
  For j = 1 To iCountArea

    iCount = Target.Areas(j).Rows.Count

    For i = 0 To iCount - 1
      iRow = Target.Areas(j).Row + i
      If (theSheet.Cells(iRow, 1).ID = "") Then  
        If (theSheet.Range("C" & iRow & ":J" & _
                               iRow).Text = "") Then 
          '新しく追加された後に消された行の場合、A列は空に
          theSheet.Cells(iRow, 1) = ""
        Else
          '新しく追加された行のA列は「追加」に
          theSheet.Cells(iRow, 1) = "追加"
        End If
      Else
        If (theSheet.Range("C" & iRow & ":J" & _
                               iRow).Text = "") Then
          '空にされた行のA列は「削除」に
          theSheet.Cells(iRow, 1) = "削除"
        Else
          '変更された行のA列は「更新」に
          theSheet.Cells(iRow, 1) = "更新"
        End If
      End If
      Next i
    Next j
    
End Sub
リスト3 更新行・更新種別のフラグを立てるマクロ

 このマクロはワークシートのChangeイベントを使用しているため、シート上のセルの値が変更された場合に自動的に呼び出されます。結果的に、セルの値を変えるだけでフラグ列に更新種別が入ります。後はシート上の「更新」ボタンがクリックされたときに、フラグ列の値に従って更新/追加/削除の処理を行うだけです。

更新操作における同時実行性の制御

 次に、ほかのユーザーが同じデータを変更していた場合にどうするかですが、一般的な制御としては

  1. ほかのユーザーが変更していたかどうかにかかわらず更新する
  2. 更新するかどうかを選べるようにする
  3. ほかのユーザーが更新していたら必ず失敗させる

のいずれかを行うことになります。

 (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のような形です。赤字部分がトランザクション・レベルを設定する部分です。

Private oraconn As New ADODB.Connection
'接続処理(省略)
'トランザクション開始
oraconn.BeginTrans
'トランザクション・レベル設定
oraconn.IsolationLevel = adXactSerializable
'検索処理実行(省略)
'更新処理実行(省略)
リスト4 ADO+ODBC、ADO+OLEの場合のイメージ

Private OraSess As OraSession
Private OraDB As OraDatabase 
'接続処理(省略)
'トランザクション開始
OraDB.BeginTrans 
'トランザクション・レベル設定
OraDB.ExecuteSQL ("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
'検索処理実行(省略)
'更新処理実行(省略)
リスト5 oo4oの場合のイメージ

 このマクロで更新処理をしようとしたときに、検索処理後にほかのトランザクションがデータを変更していた場合はエラーが返され、更新処理は失敗します。一度commitまたはrollbackしてトランザクションを終了させ、新たにトランザクションを開始して更新することになります。実際にはこれらの処理は複数のプロシージャに分かれると思いますが、その際はトランザクションの開始と終了の制御についても考慮する必要があります。

 なお、本記事で使用したマクロ(oo4oのみ、トランザクション・レベルの変更は除く)を含んだExcelファイルを以下のリンクからダウンロードすることが可能です。

次ページに続く)

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。