oracle8に接続、Access97での質問。自動でIDを取得するには…

質問

仮にoracleのテーブルのデータ（02EE9001、02EE9002）があるとしAccessのフォーム上でIDコードを入力しなかった場合自動でIDコードを割り当てられるようにしたいのです。たとえば今Accessのフォーム上でIDコードを入力しないで登録した場合、02EE9003が自動で割り当てられるようにしたいのですがどうすればいいですか？ちなみ16進数でIDコードを割り当てています。しかも、02EEは固定です。仮にコードを割り当てられなくなったときは「空きIDがありません。」というようなメッセージも出したいのです。どうかよろしくお願いします。かってなことばかりですみません。

回答No.3

muyoshid · ベストアンサー

こんにちわ。

トリガは、DDL (Insert/Delete/Update) 文実行時にDDL の延長上で
動くPL/SQL の事です。
詳細は、PL/SQL のマニュアルを参照してみてください。
ちょっとプログラムを書いてみましたので、参考にしてみて下さい。
※ 但し、このプログラムには以下の問題がありますので
　　ご注意下さい。
　　1. 採番テーブルにあるコードが全て採番済みになっている
　　　　場合を考慮していません。
　　2. テーブルから採番をしているため、複数の端末から同時に
　　　　Insert が行われた時に、同じ番号を採番してしまう可能性
　　　　があります。
--採番テーブル
Create table SeqTab (
code varchar2(8) not null,
flag varchar2(1) not null
);
Create index IXseq on Seqtab(flag);

--実際にデータを登録するテーブル
Create table test (
id number not null,
code varchar2(8) null
);

-- 採番テーブルを初期化するプロシージャ
declare
vcode varchar2(8);
begin
FOR vloop in 9000..9100 LOOP
vcode := '02EE' || to_char(vloop, 'FM9999');
Insert into SeqTab(code, flag) values (vcode, '0');
END LOOP;
commit;
end;
/

-- Insert 文実行時のトリガ
Create or Replace trigger Ttest
before insert on test
for each row
declare
vcode varchar2(8);
begin
IF :new.code IS NOT NULL THEN
-- code が指定されていれば、採番テーブルの該当番号を
-- 採番済みにする
Update SeqTab set flag = '1'
where code = :new.code;
-- 採番テーブルに無いcode が指定されたら、採番テーブルに
-- 登録する
IF SQL%NOTFOUND THEN
Insert into SeqTab (code, flag) values (:new.code, '1');
END IF;
ELSE
-- 採番テーブルから未採番のコードを取り出す
Select min(code) into vcode from SeqTab
where flag = '0';
-- 採番テーブルの該当コードを採番済みにする
Update SeqTab set flag = '1' where code = vcode;
-- 採取したコードをセットする
:new.code := vcode;
END IF;
end;
/

採番テーブルを初期化した状態で、TEST 表にデータをInsert すると
定義したトリガが動きます。

SQL> insert into test (id, code) values (1, '02EE9001');
SQL> insert into test (id, code) values (2, '02EE9999');
SQL> commit;

SQL> select * from test;
ID CODE
---------- --------
1 02EE9001
2 02EE9999

この状態で採番テーブルの状態を確認すると、
SQL> select * from Seqtab where code in (select code from test);
CODE F
-------- -
02EE9001 1
02EE9999 1
のようになっています。

ここで、code=null としてデータをInsert すると、採番テーブルから
未採番 (flag='0') のデータを検索して採番します。
SQL> insert into test (id, code) values (3, null);
SQL> insert into test (id, code) values (4, null);
SQL> commit;
SQL> select * from test;

ID CODE
---------- --------
1 02EE9001
2 02EE9999
3 02EE9000
4 02EE9002

SQL> select * from Seqtab where code in (select code from test);
CODE F
-------- -
02EE9000 1
02EE9001 1
02EE9002 1
02EE9999 1

補足

まことにありがとうございます。しかしながら、まだ新人のためAccessしか使わせてもらっていません。なのでPL/SQLは使えないし、触らせてもらえません。なのでこの処理をAccess側でやらなければなりません。よろしくお願いします。

回答No.4

osamuy

＞ なのでPL/SQLは使えないし、触らせてもらえません。
使えないのは勉強すれば良いだけですが、触れさせてもらえません、ってのは将来の事を考えると、ちょっともったいない気がします。
頼み込んで、多少実験できるアカウントを作ってもらっては。
おそらくここで回答しているほとんどの人が、sqlplusを叩いていく事で、ORACLEやSQLをマスターしていったと思われるので。

ともあれ、Accessでやるなら、VBAでIDを生成、ADOかDAOでテーブルに挿入、するとか。
同時アクセス／変更とか、考えなくて済むなら、結構お手軽にできます
（ただし、仕事として請け負うレベルのものなら、考えなくて済む場合ってのはほとんどないでしょうが）。

詳しくは、職場の先輩にでもお尋ね下さい。

回答No.2

cse_ri2

No.1の方の言うとおりです。

AccessからOracleのテーブルを参照していますので、
参照するテーブルの機能はAccessではなく、Oracle
の機能が適用されます。

項目に自動でIDを振る機能はAccessのテーブル固有の
機能なので、それをOracleで実現しようとしたら専用
のプログラムを開発しなくてはいけません。
それが、No.1の方の言う「トリガー」です。

moshamoshaさんは初心者とのことですので、たぶん
これを自分で作成するのは非常に困難だと思われます。
あきらめるか、Oracleサーバの管理者に相談して問題
解決を依頼することをオススメします。

補足

そのためのプログラムを教えてもらいたくて投稿したのですが…あきらめるわけにはいきません！どなたかよろしくお願いします！本当に困っています。

回答No.1

osamuy

Insert時のトリガを使っては。

補足

初心者なのでゆってる意味がわかりません。トリガって何ですか？本当のド素人です。