テストテーブルや変更履歴を自動保存するトリガなんかを色々と作って使っている。
面倒なので自宅PCのOracle DBではデフォルトのサンプルを使ってなんやかんやしているわけだが
サンプルのテーブルは僕が作ったわけではないのでテーブル構造がわからない。
型とNot Null制約の有無くらいならdescribeで分かるけどね・・・
主キーが知りたいんだよ！っていう場合が多々ある。
主キーを判別する方法はいくつかあるようだが
user_cons_columnsテーブルを使ってユーザが指定したテーブルの主キーを列挙するPL/SQLプログラムを作ってみた。
declare
-- レコード型コレクション(結合配列)変数
type column_data is table of user_cons_columns%rowtype index by pls_integer;
col column_data;
-- カーソル変数
type refcs is ref cursor;
cs refcs;
-- ループカウンタ
lc number default 1;
begin
-- カーソルにSQL文を設定してオープン
open cs for
'select * from user_cons_columns
where constraint_name = (
select constraint_name from user_constraints
where table_name = :table_name_input
and constraint_type = ''P''
)' using upper('&table_name_input'); -- ユーザ入力値を大文字に変換してバインド
-- Fetchループ
loop
fetch cs into col(lc);
exit when cs%notfound;
-- 所有者と対象テーブル名は一度だけ出力
if 1 = cs%rowcount then
dbms_output.put_line('所有者：'||col(lc).owner||' / '||
'テーブル：'||col(lc).table_name);
end if;
dbms_output.put_line('主キー：'||col(lc).column_name);
lc := lc + 1;
end loop;
-- 結果セットが0行だった場合はエラーメッセージを表示する
if 0 = cs%rowcount then -- ！カーソルをcloseした後はカーソル属性を使用できない！
raise_application_error(
-20999,
'指定した表またはビューが存在しないか、データが空です。'
);
else
dbms_output.put_line(CHR(13)||CHR(10)||'正常に処理が終了しました。');
end if;
close cs;
exception
-- テーブルに主キーが無い場合はエラーメッセージを出力
when no_data_found then
dbms_output.put_line('主キーがありません。');
-- その他のエラーが発生した場合は対応するエラーメッセージを出力
when others then
dbms_output.put_line(sqlerrm);
end;
/
実行すると入力要求が出る
主キーを知りたいテーブルの名前を入力する。
たとえばサンプルのEMP表だと結果はこうなる。
EMPNO列が主キーだということがわかる。
複合主キーのテーブルを作ってみた。
このDOUBLE_PRIME表の主キーは
COMP_ID列とEMP_ID列。複合主キーにも対応していることがわかる。
大したこと無いプログラムだけど、作りたい物が作れた時の達成感は味わえた。
データがinsertされてない表には使えないのはご愛嬌！
(もう疲れ果てました)
