Oracle sequence usage: Creation and Deletion

Before you begin to explain how to use the Oracle sequence, add "/" to the use of Oracle client sqlplus, that is, if you execute multiple rows of statements, you must add "/" to end the statement and execute it! The topic of this article is to create an Oracle sequence and trigger to implement auto-increment of the table's primary key.

1. First create a sequence. The syntax format of the Oracle sequence is:
Create sequence name
[Increment by n]
[Start with n]
[{MAXVALUE/MINVALUE n | NOMAXVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

1) increment by is used to define the sequence step. If omitted, the default value is 1. If a negative value is displayed, the value of the Oracle sequence decreases according to this step.

2) start with defines the initial value of the sequence (that is, the first value generated). The default value is 1.

3) MAXVALUE defines the maximum value that can be generated by the sequence generator. The "NOMAXVALUE" option is the default option, indicating that no maximum value is defined. In this case, the maximum value generated by the system for an incremental Oracle sequence is the 27 power of 10. For a descending sequence, the maximum value is-1.

4) MINVALUE defines the minimum value generated by the sequence generator. The "NOMAXVALUE" option is the default option, indicating that there is no minimum value defined. What is the minimum value that the system can produce for the descending sequence? 10 to the power of 26; for incremental sequence, the minimum value is 1.

5) CYCLE and NOCYCLE indicate whether to CYCLE when the value of the sequence generator reaches the limit value. CYCLE indicates loop, and NOCYCLE indicates no loop. If there is a loop, when the ascending sequence reaches the maximum value, it loops to the minimum value. When the descending sequence reaches the minimum value, it loops to the maximum value. If there is no loop, an error occurs when a new value is generated after the limit value is reached.

6) CACHE (buffer) defines the size of the memory block for storing the sequence. The default value is 20. NOCACHE indicates no memory buffer for the sequence. Buffer the sequence memory to improve the sequence performance.

2. the syntax for deleting an Oracle SEQUENCE is the drop sequence name;

Assume that the table TEST has the primary key TEST_ID.
1) Create an incremental sequence SEQ_TEST:
Create sequence SEQ_TEST
Increment by 1
Start with 1
Minvalue 1 nomaxvalue
Nocylce

2) create a trigger. when data is inserted into the TEST table, use the Oracle sequence to deduplicate the primary key value.
Create trigger TRG_TEST before insert on TEST
For each row
Begin
Select SEQ_TEST.nextval into: new. TEST_ID from dual;
End;

Now, the creation is complete!

You can also call the sequence in an SQL statement at the time of insertion without using a trigger, for example
Insert into TEST values (SEQ_TEST.nextval ,......) ;

1. Example code for creating a tablespace in Oracle
2. How to Create a tablespace in Oracle
3. Oracle administrator creation procedure Overview
4. Solutions that you have to look at when creating a stored procedure in Oracle
5. Detailed explanation of the entire process of creating user permissions in Oracle