XML
SQL
oracle
XMLTABLE

ダイナミックSQLを生成し実行結果を得る単一SQL ステートメントの作り方(Oracle)

表題の通り、「単一SQLステートメントのみ」でダイナミックSQLを作成し、かつそのSQLの実行結果を出力する方法です。そこはかとなく意味不明感漂ってますけど、一応可能です。といってもただのSQLではどうあがいても無理ですので、XML拡張の助けを借ります。実用性はたぶんありませんが、なにかの折に使えるかもしれません(^^)。

ではさっそく、簡単な例で見てみます。

以下は、「自分のスキーマ内にあるテーブルをサーチし、それぞれのテーブルの行数をカウントする」単一SQLステートメントです。

SELECT table_name, cnt count
FROM   user_tables,
       XMLTABLE (
           '/ROWSET/ROW' 
           PASSING DBMS_XMLGEN.getxmltype (
               'SELECT COUNT(*) CNT FROM ' || table_name) 
           COLUMNS cnt NUMBER);


TABLE_NAME           COUNT
--------------- ----------
TEST_A                  10
TEST_B                  10
TEST_C              100000
...
...

上記のDBMS_XMLGEN.getxmltypeは、文字列引数のSQLステートメントを実行し結果をXMLTYPE型のXMLデータで返すXML関数、そしてXMLTABLE()は、XMLTYPE型のXMLデータをテーブルに変換するXML拡張表現です。XMLTABLE()は、コレクション型に対するTABLE()と似ていますね。TABLE()と同様にラテラル(クロスアプライ)を受け付けます。

ここでは、XMLTABLE()のラテラルを利用して、USER_TABLESのテーブル名をDBMS_XMLGEN.getxmltypeに流し込んでダイナミックSQLを作成しています。SQLの実行でそれぞれのテーブルに対するカウントがXMLTYPEのXMLデータで返されるので、そのままXMLTABLE()でテーブルに置換しています。これだけです。

では、多少複雑にしてみましょう。

TEST_X%にマッチするテーブルを探し、テーブル内にIDという名のカラムがあれば、カラム値の大きい方から3行を出力、カラムがなければ <N/A> をそれぞれ出力する」SQLを作ってみます。

SELECT table_name, rank, id top3_ids
FROM   (SELECT t.table_name, c.column_name
        FROM   user_tables t, user_tab_columns c
        WHERE  t.table_name LIKE 'TEST_X%' AND t.table_name = c.table_name(+) AND c.column_name(+) = 'ID'),
       XMLTABLE (
           '/ROWSET/ROW'
           PASSING DBMS_XMLGEN.getxmltype (
                          'select id, rank from (select '
                       || NVL (column_name, '''<N/A>''')
                       || ' id, row_number() over (order by '
                       || NVL (column_name, 'null')
                       || ' desc) rank from '
                       || table_name
                       || ') where rank <= '
                       || NVL2 (column_name, 3, 1))
           COLUMNS id VARCHAR2 (30), rank NUMBER);


TABLE_NAME            RANK TOP3_IDS
--------------- ---------- ------------------------------
TEST_X1                  1 20
TEST_X1                  2 19
TEST_X1                  3 18
TEST_XX2                 1 <N/A>

インラインビューの結果行に応じで自由にSQLを作ることができます。それぞれのダイナミックSQLで返すカラム数や行数も自由です。ただXMLTABLE()で受けるカラムの数とタイプは実行するSQL毎にダイナミックに変更することはできません。とはいえ、COLUMNS句で指定したカラムが存在しなくてもエラーにならずNULLがセットされるだけなので特に問題にはならないでしょう。

まぁ突き詰めていけば、全然関連のないダイナミックSQLでもいいわけです。こんな感じでID=1のときは最小ユーザ名をそれ以外は今年の年を返す、とかね。SQL全体を切り替えちゃえば、もうなんでもあり :-)

SELECT * 
FROM   (SELECT 1 id FROM dual UNION SELECT 2 FROM dual) t,
       XMLTABLE(
           '/ROWSET/ROW' 
           PASSING dbms_xmlgen.getxmltype(
              CASE WHEN id = 1 
                   THEN 'select min(username) username from dba_users'
                   ELSE 'select to_char(sysdate, ''YYYY'') year from dual' 
              END)
          COLUMNS username VARCHAR(30), year NUMBER);

        ID USERNAME                             YEAR
---------- ------------------------------ ----------
         1 ANONYMOUS
         2                                      2018

ちょっと脱線しましたが、まとめると以下の感じですかね。意外と簡単ですね。

あとCOLUMNS句では、DATE型はYYYY-MM-DDフォーマットしか受け付けない様なので、時分秒が必要な場合はTIMESTAMP型(YYYY-MM-DDTHH:MI:SS)かまたは文字列型で受けて最後に型変換するしかないようです。

SELECT *
FROM (
      <変動する値を得るインラインビュー>
     ),
     XMLTABLE (
           '/ROWSET/ROW'
           PASSING DBMS_XMLGEN.getxmltype (
               <変動する値を使用したダイナミックSQLの文字列>
               )
         COLUMNS <カラム名1> <カラムタイプ1>,
                 <カラム名2> <カラムタイプ2> ...
         );

なんというかオラクルは、ほんっっとに多機能でおもしろいですよね。ま、その分バグも多いですけど。

以上です。