OracleにはLIMIT句がない
OracleにはMySQLやPostgreSQLのようなLIMIT句がないので、
SELECT * FROM hoge LIMIT N;
とか書くとエラーになります。
行1でエラーが発生しました。:
ORA-00933: SQLコマンドが正しく終了されていません。
ROWNUM
では、指定した件数のみ取得したい場合どうするかというと、OracleにはSELECTの結果の行番号を返す ROWNUM という擬似列があるので、それを使います。
- API: ROWNUM疑似列
例えば2件だけ取得したい場合、
SELECT * FROM hoge WHERE ROWNUM <= 2
とすると、行番号が2以下のレコードだけ選択されるので、2件だけレコードを取ってくることができます。
ROWNUMの罠
例えばWHEREにROWNUM <= 2ではなく、ROWNUM = 2 を指定した場合、2番目のレコードが返って来ると思いきや、何も返ってきません。
SQL > SELECT * FROM hoge WHERE ROWNUM = 2
レコードが選択されませんでした。
同様に ROWNUM >= 2 とか書いても何も返ってきません。
SELECT * FROM hoge WHERE ROWNUM >= 2
レコードが選択されませんでした。
なぜこうなるかというと、ROWNUMは結果セットの中での行番号を表しているからです。
言葉だと説明しにくいので、コードで書くと下記のような感じになります。(実際の実装ではありません)
rownum = 1 # ROWNUMには最初1が入っている
records.each do |record|
if select(record) # 行が選択された場合のみROWNUMの値がインクリメントされる
rownum += 1
end
end
1件目は ROWNUM = 2 の条件を満たさないので選択されず、2件目以降もROWNUMは1のままで、何も返ってこないという結果になります。
サブクエリを使う
では、ROWNUMでOFFSETみたいなことがやりたい時はどうするかというと、このように先にサブクエリでROWNUMを確定させてから範囲指定する必要があります。
SELECT
column1
FROM
(
SELECT
column1
, ROWNUM AS rn
FROM
hoge
)
WHERE
rn BETWEEN 10 AND 20
OFFSET/FETCH 構文
Oracle バージョン12cでOFFSET/FETCHという構文が追加されたそうです。
参考: 第37回 新しいSQLについて
例えば10行目から20行目を取得したい場合はOFFSET 10 ROWS FETCH FIRST 20 ROWS ONLYのように書けます。
SELECT column1 FROM hoge ORDER BY column1 DESC
OFFSET 10 ROWS FETCH FIRST 20 ROWS ONLY;
こっちを使ったほうがサブクエリを使う必要がないので、簡潔に書くことができます。
ということで、OracleにLIMIT句がないという話は以上です。