Oracle SQL 順序(SEQUENCE)
Oracleでは連続した数値を発生させる機能を順序という名前で提供しています。この順序が無かった時のバージョンでは以下の様なことがありました。
一般的な販売管理等で、伝票番号の様に連続したユニークな数値が必要な場合があります。伝票番号の生成は伝票入力プログラムで入力を確定した時点で行われるものですが、該当する伝票データ表の伝票番号の最大値を取得しそれを書込みデータの伝票番号としていました。
さらに、複数の端末からの処理を可能にする為には、この伝票番号を取得する時に、その前後でロック及びアンロックを行う必要があります。このことは、安全にかつ確実に伝票番号を設定できなくなる可能性を示しています。
こういったことを解消する為にOracleは、順序を導入し複数のユーザからアクセスが発生しても順番にユニークな番号を生成する機能を提供しています。順序は以下のCREATE SEQUENCE文により生成します。
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
CREATE SEQUENCE文の構文
CREATE SEQUENCE <順序名> [ START WITH <初期値> ] [ INCREMENT BY <増分値> ] [ MAXVALUE <最大値> | NOMAXVALUE ] [ MINVALUE <最小値> | NOMINVALUE ] [ CYCLE | NOCYCLE ] [ CACHE <キャッシュ数> | NOCYCLE ]
<CYCLE>について
CYCLEの指定は順序の値がMAXVALUEに到達後、次の順序値を取得する時にMINVALUEに戻るかどうかの設定です。
| 各パラメータを指定しない場合のデフォルト値 | |
|---|---|
| START WITH | 1 |
| INCREMENT BY | 1 |
| MAXVALUE | NOMAXVALUE |
| MINVALUE | 1 |
| CYCLE | NOCYCLE |
| CACHE | 20 |
順序の生成
SQL> CREATE SEQUENCE TS_伝票番号; 順序が作成されました。
これにより順序名が"TS_伝票番号"で全てのパラメータはデフォルト値で生成されました。
実際に順序のパラメータがその様に設定されたかは、USER_SEQUENCESデータディクショナリビューを検索すると様子がわかります。
SQL> SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TS_伝票番号'; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- TS_伝票番号 1 1.0000E+27 1 N N 20 1
また、別の順序を今度は少しパラメータを設定して生成します。
SQL> CREATE SEQUENCE TS_伝票番号_2 2 START WITH 100 3 INCREMENT BY 2 4 MAXVALUE 1000 5 MINVALUE 10 6 CYCLE 7 CACHE 40; 順序が作成されました。 SQL> SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME LIKE 'TS_伝票番号%'; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- TS_伝票番号 1 1.0000E+27 1 N N 20 1 TS_伝票番号_2 10 1000 2 Y N 40 100
USER_SEQUENCESデータディクショナリビューの列定義をDESC命令により参照します。
SQL> DESC USER_SEQUENCES 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- SEQUENCE_NAME NOT NULL VARCHAR2(30) MIN_VALUE NUMBER MAX_VALUE NUMBER INCREMENT_BY NOT NULL NUMBER CYCLE_FLAG VARCHAR2(1) ORDER_FLAG VARCHAR2(1) CACHE_SIZE NOT NULL NUMBER LAST_NUMBER NOT NULL NUMBER
この定義により、SEQUENCE_NAME（順序名）はVARCHAR2(30)の制限がありますので30バイトを
超えることはできません。もっとも順序名ですからそんなに長い名前は必要は無いはずです。
順序の値の取得
順序から値を取得する為の機構として、NEXTVAL、CURRVALの擬似列を利用します。NEXTVALは次の順序値を取得するもので、CURRVALは現在値を取得するものです。これらを単純に取得する場合はSYSDATE擬似列の様に、システムの用意してあるDUAL擬似表からSELECTすることになります。
ただし、CURRVALは少し注意が必要で、順序を生成した直後にこのCURRVALをSELECTするとエラーが発生します。Oracle上では順序の生成直後にはどうも現在値がまだ存在しないようです。このために、生成直後にはまずNEXTVALを取得することで現在値を確定してやります。
この様子を以下のSQL実行で確認します。
SQL> SELECT TS_伝票番号.CURRVAL FROM DUAL;
SELECT TS_伝票番号.CURRVAL FROM DUAL
*
1行でエラーが発生しました。
ORA-08002: 順序TS_伝票番号.CURRVALはこのセッションではまだ定義されていません
SQL> SELECT TS_伝票番号.NEXTVAL FROM DUAL;
NEXTVAL
----------
1
SQL> SELECT TS_伝票番号.CURRVAL FROM DUAL;
CURRVAL
----------
1
次のNEXTVALの参照までは、何度CURRVALの参照を行っても値は変化しません。
SELECT文中でNEXTVALを参照するだけではなく、その他のINSERT文等でも参照可能です。
以下にINSERT文でNEXTVALを参照する例を示します。
SQL> SELECT * FROM TT_売上;
売上番号 売上日 得意先コード 担当者コード
---------- -------- ------------ ------------
1 02-11-01 1 1
2 02-11-03 2 4
3 02-11-04 4 5
4 02-11-13 3 2
5 02-11-15 5 7
6 02-11-15 1 9
6行が選択されました。
SQL> INSERT INTO TT_売上
2 VALUES(TS_伝票番号.NEXTVAL, SYSDATE, 1, 1);
1行が作成されました。
SQL> SELECT * FROM TT_売上;
売上番号 売上日 得意先コード 担当者コード
---------- -------- ------------ ------------
1 02-11-01 1 1
2 02-11-03 2 4
3 02-11-04 4 5
4 02-11-13 3 2
5 02-11-15 5 7
6 02-11-15 1 9
7 02-12-05 1 1
7行が選択されました。
■関連記事
⇒INSERT...VALUES文の構文順序の削除
DROP SEQUENCE <順序名>
DROP SEQUENCE文により指定した順序を削除します。
SQL> SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME LIKE 'TS_伝票番号%'; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- TS_伝票番号 1 1.0000E+27 1 N N 20 21 TS_伝票番号_2 10 1000 2 Y N 40 100 SQL> DROP SEQUENCE TS_伝票番号_2; 順序が削除されました。 SQL> SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME LIKE 'TS_伝票番号%'; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------------------------ ---------- ---------- ------------ - - ---------- ----------- TS_伝票番号 1 1.0000E+27 1 N N 20 21
順序とセッションについて
順序の生成のところで、順序を生成した直後にこのCURRVALをSELECTするとエラーが発生することを述べましたが、ユーザセッションが確立された直後(データベースへのログイン後)も同様になります。以下にその様子を示します。
順序は伝票番号やマスターコードなど一意な連番が必要なところで使用されると思いますが、各データを新規に追加する場合には必ずNEXTVALで値を取得するべきだと思います。
尚、VisualBasic等のアプリケーションでINSERTを行う場合、伝票番号を設定する場合は予め、SELECT文で順序のNEXTVALで値を取得後、INSERT文のVLAUE句の中に設定すれば、今INSERTされた伝票番号が何番であったかが分ります。
SQL*Plus: Release 9.0.1.0.1 - Production on 木 Jun 3 17:54:04 2004
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
JServer Release 9.0.1.1.1 - Production
に接続されました。
SQL> SELECT TS_伝票番号.CURRVAL FROM DUAL;
SELECT TS_伝票番号.CURRVAL FROM DUAL
*
1行でエラーが発生しました。
ORA-08002: 順序TS_伝票番号.CURRVALはこのセッションではまだ定義されていません
SQL> SELECT TS_伝票番号.NEXTVAL FROM DUAL;
NEXTVAL
----------
25
SQL> SELECT TS_伝票番号.CURRVAL FROM DUAL;
CURRVAL
----------
25
