e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第51回 「PL/SQLで自分のツールを作る」

2013.03.18

こんにちは。インストラクターの蓑島です。

今回はコラムとして、PL/SQLの機能を使って作業の効率化を図る事例を話題にします。
題して、「PL/SQLで自分のツールを作る!」です。気軽に読んでください。

例えばデータベースを使ったプログラムを書く場合、必ず表のキー(主キー、外部キー)について事前に把握する必要があります。
手元に表定義のドキュメントがない場合、あるいは、なにか便利なツールがない場合、キーを調べるには、自分でデータディクショナリを問い合わせる必要がありますが、これはなかなか面倒です。

例えば、USER_CONSTRAINTSビューで、表に定義されている制約の名前とタイプがわかりますが、しかし、列の情報はわかりません。
そこで、USER_CONS_COLUMNSビューを問い合わすことにより、制約列の名前がわかります。
また、外部キーの参照している親表の名前や親キーを調べるには、外部キー制約の名前をもとに、USER_CONSTRAINTSから、参照される親表の制約名(R_CONSTRAINT_NAME)を調べ、それを元に、もう一度USER_CONSTRAINTSビューとUSER_CONS_COLUMNSビューを問い合わす必要があります。なかなか面倒ですね。

もちろん、そういった用件を満たす複雑な問い合わせを書いてもいいのですが、PL/SQLプログラムを使えば自由なメッセージで結果の表示ができますから、より使いやすいと思います。

以下はそのようなPL/SQLプログラムです。
表名を指定すれば、その主キーとそれを参照する子表と外部キーを教えてくれます。
コピペすればすぐに実行できる内容です。

<<<ここから>>>
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
   V_TABLE_NAME  VARCHAR2(100);  -- 表名
   V_PK_NAME   VARCHAR2(100);  -- 主キー制約の名前
   V_COLUMNS   VARCHAR2(3000); -- 列名の取得(列1,列2,・・)
BEGIN
   -- 表名の取得(キーボードから入力)
   V_TABLE_NAME := UPPER('&TABLE_NAME'); 
   -- 主キー制約の名前の取得
   SELECT CONSTRAINT_NAME INTO V_PK_NAME
   FROM   USER_CONSTRAINTS 
   WHERE  TABLE_NAME = V_TABLE_NAME
          AND CONSTRAINT_TYPE = 'P';
   -- 取得した主キーの列名(複合例)の取得
   FOR REC  IN (SELECT COLUMN_NAME  
                FROM  USER_CONS_COLUMNS 
                WHERE CONSTRAINT_NAME = V_PK_NAME
                ORDER BY POSITION ) LOOP
      -- "列名," を後ろに追記する
      V_COLUMNS := V_COLUMNS || REC.COLUMN_NAME || ',';   
   END LOOP;
   -- 最後の余分な','をとっている
   V_COLUMNS := SUBSTR(V_COLUMNS,1,LENGTH(V_COLUMNS)-1);  
     --  "列名,列名"の前後をかっこ()で囲む
   V_COLUMNS := '(' || V_COLUMNS || ')';
   DBMS_OUTPUT.PUT_LINE('表名:' || V_TABLE_NAME 
                        || '  主キー :' || V_COLUMNS);

  -- この表を参照する子表の外部キー制約名を取得し、
  -- 子表の名前と外部キー列を表示する
   FOR REC1 IN ( SELECT CONSTRAINT_NAME, TABLE_NAME
                 FROM  USER_CONSTRAINTS
                 WHERE R_CONSTRAINT_NAME = V_PK_NAME 
                   AND CONSTRAINT_TYPE = 'R')   LOOP
    -- さらに外部キー制約名から、列名(複合列)を変数に取得する
     V_COLUMNS := NULL;
     FOR REC2 IN ( SELECT COLUMN_NAME FROM  USER_CONS_COLUMNS
                   WHERE  CONSTRAINT_NAME = REC1.CONSTRAINT_NAME
                   ORDER BY POSITION)  LOOP
         V_COLUMNS := V_COLUMNS ||  REC2.COLUMN_NAME || ',';
     END LOOP;
     V_COLUMNS := SUBSTR(V_COLUMNS,1,LENGTH(V_COLUMNS)-1);
     V_COLUMNS := '(' || V_COLUMNS || ')';
     -- 子表の情報を画面表示
     DBMS_OUTPUT.PUT_LINE('子表:' || REC1.TABLE_NAME 
                          || '  外部キー :' || V_COLUMNS);
  END LOOP;
END;
/
<<<ここまで>>>

以上のスクリプトをファイルに貼り付けて、SQL*Plusや、SQL*Developerなどから実行できます。

以下は実行例です。

SQL> @test.sql
table_nameに値を入力してください: OYA
表名:OYA  主キー :(A,B,C)
子表:KO1  外部キー :(A,B,C)
子表:KO2  外部キー :(COLA,COLB,COLC)

PL/SQLプロシージャが正常に完了しました。

ここでは、「OYA」という親表があり、その主キー(複合主キー)は、(A列、B列、C列)の組み合わせです。
そして、この複合主キー列を参照する二つの子表 「KO1表」と「KO2表」があり、それぞれの外部キー(複合外部キー)列が、(A列、B列、C列)と、(COLA列,COLB列,COLC列)であることがわかります。

このように、いままで学習してきたPL/SQLを使って、自分なりのツールを作っておけば、なにかと便利です。
ぜひ、工夫して自分のツールを作り、仕事に活用してください。

(※注意)
上記のスクリプトは、一意キー(UNIQUEキー)を参照する外部キーについては考慮していません。
外部キーが参照する相手先は通常は主キーですが、1行に特定という意味で、一意キーでも可能ではあります。
もし必要があれば、14行目を以下のように修正すれば、一意キーを参照する外部キーを対象にすることができます。

(修正前) AND CONSTRAINT_TYPE = 'P';
(修正後) AND CONSTRAINT_TYPE = 'U';

それでは今回はここまでとします。
次回は、「定義者権限と実行者権限」について解説します。
「実行者権限のプロシージャ」というものがあり、状況によっては役に立つ面白い機能です。ご期待ください。

先頭へ戻る