Tested on Oracle 9. You can create a sequence as follows. However, if you tell it to start at zero without specifying an appropriate MINVALUE, you get an ORA-04006:
SQL> create sequence sequence1
2 increment by 7
3 start with 0
4 nocache
5 /
create sequence sequence1
*
ERROR at line 1:
ORA-04006: START WITH cannot be less than MINVALUE
SQL> create sequence sequence1
2 increment by 7
3 minvalue 0
4 start with 0
5 nocache
6 /
Sequence created.
SQL>
Once you have created a sequence, you can access successive terms as follows. You use them wherever you need to have a series of unique values:
SQL> select sequence1.nextval
2 from dual
3 /
NEXTVAL
----------
0
SQL> select sequence1.nextval
2 from dual
3 /
NEXTVAL
----------
7
SQL>
Once you have selected a term for a sequence, you can refer to that value (the current value) as often as you want:
SQL> select sequence1.currval
2 from dual
3 /
CURRVAL
----------
7
SQL> select sequence1.currval
2 from dual
3 /
CURRVAL
----------
7
SQL>
If you start a new session, you cannot look at the sequence’s CURRVAL in this way:
SQL> conn / as sysdba
Connected.
SQL> select sequence1.currval
2 from dual
3 /
select sequence1.currval
*
ERROR at line 1:
ORA-08002: sequence SEQUENCE1.CURRVAL is not yet
defined in this session
SQL>
If you want to do that, you have to SELECT the sequence’s NEXTVAL first:
SQL> select sequence1.nextval
2 from dual
3 /
NEXTVAL
----------
14
SQL>
This reserves that term for the new session’s use. Then you can look at the current value as often as you want. This will not change even if other sessions select new values from the sequence in the meantime:
SQL> select sequence1.currval
2 from dual
3 /
CURRVAL
----------
14
SQL> select sequence1.currval
2 from dual
3 /
CURRVAL
----------
14
SQL>
No comments:
Post a Comment