IDの自動生成と削除
解決以前お伺いしたIDに関する追加質問です。
オラクルを使っていて、IDを自動生成するいい方法を探しています。今、作成している方法は、
1: query1をつかって、sql.add('select max(ID) from table')で現在登録されている最大のID番号を持ってくる。ちなみに、IDの型はintegerです。
2:別query2をつかって、sql.add('insert .. into (valueID)');
prepare;
params[0].asinteger := ここにquery1で取得したmaxIDの値+1をいれたいとおもっているのですが、呼び出し方がわからない状態です。
また、登録前に、現在すでに登録している最小IDを探し出し、０よりも大きく最小ID値よりも小さい数字が存在した時は、そのIDを呼び出して、insert
したいとおもっています。
すみませんが、ご教授お願いいたします。
前の質問で、Mr.XRAYさんが、
>OracleにはCREATE SEQUENCEというのがあります．
と書いていますが、調べましたか?
Oracle側で生成してくれますよ。
INSERT ... INTO TABLE VALUE(ID.NEXTVAL,....)とすると、IDの値がどんどん大きくなってしまい、最終的にはこれ以上生成できないという状態になると考えられます。仮に、IDの値が999までしか入らないとして、すでに999まで使用している状態だが、20〜30番の間はデータを削除したので使用できる場合、使えるIDを探して20〜30のIDでデータを登録する方法を探しています（30番まで達したら、すでに存在している31へ行くのではなく、もうデータが登録できないと判断する）。
★追加★CREATE SEQUENCEについての追加質問があります。NEXTVALを使用したい場合には、SQLでCREATE SEQUENCEを実行しないといけないと思うのですが、どのテーブルにCREATE SEQUENCEを使うのか指定することができません。
いろいろと説明を読みましたが、その部分に疑問をがあります。これは、ただテーブルの指定せずSQL上で実行すればいいのですか
>20〜30番の間はデータを削除したので使用できる場合、使えるIDを探して
は、
SELECT ID FROM テーブル名 ORDER BY ID
で、IDの値を１つずつ確認し、欠けている（削除している）ID
を探していくのは、いかがですか？
下のQueryで空きIDの内の最小のもの(gapID)が得られるはずです。
(使ったことあります)
select min(a.ID + 1) as gapID
from (select ID from TableA) a
where not exists
(select * from TableA b where b.ID = a.ID + 1);
gapIDがMaxID+1なら空きがないことになります。
しかし、IDに999などの上限を設ける理由があるのですか？
IDが(long)Integerなら21億まで使えるのに
すみません、前発言のSQLは、
IDが１から振られていて、1,2,3とIDが削除されていた場合、
1を抽出できません。
(ゴメン m(__)m)
DB Engineにもよりますが、IF とか WHEN などが使えれば
MIN(ID)が1かどうかの判定と前発言のSQLを組み合わせた
１本のSQLでいけるのではないでしょうか
OracleのCREATE SEQUENCEは、初期値・最小値・最大値を設定できます。最大値を超した場合は、初期値にリセットされます。
私の環境だと create sequeceが使えないみたいです。データベースの管理者に言われました。この場合は、max(ID) + 1をselect文で使わないといけないと思うのですが、
変数にi := qry.sql.add('select max(ID) from table);としても、max値をとってきません（１をとってきます）。
select min(a.ID + 1) as gapID
from (select ID from TableA) a
where not exists
(select * from TableA b where b.ID = a.ID + 1);も試したのですが、同様の結果になりました。
>変数にi := qry.sql.add('select max(ID) from table);としても、max値をとってきません（１をとってきます）。
Add メソッドの戻り値ではなく、
そのSQL文を実行してみたらどうなりますか？
SQL文だと、max値をとってくることができました。
欠番IDの再利用にこだわるのであれば、ID取得関数を作っては？
下記でどうですか？
//IDを取得する関数
function GetNextID: Integer;
begin
with query1 do begin
SQL.clear;
SQL.Add('select min(ID) as A from TableA');
Open;
//min(ID)が1の時は欠番を探す(欠番がなければ(MaxID+1)を)
if FieldByName('A').AsInteger = 1 then begin
close;
SQL.clear;
SQL.Add('select min(a.ID + 1) as NextID');
SQL.Add(' from (select ID from TableA) a');
SQL.Add(' where not exists');
SQL.Add(' (select * from TableA b where b.ID = a.ID + 1);');
Result := FieldByName('NextID').AsInteger;
close;
end else begin //Min(ID)>1のときは1〜(MinID-1)が欠番となっているので1を再利用
close;
Result := 1;
end;
end;
end;
//メイン側
var
ID: Integer;
begin
ID := GetNextId;
if ID > 999 then begin
ShowMessage('満杯で〜す！');
Exit;
end;
with query2 do begin
SQL.clear;
SQL.Add('Insert Into TableA (ID, Data1, Data2, ...)'
SQL.Add(' Values (:ID, :Data1, :Data2, ...');
:
:
end;
end;
訂正(追加)
> SQL.Add(' (select * from TableA b where b.ID = a.ID + 1);');
Opne; //<---抜けてました
> Result := FieldByName('NextID').AsInteger;
訂正の訂正が必要ですね
追加行の"Open"のつづりがまちがってます。(トホホ…)
おまでです、
SQLでCase文を使えば1本のSQLで行けそうです。
下記SQLはFireBird 2.1で通ります。Oracleでも多分OKと思います。
function GetNextID: Integer;
begin
with query1 do begin
SQL.clear;
SQL.Add('select case when min(ID) = 1 then');
SQL.Add(' (select min(a.ID + 1) from (select ID from TableA) a');
SQL.Add(' where not exists (select * from TableA b where b.ID = a.ID + 1))';
SQL.Add(' else 1 end as NEXTID from TableA;');
SQL.Open;
Result := FieldByName('NEXTID').AsInteger;
Close;
end;
end;
簡単なサンプルテーブルを作って
ID=1を欠番にした時 １が得られる
ID=1以外を欠番にした時 欠番の内の最小値が得られる
IDに欠番がない時 最大値+１が得られる
の３通りで確認してみてください。
ありがとうございます。ちなみに、プライマリキーがかかっているIDすべてを一度リセットして、DelphiのSQL文を使ってIDを再度割り振る方法をご存知ですか？
"一度リセットして"の意図が、
3,4,5, 10,11,12, 18,19,20 のように歯抜け状態のIDを元に
1,2,3, 4, 5, 6, 7, 8, 9 のように同じIDフィールドに対してSQL一発で
連番にしたいということであれば、無理だと思います。
もし、出来るのならそのDB EngineとSQL文を私も知りたいです。
新フィールド"ID2"を追加する。
歯抜け"ID"の順に"ID2"にSQLで連番を振る <== これはSQLで可能です
"ID"を削除する
"ID2"を"ID"に名称変更し"Unique"設定をする
と言う手順を踏むのが無難な方法ではないでしょうか。
いろいろな情報どうもありがとうございました。
| ツイート |  |