Oracle sequence details

1. Basic syntax
(1) create a sequence
Create sequence [user.] sequence_name
[Increment by n]
[Start with n]
[Maxvalue n | nomaxvalue]
[Minvalue n | nominvalue]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE]
[ORDER | NOORDER]
;
Increment by: Specifies the interval between serial numbers. This value can be a positive or negative integer, but cannot be 0. The sequence is ascending. When this clause is ignored, the default value is 1.
Start with: Specifies the first serial number generated. In ascending order, the sequence can start from a value greater than the minimum value. The default value is the minimum value of the sequence. For descending order, the sequence can start from a value smaller than the maximum value. The default value is the maximum value of the sequence.
MAXVALUE: specifies the maximum value that can be generated by the sequence.
NOMAXVALUE: set the maximum value to 1027 in ascending order and-1 in descending order.
MINVALUE: specifies the minimum value of the sequence.
NOMINVALUE: specify the minimum value as 1 for ascending order. Specify a minimum value of-1026 for descending order.
CYCLE: used cyclically, and then returned with a large maximum value. CACHE: Specify the cache value. If the CACHE value is specified, Oracle can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdown abort), the sequence in the cache will be lost. Therefore, nocache can be used to prevent this situation when creating sequence.
ORDER: sequence values are used in sequence.

(2) Change the sequence
ALTERSEQUENCE [user.] sequence_name
[Increment by n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE]
[ORDER | NOORDER]
;

(3) Delete Sequence
Drop sequence [user.] sequence_name;

2. Sequence usage
The sequence provides two methods: NextVal and CurrVal.
NextVal: gets the next value of the sequence. a nextval increases the value of a sequence.
CurrVal: obtains the current value of the sequence.

However, note that the first time NEXTVAL returns the initial value, the subsequent NEXTVAL automatically increases the value of your increment by, and then returns the added value. CURRVAL always returns the value of the current SEQUENCE, but CURRVAL can be used only after the first NEXTVAL initialization; otherwise, an error will occur.

3. Create a sequential number table SEQUENCE + TRIGGER
3.1 create a table
Create or replace table test_table
(
Idd number (3 ),
Named varchar2 (20)
);
/

3.2 Create sequence sequence_test_table_id:
Create sequence "sequence_test_table_id" increment by 1 start with 1 minvalue 1 maxvalue 999 cycle nocache order;

3.3 create a trigger
To use sequence, you must also use oracle triger (trigger)
Create or replace trigger trigger_insert_sequence
Before insert on test_table
Referencing old as old new as new

* Indicates that the trigger is triggered before each row is inserted to test_table.
For each row
Begin
* Read the next value of sequence_test_table_id and assign it to the new idd of test_table.
Select sequence_test_table_id.nextval into: new. idd from dual;

Exception
When others then
-- Consider logging the error and then re-raise
Raise;
End trigger_insert_sequence

3.4 Test
Insert into test_table (named) values (sysdate );

If we want the sequence to contain more information, such as dates, for future statistics, can we?
Of course!

We can make an idea at triger :)
For example, we need to add the date 20030225xxx to the sequence, and the following xxx represents the value from the sequence.
Update the table first:
Alter table test_table modify (id number (11) * extend the column width
Modify the triger
Create or replace trigger trigger_insert_sequence
Before insert on test_table
Referencing old as old new as new
For each row begin

Select to_char (sysdate, 'yyyymmdd') | lpad (sequence_test_table_id.nextval, 3, '0') into: new. idd from dual;

Exception
When others then
-- Consider logging the error and then re-raise
Raise;
End trigger_insert_sequence