Oracle SQL 順序(SEQUENCE)
Oracleでは連続した数値を発生させる機能を順序という名前で提供しています。この順序が無かった時のバージョンでは以下の様なことがありました。
一般的な販売管理等で、伝票番号の様に連続したユニークな数値が必要な場合があります。伝票番号の生成は伝票入力プログラムで入力を確定した時点で行われるものですが、該当する伝票データ表の伝票番号の最大値を取得しそれを書込みデータの伝票番号としていました。
さらに、複数の端末からの処理を可能にする為には、この伝票番号を取得する時に、その前後でロック及びアンロックを行う必要があります。このことは、安全にかつ確実に伝票番号を設定できなくなる可能性を示しています。
こういったことを解消する為にOracleは、順序を導入し複数のユーザからアクセスが発生しても順番にユニークな番号を生成する機能を提供しています。順序は以下のCREATE SEQUENCE文により生成します。
おすすめ書籍
本書用のWebアプリを使ってSQLを実行し学習していきます。初心者の方に分かりやすく、図表を多く使って解説されています。 学習の最後には200問越えのドリルが付いていて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 |
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命令により参照します。
SEQUENCE_NAME NOT NULL VARCHAR2(30) |
INCREMENT_BY NOT NULL NUMBER |
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 |
ORA-08002: 順序TS_伝票番号.CURRVALはこのセッションではまだ定義されていません |
SQL> SELECT TS_伝票番号.NEXTVAL FROM DUAL; |
SQL> SELECT TS_伝票番号.CURRVAL FROM DUAL; |
次のNEXTVALの参照までは、何度CURRVALの参照を行っても値は変化しません。
SELECT文中でNEXTVALを参照するだけではなく、その他のINSERT文等でも参照可能です。
以下にINSERT文でNEXTVALを参照する例を示します。
SQL> SELECT * FROM TT_売上; |
2 VALUES(TS_伝票番号.NEXTVAL, SYSDATE, 1, 1); |
SQL> SELECT * FROM TT_売上; |
■関連記事
⇒
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 |
ORA-08002: 順序TS_伝票番号.CURRVALはこのセッションではまだ定義されていません |
SQL> SELECT TS_伝票番号.NEXTVAL FROM DUAL; |
SQL> SELECT TS_伝票番号.CURRVAL FROM DUAL; |
