⇧ IT人材が不足してるって言ってるにも関わらず、矛盾してる気がしますな...
Oracle Databaseで最新レコードを取得したい
Oracle DatabaseにはLimit句がないらしいので、最新レコードとか取得したい場合どうするのかな？と調べてたら、
⇧ rownumというものを使うらしい。
上記サイト様にあるように、「サブクエリ（副問合せ）」で降順にソートした後に、rownumで件数を絞る方法で実現できるっぽい。
ただ、
⇧ Oracle Database 12cからは、rownum以外も利用できるようになったらしい。
今回は、rownumを使う方法を試してみます。
で、
⇧ 上記のようなテーブルにデータを用意しておいて、試してみたけども、
SELECT * FROM ( SELECT * FROM users ORDER BY user_id DESC ) WHERE rownum <= 3;
⇧ は上手く機能しますと。
SELECT * FROM (
SELECT * FROM users ORDER BY (
SELECT column_name FROM all_cons_columns WHERE constraint_name = (
SELECT constraint_name FROM user_constraints
WHERE UPPER(table_name) = UPPER('users') AND CONSTRAINT_TYPE = 'P'
)
) DESC
) WHERE rownum <= 3;
⇧ については、どうも上手くいかない。
「サブクエリ（副問合せ）」って入れ子の内側から実行されていくもんだと思ってたので、いけるかと思ったけど、
SELECT column_name FROM all_cons_columns WHERE constraint_name = (
SELECT constraint_name FROM user_constraints
WHERE UPPER(table_name) = UPPER('users') AND CONSTRAINT_TYPE = 'P'
);
⇧ の部分は、「サブクエリ（副問合せ）」にするとORDER BYで機能しないっぽい...
まぁ、どちらにしろ、主キーが複数のカラムになっている複合主キーの場合、
SELECT column_name FROM all_cons_columns WHERE constraint_name = (
SELECT constraint_name FROM user_constraints
WHERE UPPER(table_name) = UPPER('address_detail') AND CONSTRAINT_TYPE = 'P'
);
⇧ という結果になるので、上記をORDER BYに指定したクエリを実行すると、
SELECT * FROM (
SELECT * FROM address_detail ORDER BY (
SELECT column_name FROM all_cons_columns WHERE constraint_name = (
SELECT constraint_name FROM user_constraints
WHERE UPPER(table_name) = UPPER('address_detail') AND CONSTRAINT_TYPE = 'P'
)
) DESC
) WHERE rownum <= 3;
失敗しますと。
SELECT * FROM ( SELECT * FROM address_detail ORDER BY address_id DESC, user_id DESC ) WHERE rownum <= 3;
⇧ は上手くいくっぽい、カラム毎にDESCを付ける必要あるっぽいけど。
まぁ、何が言いたいかと言うと、Oracle Databaseで最新のレコード取得するの、滅法矢鱈と面倒くさい...
Javaとかで、動的にSQLを作る方法ならSQLを複数回に分けて実行する方法で、テーブル毎の主キーを取得して、ORDER BYしてから、rownumで最新のレコード10件とか実現できると思うけど、主キーのないテーブルの場合どうするかとか考える必要もあるし、どちらにしろ面倒くさいことには変わりはないですかね...
ちなみに、
SELECT * FROM all_tab_columns
WHERE UPPER(table_name) = UPPER('address_detail');
で、取得できる情報の中のcolumn_idの値でカラムの順番が分かるので、
SELECT column_name FROM all_tab_columns
WHERE UPPER(table_name) = UPPER('address_detail')
AND column_id = 1;
最初のカラム名が取得できるっぽい。
⇧ 確かに、一番目のカラムの物理名を取得できてます。
最初のカラム名を取得する方法については、検索してもヒットしなかったところを見ると需要が無いのかもしれない...
all_cons_columnsとall_tab_columnsの違いについては、
■ALL_CONS_COLUMNS
ALL_CONS_COLUMNSは、現行のユーザーがアクセスでき、また制約に指定されている列を示します。
https://docs.oracle.com/cd/F19136_01/refrn/ALL_CONS_COLUMNS.html
■ALL_TAB_COLUMNS
ALL_TAB_COLUMNSは、現行のユーザーがアクセスできる表、ビューおよびクラスタの列を示します。
https://docs.oracle.com/cd/F19136_01/refrn/ALL_CONS_COLUMNS.html
⇧ ということらしい。
主キーもなく、INSERTやUPDATEされた日時が分かるようなカラムもない場合は、テーブルの最初のカラム名を取得してORDER BYする感じになるんかな？
テーブルの作り次第ではあると思うけど...
By the way、何で、最新のレコードを取得しようと思ったかと言うと、DBUnitさんが全レコードを取得するとレコード数が多過ぎて、Java側でOutOfMemoryErrorが起きてしまうから、苦肉の策で取得するレコード数を絞りたかったということですね...
今回の話とは関係ないけど、複雑なSQL文の生成とかもAIで自動化して欲しいよね...
毎度モヤモヤ感が半端ない...
今回はこのへんで。
