Oracle PL/SQL開発 No.2-指定したテーブルの主キーを表示-

普段は社用PCのローカルにインストールしたOracle DBでプログラムを書いていて、
テストテーブルや変更履歴を自動保存するトリガなんかを色々と作って使っている。

面倒なので自宅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されてない表には使えないのはご愛嬌！
(もう疲れ果てました)