CREATE SEQUENCE
CREATE SEQUENCE#
Syntax#
create_sequence ::=#
sequence_options ::=#
sync_table_clause ::=#
Prerequisites#
Only the SYS user and users having the CREATE SEQUENCE system privilege can execute this statement. In addition, it is necessary to be the SYS user or have the CREATE ANY SEQUENCE system privilege in order to create a sequence in another user's schema
Description#
This statement is used to define a new sequence having the specified name and automatically populate the sequence.
user_name#
This is used to specify the name of the owner of the sequence to be created. If it is omitted, Altibase will create the sequence in the schema of the user who is connected via the current session.
seq_name#
This is used to specify the name of the sequence to be created. Refer to "Rules for Object Names" for more information on specifying names.
START WITH#
This is the initial value of the sequence. This can be set to any value between MINVALUE and MAXVALUE inclusive. If this value is omitted and the value for INCREMENT BY is more than 0, the default value is the same as the minimum value of the sequence. If this value is omitted and the value for INCREMENT BY is less than 0, the default value is the same as the maximum value of the sequence.
INCREMENT BY#
This is the value by which the sequence increments. The default value is 1. The absolute of this value must be less than the difference between MAXVALUE and MINVALUE.
MAXVALUE#
This is the maximum value of the sequence. This can be set to any value between -9223372036854775805 and 9223372036854775806. If the value for INCREMENT BY is more than 0, the default value is 9223372036854775806. If the value for INCREMENT BY is less than 0, the default value is -1.
MINVALUE#
This is the minimum value of the sequence. This can be set to any value between -9223372036854775806 and 9223372036854775805. If the value for INCREMENT BY is more than 0, the default value is 1. If the value for INCREMENT BY is less than 0, the default value is -9223372036854775806.
CYCLE#
This clause is used to ensure that the sequence will continue to generate values when it reaches the value specified using MAXVALUE or MINVALUE. The sequence cycles again from the minimum value in the case of an ascending sequence, or from the maximum value in the case of a descending sequence.
CACHE#
A specified number of sequence values can be created in advance and cached in memory so that they can be accessed more quickly. The cache is populated when a key value is first requested from a new sequence, and is accessed every time another key value is requested from the sequence. After the last sequence value in the cache has been used, the next request for a key value from the sequence causes new sequence values to be created and cached in memory. When a sequence is created, the default CACHE value is 20.
ENABLE | DISABLE SYNC TABLE#
This specifies whether or not to create a custom table for sequence replication for the purpose of replicating a sequence.
ENABLE#
Creates a custom table for sequence replication. The table is automatically granted the name, [sequence name]$seq.
DISABLE#
Does not create a custom table for sequence replication.
If this option is not specified, a custom table for sequence replication is not created by default.
Consideration#
- Please note that the sequence_name.CURRVAL value of a newly created sequence cannot be accessed. In order to access the sequence_name.CURRVAL value for a newly created sequence, it is first necessary to access the sequence_name.NEXTVAL value.
- A custom table for sequence replication can be created only if the length of the sequence name is equal to or smaller than 36 bytes.
Examples#
The following SQL statements show how to define new sequences and check sequence values and information.
iSQL> CREATE TABLE seqtbl(i1 INTEGER);
Create success.
iSQL> CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
FOR i IN 1 .. 10 LOOP
INSERT INTO seqtbl VALUES(i);
END LOOP;
END;
/
Create success.
iSQL> EXEC proc1;
Execute success.
<Query> Use the following statements to check information on sequence objects.
iSQL> select * from v$seq;
This command displays information about all sequence objects that have been created. Unlike Select * from seq, querying the performance view allows information about other users' sequences to be viewed. For more information on the performance view V$SEQ, please refer to the section of the Data Dictionary that explains performance views in the General Reference.
<Query> Create a sequence named seq1 that begins at 13, increments by 3, and has a minimum value of 0 and no maximum value.
iSQL> CREATE SEQUENCE seq1
START WITH 13
INCREMENT BY 3
MINVALUE 0 NOMAXVALUE;
Create success.
iSQL> INSERT INTO seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> SELECT * FROM seqtbl;
SEQTBL.I1
--------------
1
2
3
4
5
6
7
8
9
10
13
16
12 rows selected.
<Query> Change seq1 so that it increments by 50 and starts over at the minimum value if it reaches the maximum value of 100.
iSQL> ALTER SEQUENCE sys.seq1
INCREMENT BY 50
MAXVALUE 100
CYCLE;
Alter success.
iSQL> INSERT INTO sys.seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> SELECT * FROM sys.seqtbl;
SEQTBL.I1
--------------
1
2
3
4
5
6
7
8
9
10
13
16
66
0
50
100
16 rows selected.
<Query> Check the current value of seq1, which will cause a new value to be generated.
iSQL> SELECT seq1.CURRVAL FROM dual;
SEQ1.CURRVAL
-----------------------
100
1 row selected.
<Query> Change the value in column i1 to the next value of the sequence, which is 0.
iSQL> UPDATE SEQTBL SET i1 = seq1.NEXTVAL;
16 rows updated.
<Query> Check the current value of seq1.
iSQL> SELECT seq1.CURRVAL FROM dual;
SEQ1.CURRVAL
-----------------------
0
1 row selected.
<Query> Change seq1 so that the specified number of sequence values (25) are cached for the faster access.
iSQL> ALTER SEQUENCE seq1
INCREMENT BY 2
MAXVALUE 200
CACHE 25;
Alter success.
iSQL> CREATE OR REPLACE PROCEDURE proc2
AS
BEGIN
FOR i IN 1 .. 30 LOOP
INSERT INTO sqqtbl VALUES(seq1.NEXTVAL);
END LOOP;
END;
/
Create success.
iSQL> EXEC proc2;
Execute success.
iSQL> SELECT * FROM seqtbl;
SEQTBL.I1
--------------
0
50
100
0
50
100
0
50
100
0
50
100
0
50
100
0
2
4
6
8
10
12
14
.
.
.
58
60
46 rows selected.
<Query> When connected to a database as the SYS user, output information on all sequences.
iSQL> SELECT * FROM SEQ;
USER_NAME
--------------------------------------------
SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY
------------------------------------------------
MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
------------------------------------------------
SYS
SEQ1 60 2
0 200 YES 25
1 row selected.
<Query> The following SQL statements show how to define sequences and view sequence values and information using multiple user accounts.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE USER user1 IDENTIFIED BY user1;
Create success.
iSQL> CREATE USER user2 IDENTIFIED BY user2;
Create success.
iSQL> CONNECT user1/user1;
Connect success.
iSQL> CREATE SEQUENCE seq1 MAXVALUE 100 CYCLE;
Create success.
iSQL> CREATE SEQUENCE seq2;
Create success.
<Query> Output information on all sequences created by user1.
iSQL> SELECT * FROM SEQ;
SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY
------------------------------------------------
MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
------------------------------------------------
SEQ1 1 1
1 100 YES 20
SEQ2 1 1
1 9223372036854775806 NO 20
2 rows selected.
iSQL> CONNECT user2/user2;
Connect success.
iSQL> CREATE SEQUENCE seq1 INCREMENT BY -30;
Create success.
iSQL> CREATE SEQUENCE seq2 INCREMENT BY -10 MINVALUE -100;
Create success.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE SEQUENCE seq2 START WITH 20 INCREMENT BY 30;
Create success.
iSQL> CREATE SEQUENCE seq3 CACHE 40;
Create success.
<Query> When connected to the database as the SYS user, output information on all sequences.
iSQL> SELECT * FROM SEQ;
USER_NAME
--------------------------------------------
SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY
------------------------------------------------
MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
------------------------------------------------
SYS
SEQ1 60 2
0 200 YES 25
SYS
SEQ2 20 30
1 9223372036854775806 NO 20
SYS
SEQ3 1 1
1 9223372036854775806 NO 40
USER1
SEQ1 1 1
1 100 YES 20
USER1
SEQ2 1 1
1 9223372036854775806 NO 20
USER2
SEQ1 -1 -30
-9223372036854775806 -1 NO 20
USER2
SEQ2 -1 -10
-100 -1 NO 20
7 rows selected.
<Query> Create the sequence seq1 with the cache size 100, which creates a custom table for sequence replication.
CREATE SEQUENCE seq1 CACHE 100 ENABLE SYNC TABLE;