In this article, we learn about the sequence.
SEQUENCE
- A sequence generates a sequence of unique integer values.
- A sequence has different orders like ascending or descending.
- We can view the sequence object under the Programmability →Sequences.
SYNTAX OF CREATING SEQUENCE
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH start_value]
[ INCREMENT BY increment_value ]
[ { MINVALUE [ min_value ] } | { NO MINVALUE } ]
[ { MAXVALUE [ max_value ]} | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ cache_size ] } | { NO CACHE } ]
[; ]
- The sequence name specifies a name to uniquely identify.
- Start with states the first value of sequence numbers.
- Increment by specifying the interval between sequence numbers.
- Min value states the minimum value of the sequence.
- Max value states the maximum value produced by the sequence.
- The cycle indicates the sequence is continued for generates the value starts from the min_value or max_value.
- Cache specifies the values to improve the performance.
EXAMPLE OF SEQUENCE
- In the following example, test_seq is a sequence name that incremented by 1, starts at 112, the max value is 120, and min value 112, does not cycle, and caches 7.
CREATE SEQUENCE test_seq INCREMENT BY 1
START WITH 112 MAXVALUE 120 MINVALUE 112
CYCLE CACHE 7
RESULT
- We can access the value help of CURVAL and NEXTVAL.
- The NEXTVAL returns the original value of the sequence.
- The CURVAL returns the current value which is the next value to the NEXTVAL.
- If the sequence is not yet used, the values of NEXTVAL and CURVAL are the same.
SELECT test_seq.NEXTVAL FROM dual
Output:- 112
SELECT test_seq.CURVAL FROM dual
Output:- 112
- In the below example, the NEXTVAL function generates a sequence number from the dual sequence object.
- When we execute the following statement again, then the value incremented by 1.
SELECT test_seq.NEXTVAL FROM dual
Output:- 113
ALTER SEQUENCE
- Syntax:- ALTER SEQUENCE <Seq_Name> variable value
- Example:- ALTER SEQUENCE test_seq MAXVALUE 125
DROP SEQUENCE
- Syntax:- DROP SEQUENCE <Seq_Name>
- Example:- DROP SEQUENCE test_seq
If you are new to database learning — SQL Server recommended is the following must-watch the video: -