SEQUENCEによるAutoIncカラムの実現

ORACLEのテーブルのカラム型には、いわゆるAutoInc型やAutoNumber型のように、新規レコードを挿入すると自動的にインクリメントされてユニークになることが保証されるカラム型というものはありません。

かわりに、SEQUENCEというカウンタ様の仕組みを利用して、新しいキー値を取得して使います。

  CREATE SEQUENCE MySeq
  START WITH 1
  INCREMENT BY 1;

のようにして作成したシーケンスを使って、新しいキーを取得してデータを挿入するには、このようにして、シーケンスのカウントアップを行います。

  INSERT INTO MyTable VALUES (MySeq.NextVal, Data1, Data2 ... )

あるいは、以下のように、シーケンスのカウントアップと挿入を２段階に分けて実行します。

  DECLARE
    NewKey NUMBER(10);
  BEGIN
    SELECT MySeq.Nextval INTO NewKey FROM dual;
    INSERT INTO TESTINS VALUES(NewKey, 'AA');
  END;

後者の方法は、Master/Detail関係にある複数テーブルに一度に新規レコードを追加する場合などにとられます。たとえばこんな風に…

  DECLARE
    NewKey NUMBER(10);
  BEGIN
    SELECT MySeq.Nextval INTO NewKey FROM dual;
    INSERT INTO MASTER VALUES(NewKey, 'AA');
    INSERT INTO DETAIL VALUES(NewKey, 0, 'CCD', 'EEE');
    INSERT INTO DETAIL VALUES(NewKey, 1, 'CCF', 'GGG');

  END;

TOracleDataSetを用いてデータを挿入する際に、SequenceFieldプロパティをセットしておくと、テーブルの特定カラムの値を、シーケンスから取得するようにできます。

  • TOracleDataSet
    • property
      SequenceField :
      新規レコードの特定のカラムに、シーケンスから値を取得するよう設定します

      • Sequence: シーケンスの名称
      • Field : シーケンスから取得した値をセットすべきフィールド名
      • ApplyMoment: シーケンスからのキー取得のタイミング。
        以下の３つのいずれかです。

        • On New Record　： Append/Insert のタイミングでNextValを取得します
        • On Post ： レコードを PostするタイミングでNextValを取得します
        • On Server ： NextValは、INSERT文/UPDATE文の一部として実行されます。

たとえば、TOracleDataSet.SQL = SELECT EMPNO, ENAME, ...FROM EMP のとき、Sequence = MySeq,
Field=EMPNOの場合、

  1. ApplyMoment = On New Record だとすると、OracleDataSet1.Append; によってSELECT MySeq.NextVal INTO ***
    が実行されて、その結果が即座にDataSetにセットされます。この場合、データセットの更新をCancelしても、シーケンスの値は元に戻らないので、カウンタの値を一つ無駄にしてしまうことになります。
  2. ApplyMoment = On Post
    だと、Appendの時点でDataSetのEMPNOフィールドはNULLのままで、ENAMEプロパティをセットしてPostするタイミングで、まずSELECT
    MySeq.NextVal INTO *
    が実行され、ついでその結果のキー値をDataSetのEMPNOフィールドにセットしてから、INSERT文が実行されます。（このApplyMoment=OnPost**が基本と考えていいでしょう）
  3. ApplyMoment = On Server の場合、Append/ Post
    の時点で、データセットのEMPNOフィールドはNULLのまま、INSERT INTO EMP VALUES( MySeq.NextVal, ENAME,
    ....)
    が実行されます。これによって、データベース上のテーブルには新しいキー値を持つ有効なレコードが作成されますが、このままではローカルのTOracleDataSet上のレコードのEMPNOはNULLのままです。そのため、TOracleDataSetの
    RefreshOptionsプロパティを適切にセットして、データの更新後にレコードの再読込を行わせる必要があります。なお、RefreshOptions.roAfterInsertがセットされている場合には、この方法が効率的です。

デフォルト値およびトリガーによる更新に関する考慮

テーブルのカラムはデフォルト値が設定できます。たとえば

  create table TestTable
  (
    KEY  number not null,
    DATA number default 0 not null
  );

のようにして作成したテーブルにデータを挿入する際、DATAフィールドの値が与えられなかった場合、デフォルト値の0がテーブルにセットされます。

また、このようにトリガーが設定されていると、テーブルデータを更新する都度、トリガーが働いてデータが更新されます。

  create or replace trigger MyTrigger
    before update on testtable  
    for each row
  begin
    Update testtable
    Set data = data + 1;
  end MyTrigger;

これらの機能はデータの整合性を保つために有用ですが、サーバ上でデータが自動的に更新されることによって、クライアントのTOracleDataSetが保持しているデータレコードと、サーバ上のレコードのデータ間に不一致が生ずる原因となりえます。この不一致を解消するためには、随時サーバからデータを読み込みなおす必要があります。このためにRefreshOptionsプロパティを使います。

  • TOracleQuery

    • property
      RefreshOptions:
      サーバデータ読み込みのタイミングを設定します。以下の４つのタイミングの組み合わせです

      • roAfterInsert :
        INSERT終了後に、サーバからデータを読み直し、デフォルト値やトリガーの結果を反映します
      • roAfterUpdate :
        UPDATE終了後に、サーバからデータを読み直し、デフォルト値やトリガーの結果を反映します
      • roBeforeEdit :
        他のユーザがレコードを更新していれば、その変更を編集直前に再読込します。後述の、レコードロックに関する説明を参考にしてください。
      • roAllFields : TRUEの場合JOINで結合された
        他のテーブルのレコードや、計算項目も読み直します。この際、もとのSQL文のWHERE句に
        rowid = :rowid
        を加えたクエリが実行されますが、もとのWHERE句の条件によってはこのクエリにヒットするレコードが0件になってしまう問題があるため、工夫が必要になることがあります。詳しくはTOracleQuery.RefreshOptionsのヘルプを参照してください。
カテゴリー
DOA
前の記事
TOracleDataSet によるデータの更新
2010年4月18日
次の記事
TOracleDataSet とレコードのロック
2010年4月20日