MySQLやPostgreSQLでは、出力する件数を指定する LIMIT 句が用意されているが、Oracle Databaseには存在しないため他の記法を利用する必要がある。毎回忘れるので備忘のため記事にしておく。
サンプルデータ
SQLで操作するためのサンプルデータとして、以下のような id に都道府県コード、name に都道府県名のローマ字表記にしたものをレコードに持つテーブルを作成する。
| id | name |
|---|---|
| 1 | Hokkaido |
| 2 | Aomori |
| ... | ... |
| 46 | Kagoshima |
| 47 | Okinawa |
ただし、あとでソートの操作との組み合わせを確認する際に分かりやすくするため、id の連番ではなく適当に並べ替えた順番で INSERT していく。
CREATE TABLE prefecture (
id NUMBER,
name VARCHAR2(10)
);
INSERT INTO prefecture VALUES (1, 'Hokkaido');
INSERT INTO prefecture VALUES (24, 'Mie');
INSERT INTO prefecture VALUES (10, 'Gunma');
INSERT INTO prefecture VALUES (2, 'Aomori');
INSERT INTO prefecture VALUES (17, 'Ishikawa');
（中略）
INSERT INTO prefecture VALUES (7, 'Fukushima');
INSERT INTO prefecture VALUES (42, 'Nagasaki');
INSERT INTO prefecture VALUES (34, 'Hiroshima');
INSERT INTO prefecture VALUES (44, 'Oita');
INSERT INTO prefecture VALUES (47, 'Okinawa');
作成した直後に全件 select をかけると、以下のように INSERT した順番で出力される。
SQL> SELECT * FROM prefecture;
ID NAME
---------- ----------
1 Hokkaido
24 Mie
10 Gunma
2 Aomori
17 Ishikawa
（中略）
ID NAME
---------- ----------
34 Hiroshima
44 Oita
47 Okinawa
47 rows selected.
（参考）limit 句を使おうとすると、正常な SQL とみなされずエラーになる。
SQL> SELECT * FROM prefecture LIMIT 10;
SELECT * FROM prefecture LIMIT 10
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ROWNUM を使う
WHERE句の中で ROWNUM を使って、取得する行数を指定する。例えば、先頭の10行を取得したい場合は WHERE ROWNUM <= 10 のように指定する。
SQL> SELECT * FROM prefecture WHERE ROWNUM <= 10;
ID NAME
---------- ----------
1 Hokkaido
24 Mie
10 Gunma
2 Aomori
17 Ishikawa
39 Kochi
6 Yamagata
41 Saga
11 Saitama
8 Ibaraki
10 rows selected.
ソートした上で行数を指定したい場合は注意が必要。SQL では WHERE > ORDER の順に記述する必要があるので、愚直に書くと取得したものをソートしたものが出力されることになる。
SQL> SELECT * FROM prefecture WHERE ROWNUM <= 10 ORDER BY id;
ID NAME
---------- ----------
1 Hokkaido
2 Aomori
6 Yamagata
8 Ibaraki
10 Gunma
11 Saitama
17 Ishikawa
24 Mie
39 Kochi
41 Saga
10 rows selected.
SQL> SELECT * FROM prefecture ORDER BY id WHERE ROWNUM <= 10;
SELECT * FROM prefecture ORDER BY id WHERE ROWNUM <= 10
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ソートしたものの先頭行を取得したい場合には、副問合せの形で書く必要がある。
SQL> SELECT * FROM (SELECT * FROM prefecture ORDER BY id) WHERE ROWNUM <= 10;
ID NAME
---------- ----------
1 Hokkaido
2 Aomori
3 Iwate
4 Miyagi
5 Akita
6 Yamagata
7 Fukushima
8 Ibaraki
9 Tochigi
10 Gunma
10 rows selected.
FETCH FIRST N ROWS ONLY を使う
Oracle Database 12c 以降で追加されている構文。該当するものの先頭 N 行を取得する。
SQL> SELECT * FROM prefecture FETCH FIRST 10 ROWS ONLY;
ID NAME
---------- ----------
1 Hokkaido
24 Mie
10 Gunma
2 Aomori
17 Ishikawa
39 Kochi
6 Yamagata
41 Saga
11 Saitama
8 Ibaraki
10 rows selected.
ROWNUM の際と同様に、ORDER と組み合わせたい場合は注意が必要。こちらの場合は ORDER > FETCH の順に記載する必要がある。
SQL> SELECT * FROM prefecture FETCH FIRST 10 ROWS ONLY ORDER BY id;
SELECT * FROM prefecture FETCH FIRST 10 ROWS ONLY ORDER BY id
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> SELECT * FROM prefecture ORDER BY id FETCH FIRST 10 ROWS ONLY;
ID NAME
---------- ----------
1 Hokkaido
2 Aomori
3 Iwate
4 Miyagi
5 Akita
6 Yamagata
7 Fukushima
8 Ibaraki
9 Tochigi
10 Gunma
10 rows selected.
指定した行数を取得してからソートしたい場合は、副問合せの形で書く必要がある。
SQL> SELECT * FROM (SELECT * FROM prefecture FETCH FIRST 10 ROWS ONLY) ORDER BY id;
ID NAME
---------- ----------
1 Hokkaido
2 Aomori
6 Yamagata
8 Ibaraki
10 Gunma
11 Saitama
17 Ishikawa
24 Mie
39 Kochi
41 Saga
10 rows selected.
こちらの記法の方が limit 句と同じように記述できるため、MySQL や PostgreSQL をすでに利用しているのであれば理解しやすいと思う。
Oracle Database 12c 以前では使えないというデメリットはあるが、12c がリリースされたのは 2014年であるため、比較的新しいサービスであれば問題なく利用できるのではないかと思う。
![図解入門よくわかる 最新Oracleデータベースの基本と仕組み[第5版]](https://m.media-amazon.com/images/I/51zFPGvW4HL._SL500_.jpg "図解入門よくわかる 最新Oracleデータベースの基本と仕組み[第5版]")
