ORACLE Auto-incrementing SEQUENCE

ORACLE Auto-incrementing SEQUENCE in oracle, sequence is the so-called serial number. It will automatically increase each time it is obtained. It is generally used in places where it needs to be sorted by serial number. Www.2cto.com 1. Create Sequence you must have the create sequence or create any sequence permission first, 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 loop CACHE 10; once emp_sequence is defined, you can use CURRVAL, nextval currval = to return the current sequence Value NEXTVAL = to increase the sequence value, and then return the sequence value, for example, emp_sequence.CURRVAL emp_sequence.NEXTVAL, where sequence can be used: -SELECT statements that do not contain subqueries, snapshot, and VIEW -In the subquery of the INSERT statement-in the VALUES of the NSERT statement-in the SET of UPDATE, you can see the following example: insert into emp VALUES (em1_q. nextval, 'Lewis ', 'cler', 7902, SYSDATE, 1200, NULL, 20); SELECT em1_q. currval from dual; but note:-the first NEXTVAL returns the initial value. The subsequent NEXTVAL automatically increases the value of your defined 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. NEXTVAL increases the SEQUENCE value once. Therefore, if you use multiple NEXTVAL values in the same statement, their values are different. Understand? -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. 2. Alter Sequence: you are 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 -- To NOCACHE from the beginning after 10000; affects the initialization parameters of Sequence: SEQUENCE_CACHE_ENTRIES = sets the number of sequence that can be simultaneously cached. You can easily Drop Sequence drop sequence order_seq; example Sequence: create sequence scott. dmifpostid start with 261 increment by 1 nominvalue nomaxvalue nocycle cache 20 NOORDER 3. How to use the first method: Generally, you need to create a TRIGGER ), before data is inserted, Run Sequence to generate an auto-increment number. Example Trigger create or replace trigger scott. DMIFATTACH_TRG before insert on scott. dmifattach referencing old as old new as new for each row begin select SCOTT. DMIFATTACHID. nextval into: new. ATTACHID from dual; end; Method 2: it can be called directly when data is inserted. Insert into table (id, name) values (seq_name.nextval, 'name'); others: For hibernate, a Sequence is run to generate an id before inserting data, so if there is a TRIGGER and you have to run the Sequence again, you will find that the id is increased with 2 as the step. Delete the Trigger and the step is 1. If hibernate is used. in the xml file, set the ID generation method to sequence. <id name = "postid" type = "java. lang. integer "column =" POSTID "> <meta attribute =" field-description "> @ hibernate. id generator-class = "sequence" type = "java. lang. integer "column =" POSTID "</meta> <! -- Set the id Generation Method --> <generator class = "sequence"> <param name = "sequence"> SCOTT. DMIFPOSTID </param> </generator> </id> simultaneously calls this. getHibernateTemplate (). when the save () method is used, the system automatically generates the id set to be queried to this object based on sequence.