Common sequence operations in Oracle

Some common operations of sequence in Oracle sequence are so-called serial numbers in oracle. It will automatically increase each time it is obtained, and is generally used in places where it needs to be sorted by serial number. Create Sequence you must first have the create sequence or create any sequence permission, create sequence emp_sequence increment by 1 -- add several start with 1 each time -- count NOMAXVALUE from 1 -- do not set the maximum value NOCYCLE -- accumulate all the time, do not cycle NOCACHE after reaching the maximum value; it is generally set to no cache. Otherwise, the syntax format for creating an Oracle sequence is as follows: 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, it indicates Ora. The values of the cle sequence follow 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. Once emp_sequence is defined, you can use CURRVAL, nextval currval = to return the current value of sequence NEXTVAL = to increase the value of sequence, and then return the sequence value such as: emp_sequence.CURRVAL sequence where sequence can be used: -Alter Sequence in-update set in-VALUES of-NSERT statement in a subquery that does not contain the SELECT statement-INSERT statement of the subquery, snapshot, and VIEW, or the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create. alter Sequence example alter sequence emp_sequence increment by 10 MAXVALUE 10000 CYCLE -- start NOCACHE from the beginning after 10000;-if the CACHE value is specified, ORACLE can place some sequence in the memory in advance, in this way, access 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, you can use nocache when creating sequence to prevent this situation. It is easy to Drop Sequence drop sequence order_seq.