Oracleでページングを実現！ OFFSET句の使い方

 
一覧画面の表示などでページャーを作成する場合、
DBからデータを取得するSQLで取得件数を制御する方法があります。
 
oracle DatabeseではこれまでROWNUMやROW_NUMBERで実現可能でしたが、
ROWNUMの採番順序とソートの順序などを考慮するなど直感的にできませんでした。
 
しかしoracle12cからはOFFSET句に対応しており、簡単に取得する行数を指定できるようになりました。
 
そこで今回はOFFSET句についてご説明したいと思います。
 

構文

SELECT … [OFFSET <スキップ行数> ROWS]  
FETCH {FIRST|NEXT} [<取得行数>|<パーセント> PERCENT]  
ROWS {ONLY|WITH TIES} ;
 
 

基本的な使い方

以下のようなID、NAME、RANKを持つSAMPLEテーブルを用意します。

SQL> SELECT * FROM SAMPLE ORDER BY RANK;
        ID NAME                 RANK
---------- -------------------- --------------------
         3 Aさん                1位
         2 Bさん                2位
         6 Cさん                3位
         1 Eさん                4位
         4 Dさん                4位
         5 Fさん                6位
         7 Gさん                7位

SQL> SELECT * 
FROM SAMPLE 
ORDER BY RANK 
OFFSET 1 
ROWS FETCH FIRST 3 ROWS ONLY;
        ID NAME                 RANK
---------- -------------------- --------------------
         2 Bさん                2位
         6 Cさん                3位
         1 Eさん                4位

WITH TIESの使い方

基本的な使い方ではOFFSET句の最後にROWS ONLYを指定していますが
ここでWITH TIESを指定すると、最後の行のORDER BYキーと同じ値の行が全て出力されます。

SQL> SELECT * 
FROM SAMPLE 
ORDER BY RANK 
OFFSET 1 
ROWS FETCH FIRST 3 ROWS WITH TIES;
        ID NAME                 RANK
---------- -------------------- --------------------
         2 Bさん                2位
         6 Cさん                3位
         1 Eさん                4位
         4 Dさん                4位

PERCENTの使い方

基本的な使い方では取得する行数を直接指定して取得していますが、
取得する行数を％(割合)でも指定することができます。
ただしこの割合とはスキップする前の行数、
つまりOFFSET句の前までのSQLに対する割合ですので注意してください。

SQL> SELECT * 
FROM SAMPLE 
ORDER BY RANK 
OFFSET 1 
ROWS FETCH FIRST 50 PERCENT ROWS ONLY;
        ID NAME                 RANK
---------- -------------------- --------------------
         2 Bさん                2位
         6 Cさん                3位
         1 Eさん                4位
         4 Dさん                4位

SQL> SELECT * 
FROM SAMPLE 
WHERE ID > 3
ORDER BY RANK 
OFFSET 1 
ROWS FETCH FIRST 50 PERCENT ROWS ONLY;
        ID NAME                 RANK
---------- -------------------- --------------------
         4 Dさん                4位
         5 Fさん                6位

まとめ

新しい書き方で書くよりも慣れ親しんだ書き方の方が良いという方もいるかと思います。
しかし、OFFSET句を使用することで副問い合わせが不要になるなど可読性の向上が可能です。
保守等でSQLを解析する時なども考慮して、しっかり抑えておきましょう。