B コードおよび問合せのサンプル

この付録では、AUTONUMBERデータ型のエミュレーション、CASCADE UPDATE参照整合性および名前マッピング問合せをサポートするためのコードおよび問合せの例を示します。内容は次のとおりです。

• B.1 AUTONUMBERデータ型のエミュレーション

• B.2 名前マッピング問合せ

• B.3 デフォルト値

• B.4 列および表の検証

• B.5 CASCADE UPDATEトリガー・コード

B.1 AUTONUMBERデータ型のエミュレーション

Microsoft Accessでは、AUTONUMBERデータ型がサポートされています。AUTONUMBERデータ型は、固有のJet DBMSファイル内の列に対して、一定に増加する長整数型の順序を提供します。Oracleでは順序がサポートされています。順序は、列で一意の識別子として使用可能な番号のセットを生成します。Microsoft AccessのAUTONUMBERとOracleの順序との重要な相違点は、Oracleでは、新規レコードを表に挿入する際に順序番号を列に配置するために、トリガーが必要になることです。

Oracle表へのODBCリンクがJetに存在する場合、挿入の実行時にOracleトリガーによってキー値が変更または初期化されると、Jetは新しいキー値を取得するための一連の問合せを実行し、挿入された行がダイナセットのメンバーになります。Jetが挿入された行を再選択する際に問題が発生した場合、行は#DELETEDと表示されます。これは、更新操作には適用されません。

次の例では、OracleでAUTONUMBERデータ型のエミュレーションを行う方法を示します。

Oracle表

CREATE TABLE                      OTBLAUTONUMBERTEST( PK          NUMBER          (10,0), NAME            VARCHAR2                (50), CONSTRAINT                 PK_OTBLAUTONUMBERTEST PRIMARY KEY (PK))

Oracleの順序

CREATE SEQUENCE   TEST     INCREMENT BY 1    START WITH 1000 

トリガー・コード

Create Trigger TRG_CNT_OTBLAUTONUMBERTEST
Before INSERT OR UPDATE on OTBLAUTONUMBERTEST
FOR EACH ROW
DECLARE
   iAUTONUMBER SCOTT.OTBLAUTONUMBERTEST.PRIMARYKEY%TYPE;
   cannot_change_AUTONUMBER EXCEPTION;

BEGIN
   IF INSERTING THEN
      SELECT TEST.NEXTVAL into iAUTONUMBER FROM dual;
      :new.PRIMARYKEY := iAUTONUMBER;
   END IF; -- End of Inserting Code

   IF UPDATING THEN 
      -- Do not allow the PK to be changed.
      IF NOT(:new.PRIMARYKEY = :old.PRIMARYKEY) THEN
         RAISE cannot_change_AUTONUMBER;
      END IF;

   END IF; -- End of Updating Code

EXCEPTION
   WHEN cannot_change_AUTONUMBER THEN
      raise_application_error(-20000,'Cannot Change AUTONUMBER Value');
END;

このトリガーは、表に対するINSERT操作とUPDATE操作の両方をトラップすることによって、AUTONUMBERデータ型のエミュレーションを行います。INSERT操作が実行されると、トリガーは、PRIMARYKEY列について、一連のTESTの次の値を取得します。UPDATE操作が実行されると、トリガーは、AUTONUMBERの更新が試行されているかどうかを確認します。更新中の場合は、例外が発生し、エラーがMicrosoft Accessに戻されます。

UPDATEでAUTONUMBERを暗黙に保護することはお薦めしません。次の例では、Jetはダイナセットを正常に管理することができず、予期しない結果が発生します。

IF UPDATING THEN 
   -- Do not allow the PK to be changed.

   IF NOT(:new.PRIMARYKEY = :old.PRIMARYKEY) THEN
      :new.PRIMARYKEY := :old.PRIMARYKEY);
   END IF;

END IF; -- End of Updating Code

トリガーにコードを追加してAUTONUMBERフィールドのエミュレーションを制限することによって、Microsoft Accessは行挿入時にAUTONUMBERに対して値を渡すことができます。次のコードでは、渡された値がNULLの場合にのみ、新しいAUTONUMBER値が生成されます。

IF INSERTING THEN
   IF (:new.PRIMARYKEY IS NULL) THEN
      SELECT test.NEXTVAL into iAUTONUMBER FROM dual;
      :new.PRIMARYKEY := iAUTONUMBER;
   END IF:
END IF; -- End of Inserting Code

B.2 名前マッピング問合せ

Microsoft Accessで名前マッピング問合せを構築するには、QBEまたはSQLウィンドウのいずれかを使用して問合せを定義します。次の例では、元のMicrosoft Access表SeqDateTableが、O_SEQDATETABLEという名前でOracleにエクスポートされます。エクスポート後に、この表はR_SeqDateTableという名前でJetに添付されます。

次の問合せをSeqDateTableという名前で保存すると、元の表が置き換えられ、Oracleへのマッピングが完了します。この問合せでは、Microsoft Accessで使用できるように、列名PRIMARYKEY、O_SEQUENCEおよびFIRSTDATEが、PrimaryKey、SequenceおよびFirstDateにマップされます。

SELECT             NameMapper.PRIMARYKEY AS PrimaryKey, NameMapper.O_SEQUENCE AS Sequence, NameMapper.FIRSTDATE AS FirstDate FROM R_SEQDATETABLE;

B.3 デフォルト値

Oracleは、宣言的なデフォルト値をサポートしています。ただし、Microsoft AccessからOracleへアプリケーションを移行すると、デフォルト値をサポートするために挿入トリガーが必要な場合があります。すべてのデフォルト処理をトリガーで行うことをお薦めします。これによって、コードが集約され、メンテナンスが簡単になります。次のコード例では、トリガーでのデフォルト値のサポート方法を示します。

CREATE OR REPLACE TRIGGER BIU_M2
 BEFORE INSERT OR UPDATE
 ON M2
 FOR EACH ROW

   BEGIN
      IF INSERTING THEN
      /* Manage Default Values if a new value is NULL */
         IF :new.Address IS NULL THEN
            :new.Address := 'Default';
         END IF;
      END IF; -- End of Inserting Code
   END; -- Trigger BI_M2

B.4 列および表の検証

Oracleでは、表の制約および列の制約を強制するために使用できるCHECK文がサポートされています。ただし、Microsoft AccessからOracleにアプリケーションを移行する際に、検証をサポートする挿入トリガーが必要な場合があります。次のコード例では、トリガーでの検証のサポート方法を示します。<Access Validation Code>は、Microsoft Accessアプリケーションの検証コードを挿入できる場所を示します。

CREATE OR REPLACE TRIGGER BIU_M2
 BEFORE INSERT OR UPDATE
 ON M2
 FOR EACH ROW

 BEGIN
      -- Validation Code
      IF NOT ( <Access Validation Code > ) THEN
         raise_application_error (-20000, '<Access Error Message>');
      END IF;
END; -- Trigger BI_M2

B.5 CASCADE UPDATEトリガー・コード

Oracleでは、CASCADE UPDATE参照整合性制約を直接サポートしていません。CASCADE UPDATEがサポートされている場合、主キーが変更されると、リンクされた表の、関連付けられているすべての外部キーも変更されます。CASCADE UPDATEは、アプリケーションの一般的な設計機能ではありません。主キーは、多くの場合、アプリケーションの存続期間中は不変である必要があります。

次のコード例は、2つの表に基づいています。

create table M1 ( f1 number, f2 number, f3 number )
create table M2 (f1 number, f2 number, f3 number )
alter table M1 add primary key (f1)
alter table M2 add primary key (f1)

この定義では、1対多のカーディナリティがサポートされます。1対1カーディナリティのサポートを追加するには、次の定義を追加します。

alter table M1 add constraint uq_M1_001 unique (f2, f3)
alter table M2 add constraint uq_M2_001 unique (f2, f3)

次のコードでは、2つの表M1およびM2に対するCASCADE UPDATEコードが実装されます。この例では、主キー/外部キーのリレーションシップにある2つの列が使用されています。このように複雑なリレーションシップが使用されることは多くありませんが、ここでは、適切なコードを示すために使用されています。

2つの表で、参照整合性の宣言的なサポートとプロシージャによるサポートを共存させることはできません。2つの表の間でCASCADE UPDATEをサポートするには、表の間のすべての宣言的な主キー/外部キーのリレーションシップおよび参照整合性を削除して、かわりに手続き型のコードでサポートする必要があります。次のコード例では、この方法の概要を示します。

CREATE OR REPLACE PACKAGE P_M1 AS
    fire_trigger boolean := TRUE;
 END P_M1;

 CREATE OR REPLACE PACKAGE P_M2 AS
    fire_trigger boolean := TRUE;
 END P_M2;

 CREATE OR REPLACE PACKAGE UQ_M1_M2 AS

 PROCEDURE cascade_update (
           o_F2    IN  number,
           o_F3    IN  number,
           n_F2    IN  number,
           n_F3    IN  number,
           bResult OUT boolean );

        PROCEDURE cascade_delete (
           F2      IN  number,
           F3      IN  number,
           bResult OUT boolean );

        FUNCTION pk_exists (
           F2      IN  number,
           F3      IN  number) RETURN boolean;

        FUNCTION fk_exists (
           F2      IN  number,
           F3      IN  number) RETURN boolean;

 END  UQ_M1_M2;

CREATE OR REPLACE PACKAGE BODY UQ_M1_M2 AS

   /* Procedure cascade_update is called when field(s) */
   /*   F2 or                                          */
   /*   F3                                             */
   /*   are changed in table M1.                       */
   /*   The changes are cascaded in table M2           */

   PROCEDURE cascade_update (
             o_F2    IN  number,
             o_F3    IN  number,
             n_F2    IN  number,
             n_F3    IN  number,
             bResult OUT boolean ) IS

   CURSOR d_cur (n1 number, n2 number) IS
      SELECT * FROM m2 
      WHERE f2 = n1 AND f3 = n2
      FOR UPDATE of f2, f3;

   BEGIN

      FOR d_cur_rec IN d_cur ( o_F2, o_F3 )
      LOOP
         UPDATE M2 SET f2 = n_F2, f3 = n_F3
            WHERE CURRENT OF d_cur;
      END LOOP; -- Detail Record Loop

      bResult := true;

   END cascade_update;

   /* Procedure cascade_delete is called when a record */
   /*   in M1 is being deleted and associated          */
   /*   child records in M2 must also be deleted.      */

   PROCEDURE cascade_delete (
           F2      IN  number,
           F3      IN  number,
           bResult OUT boolean ) IS

   CURSOR d_cur (n1 number, n2 number) IS
      SELECT * FROM m2 
      WHERE f2 = n1 AND f3 = n2
      FOR UPDATE;

   BEGIN

      FOR d_cur_rec IN d_cur ( F2, F3 )
      LOOP
         DELETE FROM M2
            WHERE CURRENT OF d_cur;
      END LOOP; -- Detail Record Loop

      bResult := true;

   END cascade_delete;

   /* Procedure pk_exists is called to determine is a given
      primary key exists in table M1                           */

   FUNCTION pk_exists (
        F2      IN  number,
        F3      IN  number) RETURN boolean IS

   l_F2        number;
   l_F3        number;   
   bResult     boolean;

   CURSOR p_cur (n1 number, n2 number) IS
      SELECT F2, F3 FROM m1 
      WHERE f2 = n1 AND f3 = n2;

   BEGIN
      OPEN p_cur( F2, F3 );
      FETCH p_cur INTO l_F2, l_F3;
      IF p_cur%NOTFOUND THEN
         bResult := false;
      ELSE
         bResult := true;
      END IF;
      
      CLOSE p_cur;

      RETURN( bResult );
      
   END pk_exists;

   /* Procedure pk_exists is called to determine is a given
      primary key exists in table M1                           */

   FUNCTION fk_exists (
        F2      IN  number,
        F3      IN  number) RETURN boolean IS

   l_F2         number;
   l_F3         number;
   bResult      boolean;

   CURSOR d_cur (n1 number, n2 number) IS
      SELECT F2, F3 FROM m2 
      WHERE f2 = n1 AND f3 = n2;

   BEGIN
      OPEN d_cur( F2, F3 );
      FETCH d_cur INTO l_F2, l_F3;
      IF d_cur%NOTFOUND THEN
         bResult := false;
      ELSE
         bResult := true;
      END IF;
      
      CLOSE d_cur;

      RETURN( bResult );

   END fk_exists;

 END UQ_M1_M2;


 CREATE OR REPLACE TRIGGER AUD_M1
 AFTER UPDATE OR DELETE
 ON M1
 FOR EACH ROW

   DECLARE
   bResult_OK      BOOLEAN;
   bCascadeDeletes BOOLEAN  := TRUE;
     
   BEGIN

      IF UPDATING THEN
         IF (:old.F2 <> :new.F2) OR (:old.F3 <> :new.F3) THEN
            P_M2.fire_trigger := FALSE;
            UQ_M1_M2.cascade_update( :old.F2, :old.F3, :new.F2, :new.F3, 
                                      bResult_OK );
            P_M2.fire_trigger := TRUE;
         END IF;
      END IF; -- End of Updating Code

      IF DELETING THEN
         IF bCascadeDeletes THEN
            UQ_M1_M2.cascade_delete( :old.F2, :old.F3, bResult_OK );
         ELSE
            IF UQ_M1_M2.fk_exists( :old.F2, :old.F3 ) THEN
               raise_application_error( -20000, 'Rows exist in child table');
            END IF;
         END IF;
      END IF; -- End of Deleting Code

   END; -- Trigger AUD_M1


 CREATE OR REPLACE TRIGGER AIU_M2
 AFTER INSERT OR UPDATE
 ON M2
 FOR EACH ROW

   DECLARE
   bResult_OK    BOOLEAN;
     
   BEGIN

      IF INSERTING THEN
         IF NOT( UQ_M1_M2.pk_exists( :new.F2, :new.F3 ) ) THEN
            raise_application_error (-20000, 'No corresponding row in parent 
                       table');
         END IF;
      END IF; -- End of Inserting Code

      IF ( UPDATING AND P_M2.fire_trigger ) THEN
         IF NOT( UQ_M1_M2.pk_exists( :new.F2, :new.F3 ) ) THEN
            raise_application_error (-20000, 'No corresponding row in parent table');
         END IF;
      END IF; -- End of Updating Code

   END; -- Trigger AUD_M2