GNU/Linux |
RedHat 5.2(Apollo) |
|
create_sequence(l) |
create sequence - create a new sequence number generator
create sequence seqname
[increment incby_value] | ||
[minvalue min_value] | ||
[maxvalue max_value] | ||
[start start_value] | ||
[cache cache_value] | ||
[cycle] |
Create sequence will enter a new sequence number generator into the current data base. Actually, new single block table with name seqname will be created and initialized. The generator will be “owned” by the user issuing the command.
The increment is optional clause. Positive value will make ascending sequence, negative - descending. Default value is 1.
The optional integer minvalue determines the minimum value a sequence can be. Defaults are 1/-2147483647 for ascending/descending sequences.
Use optional integer maxvalue to determine the maximum value for sequence. Defaults are 2147483647/-1 for ascending/descending sequences.
The optinal start value enables sequence to begin anywhere. Default is minvalue for ascending sequences and maxvalue for descending ones.
The cache option enables sequence numbers to be preallocated and stored in memory for faster access. The minimum value is 1 (i.e. - no cache) and it is default. NOTE: each backend uses own cache to store allocated numbers. Cached but not used in current session numbers will be lost.
The optional cycle keyword may be used to enable sequence to continue when the maxvalue/minvalue has been reached by ascending/descending sequence. If the limit is reached, the next number generated will be whatever the minvalue/maxvalue is.
After sequence created, You may use function nextval with sequence name as argument to get new number from sequence specified. Function currval (’sequence_name’) may be used to determine number returned by last call to nextval for specified sequence in current session.
Use query like
select * from <sequence_name>;
to get parameters of a sequence.
Low-level locking is used to enable multiple simultaneous calls to a generator.
--
-- Create sequence seq caching 2 numbers, starting with 10
--
create sequence seq cache 2 start 10;
--
-- Select next number from sequence
--
select nextval (’seq’);
--
-- Use sequence in insert
--
insert into table _table_ values (nextval
(’seq’),...);
drop sequence(l).
create_sequence(l) |