トップ > SQL入門 > 36章

36章順序

それでは今回は順序について学んでいきましょう。

順序とは一意な値を生成するためのオブジェクトです。

どのような時に一意な値が必要なのでしょうか。
例えば銀行の口座番号は必ず一意な番号でないと問題が発生します。
一意な番号を発行するためには、プログラムを作るのも手間がかかります。
こんな面倒なことをしなくても順序を作成すれば簡単に一意な値を作成できます。

それでは順序の作成方法です。

[構文]　順序の作成
　CREATE SEQUENCE シーケンス名
　[ INCREMENT BY 値 ]
　[ START WITH 値 ]
　[ MAXVALUE 値 | NOMAXVALUE ]
　[ MINVALUE 値 | NMINVALUE ]
　[ CYCLE | NOCYCLE ]
　[ CACHE 値 | NOCACHE ];

順序のオプション

INCREMENT BY	増分値 (デフォルト1)
START WITH	初期値 (デフォルト1)
MAXVALUE NOMAXVALUE	最大値 (デフォルトNOMAXVALUE)
MINVALUE NMINVALUE	最少値(デフォルトNOMINVALUE)
CYCLE NOCYCLE	CYCLEの場合、最大値に達すると最小値に戻り、順序値を生成 (デフォルトCYCLE)
CACHE NOCACHE	CACHEの場合、メモリ上に事前に順序値を生成 (デフォルトCACHE 20)

順序のオプションはすべてデフォルト値があるため、
特にオプションをしていない場合はすべてデフォルト値で作成されます。

では実行例です。


  SQL> create sequence test_seq
    2  increment by 10
    3  start with 100
    4  maxvalue 9999
    5  nocycle;

  Sequence created.

上記コマンドで順序を作成することが出来ました。
次に使用してみましょう。まずは順序値を表示します。

現在の順序値を表示するにはcurrvalキーワードを使用します。
順序名.currvalとすることで現在の順序値を確認できます。


  SQL> select test_seq.currval from dual;
  select test_seq.currval from dual
                               *
  ERROR at line 1:
  ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session

エラーとなってしまいました。
TEST_SEQの順序はまだこのセッションでは定義されていません。
まだ作成しただけで順序値を生成していませんでした。
次の順序値を生成するには順序名.nextvalです。


  SQL> select test_seq.nextval from dual;

     NEXTVAL
  ----------
         100

今回初期値を100としましたので、100が最初に表示されました。
このように順序値を生成する場合にはSELECT文で順序名.nextvalを指定してください。

では何回か実行していきましょう。


  SQL> select test_seq.nextval from dual;

     NEXTVAL
  ----------
         110

もう一度実行すると次の順序値を生成します。増分値は10なので次は110が表示されました。
次にcurrvalを指定し現在の順序値を確認してみましょう。


  SQL>  select test_seq.currval from dual;

     CURRVAL
  ----------
         110

順序の使用方法が理解できてきましたので、DML文で使用してみましょう。
DML文も使用法は一緒です。


  SQL> insert into departments values (test_seq.nextval,'TEST',1);

  1 row created.

  SQL> select * from departments;

     DEPT_ID DEPT_NAME                          LOC_ID
  ---------- ------------------------------ ----------
          10 Human Resources                         1
          20 Consulting                              1
          30 Sales                                   2
          40 Engineering                             2
          50 Human Resources                         1
          60 International
         100 DB                                      3
         120 TEST                                    1

  9 rows selected.

上記例ではdept_id列に対し順序値を使用しました。
DEPT_IDが120のレコードが追加されています。

このように行を挿入するときに、意識せずに一意な値を生成できるのは
便利なオブジェクトですね。

順序の動作

では少し注意点を確認していきます。

INSERT文を数回間違えてみましょう。


  SQL> select test_seq.currval from dual;

     CURRVAL
  ----------
         120

  SQL> insert into departments values (test_seq.nextval,'TEST','TEST');
  insert into departments values (test_seq.nextval,'TEST','TEST')
                                                          *
  ERROR at line 1:
  ORA-01722: invalid number

  SQL> select test_seq.currval from dual;

     CURRVAL
  ----------
         130

SQLを失敗してしまった後で、もう一度順序値を確認したところ、順序値が増えています。
順序は一度生成すると元に戻ることはできません。


  SQL> select test_seq.currval from dual;

     CURRVAL
  ----------
         130

  SQL> insert into departments values (test_seq.nextval,'TEST',1);

  1 row created.

  SQL> rollback;

  Rollback complete.

  SQL> select test_seq.currval from dual;

     CURRVAL
  ----------
         140

上記例ではINSERT文実行後、ROLLBACKを実行しましたが順序値が増えています。

このように順序値はSQLが失敗しても、ROLLBACKが実行しても、順序値は戻りません。
その結果、SQL文が失敗時や、ROLLBACKが実行時にはその順序値が飛んでしまいます。

またその他として、以下のような場合も順序値が飛んでしまいます。

・順序値をほかのユーザと共有して使用している場合
・CACHEオプションを使用している時に、DB障害が発生した場合

順序値を別のユーザと共有で使用している場合、片方のユーザからすれば、
他のユーザに順序値を使用されると、その使用された順序値は使用できなくなるため、
順序値が飛んでいるように見えます。

またCACHEオプションはあらかじめ、メモリ上に順序値を生成しておくことで、
性能が早くなるオプションですが、このCACHEオプション使用時に、DB障害で
データベースがダウンしてしまうと、メモリ上にあった順序値はなくなってしまいます。
その結果、最後に生成された順序値から、新しく次の順序値を生成します。

このように順序値を使用していると、順序値が連続した値でなくなる可能性があります。
連続した値を生成したいなどの条件がある場合はご注意ください。
では最後に順序の削除方法です。

[構文]　順序の削除
　DROP SEQUENCE 順序名;

上記コマンドで順序を削除することができます。