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行取得できました。

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

PHP 復習メモ - 論理演算子 && and || or

PHP の落とし穴シリーズ。
(ちなみに PHP のバージョンは 5.5.9 です)

PHP の論理演算子の論理積と論理積には "&&"、"and"、"||"、"or" がありますが、 && は、and の別名や古い記法ではありません。 演算の優先順位が異なっている別の演算子なのです。

次のような 2つの処理では、結果が変わることがあります。

# 1
$result = func1() && func2();
# 2
$result = func1() and func2();

and は代入演算子より優先順位が低いため 以下のように解釈されます。

# 1'
$result = (func1() && func2());
# 2'
($result = func1()) and func2();

関数 func1() が true を返し、 関数 func2() が false を返す場合 変数 $result には #1 は、false が格納されますが #2 は、true が格納されます。

|| と or の関係も同じです。

ちゃんとカッコで囲んでおけば大丈夫ですが、 とりあえずは && と || を使うようにしておけば良いのかなと思います。

PHP 復習メモ - isset() は null のときも false

PHP の落とし穴シリーズ。
(ちなみに PHP のバージョンは 5.5.9 です)

まだ isset() が 言語構造だとかわかってなかったときに 落とし穴に落ちたことがあります。

次のように配列の要素を取り出そうとして そのキーが存在しない場合、警告が出ます。

$key = $my_array['key'];
Notice: Undefined index: key in ...

isset を使うと確認ができます。

$key = isset($my_array['key']) ? $my_array['key'] : NULL;

if文でも便利です。

if (isset($my_array['key']) && $my_array['key'] ==  ...

PHP の &&(and) や ||(or) は短絡評価なので 後続の式の評価はされません。(警告が出ません)

ただ、この isset ですが、 値が null の場合も true になってしまいます。

たとえば、 次のような処理を書いてしまうと isset() が false になってしまい is_null() が評価されません。

$my_array['key'] = null;

if (isset($my_array['key']) && is_null($my_array['key'])) {
    echo "null";
}
else {
    echo "not null";
}
not null

このような場合は array_key_exists などの他の手段を使う必要があります。

$my_array['key'] = null;

if (array_key_exists('key', $my_array) && is_null($my_array['key'])) {
    echo "null";
}
else {
    echo "not null";
}
null

とは言え、isset() は便利ですので 理解した上で、使い分ける必要があるのだと思います。

[参考]
PHP: array_key_exists - Manual

PHP 復習メモ - PHPの三項演算子は左結合

PHP が他の言語と違う変なトコの 1つです。
(ちなみに PHP のバージョンは 5.5.9 です)

あまりこんな書き方はしないと思うので 実害はないかもしれませんが PHP の三項演算子は 他の言語と違って左結合です。

$ php -r "echo true ? 1 : false ? 2 : 3;"
2

結果は 2 が返ってきます。

これは次のように左結合で解釈されるためです。

((true ? 1 : false) ? 2 : 3);

他の言語だとだいたい右結合ですよね。

(true ? 1 : (false ? 2 : 3));

とりあえず、三項演算子を使うときはカッコで囲みましょう。

余談ですが、1つ目の値(判定に使っている値)と 2つ目の値(TRUEの時の値)が同じ場合、 省略することができます。

$result = func() ?: null;

I/O 処理などがあり 2回実行したくないときに使えます。

PHP 復習メモ - 同じ関数名は定義できない

最近 PHP 力が下がってる気がするので基本から復習中。
(ちなみに PHP のバージョンは 5.5.9 です)

PHP では引数が違っていても同じ関数名は定義できません。

function a ($v1) {
    ...
}

function a ($v1, $v2) {
    ...
}

// Fatal error: Cannot redeclare a() in ...

Fatal error が発生します。

PHP では関数名の大文字小文字を区別しないため 大文字・小文字でもエラーになります。

function a ($v1) {
    ...
}

function A ($v1, $v2) {
    ...
}

// Fatal error: Cannot redeclare A() in ...

PHPの関数も再定義はできません。

function strlen ($v1) {
    ...
}

// Fatal error: Cannot redeclare strlen() in ...

クラスやメソッドも同じです。 (ちなみに、クラス名やメソッド名も大文字小文字を区別しません)

class Test {}

class Test {}

// Fatal error: Cannot redeclare class Test in ...
class Test {

    function b ($v1) {
        ...
    }

    function b ($v1, $2) {
        ...
    }
}

// Fatal error: Cannot redeclare Test::b() in ...

久しぶりに PHP でハマったこと - 配列とリファレンス

結構あるあるだと思うのですが、すっかり忘れていてハマってしまいました。 ちなみに PHP のバージョンは 5.5.9 です。

PHP の配列の代入は値渡しです。

$a = [1, 2, 3];

$b = $a[0];

$c = $a;
$c[0] = 4;

print_r($a);
// Array ( [0] => 1 [1] => 2 [2] => 3 )

上記のように代入先の配列 $c の要素を変更しても 元の配列 $a の要素は変更されません。

ところが、次のように 配列の要素を参照渡ししてしまうと その要素はリファレンスになってしまいます。

$a = [1, 2, 3];

$b =& $a[0];

$c = $a;
$c[0] = 4;
$c[1] = 6;

print_r($a);
// Array ( [0] => 4 [1] => 2 [2] => 3 )

その結果、上記のように値渡ししたはずの配列 $c の要素を変更したのに 元の配列 $a の要素が変更されてしまいます。

このとき、更に怖いのが $a の 2つ目の要素は 変更されていないことです。 変数 $b に参照渡しした 1つ目の要素だけがリファレンスになります。

これは関数に引数として配列を渡すときも同じです。

function test($arr) {
    $arr[0] = 8;
}

$a = [1, 2, 3];

$b =& $a[0];

test($a);

print_r($a);
// Array ( [0] => 8 [1] => 2 [2] => 3 )

関数の内部で引数の配列を変数としてそのまま利用していると 変更した内容が、元の配列に反映されてしまします。

PHPのマニュアルにも書いてあります。

[参考]
PHP: リファレンスが行うことは何ですか? - Manual

通常あまり発生することではないと思いますが それゆえに忘れがちで気づきにくいですね。

Google サイト内検索

Amazonアソシエイト