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

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


##ダイナミック単一SQLクエリ
ではさっそく、簡単な例で見てみます。

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

```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という名のカラムがあれば、カラム値の大きい方から３行を出力、カラムがなければ ``<N/A>`` をそれぞれ出力する」SQLを作ってみます。

```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全体を切り替えちゃえば、もうなんでもあり :-)

```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> ...
         );
```

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

以上です。