create sequence seq01 increment by 1 start with 1 maxvalue 99999999
minvalue 1 cycle order cache 20
;
作成シーケンスの確認
select s.SEQUENCE_OWNER,s.SEQUENCE_NAME,s.MIN_VALUE,s.MAX_VALUE,s.INCREMENT_BY
,s.CYCLE_FLAG,s.CACHE_SIZE,s.LAST_NUMBER
from dba_sequences s inner join dba_users u on s.SEQUENCE_OWNER = u.USERNAME
and u.COMMON = 'NO'
where s.SEQUENCE_NAME='SEQ01'
;
シーケンスの現在値取得
select seq01.currval as id from dual
;
シーケンス値取得（インクリメント）
select seq01.nextval from dual
;
SQL> create sequence seq01 increment by 1 start with 1 maxvalue 99999999
2 minvalue 1 cycle order cache 20
3 ;
順序が作成されました。
SQL> select s.SEQUENCE_OWNER,s.SEQUENCE_NAME,s.MIN_VALUE,s.MAX_VALUE,s.INCREMENT_BY
2 ,s.CYCLE_FLAG,s.CACHE_SIZE,s.LAST_NUMBER
3 from dba_sequences s inner join dba_users u on s.SEQUENCE_OWNER = u.USERNAME
4 and u.COMMON = 'NO'
5 where s.SEQUENCE_NAME='SEQ01'
6 ;
SEQUENCE_O SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CY CACHE_SIZE LAST_NUMBER
---------- -------------------- ---------- ---------------- ------------ -- ---------- -----------
SOONI SEQ01 1 99999999 1 Y 20 1
SQL>
-- currvalは現在のセッション内で事前にnextvalしていないと利用できません。
--
SQL> select seq01.currval as id from dual;
select seq01.currval as id from dual
*
行1でエラーが発生しました。:
ORA-08002: 順序SEQ01.CURRVALはこのセッションではまだ定義されていません
SQL> select seq01.nextval from dual;
NEXTVAL
----------
2
SQL> select seq01.currval as id from dual;
ID
----------
2
SQL>
SQL*Plus用
set lin 300
COLUMN SEQUENCE_OWNER FORMAT A10
COLUMN SEQUENCE_NAME FORMAT A16
COLUMN SEQUENCE_NAME FORMAT A16
COLUMN MAX_VALUE FORMAT 99999999999999999999999999990
COLUMN OWNER FORMAT A10
COLUMN TABLE_NAME FORMAT A16
COLUMN COLUMN_NAME FORMAT A16
COLUMN SEQUENCE_NAME FORMAT A20
COLUMN GENERATION_TYPE FORMAT A14
