はじめに
表題の通り、「単一SQLステートメントのみ」でダイナミックSQLを作成し、かつそのSQLの実行結果を出力する方法です。そこはかとなく意味不明感漂ってますけど、一応可能です。といってもただのSQLではどうあがいても無理ですので、XML拡張の助けを借ります。実用性はたぶんありませんが、なにかの折に使えるかもしれません(^^)。
ダイナミック単一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を作ってみます。
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> ...
);
おわりに
なんというかオラクルは、ほんっっとに多機能でおもしろいですよね。ま、その分バグも多いですけど。
以上です。