More than 3 years have passed since last update.

Laravel + Oracle 12c 以前の名残と向き合うために

Posted at 2020-05-16

対象読者

様々な事情で、DB Oracle バージョン12c以前を利用している
- もしくは、12c以前の名残が残っており、SEQUENCEオブジェクトから取得した番号をIDカラムに指定してINSERTしている
Laravelを利用している

yajra/laravel-oci8 パッケージを利用して IDの採番を意識しなくても済むようにする

Oracle 12c でIdentity Columnsの登場により、以前よりIDの自動採番を意識しなくて済むようになりました。

内部的にはSEQUENCEオブジェクトを生成して、 .NEXTVAL を取得してます。

CREATE TABLE　HOGE (
    ID    NUMBER    GENERATED AS IDENTITY,
    ..

もしくは、12c以降だとカラムのDEFAULT値にSEQUENCEを指定できるようにもなっています。

CREATE TABLE　HOGE (
    ID    NUMBER    DEFAULT HOGE_SQ.NEXTVAL  NOT NULL,
    ..

これから新規に作成するテーブルは、これに準じて作成すればいいと思うんですが、

なかなか既存のテーブルの変更に踏み込めていなかったりするわけです。

12c以前の名残で、SEQUENCEオブジェクトから .NEXTVAL で次の番号を取得して INSERT の際に ID に指定しているというサービスも少なくはないと思います。

CREATE TABLE　HOGE (
    ID    NUMBER    NOT NULL, -- IDENTITY も DEFAULT SEQUENCE も使えていない名残

INSERT INTO HOGE (ID, ..) VALUES ({SEQUENCEオブジェクトから .NEXTVAL で取得した値}, ..)

データを登録する度に、アプリ側のコードでIDを考慮してSEQUENCEオブジェクトから番号を取得して、IDに埋め込んで..

というのは、手間で、なるべくサービスのアプリケーションコードでは気にしたくないですよね。

幸いにも、Laravelを利用していると、 yajra/laravel-oci8 パッケージというものが見つかりました 👀

使い方は簡単で、モデルクラスに $sequence メンバ変数を定義するだけです。

class Hoge extends Model -- Illuminate\Database\Eloquent\Model
{
    public $sequence = 'HOGE_SQ';
    ..

これを定義してあげると、わざわざIDに埋め込む必要もなくなります。

ただし、もともとIDを指定してデータを作成しているようなアプリケーションコードがあれば注意が必要です！

Hoge::create(['ID' => 1, 'NAME' => 'hoge']);

例えば、上記のようなコードがもともと存在していたとすると、 ORA-01036: illegal variable name/number エラーが発生します。

原因は、Oracleの RETURNING句 と INTO句 です。

内部的に実行されるクエリを確認してみましょう。

DB::enableQueryLog();
// 実行されるクエリを確認したいコード
Hoge::create(['ID' => 1, 'NAME' => 'hoge']);
dd(DB::getQueryLog());

insert into "HOGE" ("ID", "NAME") values (?, ?) returning "ID" into ?
// bindされる値は順に、 1, hoge, {.NEXTVALから採番された値}

Oracleの RETURNING句 と INTO句 を組み合わせることで、

INSERTによって影響を受けたレコードに対しての評価（IDは {.NEXTVALから採番された値} ですよね? という評価）をしています。

結果、 1 と {.NEXTVALから採番された値} が異なるのでエラーとなってしまいます。

もし、 $sequence メンバ変数を定義する際には、既存コードの影響を確認した上で変更を加えてください。