ORACLE の LIMIT OFFSET

業務でデータベースは PostgreSQL を使うことが多いですが ORACLE も使います。 その ORACLE が 12c になりました。 そう、12c になるということは LIMIT や OFFSET が使えるということです。 そんな嬉しさから書きます。

まずはデータの準備。

テーブルを作りたくないので 次の SQL でゴマかします。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4
         5         80          4
         7         75          6
         2         70          7

このようにテストの点数と ランキングが入っています。

まずは LIMIT ですが ORACLE (というかSQL:2008)では LIMIT ではなく 次のような書き方をします。

FETCH FIRST 100 ROWS ONLY

ちなみに この書き方は PostgreSQL でもサポートされています。 (完全に同じではありません)

まずは 4 行だけ取得。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
FETCH FIRST 4 ROWS ONLY
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4

4 行取得できました。

取得元のデータには RANK が 4 のデータが 2 レコードあります。 4位タイということですね。 4位までをを取得しようとして 4位が 1人しか取得できないのは困りますよね。

そんなときは 次のように書きます。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK
FETCH FIRST 4 ROWS WITH TIES
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4
         5         80          4

これで 4 位までを取得することができました。

ここで注意しなくてはならないのは ORDER BY から ID 列を外していることです。

WITH TIES の仕様は “ソート順の最後の項目が同じであれば取得する”ということなので ID が最後ではダメなのです。 もし更に“同順位は ID でソートしたい”ということであれば もう 1 段階ラッピングする必要があります。

行の他に百分率で指定することもできます。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
FETCH FIRST 50 PERCENT ROWS ONLY
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4

50% を指定したので 7行中の 4行が取得できました。

次は OFFSET です。
次のような書き方をします。

OFFSET 10 ROWS

OFFSET 4 で 5行目から取得してみます。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
OFFSET 4 ROWS
FETCH FIRST 4 ROWS ONLY
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         5         80          4
         7         75          6
         2         70          7

5 行目から 3行取得できました。

これでページ送りなんかの機能も簡単に作れますね。

Oracle のマルチテーブル・インサート

最近は PostgreSQL の機能も充実してきましたが Oracle にはまだまだ独自の機能が残っています。

今回は、前回やったマルチテーブル・インサートについて。

次のように INSERT文をまとめることができます。

SQL> INSERT ALL
  2    INTO table1 (field1, field2) VALUES ('xxxxx1', 'xxxxxxxxx')
  3    INTO table1 (field1, field2) VALUES ('xxxxx2', 'xxxxxxxxx')
  4    INTO table1 (field1, field2) VALUES ('xxxxx3', 'xxxxxxxxx')
  5  SELECT * FROM DUAL;

まとめるだけでなく複数のテーブルに INSERT できます。

SQL> INSERT ALL
  2    INTO table1 (field1, field2) VALUES ('xxxxx1', 'xxxxxxxxx')
  3    INTO table2 (field1, field2) VALUES ('xxxxx2', 'xxxxxxxxx')
  4    INTO table3 (field1, field2) VALUES ('xxxxx3', 'xxxxxxxxx')
  5  SELECT * FROM DUAL;

文末の DUAL に気づいたでしょうか?

  5  SELECT * FROM DUAL;

ここにはテーブルや福問い合わせを指定することができます。

つまり 次のようなことができてしまうわけです。

SQL> INSERT ALL
  2    INTO table1 (field1, field2) VALUES (fieldA, fieldB)
  3    INTO table2 (field1, field2) VALUES (fieldA, fieldC)
  4    INTO table3 (field1, field2) VALUES (fieldA, fieldD)
  5  SELECT * FROM table4;

さらに条件も指定することができます。

SQL> INSERT ALL
  2    WHEN fieldB < 10 THEN
  3      INTO table1 (field1, field2) VALUES (fieldA, fieldB)
  4    WHEN fieldC < 10 THEN
  5      INTO table2 (field1, field2) VALUES (fieldA, fieldC)
  6    WHEN fieldD < 10 THEN
  7      INTO table3 (field1, field2) VALUES (fieldA, fieldD)
  8  SELECT * FROM table4;

やっぱり Oracle は恐ろしいですね。

Oracle でも 1回の SQL で複数行 INSERT する

PostgreSQL や MySQL では次のように INSERT 文で 複数行を指定することができます。

psql=# INSERT INTO table1 (field1, field2)
psql-#   VALUES ('xxxxx1', 'xxxxxxxxx')
psql-#        , ('xxxxx2', 'xxxxxxxxx')
psql-#        , ('xxxxx3', 'xxxxxxxxx');

Oracle ではこの書き方ができません。

以下のように書きます。

SQL> INSERT ALL
  2    INTO table1 (field1, field2) VALUES ('xxxxx1', 'xxxxxxxxx')
  3    INTO table1 (field1, field2) VALUES ('xxxxx2', 'xxxxxxxxx')
  4    INTO table1 (field1, field2) VALUES ('xxxxx3', 'xxxxxxxxx')
  5  SELECT * FROM DUAL;

単に INSERT文をまとめただけのようですが この書き方は、異なるテーブルでもまとめることができます。 マルチテーブル・インサート と言うのですが 単に INSERT をまとめるだけではありません。

詳しくは次回。

Oracle で簡単棒グラフ表示

Oracle の SQL で簡単に棒グラフを作る小ネタです。

表示制御のために COLUMN とかを使っていますが ポイントは LPAD 関数の使い方です。

SQL> COLUMN TNAME FORMAT A30
SQL> COLUMN BAR   FORMAT A30

SQL> SELECT tname, LPAD('*', LENGTH(tname), '*') AS bar FROM TAB;

TNAME                          BAR
------------------------------ ------------------------------
W_AB_T_OPTAR_POLOLOPL_CLOSED   ****************************
W_START                        *******
E_AB_T_PLANT                   ************
E_AB_T_EERCC_BODY              *****************
E_AB_T_EERCC_HEAD              *****************
E_AB_PLANT_DATA                ***************
E_AB_KUJIDE_BODY               ****************
E_AB_KUJIDE_HEAD               ****************
R_AB_T_OPTAR_CLOSED            *******************
R_AB_T_OPTAR_KAMOKU            *******************
R_AB_KK_DATA                   ************

このサンプルはテーブルの名前の長さという かなりどうでも良いものを棒グラフにしてますが (棒グラフ化しなくてもそのままですし)、 棒グラフ化することで 見えやすくなるものは結構あると思います。