V11.5 Update Guide

SQL sequence support

c-treeACE SQL Sequence support allows a sequence of numeric values to be created. The sequence can be used as a source of values, which can be used wherever numeric values in ascending or descending order are required.

CREATE SEQUENCE

Syntax:

CREATE SEQUENCE [owner_name.]sequence_name
[ START WITH start_value ]
[ INCREMENT BY increment_value ]
[ MAXVALUE max_value | NOMAXVALUE ]
[ MINVALUE min_value | NOMINVALUE ]
[ CYCLE | NOCYCLE ]

Description:

The CREATE SEQUENCE command creates a new sequence in the current database.

• sequence_name is the name that is to be assigned to the sequence. The sequence_name in a database must be unique.
• start_value specifies a starting value for the sequence. The value of start_value must be between -9223372036854775808 and 9223372036854775807. The default is 1.
• increment_value specifies the value by which the sequence is incremented when sequence.NEXTVAL is called. Increment by can be positive or negative. The default is 1.
• max_value specifies the maximum value that can be returned by the sequence. If the sequence is a cycling sequence, then the next value after the max_value will be min_value for an ascending sequence and the next value after min_value will be max_value for an ascending sequence. Note that the value of increment_value may mean that the exact max_value or min_value will never be returned. The default is 9223372036854775807.
• min_value specifies the minimum value that can be returned by the sequence. The default min_value is -9223372036854775808.

When CYCLE is specified, a sequence will wrap from the max_value to the min_value for an ascending sequence or from the min_value to a max_value for a descending sequence. When NOCYCLE (the default) is specified, an error is returned when NEXTVAL (see Sequence Values) would exceed the max_value for an ascending sequence or the min_value for a descending sequence.

Example:

The following command creates a sequence called myseq. It starts with a value of 5, increments by 5, and returns a maximum value of 50.

CREATE SEQUENCE myseq START WITH 5 INCREMENT BY 5 MAXVALUE 50;

DROP SEQUENCE

Syntax:

DROP SEQUENCE sequence_name

Description:

The DROP SEQUENCE command deletes the specified sequence.

• sequence_name is the name of the sequence that is to be deleted.

Example:

To delete the sequence myseq, enter:

DROP SEQUENCE myseq ;

ALTER SEQUENCE

Syntax:

ALTER SEQUENCE sequence_name
[ INCREMENT BY increment_value ]
[ MAXVALUE max_value | NOMAXVALUE ]
[ MINVALUE min_value | NOMINVALUE ]
[ CYCLE | NOCYCLE ]

Description:

ALTER SEQUENCE is a SQL command used to alter one or more characteristics of a sequence.

• sequence_name is the name of the sequence that is to be modified.
• increment_value specifies a new increment by value to be associated with the sequence.
• max_value specifies a new maximum value to be associated with the sequence.
• min_value specifies a new minimum value to be associated with the sequence.
• cycle specifies if the sequence should cycle or not.

Note that it is possible to put the sequence into a state from which no further values can be returned through the use of the alter command. For example, if the new max_value is less than the current value of the sequence for an ascending sequence, or if the new increment_value would make the next value be outside of the sequence bounds.

Sequence Values

Sequence values in SQL statements can be referred as follows:

CURRVAL: Returns the current value of a sequence

NEXTVAL: Increments the sequence and returns the next value

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

• sequence.CURRVAL
• sequence.NEXTVAL

Example:

select mysequence.nextval;

insert into mytable values (mysequence.nextval, 'aaa');

Error Codes

-20265 "Sequence with the same name already exists"

-20266 "Sequence cannot be used here"

-20267 "Sequence not found"

-20268 "START-WITH/CURRENT-VALUE cannot be greater than MAXVALUE"

-20269 "START-WITH/CURRENT-VALUE cannot be less than MINVALUE"

-20270 "Invalid sequence MINVALUE specified"

-20271 "Invalid sequence INCREMENT specified"

-20272 "START-WITH cannot be altered in sequence"

-20273 "No options specified for ALTER SEQUENCE"

-20274 "Sequence increment has exceeded MAXVALUE"

-20275 "Sequence decrement has exceeded MINVALUE"

-20276 "Only SELECT and ALTER privileges are valid for sequences"